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: analytical_stem_view and related views: updated COMMENTs from data dictionary spreadsheet, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#copy-data-dictionary-definitions-to-database
schemas/public_.sql: ran analytical_stem_view_modify(), which transfers the column COMMENTs from analytical_stem_view to analytical_stem
bugfix: schemas/public_.sql: analytical_stem_view_modify(): updated to new analytical_stem_view column names
fix: schemas/public_.sql: analytical_stem_view derived and related views: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database but with the current columns of analytical_stem as the left-hand column
schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database
schemas/public_.sql: added 2014-6-4.Iara_Lacher.reserve_prioritization
schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: also include New World occurrences by coordinates, using new lat_long_in_new_world(). this modification (as requested by Jeff) will help reduce the false negatives filtered out by including only data with placenames.
schemas/public_.sql: added lat_long_in_new_world() wrapper around util.lat_long_in_new_world()
schemas/public_.sql, inputs/.TNRS/schema.sql: upgraded to Postgres 9.3.4 format, which removes trailing " "
schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: allow geovalid_bien = NULL to enable filtering New World status by coordinates rather than country
schemas/public_.sql: 2014-6-12.Jeff_Ott.climatic_range_determinants: added geovalid_bien
schemas/vegbien.sql: added 2014-6-12.Jeff_Ott.climatic_range_determinants (wiki.vegpath.org/Data_requests)
schemas/public_.sql: analytical_stem_view: example terms from each source: added location in the VegBIEN core DB
schemas/public_.sql: analytical_stem_view: terms in normalized schemas: put the source name right next to the term name (instead of after the table name) so it's easy to find
schemas/public_.sql: analytical_stem_view: VegX terms: populated definitions
schemas/public_.sql: analytical_stem_view: VegX terms: referenced to the specific table/subpath that they are in
bugfix: schemas/public_.sql: analytical_stem_view: locationName: re-sourced to VegX because this is actually not a DwC term
schemas/public_.sql: analytical_stem_view: populated definitions of linked DwC terms from DwC data dictionary
schemas/public_.sql: analytical_stem_view: renamed terms to link URLs, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-rename-terms-to-link-URLs
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
schemas/public_.sql: analytical_stem_view: populated links from unrenamed term names, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-populate-links-from-unrenamed-term-names
fix: schemas/public_.sql: analytical_stem_view: links: removed = before them because the link is in fact one member in a list of definitions (the = would apply to all definitions, not just the link, and since it apply to all definitions, it's redundant because of course the comment is what the column equals)
fix: schemas/public_.sql: analytical_stem_view: vegpath.org links: use ; instead of : between link and definition, because it makes more grammatical sense
schemas/public_.sql: analytical_stem_view: vegpath.org links: removed no longer needed // suffix
schemas/public_.sql: analytical_stem_view: added links and definitions for half a dozen of the terms from various sources
schemas/public_.sql: analytical_stem_view: indicated provenance of the rest of the terms
schemas/public_.sql: analytical_stem_view: flagged the DwC terms
schemas/public_.sql: added plot.**.modify()
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()
fix: schemas/public_.sql: tnrs_input_name: added ORDER BY to ensure a stable sort order. this also creates better query plans by enabling merge joins.
bugfix: schemas/public_.sql: tnrs_input_name: need to exclude unsafe taxon names as a workaround to #935 (TNRS crashes when scrubbing names with two infraspecific epithets)
schemas/public_.sql: taxonlabel: added index on taxonomicname to facilitate retrieving rows from tnrs_input_name
inputs/.TNRS/schema.sql: added taxon_name_is_safe()
inputs/.TNRS/schema.sql: added unsafe_taxon_names()
schemas/public_.sql: sync_*(): renamed to *_modify() to facilitate finding these functions when modifying the corresponding view (using the new naming convention for a view's on-modify function)
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: schemas/public_.sql: sync_*(): use util.copy() instead of CREATE TABLE AS so that table and column comments are also copied. this avoids the need to separately add the same comments to the view and its materialized table.
bugfix: schemas/public_.sql: sync_geoscrub_input_to_view(): `CREATE TABLE geoscrub_input AS __`: needs `LIMIT 0`
schemas/public_.sql: sync_analytical_stem_to_view(): removed DROP TABLE IF EXISTS because this is now done automatically by util.recreate()
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)
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: 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/schema.sql: added tnrs__match_num__next()
inputs/.TNRS/schema.sql: added tnrs__batch_begin() trigger to populate the match_num (match sort order)
schemas/public_.sql: added 2014-3-11.Jeff_Ott.climatic_range_determinants
schemas/public_.sql: analytical_stem_view: added scrubbed_taxon_name_with_author, needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)
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)
schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)
fix: schemas/public_.sql: sync_analytical_stem_to_view(): removed fkey to source.shortname because this prevents reloading individual datasources
schemas/public_.sql: analytical_stem_view: scrubbed_morphospecies_binomial: use new taxon_scrub.scrubbed_morphospecies_binomial
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
bugfix: schemas/public_.sql: _plots_20_tnrs_names: verbatim_name_with_author: use taxonverbatim.taxonomicname rather than taxonlabel.taxonomicname
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: schemas/public_.sql: _plots_20_tnrs_names: fixed order of tnrs_taxonomic_status, tnrs_accepted_name_family
schemas/public_.sql: _plots_20_tnrs_names: added tnrs_taxonomic_status, as requested in issue #915
schemas/public_.sql: analytical_stem_view: taxonomic_status: don't filter with map_taxonomic_status() anymore since this is now done by "TNRS"."MatchedTaxon"
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.
schemas/public_.sql: _plots_20_tnrs_names: use accepted_morphospecies_binomial, which is now available directly in the TNRS results, instead of just the morphospecies_suffix, as requested in issue #915
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
fix: inputs/.TNRS/schema.sql: COMMENTs: always include newline before and after
bugfix: schemas/public_.sql: plots_20_tnrs_names: tnrs_accepted*: use new accepted_*, not scrubbed_*, as scrubbed_* does not contain only the accepted name (as implied by the tnrs_accepted_* column name)
bugfix: inputs/.TNRS/schema.sql: taxon_scrub, etc.: undid rename of accepted name columns to scrubbed_* (r13435), because these are actually not the same (scrubbed_* is the combination of accepted and no-opinion names). the accepted name columns will now be named accepted_*, following the standard naming scheme.
bugfix: schemas/public_.sql: _plots_20_tnrs_names: morphospeciesSuffix must still be called morphospecies_suffix, not taxon_morphospecies, because that column has not been updated to the taxon_morphospecies yet (fixes r13435)
fix: inputs/.TNRS/schema.sql: taxon_scrub, etc.: scrubbed_*: use columns from MatchedTaxon whenever possible, to as much as possible avoid the need to join to taxon_scrub.scrubbed_unique_taxon_name.*
schemas/Makefile: vegbien.sql: exclude the same set of Source tables excluded by inputs/.TNRS/schema.sql, so that reinstalling TNRS doesn't change the contents of this file
schemas/public_.sql: _plots_20_tnrs_names: changed to set of columns requested by Martha
schemas/public_.sql: _plots_20_tnrs_names: added morphospecies suffix in order to also validate it
schemas/public_.sql: _plots_20_tnrs_names: added verbatim name in order to also validate the formation of the concatenated name
