bugfix: schemas/util.sql: mk_drop_from_create(): only match CREATE if no custom DROP came before it

bugfix: schemas/public_.sql: sync_geoscrub_input_to_view(): `CREATE TABLE geoscrub_input AS __`: needs `LIMIT 0`

fix: schemas/util.sql: explain2notice_msg_if_can(): also need to catch invalid_cursor_definition ("cannot open multi-query plan as cursor")

schemas/public_.sql: sync_analytical_stem_to_view(): removed DROP TABLE IF EXISTS because this is now done automatically by util.recreate()

schemas/util.sql: added copy()

schemas/util.sql: added copy_data()

fix: inputs/.TNRS/schema.sql: added back index on Name_submitted, which is needed for tnrs_input_name to work properly (now that there is no automatic index created by a unique constraint)

schemas/vegbien.sql: tnrs_input_name: don't scrub accepted names, as using multiple matches per name no longer provides a single accepted name to scrub. instead, the Accepted_* fields can be whitespace-split to generate the same columns that would have been generated by the scrubbing (and without the overhead of the extra TNRS call).

fix: inputs/.TNRS/schema.sql: tnrs: removed unique constraint on Name_submitted, Name_matched because there can be more than one match with the same Name_matched (but different accepted names, etc.)

fix: inputs/.TNRS/schema.sql: tnrs.tnrs__valid_match index: made it non-unique to allow multiple matches per name, as is needed to implement #917

bugfix: inputs/.TNRS/schema.sql: tnrs__match_num__fill(): only fill if not set, to support case where tnrs is being restored from a .sql file (where match_num is already set)

inputs/.TNRS/schema.sql: tnrs: documented runtime to add a constraint (3 min)

inputs/.TNRS/schema.sql: unique constraint on Name_submitted: added Name_matched to allow multiple matches per name, as is needed to implement #917

inputs/.TNRS/schema.sql: tnrs: documented how to populate a new column

inputs/.TNRS/schema.sql: tnrs: pkey: use match_num instead of Name_number to allow multiple matches per name, as is needed to implement #917

inputs/.TNRS/schema.sql: tnrs.match_num: made it NOT NULL now that it's populated

inputs/.TNRS/schema.sql: tnrs: populate match_num

inputs/.TNRS/schema.sql: tnrs: documented how to add and remove columns

inputs/.TNRS/schema.sql: made COMMENTs start on their own line, using the steps at wiki.vegpath.org/Postgres_queries#make-COMMENTs-start-on-their-own-line

inputs/.TNRS/schema.sql: tnrs: added match_num

schemas/Makefile: added back rename/%, which is used by `inputs/.TNRS/data.sql.run refresh`. updated it to use schema bundles.

inputs/.TNRS/schema.sql: added tnrs__match_num__next()

inputs/.TNRS/schema.sql: added tnrs__batch_begin() trigger to populate the match_num (match sort order)

schemas/util.sql: added seq__reset()

schemas/util.sql: added seq__create()

fix: schemas/util.sql: try_cast(), is_castable(): also catch invalid_schema_name, thrown by `'pg_temp.__'::regclass`

schemas/vegbien.ERD.mwb: regenerated exports

schemas/vegbien.ERD.mwb: re-updated to schemas/vegbien.my.sql, which now recognizes the broken tables. fixed sync issues. vegbien.ERD.mwb is now fully in sync with vegbien.my.sql.

fix: lib/PostgreSQL-MySQL.csv: need to replace "double precision" with "double" to work with MySQL Workbench 5.2.47

schemas/vegbien.ERD.mwb: updated to schemas/vegbien.my.sql. some tables weren't recognized (likely due to bugs in MySQL Workbench 5.2.47), and have been left as-is (unsynced). note that downgrading to 5.2.35 is not an option, because that is fatally broken by a system upgrade.

fix: schemas/vegbien.ERD.mwb: use schemas/vegbien.my.sql instead of schemas/vegbien.my.sql.changes.sql as the sync source

schemas/vegbien.ERD.mwb: switched back to MySQL Workbench 6.1.6 version, which also works with MySQL Workbench 5.2.47

schemas/vegbien.ERD.mwb: restored version for MySQL Workbench 5.2.35 (undid r13549), as 6.1.6 has bugs in the DDL file sync

schemas/vegbien.ERD.mwb: renamed to vegbien.ERD.MySQL_Workbench_6.1.6.mwb to differentiate the versions for different versions of MySQL Workbench

schemas/: svn:ignore *.changes.sql, needed for MySQL Workbench 6.1.6

schemas/vegbien.ERD.mwb: updated

schemas/vegbien.ERD.mwb: updated layout for MySQL Workbench 6.1.6, which uses a different line spacing

schemas/public_.sql: added 2014-3-11.Jeff_Ott.climatic_range_determinants

schemas/public_.sql: analytical_stem_view: added scrubbed_taxon_name_with_author, needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

inputs/.TNRS/schema.sql: taxon_scrub.scrubbed_unique_taxon_name.*: added scrubbed_taxon_name_with_author, needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

fix: schemas/public_.sql: sync_analytical_stem_to_view(): removed fkey to source.shortname because this prevents reloading individual datasources

schemas/public_.sql: added scrubbed_specific_epithet, scrubbed_species_binomial, which are needed by Jeff Ott's analysis (wiki.vegpath.org/Data_requests)

fix: schemas/util.sql: mk_drop_from_create(): also support CREATE queries that include the SELECT statement on the same line as the CREATE

