bugfix: schemas/vegbien.sql: locationevent__communities(): inner-join to commdetermination instead of left-joining, so that this does not produce community entries (which occur because there is one commclass per locationevent, but only some commclasses will then have a commdetermination)
schemas/vegbien.sql: projectcontributor: added missing unique constraint
moved everything into /trunk/ to create the standard svn layout, for use with tools that require this (eg. git-svn). IMPORTANT: do NOT do an `svn up`. instead, re-use your working copy's existing files with `svn switch` (http://svnbook.red-bean.com/en/1.6/svn.ref.svn.c.switch.html).
schemas/vegbien.sql: plot.**, analytical_stem_view, analytical_plot: added project_contributors
schemas/vegbien.sql: added project_contributors()
schemas/vegbien.sql: analytical_stem_view: scientificName_verbatim: don't use taxonverbatim.taxonname+author as the scientificName_verbatim if only the author is provided. (this lead to weird scientificName_verbatims that contain just the author.)
bugfix: schemas/vegbien.sql: location: use the place_id from the parent location when no place_id is specified. this fixes a bug in analytical_stem_view where the parent location's place_id was used because it was sometimes missing from the sublocation, but the parent place_id itself was sometimes missing instead if sublocations each had their own place information. this way, it is always available directly in the sublocation, populated from the parent location if needed.
bugfix: schemas/vegbien.sql: location: added place_id which is autopopulated from the current locationplace. join on this in plot.**, to avoid a 1:many join when a location has multiple locationplaces.
bugfix: schemas/vegbien.sql: locationevent_unique_within_parent_by_location unique index: need COALESCE around location_id since it's nullable
fix: schemas/vegbien.sql: analytical_specimen: added specimens-related columns that are in analytical_plot
bugfix: schemas/vegbien.sql: updated for renamed county_centroids column names
schemas/vegbien.sql: include the family_higher_plant_group lookup table values so that these don't need to be regenerated from the NCBI nodes whenever the DB is reloaded
schemas/vegbien.sql: taxonlabel_update_ancestors(): don't do an index scan if the value being scanned for is NULL, to support testing this function without the indexes in place, without extra full-table scans for NULL values affecting things. this can be used to determine if the function is actually using the indexes, by turning them off and seeing if the runtime changes.
schemas/vegbien.sql: taxonlabel_update_ancestors(): don't create a performance-intensive nested transaction (EXCEPTION block) for each INSERT, because there should no longer be duplicate ancestors, so it's OK to abort the whole transaction if this assertion fails
bugfix: schemas/vegbien.sql: taxonlabel_update_ancestors_on_{insert,update}(): only use either the matched taxon's ancestors or the parent's ancestors, to avoid issues related to duplication between these two ancestors lists. this also fixes a bug where the 2nd taxonlabel_update_ancestors() call assumes that the existing ancestors are for the old parent, when in fact they have actually just been set to those for the new matched taxon (which horribly confuses taxonlabel_update_ancestors()).
schemas/vegbien.sql: _taxonlabel_set_parent_id(): just use a plain UPDATE statement, to avoid the significant parsing and stringification overhead of EXECUTE and quote_nullable(). it is not clear that EXECUTE is actually necessary to avoid caching the query plan, because the cache should be invalidated automatically when the table's ANALYZE statistics are regenerated.
schemas/vegbien.sql: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields
schemas/vegbien.sql: synced to DB (the view renderer apparently changed the text of a view)
schemas/vegbien.sql: analytical_stem_view: scrubbed_author: removed empty COALESCE around value (left over from when multiple values needed to be combined for many TNRS fields)
schemas/vegbien.sql: provider_count_view: source totals: use the much faster query developed for Brad (wiki.vegpath.org/VegBIEN_FAQ#from-Brad-on-2013-12-4), which avoids the need to do a GROUP BY on all of analytical_stem. eventually, we will want to apply the same optimization to the first publisher subtotals.
schemas/vegbien.sql: collected_dates: documented runtime (2.5 min)
schemas/vegbien.sql: collected_date_min: replaced with collected_dates view that lists all dates we have, so that we can determine which of these may be valid. it turns out that we have data collected from very far back (to the year 1), which are not merely 2-digit years because PostgreSQL will only parse early years when there are 4 digits.
bugfix: schemas/vegbien.sql: collected_date_min: exclude invalid dates < 1000-01-01
bugfix: schemas/vegbien.sql: collected_date_min: exclude -infinity
schemas/vegbien.sql: added collected_date_min view
schemas/vegbien.sql: analytical_stem_view: added taxonomic_status. notice that PostgreSQL 9.3 puts each view column on a separate line, making it much easier to review the svn diff!
fix: schemas/vegbien.sql: taxon_trait_view: include only TNRS-valid names
schemas/vegbien.sql: sync_analytical_stem_to_view(): use new util.force_recreate() instead of manually dropping and re-creating every view that uses this. this avoids the need to add several lines to this function every time we add a new scientific view (of which we expect to have many), because force_recreate()'s error parsing handles this automatically. this makes it possible for a non-expert user to add scientific views without compromising the ability to add columns to analytical_stem_view, because they don't need to understand Postgres's dependency error messages when updating analytical_stem with this function.
bugfix: schemas/vegbien.sql: plantobservation_aggregateoccurrence_count_1(): only default aggregateoccurrence.count to 1 for specimens data, because plots data may have any number of individuals in a taxon_presence record that has no explicit individual_count
schemas/*.sql: updated for PostgreSQL 9.3. this reorders some functions, adds empty comment headers for omitted SEQUENCE SET commands, and (best of all) finally splits view columns onto multiple lines, so that changes in the columns are actually legible (and produce their own svn diff!)
bugfix: schemas/vegbien.sql: scrubbed_morphospecies_binomial: only append the morphospecies suffix if there is not a scrubbed specific epithet
bugfix: schemas/vegbien.sql: scrubbed_morphospecies_binomial: only populate this from the component ranks; do not put a full taxon name in here if it would otherwise be NULL
bugfix: schemas/vegbien.sql: tnrs_input_name: MatchedTaxon self-join: must use a NOT NULL column for a proper anti-join. this unfortunately requires the more verbose LEFT JOIN ON syntax (which allows using the pkey as the NOT NULL column) instead of NATURAL LEFT JOIN (which requires using another column, which are all nullable)
schemas/vegbien.sql: tnrs_input_name: use plain UNION, which automatically removes duplicates, rather than UNION ALL with a manual EXCEPT-removal of rows in the first SELECT
schemas/vegbien.sql: tnrs_input_name: updated to use taxon_scrub.scrubbed_unique_taxon_name.*, to avoid further dependencies on AcceptedTaxon
schemas/vegbien.sql: taxon_trait_view: updated to use new taxon_scrub
schemas/vegbien.sql: analytical_stem_view: updated to use new taxon_scrub. this avoids the need to manually COALESCE every accepted* and matched* field, and makes the formulas much clearer
bugfix: schemas/vegbien.sql: analytical_stem_view: scrubbed_taxon_name_no_author, scrubbed_author: need to COALESCE these to the matched* when no accepted* is available
schemas/vegbien.sql: analytical_stem_view, etc.: renamed scrubbed fields with the scrubbed_* prefix, to clearly distinguish these from the equivalent fields for other taxon names
bugfix: schemas/vegbien.sql: analytical_stem_view: family, genus: need to COALESCE these to the matched* when no accepted* is available
bugfix: schemas/vegbien.sql: 2013-7-10.Naia.range_limiting_factors: filter by coordinateUncertaintyInMeters filter: assume true for rows with no coordinateUncertaintyInMeters
schemas/vegbien.sql: 2013-7-10.Naia.range_limiting_factors: filter by coordinateUncertaintyInMeters <= 10 km
schemas/vegbien.sql: 2013-7-10.Naia.range_limiting_factors: don't sort the results by occurrence_id, because this is not a meaningful ordering and prevents incremental output from the query
schemas/vegbien.sql: 2013-7-10.Naia.range_limiting_factors: also filter out rows without species
fix: schemas/vegbien.sql: analytical_plot, analytical_specimen: removed derived columns that are not part of the validation
fix: schemas/vegbien.sql: analytical_plot, analytical_specimen: removed internal ID columns that are not part of the validation
schemas/vegbien.sql: analytical_plot: removed derived columns that should not be validated by data providers
schemas/vegbien.sql: analytical_specimen: synced to analytical_stem
schemas/vegbien.sql: analytical_plot: documented that this contains all of the analytical_stem columns, minus specimenHolderInstitutions, collection, accessionNumber, occurrenceID
schemas/vegbien.sql: analytical_plot: synced to analytical_stem
schemas/vegbien.sql: analytical_stem_view: added individualCount
schemas/vegbien.sql: plot.**, analytical_stem_view: added slopeAspect, slopeGradient
schemas/vegbien.sql: taxondetermination_set_iscurrent(): include new iscurrent__verbatim, so that taxondeterminations the datasource marks as current are always considered first. this currently applies to VegBank and CVS.
schemas/vegbien.sql: taxondetermination.isoriginal: made it nullable like iscurrent__verbatim, because this is populated from the datasource. taxondetermination_set_iscurrent() now supports isoriginal=NULL, so this is not a problem.
schemas/vegbien.sql: taxondetermination.is_datasource_current: renamed to iscurrent__verbatim and made it nullable, so that this can be used to store the verbatim iscurrent status
schemas/vegbien.sql: taxondetermination_set_iscurrent(): removed setting of is_datasource_current (which is now the same as iscurrent), so that this can be used to store the verbatim iscurrent status
schemas/vegbien.sql: taxondetermination_set_iscurrent(): isoriginal: make sure it is always either true or false, so that if the NOT NULL constraint on this is ever removed you don't end up with the incorrect sort order false, true, NULL (it should be false=NULL, true)
schemas/vegbien.sql: use plain taxondetermination.iscurrent instead of is_datasource_current since these are now the same
schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current: set to the same value as iscurrent, since these now have the same formula
schemas/vegbien.sql: taxondetermination_set_iscurrent(): removed no longer used accepted, matched determinationtypes (for these determinations, left-join to TNRS.ScrubbedTaxon)
schemas/vegbien.sql: added 2013-7-10.Naia.range_limiting_factors
schemas/vegbien.sql: sync_analytical_stem_to_view(): row_num: renamed to taxon_occurrence__pkey because previous taxon determinations have been removed, so each row is in fact a taxon_occurrence (~= VegCore.vegpath.org?ERD.taxon_occurrence)
fix: schemas/vegbien.sql: analytical_stem_view: don't ORDER BY datasource, because this requires a slow full-table sort after the hash joins. (when selecting a subset of analytical_stem_view, nested loops are used automatically without needing an ORDER BY to force this.) to get the datasource-sorted order (plus a sort-order guarantee), you can still add a manual `ORDER BY datasource`, which will use a fast index scan on one of the datasource indexes.
schemas/vegbien.sql: analytical_stem: added row_num, which can serve as the taxon_observation ID (DwC occurrenceID)
schemas/vegbien.sql: analytical_stem: locationID... index: use eventDate instead of dateCollected since it's now eventDate that identifies the locationevent
schemas/vegbien.sql: analytical_stem_view: use plot.** to obtain plot-related fields, so that the same code does not need to be maintained in both analytical_stem_view and plot.**
schemas/vegbien.sql: analytical_stem_view: moved specimen-specific fields to occurrence section
schemas/vegbien.sql: analytical_stem_view, plot.**: added separate location__cultivated__bien
schemas/vegbien.sql: added separate eventDate, in addition to dateCollected
fix: schemas/vegbien.sql: dateCollected: use aggregateoccurrence.collectiondate before locationevent.obsstartdate rather than after, because this is more accurate. it was previously the other way around to allow dateCollected to be the pkey for the row's locationevent (for plots data).
schemas/vegbien.sql: analytical_stem_view, plot.**: locationevent__pkey: moved to right before the locationevent-related fields
schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**
schemas/vegbien.sql: plot.**: added locationevent__pkey so that this view can be joined to other VegBIEN tables, which require the internal pkey
schemas/vegbien.sql: sync_analytical_stem_to_view(): speciesBinomialWithMorphospecies index: documented runtime (1 h)
schemas/vegbien.sql: plot.**: updated to use the same column formulas as analytical_stem_view
schemas/vegbien.sql: added 2013-10-18.Brian_Enquist.Canadensys view
schemas/vegbien.sql: sync_analytical_stem_to_view(): added index on speciesBinomialWithMorphospecies for Brian Enquist's Canadensys request
bugfix: schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current (used by analytical_stem_view): need to separately check if `determinationtype IS NULL`, because `determinationtype NOT IN (accepted, matched))` will return NULL (false) if determinationtype is NULL, causing no match
fix: schemas/vegbien.sql: analytical_stem_view: renamed specimens columns to use the VegCore names, where these differ from DwC, so that the now-VegCore staging table column names are the same as the analytical_stem_view column names
schemas/vegbien.sql: regenerated using `make schemas/remake`. note that analytical_stem_view column renamings need this step after a search-and-replace of the column names, in order to remove excess "" around all-lowercase names and reset generated index names.
schemas/vegbien.sql: datasource_publish(): use parameter names instead of $# because this is a PL/pgSQL function
bugfix: schemas/vegbien.sql: datasource_publish(): if the datasource to publish already has the published name, don't datasource_rm() it
schemas/vegbien.sql: analytical_stem_view: join to the geoscrub_output table directly, instead of using the imported canon_place entries. this avoids the need to import geoscrub_output into VegBIEN (which is expected to take 2+ hours after the refresh), as well as the need to then refresh any datasources whose geoscrubbing input data has changed.
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
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.)
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`.
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.
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
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
schemas/vegbien.sql: analytical_plot: added stratum__name
