inputs/.TNRS/schema.sql: added new derived columns to derived views
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/Source/test.xml.ref: updated
inputs/.TNRS/Source/map.csv: datasetURL: updated to indicate that this uses the TNRS dev server (the actual URL is private)
removed no longer needed inputs/.TNRS/grants.sql, since the grants in schema.sql are now being run
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
inputs/.TNRS/data.sql: refreshed
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/.htaccess: qsappend does not support fragment, so append it separately
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
lib/tnrs.py: switched to downloading all matches per name, as is needed to implement #917. note that this will break the parts of the schema that use the tnrs table, until Brad's match-picking algorithm can be implemented, but this tradeoff is necessary to be able to begin scrubbing sooner (Martha; wiki.vegpath.org/2014-05-29_conference_call#TNRS)
schemas/vegbien.sql: tnrs_input_name: don't scrub accepted names, as using multiple matches per name no longer provides a single accepted name to scrub. instead, the Accepted_* fields can be whitespace-split to generate the same columns that would have been generated by the scrubbing (and without the overhead of the extra TNRS call).
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)
fix: inputs/.TNRS/schema.sql: tnrs: removed unique constraint on Name_submitted, Name_matched because there can be more than one match with the same Name_matched (but different accepted names, etc.)
fix: inputs/.TNRS/schema.sql: tnrs.tnrs__valid_match index: made it non-unique to allow multiple matches per name, as is needed to implement #917
bugfix: inputs/.TNRS/schema.sql: tnrs__match_num__fill(): only fill if not set, to support case where tnrs is being restored from a .sql file (where match_num is already set)
inputs/.TNRS/schema.sql: tnrs: documented runtime to add a constraint (3 min)
inputs/.TNRS/schema.sql: unique constraint on Name_submitted: added Name_matched to allow multiple matches per name, as is needed to implement #917
inputs/.TNRS/schema.sql: tnrs: documented how to populate a new column
inputs/.TNRS/schema.sql: tnrs: pkey: use match_num instead of Name_number to allow multiple matches per name, as is needed to implement #917
inputs/.TNRS/schema.sql: tnrs.match_num: made it NOT NULL now that it's populated
inputs/.TNRS/schema.sql: tnrs: populate match_num
inputs/.TNRS/schema.sql: tnrs: documented how to add and remove columns
inputs/.TNRS/schema.sql: made COMMENTs start on their own line, using the steps at wiki.vegpath.org/Postgres_queries#make-COMMENTs-start-on-their-own-line
inputs/.TNRS/schema.sql: tnrs: added match_num
inputs/.TNRS/data.sql.run: refresh(): documented runtime (1 min)
inputs/.TNRS/schema.sql: added tnrs__match_num__next()
inputs/.TNRS/schema.sql: added tnrs__batch_begin() trigger to populate the match_num (match sort order)
inputs/.TNRS/schema.sql: taxon_scrub.scrubbed_unique_taxon_name.*: added scrubbed_taxon_name_with_author, needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)
inputs/.TNRS/schema.sql: taxon_scrub: added scrubbed_morphospecies_binomial, analogous to accepted_morphospecies_binomial for scrubbed_*
inputs/.TNRS/schema.sql: taxon_scrub: documented how to modify it
inputs/.TNRS/schema.sql: added taxon_scrub_modify()
inputs/.TNRS/schema.sql: MatchedTaxon_modify(): use simpler util.recreate_view()
inputs/.TNRS/schema.sql: MatchedTaxon_modify(): documented usage
inputs/.TNRS/schema.sql: MatchedTaxon_modify(): removed no longer needed DROP VIEW statement
fix: schemas/util.sql: force_recreate(): renamed to just recreate(), because "force" normally implies that things will be deleted, which this function does not do
fix: inputs/.TNRS/schema.sql: MatchedTaxon.taxonomicStatus: filter using map_taxonomic_status() so that the corrected value is available in the normalized DB, not just analytical_stem
inputs/.TNRS/schema.sql: MatchedTaxon: to modify: use new MatchedTaxon_modify(), which eliminates the work of putting together the dependent views
inputs/.TNRS/schema.sql: added MatchedTaxon_modify()
bugfix: inputs/.TNRS/schema.sql: map_taxonomic_status(): need to use accepted name instead of scrubbed name (which also includes no-opinion names), as described at http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields. this used to be the accepted name, but got switched when the concatenated name was also used to store the matched name for no-opinion names.
inputs/.TNRS/schema.sql: MatchedTaxon: documented how to modify it (using util.force_recreate())
inputs/.TNRS/schema.sql: MatchedTaxon, etc.: added accepted_morphospecies_binomial derived field
inputs/.TNRS/schema.sql: MatchedTaxon.Accepted_name_species: mapped to accepted_species_binomial
