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

schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept

schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover

schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species

schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_06a_list_of_stems, for use in figuring out the diff in _plots_06_list_of_plots_with_stem_measurements

schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location

bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id

fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries

fix: schemas/vegbien.sql: location: added plot_source_id index to provide the equivalent of the location.source_id index for outer plots. this will help Postgres choose the right query plans in queries involving outer plots.

schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table

schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table

schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_10a_aggregate_observation_individual_counts, for use in debugging diffs in _plots_10_count_of_individuals_per_plot_in_each_proj

schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.

schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.

fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger

schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries

schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries

schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries

schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries

schemas/vegbien.sql: synced with DB, which reorders ~type tables

bugfix: schemas/vegbien.sql: added back keys() matchup functions, which get cascadingly deleted when the queries are re-created to change column names, etc. note that this causes the associated ~type tables to sort before them.

schemas/vegbien.sql: added new ~type tables

schemas/vegbien.sql: rm_query_group(): support removing all validations as well as just those with a particular group prefix (and renamed to rm_output_queries())

schemas/vegbien.sql: synced with DB, which reorders ~type tables

fix: schemas/vegbien.sql: added back traits* ~type tables

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)

schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .

schemas/vegbien.sql: source_by_shortname(): documented that it must be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for each row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)

fix: schemas/vegbien.sql: location: added index on top_plot

fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query

schemas/vegbien.sql: added more ~type tables for plots queries

schemas/vegbien.sql: added public_validations.rm_all_queries(schema), which removes all validations queries in a schema, or in all schemas (useful when recreating validations queries from the DDL export file)

schemas/util.sql: query_relations(): support passing in an input query instead of an output query, which will remove only in the datasource's schema. (note that it was not clear that passing in an input-side query was not previously supported.)

schemas/vegbien.sql: schema_anchor: clarified that this identifies this function's schema (for use in locating helper functions), but is not necessarily the schema operated on, as the comment implied

schemas/vegbien.sql: replaced custom %== operators with much simpler custom keys() functions. this avoids both the need to write out an = comparison for each field, and especially, also the need to write both an operator and a function implementing that operator.

schemas/util.sql, vegbien.sql: do not declare IMMUTABLE SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment)

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name

bugfix: schemas/util.sql: diff(text, text) and types that use its custom FULL JOIN matching: need to use separate operator %== for determining which records to put on the same row, so that the = filter for identical rows only excludes rows that are actually the same, not all rows with the same keys (which would usually remove all rows)

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

schemas/vegbien.sql: _plots_02_list_of_project_names: added = operator that causes FULL JOIN to only use some columns, so that rows for the same label column are put on the same row in the diff table

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

fix: schemas/vegbien.sql: public_validations._plots_* that use projectname: re-alias to project_name to match input queries. (reexporting apparently changes the relative order of the ~type tables.)

schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully

schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)

schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources

schemas/vegbien.sql: added validatable_datasources()

schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables

fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.

bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param

schemas/vegbien.sql: added query_view_relation_max_prefix_len()

schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query

schemas/vegbien.sql: added new_query_view_relation_name()

schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query

fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views

schemas/vegbien.sql: added public_validations.query_views()

schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)

fix: schemas/vegbien.sql: renamed output views to match input views

schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)

schemas/vegbien.sql: added remake_diff_tables()

schemas/vegbien.sql: added validation_views()

schemas/vegbien.sql: added remake_diff_table()

schemas/vegbien.sql: added rematerialize_in_view()

schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)

schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)

bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path

schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for

schemas/vegbien.sql: schema_null params: use schema_anchor instead of source for this, since it is guaranteed to exist regardless of which tables are in the VegBIEN schema

schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)

schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.

schemas/vegbien.sql: added schema_anchor, for use with schema_null params

validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.

schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using

schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*