inputs/.TNRS/schema.sql: tnrs: added match_num

schemas/vegbien.ERD.mwb: re-updated to schemas/vegbien.my.sql, which now recognizes the broken tables. fixed sync issues. vegbien.ERD.mwb is now fully in sync with vegbien.my.sql.

schemas/vegbien.ERD.mwb: updated to schemas/vegbien.my.sql. some tables weren't recognized (likely due to bugs in MySQL Workbench 5.2.47), and have been left as-is (unsynced). note that downgrading to 5.2.35 is not an option, because that is fatally broken by a system upgrade.

fix: schemas/vegbien.ERD.mwb: use schemas/vegbien.my.sql instead of schemas/vegbien.my.sql.changes.sql as the sync source

schemas/vegbien.ERD.mwb: switched back to MySQL Workbench 6.1.6 version, which also works with MySQL Workbench 5.2.47

schemas/vegbien.ERD.mwb: restored version for MySQL Workbench 5.2.35 (undid r13549), as 6.1.6 has bugs in the DDL file sync

schemas/vegbien.ERD.mwb: updated

schemas/vegbien.ERD.mwb: updated layout for MySQL Workbench 6.1.6, which uses a different line spacing

fix: lib/PostgreSQL-MySQL.csv: preserve schema assignments by translating `SET search_path` to `USE`

schemas/vegbien.ERD.mwb: added geoscrub, TNRS tables, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema)

schemas/Makefile: vegbien.sql: also include geoscrub, TNRS schemas, as requested in the 2014-04-10 conference call (wiki.vegpath.org/2014-04-10_conference_call#VegBIEN-schema). this involves having a separate public_.sql file for restoring the public schema.

inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: added breakdowns _specimens_13a_list_of_all_verbatim_lat_long, _specimens_13b_list_of_all_decimal_lat_long to help troubleshoot the diff

fix: inputs/NY/validations.sql, schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: count lat/longs together instead of separately, because the DISTINCT is by coordinate pair, not individual coordinate value (which wouldn't make much sense)

bugfix: schemas/vegbien.sql: coordinates_unique: added coordsaccuracy_m

schemas/vegbien.sql: specimens*: ran through pipeline

schemas/vegbien.sql: _specimens_16_list_distinct_specimen_descriptions: re-ran through pipeline after removing duplicated rows

schemas/vegbien.sql: _specimens_11_list_of_three_standard_political_divisions: ran through pipeline

schemas/vegbien.sql: _specimens_13_count_of_all_verbatim_and_decimal_lat_long: ran through pipeline

schemas/vegbien.sql: _specimens_10_count_number_of_records_by_institution: ran through pipeline

schemas/vegbien.sql: public_validations: specimens queries: added autogenerated ~type tables

schemas/vegbien.sql: public_validations: added _specimens_01_count_of_total_records_specimens_in_source_db

schemas/vegbien.sql: specimenreplicate.institution_id: renamed to duplicate_institutions_sourcelist_id, as decided in the conference calls (wiki.vegpath.org/2014-03-13_conference_call#schema-changes-2)

schemas/vegbien.sql: _plots_08_list_of_plots_which_use_percent_cover, _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: reran with fixes, which removes the incorrectly auto-added copies columns. (they were only able to be auto-added because the tables had no rows.)

schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project

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