inputs/.TNRS/schema.sql: *_modify(): removed the need to manually maintain copies of the dependent view definitions with the *s in place, because the *s are now added automatically by view_def_to_orig()
fix: schemas/util.sql: view_def_to_orig(): also need to merge .* expressions resulting from a SELECT * of a join, to avoid duplicated columns
fix: schemas/util.sql: view_def_to_orig(): require at least 6 cols to avoid false positives in the expansion match pattern
bugfix: schemas/util.sql: view_def_to_orig(): 1st col: can't prepend \y because it considers only \w chars, not "
bugfix: schemas/util.sql: view_def_to_orig(): don't match whitespace in the middle of a "" identifier, as this could throw off the parser
schemas/util.sql: eval(): restore user's intent by running util.view_def_to_orig() on the query to unexpand expanded * expressions
schemas/util.sql: pg_get_viewdef(): use util.view_def_to_orig()
schemas/util.sql: added view_def_to_orig()
fix: schemas/util.sql: pg_get_viewdef(): should be STABLE because it references system catalogs
schemas/util.sql: added pg_get_viewdef() wrapper, which unexpands expanded * expressions
fix: schemas/public_.sql: tnrs_input_name: added ORDER BY to ensure a stable sort order. this also creates better query plans by enabling merge joins.
bugfix: schemas/public_.sql: tnrs_input_name: need to exclude unsafe taxon names as a workaround to #935 (TNRS crashes when scrubbing names with two infraspecific epithets)
schemas/public_.sql: taxonlabel: added index on taxonomicname to facilitate retrieving rows from tnrs_input_name
inputs/.TNRS/schema.sql: added taxon_name_is_safe()
inputs/.TNRS/schema.sql: added unsafe_taxon_names()
schemas/public_.sql: sync_*(): renamed to *_modify() to facilitate finding these functions when modifying the corresponding view (using the new naming convention for a view's on-modify function)
bugfix: inputs/.TNRS/schema.sql: MatchedTaxon_modify(): updated to include taxon_scrub derived fields
bugfix: schemas/util.sql: mk_drop_from_create(): need to match first rather than last CREATE
inputs/.TNRS/schema.sql: *_modify(): allow running without a view_query, as recreate_view() now supports this
schemas/util.sql: recreate_view(): support omitting the view_query if the view has already been modified (eg. for public.*_view, which allow changing the view as a separate step)
fix: schemas/public_.sql: sync_*(): use util.copy() instead of CREATE TABLE AS so that table and column comments are also copied. this avoids the need to separately add the same comments to the view and its materialized table.
bugfix: schemas/util.sql: recreate(): need to handle case where util.mk_drop_from_create() is NULL
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
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: 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