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
lib/tnrs.py: max_names: raised back up to 500 now that a workaround for the Internal Server Errors is in place (https://github.com/iPlantCollaborativeOpenSource/TNRS/issues/7)
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()
fix: lib/tnrs.py: max_names: lowered to 50 because the dev TNRS server is now always crashing with an Internal Server Error when scrubbing 500 names at a time (https://github.com/iPlantCollaborativeOpenSource/TNRS/issues/7)
web/.htaccess: auto-detect dotpath in query string: also auto-prepend & for query strings appended to a username-based prefix, so that these don't need to have & manually prepended to make them appendable
web/.htaccess: auto-detect dotpath in query string: also auto-detect dotpath for query strings appended to a username-based prefix, so that these don't need to have . prepended to make them appendable
web/username_prefix.php: no prefix: use $_SERVER["SCRIPT_URL"] instead of rtrimming $_SERVER["REQUEST_URI"]
web/username_prefix.php: has prefix: use same expr whether or not it has a query string, because the with-query-string expr works for both cases
web/username_prefix.php: use if statements instead of `? :` for clarity
web/username_prefix.php: use $subpath instead of $_SERVER["PHP_AUTH_USER"] for clarity
bugfix: **/.htaccess: redirects with fragment: qsappend does not support fragment, so append it separately
web/.htaccess, username_prefix.php: username-based prefix subpaths: support dotpath in query string (which will be appended verbatim to the prefix). use "__?@__" instead of "__?__&" to signal username-based prefix, so that the query string does not end in a dangling & .
bugfix: inputs/.TNRS/.htaccess: qsappend does not support fragment, so append it separately
bugfix: web/.htaccess: auto-detect dotpath in query string: don't exclude dotpaths that come before a normal query string (&k=v...)
web/.htaccess: auto-detect dotpath in query string: documented what each RewriteCond does
web/.htaccess: auto-detect dotpath in query string: use %{QUERY_STRING} instead of %0 for clarity
web/.htaccess, web/username_prefix.php: username-based prefix subpaths: support query strings in addition to paths
web/index.php: removed no longer needed redirection of username-based prefix subpaths, which is now handled by web/.htaccess
web/.htaccess: handle username-based prefix subpaths (of the form "subpath@url?") for all paths rather than just for the root (which were handled by index.php)
added web/username_prefix.php, for use with username-based prefix redirects
web/index.php: subpath@host? redirects: also support multiple @ and nested . in prefix: a@b.c@host? -> host?b.c.a
web/index.php: support prefix redirect of the form subpath@host? , which is better than subpath.host because case is preserved and special chars allowed. (must require trailing ? for redirect, to avoid needing login to view the page itself.)
bugfix: web/index.php: full directory index: appending query string: need to use $_SERVER["QUERY_STRING"], not $_SERVER["HTTP_AUTHORIZATION"] for this
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: lib/tnrs.py: Constrain by Source: turn it on so that the download settings reflect what TNRS actually used, while this is broken
fix: lib/tnrs.py: max_names: reduced back to 500 because even 5000 crashes the dev TNRS server
lib/tnrs.py: max_names: reduced to 5000 because 100,000 causes an internal server error
bugfix: /README.TXT: Full database import: To run TNRS: to rescrub all names: also need to re-create public-schema views that were cascadingly deleted
/README.TXT: Full database import: To run TNRS: added steps to rescrub all names
backups/TNRS.backup.md5: updated
lib/tnrs.py: switched to downloading all matches per name, as is needed to implement #917. note that this will break the parts of the schema that use the tnrs table, until Brad's match-picking algorithm can be implemented, but this tradeoff is necessary to be able to begin scrubbing sooner (Martha; wiki.vegpath.org/2014-05-29_conference_call#TNRS)
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: 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)
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/test_taxonomic_names/_scrub/*: updated to TNRS schema
inputs/.TNRS/schema.sql: tnrs: added match_num
inputs/.TNRS/data.sql.run: refresh(): documented runtime (1 min)
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)
exports/2014-3-11.Jeff_Ott.climatic_range_determinants.csv.run: documented export_() runtime (11 min) and rowcount (11 million matching the filter criteria)
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`
lib/tnrs.py: max_names: increased to 100000 because the dev server can handle more names (no simultaneous users), as decided in the conference call (wiki.vegpath.org/2014-05-29_conference_call#TNRS)
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
removed no longer used config/VirtualBox_VMs/VegCore ERD/. use the Ubuntu 14.04 VM instead, which now has the VegCore ERD setup.
config/VirtualBox_VMs/Ubuntu 14.04/Ubuntu 14.04.vbox: added VegCore ERD setup
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
lib/tnrs.py: commented out the value of max_names that is not active, for clarity
config/VirtualBox_VMs/vegbiendev/vegbiendev.vbox: updated: merged post-bootloader installation steps into one snapshot now that eth1 addition is successful
config/VirtualBox_VMs/vegbiendev/vegbiendev.vbox: updated, which adds eth1