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
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.
schemas/vegbien.sql: analytical_*: Renamed plotName to locationName to match the new VegCore term name
schemas/vegbien.sql: analytical_stem_view: locationevent info: Fixed bug where need to use project.sourceaccessioncode instead of locationevent.project_id for the projectID
schemas/vegbien.sql: Removed no longer needed darwin_core table. Use analytical_stem instead, which is now identical.
schemas/vegbien.sql: Merged darwin_core into analytical_stem
schemas/vegbien.sql: analytical_*: Removed NOT NULL constraint on dateCollected
schemas/vegbien.sql: source: Added sampletype field to indicate a plot or specimen datasource
schemas/vegbien.sql: Added darwin_core_view
schemas/vegbien.sql: Renamed taxonconcept.concept_source_id back to concept_reference_id
schemas/vegbien.sql: analytical_*: Added index on NOT NULL columns, starting with institutionCode
schemas/vegbien.sql: analytical_*: Removed primary keys and NOT NULL constraints on columns that sometimes have NULL values
schemas/vegbien.sql: Added cultivated_family_locations to store locations where various taxon families are considered cultivated
lib/PostgreSQL-MySQL.csv: Change text to varchar(255) because text columns can't be used in indexes in MySQL
schemas/vegbien.sql: analytical_aggregate: Added identifiedBy, which is no longer a scoping field (which would prevent scientificNameWithMorphospecies from being unique) now that there is only one taxondetermination for each taxonoccurrence
schemas/vegbien.sql: analytical_aggregate: Added primary key on institutionCode, plotName, scientificNameWithMorphospecies, recordNumber. Note that this makes these fields NOT NULL, which should not be a problem because there are inner joins instead of LEFT JOINs on most of the tables which provide them, and LEFT JOINed tables have their identifying fields combined to create a NOT NULL value.
schemas/vegbien.sql: analytical_aggregate: Added primary key on institutionCode, plotName, scientificNameWithMorphospecies. Note that this makes these fields NOT NULL, which should not be a problem because there are inner joins instead of LEFT JOINs on the tables which provide them.
