bugfix: inputs/.TNRS/schema.sql: removed no longer used derived column __accepted_infraspecific_label, which had a buggy formula that broke derived_cols_populate()
fix: inputs/.TNRS/schema.sql: taxon_match: to remove a column: updated instructions
inputs/.TNRS/schema.sql: added new derived columns to derived views
fix: schemas/util.sql: derived_col_update(): also need steps to drop column, because DROP __ CASCADE doesn't work when there are dependent views
inputs/.TNRS/schema.sql: _accepted_infraspecific{rank,epithet}: use array slice of new _accepted{genus,specific_epithet,infra_{rank,epithet}}, which is simpler than using remove_prefix() in __accepted_infraspecific_label
inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": don't need to use *Accepted_name anymore because _accepted{genus,specific_epithet,infra_{rank,epithet}} is now generated from *Accepted_name
inputs/.TNRS/schema.sql: taxon_match."__accepted_{genus,specific_epithet}": renamed to "__accepted_{genus,specific_epithet,infra_{rank,epithet}}" since this now includes these other ranks as well
bugfix: inputs/.TNRS/schema.sql: taxon_match."__accepted_{genus,specific_epithet}": use "*Accepted_name" instead of "Accepted_species[_binomial]__@TNRS__@vegpath.org" (from "*Accepted_name_species") because Accepted_name_species apparently sometimes does not match the Accepted_name and uses malformed Unicode characters
inputs/.TNRS/schema.sql: taxon_match: `inputs/.TNRS/data.sql.run refresh`: documented runtime (1 min)
bugfix: inputs/.TNRS/schema.sql: taxon_match: use "Accepted_species[_binomial]__@TNRS__@vegpath.org" instead of "*Accepted_name_species". this fixes a bug in __accepted_infraspecific_label where Accepted_name_species with trailing whitespace could not be prefix-removed from names that contained just a species binomial.
fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_species[_binomial]__@TNRS__@vegpath.org", which removes trailing whitespace
inputs/.TNRS/schema.sql: added steps to remove a column and to add a non-derived column
inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns at the end: merged into "to add a new derived column"
inputs/.TNRS/schema.sql: to add columns in the middle: renamed to "to move a column to the middle" for clarity
inputs/.TNRS/schema.sql: to populate a new column: updated to use util.derived_col_update()
fix: inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns: also need to run util.recreate_view()
inputs/.TNRS/schema.sql: taxon_match: to remove columns or add columns at the end: don't need to run `rm=1 inputs/.TNRS/data.sql.run` because this is now run by `make schemas/remake`
schemas/util.sql: remove_prefix(), remove_suffix(): support case-insensitive matching
bugfix: inputs/.TNRS/schema.sql: taxon_match.__accepted_infraspecific_label: need to use case-insensitive matching of the removed prefix because TNRS lowercases part of the Accepted_name
bugfix: inputs/.TNRS/schema.sql: taxon_match: use wrapper for util.remove_prefix() so CHECK constraints that use it don't get dropped when the util schema is reinstalled
inputs/.TNRS/schema.sql: taxon_match: COMMENT: added steps to port derived column changes to vegbiendev
bugfix: inputs/.TNRS/schema.sql: taxon_match: derived columns: use new "matched~Name[_no_author]___@TNRS__@vegpath.org" instead of "*Name_matched" so that "No suitable matches found." is removed before concatenating with other fields
inputs/.TNRS/schema.sql: taxon_match: added derived column "matched~Name[_no_author]___@TNRS__@vegpath.org", which removes the "No suitable matches found." string
inputs/.TNRS/schema.sql: reordered derived columns in dependency order
bugfix: inputs/.TNRS/schema.sql: "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org": need to use "[accepted_]genus__@DwC__@vegpath.org" rather than "*Accepted_name" for this for rank = genus
inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"
bugfix: inputs/.TNRS/schema.sql: "[accepted_]genus__@DwC__@vegpath.org": need to populate this for rank = genus
inputs/.TNRS/schema.sql: taxon_match: added derived column "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"
bugfix: inputs/.TNRS/schema.sql: derived columns: use "Accepted_family__@TNRS__@vegpath.org" instead of "*Accepted_name_family" because "*Accepted_name_family" is sometimes missing
fix: inputs/.TNRS/schema.sql: taxon_match: added derived column "Accepted_family__@TNRS__@vegpath.org", which is needed because "*Accepted_name_family" isn't always populated
bugfix: inputs/.TNRS/schema.sql: taxon_match: to add columns in the middle: also need to run util.derived_cols_repopulate() since the dependency order has changed
fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: to add columns in the middle: also need to run util.derived_cols_update()
fix: inputs/.TNRS/schema.sql: taxon_match: COMMENT: updated util.derived_cols_sync() to util.derived_cols_update()
bugfix: schemas/util.sql: derived_cols_trigger_update(): need to clear derived cols first so old values won't be used in calculations
inputs/.TNRS/schema.sql: derived_cols_trigger_update(): put expr on same line as var, to save space
inputs/.TNRS/schema.sql: derived_cols_trigger_update(): removed lines between statements, which add too much space
fix: schemas/util.sql: derived_cols_trigger_update(): don't indent expr because may have multiple lines
bugfix: schemas/util.sql: derived_col_update(): set_comment(): vars can't have same name as params, which will be substituted
inputs/.TNRS/schema.sql: taxon_match: added derived column "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"
inputs/.TNRS/schema.sql: taxon_match: added derived column "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"
fix: schemas/util.sql: derived_col_update(): column comment: formula: put on own line to support multiline exprs
bugfix: schemas/util.sql: derived_col_update(): column comment: need to use expr from DB, not as provided by user, to avoid this changing when derived_col_update() is rerun
inputs/.TNRS/schema.sql: taxon_match: added derived columns scrubbed_*
inputs/.TNRS/schema.sql: taxon_match: put matched-taxon derived columns before accepted-taxon derived columns
inputs/.TNRS/schema.sql: taxon_match: added derived column "[matched_]species[_binomial]~(Accepted_)__@TNRS__@vegpath.org"
inputs/.TNRS/schema.sql: _matched_has_accepted: renamed to matched_has_accepted because the leading _ did not create vertical alignment anyway, due to different quoting syntaxes
schemas/util.sql: derived_col_update(): add steps to rename column
bugfix: schemas/util.sql: derived_col_update(): don't set_comment() until CHECK constraint exists so that we can use its canon-ed formula
inputs/.TNRS/schema.sql: taxon_match: added derived column _matched_has_accepted
inputs/.TNRS/schema.sql: taxon_match: added derived columns "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org"
inputs/.TNRS/schema.sql: taxon_match: added derived column "__accepted_infraspecific_{rank,epithet}"
inputs/.TNRS/schema.sql: taxon_match: added derived column __accepted_infraspecific_label
bugfix: schemas/util.sql: derived_col_update(): steps to modify: also need to run util.derived_cols_populate()
inputs/.TNRS/schema.sql: taxon_match: added derived columns "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org"
inputs/.TNRS/schema.sql: taxon_match: ran derived_cols_update()
inputs/.TNRS/schema.sql: taxon_match: documented that whenever columns are renamed, util.derived_cols_sync() should be run
inputs/.TNRS/schema.sql: taxon_match__fill_derived(): updated using new util.derived_cols_sync()
fix: inputs/.TNRS/schema.sql: taxon_match: to populate a new column: also need to recluster table so rows are exported in sorted order
fix: inputs/.TNRS/schema.sql: taxon_match: cluster the table so the rows are always exported in the same order, even when an UPDATE statement is used to run triggers
inputs/.TNRS/schema.sql: added _accepted{genus,specific_epithet} derived column, using the steps at wiki.vegpath.org/Postgres_queries#maintaining-derived-column-formulas-across-column-renames
fix: inputs/.TNRS/schema.sql: taxon_match: added back * prefixes on TNRS-namespace column names
inputs/.TNRS/schema.sql: MatchedTaxon: taxon_best_match: don't alias to `s` since this is no longer a nested select
inputs/.TNRS/schema.sql: MatchedTaxon: nested select: use just taxon_best_match since this no longer performs renamings
inputs/.TNRS/schema.sql: MatchedTaxon: renamed output columns to match input columns, so that the nested select would not be performing any renamings
fix: inputs/.TNRS/schema.sql: MatchedTaxon: added all columns from taxon_match (some of them apparently hadn't been included in MatchedTaxon)
fix: inputs/.TNRS/schema.sql: reordered columns to match taxon_match
fix: inputs/.TNRS/schema.sql: added back Family_matched, which had gotten merged with Name_matched_accepted_family when TNRS temporarily stopped populating Name_matched_accepted_family
inputs/.TNRS/schema.sql: MatchedTaxon: taxonomicStatus: moved outside the inner SELECT so that the inner SELECT would consist solely of renamings
inputs/.TNRS/schema.sql: MatchedTaxon: removed unneeded "Name_matched." prefix on source-specific names (only the * is needed because there is only one table)
inputs/.TNRS/schema.sql: matchedFamily: just use Name_matched_accepted_family, because TNRS has now been reloaded so that the names that were missing this have it populated
inputs/.TNRS/schema.sql: taxon_match: taxon_match__valid_match: replaced with taxon_best_match__valid_match, which also applies taxon_best_match's filters, since taxon_match is now accessed through taxon_best_match
fix: inputs/.TNRS/schema.sql: MatchedTaxon: use taxon_best_match instead of taxon_match because this should provide only one match per taxon
inputs/.TNRS/schema.sql: added taxon_best_match view
inputs/.TNRS/schema.sql: taxon_match: added taxon_match__one_selected_match unique index
inputs/.TNRS/schema.sql: taxon_match__fill(): split into separate DECLARE blocks for each field for clarity
fix: inputs/.TNRS/schema.sql: taxon_match: renamed related items to start with taxon_match__*
inputs/.TNRS/schema.sql: taxon_match: insert names via taxon_match_input auto-updatable view instead of directly into taxon_match, to allow the taxon_match columns to be renamed while still supporting inserts using the TNRS column names
inputs/.TNRS/schema.sql: tnrs_match: renamed to taxon_match to use the normalized VegCore name for this, and to avoid repeating the schema name
bugfix: inputs/.TNRS/schema.sql: taxon_name_is_safe(): need to use `NOT (_ = ANY()) instead of ` != ANY`, because the != operator is applied to each element
inputs/.TNRS/schema.sql: tnrs: renamed to tnrs_match to distinguish it from other TNRS-related tables
inputs/.TNRS/schema.sql: `taxon_scrub.scrubbed_unique_taxon_name.*`: added to-modify instructions
inputs/.TNRS/schema.sql: *_modify(): merged these into the "to modify" instructions in the corresponding views, because there is no need to create a separate *_modify() function for every view now that their definitions are all the same
schemas/public_.sql, inputs/.TNRS/schema.sql: upgraded to Postgres 9.3.4 format, which removes trailing " "
inputs/.TNRS/schema.sql: unsafe_taxon_names(): removed the name with "spp." now that this TNRS bug (https://pods.iplantcollaborative.org/jira/browse/TNRS-193) has been fixed
inputs/.TNRS/schema.sql: *_modify(): removed the need to manually maintain copies of the dependent view definitions with the *s in place, because the *s are now added automatically by view_def_to_orig()
inputs/.TNRS/schema.sql: added taxon_name_is_safe()
inputs/.TNRS/schema.sql: added unsafe_taxon_names()
bugfix: inputs/.TNRS/schema.sql: MatchedTaxon_modify(): updated to include taxon_scrub derived fields
inputs/.TNRS/schema.sql: *_modify(): allow running without a view_query, as recreate_view() now supports this
fix: inputs/.TNRS/schema.sql: added back index on Name_submitted, which is needed for tnrs_input_name to work properly (now that there is no automatic index created by a unique constraint)
