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

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.

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: removed unused function _taxonlabel_set_matched_label_id(), which refers to obsolete fields

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.

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!

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

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

schemas/vegbien.sql: analytical_plot: documented that this contains all of the analytical_stem columns, minus specimenHolderInstitutions, collection, accessionNumber, occurrenceID

schemas/vegbien.sql: analytical_stem_view: added individualCount

schemas/vegbien.sql: plot.**, analytical_stem_view: added slopeAspect, slopeGradient

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

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

schemas/vegbien.sql: analytical_stem_view: changed column order, etc. to match plot.**

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

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: added plot.** subset view of analytical_stem_view, used for VegBank validation

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: 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

schemas/vegbien.sql: analytical_stem_view: added stratum table (stratum__name)

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.sql: added source_id to allow different datasources to have their own 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).

schemas/vegbien.sql: stratum: added stratumname

bugfix: schemas/vegbien.sql: source: removed testing row that had gotten in during `make schemas/remake`

bugfix: schemas/vegbien.sql: datasource_publish(): need to remove the current live datasource instead of the datasource to publish. note that datasource_rename() does not currently generate an error if the specified datasource doesn't exist.

bugfix: schemas/vegbien.sql: datasource_publish(): run it in a nested transaction so that there is always one published copy of the datasource. (note that a nested transaction is not automatically created for each function, http://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures?In_PG_your_procedures_aren%27t_separate_transactions#answer-6283201 .)

schemas/vegbien.sql: added datasource_publish()

schemas/vegbien.sql: added datasource_rename()

schemas/vegbien.sql: added rm_version_suffix()

schemas/vegbien.sql: taxon_trait: added query to use to export

schemas/vegbien.sql: added datasource_rm(). this uses an internal schema-scoping parameter to ensure that the function always operates on tables in the schema it was defined in, rather than tables in the search_path. this ensures that when the public schema is renamed (e.g. from an imported version), the function will continue to operate on its own schema rather than whichever schema happens to be called public. this avoids any surprises if you are trying to remove a datasource in one schema, and don't want it to unintentionally be removed in another schema instead.

schemas/vegbien.sql: added covering indexes on foreign keys where needed. this enables rows to be cascadingly deleted without a full table scan.

schemas/vegbien.sql: source.revision: renamed to import_revision for clarity

schemas/vegbien.my.sql: updated with `make schemas/remake`

schemas/vegbien.sql: analytical_stem: synced with analytical_stem_view using sync_analytical_stem_to_view()

schemas/vegbien.my.sql: updated for new bin/repl text mode matching, which also affects non-regexps. this causes the replacement of a few more occurrences of PostgreSQL-only one-word typenames with their MySQL equivalents.

schemas/vegbien.sql: re-ran `make schemas/public/reinstall; make schemas/remake` cycle, which apparently changed sort order of statements

schemas/vegbien.sql: split geoscrub_input_view's new-row-only filtering into separate view geoscrub_input_new, so that the full geoscrub_input rows are still available. the reduction in geoscrub_input from eliminating the already-scrubbed rows was only 280,000 (5076500 - 4799173) out of a possible 1.7 million (1707970), so it makes sense to just run geoscrubbing on the full input. (the lower-than-expected reduction is most likely due to rows from pre-refresh data being present in the original geoscrub_output table, which have been replaced by different, post-refresh input rows.)

schemas/vegbien.sql: higher_plant_group_nodes: ferns and allies: added Lycopodiophyta node, as requested by Brad in the conference call (wiki.vegpath.org/2013-06-13_conference_call)

schemas/vegbien.sql: allow public_ to view lookup tables (cultivated_family_locations, higher_plant_group_nodes)

schemas/vegbien.sql: added _filter_genus()

schemas/vegbien.sql: cultivated_family_locations: documented that table is from sftp://nimoy.nceas.ucsb.edu/home/bien/bien2_scripts/geoscrub/cultivated/cult_by_taxon/flag_by_taxa.inc (i.e. not generated by a function)

schemas/vegbien.sql: place.geovalid: require it to be NOT NULL so that it's always a 2-valued boolean (but default it to false since it's not a required field)

