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.
planning/timeline/timeline.2013.xls: updated for progress
/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
mappings/VegCore.htm: regenerated from wiki. added Stratum table.
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
schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
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.
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.
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)
schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
schemas/VegCore/ERD/VegCore.ERD.mwb: removed unnecessary hstore extenders that duplicate an extender of the same name in a superclass
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
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).
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)
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
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)
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.
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.
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").
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).
schemas/VegCore/ERD/VegCore.ERD.mwb: method: added optional name field
schemas/VegCore/ERD/VegCore.ERD.mwb: stratum: added definition (a non-horizontal way of subdividing a plot)
schemas/VegCore/ERD/: svn:ignore *.bak
schemas/VegCore/ERD/VegCore.ERD.mwb: stratum, stratum_event: added VegX synonyms
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.
schemas/VegCore/ERD/VegCore.ERD.mwb: individual_count.taxon: renamed to taxon_presence to clarify that this is an fkey to taxon_presence
schemas/VegCore/: moved ERD-related files to ERD/ subdir
added schemas/VegCore/ERD/SQL symlink to the dir with the VegCore SQL schemas
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)
web/.htaccess: mod_autoindex: IndexHeadInsert: use IndexStyleSheet for stylesheet link instead
schemas/VegCore/ERD/VegCore.ERD.png.map.htm: VegCore logo: hyperlink this to VegCore.vegpath.org as well, instead of to the data dictionary
schemas/VegCore/VegCore.ERD.mwb: moved VegCore.vegpath.org link next to logo so it can be found more easily
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.
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)
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
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
schemas/VegCore/VegCore.ERD.mwb: source.name: require this field (for records, populate it from id_within_dataset)
schemas/VegCore/VegCore.ERD.mwb: record.dataset_record_id: renamed to id_within_dataset for clarity
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.
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
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.
bugfix: inputs/VegBank/import_order.txt: added stratum
inputs/VegBank/taxon_observation.**/postprocess.sql: added stratum, stratumtype to the left-join
inputs/VegBank/stemcount_/map.csv: stratum_id: removed table prefix so it can be used as a join column
inputs/VegBank/: mapped stratum
inputs/VegBank/: mapped stratumtype
/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
/README.TXT: Datasource setup: added additional steps for new-style datasources, from http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
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.
inputs/VegBank/taxonobservation_/map.csv: taxonomic ranks not in VegCore: removed table prefix so they will be automapped (they are globally unique)
planning/timeline/timeline.2013.xls: updated as decided in the conference call
inputs//: don't import joined tables, because they are now imported in the taxon_observation.** left-join instead
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.
schemas/util.sql: added mk_subset_by_row_num_func(regclass), which uses a sort_col instead of a row_num column
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
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).
bugfix: inputs/VegBank/stemlocation_/postprocess.sql: added missing index on aggregateOrganismObservationID, needed for the 1:many portion of the taxon_observation.** left-join
inputs/VegBank/stemcount_/postprocess.sql: moved stemcount___parent index before the derived columns section because it does not depend on them
bugfix: inputs/VegBank/stemcount_/postprocess.sql: added missing index on taxonOccurrenceID, needed for the 1:many portion of the taxon_observation.** left-join
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
