schemas/vegbien.sql: taxonverbatim: added subspecies, as decided in the conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema-2)
fix: schemas/vegbien.sql: plots* with duplicated rows: removed duplicated rows
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
fix: schemas/vegbien.sql: _specimens_11_list_of_three_standard_political_divisions: use same column names as input query
schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: ran through pipeline
fix: schemas/vegbien.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: dateCollected: also need to convert to text in GROUP BY/ORDER BY
bugfix: schemas/vegbien.sql, inputs/NY/validations.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_12_distinct_collector_name_collect_num_date_w_count: dateCollected: cast this to text rather than date because some values for this field are not valid dates and will throw an error if cast to date
schemas/vegbien.sql: _specimens_10_count_number_of_records_by_institution: ran through pipeline
bugfix: schemas/vegbien.sql, validation/aggregating/specimens/qualitative_validations_specimens.sql: _specimens_10_count_number_of_records_by_institution: need to dereference specimenreplicate.duplicate_institutions_sourcelist_id to the corresponding sourcelist.name
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: schema comment: documented how to run the validations. this information is also in the usage comment for public_validations.remake_diff_table(), but is copied here for easy reference.
schemas/vegbien.sql: public_validations: specimens queries: added autogenerated ~type tables
schemas/vegbien.sql: public_validations: added specimens queries to pipeline
fix: schemas/vegbien.sql: public_validations.rematerialize_out_view(text, regclass): run with join_collapse_limit = 1 to fix query planner issues. this option has been tested on the queries that do not yet use the standard join sequence (plots #11,12,13,14,16,17,18), and all of these queries also work fine with join_collapse_limit = 1. (the standard join sequence is used to ensure both correctness of the query and compatibility with join_collapse_limit = 1, but in some cases is not needed for join_collapse_limit.)
bugfix: schemas/vegbien.sql: schemas/vegbien.sql(): need to util.use_schema(schema_anchor) before initializing vars that use own-schema functions
schemas/vegbien.sql: updated _specimens_01_count_of_total_records_specimens_in_source_db
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: schema comment: changed "to sync the queries with schemas/vegbien.sql" to "to reset the queries to what's in schemas/vegbien.sql" for clarity
fix: schemas/vegbien.sql: schema comment: to reset the key and value columns for all validations queries: updated running of custom keys() functions to use keys() types instead
schemas/vegbien.sql: schema comment: to sync the queries with schemas/vegbien.sql: use new public_validations.rm_output_queries() instead of rm_all_queries() to leave the input queries in place
schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries
bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)
schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query
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: public_validations: queries that use EXISTS: join locationevent.plot_id to plot.plot_id directly instead of going via location.plot_location_id
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: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id
schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.
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
bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: only include stemobservation records which have actual stem IDs, not merely stem-related measurements (DBH, etc.)
bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: LEFT JOIN to project instead of inner joining, to get Postgres to use the right query plan. this is the last change needed to make query #6 runnable.
bugfix: schemas/vegbien.sql: rematerialize_out_view(): run all queries with `SET enable_seqscan = off` to avoid slow query plans. this fixes _plots_06_list_of_plots_with_stem_measurements and significantly speeds up _plots_10_count_of_individuals_per_plot_in_each_project (and possibly others).
schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)
fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
bugfix: schemas/vegbien.sql: source_by_shortname(): documented that in some cases, it is actually a bad idea to use a nested SELECT, because this will prevent Postgres from using an index scan (causing an equally bad slowdown as not inlining in cases where a nested SELECT is required).
schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)
schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)
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
bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated
schemas/vegbien.sql: public_validations schema comment: documented how to remove a validations query so its columns can be changed (use public_validations.rm_query_view())
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: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)
fix: schemas/vegbien.sql: _plots_10_count_of_individuals_per_plot_in_each_project: fixed to handle subplots properly, using new locationevent.place_visit_id
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
bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix
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: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()
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_all_queries(schema): documented this function's unexpected behavior when schema = public_validations (it removes in all schemas, not just public_validations). this is because this uses rm_query_view().
fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries
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())
fix: schemas/vegbien.sql: public_validations: schema comment: removed incorrect "" in `SET search_path = "public_validations", pg_catalog;` now that they are no longer auto-added
schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
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)
fix: schemas/vegbien.sql: rm_all_queries(): all-schemas mode: also remove queries that are only present on the input side, so that the input side can also be re-created from the DDL file
fix: schemas/vegbien.sql: rm_all_queries(): documented that this actually does remove queries in all schemas when schema is NULL, because rm_query_view(public_validations.view) searches all schemas
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)
schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)
schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql
schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)
schemas/vegbien.sql: rm_query_group(): documented that this only removes validations output queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB
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: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)
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: validation_views(): moved filtering out of _validations schemas to validatable_datasources() so that calling this function with no args gives the validation views in *all schemas as one would expect
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
fix: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: count top-level plots to match the input query
schemas/vegbien.sql: remake_diff_table(): instructions for how to regenerate the diff table: put these at the beginning of the table comment instead of the end, because they are the most important info about the table that a user needs to know
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.
