bugfix: schemas/public_.sql: _plots_21_tnrs_output_names: need to use TNRS output rather than input name
schemas/public_.sql: added _plots_20_tnrs_input_names, _plots_21_tnrs_output_names
schemas/public_.sql: public_validations: schema comment: documented how to run just the output queries on a particular datasource
fix: schemas/public_.sql: rm_all_queries(): also remove output-only result tables
schemas/public_.sql: query_view_regexp(): also match auxiliary tables
schemas/public_.sql: added remake_output_tables(datasource_schema, prefix)
schemas/util.sql: added loop_ignore_errors()
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/public_.sql: query_relations(): use new public_validations.truncated_prefixed_name_regexp(), which does not require passing in a max_prefix_len
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()
bugfix: schemas/public_.sql: public_validations.rm_output_queries(): default regexp needs to be anchored at beginning of string, and should match the regexp in public_validations.validation_views()
schemas/public_.sql: _km_to_m(): made it a wrapper around util._km_to_m() so this function is only in one place
schemas/util.sql: added _km_to_m() for use when public._km_to_m() is not defined yet
schemas/postgresql.conf: wal_level: set to hot_standby to enable online backup with pg_start_backup()
schemas/vegbien.ERD.mwb: regenerated exports
fix: lib/PostgreSQL-MySQL.csv: preserve schema assignments by translating `SET search_path` to `USE`
schemas/vegbien.ERD.mwb: added geoscrub, TNRS tables, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema)
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.
bugfix: inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13*: also need to include coordinate pairs which have one of their coordinates NULL, by using OR instead of AND
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
fix: schemas/vegbien.sql: public_validations schema comment: to remove a validations query so its columns can be changed: use rm_output_queries() rather than rm_query_view() because that also removes input queries
bugfix: schemas/util.sql: is_castable(): need to pass NULL through, for proper NULL propagation
schemas/util.sql: added is_castable()
schemas/util.sql: added try_cast()
schemas/util.sql: added util.cast(), which allows casting to an arbitrary type without eval()
bugfix: schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: DISTINCT: added coordsaccuracy_m
bugfix: schemas/vegbien.sql: coordinates_unique: added coordsaccuracy_m
fix: schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: need to DISTINCT the values that are being counted, because the coordinates_unique unique constraint includes other columns as well, so there may be multiple instances of each lat/long
fix: schemas/vegbien.sql: specimens*_of_unique_verb_subsp_taxa_with_author: include only names with subspecies (filtering by taxonverbatim.subspecies rather than taxonlabel.taxonomicname)
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
bugfix: schemas/util.sql: remake_diff_table(): need to rm_freq() type_table, because left/right_table don't have freq yet
schemas/util.sql: auto_rm_freq(): use new rm_freq()
schemas/util.sql: added rm_freq(regclass[])
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/util.sql: remake_diff_table(): result table comment: documented how to display NULL values that are extra or missing
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
*{.sh,run}: runscript targets: use begin_target instead of echo_func so the target name is properly echoed. note that this requires using with_rm so that $rm is properly progagated to applicable invoked targets. (previously, $rm was progagated to all invoked targets. note that with_rm only works inside a runscript target that starts with begin_target.)
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)
fix: schemas/VegCore/mk_derived: added `EOF` at end to avoid (benign) "here-document delimited by end-of-file" warnings on Linux
fix: schemas/util.sql: trim(): by default, cascadingly drop dependent columns so that they don't prevent trim() from succeeding. note that this requires the dependent columns to then be manually re-created.
added schemas/VegCore.ERD.pdf symlink for easy access
bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)
*{.sh,run}: use new begin_target instead of `echo_func; set_make_vars`
fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output
schemas/util.sql: added fold_explain_msg()
bugfix: schemas/util.sql: set_col_types(): need to COALESCE the executed SQL to '' because util.eval() does not support NULL (and shouldn't, because this indicates a missing COALESCE in constructing the statement)
schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()
schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY) for clarity
schemas/util.sql: added mk_not_null()
added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad
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.)
bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query
fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)
schemas/util.sql: try_create(): also handle "child table is missing column" errors
schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE
schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block
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
schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types
bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())
bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
schemas/util.sql: added debug_print_func_call(text) and use it where applicable
schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match
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/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.
bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if all tables have all 1s
schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if all tables have all 1s
schemas/util.sql: added freq_always_1(regclass[])
