inputs/.TNRS/schema.sql: tnrs: pkey: use match_num instead of Name_number to allow multiple matches per name, as is needed to implement #917
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: 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)
