bugfix: schemas/util.sql: mk_set_comment(): need to handle NULL comment properly
schemas/util.sql: added show_set_comment(regclass)
schemas/util.sql: set_comment(): use util.mk_set_comment()
schemas/Makefile: added vegbien/install, vegbien/uninstall which also handle TNRS
fix: schemas/Makefile: vegbien.sql: also remake inputs/.TNRS/schema.sql since its contents change along with vegbien.sql
schemas/util.sql: added mk_set_comment()
bugfix: schemas/util.sql: force_recreate(): recreate_users_cmds: use create_if_not_exists() rather than eval(), because cmd might manually re-create a deleted dependent view, causing it to already exist
bugfix: schemas/util.sql: save_drop_view(): util.show_create_view(): don't include `OR REPLACE` because that causes nonuniform errors (eg. invalid_table_definition), instead of the standard duplicate_table exception caught by util.create_if_not_exists()
fix: schemas/util.sql: show_create_view(): removed extra trailing ; because pg_get_viewdef() now includes one
schemas/util.sql: show_create_view(): made inclusion of `OR REPLACE` configurable
schemas/util.sql: save_drop_view(): use util.drop_view()
schemas/util.sql: save_drop_views(): return text[] instead of concatenated text so that the queries will be run individually and be easier to debug
schemas/util.sql: added eval(queries text[])
bugfix: schemas/util.sql: force_recreate(): need to drop users in reverse to avoid dependencies that block the drop
schemas/util.sql: added array_reverse()
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: 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.)
