Activity
From 03/19/2013 to 04/17/2013
04/17/2013
- 05:28 PM Revision 8586: schemas/VegCore/VegCore.ERD.mwb: fixed split connector
- 05:25 PM Revision 8585: schemas/VegCore/VegCore.ERD.mwb: fixed inheritance lines
- 05:22 PM Revision 8584: schemas/VegCore/VegCore.ERD.mwb: source: added contact field (fkey to party)
- 05:17 PM Revision 8583: schemas/VegCore/VegCore.ERD.mwb: party: inherit from record instead of source, because a party is defined by and scoped by a datasource but isn't a datasource itself (however, a named datasource can have an associated admin party)
- 05:12 PM Revision 8582: schemas/VegCore/VegCore.ERD.mwb: moved tables to make more room between specimen and taxon tables
- 04:45 PM Revision 8581: schemas/VegCore/VegCore.ERD.mwb: split taxon_occurrence into taxon_occurrence and taxon_observation, because a taxon_*occurrence* is actually the *thing* on which a taxon_*observation* is made, not the observation itself. rearranged tables significantly to put observations next to the things they observe, and to make the ERD less wide so it will fit on a small screen without as much scaling (allowing a bigger font size when the ERD is shrunk to fit the screen).
- 02:32 PM Revision 8580: schemas/VegCore/VegCore.ERD.mwb: added primary columns. changed VARCHAR(45) columns to TEXT and also set default type in ERD so future columns won't need to be changed.
- 01:04 PM Revision 8579: schemas/VegCore/VegCore.ERD.mwb: added primary columns, including catch-all hstores for custom columns. made stem inherit from individual and stem_observation from individual_observation because any stem can have individual-level traits and identifying info.
- 11:38 AM Revision 8578: schemas/VegCore/VegCore.ERD.mwb: replaced trait table with traits hstore on taxon_occurrence, since the trait table is basically just a way of allowing the user to add arbitrary measurement columns to the taxon_occurrence. units can be indicated by storing a record with the quantity and units, converted to a string, and parsing it back out as a record upon retrieval.
- 11:29 AM Revision 8577: schemas/VegCore/VegCore.ERD.mwb: renamed taxon_observation to taxon_presence for clarity and to distinguish it better from taxon_occurrence
- 11:25 AM Revision 8576: schemas/VegCore/VegCore.ERD.mwb: taxon_determination: added identified_date and unique index on identifying columns
- 11:15 AM Revision 8575: schemas/VegCore/VegCore.ERD.mwb: taxon_determination: added identified_by. rearranged taxon tables to visually differentiate between inheritance relationships (1:1) and directional HAS-A arrows
- 10:38 AM Revision 8574: schemas/VegCore/VegCore.ERD.mwb: taxon tables: added catch-all hstores for custom columns
- 10:17 AM Revision 8573: schemas/VegCore/VegCore.ERD.mwb: split taxon_concept into taxon_name (containing the components of a name) and taxon_concept (containing the taxon's formal specification and relationships to other taxon_concepts). this avoids requiring a taxon_concept entry for names that are not true taxon concepts. taxon_name: added taxon name/author-related components. TNRS taxonomic scrubbing steps: added dummy id column to avoid sync problems with empty tables.
- 08:38 AM Revision 8572: schemas/VegCore/VegCore.ERD.mwb: taxon tables: added nonidentifying columns
- 06:55 AM Revision 8571: schemas/VegCore/VegCore.ERD.mwb: regenerated exports
- 06:51 AM Revision 8570: schemas/VegCore/VegCore.ERD.mwb: categories: updated box sizes
- 06:45 AM Revision 8569: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 06:40 AM Revision 8568: schemas/VegCore/VegCore.ERD.mwb: validatable_place: added unique index on identifying columns
- 06:38 AM Revision 8567: schemas/VegCore/VegCore.ERD.mwb: made place_path and coordinates globally-scoped rather than per-datasource, so that there is only one geovalidation row for each combination of them rather than one per datasource place. added globally-scoped validatable_place table that stores combinations of place_path and coordinates, and made geovalidation extend it (since geovalidation provides optional fields and is 1:1 with it).
- 06:06 AM Revision 8566: schemas/VegCore/VegCore.ERD.mwb: made geovalidation extend place_path instead of being pointed to by it, because it provides a set of optional fields a (geovalidated) place_path may have
- 05:33 AM Revision 8565: schemas/VegCore/VegCore.ERD.mwb: made place_path extend place instead of place having a place_path, because place_path now provides an optional set of fields that a place may provide, rather than being a completely separate entity
- 05:20 AM Revision 8564: schemas/VegCore/VegCore.ERD.mwb: renamed named_place to place_path since place now serves the purpose that named_place used to (i.e. representing geographical areas)
- 05:13 AM Revision 8563: schemas/VegCore/VegCore.ERD.mwb: removed invisible U+FEFF Unicode character
- 05:10 AM Revision 8562: schemas/VegCore/VegCore.ERD.mwb: renamed location to place because any place can be a location, not just a plot or point (e.g. Barro Colorado Island is a CTFS "plot" but definitely much larger than the typical plot)
- 05:03 AM Revision 8561: schemas/VegCore/VegCore.ERD.mwb: labeled TNRS taxonomic scrubbing steps
- 04:42 AM Revision 8560: schemas/VegCore/VegCore.ERD.mwb: labeled TNRS taxonomic scrubbing steps
- 03:52 AM Revision 8559: schemas/VegCore/VegCore.ERD.mwb: geovalidation: put named_place before coordinates so they would be in path order (one named_place can have many coordinates in it)
- 03:43 AM Revision 8558: schemas/VegCore/VegCore.ERD.mwb: regenerated exports
- 03:39 AM Revision 8557: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 03:30 AM Revision 8556: schemas/VegCore/VegCore.ERD.mwb: taxon_assertion: added fkey to taxon_concept/taxon_path to store any parsed taxonomic fields provided by the data provider
- 03:04 AM Revision 8555: schemas/VegCore/VegCore.ERD.mwb: linked taxon_concept directly to taxon_string rather than to child table taxon_name, because it creates unnecessary confusion to have two tables storing taxonomic strings. a taxon_string is anything that can be used as input to TNRS, and may or may not include a cf_aff.
- 02:57 AM Revision 8554: schemas/VegCore/VegCore.ERD.mwb: link each taxon_string to a parsed_taxon_assertion. removed verbatim_taxon_assertion because that function is now performed by taxon_string.
- 02:48 AM Revision 8553: schemas/VegCore/VegCore.ERD.mwb: added taxon_name, which is used by taxon_concept
- 02:37 AM Revision 8552: schemas/VegCore/VegCore.ERD.mwb: added taxon_string, which is used by taxon_assertion
- 02:15 AM Revision 8551: schemas/VegCore/VegCore.ERD.mwb: added unique indexes on identifying columns where possible. unique indexes differ from primary keys in that they are generally on multiple columns, while pkeys are on a text field which is the concatenation of the identifying columns.
- 01:49 AM Revision 8550: schemas/VegCore/VegCore.ERD.mwb: hierarchical tables: made parent pointer NOT NULL so that it can be an identifying column. this requires the root(s) of the hierarchy to be indicated with a self-referential parent pointer rather than a NULL value. this also allows materializing parent elements to arbitrary levels without worrying about hitting the root, because the root's ancestors will simlpy be itself for as many levels as are needed (i.e. materializing several levels up the tree is guaranteed to produce a non-NULL entry for all levels).
- 01:36 AM Revision 8549: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 01:35 AM Revision 8548: schemas/VegCore/VegCore.ERD.mwb: taxon_assertion: removed fkey to taxon_concept because only parsed_taxon_assertions have a corresponding taxon_concept
- 01:31 AM Revision 8547: schemas/VegCore/VegCore.ERD.mwb: fixed table positions
- 01:25 AM Revision 8546: schemas/VegCore/VegCore.ERD.mwb: lined up tables
- 01:24 AM Revision 8545: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 01:21 AM Revision 8544: schemas/VegCore/VegCore.ERD.mwb: parsed_taxon_assertion.matched_taxon: changed it to be an fkey to taxon_path, because the parsing must also populate the denormalized path fields in addition to defining a taxon_concept with the concatenated scientific name
- 01:18 AM Revision 8543: schemas/VegCore/VegCore.ERD.mwb: fixed inheritance lines
- 01:17 AM Revision 8542: schemas/VegCore/VegCore.ERD.mwb: added taxon_path
- 01:10 AM Revision 8541: schemas/VegCore/VegCore.ERD.mwb: geological_context: made it the same color as community because it is a type of community for the geological aspects of the location
- 01:01 AM Revision 8540: schemas/VegCore/VegCore.ERD.mwb: renamed fkeys to match renamed tables
- 12:34 AM Revision 8539: schemas/VegCore/VegCore.ERD.mwb: merged synonym_taxon, accepted_taxon into taxon_concept because *any* taxon_concept can have an accepted taxon which indicates the canonical name for that taxon, not just TNRS match results
- 12:15 AM Revision 8538: schemas/VegCore/VegCore.ERD.mwb: merged taxon_concept and taxon, because every taxon is according to someone (whether that's Weakley, Tropicos, or the person identifying a taxon_occurrence). note that the according_to is usually different from the source (which is the datasource that owns the record), but if there is no specific according_to, the according_to is the datasource itself.
04/16/2013
- 11:00 PM Revision 8537: schemas/VegCore/VegCore.ERD.mwb: lightened table colors so dark text is easier to see against them. used colors from VegBank ERD (http://vegpath.org/fs/schemas/VegBank/vegbank.ERD.pdf) where possible, to have a familiar look-and-feel for those who have seen the VegBank ERD.
- 10:38 PM Revision 8536: schemas/VegCore/VegCore.ERD.mwb: categories: added stratum
- 10:29 PM Revision 8535: schemas/VegCore/VegCore.ERD.mwb: categories: put categories on the same halves of the legend as they are in the ERD
- 10:23 PM Revision 8534: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 10:23 PM Revision 8533: schemas/VegCore/VegCore.ERD.mwb: added stratum, plot_stratum
- 06:14 PM Revision 8532: schemas/VegCore/VegCore.ERD.mwb: added plot subclass of location
- 05:49 PM Revision 8531: schemas/VegCore/VegCore.ERD.mwb: fixed inheritance lines
- 05:45 PM Revision 8530: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 05:34 PM Revision 8529: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 05:31 PM Revision 8528: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 05:28 PM Revision 8527: schemas/VegCore/VegCore.ERD.mwb: removed redundant "DEFAULT NULL" clauses on nullable fields
- 05:24 PM Revision 8526: schemas/VegCore/VegCore.ERD.mwb: renamed taxon_occurrence.*_taxon_determination to taxon_occurrence.*_determination because the column name does not need to be globally unique, and can therefore be shorter while still being unambiguous within the table
- 04:34 PM Revision 8525: schemas/VegCore/VegCore.ERD.mwb: fixed lines
- 04:33 PM Revision 8524: schemas/VegCore/VegCore.ERD.mwb: changed all fkeys to CASCADE to support cascading deletes. this allows datasources to be reloaded, because the corresponding source row can be deleted, and the delete will cascade to every record that's part of that datasource.
- 04:25 PM Revision 8523: schemas/VegCore/VegCore.ERD.mwb: changed globally unique column names to locally unique column names, now that the SQL dotpath algorithm (http://vegpath.org/wiki/SQL_dotpaths#Algorithm) can be used to generate globally unique column names automatically according to the fkeys. in particular, this means that the pkeys have all been renamed to "id".
- 02:30 PM Revision 8522: schemas/VegCore/VegCore.my.sql: removed default_schema qualifier from names because all objects are in the same schema
- 02:29 PM Revision 8521: schemas/VegCore/VegCore.my.sql: regenerated from ERD
- 09:25 AM Revision 8520: bugfix: schemas/VegCore/VegCore.ERD.mwb: fixed pkey names to match table names
- 09:18 AM Revision 8519: schemas/VegCore/VegCore.ERD.mwb: renamed matched_taxon table to synonym_taxon because it can be used to store any taxon which has a corresponding accepted_taxon, not just the match results from TNRS
- 09:05 AM Revision 8518: schemas/VegCore/VegCore.ERD.mwb: renamed qualified_taxon tables to *taxon_assertion because they are not just a taxon name plus an optional cf/aff qualifier, but an actual operator/value pair that is the right-hand side of a comparison with a taxon or taxon_occurrence
- 08:43 AM Revision 8517: bugfix: schemas/VegCore/VegCore.ERD.mwb: qualified_taxon subclasses: changed HAS-A arrows to point in the other direction, because the many:1 relationship in fact narrows towards the accepted_taxon. the arrows used to be in the opposite direction because of the way the TNRS results are attached incrementally.
- 08:28 AM Revision 8516: README.TXT: Maintenance: to backup the Ubuntu VM: removed invisible U+FEFF Unicode character
- 08:26 AM Revision 8515: README.TXT: added steps to backup the Ubuntu VM
- 07:48 AM Revision 8514: bugfix: schemas/.htaccess: corrected $innermost_dir
- 07:46 AM Revision 8513: added .htaccess files for inputs/, schemas/ because these reside outside the DOCUMENT_ROOT, and therefore need an explicit RewriteBase directive so that RewriteRules in their subdirs can construct self-referential URLs. this ensures that the input URLs to RewriteRules are always URL paths instead of filesystem paths, which tend to mess up the redirects. (mod_rewrite is capable of doing filesystem-only redirects, but the associated RewriteRules require special care to ensure they match and reconstruct the filesystem path properly.) the presence of filesystem paths in RewriteRule inputs had been causing numerous bugs, especially in rules that use %{REQUEST_URI}, and the addition of the RewriteBases will hopefully avoid these types of bugs in the future. note that *every* path that resides outside the DOCUMENT_ROOT needs an explicit RewriteBase in its top-level .htaccess.
- 07:14 AM Revision 8512: schemas/VegCore/VegCore.ERD.mwb: ensured that inheritance relationships are drawn as 1:1
- 07:00 AM Revision 8511: web/**/.htaccess: appended query strings for external redirects: just use ? rather than ?& when an external redirect will be perfomed, because no dotpath parsing will be performed on the query string first (which would necessitate the & if the new query string looks like a dotpath)
04/14/2013
- 09:30 PM Revision 8510: schemas/VegCore/VegCore.ERD.png: Regenerated again because some tables were highlighted when it was exported, causing the highlighting to appear in the PNG
- 09:27 PM Revision 8509: schemas/VegCore/VegCore.ERD.mwb: Regenerated exports
- 09:26 PM Revision 8508: schemas/VegCore/VegCore.ERD.mwb: shortened embedded ERD URL using new abbreviations
- 09:19 PM Revision 8507: web/**/.htaccess: RewriteRules: ensured flags are in alphabetical order
- 09:10 PM Revision 8506: removed no longer needed web/dotpath.php. use web/.htaccess's parse-dotpath RewriteRule instead.
- 09:07 PM Revision 8505: web/.htaccess: parse dotpath in query string: moved parsing from dotpath.php to RewriteRules. with the eventual addition of config env vars, this makes it possible for subdirs to configure their own dotpath parsing, rather than having *all* levels of the dotpath translated to a URL path. (for inner levels, it may el.g. be more appropriate to parse the dotpath as query params or pass it along to an external URL.) note that it takes only two lines of mod_rewrite (plus a workaround RewriteRule) to replace over a dozen lines of PHP code.
- 08:54 PM Revision 8504: web/.htaccess: renamed REQUEST_URI_no_/ to REQUEST_URI_no_extra_/ to clarify that only an excess / is removed
- 08:52 PM Revision 8503: web/.htaccess: moved set REQUEST_URI_no_/ under translate subdomain to path because it needs to be set right before being used to be accurate
- 08:44 PM Revision 8502: web/.htaccess: setting REQUEST_URI_no_/: use one RewriteCond instead of two since the regexps can be combined
- 02:50 PM Revision 8501: web/.htaccess: parse dotpath in query string: removed dotpath auto-detection code because this is now handled by a separate RewriteRule
- 02:49 PM Revision 8500: web/.htaccess: auto-detect dotpath in query string (having a . before any = ) and mark it using a leading . . this is done as a separate step so that the dotpath is already explicitly marked as such when it's parsed.
- 02:01 PM Revision 8499: web/dotpath.php: just match the QUERY_STRING directly, excluding &, instead of splitting apart the QUERY_STRING and then matching the dotpath portion. this allows the resulting regexp to be used directly in a .htaccess file.
- 01:08 PM Revision 8498: web/.htaccess: prepend dir name if it's part of the filename: support MultiViews filenames without the extension, by using a lookahead request -F instead of a regular -f
- 01:02 PM Revision 8497: schemas/VegCore/.htaccess: added RewriteBase to support relative redirects. this enables auto-prepending dir name if it's part of the filename, e.g. VegCore/ERD.pdf -> VegCore/VegCore.ERD.pdf).
- 12:59 PM Revision 8496: web/.htaccess: prepend dir name if it's part of the filename (this works only if the dir supports relative redirects and sets %{ENV:innermost_dir})
- 11:44 AM Revision 8495: schemas/VegCore/VegCore.ERD.mwb: made matched_taxon and accepted_taxon inherit from taxon instead of qualified_taxon because they don't contain the cf_aff (cf_aff.vegpath.org; aka identificationQualifier) that makes a taxon qualified
- 11:16 AM Revision 8494: schemas/VegCore/VegCore.ERD.mwb: removed occurrence table, because it's really a synonym for taxon_occurrence ("specimen, observation, etc." <http://rs.tdwg.org/dwc/terms/#Occurrence>)
- 10:48 AM Revision 8493: schemas/VegCore/VegCore.ERD.mwb: added labels for the left and right halves (event and occurrence). rearranged tables to help separate event- and occurrence-related tables.
- 10:17 AM Revision 8492: schemas/VegCore/VegCore.ERD.mwb: rearranged to leave more space for location_observation, and to put all event-related fields on the left and occurrence-related fields on the left
- 09:32 AM Revision 8491: web/.htaccess: parse dotpath in query string: heuristically detect query strings that don't contain dotpaths (such as mod_autoindex sort conditions), rather than always interpreting the part before the first & as the dotpath. now, the part before the first & will be interpreted as a dotpath if it has a . before any = . (query param names containing . are rare, so this usually works correctly.) note that you can always prepend . to force a dotpath, or & to force a query param.
- 09:06 AM Revision 8490: web/dotpath.php: remove any . prefix which forces interpretation as a dotpath
- 07:38 AM Revision 8489: schemas/VegCore/VegCore.ERD.mwb: taxon_concept: renamed source to according_to
- 07:06 AM Revision 8488: schemas/VegCore/VegCore.ERD.mwb: removed no longer used images from zip archive using `zip -d`. this needs to be done every time an image is deleted from the diagram to remove it from the archive (and reclaim the unused space, since `zip -d` will also shrink the archive). note that you can't just expand and re-zip the files, because there is a header of some sort that indicates to MySQL Workbench that it's a valid .mwb file.
- 06:27 AM Revision 8487: bugfix: schemas/VegCore/VegCore.ERD.mwb: also fixed inheritance relationships to the record table to be 1:1 (by search-and-replacing and then updating document.mwb.xml in the .mwb zip archive using `zip -u`). added separate legend for relationships and connector styles, which now uses example tables instead of screenshot images to ensure accurate sizing at all ERD zoom levels.
- 05:17 AM Revision 8486: bugfix: schemas/VegCore/VegCore.ERD.mwb: fixed inheritance relationships to be 1:1 with optional participation by the derived table, rather than the incorrect 1:many relationshipwhich MySQL Workbench defaults to. (in OOP, each derived instance has its *own* instance of the base class, because it is a separate object.)
- 05:07 AM Revision 8485: web/**/.htaccess: When there is *no* external redirect for the directory without the trailing /, instead auto-add the trailing / to ensure mod_autoindex relative links work properly. This must be done manually because the top-level .htaccess will not auto-add the trailing / if the dir has an .htaccess file (to allow the custom external redirects). There are actually only two .htaccess files that this applies to (web/.phpPgAdmin/.htaccess, .phpMyAdmin/.htaccess), indicating that dirs which have any kind of virtual paths (redirects) almost always have a "root" redirect when there is no specific subpath. This root redirect is intended to link to the datasource's *own* index of its available resources, e.g. the list of tables for a database source.
- 04:48 AM Revision 8484: removed web/aggregators/.htaccess because it was only there to auto-add the trailing /, but this is now done by web/.htaccess
- 04:29 AM Revision 8483: bugfix: web/.htaccess: auto-add trailing /: %{LA-F:REQUEST_FILENAME} lookaheads don't work, because they do not show the externally redirected URL, so we need to use an alternate mechanism instead, which just assumes that any dir containing an .htaccess file may contain a redirect and ignores just those dirs (subdirs are not ignored and will receive a trailing / as expected). To create the trailing / effect when you have a .htaccess *without* an external redirect, you need to manually tell it to correct the path for just that dir without the / (i.e. match ^/ , because a trailing / causes mod_rewrite to use an absolute filesystem path instead).
- 04:21 AM Revision 8482: web/.htaccess: moved auto-add trailing / to after don't rewrite existing dirs, so that the path will automatically not be rewritten if it contains a trailing / and is a dir
- 04:18 AM Revision 8481: web/.htaccess: don't rewrite existing paths: only prevent existing dirs *with trailing /* from being rewritten, because dirs without trailing / may be redirected
- 01:31 AM Revision 8480: removed web/**/.htaccess whose only purpose was to auto-add the trailing / (which is now handled by the top-level .htaccess)
- 01:30 AM Revision 8479: web/**/.htaccess: removed no longer needed auto-add of trailing / , which is now implemented for all dirs by the top-level .htaccess
- 01:27 AM Revision 8478: web/.htaccess: auto-add trailing / if not external redirect: added test if `%{REQUEST_FILENAME} -d` in addition to checking %{LA-F:REQUEST_FILENAME}, because there are some cases (e.g. subdirs that are symlinked outside the DOCUMENT_ROOT) where LA-F:REQUEST_FILENAME for some reason appends /home to the REQUEST_FILENAME, but the current REQUEST_FILENAME is fortunately already a dir and can be checked directly
- 01:07 AM Revision 8477: web/.htaccess: auto-add trailing / if not external redirect: removed no longer applicable comment that dir without trailing / is provided as an absolute filesystem path, because this is only true in inner .htaccess files for their top-level dirs
- 01:01 AM Revision 8476: bugfix: web/.htaccess: auto-add trailing / if not external redirect: match any path that does not end in / (and satisfies the -d lookahead), rather than only absolute filesystem paths, which are only applicable in inner .htaccess files (and even then only for the outermost dir they control) but not in the top-level .htaccess file. Note that the inner .htaccess files can still use ^/ to match *their own* dir, just not their *subdirs*, without a trailing / .
- 12:25 AM Revision 8475: bugfix: web/.htaccess: auto-add trailing / if not external redirect: replace with "%{REQUEST_URI}/" instead of the empty string (which gets auto-translated to have a trailing /), because the empty string only works in the top-level dir of the .htaccess file (it redirects to the RewriteBase, which ends in /). The entire REQUEST_URI is needed to do the auto-add in dirs which do not have their own auto-add trailing / rule. Also, using REQUEST_URI avoids the need to (sometimes) set RewriteBase for use by *relative* URLs in replacement strings, because this is an absolute URL instead. Setting RewriteBase manually is best avoided, because it hardcodes the URL path to the .htaccess file, which may change.
- 12:17 AM Revision 8474: bugfix: web/.htaccess: auto-add trailing / if not external redirect: use "%{LA-F:REQUEST_FILENAME} -d" instead of "$0 -F" so that the actual value of REQUEST_FILENAME tested, and $0 can also match strings which are not file paths. This is because only those no-trailing-/-paths *which did not match the RewriteBase* are provided to mod_rewrite as absolute filepaths, but RewriteBase is not an issue in the outermost .htaccess (because the RewriteBase is / , which is always matched). This means the method of testing for ^/ to match a no-trailing-/-path *will only work in inner .htaccess files* (where it is currently used extensively).
04/13/2013
- 05:47 PM Revision 8473: web/.htaccess: bugfix: moved auto-add trailing / if not external redirect to before don't rewrite existing paths because the trailing / also needs to be added on paths which *are* existing paths, but are just missing the trailing /
- 06:37 AM Revision 8472: web/.htaccess: auto-add trailing / if not external redirect (limited form of DirectorySlash). this allows URLs without trailing /, for dirs which have a special external redirect to the datasource's homepage when there is no trailing /, but still auto-adds the trailing / for all other dirs.
- 06:17 AM Revision 8471: README.TXT: Debugging: mod_rewrite: cached redirects issue: changed to limit the cache clearing to when a *DirectorySlash*-related redirect is changed, since only those redirects are permanent, not the RewriteRule redirects (unless you explicitly set them as permanent)
- 06:03 AM Revision 8470: README.TXT: Debugging: mod_rewrite: added tip that whenever you change a RewriteRule, you *must* clear your browser's cache to ensure that a cached redirect is not used instead (temporary redirects are not supposed to be cached, but sometimes Firefox caches them anyway)
- 04:41 AM Revision 8469: README.TXT: umask: Removed -S because this causes the new umask to be echoed, and is not needed even when setting a symbolic umask
- 04:18 AM Revision 8468: README.TXT: Datasource setup:: added step to use umask to prevent files from becoming web-accessible
- 04:16 AM Revision 8467: README.TXT: Testing: what to put in your .profile: added `umask -S ug=rwx,o=` to prevent files from becoming web-accessible
- 04:05 AM Revision 8466: README.TXT: fixed leading whitespace
- 02:46 AM Revision 8465: lib/util.run: run_cmd (): renamed to run_args_cmd to clarify that this runs only the command line args command, not any command. added doc comment to clarify this.
- 02:41 AM Revision 8464: lib/util.run: EXIT trap: use on_exit() function which calls run_cmd instead of having run_cmd be the handler, so that users can override this function to perform other commands (or no commands) after the script is read
- 02:28 AM Revision 8463: lib/util.run: echo_func (): use the caller's FUNCNAME via the $FUNCNAME[] array instead of requiring them to pass it in the function args as `echo_func "$FUNCNAME" "$@"`
- 02:11 AM Revision 8462: README.TXT: Maintenance: added steps to synchronize a Mac's settings with my testing machine's
- 02:10 AM Revision 8461: README.TXT: Maintenance: to synchronize vegbiendev, jupiter, and your local machine: moved swap=1 right before src= dest=, which it applies to. aligned whitespace of the various commands.
- 01:49 AM Revision 8460: README.TXT: Maintenance: steps to synchronize vegbiendev, jupiter, and your local machine: added steps to install dependencies
- 01:44 AM Revision 8459: README.TXT: Maintenance: Added steps to synchronize vegbiendev, jupiter, and your local machine when changes are made on vegbiendev
- 01:34 AM Revision 8458: README.TXT: converted spaces to tabs for easier readability in a whitespace-highlighting text editor, and so that pressing backspace deletes an indent rather than just a character (creating a misaligned indent)
04/11/2013
- 10:29 PM Revision 8457: Moved validation/ under planning/workflow/
- 10:28 PM Revision 8456: Moved planning/use_cases/ under planning/goals/
- 10:26 PM Revision 8455: Moved planning/milestones/ under planning/timeline/
- 10:23 PM Revision 8454: Added inputs/VegBIEN/planning shortcut to fs/planning
- 10:16 PM Revision 8453: web/.fs: symlink via datasources/VegBIEN/fs instead
- 10:14 PM Revision 8452: Added inputs/VegBIEN/fs symlink to /servers/vegbiendev/fs
- 05:56 PM Revision 8451: Added web/.gbif shortcut to datasources/GBIF
- 04:52 PM Revision 8450: web/**/.htaccess: Use [qsappend] flag with all RewriteRules to ensure that the original query string is never lost
- 04:30 PM Revision 8449: inputs/VegBank/.htaccess: Added RewriteCond to check that the REQUEST_FILENAME is not an existing dir, which is apparently needed to prevent the verify/ subdir from being interpreted as a redirect
- 02:45 PM Revision 8448: web/**/.htaccess: Removed preprocessing commands that need to run for every dir, because these are now inherited from the outermost .htaccess using InheritBefore. This allows the inner .htaccess files to focus just on the fallback rewrites, without needing lots of boilerplate code (except for the obligatory RewriteEngine/RewriteOptions/SetEnvIf preamble).
- 02:34 PM Revision 8447: web/.htaccess: handle DirectoryIndex subrequests when there is no DirectoryIndex, by deleting the /index filename. This ensures that any fallback redirect is not invoked, and instead the action corresponding to a trailing / (usually the display of a directory listing) is performed.
- 02:07 PM Revision 8446: web/**/.htaccess: Use InheritBefore RewriteOption so that the outermost dir's rules are always run first. This is necessary so that all the HTTP_HOST and dotpath handlers, etc. are applied before any fallback handlers, *without* needing to copy and paste each rule into *every* .htaccess file. (Note that the InheritBefore RewriteOption is only available in Apache 2.4.) The outermost .htaccess file, however, must *not* use InheritBefore because this would inherit rules from the global Apache config, which normally should not be run and which cause an infinite redirect loop.
- 01:56 PM Revision 8445: web/**/.htaccess: fallback rules which should not be inherited by subdirs: Filter using a RewriteCond on %{ENV:innermost_dir} so that the rule is only run if the innermost dir was this dir. (This is needed when using InheritBefore.)
- 01:36 PM Revision 8444: web/**/.htaccess: Added SetEnvIf directives to set the $innermost_dir env var, which is used by InheritBefore RewriteRule inheritance. You can easily populate the current dir name in jEdit by right-clicking the buffer tab and selecting Insert File Path (which requires the BufferTabs plugin), and then picking out the dir right before the .htaccess. If multiple dirs in the dir path have the same name (e.g. a/b/a/), add a distinguishing prefix to the inner dirs (this is rare).
04/10/2013
- 11:17 PM Revision 8443: web/.htaccess: Support rules being inherited by subdirs using InheritBefore, by setting an env var $innermost_dir that stores the name of the innermost subdir to contain an .htaccess file. This env var can then be tested for in a RewriteCond in rules that should only apply locally. This works because SetEnvIf processes directives from the outer dir to the inner dir, so that the inner dir's setting comes last and is the final value of $innermost_dir. Note that you must use SetEnvIf rather than SetEnv because "The internal environment variables set by [SetEnv] are set after most early request processing directives are run, such as access control and URI-to-filename mapping. If the environment variable you're setting is meant as input into this early phase of processing *such as the RewriteRule directive*, you should instead set the environment variable with SetEnvIf." [emphasis added] (http://httpd.apache.org/docs/current/mod/mod_setenvif.html#setenvif)
- 10:20 PM Revision 8442: web/.htaccess: don't rewrite existing paths: Changed match string to what is used in subdirs, so that if this rule is inherited, what is considered an existing path (i.e. a file or subdir) is restricted appropriately
- 10:18 PM Revision 8441: web/**/.htaccess: no-replacement RewriteRules: Removed the standard [discardpath,noescape] flags because they add unnecessary clutter and are not needed for this type of rule. Note that these flags are still required on regular RewriteRules which have a replacement string.
- 10:15 PM Revision 8440: web/**/.htaccess: no-replacement RewriteRules: Use just ^ as the match string because there is no need to actually match characters. This also makes it easy to visually tell what is a no-replacement RewriteRule vs. a RewriteRule that matches the entire path.
- 10:11 PM Revision 8439: web/.htaccess: Renamed $REQUEST_URI_NO_SLASH to $REQUEST_URI_no_/ to take advantage of (some) special chars being allowed in env var names
- 09:42 PM Revision 8438: Added web/people/
- 09:08 PM Revision 8437: web/.htaccess: fallback redirect: Use new VegCore/ instead of specifying the full wiki URL
- 09:07 PM Revision 8436: schemas/VegCore/.htaccess: Fixed bug where need to issue explicit redirect because path contains a fragment (#...), which must be sent to the browser
- 09:03 PM Revision 8435: Added schemas/VegCore/.htaccess
- 08:57 PM Revision 8434: Added web/.vegcore shortcut to exchange_schemas/VegCore
- 08:34 PM Revision 8433: Added root .htaccess (accessed via web/servers/vegbiendev/fs or one of its shortcuts)
- 08:28 PM Revision 8432: web/.htaccess: translate subdomain to path: Use new REQUEST_URI_NO_SLASH to avoid adding an automatic trailing / when a subdir was reached as a subdomain without a path (e.g. VegBank.vegpath.org), which should be equivalent to going to the subdir's homepage (vegpath.org/VegBank) rather than to its directory listing (vegpath.org/VegBank/ ; note the trailing /)
- 08:25 PM Revision 8431: web/.htaccess: set REQUEST_URI_NO_SLASH for use by translating subdomain to path
- 08:19 PM Revision 8430: Added web/.fs symlink to .vegbiendev/fs
- 01:53 PM Revision 8429: web/svn-web, wiki: Made them hidden so they wouldn't appear in the dir listing (which should not contain shortcuts to inner paths, as these would create significant clutter). web/svn unfortunately can't be made hidden because there is already a .svn dir.
- 01:47 PM Revision 8428: Added web/.nimoy, .vegbiendev shortcuts
- 01:44 PM Revision 8427: web/DwC, VegX: Replaced with versions in exchange_schemas/
- 01:39 PM Revision 8426: Moved web/TCS/ to schemas/
- 01:36 PM Revision 8425: Added web/exchange_schemas/ symlink
- 01:35 PM Revision 8424: Removed web/exchange_schemas/ in preparation for making it a symlink
- 01:34 PM Revision 8423: Added schemas/.htaccess
- 01:33 PM Revision 8422: Added web/exchange_schemas/
- 01:28 PM Revision 8421: Merged web/DwC,VegX with the corresponding dir in schemas/
- 01:19 PM Revision 8420: Moved web/BIEN2, IH into aggregators/
- 01:18 PM Revision 8419: Moved web/BIEN2, IH into aggregators/
- 01:09 PM Revision 8418: Added web/aggregators, primary_databases with symlinks to the applicable datasources
- 01:04 PM Revision 8417: web/.tnrs: Updated to point to datasources/.TNRS/
- 01:01 PM Revision 8416: Removed web/TNRS/ in preparation for replacing it with a symlink
- 12:59 PM Revision 8415: Added inputs/.TNRS/.htaccess
- 12:13 PM Revision 8414: web/.redmine/: Updated for new VegBIEN/ location
- 12:12 PM Revision 8413: web/.htaccess: Use new %{REQUEST_SCHEME} to construct self-refential URLs
- 10:14 AM Revision 8412: web/VegBIEN/: Moved to datasources/
- 10:07 AM Revision 8411: Added inputs/.htaccess, which parses dotpaths and adds trailing / when datasources/ subdir is listed. (DirectorySlash cannot be used for this because it would affect all subdirs, too, which must not have DirectorySlash on. Turning DirectorySlash off in each subdir would not work, because the / is added *as soon as* a valid filesystem path is formed, even if the inner dir would not have added the /.)
- 09:48 AM Revision 8410: web/**/.htaccess: internal redirects: Use ?&$0 instead of just ?$0 to prevent the query string from being reinterpreted as a dotpath by the destination dir
- 09:45 AM Revision 8409: web/VegBIEN/Redmine/*/.htaccess: Use relative URL paths. This requires adding a RewriteBase directive because these dirs can be reached by symlinks.
- 09:40 AM Revision 8408: inputs/SALVIAS/.htaccess: Removed test RedirectMatch directive
- 09:38 AM Revision 8407: web/**/.htaccess: RewriteOptions: Redirect dir paths without the trailing / to the datasource's homepage, and let dir paths with the trailing / point to the directory index. This is the opposite of the way it was before, and is more intuitive because the trailing / indicates a directory listing, while the trailing / would generally be omitted when linking to the datasource itself.
- 07:49 AM Revision 8406: web/**/.htaccess: handle DirectoryIndex subrequests, which append "index" to the dir
- 06:15 AM Revision 8405: web/**/.htaccess: RewriteOptions: Removed inherit because if the destination is not found in the current dir, a 404 should be returned instead of trying to invoke the parent dir's fallback handler (which applies only to paths in the parent dir). Ideally, we would use InheritBefore to get the subdomain preprocessing, etc., but the parent dir's fallback handler would still need to be turned off somehow when the rule was being run by a subdir.
- 05:59 AM Revision 8404: web/.htaccess: Only forward specific terms (subpaths) to VegCore, not also the dir index itself
- 05:15 AM Revision 8403: web/**/.htaccess: RewriteOptions: Added AllowNoSlash (available in Apache 2.4) so dir paths without the trailing / can be parsed by mod_rewrite
- 05:12 AM Revision 8402: web/.htaccess: Turned off DirectorySlash so that the dir without the trailing / can have a separate meaning (e.g. as the home page of a datasource, rather than a listing of the short URLs available for it)
- 03:33 AM Revision 8401: web/main.conf: <Directory .>: Added `Require all granted`, which is needed by Apache 2.4 to prevent 403 Forbidden errors. This change breaks compatibility with older versions of Apache, but is unfortunately required by 2.4.
- 03:29 AM Revision 8400: web/**/.htaccess: parse dotpath in the query string: Use just dotpath instead of dotpath.php because the extension is added automatically by MultiViews
- 03:28 AM Revision 8399: web/**/.htaccess: parse dotpath in the query string: Remove any index.* suffix, which is added by MultiViews in Apache 2.4
- 03:28 AM Revision 8398: web/main.conf: Updated the path of the <Directory> directive for the DocumentRoot
- 03:21 AM Revision 8397: web/.htaccess: Set Options +Indexes because this isn't the default in Apache 2.4
- 01:46 AM Revision 8396: web/.htaccess: DirectoryIndex: Use index instead of hardcoding index.php because MultiViews will now add the appropriate extension automatically
- 01:44 AM Revision 8395: web/.htaccess: Turn on MultiViews to allow auto-adding of the file extension
- 12:15 AM Revision 8394: inputs/**/.htaccess: Use a direct filesystem path rather than going through /svn (the Redmine web interface) because this loads much faster, and avoids needing to manually specify svn-web instead of svn for files that should be displayed in the browser instead of downloaded. It also adds support for files not in svn. Note that the use of a relative RewriteRule replacement requires RewriteBase when the directory is not under the document root, in order to construct the correct self-referential URL (http://httpd.apache.org/docs/current/mod/mod_rewrite.html#rewritebase).
04/09/2013
- 10:59 PM Revision 8393: inputs/**/.htaccess: Removed no longer needed trailing / in paths
- 10:55 PM Revision 8392: inputs/CTFS/*/.htaccess: Use new direct shortcuts to /Redmine subtrees
- 10:47 PM Revision 8391: Added wiki shortcut to Redmine/wiki
- 10:43 PM Revision 8390: web/.htaccess: use separate lowercase version when available: Support paths without a trailing /
- 10:41 PM Revision 8389: web/.htaccess: use separate lowercase version when available: Fixed bug where need to use [last] flag, in order to rewrite in the new subdir instead of applying the rest of this dir's rules (which would forward to VegCore)
- 10:11 PM Revision 8388: web/: datasources indexed in VegPath: Link to the corresponding inputs/ dir instead, which now stores the data and the short URLs in one place
- 10:10 PM Revision 8387: web/.htaccess: Use %{REQUEST_URI} ("The path component of the requested URI" <http://httpd.apache.org/docs/current/mod/mod_rewrite.html#rewritecond>) instead of /$0, in order to preserve whether or not the path had a trailing slash. This also ensures that the rule functions correctly if it is invoked in a subdir context, which may occur for the "remove www subdomain" rule.
- 10:01 PM Revision 8386: web/.htaccess: Removed "don't redirect subdir paths" because this is now handled by "don't rewrite existing paths" as described in r8375
- 09:48 PM Revision 8385: web/: datasources indexed in VegPath: Added .htaccess files to corresponding inputs/ dir so the short URLs can also be accessed via VegBIEN's own dir for the datasource
- 09:36 PM Revision 8384: inputs/input.Makefile: Removed .PRECIOUS from %/header.csv, %/map.csv so that these scripts are deleted on error. This is useful for the runscripts and for non-data dirs whose header.csv cannot be made.
- 09:33 PM Revision 8383: inputs/input.Makefile: SVN: Only run %/add on subdirs with visible (non-hidden) files. Subdirs with only hidden files (e.g. .htaccess) are assumed to be non-data dirs.
- 09:23 PM Revision 8382: inputs/input.Makefile: Staging tables installation: %/install: $(logInstall): Only use log file if log dir exists, to support non-data dirs
- 09:13 PM Revision 8381: inputs/input.Makefile: Staging tables installation: %/install: ignore errors in $(exportHeader) and $(cleanup) if the table does not exist (i.e. a non-data dir)
- 09:11 PM Revision 8380: inputs/input.Makefile: Staging tables installation: %/install: Don't run $(import_install_) for empty dirs because there is no data to import
- 04:37 PM Revision 8379: lib/sql.py: parse_exception(): typed_name_re: Fixed bug where need to require the "" around the table/column name, because otherwise, the regexp will try to match as few characters as possible, causing it to match only the first letter of the name
- 04:13 PM Revision 8378: web/**/.htaccess: Removed "handle DirectoryIndex" rule, which does not appear to be needed with the new dotpath format
- 04:10 PM Revision 8377: web/VegBank/.htaccess: RewriteRule: Fixed bug where need to match tables by themselves as well as followed by columns
- 03:59 PM Revision 8376: web/**/.htaccess: Added "don't rewrite existing paths" and "handle DirectoryIndex" rules to all .htaccess files, not just those for dirs that contain subdirs, to facilitate linking directly to files in the filesystem without first needing to retrofit the applicable .htaccess file.
- 03:42 PM Revision 8375: web/**/.htaccess: Restrict redirection only of fully-existing paths, rather than of any path whose *subdir* exists, in order to avoid having to hardcode the absolute path to the directory containing the .htaccess file. This more restrictive approach still works because if the subdir exists and contains an .htaccess file, this (outer) .htaccess file's RewriteRules will be ignored completely until the inner .htaccess file's rules have been run, so that any subdir redirects will happen first. This "hole-punch" effect causes an existing filesystem path to always take priority over a virtual path (i.e. a redirect) defined in an outer .htaccess file.
- 02:57 PM Revision 8374: Added web/datasources/ symlink to inputs/
- 02:49 PM Revision 8373: web/servers/vegbiendev/fs: Updated symlink for new web/ dir nesting level
- 02:41 PM Revision 8372: Deleted no longer used www/
- 02:36 PM Revision 8371: Renamed www/ back to web/
- 02:34 PM Revision 8370: Removed www/logs/ because it was actually successfully moved into main/ (but some of its files weren't)
- 02:33 PM Revision 8369: Added www/logs/
- 02:24 PM Revision 8368: web/: Moved auxiliary files into the main/ subdir in preparation for having just the web/ dir. Renamed web/ to www/ so it can be replaced with web/main/.
- 02:09 PM Revision 8367: web/main/servers/vegbiendev/: Split it into db and fs forks, with db being the default. The fs fork links directly to /home/bien/svn on vegbiendev, which makes world-readable files directly web-accessible. (Permissions on /home/bien/svn and subdirs have been checked to ensure that private files are not world-readable.)
- 01:33 PM Revision 8366: Added web/main/IH/db/
- 01:26 PM Revision 8365: web/main/index.php: Updated fragment redirect for new dotpath format (using ? instead of a relative path)
- 01:20 PM Revision 8364: web/main/index.php: Updated path templates for new dotpath format (using ? instead of /)
- 01:06 PM Revision 8363: web/main/**/.htaccess: Support dotpaths in the query string instead of in the path, so that non-dotpath paths don't need to be suffixed with / to prevent their filenames from being interpreted as dotpaths. Putting dotpaths in the query string still requires only one character between the host and the path, but it's ? instead of / . ? is in many ways more natural, because the dotpath is a non-filesystem string to be parsed rather than something that's already a filesystem path. This change also avoids the need to strip trailing /s in many RewriteRules, because the dotpath mechanism is no longer appending them.
- 01:00 PM Revision 8362: Added web/main/dotpath.php, which parses any dotpath in the query string
- 12:58 PM Revision 8361: web/main/util.php: Added coalesce()
- 10:18 AM Revision 8360: web/main/svn*: Fixed symlinks to use .redmine instead of Redmine
- 09:37 AM Revision 8359: web/main/IH/.htaccess: Fixed whitespace
- 09:36 AM Revision 8358: web/main/IH/.htaccess: RewriteRule: Fixed bug where need to \-escape % because it's a special character in the replacement string (it references a regexp group matched by the last RewriteCond)
- 09:28 AM Revision 8357: Removed web/main/Redmine symlink so that it isn't listed in the directory listing as a visible (non-hidden) file. (There is already a .redmine symlink which matches all case variations via web/main/.htaccess logic.)
- 09:22 AM Revision 8356: Added web/main/svn* symlinks to VegBIEN/Redmine/svn*
- 09:18 AM Revision 8355: web/main/svn*/: Removed in preparation for replacing with symlinks, which will work now that absolute paths are used where needed
- 09:15 AM Revision 8354: web/main/**/.htaccess: internal redirects using relative paths with .. : Use absolute paths instead so that when the directory is reached through a symlink, the redirect will still work. Note that relative paths without .. do not need to be absolute paths because the subtree structure is the same (just the parent dirs are different).
- 08:01 AM Revision 8353: schemas/VegCore/VegCore.ERD.mwb: Regenerated exports
- 07:26 AM Revision 8352: schemas/VegCore/VegCore.ERD.mwb legend images: resized to medium-sized squares because apparently MySQL Workbench can't handle rectangular or very large images (it distorts them or resets them to their original size when the diagram is reloaded)
- 06:08 AM Revision 8351: schemas/VegCore/VegCore.ERD.mwb: Legend: Added IS-A, HAS-A, and "inherits from record" entries
- 06:08 AM Revision 8350: Added lib/MySQL_Workbench/connector.png, solid_line.png, dotted_line.png
- 05:00 AM Revision 8349: schemas/VegCore/VegCore.ERD.mwb: Fixed lines
- 04:59 AM Revision 8348: schemas/VegCore/VegCore.ERD.mwb: Renamed placename to named_place to match the table name
- 04:55 AM Revision 8347: schemas/VegCore/VegCore.ERD.mwb: Renamed measurement to trait because this is the more commonly used name for the entity
- 04:47 AM Revision 8346: schemas/VegCore/VegCore.ERD.mwb: Added VegCore logo
- 04:46 AM Revision 8345: Added schemas/VegCore/VegCore.logo.svg
- 04:09 AM Revision 8344: schemas/VegCore/VegCore.ERD.mwb: taxon: Added recursive parent fkey for (optionally) storing taxon hierarchies
- 03:42 AM Revision 8343: schemas/VegCore/VegCore.ERD.mwb: Repositioned taxon_determination
- 03:39 AM Revision 8342: schemas/VegCore/VegCore.ERD.mwb: Moved taxon next to qualified_taxon instead of above it, because inheritance (IS-A) is shown vertically while HAS-A is shown horizontally
- 03:25 AM Revision 8341: schemas/VegCore/VegCore.ERD.mwb: Populated legend
04/05/2013
- 12:23 AM Revision 8340: schemas/VegCore/VegCore.ERD.mwb: Added exports
- 12:23 AM Revision 8339: schemas/VegCore/VegCore.ERD.mwb: Fixed lines and settings for the Linux MySQL Workbench
04/04/2013
- 10:01 PM Revision 8338: schemas/VegCore/VegCore.ERD.mwb: Added table colors
- 10:01 PM Revision 8337: Removed backup file schemas/VegCore/VegCore.ERD.mwb.bak
- 09:48 PM Revision 8336: Added schemas/VegCore/VegCore.ERD.mwb, VegCore.my.sql with first VegCore ERD and MySQL schema. All tables are in the ERD, but contain only pkey and fkey columns.
- 09:52 AM Revision 8335: lib/sql.py: mk_select(): using subset function: Turn off enable_sort (within the transaction) to avoid unwanted slow sorts. This change (along with the subset functions themselves) should significantly reduce the long FIA.occurrence_all table subset time (~8 hours altogether) and with it the total import time, which had more than doubled as a result of the FIA refresh. Note that this issue would have been even more pronounced for larger datasets, such as the GBIF refresh, which would have taken ~2.5 days longer (400 million rows * ~30% are plants * (FIA: ~8 hours/16.7 million rows) * 1 day/24 hours).
- 09:30 AM Revision 8334: lib/sql.py: mk_select(): Use subset function when it's available for fast querying at large OFFSET values
- 09:29 AM Revision 8333: lib/sql.py: Added has_subset_func()
- 08:48 AM Revision 8332: inputs/FIA/occurrence_all/import: Run mk_subset_by_row_num_func() to make the subset functions available for fast querying at large OFFSET values
- 08:43 AM Revision 8331: schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where need to add 1 to the 0-based offset_ to get the 1-based row_num (which is usually a serial column)
- 08:38 AM Revision 8330: schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where need to subtract 1 from the end row_num because BETWEEN limits are inclusive of the bounds
- 08:33 AM Revision 8329: schemas/util.sql: mk_subset_by_row_num_func(): regular subset function: Fixed bug where also need to COALESCE() offset_ to 0 when it's added to the limit_
- 08:20 AM Revision 8328: schemas/util.sql: mk_subset_by_row_num_func(): subset function which turns off enable_sort: Fixed bug where need to pass ($2, $3) instead of ($1, $2) to the regular subset function
- 08:14 AM Revision 8327: inputs/FIA/occurrence_all/import: Added occurrence_all-row_num column for use with mk_subset_by_row_num_func()
- 08:12 AM Revision 8326: schemas/util.sql: mk_subset_by_row_num_func(): Also create subset function which turns off enable_sort. This is used for limit values greater than ~100,000 to avoid unwanted slow sorts. The regular subset function is still needed to work with EXPLAIN, so that it produces expanded output instead of just a function scan.
- 07:27 AM Revision 8325: schemas/util.sql: Added mk_subset_by_row_num_func()
- 07:10 AM Revision 8324: schemas/util.sql: Added type_qual_name()
- 06:33 AM Revision 8323: schemas/util.sql: force_update_view(): Fixed bug where also need to drop view for "cannot change name of view column" errors
- 05:24 AM Revision 8322: inputs/FIA/occurrence_all/import: Use new force_update_view(), which only drops the view if its columns have changed and otherwise just uses CREATE OR REPLACE VIEW, rather than always first running DROP VIEW IF EXISTS
- 05:20 AM Revision 8321: schemas/util.sql: Added force_update_view()
- 04:23 AM Revision 8320: bin/make_analytical_db: Commented out export_analytical_db because we are not yet using the analytical DB in MySQL, and it doesn't make sense to generate a large, unused CSV export each time
- 04:19 AM Revision 8319: bin/export_analytical_db: Replaced analytical_aggregate with analytical_stem
- 03:53 AM Revision 8318: inputs/FIA/occurrence_all/: Updated header.csv for new column order
- 03:40 AM Revision 8317: inputs/FIA/occurrence_all/import: Use directional joins (LEFT/RIGHT JOIN) instead of inner joins to ensure that the PostgreSQL query planner always joins starting with the TREE table. Note that the directional joins are now needed for a different reason than when they were initially added, which had been to avoid slow sorts. The sorts (at least for LIMIT-only queries) went away when small tables such as COUNTY and REF_UNIT were added to the joins.
- 01:16 AM Revision 8316: inputs/FIA/*/map.csv: Changed newlines between table and field name to - because the newlines mess up the flow of queries and also break pgAdmin's display of EXPLAIN output. The - was chosen because it's a non-whitespace character that linewraps in browsers, phpPgAdmin, and Google spreadsheets (although unfortunately not in pgAdmin). It is better than space because you can set a text editor to treat it as a word character, allowing the entire column name (<table>-<field>) to be selected by double-clicking it.
04/03/2013
- 09:55 PM Revision 8315: Added planning/workflow/normalized_vs_denormalized/denormalized.generic_standardizations.png (a slide from Brad's bien3_architecture_denormalized.pptx PowerPoint), which shows the staging table preprocessing particularly well
- 09:45 PM Revision 8314: README.TXT: Full database import: record the import times in inputs/import.stats.xls: Added instructions for what to do if the rightmost imports start getting truncated due to the 255-column limit in spreadsheets. (This will occur in 8 imports.)
- 09:32 PM Revision 8313: inputs/import.stats.xls: Removed the previous imports from the current tab because they are also in the 2012-6~9 tab, and should not be in two places
- 09:28 PM Revision 8312: inputs/import.stats.xls: Updated import times. MO and FIA have been refreshed.
04/02/2013
- 04:17 PM Revision 8311: Removed no longer needed inputs/GBIF/import. Use ./run instead.
- 04:17 PM Revision 8310: Removed no longer needed inputs/GBIF/_MySQL/import. Use ./run instead.
- 04:16 PM Revision 8309: inputs/GBIF/_MySQL/run: import: Run make directly instead of via ./import
- 04:15 PM Revision 8308: inputs/GBIF/_MySQL/run: Use new import.run, which defines all()
- 04:06 PM Revision 8307: Added planning/workflow/normalized_vs_denormalized/bien3_architecture_(de)normalized.pptx
- 03:57 PM Revision 8306: Added planning/workflow/normalized_vs_denormalized/BIEN-modArch-Dec2010 NS-SBD 1.4.ppt.url
- 03:50 PM Revision 8305: planning/workflow/: Moved normalized vs. denormalized files to separate normalized_vs_denormalized/ subfolder
- 03:21 PM Revision 8304: Regenerated inputs/ACAD/Specimen/logs/steps.by_col.log.sql
- 03:15 PM Revision 8303: inputs/GBIF/raw_occurrence_record/run: Override MySQL_export() so $filter can be customized
- 03:13 PM Revision 8302: inputs/GBIF/table.run: import(): Updated for lib/table.run template changes
- 03:09 PM Revision 8301: lib/table.run: template: import(): Also pass "$@" to superclass method
- 03:08 PM Revision 8300: lib/table.run: template: Use "$FUNCNAME" instead of hardcoding import
- 03:02 PM Revision 8299: Added inputs/GBIF/MySQL_export, used by ./table.run
- 02:57 PM Revision 8298: lib/util.run: echo_func: Fixed bug where need to use BASH_LINENO[0] for the line #s to match up with the files. For some reason the required array indexes for BASH_SOURCE (1) and BASH_LINENO (0) differ by one.
- 02:51 PM Revision 8297: inputs/GBIF/run: Use new import.run, which defines all()
- 02:51 PM Revision 8296: lib/table.run: Use new import.run, which defines all()
- 02:49 PM Revision 8295: Added lib/import.run
- 02:48 PM Revision 8294: lib/util.run: echo_func: Include the line # of the function to make it easier to find where the code being run is
- 02:32 PM Revision 8293: lib/table.run: Added all (default target)
- 02:26 PM Revision 8292: lib/util.run: run_cmd: If bash exited with an error, don't run the "$@" command. This test is necessary because `trap run_cmd EXIT` will run run_cmd as the result of *any* exit from the shell, including an error.
- 02:21 PM Revision 8291: *run: Use -e option to bash on the #! line instead of separate `set -o errexit` line so that there is no issue with the `set -o errexit` line getting separated from the #! line (errexit is required for the scripts to work properly)
- 02:09 PM Revision 8290: lib/util.run: run_cmd: When no command specified, default to running the `all` command, just like make
- 02:07 PM Revision 8289: lib/util.run: Run run_cmd at shell exit (using trap) instead of requiring every runscript to have `run_cmd ` at the end of it
- 01:49 PM Revision 8288: Added inputs/GBIF/run
- 01:48 PM Revision 8287: Added inputs/GBIF/raw_occurrence_record/run
- 01:47 PM Revision 8286: Added inputs/GBIF/table.run
- 01:45 PM Revision 8285: Added inputs/GBIF/_MySQL/run
- 01:42 PM Revision 8284: lib/util.run: fwd: Check that $subdirs is defined. Added $subdirs to usage.
- 01:39 PM Revision 8283: lib/util.run: fwd: Added usage
- 01:32 PM Revision 8282: lib/table.run: Switched from echo_run to echo_func
- 01:16 PM Revision 8281: lib/util.run: run_cmd: Echo the command being run, including the top-level run script. This is in addition to the echoing of the command in the function itself (using echo_func), which provides *both* the runscript that was run *and* the file where the invoked command was actually located (which may be different due to includes).
- 01:12 PM Revision 8280: lib/util.run: Echo the command at the beginning of each function using new echo_func, instead of having to type echo_run before every call to a function. Note that because echo_func uses BASH_SOURCE, the path to the file containing the function will be included in the debug message, which greatly facilitates locating which file a command is in.
- 01:08 PM Revision 8279: lib/util.run: Added echo_func
- 12:50 PM Revision 8278: lib/util.run: Added echo_cmd and use it in echo_run
- 12:46 PM Revision 8277: lib/util.run: echo_cmd(): Renamed to echo_run for clarity, because it also runs the command
- 12:39 PM Revision 8276: lib/util.run: Added inline_make()
- 12:39 PM Revision 8275: lib/util.run: Added echo_stdin()
- 12:30 PM Revision 8274: bin/my2pg_export: Put --password first because it's an authentication-related option
- 10:52 AM Revision 8273: Added lib/table.run, which includes the commands in import.sh but uses run scripts to allow running commands other than just import. (For example, map_table or postprocess can be run separately. Uninstall-related commands which would not belong in an import script can also be added, because import is only one of many commands a run script can offer.)
- 10:35 AM Revision 8272: Added lib/util.run with general functions and template for run scripts (a bash-based replacement for make). Unlike make, run scripts support full bash functionality including multiline commands. The run script template also includes syntax for various kinds of relative includes in bash.
- 12:03 AM Revision 8271: lib/common.Makefile: Added $(require_var)
04/01/2013
- 10:42 PM Revision 8270: bin/publish_analytical_db: Fixed bug where need to remove `ESCAPED BY '"'` because this would causing " followed by an escape sequence char to be interpreted specially (e.g. "n -> \n). MySQL automatically takes care of quote doubling when you specify `FIELDS OPTIONALLY ENCLOSED BY`.
- 10:13 PM Revision 8269: lib/common.Makefile: Compression: Added `%:: %.gz`, `%.gz: %`
- 08:07 PM Revision 8268: planning/workflow/import_process_comparison.odg: Moved "staging tables" under the method labels to reduce empty space
- 07:52 PM Revision 8267: planning/workflow/import_process_comparison.odg: Removed margins so the labels would align with the page margin on the Import process wiki page <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Import_process>
- 07:32 PM Revision 8266: Added planning/workflow/import_process_comparison.odg and .png export
- 06:12 PM Revision 8265: lib/db_xml.py: put_table(): Fixed bug where command to advance start to fetch next set was unintentionally deleted when removing the is_view check
- 06:11 PM Revision 8264: inputs/UNCC/Specimen/new_terms.csv: Updated for updated VegCore vocab
- 03:53 PM Revision 8263: inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.data.sql.md5: Regenerated after appending agent table to GBIFPortalDB-2013-02-20.data.sql
- 03:51 PM Revision 8262: Added inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.data.sql.gz.md5
03/28/2013
- 08:16 PM Revision 8261: Added inputs/GBIF/raw_occurrence_record/ from refresh
- 08:07 PM Revision 8260: inputs/GBIF/MySQL.schema.sql: Regenerated with inline enum type translated to CHECK constraint
- 08:07 PM Revision 8259: bin/my2pg: Translate inline enum type to CHECK constraint
- 07:43 PM Revision 8258: Added inputs/GBIF/**/MySQL.schema.sql
- 07:42 PM Revision 8257: Added inputs/GBIF/_MySQL/MySQL.*.sql.make
- 07:36 PM Revision 8256: inputs/FIA/: Archived no longer used subdirs from BIEN2 export
- 07:29 PM Revision 8255: inputs/FIA/: Archived no longer used subdirs from BIEN2 export
- 07:22 PM Revision 8254: inputs/input.Makefile: SVN: add: Removed Source/map.csv prerequisite because it is not related to adding unversioned files in the dir. It was originally a prerequisite in order to auto-create it when the datasource dir is first created, but the map.csv recipe does not currently create metadata-only map.csvs. In the future, metadata-only map.csvs will be replaced with constant columns added to the applicable tables.
- 07:19 PM Revision 8253: Added inputs/FIA/_archive
- 07:19 PM Revision 8252: inputs/input.Makefile: %/map.csv: Fixed bug where can only make header.csv if map.csv does not exist, because some subdirs are metadata-only and don't have a corresponding DB table
- 07:02 PM Revision 8251: README.TXT: Datasource setup: Install the staging tables: For a MySQL .sql export: Documented which password to use at each of the two password prompts my2pg_export will give you. You could also embed the value of the 2nd prompt in the _MySQL/*.make file using `--password="$(cat path/to/config/bien_password)"`.
- 06:56 PM Revision 8250: README.TXT: Datasource setup: Install the staging tables: Removed requirement that `make inputs/<datasrc>/reinstall quiet=1 &` be run on vegbiendev for MySQL .sql exports, because the hostname is now set to vegbiendev instead of localhost
- 06:38 PM Revision 8249: inputs/input.Makefile: sql/install: Use psql_script_vegbien instead of $(psqlNoSearchPath) (which uses psql_verbose_vegbien) because the insert statement for each data row should not be echoed
- 06:14 PM Revision 8248: inputs/FIA/occurrence_all/import: Run remake_VegBIEN_mappings at end to keep mappings to next stage of import process up to date
- 06:14 PM Revision 8247: inputs/FIA/occurrence_all/: Accepted new test output
- 06:13 PM Revision 8246: lib/import.sh: remake_VegBIEN_mappings(): Also remake VegBIEN.csv and test.xml.ref use `make test`
- 06:11 PM Revision 8245: lib/import.sh: Added remake_VegBIEN_mappings()
- 06:10 PM Revision 8244: inputs/input.Makefile: %/map.csv: make $*/header.csv first in case it doesn't exist (e.g. if it has been deleted so that it will be remade)
- 06:07 PM Revision 8243: inputs/FIA/occurrence_all/map.csv: Regenerated using new input table mappings
- 05:47 PM Revision 8242: lib/import.sh: Added make() and use it instead of the full make command
- 05:23 PM Revision 8241: inputs/input.Makefile: postprocess: Use %/postprocess instead of %/postprocess.sql/run so $*/import is also run
- 05:21 PM Revision 8240: inputs/FIA/: Ran inputs/FIA/import. This maps to VegCore's commonName.
- 05:19 PM Revision 8239: inputs/input.Makefile: %/postprocess: Also run the $*/import script, if it exists. Note that this is not the same as the %/import make target.
- 05:12 PM Revision 8238: inputs/input.Makefile: %/postprocess.sql/run: Factored out into separate %/postprocess command, which can eventually also perform other actions
- 04:59 PM Revision 8237: inputs/FIA/PLOT/map.csv: ELEV: Remapped to elevation_ft, assuming units based on the actual elevation of the region for a sample plot record
- 04:27 PM Revision 8236: inputs/VegBank/taxonobservation_/map.csv: Mapped int_currplantcommon to vernacularName
- 04:25 PM Revision 8235: mappings/VegCore.htm: Renamed salvias_plots table plotMetadata to PlotMetadata because of SALVIAS refresh on nimoy
- 04:18 PM Revision 8234: mappings/VegCore.htm: Regenerated from wiki. Added flower, fruit, commonName.
- 03:37 PM Revision 8233: mappings/Makefile: $(vocab); bin/redmine_synonyms: Support crossed out (deprecated) terms
- 03:24 PM Revision 8232: README.TXT: Maintenance: VegCore data dictionary: Added steps to update the data dictionary's Tables section if necessary
- 02:14 PM Revision 8231: inputs/GBIF/_MySQL/Makefile: %.data.sql: Added agent table
- 01:18 PM Revision 8230: Added inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.data.sql.md5
- 01:11 PM Revision 8229: Added inputs/GBIF/_MySQL/GBIFPortalDB-2013-02-20.schema.sql
- 11:02 AM Revision 8228: Added web/main/svn*/, now using .htaccess to forward to Redmine/*
- 10:55 AM Revision 8227: Removed web/main/svn, svn-web symlinks because they need to be .htaccess-es in order for the relative mod_rewrite commands to work correctly
- 10:50 AM Revision 8226: Added web/main/svn, svn-web symlinks to Redmine/* for shorter URLs
- 10:49 AM Revision 8225: Added web/main/Redmine/svn-web/
- 08:28 AM Revision 8224: inputs/GBIF/: Added scripts for subsetting refresh
- 12:24 AM Revision 8223: lib/sql.py: table_order_by(): Documented that it returns None if table is a view, because table_cluster_on() would return None. This is necessary for inputs/FIA/occurrence_all/ sorting to work correctly, because specifying a manual sort order would prevent the query planner from just using fast nested loop joins and instead cause it to perform a slow sort. (This appears to be a bug in the query planner, because when the column list specified matches the joined-on indexes, there should be no need for post-nested loop re-sorting.)
- 12:20 AM Revision 8222: inputs/FIA/occurrence_all/test.xml.ref: Updated inserted row count for new row sort order
- 12:19 AM Revision 8221: lib/db_xml.py: put_table(): Fixed bug where also need to advance start to fetch next set when table is a view, because the views that are now being used with the import (inputs/FIA/occurrence_all/) are static rather than dynamic and do not return different rows after the previous set of rows has been imported
03/27/2013
- 11:43 PM Revision 8220: inputs/FIA/occurrence_all/import: Removed no longer applicable comment that directional joins are needed for PostgreSQL query planner to avoid slow sorts
- 11:40 PM Revision 8219: inputs/FIA/TREE/import: Reclustered table by TREE.parent path index, to facilitate path-order joins
- 11:39 PM Revision 8218: inputs/FIA/occurrence_all/import: Changed all RIGHT JOINs to inner joins so that tables would be joined in path order (i.e. general->specific). This optimizes the incremental joins so that the small tables are joined to each other before being joined to the large tables, rather than each row of the large tables being looked up in the small tables. This effect may not be noticeable for small LIMIT values, but would become apparent for large LIMIT values, such as the 1-million-row partitions used by db_xml.put_table() for column-based import. Note that inner joins used to cause the query planner to produce incorrect results containing slow sorts, but now this appears to no longer be an issue, perhaps because the result is not sorted by the TREE.ID index (which is not in the same order as the path indexes *.unique, *.parent).
- 10:46 PM Revision 8217: inputs/FIA/occurrence_all/import: Removed trailing whitespace
- 10:30 PM Revision 8216: Removed unused inputs/FIA/COND_unique/. Use COND instead.
- 09:52 PM Revision 8215: inputs/FIA/import: Use `set -o errexit` instead of putting ` || exit` after each command
- 09:52 PM Revision 8214: lib/import.sh: map_table(): Removed unneeded () around psql. This also fixes a bug where an error exit status from psql would not have aborted the script because `set -o errexit` does not apply to commands enclosed in (). For () you need to use ` || exit` instead (or ` || return` inside a function).
- 09:42 PM Revision 8213: lib/import.sh: Use `set -o errexit` so any command that exits with an error aborts the script. Note that a command's exit status can still be ignored using ` || true`. Removed no longer needed ` || return` in functions.
- 09:40 PM Revision 8212: schemas/util.sql: Renamed rename_if_exists() to try_create() because it can be used to create a column in any way, not just by renaming another column
- 09:33 PM Revision 8211: lib/import.sh: functions: abort if a command encounters an error
- 09:17 PM Revision 8210: schemas/VegCore/mk_derived: Added cultivated from oldGrowth
- 09:16 PM Revision 8209: schemas/util.sql: Added try_mk_derived_col()
- 08:35 PM Revision 8208: inputs/FIA/*/import: Run mk_derived after postprocessing commands
- 08:28 PM Revision 8207: inputs/FIA/import_order.txt: Added occurrence_all/
- 08:23 PM Revision 8206: mappings/VegCore-VegBIEN.csv: subplotID,subplot -> location.sourceaccessioncode: Fixed bug where need /_first to handle the case where both subplotID and subplot are provided
- 08:15 PM Revision 8205: Added inputs/FIA/map.csv, which maps shared columns to VegCore
- 08:12 PM Revision 8204: inputs/FIA/FIA_COND_unique/test.xml.ref: Updated now that PLOT, CONDID have been mapped
- 08:12 PM Revision 8203: inputs/FIA/*/map.csv for pre-refresh tables: Added back * before unmapped column names
- 08:03 PM Revision 8202: lib/csvs.py: stream_info(): Fixed bug where headers with multiline columns were not supported because only the first line (not the first multiline row) is sniffed for the dialect
- 06:56 PM Revision 8201: inputs/input.Makefile: %/header.csv: Fixed bug where newlines inside column names were incorrectly formatted by psql's table header formatting, by using COPY TO STDOUT instead
- 05:28 PM Revision 8200: schemas/util.sql: Added do_optionally_ignore()
- 04:28 PM Revision 8199: schemas/util.sql: Renamed rename_if_exists() to try_create() because it can be used to create a column in any way, not just by renaming another column
- 04:12 PM Revision 8198: lib/import.sh: Added mk_derived(). Added mk_derived to usage template.
- 04:11 PM Revision 8197: Added schemas/VegCore/mk_derived, which will be run in the import scripts
- 04:09 PM Revision 8196: lib/import.sh: psql(): Set psql vars :schema, :table, :table_str for use by the psql commands
- 03:22 PM Revision 8195: lib/import.sh: Export $schema, $table so they are available to programs invoked within an import script, which should not reset these vars if they include import.sh
- 03:20 PM Revision 8194: lib/import.sh: Only set $table, $schema if they don't already exist
- 03:11 PM Revision 8193: lib/import.sh: Added $root_dir and use it in $bin_dir
- 03:11 PM Revision 8192: inputs/FIA/*/import: Use new mk_*_col()
- 02:50 PM Revision 8191: schemas/*functions.sql: Renamed to *util.sql because now that these schemas are used by the new-style import scripts, there can be more than just functions in them
- 02:43 PM Revision 8190: schemas/util.sql: Added mk_const_col()
- 02:37 PM Revision 8189: schemas/util.sql: Added type_qual()
- 02:34 PM Revision 8188: schemas/util.sql: mk_derived_col(): Added "idempotent" comment
- 02:23 PM Revision 8187: schemas/util.sql: Added mk_derived_col()
- 02:22 PM Revision 8186: inputs/FIA/COND/import: oldGrowth: Updated expr column names
- 01:49 PM Revision 8185: schemas/util.sql: Added typeof(text, regtype)
- 12:54 PM Revision 8184: inputs/FIA/*/import: Removed util. before function names because util is in the search_path
- 12:43 PM Revision 8183: schemas/*functions.sql: Renamed to *util.sql because now that these schemas are used by the new-style import scripts, there can be more than just functions in them
03/25/2013
- 11:19 PM Revision 8182: schemas/functions.sql: Added existing_cols()
- 11:12 PM Revision 8181: schemas/functions.sql: col_type(): Fixed bug where a NULL col name crashed the undefined_column throw, because MESSAGE can't be NULL and the NULL name was nulling out the entire message
- 11:08 PM Revision 8180: schemas/functions.sql: Added col_exists()
- 10:31 PM Revision 8179: inputs/FIA/COND/map.csv: Mapped SLOPE, ASPECT
- 10:23 PM Revision 8178: web/main/.htaccess: remove linewraps (of the form table.path.vg/_-term) used to create a newline for Google spreadsheets
- 09:45 PM Revision 8177: inputs/FIA/*/map.csv: Replaced . between table and column name with newline, so that table viewers like pgAdmin will display both the table and column name at the left edge of the header cell, rather than displaying only the table name because the column name doesn't fit. This fixes the problem of seeing a bunch of columns whose names all start with a table name, and not knowing what each of them is. It also preserves the ability to see at a glance which table a column is in, which helps in navigating wide tables. Removed * before unmapped terms, because whether a term is mapped is generally obvious from the table name itself.
- 09:01 PM Revision 8176: inputs/input.Makefile: %/.map.csv.last_cleanup: Run fix_line_endings after canon/translate to standardize Python's \r\n line endings back to \n. This prevents issues with mixed line endings because LibreOffice (and probably Excel) treat all cell-internal line endings as \n but row line endings as whatever the file had, while text editors like jEdit translate all line endings to whatever the autodetected line ending is. (This creates spurious line ending diffs when a map spreadsheet containing multiline cells is edited in a text editor.)
- 08:45 PM Revision 8175: Added bin/fix_line_endings to standardize \r\n line endings to \n
- 08:12 PM Revision 8174: inputs/FIA/COND/import: Renamed COND.oldgrowth to VegCore name oldGrowth
- 07:52 PM Revision 8173: inputs/FIA/*/map.csv: Ensured that joined columns are globally unique, so they don't map to an ambiguous VegCore term in the future
- 07:38 PM Revision 8172: inputs/FIA/*/map.csv: Mapped terms to VegCore
- 07:22 PM Revision 8171: schemas/functions.sql: col_type(): Include column name in error message
- 06:57 PM Revision 8170: inputs/FIA/*/import: Updated column names to match map.csv
- 06:47 PM Revision 8169: schemas/functions.sql: col_type(): Raise undefined_column exception if column does not exist, instead of silently returning NULL
- 06:34 PM Revision 8168: inputs/FIA/import: Abort if any invoked script encounters an error
- 05:44 PM Revision 8167: planning/timeline/timeline.2013.xls: Updated for current progress
- 04:55 PM Revision 8166: inputs/FIA/*/map.csv: Removed no longer needed leading . from joined fields (globally-unique terms), because functions.to_global_col_names() is not used anymore
- 04:46 PM Revision 8165: Added inputs/FIA/occurrence_all/, which combines all the core tables in a denormalized view. Note that it is not necessary to materialize this view into a (large) denormalized table, because the unique indexes and left/right joins allow the rows to be denormalized on the fly.
- 04:36 PM Revision 8164: inputs/FIA/*/import: Use map_table to set column names based on the contents of map.csv, instead of using functions.to_global_col_names() and functions.rename_if_exists(). Added map.csv for all tables.
- 03:19 PM Revision 8163: inputs/FIA/: Changed postprocess.sql scripts to import scripts that can be run directly. Added top-level inputs/FIA/import to run all of them together.
- 03:05 PM Revision 8162: inputs/FIA/COND/postprocess.sql: Removed trailing whitespace
- 02:25 PM Revision 8161: Added lib/import.sh, for use by new, simpler import scripts used by FIA. Note that for now, input.Makefile is still used to create map.csv.
03/22/2013
- 11:13 PM Revision 8160: inputs/input.Makefile: Moved postprocess.sql from $(exportHeader) to %/install because that is not part of the $(exportHeader) functionality. Added %/header.csv and use it in $(exportHeader).
- 11:05 PM Revision 8159: inputs/input.Makefile: $(catSrcs): Fixed bug where need to use $(nonHeaderSrcs) instead of $(srcs) to exclude header.csv
- 08:07 PM Revision 8158: schemas/functions.sql: map: Added additional columns that are present in the standard map spreadsheet format (filter, notes). These columns are necessary to make COPY FROM work, because it requires the # of columns to be the same in the input data and the output table.
- 07:39 PM Revision 8157: inputs/input.Makefile: Moved $(cleanup) from $(exportHeader) to %/install because this is not part of exportHeader's functionality
- 07:29 PM Revision 8156: inputs/input.Makefile: $(mkSrcMap): Use header.csv instead of the header of the CSVs, so that the column list in the map spreadsheet matches the actual DB table
- 07:18 PM Revision 8155: inputs/input.Makefile: %.sql/run: Change to the directory the file is located in, so that includes (\i) are relative to the file, rather than relative to whatever happens to be the current directory
- 07:15 PM Revision 8154: inputs/input.Makefile: %/install: Always generate a header.csv, even for CSV inputs with their own header. This will include the *actual* column names in the staging table, which may differ from their names in the CSVs (e.g. the addition of row_num). Note that header.csv is not included in the CSVs list itself, and will not override the header or dialect in them.
- 06:09 PM Revision 8153: schemas/functions.sql: Added set_col_names()
- 06:08 PM Revision 8152: schemas/functions.sql: rename_if_exists(): Also ignore duplicate_column exceptions, which are generated when a column is renamed to itself (as well as when two columns are renamed to the same place)
- 06:02 PM Revision 8151: schemas/functions.sql: Added col_names(regclass), which unlike col_names(regtype) returns names in the order they are in the table
- 04:56 PM Revision 8150: schemas/functions.sql: Added map_values()
- 02:50 PM Revision 8149: schemas/functions.sql: map_get(): Fixed bug where can't use STRICT in EXECUTE INTO because there will sometimes be no match, causing a "query returned no rows" error
- 02:33 PM Revision 8148: schemas/functions.sql: rename_cols(): Support any renames type with an -> operator
- 02:27 PM Revision 8147: schemas/functions.sql: Added operator ->(regclass, text)
- 01:49 PM Revision 8146: schemas/functions.sql: Added map_get()
- 01:38 PM Revision 8145: schemas/functions.sql: table2hstore(): Made it STABLE instead of IMMUTABLE because the input table is not constant
- 01:36 PM Revision 8144: schemas/functions.sql: Added table2hstore()
- 01:34 PM Revision 8143: schemas/functions.sql: Added reset_map_table()
- 12:51 PM Revision 8142: schemas/functions.sql: Added truncate()
- 12:28 PM Revision 8141: schemas/functions.sql: mk_map_table(): Use the sql language instead of plpgsql because EXECUTE is not used directly, so plpgsql is not actually needed
- 12:25 PM Revision 8140: schemas/functions.sql: mk_map_table(): Store map table schema in separate `map` table and extend it using LIKE, for easier maintainability of the map schema
03/21/2013
- 08:27 AM Revision 8139: schemas/functions.sql: Added mk_map_table()
- 07:54 AM Revision 8138: schemas/functions.sql: ensure_prefix(): Made it IMMUTABLE instead of STABLE
- 07:37 AM Revision 8137: schemas/functions.sql: Added rename_cols()
- 06:01 AM Revision 8136: inputs/FIA/*/postprocess.sql: Avoid using :table, :table_str so that the commands in the script can also be run by pasting them into pgAdmin
- 02:13 AM Revision 8135: README.TXT: Full database import: Manual steps to run TNRS/remake analytical DB: Added `export version=<version>` to ensure that the import is run into the correct schema. Since these instructions are for running commands separately from the rest of the import, it's important to first ensure that the import environment is set up properly.
- 02:08 AM Revision 8134: schemas/vegbien.ERD.mwb: Added taxon_trait to ERD
- 02:04 AM Revision 8133: schemas/vegbien.ERD.mwb: Regenerated exports
- 01:58 AM Revision 8132: schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.
- 01:53 AM Revision 8131: schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.
- 01:27 AM Revision 8130: inputs/FIA/*/postprocess.sql: Added index on *.CN (autogen IDs)
- 01:25 AM Revision 8129: README.TXT: Full database import: Added steps to use `screen` to allow recovering from a closed terminal window
- 01:08 AM Revision 8128: inputs/FIA/TREE/postprocess.sql: TREE.unique index: Renamed to TREE.ID because this is on an autogenerated pkey rather than on domain values (for which a set of unique columns has not yet been found and may not exist)
- 01:03 AM Revision 8127: inputs/FIA/REF_SPECIES/postprocess.sql: Matched SPECIES_SYMBOL to .SYMBOL. Added .SYMBOL_TYPE for use in joining to REF_PLANT_DICTIONARY.
- 12:41 AM Revision 8126: Added inputs/FIA/REF_UNIT/postprocess.sql
- 12:36 AM Revision 8125: Added inputs/FIA/REF_RESEARCH_STATION/postprocess.sql
- 12:17 AM Revision 8124: Added inputs/FIA/COUNTY/postprocess.sql
- 12:02 AM Revision 8123: Added inputs/FIA/REF_PLANT_DICTIONARY/postprocess.sql
03/20/2013
- 05:16 PM Revision 8122: inputs/FIA/COND/postprocess.sql: Matched COND.HABTYPCD1, COND.HABTYPCD1_PUB_CD to REF_HABTYP_DESCRIPTION
- 05:07 PM Revision 8121: inputs/input.Makefile: Staging tables installation: $(exportHeader): Fixed bug where need to run postprocess.sql before exporting the header, because *it* can change the column names
- 05:02 PM Revision 8120: inputs/input.Makefile: Staging tables installation: $(exportHeader): export the header before running $(cleanup), because the header is not affected by the data cleanup operations and thus can be generated right away, to allow mapping while the cleanup operations run
- 04:30 PM Revision 8119: inputs/FIA/REF_HABTYP_DESCRIPTION/postprocess.sql: Prepare columns for joining with COND
- 03:23 PM Revision 8118: inputs/input.Makefile: Staging tables installation: $(exportHeader): Fixed bug where need to use psql_script_vegbien instead of the psql_verbose_vegbien used by $(psqlAsBien), to avoid echoing commands as part of the exported header
- 03:10 PM Revision 8117: Added planning/workflow/(de)normalized_import.mappings.png
- 03:04 PM Revision 8116: Added planning/workflow/denormalized_import.png, normalized_import.png
- 10:37 AM Revision 8115: web/main/IH/: Added lowercase alias
- 10:32 AM Revision 8114: Added web/main/IH/
- 10:12 AM Revision 8113: inputs/input.Makefile: Staging tables installation: Added postprocess target, which runs all the postprocess.sql files
- 09:34 AM Revision 8112: inputs/FIA/REF_SPECIES/postprocess.sql: Cast ID column to integer
- 08:52 AM Revision 8111: inputs/FIA/*/postprocess.sql: Cluster tables by their *.unique index for faster joins
- 08:51 AM Revision 8110: inputs/FIA/*/postprocess.sql: Cast ID columns to integer using new functions.set_col_types()
- 08:49 AM Revision 8109: bin/psql_verbose_vegbien: Run with client_min_messages = NOTICE to display notices for debugging. This is supposed to be the default, but apparently isn't.
- 08:47 AM Revision 8108: inputs/input.Makefile: BIEN commands: $(psqlAsBien): Use psql_verbose_vegbien instead of psql_script_vegbien so that timings and notices are displayed, which is useful for profiling and debugging
- 08:32 AM Revision 8107: schemas/functions.sql: Added col_cast and set_col_types()
- 07:45 AM Revision 8106: schemas/functions.sql: Added col_ref, col_type()
- 06:51 AM Revision 8105: schemas/functions.sql: Added cluster_once()
- 06:36 AM Revision 8104: schemas/functions.sql: Added cluster_index()
- 05:55 AM Revision 8103: schemas/functions.sql: create_if_not_exists(): Also handle duplicate_column exceptions
- 05:54 AM Revision 8102: schemas/functions.sql: Added rename_if_exists()
- 05:48 AM Revision 8101: inputs/FIA/COND/postprocess.sql: Renamed oldgrowth to COND.oldgrowth so it wouldn't be renamed by to_global_col_names()
- 04:28 AM Revision 8100: inputs/FIA/COND/postprocess.sql: Added oldgrowth column as part of the postprocessing instead of as part of the view that left joins the core tables together. This avoids needing to regenerate the oldgrowth field whenever the view is queried or materialized.
- 04:01 AM Revision 8099: inputs/FIA/TREE/postprocess.sql: Added index on columns that join to parent tables
- 03:00 AM Revision 8098: inputs/FIA/*/postprocess.sql: Removed table prefix from globally-unique columns that should be joined on
- 02:25 AM Revision 8097: schemas/functions.sql: Marked STRICT functions as such
- 02:22 AM Revision 8096: schemas/functions.sql: col_global_names(): Treat any column name that contains . as already being globally unique, and don't prepend the table name. This allows renaming the table columns after running col_global_names(), without causing the table name to be re-prepended the next time col_global_names() is run.
- 02:09 AM Revision 8095: schemas/functions.sql: Added contains()
- 02:07 AM Revision 8094: schemas/functions.sql: Added create_if_not_exists()
- 01:28 AM Revision 8093: inputs/FIA/*/postprocess.sql: Use functions.to_global_col_names() to ensure that all column names are globally unique. This makes it easy to join the tables together without worrying about column name collisions.
- 01:15 AM Revision 8092: inputs/FIA/*/postprocess.sql: Use new functions.create_if_not_exists() to allow re-running postprocess.sql idempotently
03/19/2013
- 11:48 PM Revision 8091: inputs/input.Makefile: Staging tables installation: %/install: Use new %.sql/run to run postprocess.sql
- 11:47 PM Revision 8090: inputs/input.Makefile: Staging tables installation: Added %.sql/run to run postprocess.sql, etc. separately from the install targets they are a part of
- 11:47 PM Revision 8089: inputs/input.Makefile: Staging tables installation: Added %.sql/run to run postprocess.sql, etc. separately from the install targets they are a part of
- 10:43 PM Revision 8088: schemas/functions.sql: Added to_global_col_names()
- 10:22 PM Revision 8087: schemas/functions.sql: col_global_names(): Use new functions.ensure_prefix() to only add the table name prefix if it doesn't already exist. This makes the function idempotent.
- 10:19 PM Revision 8086: schemas/functions.sql: Added ensure_prefix()
- 10:17 PM Revision 8085: schemas/functions.sql: Added has_prefix()
- 10:09 PM Revision 8084: schemas/functions.sql: Added col_global_names()
- 09:59 PM Revision 8083: schemas/functions.sql: Added name(regtype)
- 09:43 PM Revision 8082: schemas/functions.sql: Added col_names()
- 09:27 PM Revision 8081: root Makefile: Installation: Fixed bug where need to run schemas/public/install separately because schemas/install installs only the util schemas
- 09:26 PM Revision 8080: root Makefile: Installation: install util schemas (temp functions py_functions) before inputs, so that inputs can use util functions in their postprocess.sql or create.sql scripts. (However, they must not use util functions in views or index functions, because these would be cascadingly deleted whenever the util schemas are reinstalled before an import.)
- 08:07 PM Revision 8079: README.TXT: Single datasource import: Added by_col=1 to all commands
- 02:28 AM Revision 8078: mappings/VegCore-VegBIEN.csv: locationRemarks: Remapped to locationnarrative because location.notespublic is a boolean field
- 02:05 AM Revision 8077: lib/sql_io.py: mk_errors_table(): Create a unique index on the MD5 of the value and error instead of on the values directly, because some strings are too long to index (e.g. row 2537268 of MO.Specimen causes an error "index row size 3032 exceeds maximum 2712 for index [...] Values larger than 1/3 of a buffer page cannot be indexed")
- 12:49 AM Revision 8076: inputs/import.stats.xls: Updated import times
Also available in: Atom