schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table

schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table

schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_10a_aggregate_observation_individual_counts, for use in debugging diffs in _plots_10_count_of_individuals_per_plot_in_each_proj

schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.

schemas/vegbien.sql: added new ~type tables

fix: schemas/vegbien.sql: plots* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)

schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)

fix: schemas/vegbien.sql: location: added index on top_plot

schemas/vegbien.sql: added more ~type tables for plots queries

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables

schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using

schemas/vegbien.sql: public_validation: split into separate subschemas for plots, specimens, traits

schemas/vegbien.ERD.mwb: synced to schemas/vegbien.my.sql, which adds the validation schema

schemas/vegbien.sql: location: added autopopulated top_plot

bugfix: schemas/vegbien.sql: commconcept: added missing unique constraint on commdescription, which is also an identifying field in addition to the ID (which is in commname.commname)

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

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: 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/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: 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: 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: sync_analytical_stem_to_view(): added index on speciesBinomialWithMorphospecies for Brian Enquist's Canadensys request

schemas/vegbien.ERD.mwb: fixed lines

schemas/vegbien.ERD.mwb: updated to PostgreSQL schema

schemas/vegbien.ERD.mwb: updated to PostgreSQL schema

schemas/vegbien.ERD.mwb: updated to PostgreSQL schema

schemas/vegbien.ERD.mwb: updated to PostgreSQL schema

schemas/vegbien.ERD.mwb: Added taxon_trait to ERD

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: 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.ERD.mwb: Fixed table positions due to plantobservation field addition

schemas/vegbien.sql: analytical_stem_view: Added occurrenceRemarks

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: 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.ERD.mwb: Synced with schema

schemas/vegbien.ERD.mwb: Fixed table sizes

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.ERD.mwb: Fixed table sizes

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: Removed notespublic, notesmgt, which are not used by VegBIEN

schemas/vegbien.sql: analytical_stem_view: derived terms: Added _bien suffix per Brad's request (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#Brad-Boyles-comments)

schemas/vegbien.sql: analytical_stem_view: Added locality

schemas/vegbien.sql: analytical_stem_view: Added georeferenceProtocol, which is set to 'county centroid' when county centroid coordinates are used

schemas/vegbien.sql: taxonverbatim: Added source_id to allow creating taxonverbatims without a (scoping) taxonlabel

schemas/vegbien.sql: analytical_stem_view: Removed speciesBinomialWithMorphospecies now that it's duplicated by scientificNameWithMorphospecies

schemas/vegbien.sql: scientificName: Set to taxonverbatim.taxonname instead per Brad's changes at <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/Spot-checking#2013-1-18&gt;. Renamed to taxonName since this now doesn't include the author, which is part of DwC's scientificName field.

schemas/vegbien.sql: taxonlabel: Removed creationdate, which duplicates taxondetermination.determinationdate

schemas/vegbien.sql: taxonverbatim: Added morphoname (which is different from the morphospecies suffix)

schemas/vegbien.sql: plantobservation: Renamed collectionnumber to authorplantcode since this number, which identifies the plant, is actually different from the collectionnumber that identifies the specimen collected from it. This distinction is meaningful for plots data, but generally not for specimens data.

schemas/vegbien.sql: specimenreplicate: Added collectionnumber

schemas/vegbien.sql: taxonlabel: Removed no longer used matched_label_fit_fraction. Use taxondetermination.taxonfit instead.

schemas/vegbien.ERD.mwb: Expanded analytical_stem to fit the width of all fields

schemas/vegbien.sql: analytical_stem: Added locationName (authorPlotCode), subplot, individualCode (authorPlantCode) for use in validation

schemas/vegbien.ERD.mwb: Moved family_higher_plant_group to leave room for analytical_stem to expand

schemas/vegbien.sql: taxonverbatim: taxonverbatim_unique: Added morphospecies so that there can be multiple taxonverbatims for the same taxonlabel, each with different morphospecies suffixes

schemas/vegbien.sql: Regenerated analytical_stem using sync_analytical_stem_to_view()

schemas/vegbien.sql: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>

schemas/vegbien.sql: Fixed fkey constraint names

schemas/vegbien.sql: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>

schemas/vegbien.sql: fkeys to source: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>

schemas/vegbien.sql: commconcept: Renamed source_id back to reference_id (it was previously renamed to source_id in a bulk rename)

schemas/vegbien.sql: taxondetermination: Added back reference_id, which is different than the scoping source_id (reference_id was previously renamed to source_id in a bulk rename)

schemas/vegbien.sql: taxondetermination: Added scoping source_id field to allow other datasources (e.g. TNRS) to make taxondeterminations. (Repurposed existing non-scoping source_id.)

schemas/vegbien.sql: analytical_*: Renamed locationName to locationID because it's now globally unique (within the datasource) and can be used as a sourceaccessioncode

schemas/vegbien.sql: analytical_*: Renamed individualID to individualObservationID because this actually corresponds to plantobservation.sourceaccessioncode, which is an observation of an individual

schemas/vegbien.sql: Merged provider_view, provider_count, and owner_count into provider_count, using the combining query for Brad's data providers page at <http://bien.nceas.ucsb.edu/bien/people/data-providers/>

schemas/vegbien.sql: trait: trait_unique: Removed value and units because there should only be one value of a trait for each taxonoccurrence

schemas/vegbien.sql: Reattached trait to taxonoccurrence instead of taxonlabel, because the TraitObservation traits data is actually associated with a particular occurrence (plant observation complete with location, date, etc.), rather than just a taxon

schemas/vegbien.ERD.mwb: Added trait table to ERD

schemas/vegbien.sql: trait: Added units field

schemas/vegbien.sql: trait: Renamed type to name because TraitObservation stores trait names rather than types

schemas/vegbien.sql: trait: Linked to taxonlabel instead of stemobservation, because TraitObservation's traits are taxon-level and stem-level traits currently go in named fields instead of a stem traits table

schemas/vegbien.sql: Added materialized view owner_count, generated from owner_count_view

schemas/vegbien.sql: Added materialized view provider_count, generated from provider_count_view

schemas/vegbien.sql: sourcelist_unique: Removed COALESCE around name because it's NOT NULL

schemas/vegbien.sql: Allow multiple institutionCodes for each specimenreplicate by linking new sourcelist table many-to-many to source via sourcename (which is now a linking table)

schemas/vegbien.sql: sourcename: Removed system, which has been replaced by source_id as the scoping field

schemas/vegbien.sql: party: Added sourceaccessioncode and uniquify on it instead when provided. vegbien.ERD.mwb: Rearranged party-related tables to allow the tables to be fully expanded.

schemas/vegbien.sql: analytical_*: Renamed subInstitutionCode to institutionCode because this is the institution storing the specimen, as defined by DwC

schemas/vegbien.sql: analytical_*: Renamed institutionCode to datasource because this is actually the top-level datasource providing the record, not the institution storing the specimen

schemas/vegbien.sql: Renamed sampletype to observationtype to match the VegCore term

schemas/vegbien.sql: analytical_*: Changed type of boolean columns to integer so that they will be exported as 1/0 instead of t/f by export_analytical_db. This will enable MySQL's LOAD DATA INFILE to import the values correctly.