Project

General

Profile

Statistics
| Revision:
  • svn:ignore: *.bak *.log *.changes.sql

# Date Author Comment
13745 06/13/2014 01:16 PM Aaron Marcuse-Kubitza

merged inputs/VegBIEN into schemas/VegBIEN, since for the purposes of the data dictionary URLs, VegBIEN is primarily an exchange schema

13738 06/12/2014 07:10 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: moved to schemas/VegBIEN/data_dictionary/

13737 06/12/2014 05:42 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: analytical_stem_view: approved by/to discuss: renamed to * (removing "approved by") because the participants in today's conference call indicated that they were happy with the definitions, and the issues raised before the call had been addressed

13736 06/12/2014 05:32 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added all tables used to create analytical_stem_view, not just those that Brian M listed. (Brian M generated his tables list from the analytical_stem_view columns rather than the joins, and renamed plot.** to plot, which is why his list ended up missing tables and in the wrong order.)

13734 06/12/2014 03:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: added 2014-6-12.Jeff_Ott.climatic_range_determinants (wiki.vegpath.org/Data_requests)

13732 06/12/2014 11:51 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: resized columns to fit laptop screen

13731 06/12/2014 10:43 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added my and Brian McGill's notes from the data dictionary conference call

13730 06/12/2014 08:00 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added analytical_stem_view table definitions

13729 06/12/2014 07:38 AM Aaron Marcuse-Kubitza

removed schemas/vegbien.ERD.MySQL_Workbench_6.1.6.mwb, because vegbien.ERD.mwb is now targeted to the correct version of MySQL Workbench (and is more up-to-date)

13728 06/10/2014 07:35 PM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database

13727 06/10/2014 07:20 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added "to discuss" column requested by Martha. resized columns to avoid truncating the viewFullOccurrence term names.

13726 06/10/2014 03:40 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: finished populating definitions of terms

13724 06/10/2014 02:30 PM Aaron Marcuse-Kubitza

added schemas/DwC/hist symlink to history/ to help fit URLs in the 63-char limit

13722 06/10/2014 11:38 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: finished populating viewFullOccurrence->analytical_stem_view and analytical_stem_view->viewFullOccurrence mappings

13721 06/10/2014 11:12 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: use new deletion comments (with () ) to indicate portions of external terms whose semantic meaning does not apply to our usage

13719 06/10/2014 10:59 AM Aaron Marcuse-Kubitza

web/username_prefix.php: comments: use [] instead of () because in writing, [] denotes insertion, while () (as an editing mark) denotes something to remove

13717 06/10/2014 10:15 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: removed extra rows and columns so that they aren't included when copying and pasting

13716 06/10/2014 10:11 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added tab to store viewFullOccurrence->analytical_stem_view mapping

13715 06/10/2014 09:55 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added viewFullOccurrence column

13713 06/09/2014 04:19 PM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: added links to editable and clickable versions of the spreadsheet, to enable switching back and forth between them. populated more definitions and links.

13708 06/07/2014 07:35 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: populated some definitions from VegCore data dictionary where available

13707 06/07/2014 07:20 AM Aaron Marcuse-Kubitza

bugfix: schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: restored "" that had been stripped out when the data was copied out and pasted back, due to a bug in Google spreadsheets. prepended : before initial " as a workaround to prevent the " getting stripped out again.

13706 06/07/2014 07:15 AM Aaron Marcuse-Kubitza

schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated

13705 06/07/2014 07:04 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: applied data dictionary renamings, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#apply-data-dictionary-renamings-to-database

13700 06/06/2014 11:30 PM Aaron Marcuse-Kubitza

fix: schemas/VegBIEN/VegBIEN data dictionary.xlsx: updated: restored "" that had been stripped out when the data was pasted in. set column widths to fit on a laptop screen.

13699 06/06/2014 11:20 PM Aaron Marcuse-Kubitza

added schemas/VegBIEN/VegBIEN data dictionary.xlsx

13693 06/06/2014 08:43 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: example terms from each source: added location in the VegBIEN core DB

13692 06/06/2014 08:15 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: terms in normalized schemas: put the source name right next to the term name (instead of after the table name) so it's easy to find

