schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj
schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept
schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover
schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species
schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_06a_list_of_stems, for use in figuring out the diff in _plots_06_list_of_plots_with_stem_measurements
schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location
bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id
fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
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: 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: 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.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>. 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: 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
