Activity
From 09/13/2013 to 10/12/2013
10/12/2013
- 04:59 PM Revision 11262: planning/timeline/timeline.2013.xls: updated for progress
- 04:20 PM Revision 11261: inputs/VegBank/verify/input_cols.txt, inputs/VegBank/+taxon_observation.**.sample/create.sql: updated to match taxon_observation.** columns
- 04:16 PM Revision 11260: inputs/VegBank/verify/input_cols.txt, inputs/VegBank/+taxon_observation.**.sample/create.sql: updated to match taxon_observation.** columns
- 03:49 PM Revision 11259: /README.TXT: Maintenance: to synchronize vegbiendev, jupiter, and your local machine: run all sync_uploads on the svn working copy using --size-only, because the mtimes are based on when the files were last updated by svn and are not meaningful
- 03:46 PM Revision 11258: /README.TXT: Full database import: On local machine: do steps under Maintenance > "to synchronize vegbiendev, jupiter, and your local machine": removed no longer accurate indicator that these steps are above Full database import, since Full database import is now at the beginning of the file
- 03:05 PM Revision 11257: inputs/VegBank/verify/input_cols.txt, inputs/VegBank/+taxon_observation.**.sample/create.sql: updated to match taxon_observation.** columns
- 03:03 PM Revision 11256: bugfix: inputs/VegBank/+taxon_observation.**.sample/: renamed to ^taxon_observation.**.sample because a leading + has a special meaning to bash (it indicates a shell option, and you will get an error "invalid option name"), as well as to make (it indicates that a recipe command invokes make recursively)
- 02:27 PM Task #767 (New): autodetect column mismatches
- * if @map.csv@ does not contain the @header.csv@ columns in exactly the right order, this will mess up the import in ...
- 02:14 PM Revision 11255: bugfix: inputs/VegBank/taxon_observation.**/header.csv: updated for observation_/map.csv bugfix, which added new hasobservationsynonym field. this fixes a strange test bug caused by the taxon_observation.**/map.csv column list being mismatched/misaligned with what was in the underlying tables. (column mismatches will often cause unexplainable errors in unrelated sections of code the same way that buffer overflows do in C++.)
- 02:01 PM Revision 11254: bugfix: inputs/VegBank/taxon_observation.**.sample/: renamed to +taxon_observation.**.sample so that the *-expansion of taxon_observation.** doesn't add taxon_observation.**.sample (which causes it to attempt to install taxon_observation.**.sample before taxon_observation.** is installed)
- 12:48 PM Revision 11253: bugfix: *Makefile: recursive invocation of $(MAKE): enclose targets in "" in case they contain *
- 12:12 PM Revision 11252: bugfix: lib/runscripts/table.run: load_data(): pass $is_view through to `make reinstall` so that DROP VIEW will be used instead of DROP TABLE where applicable
- 12:11 PM Revision 11251: bugfix: inputs/input.Makefile: %/uninstall: allow user to set is_view=1 flag to use DROP VIEW instead of DROP TABLE
- 12:08 PM Revision 11250: lib/sh/util.sh: added instructions for making an export only visible locally
10/10/2013
- 06:50 PM Revision 11249: bugfix: inputs/VegBank/observation_/header.csv, map.csv: updated for refresh, which inserts hasobservationsynonym at the end of the observation table
- 05:46 PM Revision 11248: inputs/VegBank/taxon_observation.**.sample/create.sql: reordered columns in the same order as analytical_plot, for easier validation
- 05:46 PM Revision 11247: bugfix: lib/runscripts/table.run: load_data(): in remaking mode, need to remake header.csv in case the columns have changed
- 03:07 PM Revision 11246: web/links/index.htm: updated to Firefox bookmarks. updated favicons.
- 02:57 PM Revision 11245: web/links/index.htm: updated to Firefox bookmarks. sudo: added instructions to turn off incorrect password e-mails.
- 02:40 PM Revision 11244: inputs/VegBank/taxon_observation.**.sample/create.sql: include only the subset of columns that is imported to VegBIEN
- 02:32 PM Revision 11243: inputs/VegBank/taxon_observation.**.sample/test.xml.ref: updated inserted row count (which was most likely generated before the output column names had been set to the input column names)
- 01:55 PM Revision 11242: added inputs/VegBank/verify/input_cols.include.txt, with runscript to generate it
- 01:26 PM Revision 11241: inputs/VegBank/verify/input_cols.unmapped.txt*: renamed to input_cols.exclude.txt* because this now includes mapped columns as well
- 01:18 PM Revision 11240: inputs/VegBank/verify/input_cols.unmapped.txt*: renamed to input_cols.exclude.txt* because this now includes mapped columns as well
- 01:11 PM Revision 11239: inputs/VegBank/verify/input_cols.unmapped.txt.run: remove unmapped join columns, since these *would* be included in the extract
- 01:09 PM Revision 11238: inputs/VegBank/verify/input_cols.unmapped.txt.run: take input directly from input_cols.txt to avoid needing to first copy and paste it into input_cols.unmapped.txt
- 01:03 PM Revision 11237: inputs/VegBank/verify/input_cols.unmapped.txt.run: added back deliberately excluded columns (DUPLICATE#of:..., etc.) so that the # of rows in the file can be subtracted from the total # of columns to get the # of input columns that would be included in the extract
- 12:43 PM Revision 11236: bugfix: inputs/input.Makefile: %/VegBIEN.csv: `ln -s` to create VegBIEN.csv: enclose the filenames in "" since they may contain * (e.g. taxon_observation.**)
- 12:23 PM Revision 11235: added inputs/VegBank/verify/input_cols.txt, input_cols.unmapped.txt (with runscript to filter input_cols.unmapped.txt)
- 11:53 AM Revision 11234: schemas/VegBIEN/attribution/BIEN 3 data use and attribution.docx: made Ramona's corrections with track changes turned on. note that you have to use MS Word for this, not LibreOffice, because LibreOffice can't save the table of contents properly in .docx or .doc format (although it can save it in .odt format).
- 08:18 AM Revision 11233: inputs/VegBank/stratum/postprocess.sql: added pkey
- 08:05 AM Revision 11232: inputs/VegBank/taxonobservation_/postprocess.sql: added __parent index on locationID to facilitate the LEFT JOINs used to create the validation input
- 07:54 AM Revision 11231: inputs/VegBank/observation_/postprocess.sql: added __parent index on locationID to facilitate the LEFT JOINs used to create the validation input
- 07:45 AM Revision 11230: inputs/VegBank/import_order.txt: added taxon_observation.**.sample so it will automatically be kept up to date
- 07:32 AM Revision 11229: inputs/VegBank/taxon_observation.**.sample/create.sql: set runtime (1 s)
- 07:30 AM Revision 11228: inputs/VegBank/: added taxon_observation.**.sample subset of plots to use in the validation. this avoids the need to import all of VegBank just to validate a few of the plots.
10/09/2013
- 10:12 PM Revision 11227: bin/map: usage: documented that verbosity > 3 in commit mode turns on debug_temp mode, which creates real tables instead of temp tables
- 09:41 PM Revision 11226: schemas/VegCore/ERD/VegCore.ERD.mwb: georeferencing: documented that this stores location determinations (= VegBIEN.vegpath.org?public.locationplace)
- 06:31 PM Revision 11225: inputs/VegBank/taxon_observation.**/: updated for data refresh
- 06:25 PM Revision 11224: inputs/VegBank/plantconcept_/: mapped columns, since this is now included in import_order.txt and therefore gets processed by the column-renaming runscripts. note that this means that in taxonobservation_/map.csv, the plantconcept_ input column names need to be changed to what they are mapped to.
- 06:16 PM Revision 11223: inputs/VegBank/taxonobservation_/create.sql: updated runtime (20 s)
- 05:54 PM Revision 11222: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 05:51 PM Revision 11221: fix: schemas/VegCore/ERD/VegCore.ERD.mwb: project: allow it to *have* a dataset rather than *being* one, because projects do not always have separate attribution requirements (e.g. in Madidi they don't). this also fixes a bug where projects whose datasets had a parent dataset (e.g. VegBank) would need a dummy event for that parent dataset so that the fkeys on both dataset.parent->dataset *and* event.parent->event would be satisfied. added project__unique constraint on name within dataset.
- 05:38 PM Revision 11220: schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event.parent (inherited): require this to be a taxa_sampling_event. documented that this is the taxa_sampling_event for the parent stratum.
- 05:15 PM Revision 11219: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 05:03 PM Revision 11218: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 05:00 PM Revision 11217: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 04:55 PM Revision 11216: schemas/VegCore/ERD/VegCore.ERD.mwb: place_visit.parent (inherited): added fkey constraint that this must be a place_visit. documented that this is the concurrent observation of the parent place.
- 04:47 PM Revision 11215: schemas/VegCore/ERD/VegCore.ERD.mwb: project: inherit from dataset, since projects generally confer attribution and conditions of use
- 04:26 PM Revision 11214: fix: schemas/VegCore/ERD/VegCore.ERD.mwb: place_visit: fixed synonyms list to "VegX.vegpath.org?plotObservation, VegBank.vegpath.org?observation (which was confusingly named) + some of VegBank.vegpath.org?plot (which is actually 1:1 with observation)"
- 04:22 PM Revision 11213: schemas/VegCore/ERD/VegCore.ERD.mwb: place_observation: renamed to place_visit for clarity, since other tables that point to this use it as a general container, not just a link to the observations about the place
- 04:16 PM Revision 11212: schemas/VegCore/ERD/VegCore.ERD.mwb: merged stratum_event into taxa_sampling_event since they serve the same purpose (separating taxa samplings within the same place_observation by subsetting methodology). merged layer into stratum since strata as we think of them are horizontal, although there are other definitions of stratum (e.g. size class) which are more similar to our method table.
- 03:51 PM Revision 11211: schemas/VegCore/ERD/VegCore.ERD.mwb: event subclasses: subject: added applicable fkey constraints to the inherited field. renamed existing table-specific subject fields to subject to merge with the inherited field.
- 03:38 PM Revision 11210: schemas/VegCore/ERD/VegCore.ERD.mwb: event: added unique constraints for every way that the scoping fields can meaningfully be combined. these correspond to the various unique constraints offered by VegBIEN.locationevent. (the event table's constraints are always tricky because there are many ways to define an event. it is important to include all of them so that there is almost always a way to generate a natural key, without resorting to an artificial key from traceable.id_by_source.)
- 03:28 PM Revision 11209: schemas/VegCore/ERD/VegCore.ERD.mwb: event.place: renamed to subject and allow this to be any traceable, not just a place. this allows taxon_observations to be scoped within their taxon_occurrences just as subplot events are scoped within parent plot events.
- 02:46 PM Revision 11208: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.authors: documented that this makes traceable mutually recursive with party_list
- 02:45 PM Revision 11207: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.authors: documented that this makes traceable mutually recursive with party_list
- 02:42 PM Revision 11206: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed inheritance connectors
- 02:38 PM Revision 11205: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 02:35 PM Revision 11204: schemas/VegCore/ERD/VegCore.ERD.mwb: record.authors: moved to traceable because actually, different sections of a denormalized source record may have separate authors (e.g. participants for taxa_sampling_event, identifiers for taxon_determination)
- 02:24 PM Revision 11203: schemas/VegCore/ERD/VegCore.ERD.mwb: record.dataset: split this into the scoping_dataset (which scopes the id_within_dataset) and the attribution_dataset (which provides attribution information)
- 01:55 PM Revision 11202: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 01:45 PM Revision 11201: schemas/VegCore/ERD/VegCore.ERD.mwb: record.authors: changed this to refer *only* to original data creators such as collectors and identifiers. this way, there is no need to generate a combined party_list of owners+authors for every record, because the record-specific authors list can just point to the same party_list as the collectors or event participants. this is also closer to Brad's meaning, which is specifically "a person or institution responsible for *creating* the dataset" [+emph].
- 01:31 PM Revision 11200: schemas/VegCore/ERD/VegCore.ERD.mwb: party_list: added array field, which stores an array combining all the list entries. if specified, a trigger will autogenerate party_list_entries from this field. if not specified, it will be autopopulated whenever a party_list_entry is added.
- 12:59 PM Revision 11199: schemas/VegCore/ERD/VegCore.ERD.mwb: added data_owner subclass of party_list_entry, which stores use_conditions for each data_owner of a dataset. dataset: added access_rights (public, private, etc.), use_conditions (citation requirements, etc.).
- 12:25 PM Revision 11198: schemas/VegCore/ERD/VegCore.ERD.mwb.run: record.authors: clarified that this should include *all* parties who helped create the data record, making it a *superset* of dataset.data_owners, who receive attribution. clarified that *record-specific* authors such as collectors and identifiers generally do not receive attribution, although *dataset-level* authors do (this field includes both).
- 12:07 PM Revision 11197: schemas/VegCore/ERD/VegCore.ERD.mwb.run: record.id_within_dataset: removed instruction to also put this in source.name, because source.name is now optional, and this would not make sense because source does not have a parent pointer
- 12:03 PM Revision 11196: schemas/VegCore/ERD/VegCore.ERD.mwb.run: documented that schema COMMENTs cannot contain ' (used in contractions such as "don't"), because MySQL Workbench cannot escape these properly
- 12:02 PM Revision 11195: schemas/VegCore/ERD/VegCore.ERD.mwb: record: added authors, which stores the people who helped create the data record. this is Brad's data author term (Brad.vegpath.org?attribution.data_author), but actually belongs to the individual data *record* rather than to an entire data*set* because records may have different authors (collectors, identifiers, etc.). note that schema COMMENTs apparently cannot contain ' (used in contractions such as "don't"), because MySQL Workbench cannot escape these properly.
- 11:38 AM Revision 11194: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.data_owners: documented that this includes all aggregators through which the data has passed, so that this is equivalent to Brad's data indexers (Brad.vegpath.org?attribution.data_indexer)
- 11:31 AM Revision 11193: fix: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.parent: fixed example to indicate that the GBIF.ARIZ dataset is contained within a specific GBIF *dataset* (the parent), but was obtained from the GBIF *organization* (the dataset_source). ARIZ is then the first_publisher, not the dataset_source.
- 11:22 AM Revision 11192: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: renamed data_source to dataset_source for clarity
- 11:14 AM Revision 11191: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.contacts: documented that this is the contact person(s) to ask about attribution questions. there should always be at least one contact person. if unknown, the person loading the data should set this to themselves.
- 11:12 AM Revision 11190: added schemas/VegCore/Brad_Boyle/BIEN 3 data use and attribution.docx symlink to schemas/VegBIEN/attribution/BIEN 3 data use and attribution.docx, so that all of Brad's attribution-related terms are in one place
- 11:07 AM Revision 11189: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.contacts: make this required, so that anyone adding data to a VegCore DB always provides at least one contact person, possibly themselves, to ask about attribution questions (as suggested by Brad in BIEN 3 data use and attribution.docx)
- 11:00 AM Revision 11188: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.first_publisher: allow this to be any kind of source, rather than just a party (i.e. an organization). this is possible now that party inherits from source.
- 10:52 AM Revision 11187: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: linked to Brad's synonyms for dataset_source (Brad.vegpath.org?attribution.proximate_data_provider), first_publisher (Brad.vegpath.org?attribution.data_publisher, Brad.vegpath.org?data_provenance.primarydataprovider)
- 10:43 AM Revision 11186: bugfix: web/people/Brad_Boyle/.htaccess: only forward requests for a term defined by Brad to the attribution/ subdir, so that Brad.vegpath.org shows a directory listing instead of forwarding right to the attribution doc
- 10:33 AM Revision 11185: bugfix: web/people/Brad_Boyle/.htaccess: set %{ENV:innermost_dir} to Brad_Boyle
- 10:28 AM Revision 11184: added web/people/Brad_Boyle/data_provenance/ (BIEN database entities related to data provenance and ownership.docx)
- 09:56 AM Revision 11183: bugfix: web/people/Brad_Boyle/attribution/.htaccess: \-escape the %s so that they aren't treated as backreferences. set the [redirect] flag so that the downloaded file has the correct filename (BIEN 3 data use and attribution.docx instead of attribution).
- 09:49 AM Revision 11182: bugfix: web/people/Brad_Boyle/attribution/.htaccess: use %20 instead of + because the Apache server apparently doesn't translate + to space correctly
- 09:47 AM Revision 11181: bugfix: web/people/Brad_Boyle/attribution/.htaccess: link to the VegBIEN subdir in /exchange_schemas instead of the main /VegBIEN because there are actually two VegBIEN subdirs, and the main one instead points to /datasources/VegBIEN
- 09:45 AM Revision 11180: added web/people/Brad_Boyle/ with attribution/ subdir for BIEN 3 data use and attribution.docx
- 09:15 AM Revision 11179: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: renamed data_source to dataset_source for clarity
- 08:54 AM Revision 11178: inputs/VegBank/plantconcept_/create.sql: documented runtime (21 min)
- 08:28 AM Revision 11177: bugfix: inputs/VegBank/plantconcept_/: added new-style import files
- 08:27 AM Revision 11176: bugfix: inputs/VegBank/import_order.txt: added plantconcept_, because new-style import needs it to be explicitly listed in import_order.txt in order to run it
- 08:24 AM Revision 11175: inputs/VegBank/run: refresh(): added usage
- 08:22 AM Revision 11174: inputs/VegBank/run: refresh(): documented that this should be run on *vegbiendev*
- 07:58 AM Revision 11173: inputs/VegBank/_archive/2012-8-30/: svn:ignore the data exports
- 07:57 AM Revision 11172: inputs/VegBank/run: added refresh() target
- 07:54 AM Revision 11171: inputs/VegBank/: refreshed VegBank so that all of Mike Lee's sample plots would be included in the input data. (VegBank was last refreshed from the live DB on 2012-8-30.) split vegbank.sql into vegbank.schema.sql and vegbank.data.sql so that the schema can be examined and imported separately, like for MySQL datasources. inputs/VegBank/vegbank.~.clean_up.sql: commented out setting comminterpretation.commname to NOT NULL, because after the refresh it is now NULL in 10 rows, where commconcept_id is also NULL.
10/08/2013
- 05:31 PM Revision 11170: lib/runscripts/datasrc_dir.run: import(): added remake (rm=1) mode that reinstalls the datasource before continuing with the subdirs' import actions
- 05:29 PM Revision 11169: lib/runscripts/in_datasrc_dir.run: added datasrc_make(), which runs make in the datasrc dir
- 12:36 PM Revision 11168: inputs/.geoscrub/_src/README.TXT: added e-mail from John Donoghue with general description of the BIEN2 geovalidation workflow
10/03/2013
- 04:04 PM Revision 11167: bugfix: schemas/vegbien.sql: plot.**: removed ORDER BY because a SELECT on the view with its own ORDER BY will actually sort the rows *twice* instead of optimizing away plot.**'s default ORDER BY
- 03:44 PM Revision 11166: schemas/vegbien.sql: added plot.** subset view of analytical_stem_view, used for VegBank validation
- 01:41 PM Revision 11165: planning/timeline/timeline.2013.xls: moved reload core & analytical database out a week because it shouldn't be done until after CVS validation is complete
- 01:10 PM Task #294 (Resolved): find plot data source provider to work with Aaron
- 01:10 PM Task #297 (Resolved): Request new data dump of specimen data from GBIF, this time in DwC format
- 01:10 PM Task #301 (Resolved): RAINFOR data
- part of SALVIAS
- 01:09 PM Task #305 (Resolved): Complete full-dataset validations for NYBG & SALVIAS
- 01:09 PM Task #306 (Resolved): Acquire additional plot data sets from providers willing to work with Aaron on mappings and validations
- 01:09 PM Task #307 (Resolved): Acquire additional specimen data sets in both DwC and DwCA format, esp. GBIF
- 01:09 PM Task #312 (Resolved): Finish importing SALVIAS data
- 01:08 PM Task #320 (Rejected): Convert user-defined VegX fields to first-class fields
- using [[VegCore]] instead
- 01:08 PM Task #324 (Resolved): NYBG validations
- 01:07 PM Task #333 (Resolved): add traits table to VegBIEN
- 01:07 PM Task #344 (Resolved): contact John about GNRS/georeferencing
- 01:07 PM Task #345: integrate GNRS into VegBIEN
- prototyped in [[VegCore]]
- 01:06 PM Task #346: add georeferencing support to schema
- prototyped in [[VegCore]]
- 01:06 PM Task #377 (Resolved): ask NYBG for direct access to server
- 01:03 PM Task #466 (Rejected): column-based import: avoid most exceptions by introspecting the DB structure instead of waiting for a parseable exception to be raised
- easier to handle exceptions than trying to anticipate them
- 01:02 PM Task #467: underline all to do items in meetings notes
- completed items have been crossed out; remaining tasks can then be underlined
- 01:01 PM Task #482: translate README.TXT to wiki page
- see [[Import process for Madidi]]
- 01:00 PM Task #544: integrate creation of analytical DB into automated testing
- it's part of the extract creation for datasource validations
- 01:00 PM Task #560: move VegCore data dictionary to a phpPgAdmin-accessible database
- see "VegCore ERD":http://vegbiendev.nceas.ucsb.edu/VegCore/ERD/
- 12:58 PM Task #562 (Rejected): flatten the mappings
- autopopulated natural pkeys actually do require the nested mappings, but these can be part of the schema instead
- 12:56 PM Task #577: use views instead of map spreadsheets to store the datasource mappings
- map spreadsheets copied to tables in [[new-style import]]
- 12:25 PM Revision 11164: inputs/.geoscrub/_src/README.TXT: added link to geovalidation description in wiki
- 11:03 AM Revision 11163: added schemas/VegBIEN/attribution/BIEN 3 data use and attribution.docx from Brad
- 10:03 AM Revision 11162: planning/timeline/timeline.2013.xls: updated to reflect that the next week should be spent doing data validations, as decided in the conference call
10/02/2013
- 05:59 AM Revision 11161: bugfix: schemas/vegbien.sql: locationevent__communities(): need to use commdescription instead of commname as the communityName because that's where communityName has been mapped to in mappings/VegCore-VegBIEN.csv. (commname instead stores the communityID.)
- 05:56 AM Revision 11160: inputs/VegBank/: added observation__community/
- 05:49 AM Revision 11159: inputs/VegBank/vegbank.~.clean_up.sql: commclass.commcode,commname: rename to prevent collisions
- 04:26 AM Revision 11158: inputs/VegBank/vegbank.~.clean_up.sql: indicate required column comminterpretation.commname
- 04:20 AM Revision 11157: inputs/VegBank/vegbank.~.clean_up.sql: commconcept.commname: rename to prevent collision with commname.commname
- 04:15 AM Revision 11156: planning/timeline/: svn:ignore: added .~* (lock files for .xls)
- 03:58 AM Revision 11155: schemas/vegbien.sql: analytical_stem_view: added instructions for what to do after updating this
- 03:55 AM Revision 11154: schemas/vegbien.sql: analytical_stem_view, analytical_plot: added communities using new locationevent__communities__array()
- 03:52 AM Revision 11153: schemas/vegbien.sql: added locationevent__communities*()
- 03:10 AM Revision 11152: bugfix: schemas/vegbien.sql: locationevent: locationevent__pull_forward_from_parent() trigger: only pull forward the location_id when there is a stratum_id, so that empty locationevents that should be auto-forwarded are not unintentionally made valid by populating the location_id field (which causes a check constraint to become satisfied, and prevents auto-forwarding from working). this fixes a bug in some datasources' automated tests, such as `make inputs/SALVIAS/plotObservations/test`.
- 02:45 AM Revision 11151: lib/sql_io.py: put_table(): default param: documented that this will be used for *all* missing rows, regardless of which error caused them not to be inserted. this means that auto-forwarding (wiki.vegpath.org/Auto-forwarding) can be used with *any* type of constraint violation, not just NOT NULL constraints (which it is typically used with).
10/01/2013
09/30/2013
- 07:47 AM Revision 11149: planning/timeline/timeline.2013.xls: updated for progress
- 07:38 AM Revision 11148: bugfix: schemas/vegbien.sql: analytical_stem_view: plot__collectors: to include plot__collectors for nested stratum events, need to calculate the locationevent__contributors__array() for the locationevent and parent_event *separately*, and then COALESCE those, instead of just running locationevent__contributors__array() once on the COALESCE of the two IDs
- 07:34 AM Revision 11147: bugfix: schemas/vegbien.sql: locationevent: added pull-forward trigger to populate location_id from the parent event. a pull-forward trigger *must* be used for this (not a COALESCE() in analytical_stem_view) because the locationevent cannot even be found by analytical_stem_view if it does not have an associated location.
- 06:37 AM Revision 11146: schemas/VegCore/ERD/VegCore.ERD.mwb: source: added required url field, which points to the source data and uniquely identifies the source. note that this differs from the id, which is a *self*-refential pkey (as described at wiki.vegpath.org/Web_interface#Clickable-fkeys).
- 05:47 AM Revision 11145: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 05:38 AM Revision 11144: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 05:32 AM Revision 11143: schemas/VegCore/ERD/VegCore.ERD.mwb: source: removed canon, because this would create too much complexity as a result of having multiple copies of the same source with different names. instead, source names will be scrubbed *upon insert* so that the row always contains the most accurate data, and there is only one copy of each source. this scrubbing may use the assistance of a thesaurus table w/ standard abbreviations, etc.
- 05:07 AM Revision 11142: schemas/VegCore/ERD/VegCore.ERD.mwb: plot.name: merged with inherited name field
- 04:43 AM Revision 11141: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 04:40 AM Revision 11140: schemas/VegCore/ERD/VegCore.ERD.mwb: source: added canon field. when there are multiple names for a source, this points to the canonically-named source. by default, points recursively to itself.
- 04:04 AM Revision 11139: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation.taxon_occurrence: documented that when not provided (and not fillable from another field), a taxon_occurrence will be created with the same id as the taxon_observation. this operation should eventually be perfomed by a trigger.
- 03:58 AM Revision 11138: schemas/VegCore/ERD/VegCore.ERD.mwb: geovalidatable_place.parent_geoplace: renamed to parent so it will merge with the inherited parent field. this is the parent geoplace.
- 03:27 AM Revision 11137: schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
- 03:20 AM Revision 11136: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 03:18 AM Revision 11135: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_observation.specimenholder_institutions: documented that instead, the taxon_occurrence for the individual_observation should have an autogenerated current_observation that merges together all the individual_observations for these institutions' specimens. in this case, each institution's specimen will still have its *own* individual_observation containing what *it* believes to be the specimenholder institutions.
- 03:03 AM Revision 11134: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_observation.specimenholder_institutions: documented that when provided, the source (i.e. traceable.source) should be set to the value of this field, because these institutions together provided the data for this entity
- 02:59 AM Revision 11133: schemas/VegCore/ERD/VegCore.ERD.mwb: party_list: inherit from source so that a party_list can be used as the source for a row (e.g. a list of institutions that together provided the data for an individual_observation that has specimens at multiple institutions)
- 02:55 AM Revision 11132: schemas/VegCore/ERD/VegCore.ERD.mwb: reobservable_presence: merged into taxon_determination, so that voucher is directly available to taxon_determination to autopopulate fields when a redetermination is made on the voucher
- 02:37 AM Revision 11131: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 02:36 AM Revision 11130: schemas/VegCore/ERD/VegCore.ERD.mwb: aggregate_observation.sampling_event: merged with inherited sampling_event field
- 02:33 AM Revision 11129: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation.parent: require that this is an fkey to another taxon_observation, since this stores the *taxon_observation* of the same taxon_occurrence in the parent sampling_event
- 02:22 AM Revision 11128: schemas/VegCore/ERD/VegCore.ERD.mwb: subplot.parent_plot: renamed to parent so it will merge with the inherited parent field. this is the parent plot.
- 02:16 AM Revision 11127: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_count.taxon_presence: renamed to parent so it will merge with the inherited parent field. this is the taxon_presence for the taxon as a whole (not subdivided by size_class, etc.).
- 02:13 AM Revision 11126: schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event.method: renamed to subsetting_method for clarity
- 02:06 AM Revision 11125: schemas/VegCore/ERD/VegCore.ERD.mwb: stratum_event.parent_event: renamed to parent so it will merge with the inherited parent field
- 01:58 AM Revision 11124: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable: added alternate pkey id_by_source, which allows referencing a traceable by its source information even when the subclass chose a different value for the pkey
- 01:49 AM Revision 11123: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation.observation_in_parent_place: merged with inherited parent field, documenting that for this table, this contains the observation of the same taxon_occurrence in the parent sampling_event
- 01:33 AM Revision 11122: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 01:24 AM Revision 11121: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_observation.place_observed_at: merged with inherited place field, documenting that for this table, this contains the place the individual was observed at
- 01:16 AM Revision 11120: schemas/VegCore/ERD/VegCore.ERD.mwb: event: reordered date_range, participants, place in the order of how likely they are to be provided. (place was considered more optional than the other two because a project does not usually have a place.)
- 01:09 AM Revision 11119: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation.sampling_event: made it required so that there is always a sampling_event to scope the primary_collector/collector_number when provided. (for specimens, this will have the same event information (date_range, participants, place) as the taxon_observation.)
- 12:35 AM Revision 11118: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation: added unique constraint on sampling_event, primary_collector, collector_number. reordered these columns in constraint order (path order).
09/29/2013
- 11:35 PM Revision 11117: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation: added primary_collector, to scope the collector_number. collectors: renamed to all_collectors for clarity, now that there is a primary_collector field.
- 11:14 PM Revision 11116: schemas/VegCore/ERD/VegCore.ERD.mwb: specimen.current_collection, owner_collection: moved to specimen_observation because these may differ each time the specimen is reobserved
- 11:08 PM Revision 11115: schemas/VegCore/ERD/VegCore.ERD.mwb: specimen.code_in_individual: renamed to id_within_individual to match the id_within_* naming convention using elsewhere
- 10:58 PM Revision 11114: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_observation: removed code because this duplicates taxon_observation.collector_number and record.id_within_dataset
- 10:53 PM Revision 11113: schemas/VegCore/ERD/VegCore.ERD.mwb: specimen.specimenholder_institutions: moved to individual_observation because the list of institutions which store a specimen from the plant actually relates to the plant, not a particular specimen (this list should be the same for all specimens taken from the plant)
- 10:34 PM Revision 11112: schemas/VegCore/ERD/VegCore.ERD.mwb: specimen: removed collection_event because this is the same as individual_observation
- 10:11 PM Revision 11111: schemas/VegCore/ERD/VegCore.ERD.mwb: specimen: removed separate fkey to individual because this is linked to from individual_observation
- 09:55 PM Revision 11110: schemas/VegCore/ERD/VegCore.ERD.mwb: place_observation: link to project. taxa_sampling_event: removed link to project since this now happens via place_observation.
- 09:35 PM Revision 11109: schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event: merged required within_place into place_observation, which already contains a required fkey to place
- 09:29 PM Revision 11108: schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event: added place_observation fkey for the concurrent place_observation, which contains all the info about the place (communities, soil data, etc.). this allows a taxon_observation to be traced to its associated location measurements.
- 08:58 PM Revision 11107: bugfix: mappings/VegCore-VegBIEN.csv: nest all taxonoccurrences inside a stratum event, so that the parent locationevent is *always* fully populated before child locationevents point to it. (previously, a stub parent event was created when the child event was imported first, which blocked the fully-populated parent event from being inserted later on.) this uses auto-folding (for VegBank/CVS) and auto-forwarding (for other datasources) to prune empty stratum events for taxonoccurrences that don't have strata. (see wiki.vegpath.org/Auto-folding, wiki.vegpath.org/Auto-forwarding for more info about these normalization techniques.) note that the inserted row counts stay *exactly the same* for all datasources except VegBank (which was being fixed), indicating that this signficant change to the mappings did *not* change the semantics of the import of taxonoccurrences.
- 08:37 PM Revision 11106: inputs/VegBank/observationcontributor_/test.xml.ref: updated inserted row count
09/28/2013
- 10:40 PM Revision 11105: bugfix: mappings/VegCore-VegBIEN.csv: stratum's locationevent: link this to the parent locationevent, so that the parent locationevent's information (such as locationeventcontributors) is accessible to the stratum's locationevent
- 09:08 PM Revision 11104: bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: inlined _join() so that taxon_observation.** wouldn't get cascadingly deleted whenever the util schema (where this normally resides) gets reinstalled
- 08:23 PM Revision 11103: bugfix: schemas/vegbien.sql: locationeventcontributor: added missing unique constraint (locationeventcontributor_unique)
- 08:21 PM Revision 11102: bugfix: schemas/vegbien.sql: locationeventcontributor.role: made it optional because some locationeventcontributors may not have an associated role
- 06:45 PM Revision 11101: schemas/vegbien.sql: analytical_stem_view, analytical_plot: added plot__collectors, which uses locationevent__contributors__array()
- 06:42 PM Revision 11100: schemas/vegbien.sql: locationevent__contributors(): split into set-returning and array-returning functions. sort results by fullname so there is a predictable ordering (VegBank does not include a sort_order in the observationContributor table).
- 05:56 PM Revision 11099: schemas/vegbien.sql: added locationevent__contributors() aggregating function for use in analytical_stem_view
- 06:53 AM Revision 11098: added inputs/VegBank/observationcontributor_/
- 06:51 AM Revision 11097: mappings/VegCore-VegBIEN.csv: mapped event__participant
- 05:51 AM Revision 11096: mappings/VegCore.htm: regenerated from wiki. added EventParticipant table containing event__participant.
- 05:04 AM Revision 11095: bugfix: inputs/VegBank/: taxonOccurrenceID: include the aggregateOrganismObservationID in this so that there is one taxonoccurrence for *each* stratum's taxonImportance. this allows the different strata to have separate taxonoccurrences that are associated with the stratum-specific locationevents, rather than all being lumped into one taxonoccurrence, with only one locationevent.
- 12:06 AM Revision 11094: schemas/VegCore/ERD/VegCore.ERD.mwb: taxon_observation.observation_in_parent_place: renamed to observation_in_parent_event because the taxon_observation might be connected to a stratum_event which is nested within another sampling_event before it's connected to any sampling_event for a parent plot. this is the case for VegBank data with associated strata.
09/27/2013
- 11:14 PM Revision 11093: bugfix: schemas/vegbien.sql: locationevent: locationevent_unique_within_creator unique index on sourceaccessioncode: need to add stratum_id, because the addition of stratum_id to *this* table instead of a separate stratum_event table (which would have required more schema changes) means we need to use the awkward construction of appending stratum_id to *every* unique constraint, including ones that ordinarily would just pass through the datasource's pkey
- 06:31 PM Revision 11092: web/links/index.htm: updated to Firefox bookmarks. added Java client- and server-side links. web: split into client- and server-side folders.
- 06:29 PM Revision 11091: planning/timeline/timeline.2013.xls: updated for progress
- 03:43 PM Revision 11090: /README.TXT: Datasource setup: added link to Example steps for a datasource (wiki.vegpath.org/Import_process_for_Madidi)
- 03:23 PM Revision 11089: /README.TXT: Full database import: To remake analytical DB: added runtime (13 h)
09/26/2013
09/24/2013
- 05:53 PM Revision 11087: planning/timeline/timeline.2013.xls: updated for progress
- 04:40 PM Revision 11086: schemas/vegbien.ERD.mwb: fixed lines
- 02:54 PM Revision 11085: schemas/vegbien.sql: analytical_plot: added stratum__name
- 02:48 PM Revision 11084: schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
- 02:47 PM Revision 11083: schemas/vegbien.sql: analytical_stem_view: added stratum table (stratum__name)
- 02:14 PM Revision 11082: mappings/VegCore-VegBIEN.csv: mapped stratum__name
- 02:09 PM Revision 11081: schemas/vegbien.sql: added source_id to allow different datasources to have their own strata
- 02:03 PM Revision 11080: schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
- 01:51 PM Revision 11079: schemas/vegbien.sql: stratum: added unique constraint on source_id/stratumname, and require stratumname, so that strata are identified by name within their datasource
- 01:45 PM Revision 11078: schemas/vegbien.sql: added source_id to allow different datasources to have their own strata
- 01:22 PM Revision 11077: schemas/vegbien.ERD.mwb: regenerated exports
- 01:18 PM Revision 11076: schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
- 01:15 PM Revision 11075: schemas/vegbien.sql: locationevent: added stratum_id to unique constraints, so that there can be multiple locationevents for the same underlying event but with different strata
- 01:01 PM Revision 11074: schemas/vegbien.sql: link locationevent to stratum instead of the other way around (similar to how it's represented in normalized VegCore as stratum_event->stratum, rather than stratum->taxa_sampling_event). this works around column-based import's lack of support for linking two tables together via two separate routes (in this case locationevent->taxonoccurrence->stratum and locationevent->stratum).
- 12:54 PM Revision 11073: web/links/index.htm: updated to Firefox bookmarks. added PostgreSQL links.
- 12:09 PM Revision 11072: schemas/vegbien.sql: stratum: added stratumname
09/22/2013
- 08:25 PM Revision 11071: mappings/VegCore.htm: regenerated from wiki. added Stratum table.
- 07:57 PM Revision 11070: schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
- 06:25 PM Revision 11069: schemas/VegCore/VegCore.ERD.mwb: model options: template for new fkey names: use the PostgreSQL naming convention so that new fkeys will sync properly with equivalent fkeys in the Postgres schema that have had their names reset
- 06:18 PM Revision 11068: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 06:13 PM Revision 11067: schemas/VegCore/VegCore.ERD.mwb: source: made name optional because some sources (e.g. records) don't have a name, and are instead identified differently. instead require name in applicable subclasses. source subclasses: added unique constraints on parent/name since the name is presumed to be identifying within the parent entity.
- 05:55 PM Revision 11066: schemas/VegCore/VegCore.ERD.mwb: party: added separate person subclass, which has the organization pointer. organization instead has a recursive parent pointer, to avoid mutual recursion between party and organization.
- 09:00 AM Revision 11065: schemas/VegCore/VegCore.ERD.mwb: dataset.parent: updated comment to refer to data_source, not traceable.source (which is now used for a different purpose)
- 08:56 AM Revision 11064: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 08:49 AM Revision 11063: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
- 08:46 AM Revision 11062: schemas/VegCore/ERD/VegCore.ERD.mwb: removed unnecessary hstore extenders that duplicate an extender of the same name in a superclass
- 08:25 AM Revision 11061: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: replaced metadata_source with data_source, since the source field inherited from traceable is actually for where the information in the dataset record itself came from
- 07:57 AM Revision 11060: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.parent: documented that this provides a containment relationship, which differs from the attribution relationship provided by source (inherited from traceable). e.g. the GBIF.ARIZ dataset is contained within the GBIF dataset (the parent), but was obtained from ARIZ (the source).
- 07:37 AM Revision 11059: schemas/VegCore/ERD/VegCore.ERD.mwb: source: moved parent pointer to dataset, since the containment relationship is intended for datasets (there may be other types of sources for which this makes sense, but not ones that are used in our data)
- 07:07 AM Revision 11058: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.table_fragment: renamed to id_within_source, since this can also identify e.g. a dataset within the source that it came from
- 06:47 AM Revision 11057: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: added metadata_source, which stores where the information *about* the dataset came from (as opposed to where the dataset came from)
- 06:31 AM Revision 11056: schemas/VegCore/ERD/VegCore.ERD.mwb: party: inherit from source because a person (or organization) can directly be a source of data. it should not be necessary to link them to a dataset in order to attribute rows to them.
- 06:25 AM Revision 11055: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.table_fragment: made this optional, since some traceables may have a source but are not *uniquely identifiable* within that source. the ID would in this case need to be populated by a subclass table, since traceable can't generate a unique ID without the fragment.
- 04:33 AM Revision 11054: schemas/VegCore/ERD/VegCore.ERD.mwb: record: clarified that "an actual data record" means "a row in a table". dataset: made definition more specific ("a collection of records from the same place, with the same attribution requirements").
- 04:08 AM Revision 11053: schemas/VegCore/ERD/VegCore.ERD.mwb: record: added definition (a collection of data relating to the same entity). note that this doesn't have to be an actual data record, it can also be a paragraph of text relating to the same thing (e.g. a method description in a PDF).
- 02:52 AM Revision 11052: schemas/VegCore/ERD/VegCore.ERD.mwb: method: added optional name field
- 02:49 AM Revision 11051: schemas/VegCore/ERD/VegCore.ERD.mwb: stratum: added definition (a non-horizontal way of subdividing a plot)
- 02:32 AM Revision 11050: schemas/VegCore/ERD/: svn:ignore *.bak
- 02:14 AM Revision 11049: schemas/VegCore/ERD/VegCore.ERD.mwb: stratum, stratum_event: added VegX synonyms
- 02:00 AM Revision 11048: schemas/VegCore/ERD/VegCore.ERD.mwb: synonyms with other exchange schemas: put the exchange schema name first, so that the first word in the URL is the source of that synonym (which is most important), while the schema-specific synonym name (which is less important) comes after it. this matches the VegCore data dictionary convention where the text of the URL (i.e. the most important part) is the exchange schema name.
- 01:53 AM Revision 11047: schemas/VegCore/ERD/VegCore.ERD.mwb: synonyms with other exchange schemas: put the exchange schema name first, so that the first word in the URL is the source of that synonym (which is most important), while the schema-specific synonym name (which is less important) comes after it. this matches the VegCore data dictionary convention where the text of the URL (i.e. the most important part) is the exchange schema name.
- 01:09 AM Revision 11046: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_count.taxon: renamed to taxon_presence to clarify that this is an fkey to taxon_presence
09/21/2013
- 11:01 PM Revision 11045: schemas/VegCore/: moved ERD-related files to ERD/ subdir
- 10:58 PM Revision 11044: added schemas/VegCore/ERD/SQL symlink to the dir with the VegCore SQL schemas
- 10:52 PM Revision 11043: schemas/VegCore/: moved ERD-related files to ERD/ subdir
- 10:45 PM Revision 11042: schemas/VegCore/: moved ERD-related files to ERD/ subdir
- 10:44 PM Revision 11041: schemas/VegCore/: moved ERD-related files to ERD/ subdir
- 10:27 PM Revision 11040: bugfix: schemas/VegCore/ERD/.htaccess: mod_autoindex: clear the IndexHeadInsert message "some listed files are not web-accessible" so that it is not displayed above the ERD image, which looks strange because the files are below it (this message can be removed for this dir because all files in it are web-accessible)
- 10:17 PM Revision 11039: web/.htaccess: mod_autoindex: IndexHeadInsert: use IndexStyleSheet for stylesheet link instead
- 10:12 PM Revision 11038: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: VegCore logo: hyperlink this to VegCore.vegpath.org as well, instead of to the data dictionary
- 10:10 PM Revision 11037: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 10:00 PM Revision 11036: schemas/VegCore/VegCore.ERD.mwb: moved VegCore.vegpath.org link next to logo so it can be found more easily
- 09:45 PM Revision 11035: schemas/VegCore/ERD/.htaccess: display the index page (containing the ERD) as part of the mod_autoindex directory listing, so that the directory listing is included when you go to the VegCore.vegpath.org/ERD/ URL. this will allow the ERD-related files to be moved to the ERD/ subdir, and still be visible in the directory listing.
- 09:42 PM Revision 11034: bugfix: web/.htaccess: mod_autoindex: IndexOptions: use +HTMLTable to ensure that the contents are always displayed in an HTML table, even when a header file is provided with HeaderName (which turns off the default HTML table for some reason)
- 09:01 PM Revision 11033: lib/sql_io.py: put_table(): added link to new INSERT ON DUPLICATE SELECT wiki page, which now contains the explanation in the doc comment
- 07:29 PM Revision 11032: schemas/VegCore/VegCore.ERD.mwb: ERD link: changed to VegCore.vegpath.org so that the user can access *all* additional files for VegCore, not just the PDF version of the ERD
- 07:24 PM Revision 11031: schemas/VegCore/VegCore.ERD.mwb: source.name: require this field (for records, populate it from id_within_dataset)
- 07:01 PM Revision 11030: schemas/VegCore/VegCore.ERD.mwb: record.dataset_record_id: renamed to id_within_dataset for clarity
- 06:59 PM Revision 11029: schemas/VegCore/VegCore.ERD.mwb: traceable: added table_fragment field, which is the portion of the source record that this traceable refers to. (denormalized source data often contains data for many VegCore tables in the same row, and the traceables for each of these table entries must be distinguished *from each other* since they share the same source. this is usually just the VegCore table name, sometimes with a distinguishing prefix (e.g. collector.party/identified_by.party; current_observation.taxon_determination/orig_observation.taxon_determination).) this field is needed *in addition to the source record* to form a unique ID for the traceable.
- 06:24 PM Revision 11028: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 06:22 PM Revision 11027: schemas/VegCore/VegCore.ERD.mwb: dataset: inherit from traceable because the information *about* the dataset (data_owners, contacts, etc.) might have been obtained from an external source, such as IH, a web page, or even an e-mail conversation
- 05:47 PM Revision 11026: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 05:45 PM Revision 11025: schemas/VegCore/VegCore.ERD.mwb: taxon_observation: draw inheritance connector to event to clarify what table this inherits from. reobservable.original_observation: renamed to orig_observation to match the naming convention of specimen.orig_collection. table relationships legend: updated "inherits from record" label to refer to the traceable table, which now serves this purpose.
09/19/2013
- 06:49 PM Revision 11024: bugfix: inputs/VegBank/import_order.txt: added stratum
- 06:48 PM Revision 11023: inputs/VegBank/taxon_observation.**/postprocess.sql: added stratum, stratumtype to the left-join
- 06:46 PM Revision 11022: inputs/VegBank/stemcount_/map.csv: stratum_id: removed table prefix so it can be used as a join column
- 06:45 PM Revision 11021: inputs/VegBank/: mapped stratum
- 06:39 PM Revision 11020: inputs/VegBank/: mapped stratumtype
- 06:27 PM Revision 11019: /README.TXT: Datasource setup: additional steps for new-style datasources: added steps not present in http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource because they were performed all at once for all datasources
- 06:24 PM Revision 11018: /README.TXT: Datasource setup: added additional steps for new-style datasources, from http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
- 04:18 PM Revision 11017: bugfix: schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current: accept any determinationtype other than the TNRS ones (accepted, matched), so that datasources that provide a custom value for this field (such as VegBank) don't have their taxondeterminations incorrectly treated as non-datasource. this fixes the VegBank bug where datasource taxondeterminations were not being joined on in analytical_stem_view, because the join included a filter for only datasource taxondeterminations, but these taxondeterminations were not being properly treated as such. **this should fix the missing taxonomic information in the VegBank validation extract.**
- 11:31 AM Revision 11016: inputs/VegBank/taxonobservation_/map.csv: taxonomic ranks not in VegCore: removed table prefix so they will be automapped (they are globally unique)
- 10:33 AM Revision 11015: planning/timeline/timeline.2013.xls: updated for progress
- 10:31 AM Revision 11014: planning/timeline/timeline.2013.xls: updated as decided in the conference call
- 02:55 AM Revision 11013: inputs//: don't import joined tables, because they are now imported in the taxon_observation.** left-join instead
- 01:22 AM Revision 11012: inputs/VegBank/taxon_observation.**/postprocess.sql: run mk_subset_by_row_num_func() to add a subset function that uses sort_col. this is used by column-based import, and also provides a common subsetting/sorting API for all the left-joined views. test.xml.ref: the inserted row count most likely changes because the sort order changes.
- 01:05 AM Revision 11011: schemas/util.sql: added mk_subset_by_row_num_func(regclass), which uses a sort_col instead of a row_num column
- 12:10 AM Revision 11010: schemas/util.sql: mk_subset_by_row_num_func(): factored creation of no-sort subset function into mk_subset_by_row_num_no_sort_func() so it can be used by other functions
09/18/2013
- 11:53 PM Revision 11009: bugfix: inputs/VegBank/stemlocation_/map.csv: remapped stemcount-related fields to OMIT, so that these don't collide with fields of the same name in stemcount_ when they are left-joined together in taxon_observation.** . having the same name causes these to be incorrectly interpreted as shared fkey columns in the NATURAL JOIN (and without the NATURAL JOIN, they would instead be collision errors).
- 10:35 PM Revision 11008: bugfix: inputs/VegBank/stemlocation_/postprocess.sql: added missing index on aggregateOrganismObservationID, needed for the 1:many portion of the taxon_observation.** left-join
- 10:28 PM Revision 11007: inputs/VegBank/stemcount_/postprocess.sql: moved stemcount___parent index before the derived columns section because it does not depend on them
- 10:26 PM Revision 11006: bugfix: inputs/VegBank/stemcount_/postprocess.sql: added missing index on taxonOccurrenceID, needed for the 1:many portion of the taxon_observation.** left-join
- 10:14 PM Revision 11005: schemas/util.sql: added ||% operator to append to escaped strings (the % indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
- 03:50 PM Revision 11004: bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: added sort_col (=identificationID) at beginning because column-based import will always sort a view by the first column, which may lead to slow query plans if the first column is not a joined table's pkey
- 02:04 PM Revision 11003: inputs/VegBank/taxon_observation.**/postprocess.sql: documented that there is no row_num because left-join to stemcount_, stemlocation_ adds rows to each taxonobservation_
- 02:03 PM Revision 11002: bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: removed row_num (=identificationID), because there is actually more than one row per VegBank taxonobservation_, so this does not properly enumerate the view rows. this is because there is a 1:many left-join to stemcount_, stemlocation_ which adds rows to each taxonobservation_. since the row_num is gone, any row-subsetting of the view using OFFSET will always need to materialize the entire view up to the OFFSET value. this works for smaller datasources like VegBank that fit almost entirely into one column-based import chunk (1 million rows), but not for larger datasources like FIA where it would be much slower to materialize all preceding 16 million rows on the last chunk (which is what OFFSET normally does with left-joins).
- 01:51 PM Revision 11001: bugfix: inputs/VegBank/taxon_observation.**/: generated header.csv and related files, which were previously not generated because the error in `rm header.csv` aborted the runscript
09/17/2013
09/16/2013
- 07:51 AM Revision 10999: inputs/VegBank/: added taxon_observation.** left-join of the tables, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
- 07:48 AM Revision 10998: inputs/VegBank/taxonobservation_/create.sql: join starting with taxoninterpretation so that we can use the taxoninterpretation_id as the row_num (text strings, formed from concatenated #s cannot be used as a row_num). there is only 1 taxonobservation without a taxoninterpretation, so we can just include one row for each taxoninterpretation.
- 02:32 AM Revision 10997: bugfix: inputs/VegBank/taxonobservation_/test.xml.ref: updated after reloading staging table. this fixed a bug where observationGranularity apparently either did not exist or was not the right type of constant column to be properly inlined the last time the tester was run. the inlining is important for using metadata switches to generate the correct XML import script.
- 12:13 AM Revision 10996: bugfix: lib/sh/make.sh: don't allow rm to override remake if an invoked script uses this file. this fixes a bug in `rm=1 inputs/.../.../run` where the remake action would be invoked on the map_table command even though it had been suppressed, because it was run externally (i.e. make.sh was reloaded) and the rm=1 flag was still active
09/15/2013
- 10:50 PM Revision 10995: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order: moved traceable before party because party is a subclass of it
- 10:02 PM Revision 10994: bugfix: inputs/input.Makefile: `%/install: %/create.sql`: don't include %/header.csv as a target, so that it won't get deleted if the install fails (especially on a step that happens *after* the header is exported)
- 09:07 PM Revision 10993: inputs/VegBank/taxonobservation_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
- 01:54 PM Revision 10992: inputs/VegBank/stemcount_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
- 01:27 PM Revision 10991: bugfix: schemas/util.sql: mk_subset_by_row_num_func(): need explicit ORDER BY on the row_num_col, to prevent PostgreSQL from sorting the rows in reverse (they will be sorted because of the index scan, but the direction is otherwise indeterminate)
- 01:20 PM Revision 10990: schemas/util.sql: mk_subset_by_row_num_func(): support tables whose pkeys don't start with 1 (such as the VegBank tables), by calculating the smallest row_num from the table
- 01:03 PM Revision 10989: schemas/util.sql: offset2row_num(), limit2row_num(): added optional min_row_num, for tables whose serial pkeys start from a value other than 1
- 12:36 PM Revision 10988: schemas/util.sql: added qual_name(regclass)
- 08:36 AM Revision 10987: schemas/util.sql: added esc_name__append()
- 08:09 AM Revision 10986: schemas/util.sql: added col__min()
- 07:34 AM Revision 10985: schemas/util.sql: added limit2row_num() and use it in mk_subset_by_row_num_func() for clarity
- 07:26 AM Revision 10984: schemas/util.sql: added offset2row_num() and use it in mk_subset_by_row_num_func() for clarity
- 07:25 AM Revision 10983: bugfix: schemas/Makefile: `%/install: vegbien.sql`: when replacing public with the specified schema name, only perform the replacement if the schema is named something other than public. this prevents text like "public schema" inside comments from being "-escaped.
- 06:50 AM Revision 10982: planning/timeline/timeline.2013.xls: updated for progress
- 06:25 AM Revision 10981: bugfix: /README.TXT: to backup files not in Time Machine: need to use -E option to sudo to preserve env, after installing the latest system update
- 05:47 AM Revision 10980: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order
- 05:40 AM Revision 10979: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 05:17 AM Revision 10978: schemas/VegCore/VegCore.ERD.mwb: party: added required name field
- 05:12 AM Revision 10977: schemas/VegCore/VegCore.ERD.mwb: fixed table positions
- 05:09 AM Revision 10976: schemas/VegCore/VegCore.ERD.mwb: event: added event_unique_within_parent, event_unique_within_place unique indexes
- 04:57 AM Revision 10975: schemas/VegCore/VegCore.ERD.mwb: source: split into source and dataset tables, since many of the source fields (first_publisher, data_owners, contacts) were only applicable to datasets. note that a dataset is specifically something *requiring attribution*, while a source just indicates where something came from. non-dataset sources are useful e.g. as the taxon_concept.according_to.
- 03:48 AM Revision 10974: schemas/VegCore/VegCore.ERD.mwb: source.owners: renamed to data_owners for clarity. documented that this is the parties who must be given attribution, such as copyrightholders.
- 03:39 AM Revision 10973: schemas/VegCore/VegCore.ERD.mwb: source: allow multiple contacts
- 03:34 AM Revision 10972: schemas/VegCore/VegCore.ERD.mwb: source: added contact (different from owners)
- 03:23 AM Revision 10971: schemas/VegCore/VegCore.ERD.mwb: model options: templates for new column names, etc.: configured to our conventions (pkey is "id"; columns are unique within the table rather than being prefixed with the table name)
- 03:08 AM Revision 10970: schemas/VegCore/VegCore.ERD.mwb: source: allow multiple data owners
- 03:04 AM Revision 10969: schemas/VegCore/VegCore.ERD.mwb: documented that record is the record in the *source* data, and traceable is a row that can have associated source information
- 03:03 AM Revision 10968: schemas/VegCore/VegCore.ERD.mwb: removed relationship table, because this is too general, and custom relationships between entities are better indicated in the applicable hstore extender field for the table in question. record: split into record and traceable tables, where record is instead the record in the *source* data, and traceable indicates that a row has associated source information.
- 02:36 AM Revision 10967: bugfix: schemas/VegCore/VegCore.ERD.mwb: party_list_entry: use just a single id field as the primary key, because the pkey must be a single text string in order to uniquely identify the row by a global ID
- 02:25 AM Revision 10966: schemas/VegCore/VegCore.ERD.mwb: party_list_entry: added role field
- 02:01 AM Revision 10965: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 01:53 AM Revision 10964: schemas/VegCore/VegCore.ERD.mwb: taxa_sampling_event: link directly to project, so that you don't have to search the parent event hierarchy to find the associated project
- 01:50 AM Revision 10963: schemas/VegCore/VegCore.ERD.mwb: fixed table positions
- 01:48 AM Revision 10962: schemas/VegCore/VegCore.ERD.mwb: spaced tables out more
- 01:36 AM Revision 10961: schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: require all taxon_occurrences to have an associated within_place (which may be set to Earth if the coordinates/placename is unknown)
- 01:30 AM Revision 10960: schemas/VegCore/VegCore.ERD.mwb: moved the plot table next the the section label for the plots half of the page
- 01:19 AM Revision 10959: schemas/VegCore/VegCore.ERD.mwb: taxon_observation: allow any taxon_observation to link directly to a taxa_sampling_event, not just aggregate_observation. this allows any taxon_observation's methodology information to be found directly by joining to taxa_sampling_event. previously, one had to join to event.parent and then taxa_sampling_event, and it was not clear that event.parent would even *be* a taxa_sampling_event.
- 01:07 AM Revision 10958: schemas/VegCore/VegCore.ERD.mwb: aggregate_observation: link to rather than being a taxa_sampling_event, because taxa_sampling_event was actually intended to be the container event in which various types of taxon_observations occur, rather than a type of taxon_observation itself. rearranged tables to avoid them covering any lines.
09/14/2013
- 11:53 PM Revision 10957: schemas/VegCore/VegCore.ERD.mwb: renamed GNRS & geovalidation steps to just geoscrubbing steps for clarity
- 11:48 PM Revision 10956: schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: added within_place to allow easily locating all the taxon_occurrences in a plot. (some taxon_occurrences require within_place, but there was no common superclass field to allow joining to just a single table.)
- 11:24 PM Revision 10955: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: moved reobservable tables (individual, specimen, etc.) before aggregate_observation tables because these are less abstract
- 11:21 PM Revision 10954: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
- 11:15 PM Revision 10953: schemas/VegCore/VegCore.ERD.mwb: individual_count: inherit from taxon_presence because this is also a type of taxon_observation (and specifically, a taxon_presence), in addition to the general taxon_presence it's associated with. it is possible e.g. for different diameter classes to be counted by different collectors, and thus to need separate taxon_observations to store the different collectors.
- 11:07 PM Revision 10952: schemas/VegCore/VegCore.ERD.mwb: added taxon_occurrence to store the underlying occurrence for each taxon_observation. we cannot use reobservable for this because not all taxon_observations are reobservable, but the one-time observations still have an associated occurrence. moved reobservable.current_determination to taxon_occurrence and renamed it to current_observation (this can be used to find the latest observation for each occurrence).
- 09:47 PM Revision 10951: planning/timeline/timeline.2013.xls: added spacing between later column and weeks to make clear that dots in the later column do not correspond to a week
- 09:26 PM Revision 10950: inputs/FIA/occurrence_all/: renamed to taxon_observation.** to clarify what an occurrence is. taxon_observation has the same meaning as in VegCore, where *each taxon_determination is considered a separate taxon_observation* of the associated specimen or vouchered plant (vegpath.org/VegCore/ERD/). (note that having multiple taxon_determinations only makes sense when there is something to reobserve.) the .** is SQL dotpath syntax (wiki.vegpath.org/SQL_dotpaths) for the recursive expansion of all tables to which taxon_observation has forward fkeys (i.e. "the left-join").
- 09:01 PM Revision 10949: inputs/FIA/occurrence_all/postprocess.sql: use :table_str var instead of hardcoding the table name as occurrence_all, to avoid needing to change this file when renaming the view
- 05:38 PM Revision 10948: inputs/FIA/occurrence_all/postprocess.sql: renamed occurrence_all--row_num to just row_num because this is for the entire view, rather than a specific table in it, and thus does not need a disambiguating table prefix. this also avoids embedding the view name in its own columns.
- 04:34 PM Revision 10947: inputs/FIA/*/map.csv: use -- to separate the table and column name instead of - , to conform with the u-name format (wiki.vegpath.org/u-name#format), which works even when only one of _- can be used in the name. -s are needed in this case to linewrap the column on a separate line as the table in phpPgAdmin.
- 04:20 PM Revision 10946: lib/runscripts/datasrc_dir.run: added postprocess target to run postprocess in just the table subdirs, skipping any additional subdirs that don't have this target
- 04:19 PM Revision 10945: lib/runscripts/datasrc_dir.run: @subdirs: moved import_order.txt subdirs into separate @table_subdirs, which provides access to just the table subdirs when the user adds other dirs to @subdirs
Also available in: Atom