13691 06/06/2014 08:08 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: VegX terms: populated definitions

13690 06/06/2014 07:52 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: VegX terms: referenced to the specific table/subpath that they are in

13689 06/06/2014 07:32 AM Aaron Marcuse-Kubitza

bugfix: schemas/public_.sql: analytical_stem_view: locationName: re-sourced to VegX because this is actually not a DwC term

13688 06/06/2014 07:24 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: populated definitions of linked DwC terms from DwC data dictionary

13687 06/06/2014 07:09 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: renamed terms to link URLs, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-rename-terms-to-link-URLs

13686 06/06/2014 06:47 AM Aaron Marcuse-Kubitza

inputs/.TNRS/schema.sql: unsafe_taxon_names(): removed the name with "spp." now that this TNRS bug (https://pods.iplantcollaborative.org/jira/browse/TNRS-193) has been fixed

13685 06/06/2014 06:44 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: populated links from unrenamed term names, using the steps at wiki.vegpath.org/VegBIEN_schema_refactoring#data-dictionary-populate-links-from-unrenamed-term-names

13684 06/06/2014 06:02 AM Aaron Marcuse-Kubitza

fix: schemas/public_.sql: analytical_stem_view: links: removed = before them because the link is in fact one member in a list of definitions (the = would apply to all definitions, not just the link, and since it apply to all definitions, it's redundant because of course the comment is what the column equals)

13670 06/06/2014 02:44 AM Aaron Marcuse-Kubitza

fix: schemas/public_.sql: analytical_stem_view: vegpath.org links: use ; instead of : between link and definition, because it makes more grammatical sense

13669 06/06/2014 02:42 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: vegpath.org links: removed no longer needed // suffix

13663 06/06/2014 12:08 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: added links and definitions for half a dozen of the terms from various sources

13655 06/05/2014 09:02 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: indicated provenance of the rest of the terms

13654 06/05/2014 08:32 AM Aaron Marcuse-Kubitza

schemas/public_.sql: analytical_stem_view: flagged the DwC terms

13648 06/05/2014 07:30 AM Aaron Marcuse-Kubitza

schemas/public_.sql: added plot.**.modify()

13647 06/05/2014 07:24 AM Aaron Marcuse-Kubitza

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

13646 06/05/2014 07:21 AM Aaron Marcuse-Kubitza

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

13645 06/05/2014 07:15 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: view_def_to_orig(): also need to merge .* expressions resulting from a SELECT * of a join, to avoid duplicated columns

13644 06/05/2014 06:52 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: view_def_to_orig(): require at least 6 cols to avoid false positives in the expansion match pattern

13643 06/05/2014 06:49 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: view_def_to_orig(): 1st col: can't prepend \y because it considers only \w chars, not "

13642 06/05/2014 06:35 AM Aaron Marcuse-Kubitza

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

13641 06/05/2014 06:34 AM Aaron Marcuse-Kubitza

schemas/util.sql: eval(): restore user's intent by running util.view_def_to_orig() on the query to unexpand expanded * expressions

13640 06/05/2014 05:50 AM Aaron Marcuse-Kubitza

schemas/util.sql: pg_get_viewdef(): use util.view_def_to_orig()

13639 06/05/2014 05:50 AM Aaron Marcuse-Kubitza

schemas/util.sql: added view_def_to_orig()

13638 06/05/2014 05:46 AM Aaron Marcuse-Kubitza

fix: schemas/util.sql: pg_get_viewdef(): should be STABLE because it references system catalogs

13637 06/05/2014 05:44 AM Aaron Marcuse-Kubitza

schemas/util.sql: added pg_get_viewdef() wrapper, which unexpands expanded * expressions

13635 06/05/2014 03:57 AM Aaron Marcuse-Kubitza

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.

13634 06/05/2014 03:50 AM Aaron Marcuse-Kubitza

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)

13633 06/05/2014 03:45 AM Aaron Marcuse-Kubitza

schemas/public_.sql: taxonlabel: added index on taxonomicname to facilitate retrieving rows from tnrs_input_name

13632 06/05/2014 03:39 AM Aaron Marcuse-Kubitza

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

13631 06/05/2014 03:30 AM Aaron Marcuse-Kubitza

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

13610 06/03/2014 11:36 AM Aaron Marcuse-Kubitza

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)

