schemas/public_.sql: iucn_red_list_view: documented how to regenerate iucn_red_list from this

bugfix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: fixed slowdown in materializing the view, which was caused by source__observation_type(), by replacing this with a LEFT JOIN as was done for is_threatened_iucn

fix: schemas/public_.sql: 2014-6-4.Iara_Lacher.reserve_prioritization: is_threatened_iucn: generate this much faster by doing a LEFT JOIN, which can reuse the same in-memory hash index for every row

bugfix: schemas/public_.sql: wrapper functions can't use util-schema types because these will cause the wrapper function to be cascadingly dropped when the util schema is reinstalled

schemas/public_.sql: added in_iucn_red_list()

schemas/public_.sql: added iucn_red_list, materialized from iucn_red_list_view

schemas/public_.sql: added iucn_red_list

schemas/public_.sql: iucn_red_list: renamed to iucn_red_list_view since this will be materialized

schemas/public_.sql: added iucn_red_list

schemas/public_.sql: added source__observation_type()

schemas/public_.sql: added subspecies() wrapper

schemas/util.sql: added south_america(), in_south_america()

schemas/util.sql: lat_long_in_new_world(): renamed to just in_new_world() because the lat/long is implied by the param type

schemas/util.sql: lat_long_in_new_world(): take a geocoord param instead of separate lat/long params

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

fix: inputs/.TNRS/schema.sql: taxon_match: renamed related items to start with taxon_match__*

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

inputs/.TNRS/schema.sql: tnrs: renamed to tnrs_match to distinguish it from other TNRS-related tables

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

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: added lat_long_in_new_world() wrapper around util.lat_long_in_new_world()

schemas/vegbien.sql: added 2014-6-12.Jeff_Ott.climatic_range_determinants (wiki.vegpath.org/Data_requests)

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: 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: 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

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

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()

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)

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.)

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: 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: 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)

fix: lib/PostgreSQL-MySQL.csv: need to replace "double precision" with "double" to work with MySQL Workbench 5.2.47

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)

schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

inputs/.TNRS/schema.sql: added taxon_scrub_modify()

inputs/.TNRS/schema.sql: MatchedTaxon_modify(): documented usage

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()

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

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: merged _plots_20_tnrs_input_names, _plots_21_tnrs_output_names into _plots_20_tnrs_names so the input and output names can be compared side-by-side

schemas/vegbien.sql: synced to DB, which adds Source.errors table indexes added in the import

schemas/public_.sql: _plots_20_tnrs_input_names, _plots_21_tnrs_output_names: added Brad's comments

schemas/public_.sql: added _plots_20_tnrs_input_names, _plots_21_tnrs_output_names

fix: schemas/public_.sql: rm_all_queries(): also remove output-only result tables

schemas/public_.sql: added remake_output_tables(datasource_schema, prefix)

schemas/public_.sql: added output_queries(prefix)

schemas/public_.sql: validation_views(): added prefix param, which can be used for additional subsetting

schemas/public_.sql: query_relations(): removed no longer used max_prefix_len default param

schemas/vegbien.sql: added public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len

schemas/public_.sql: rm_output_queries(), validation_views(): use public_validations.query_view_regexp()

schemas/public_.sql: added public_validations.query_view_regexp()

fix: lib/PostgreSQL-MySQL.csv: preserve schema assignments by translating `SET search_path` to `USE`

schemas/Makefile: vegbien.sql: also include geoscrub, TNRS schemas, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema). this involves having a separate public_.sql file for restoring the public schema.

inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: added breakdowns _specimens_13a_list_of_all_verbatim_lat_long, _specimens_13b_list_of_all_decimal_lat_long to help troubleshoot the diff

fix: inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: count lat/longs together instead of separately, because the DISTINCT is by coordinate pair, not individual coordinate value (which wouldn't make much sense)

bugfix: schemas/vegbien.sql: rm_output_queries(): need to account for the fact that util.truncated_prefixed_name_regexp() returns a whole-string regexp. this drops support for removing output queries with a particular group prefix, which we no longer use.

bugfix: schemas/vegbien.sql: rm_output_queries(): need to include relations whose names were truncated, as well

schemas/vegbien.sql: taxonverbatim: added subspecies, as decided in the conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema-2)

schemas/vegbien.sql: specimens*: ran through pipeline

schemas/vegbien.sql: _specimens_16_list_distinct_specimen_descriptions: re-ran through pipeline after removing duplicated rows

schemas/vegbien.sql: rm_output_queries(): also support removing just a particular output query

schemas/vegbien.sql: _specimens_11_list_of_three_standard_political_divisions: ran through pipeline

schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: ran through pipeline

schemas/vegbien.sql: _specimens_10_count_number_of_records_by_institution: ran through pipeline

schemas/vegbien.sql: public_validations._specimens_*: added comments from validation/aggregating/specimens/qualitative_validations_specimens.sql

schemas/vegbien.sql: remake_diff_tables(schema text): removed bien2_traits runtime because this applies only to one datasource. the bien2_traits runtime is now documented in inputs/bien2_traits/run.

schemas/vegbien.sql: public_validations: specimens queries: added autogenerated ~type tables

schemas/vegbien.sql: public_validations: added specimens queries to pipeline

schemas/vegbien.sql: public_validations: added _specimens_01_count_of_total_records_specimens_in_source_db

schemas/vegbien.sql: specimenreplicate.institution_id: renamed to duplicate_institutions_sourcelist_id, as decided in the conference calls (wiki.vegpath.org/2014-03-13_conference_call#schema-changes-2)

schemas/vegbien.sql: _plots_08_list_of_plots_which_use_percent_cover, _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: reran with fixes, which removes the incorrectly auto-added copies columns. (they were only able to be auto-added because the tables had no rows.)

schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project

schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row

schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj