schemas/util.sql: added in_reverse()

bugfix: schemas/util.sql: force_recreate(): need to use :lower:, not [:lower:]

schemas/util.sql: force_recreate(): debug-print PG_EXCEPTION_DETAIL, users to assist in debugging

fix: schemas/util.sql: debug_print_value(): added trailing newline to visually distinguish the printed value

schemas/util.sql: added debug_print_var()

schemas/util.sql: debug_print_return_value(): use new util.debug_print_value()

schemas/util.sql: added debug_print_value()

fix: schemas/util.sql: util.force_recreate(): also support dropping things besides tables

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

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

bugfix: schemas/Makefile: `%/install: public_.sql`: also need to replace `public` when preceded by ( , as it is in the validations queries

bugfix: schemas/util.sql: explain2notice_msg_if_can(): don't use util.is_explainable() because the list provided by Postgres (http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691) excludes some query types that are in fact EXPLAIN-able

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

bugfix: schemas/util.sql: materialize_query(): contents generated from: need to include search_path so query is runnable

schemas/vegbien.sql: _plots_21_tnrs_output_names: use scrubbed_unique_taxon_name (concatenated output name) because this is more similar to the concatenated name used in _plots_20_tnrs_input_names

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

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.

schemas/vegbien.ERD.mwb: regenerated exports

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.ERD.mwb: regenerated exports

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