13609 06/03/2014 10:36 AM Aaron Marcuse-Kubitza

bugfix: inputs/.TNRS/schema.sql: MatchedTaxon_modify(): updated to include taxon_scrub derived fields

13608 06/03/2014 10:25 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: mk_drop_from_create(): need to match first rather than last CREATE

13607 06/03/2014 10:21 AM Aaron Marcuse-Kubitza

inputs/.TNRS/schema.sql: *_modify(): allow running without a view_query, as recreate_view() now supports this

13606 06/03/2014 09:52 AM Aaron Marcuse-Kubitza

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)

13605 06/03/2014 08:56 AM Aaron Marcuse-Kubitza

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.

13604 06/03/2014 08:52 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: recreate(): need to handle case where util.mk_drop_from_create() is NULL

13603 06/03/2014 08:45 AM Aaron Marcuse-Kubitza

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

13602 06/03/2014 08:29 AM Aaron Marcuse-Kubitza

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

13601 06/03/2014 08:19 AM Aaron Marcuse-Kubitza

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

13600 06/03/2014 08:15 AM Aaron Marcuse-Kubitza

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

13599 06/03/2014 07:39 AM Aaron Marcuse-Kubitza

schemas/util.sql: added copy()

13598 06/03/2014 07:36 AM Aaron Marcuse-Kubitza

schemas/util.sql: added copy_data()

13589 06/02/2014 04:27 AM Aaron Marcuse-Kubitza

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)

13588 06/02/2014 04:08 AM Aaron Marcuse-Kubitza

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

13587 06/02/2014 03:43 AM Aaron Marcuse-Kubitza

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

13586 06/01/2014 09:00 PM Aaron Marcuse-Kubitza

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

13585 06/01/2014 05:00 AM Aaron Marcuse-Kubitza

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)

13584 06/01/2014 04:36 AM Aaron Marcuse-Kubitza

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

13583 06/01/2014 04:35 AM Aaron Marcuse-Kubitza

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

13582 06/01/2014 03:44 AM Aaron Marcuse-Kubitza

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

13581 06/01/2014 03:41 AM Aaron Marcuse-Kubitza

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

13580 05/31/2014 10:31 PM Aaron Marcuse-Kubitza

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

13578 05/31/2014 10:25 PM Aaron Marcuse-Kubitza

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

13577 05/31/2014 09:50 PM Aaron Marcuse-Kubitza

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

13576 05/31/2014 08:58 PM Aaron Marcuse-Kubitza

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

13573 05/31/2014 08:10 PM Aaron Marcuse-Kubitza

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

13571 05/31/2014 08:03 PM Aaron Marcuse-Kubitza

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

13570 05/31/2014 06:44 PM Aaron Marcuse-Kubitza

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

13569 05/31/2014 06:29 PM Aaron Marcuse-Kubitza

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

13566 05/30/2014 06:22 PM Aaron Marcuse-Kubitza

schemas/util.sql: added seq__reset()

13565 05/30/2014 06:15 PM Aaron Marcuse-Kubitza

schemas/util.sql: added seq__create()

13564 05/30/2014 06:11 PM Aaron Marcuse-Kubitza

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

13561 05/30/2014 07:42 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: regenerated exports

13560 05/30/2014 07:39 AM Aaron Marcuse-Kubitza

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.

13559 05/30/2014 07:37 AM Aaron Marcuse-Kubitza

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

13558 05/30/2014 06:58 AM Aaron Marcuse-Kubitza

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.

13557 05/30/2014 05:58 AM Aaron Marcuse-Kubitza

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

13556 05/30/2014 05:27 AM Aaron Marcuse-Kubitza

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

13555 05/30/2014 05:17 AM Aaron Marcuse-Kubitza

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

13554 05/30/2014 05:05 AM Aaron Marcuse-Kubitza

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

13551 05/30/2014 03:30 AM Aaron Marcuse-Kubitza

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