added inputs/CVS/^taxon_observation.**.sample/, used for the extract. note that the column list is slightly different than for VegBank.
inputs/CVS/taxonObservation_/map.csv: removed taxonObservation_-- prefix from terms that do not need to be table-specific (like for VegBank)
fix: inputs/CVS/taxonObservation_/map.csv: plantConcept_ columns: synced input and output column names to their names in plantConcept_
inputs/CVS/plantConcept_/map.csv: removed plantConcept_-- prefix from terms that do not need to be table-specific (like for VegBank)
lib/sh/db.sh: pg_table_exists(): use `SELECT NULL` instead of `SELECT *` to avoid a long column list cluttering up the log output
lib/runscripts/table.run: table_make_install(): simplified the setting of $noclobber since there no longer needs to be a different command for when the log exists
bugfix: lib/runscripts/table.run: need to errexit the make target, so that errors in the SQL install scripts are not suppressed. this requires pre-checking if the table exists (using new pg_table_exists), so that the install target's errexit does not then need to be suppressed for cases when the table already exists.
lib/sh/db.sh: added pg_table_exists()
planning/timeline/timeline.2013.xls: added timespan dots ◦ for supertasks
planning/timeline/timeline.2013.xls: crossed out and hid completed tasks
planning/timeline/timeline.2013.xls: hid previous weeks
planning/timeline/timeline.2013.xls: consolidated legend to take up fewer columns and avoid repeating labels
bugfix: inputs/CVS/import_order.txt: added taxon_observation.**. rescheduled tasks.
planning/timeline/timeline.2013.xls: updated for progress
bugfix: inputs/CVS/import_order.txt: added taxon_observation.**
inputs/CVS/: don't import joined tables, because they are now imported in the taxon_observation.** left-join instead
inputs/CVS/: added taxon_observation.** left-join of the tables, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource. this involves renaming taxonOccurrenceID->taxonOccurrenceID__overall_plot so that it can then be joined together with aggregateOrganismObservationID to create the full taxonOccurrenceID (as in VegBank).
inputs/CVS/stemCount_/map.csv: remapped stratum_ID->*STRATUM_ID so it would match up with stratum.*STRATUM_ID
inputs/CVS/taxonObservation_/map.csv: mapped TAXONINTERPRETATION_ID to identificationID
added inputs/CVS/stratum/
added inputs/CVS/stratumType/
inputs/CVS/: prepended the table name to each column name to prevent column collisions, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
bugfix: inputs/CVS/plantConcept_/map.csv: PLANTCONCEPT_ID: remapped without * prefix so that the USING join in inputs/CVS/taxonObservation_/create.sql would continue to work
inputs/CVS/taxonObservation_/header.csv, map.csv: updated to use plantConcept_ renamed columns
inputs/CVS/: switched to new-style import, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
inputs/CVS/taxonObservation_/map.csv: updated for CVS refresh
inputs/CVS/taxonObservation_/map.csv: updated input column names to plantConcept_ renamings
inputs/CVS/plantConcept_/header.csv, map.csv: updated for CVS refresh
fix: inputs/CVS/plot_/map.csv: removed filter-less collisions. note that the name county_ is assigned in plot_/create.sql, not cvs.~.clean_up.sql as one might expect, because this is a generated column.
fix: inputs/CVS/plot_/map.csv: removed filter-less collisions
fix: inputs/CVS/taxonObservation_/map.csv: moved inherited derived columns to right after the other columns, because for this table, these are actually real input columns rather than appended derived columns. the column order must match header.csv to avoid mis-renamings.
inputs/CVS/taxonObservation_/map.csv: removed filter functions, which are now performed in plantConcept_
inputs/CVS/taxonObservation_/postprocess.sql: added _parent index to facilitate joins
fix: inputs/CVS/taxonObservation_/header.csv, map.csv: updated for CVS refresh and addition of plantConcept_ derived columns
inputs/CVS/stemCount_/: translated filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns. note that the inserted row count changes, because there is now a primary key (which the table is auto-sorted by) where previously there was none.
web/links/index.htm: updated to Firefox bookmarks. added API writing links, including the best quotes from a Google developer's PowerPoint on the topic.
schemas/vegbien.sql: collected_dates: documented runtime (2.5 min)
schemas/vegbien.sql: collected_date_min: replaced with collected_dates view that lists all dates we have, so that we can determine which of these may be valid. it turns out that we have data collected from very far back (to the year 1), which are not merely 2-digit years because PostgreSQL will only parse early years when there are 4 digits.
added planning/publication/KNB/submission.published.old_site.maff, submission.published.eml.xml from old KNB site
added planning/publication/KNB/submission.*
bugfix: schemas/vegbien.sql: collected_date_min: exclude invalid dates < 1000-01-01
bugfix: schemas/vegbien.sql: collected_date_min: exclude -infinity
schemas/vegbien.sql: added collected_date_min view
inputs/CVS/plot_/: translated column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns
/README.TXT: Full database import: verifying import: In PostgreSQL: don't include current values of the datasource counts, etc., because these may change and should always be re-checked at wiki.vegpath.org/VegBIEN_contents
inputs/CVS/plot_/postprocess.sql: added pkey from the primary joined table
inputs/CVS/plot_/map.csv: documented assumptions about the units of fields
inputs/CVS/plot_/map.csv: documented assumptions about the units and meaning of numeric codes for fields
inputs/CVS/plantConcept_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#1-Translate-filters-to-postprocessing-derived-columns
web/links/index.htm: updated to Firefox bookmarks. BIEN: added DataONE compatibility links.
inputs/CVS/plantConcept_/postprocess.sql: added pkey from the primary joined table
inputs/CVS/observation_/postprocess.sql: added pkey from the primary joined table. added _parent index to facilitate joins.
fix: inputs/input.Makefile: $(svnFilesGlob): removed schema and PDF files, since these are owned by the data provider and should not be in the repository that gets open-sourced
bugfix: inputs/CVS/observation_/create.sql: only include one soilObs for each observation (using DISTINCT ON), rather than just left-joining them
inputs/: removed SALVIAS-CSV, because this is a sample datasource which was only there to test the mapping process. it should not be adding records that duplicate SALVIAS, nor should it take up maintenance effort (switching to new-style import, updating to match SALVIAS, etc.).
planning/timeline/timeline.2013.xls: removed the weeks of 12/23, 12/30 because these are during winter break. rescheduled tasks.
inputs/.TNRS/schema.sql: updated runtime (30 min) and rowcount (+2 million)
planning/timeline/timeline.2013.xls: rescheduled tasks
fix: inputs/.TNRS/schema.sql: tnrs_populate_fields(): is_valid_match: set this to false if Taxonomic_status is Invalid
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!
inputs/.TNRS/schema.sql: added map_taxonomic_status()
inputs/.TNRS/schema.sql, data.sql: updated for PostgreSQL 9.3
bugfix: inputs/CVS/stemCount_/map.csv: ensure the aggregateoccurrence.sourceaccessioncode is always populated, because this is a required field when using sourceaccessioncodes. without it, the import will exclude rows which lack a value in this field because it cannot deduplicate on it for these rows, leading to the dropping of large numbers of occurrences. this shows up when comparing provider_count to the input table's row count, and produces the following error in the .errors table:...
fix: schemas/vegbien.sql: taxon_trait_view: include only TNRS-valid names
copyright scrub: inputs/: removed data provider-owned schema and documentation files, which are not BIEN copyright and should not be part of what is submitted for open-sourcing. these files will remain accessible via the web interface (fs.vegpath.org), but will not be in the repository.
added inputs/TEAM/_src/data_cart.tsv, containing the content extracted from data_cart.maff
web/links/index.htm: updated to Firefox bookmarks. BIEN: open-sourcing: added UArizona and iPlant IP policies, which are relevant to Brad's numerous documentation and schema-modeling contributions in our repository (most done while he was an iPlant employee).
removed inputs/TEAM/_src/data_cart.pdf since this does not contain all the info in data_cart.maff
added planning/legal/open-sourcing/request_to_open_source_software.orig.docx.url
added planning/legal/open-sourcing/, which will contain the "request to open source software" form (this cannot be under version control due to copyright limitations stated in the form)
web/links/index.htm: updated to Firefox bookmarks. BIEN: open-sourcing: added potential licenses we could use (public domain/CC0, BSD, GNU Verbatim Copying License, not CC-BY because incompatible w/ GPL).
web/links/index.htm: updated to Firefox bookmarks. BIEN: added links related to open-sourcing it, including the "Request to Open Source Software" form, the funding sources that need to be included in it, and part of the delegation of authority chain (from the UC Regents) that authorizes the open-sourcing.
backups/TNRS.backup.md5: updated
schemas/vegbien.sql: sync_analytical_stem_to_view(): use new util.force_recreate() instead of manually dropping and re-creating every view that uses this. this avoids the need to add several lines to this function every time we add a new scientific view (of which we expect to have many), because force_recreate()'s error parsing handles this automatically. this makes it possible for a non-expert user to add scientific views without compromising the ability to add columns to analytical_stem_view, because they don't need to understand Postgres's dependency error messages when updating analytical_stem with this function.
schemas/util.sql: added force_recreate(), for use by sync_analytical_stem_to_view(). this uses the new `GET STACKED DIAGNOSTICS` in PostgreSQL 9.3 to access the DETAIL section of the dependent_objects_still_exist error.
web/links/index.htm: updated to Firefox bookmarks. upgrading to PostgreSQL 9.3: added Linux pg_upgrade steps and install instructions. added Mac PostGIS, psycopg2 install steps. added note that after installing, you need to restore config values that the upgrade reset: in pgAdmin > Preferences > Query tool > Query editor, set Max characters per column back to -1 (to avoid cells being truncated). (this is not a bug in PostgreSQL, only in pgAdmin, and does not signal a need to downgrade.)
planning/timeline/timeline.2013.xls: added timespan checkmarks
planning/timeline/timeline.2013.xls: hid completed tasks
inputs/CVS/run: `make .../reinstall`: documented vegbiendev runtime (45 min)
removed inputs/CVS/cvs-archive-2012-12-04.schema.sql, which has been replaced by cvs-eep-archive-2013-10-22-VegBIEN.schema.sql
bugfix: /README.TXT: to backup files not in Time Machine: PostgreSQL: need to run with `overwrite=1` so removed files are also deleted
/README.TXT: to backup files not in Time Machine: PostgreSQL: only stop PostgreSQL after all files have been copied, to minimize the time that the PostgreSQL server is down (the final copy just copies concurrent changes)
/README.TXT: updated to PostgreSQL 9.3
added inputs/CVS/_src/cvs-eep-archive-2013-10-22-VegBIEN.zip.url
added inputs/CVS/cvs-eep-archive-2013-10-22-VegBIEN.schema.sql
inputs/CVS/run: documented `make .../reinstall` runtime (25 min)
inputs/VegBank/stemlocation_/header.csv: updated from reinstalling stemlocation_
added inputs/CVS/_src/cvs-eep-archive-2013-10-22-VegBIEN.schema.sql
added inputs/CVS/_src/cvs-eep-archive-2013-10-22-VegBIEN.schema.sql.run, which makes the SQL suitable for PostgreSQL
