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.
lib/runscripts/datasrc_dir.run: import(): added remake (rm=1) mode that reinstalls the datasource before continuing with the subdirs' import actions
lib/runscripts/in_datasrc_dir.run: added datasrc_make(), which runs make in the datasrc dir
inputs/.geoscrub/_src/README.TXT: added e-mail from John Donoghue with general description of the BIEN2 geovalidation workflow
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
schemas/vegbien.sql: added plot.** subset view of analytical_stem_view, used for VegBank validation
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
inputs/.geoscrub/_src/README.TXT: added link to geovalidation description in wiki
added schemas/VegBIEN/attribution/BIEN 3 data use and attribution.docx from Brad
planning/timeline/timeline.2013.xls: updated to reflect that the next week should be spent doing data validations, as decided in the conference call
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.)
inputs/VegBank/: added observation__community/
inputs/VegBank/vegbank.~.clean_up.sql: commclass.commcode,commname: rename to prevent collisions
inputs/VegBank/vegbank.~.clean_up.sql: indicate required column comminterpretation.commname
inputs/VegBank/vegbank.~.clean_up.sql: commconcept.commname: rename to prevent collision with commname.commname
planning/timeline/: svn:ignore: added .~* (lock files for .xls)
schemas/vegbien.sql: analytical_stem_view: added instructions for what to do after updating this
schemas/vegbien.sql: analytical_stem_view, analytical_plot: added communities using new locationevent__communities__array()
schemas/vegbien.sql: added locationevent__communities*()
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`.
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).
/Makefile: mk_db: config/users.sql: unset ON_ERROR_STOP so user-exists errors don't prevent further user-adds
planning/timeline/timeline.2013.xls: updated for progress
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
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.
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).
schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map
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.
schemas/VegCore/ERD/VegCore.ERD.mwb: plot.name: merged with inherited name field
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.
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.
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.
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.
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
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)
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
schemas/VegCore/ERD/VegCore.ERD.mwb: aggregate_observation.sampling_event: merged with inherited sampling_event field
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
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.
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.).
schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event.method: renamed to subsetting_method for clarity
schemas/VegCore/ERD/VegCore.ERD.mwb: stratum_event.parent_event: renamed to parent so it will merge with the inherited parent field
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
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
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
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.)
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.)
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).
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.
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
schemas/VegCore/ERD/VegCore.ERD.mwb: specimen.code_in_individual: renamed to id_within_individual to match the id_within_* naming convention using elsewhere
schemas/VegCore/ERD/VegCore.ERD.mwb: individual_observation: removed code because this duplicates taxon_observation.collector_number and record.id_within_dataset
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)
schemas/VegCore/ERD/VegCore.ERD.mwb: specimen: removed collection_event because this is the same as individual_observation
schemas/VegCore/ERD/VegCore.ERD.mwb: specimen: removed separate fkey to individual because this is linked to from individual_observation
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.
schemas/VegCore/ERD/VegCore.ERD.mwb: taxa_sampling_event: merged required within_place into place_observation, which already contains a required fkey to place
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.
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.
inputs/VegBank/observationcontributor_/test.xml.ref: updated inserted row count
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
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
bugfix: schemas/vegbien.sql: locationeventcontributor: added missing unique constraint (locationeventcontributor_unique)
bugfix: schemas/vegbien.sql: locationeventcontributor.role: made it optional because some locationeventcontributors may not have an associated role
schemas/vegbien.sql: analytical_stem_view, analytical_plot: added plot__collectors, which uses locationevent__contributors__array()
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).
schemas/vegbien.sql: added locationevent__contributors() aggregating function for use in analytical_stem_view
added inputs/VegBank/observationcontributor_/
mappings/VegCore-VegBIEN.csv: mapped event__participant
mappings/VegCore.htm: regenerated from wiki. added EventParticipant table containing event__participant.
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.
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.
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
web/links/index.htm: updated to Firefox bookmarks. added Java client- and server-side links. web: split into client- and server-side folders.
/README.TXT: Datasource setup: added link to Example steps for a datasource (wiki.vegpath.org/Import_process_for_Madidi)
/README.TXT: Full database import: To remake analytical DB: added runtime (13 h)
bugfix: schemas/postgresql.Mac.conf: allow external client logins like for vegbiendev, by listening on all addresses rather than just localhost
schemas/vegbien.ERD.mwb: fixed lines
schemas/vegbien.sql: analytical_plot: added stratum__name
schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
schemas/vegbien.sql: analytical_stem_view: added stratum table (stratum__name)
mappings/VegCore-VegBIEN.csv: mapped stratum__name
schemas/vegbien.sql: added source_id to allow different datasources to have their own strata
schemas/vegbien.sql: stratum: added unique constraint on source_id/stratumname, and require stratumname, so that strata are identified by name within their datasource
schemas/vegbien.ERD.mwb: regenerated exports
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
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).
web/links/index.htm: updated to Firefox bookmarks. added PostgreSQL links.
schemas/vegbien.sql: stratum: added stratumname