schemas/vegbien.sql: added range_modeling_input view

schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.

schemas/vegbien.sql: analytical_stem_view: Merged taxonName_verbatim and scientificNameAuthorship_verbatim into scientificName_verbatim

schemas/vegbien.sql: Removed no longer needed unscrubbed_taxondetermination_view. Use taxonlabel joined to ScrubbedTaxon instead.

*.sql: Replaced concat_delim() with concat_ws(), which is built-in as of PostgreSQL 9.1 (http://stackoverflow.com/questions/1943433/postgresql-concat-ws-like-function)

schemas/vegbien.sql: analytical_stem_view: Added temperature_C, precipitation_m for possible use in John Wiens' plant climatic niche evolution project ("how fast is the rate of climatic niche evolution among species compared to projected changes in climate over the next 100 years? How do these rates compare for temperature and precipitation variables?")

schemas/vegbien.sql: analytical_specimen/analytical_plot: Added occurrenceRemarks

schemas/vegbien.sql: analytical_stem_view: Added occurrenceRemarks

Moved compass_dir from schemas/vegbien.sql to schemas/functions.sql so it can be used by _dms_to_dd()

Moved _dms_to_dd() from schemas/vegbien.sql to schemas/functions.sql so it can use functions schema functions

schemas/vegbien.sql: Added compass_dir enum

schemas/vegbien.sql: Added _dms_to_dd()

schemas/vegbien.sql: analytical_stem_view: scientificName_verbatim: Use the taxonname (which omits the family and author) instead when available, at Brad's request

schemas/vegbien.sql: provider_count_view: Group data by dataprovider and dataset, rather than by each separately, to satisfy the GBIF attribution requirements as described by Brad

schemas/vegbien.sql: analytical_stem_view: Moved threatened_bien after growthForm as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#SpeciesLink>

schemas/vegbien.sql: Added analytical_plot view

schemas/vegbien.sql: Added analytical_specimen view

schemas/vegbien.sql: analytical_stem_view: Moved recordedBy, recordNumber before dateCollected as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#ACAD>

schemas/vegbien.sql: Added reproductiveCondition

schemas/vegbien.sql: plantobservation: Added reproductivecondition

schemas/vegbien.sql: analytical_stem_view: Moved identifiedBy, dateIdentified, identificationRemarks right after the *_verbatim terms that they relate to, as requested by Brad <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#ACAD>

schemas/vegbien.sql: Added concat_delim()

schemas/vegbien.sql: analytical_stem_view: Removed deprecated taxonNameWithMorphospecies now that we have speciesBinomialWithMorphospecies

schemas/vegbien.sql: analytical_stem_view: speciesBinomial: Added morphospecies suffix to create speciesBinomialWithMorphospecies

schemas/vegbien.sql: Added _mm_to_m()

schemas/vegbien.sql: analytical_stem_view: Renamed scientificNameWithMorphospecies to taxonNameWithMorphospecies because it does not contain the scientific name author, as required by DwC scientificName <http://rs.tdwg.org/dwc/terms/#scientificName>

schemas/vegbien.sql: removed all accessioncode fields, as VegBIEN does not use them

schemas/vegbien.sql: analytical_stem_view: Added family_matched, taxonName_matched, scientificNameAuthorship_matched

schemas/vegbien.sql: analytical_stem_view: Added family_verbatim, scientificName_verbatim, scientificNameAuthorship_verbatim from datasource taxondetermination

schemas/vegbien.sql: taxondetermination: Added is_datasource_current, which is autopopulated to the most recent datasource-provided taxondetermination

schemas/vegbien.sql: taxondetermination: Added taxondetermination_single_accepted_determination unique index to facilitate joining on the accepted determination

schemas/vegbien.sql: taxondetermination: Added taxondetermination_single_matched_determination unique index to facilitate joining on the matched determination

schemas/vegbien.sql: taxondetermination: Removed notespublic, notesmgt, which are not used by VegBIEN