Strategy to update denormalized data, cache, pages
We have a series of items that are stored data, views, etc., that help with performance. These items need to be updated at various times when data changes or is added to the database. We need some strategy for how and when to update these denormalizations.
#1 Updated by Michael Lee over 16 years ago
A number of fields are "denorm" fields. These are indicated as attModel='denorm' in /vegbank/docs/xml/db_model_vegbank.xml.
Denorm fields are updated in two ways. The first approach was a .sql file (see /vegbank/src/sql/denorm). Then we added a java utility. I don't know what that's called. The utility uses SQL snippets from the SQLStore.properties file (/vegbank/src/java/org/vegbank/common/SQLStore.properties). We used AJaX to update the denorm fields with this utility or with custom jsp tags (vegbank:denorm). The AJaX approach failed because of http timeouts since the updates take a while sometimes.
#2 Updated by Michael Lee over 16 years ago
We also have certain views (stored html which was saved from .jsp files that are dynamic). These are built currently using ant when we build the site (because I didn't know of a better way to do it). This is done by the ant target "cache" in /vegbank/web/build.xml. The views are stored in the "raw" directory, because their contents are just raw, unstyled tables.
#5 Updated by Michael Lee almost 16 years ago
I think we decided against triggers as they proved to be slow and potentially circular. This bug remains of high importance. I think it processes correctly after XML Load, but updates should also be triggered after someone interprets a plant or plot. (and that should update the XML_Cache, too)
#6 Updated by Michael Lee almost 16 years ago
Currently denormalizations are kicked off only when someone loads data. Any subsequent changes to the database need to tie in to the denorm utility and run updates on tables that are affected by the change in question. e.g. when interpreting a plant, you should run denorm update to taxonobservation, which reads some of the denorms from taxoninterpretation, where a new record was just added.