schemas/public_.sql: analytical_stem_view: scrubbed_morphospecies_binomial: use new taxon_scrub.scrubbed_morphospecies_binomial

inputs/.TNRS/schema.sql: taxon_scrub: added scrubbed_morphospecies_binomial, analogous to accepted_morphospecies_binomial for scrubbed_*

inputs/.TNRS/schema.sql: taxon_scrub: added scrubbed_morphospecies_binomial, analogous to accepted_morphospecies_binomial for scrubbed_*

inputs/.TNRS/schema.sql: taxon_scrub: documented how to modify it

inputs/.TNRS/schema.sql: added taxon_scrub_modify()

schemas/util.sql: create_if_not_exists(): print message if already exists, so the function doesn't inexplicably appear not to have run at all

inputs/.TNRS/schema.sql: MatchedTaxon_modify(): use simpler util.recreate_view()

inputs/.TNRS/schema.sql: MatchedTaxon_modify(): documented usage

schemas/util.sql: added recreate_view(), a special case of util.recreate()

fix: schemas/util.sql: recreate(): usage: use `schema` instead of `schemas`

bugfix: schemas/public_.sql: _plots_20_tnrs_names: verbatim_name_with_author: use taxonverbatim.taxonomicname rather than taxonlabel.taxonomicname

inputs/.TNRS/schema.sql: MatchedTaxon_modify(): removed no longer needed DROP VIEW statement

schemas/util.sql: recreate(): perform the correct DROP VIEW in the function itself so that the caller does not have to worry about forming it properly

bugfix: schemas/util.sql: mk_drop_from_create(): added `DROP`

schemas/util.sql: added mk_drop_from_create()

schemas/util.sql: added regexp_match()

fix: schemas/util.sql: force_recreate(): renamed to just recreate(), because "force" normally implies that things will be deleted, which this function does not do

fix: schemas/public_.sql: _plots_20_tnrs_names: fixed order of tnrs_taxonomic_status, tnrs_accepted_name_family

schemas/public_.sql: _plots_20_tnrs_names: added tnrs_taxonomic_status, as requested in issue #915

schemas/public_.sql: analytical_stem_view: taxonomic_status: don't filter with map_taxonomic_status() anymore since this is now done by "TNRS"."MatchedTaxon"

fix: inputs/.TNRS/schema.sql: MatchedTaxon.taxonomicStatus: filter using map_taxonomic_status() so that the corrected value is available in the normalized DB, not just analytical_stem

inputs/.TNRS/schema.sql: MatchedTaxon: to modify: use new MatchedTaxon_modify(), which eliminates the work of putting together the dependent views

inputs/.TNRS/schema.sql: added MatchedTaxon_modify()

schemas/util.sql: show_create_view(): use new mk_set_relation_metadata()

schemas/util.sql: added mk_set_relation_metadata()

bugfix: inputs/.TNRS/schema.sql: map_taxonomic_status(): need to use accepted name instead of scrubbed name (which also includes no-opinion names), as described at http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields. this used to be the accepted name, but got switched when the concatenated name was also used to store the matched name for no-opinion names.

schemas/public_.sql: _plots_20_tnrs_names: use accepted_morphospecies_binomial, which is now available directly in the TNRS results, instead of just the morphospecies_suffix, as requested in issue #915

inputs/.TNRS/schema.sql: MatchedTaxon: documented how to modify it (using util.force_recreate())

schemas/util.sql: force_recreate(): documented usage, which is somewhat complex and has several important parts

inputs/.TNRS/schema.sql: MatchedTaxon, etc.: added accepted_morphospecies_binomial derived field

bugfix: schemas/util.sql: restore_views(): need to specify view name for a manual existence check, in case the view definition becomes invalid, which would produce a nonstandard (uncatchable) exception

bugfix: schemas/util.sql: explain(): don't display any log messages printed by IMMUTABLE functions in the query, which EXPLAIN apparently runs

schemas/util.sql: create_if_not_exists(): added relation param, which can be used to ensure that a standard exception is always generated if the relation exists, even if the table definition would be invalid (which generates a variety of exceptions)

bugfix: schemas/util.sql: try_cast(), is_castable(): also need to handle syntax_error_or_access_rule_violation, which is thrown by ::regclass

schemas/util.sql: added relation_exists()

schemas/util.sql: save_drop_views()/restore_views(): store the view path in addition to the definition so it can be used in restoring

bugfix: schemas/util.sql: in_reverse(): can't use `SELECT *` for composite types because this expands them

bugfix: schemas/util.sql: array_reverse(): can't use `SELECT *` for composite types because this expands them

schemas/util.sql: save_drop_views()/restore_views(): use opaque return type so it can be changed without changing the local var type in functions that use this

schemas/util.sql: force_recreate(): recreate_users_cmds: renamed to restore_views_info since this is now whatever is needed by util.restore_views()

schemas/util.sql: added restore_views() and use it in force_recreate()

bugfix: schemas/util.sql: save_drop_views(): views must be dropped in reverse dependency order, but returned in forward dependency order

bugfix: schemas/util.sql: force_recreate(): dependent views need to be re-created in forward dependency order. this is done by reversing the dependencies only for save_drop_views().

bugfix: schemas/util.sql: mk_set_comment(): need to use VIEW instead of TABLE for views

bugfix: schemas/util.sql: show_create_view(): also need to include view comment

fix: schemas/util.sql: mk_set_comment(): added ;

fix: schemas/util.sql: show_set_comment(): don't display a COMMENT ON statement if no comment, because this will be appended to table defs, etc. and would create clutter

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