bin/tnrs_db: add entry to new batch table
inputs/.TNRS/schema.sql: batch: reset name of id_by_time unique constraint since this field is now in the batch table
inputs/.TNRS/schema.sql: download_settings: renamed to batch_download_settings because this table is actually specific to the batch, and it does not make sense to have a download settings file without a batch
inputs/.TNRS/schema.sql: download_settings.id: added fkey to batch.id to create a 1:1 relationship with optional participation by download_settings. note that this relationship happens to be the same as SQL inheritance, as used in VegCore, but in this case, the 1:1 relationship is not related to inheritance.
inputs/.TNRS/schema.sql: client_version: added table, column comments with info on how to retrieve each value
inputs/.TNRS/schema.sql: added client_version table for svn revisions, with fkey from batch
inputs/.TNRS/schema.sql: added batch table and moved download_settings.time_submitted, id_by_time to it since these are not related to the download_settings file
fix: planning/timeline/timeline.2013.xls: Switching to new-style import: updated hyperlink
planning/timeline/timeline.2013.xls: moved Individual datasource removal under Streamline process of mapping and adding a new datasource
planning/timeline/timeline.2013.xls: added note that the purpose of the dots is to show what tasks should be worked on. in some cases, they are also proportional to the complexity of the task, but this may not be the case if e.g. a task was given different priorities in different months, or worked on in different amounts.
fix: planning/timeline/timeline.2013.xls: matched supertask status to subtask status
planning/timeline/timeline.2013.xls: made Switching to new-style import a subtask of Streamline process of mapping and adding a new datasource because new-style import automates many of the datasource-mapping tasks that previously needed to be done by hand
planning/timeline/timeline.2013.xls: reordered for priorities and to-do assignments from last conference call (wiki.vegpath.org/2013-08-22_conference_call#Decisions-made)
planning/timeline/timeline.2013.xls: updated for August progress and recently-added tasks
inputs/.TNRS/schema.sql: added VegCore-style id column as the primary key, instead of using time_submitted directly. this enables always using the same name for the pkey. the pkey is now autopopulated from time_submitted in a trigger, using helper column id_by_time. the user is now also able to specify their own globally-unique ID that is not based on the time_submitted.
inputs/.TNRS/schema.sql: download_settings comment: changed name of button to Download settings, which had gotten auto-replaced to download_settings
inputs/.TNRS/schema.sql: Download settings table: renamed to download_settings because although Download settings is the verbatim name of the button that this info comes from, it is not necessary to name the table a particular way in order to match up data to it correctly, so we can just use the standard naming convention (wiki.vegpath.org/u-name#format) and avoid the need to enclose the name in ""
inputs/.TNRS/schema.sql: added Download settings table, which stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt
inputs/.TNRS/Source/map.csv: mapped datasetURL
inputs/.geoscrub/Source/map.csv: mapped datasetURL
mappings/VegCore-VegBIEN.csv: mapped datasetURL
fix: mappings/VegCore-VegBIEN.csv: source__modified_date: remapped to pubdate instead of datelastmodified because this is actually metadata for the source itself, rather than for the VegBIEN record of the source
fix: inputs/.geoscrub/Source/map.csv: source__modified_date: use the mtime of the CSV file instead, since this is closer to the actual version of the biengeo code at the time it was run
inputs/.geoscrub/Source/map.csv: mapped source__modified_date. note that the test must be run with inputs/.geoscrub/Source/run instead of `make inputs/.geoscrub/Source/test` to add these metadata columns to the staging table.
mappings/VegCore-VegBIEN.csv: mapped source__modified_date (different from vegcore.vegpath.org?modified, which is for the data record)
mappings/VegCore.htm: regenerated from wiki. added source__version (= edition), source__modified_date.
bugfix: schemas/util.sql: set_col_names_with_metadata(): rename any metadata cols rather than re-adding them with new names
mappings/VegCore-VegBIEN.csv: mapped edition
bugfix: inputs/.geoscrub/{Source,geoscrub_output}/VegBIEN.csv: switched to the version needed for new-style datasources
inputs/.geoscrub/Source/map.csv: mapped edition (the version), using `svn info derived/biengeo/`
schemas/vegbien.sql: source.revision: renamed to import_revision for clarity
schemas/vegbien.my.sql: updated with `make schemas/remake`
schemas/vegbien.sql: source: datecreated, datelastmodified: default to now() like in VegBank (schemas/VegBank/vegbank.sql)
schemas/vegbien.sql: source: added datecreated, datelastmodified, etc. for source-level tracking of import and revision (wiki.vegpath.org/2013-08-22_conference_call#source-level-tracking-of-import-and-revision)
added derived/biengeo/ from https://projects.nceas.ucsb.edu/nceas/projects/biengeo/repository/
added /derived
web/links/index.htm: updated to Firefox bookmarks. Gmvault: added steps to do full backup and to backup only new e-mails
planning/timeline/timeline.2013.xls: flagged timeline issues that can be done by iPlant personnel: Attribution and conditions of use, Geoscrubbing re-run, Geoscrubbing automated pipeline, Improve and complete data provider metadata, Obtain any additional new data
web/links/index.htm: updated to Firefox bookmarks. Gmvault: added run instructions for Mac.
web/links/index.htm: updated to Firefox bookmarks. added link to Gmvault (Gmail backup), which wouldn't install for me on Mac (but that may be because I'm using 10.8, and Gmvault is for 10.7/10.6)
added planning/meetings/BIEN conference call availability.xlsx (backup of Google spreadsheet)
planning/timeline/timeline.2013.xls: updated for changes made in the conference call: moved Data provider validations (spot-checking) to beginning since that seems to have been decided to be a higher priority than architectural changes
schemas/util.sql: combining functions taking anyelement params which could be text: take text param instead, so that other argument types (e.g. integer) will first be implicitly cast to text instead of trying to concatenate integers directly. this fixes a bug in the VegBank.stemcount_,stemlocation_ _join() of two integer pkeys, which first needed to be cast to text. anyelement was previously used so that other text-like types such as varchar could also be used, but varchar is implicitly castable to text so keeping anyelement should not be necessary.
planning/timeline/timeline.2013.xls: added tasks to Avoid DB restructuring when ingesting a new datasource and Streamline process of mapping and adding a new datasource (not yet put in priority order)
inputs/VegBank/observation_/test.xml.ref: updated inserted row count
bugfix: inputs/VegBank/stemlocation_/map.csv: also join together taxonimportance_id, stemcount_id for aggregateOrganismObservationID so that the aggregateoccurrence pkeys match up with those imported from stemcount
bugfix: inputs/VegBank/stemcount_/map.csv: aggregateOrganismObservationID: prepend taxonimportance_id so that rows with only a taxonimportance entry (no stemcounts) will also have the required sourceaccessioncode
schemas/vegbien.sql: analytical_stem: synced with analytical_stem_view using sync_analytical_stem_to_view()
bugfix: schemas/vegbien.sql: sync_analytical_stem_to_view(): added re-creation of range_modeling_input view
schemas/vegbien.sql: analytical_stem_view: added aggregateOrganismObservationID (aggregateoccurrence.sourceaccessioncode) so aggregateoccurrences can be matched back up to their input rows (e.g. VegBank.stemcount)
inputs/VegBank/taxonobservation_/map.csv: plantname: remapped to DUPLICATE#of:plantconcept_plantname because this is an exact duplicate
bugfix: inputs/VegBank/taxonobservation_/map.csv: updated input column names for renamings in inputs/VegBank/vegbank.~.clean_up.sql
inputs/VegBank/taxonobservation_/map.csv: Species and lower ranks: remapped to EQUIV#to:plantname because these contain the taxonomic name at specific ranks, but plantname contains the taxonomic name of the plant itself, which is longer and populated more often
inputs/VegBank/taxonobservation_/create.sql: also join to plantname, since plantconcept.plantname may not always be populated when plantname.plantname is
fix: inputs/VegBank/taxonobservation_/map.csv: Species and below: remapped to _alts of scientificName, because these are actually the full taxonomic name at that rank, not just the epithet. Genus: documented that it includes the genus author.
inputs/VegBank/vegbank.~.clean_up.sql: disambiguated plantconcept.plantname, plantname.reference_id to enable joining plantconcept_->plantname
fix: inputs/VegBank/taxonobservation_/map.csv: also mapped plantname to scientificName, since int_currplantscifull is not always provided when this is. (it cannot replace int_currplantscifull, because when int_currplantscifull also provided, this often leaves out lower ranks.) this should fill in taxonomic information for taxonobservations that are currently missing it.
bugfix: schemas/vegbien.sql: analytical_stem_view: coordinates: use the coordinates from datasource_place instead of canon_place, because canon_place's coordinates are only what the geoscrubbing output and do not contain datasource-specific information such as coordsaccuracy_m
inputs/VegBank/taxonobservation_/map.csv: collector_id: remapped to UNUSED. removed LEFT JOIN collector_id->party since this field is never populated.
inputs/VegBank/plot_/map.csv: area|country|territory, region|state|province (from place table): remapped to DUPLICATE, since these have the same data as, and are populated less often than, their country/stateprovince couterparts
bugfix: inputs/VegBank/plot_/create.sql: need to join place.*plot_id* to plot.plot_id instead of plotplace_id. this is the cause of the "State is wrong, not Wyoming, but Tennessee" and "County is incorrect (not Powell, but Orange)" bugs in the VegBank spot-checking (wiki.vegpath.org/Spot-checking#Great-Smoky-Mountains-National-Park).
inputs/VegBank/taxonobservation_/map.csv: int_origplantscifull: remapped to EQUIV (to authorplantname). this is the scrubbed originalScientificName, but we do our own scrubbing.
fix: inputs/VegBank/taxonobservation_/map.csv: authorplantname: remapped to originalScientificName because it includes the name author
inputs/VegBank/taxonobservation_/map.csv: mapped int_origplant*, int_currplant* to *scientificName/*taxonName/etc.
inputs/VegBank/plot_/map.csv: elevation: documented that it has only 5 decimal places of precision, with only 9s and random #s after that
inputs/VegBank/plot_/test.xml.ref: update rowcount
inputs/VegBank/stemlocation_/map.csv: stemcode, stemxposition, stemyposition: remapped to UNUSED. stemhealth is the only data field in this table that is populated, which means that VegBank does not provide data on reobservable stems even though the schema supports it.
inputs/VegBank/observation_/postprocess.sql: added pkey
schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
schemas/VegCore/ERD/index.htm: regenerated
schemas/VegCore/VegCore.ERD.mwb: taxon_observation: added observation_in_parent_place, which points to the observation of the same taxon/individual in the parent place. this accounts for VegBank allowing multiple taxonImportances per taxonObservation, which only makes sense when each taxonImportance is from a different stratum and they point to a common taxonObservation for the parent plot.
bugfix: web/.htaccess: auto-detect dotpath in query string: need to include the path ($0) in the replacement, to avoid reverting to the root dir. (mod_rewrite replacements are not like relative URLs, which would interpret ?... as being relative to the current path, not the root.)
bugfix: web/.htaccess: auto-detect dotpath in query string: added missing $ at end of regexp
inputs/VegBIEN/db/.htaccess: allow access to schemas other than the public schema by requiring a schema before the table. currently, this does not break any existing URLs.
planning/timeline/timeline.2013.xls: rebalanced between the months. Geoscrubbing re-run: extended to September since it's not clear when Jim will have time for this.
planning/timeline/timeline.2013.xls: split Data provider validations (spot-checking) into separate tasks for VegBIEN and VegCore
planning/timeline/timeline.2013.xls: updated for July progress
schemas/VegCore/VegCore.ERD.mwb: geovalidation: added step at end to georeference all places to corrected_geoplace
schemas/VegCore/VegCore.ERD.mwb: moved all required fields before optional fields
schemas/VegCore/VegCore.ERD.mwb: individual: identifying_place: documented that for specimens, this is the place the specimen was collected at. within_place: documented that this is not specified for specimens since their coordinates are usually not precise enough to identify an individual. specimen: documented that when there are multiple specimen replicates (copies) of a specimen, each gets its own specimen_observation pointing to the same specimen.
schemas/VegCore/VegCore.ERD.mwb: individual: added identifying_place since some individuals may be identified by their coordinates rather than a tag
schemas/VegCore/VegCore.ERD.mwb: added back aggregate_observation as a superclass of taxon_absence, taxon_presence. have only aggregate_observation, not taxon_observation, inherit from taxa_sampling_event because taxa_sampling_event is designed for multiple or aggregate samplings, but taxon_observation can be for just one occurrence. taxa_sampling_event: added required within_place since taxa must be sampled within a particular place. individual.place: renamed to within_place to clarify that each individual does not necessarily need its own place instance. individual_observation: added place_observed_at to serve the role that individual.place previously did (the place of an individual may change between observations).
schemas/VegCore/VegCore.ERD.mwb: aggregate_observation: renamed to individual_count for clarity. added count field. renamed traits to aggregating_traits since these are used in uniquely identifying the individual_count. taxon_presence: added cover_percent.
schemas/VegCore/VegCore.ERD.mwb: taxon_presence: documented that this is actually equivalent to VegX's aggregateOrganismObservation ("An observation applying to all occurrences of an organism"), in spite of VegCore having its own aggregate_observation table with a different purpose
schemas/VegX/.htaccess, TCS/.htaccess: prepend / to subpath so that it becomes an absolute XPath. the subpath is now usually provided as a dotpath translated to a dirpath, which would not have a leading / .
fix: schemas/VegCore/VegCore.ERD.mwb.run: comment about why the Mac version does not export the PDF correctly: clarified that it is the single-page PDF where the lines take very long to load, and the printed PDF where the colors are wrong in Adobe Reader
schemas/VegCore/VegCore.ERD.mwb.run: documented that to edit VegCore.ERD.mwb, you must use the Linux version of MySQL Workbench. this is because the Mac version does not export the PDF correctly: the colors are wrong in Adobe Reader and the lines take very long to load. unfortunately, you also cannot switch back and forth between the two versions, because they render the lines with a different spacing, which makes the tables different sizes and misaligns the connector lines. it is especially unfortunate that the Mac version messes up the PDFs, because it uses a smaller line spacing and therefore smaller tables which allows the tables to be spaced farther apart for better readability.
schemas/VegCore/VegCore.ERD.mwb: aggregate_observation- and stratum-related tables: documented VegBank equivalents
schemas/VegCore/VegCore.my.sql: regenerated with new schema-qualifier statements
bugfix: schemas/VegCore/VegCore.ERD.mwb.run: VegCore.my.sql/make(): use new create_db=1 option to mysqldump_local so that the dumpfile contains the necessary schema-qualifiers to be syncable with the ERD (i.e. to allow editing the ERD by changing the dumpfile)
lib/sh/db.sh: mysqldump(): added create_db=1 flag to print the CREATE DATABASE statement
bugfix: lib/sh/util.sh: pf(): echo func decl to stderr instead of stdout
lib/sh/util.sh: echo_vars(): documented that it only prints vars that are defined
lib/sh/util.sh: local_inv: also echo_vars the new var
schemas/VegCore/VegCore.ERD.mwb: fixed lines
