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
planning/timeline/timeline.2013.xls: crossed out and hid completed tasks
planning/timeline/timeline.2013.xls: updated for progress
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: hid previous weeks
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
bugfix: inputs/input.Makefile: sql/install: exit on error by using `set -o pipefail`
fix: /Makefile: $(macPostgresLibs): added libpq.5, which is needed by PostgreSQL 9.3
fix: /Makefile: postgres-Darwin: also need to install psycopg2
/Makefile: postgres-Linux: add the PostgreSQL 9.2 apt-src in case we ever need to downgrade to it
bugfix: /Makefile: postgres-Linux: ignore errors if `sudo apt-get update` returns a non-zero exit status due to unreachable apt sources (which are likely unrelated to PostgreSQL, and should not prevent PostgreSQL configuration from continuing)
bugfix: /Makefile: postgres-Linux: fixed command to create /etc/apt/sources.list.d/pgdg.list
schemas/*.conf: upgraded to PostgreSQL 9.3, which is needed for proper exception parsing in the auto-re-create-views functionality
/Makefile: postgres-Linux: also install postgresql-#-postgis-scripts, which is used by derived/biengeo/
bugfix: schemas/vegbien.sql: plantobservation_aggregateoccurrence_count_1(): only default aggregateoccurrence.count to 1 for specimens data, because plots data may have any number of individuals in a taxon_presence record that has no explicit individual_count
schemas/*.sql: updated for PostgreSQL 9.3. this reorders some functions, adds empty comment headers for omitted SEQUENCE SET commands, and (best of all) finally splits view columns onto multiple lines, so that changes in the columns are actually legible (and produce their own svn diff!)
planning/timeline/timeline.2013.xls: added tasks "create high-level workflow diagram" and "load BIEN2 exports directly from raw data", as requested by Martha
bugfix: lib/Firefox_bookmarks.reformat.csv: remove empty <DD> tags (which Firefox now adds for all bookmarks) so they don't create a blank space on the page
bugfix: lib/Firefox_bookmarks.reformat.csv: don't prepend "page's description:" to empty <DD> tags, which Firefox now adds for all bookmarks, even if they don't have a description
web/links/index.htm: updated to Firefox bookmarks. added instructions for upgrading PostgreSQL to 9.3, and some GBIF links.
Makefile, schemas/.Mac.conf: upgraded to PostgreSQL 9.3, which is needed for proper exception parsing in the auto-re-create-views functionality. this also removes the Mac 10.8 Mountain Lion quirks, such as renaming the postgres user to _postgres (which messed everything up, but is now back to normal).
/Makefile: postgres-Linux: added steps to install PostgreSQL 9.3, which is needed for proper exception parsing in the auto-re-create-views functionality
schemas/util.sql: added save_drop_views()
schemas/util.sql: added is_empty(anyarray)
added inputs/GBIF/_src/0001000-131106143450413.zip.md5, GBIFPortalDB-2013-09-10.dump.gz.md5
schemas/util.sql: added regexp_matches_group()
schemas/util.sql: show_create_view(): also include GRANT statements, which are necessary to fully re-create the view
schemas/util.sql: added show_grants_for(table_ regclass), for use by show_create_view()
inputs/GBIF/_src/GBIFPortalDB-2013-09-10.dump.gz.url: documented download time (5.5 h for an 18 GB file)
inputs/GBIF/_src/0001000-131106143450413.zip.url: documented download time (only 2 h for an 18 GB file)
schemas/util.sql: added save_drop_view()
schemas/util.sql: added show_create_view()
added inputs/GBIF/_src/0001000-131106143450413.zip.url (DwC-A export), GBIFPortalDB-2013-09-10.dump.gz.url (raw data), portal_26_feb_2013.war.url (raw data portal)
web/.htaccess: mod_autoindex: show .* files which are normally hidden, because these are important parts of our codebase. (the leading . is not used for access controls.) .svn folders will remain hidden to avoid clutter.
inputs/GBIF/: added LOA files: _src/use_conditions/LetterOfAgreement_template.doc, BIEN LoA agreement annex.docx
inputs/.TNRS/schema.sql: tnrs_populate_fields(): regenerate the derived cols: updated runtime (40 min)
web/links/index.htm: updated to Firefox bookmarks. added links related to PostgreSQL plain-text pkeys and the GBIF data use agreement (which is apparently much less restrictive than the LoA we signed, and would even allow the data to be public). vegetation data: placed links into subfolders by datasource.
bugfix: schemas/vegbien.sql: scrubbed_morphospecies_binomial: only append the morphospecies suffix if there is not a scrubbed specific epithet
bugfix: schemas/vegbien.sql: scrubbed_morphospecies_binomial: only populate this from the component ranks; do not put a full taxon name in here if it would otherwise be NULL
inputs/.TNRS/schema.sql: tnrs: removed no longer used Accepted_scientific_name. use scrubbed_unique_taxon_name instead.
inputs/.TNRS/schema.sql: MatchedTaxon, etc.: removed no longer used acceptedScientificName (from tnrs.Accepted_scientific_name). use scrubbed_unique_taxon_name instead.
inputs/.TNRS/schema.sql: removed no longer used AcceptedTaxon. use taxon_scrub.scrubbed_unique_taxon_name.* instead.
bugfix: schemas/vegbien.sql: tnrs_input_name: MatchedTaxon self-join: must use a NOT NULL column for a proper anti-join. this unfortunately requires the more verbose LEFT JOIN ON syntax (which allows using the pkey as the NOT NULL column) instead of NATURAL LEFT JOIN (which requires using another column, which are all nullable)
schemas/vegbien.sql: tnrs_input_name: use plain UNION, which automatically removes duplicates, rather than UNION ALL with a manual EXCEPT-removal of rows in the first SELECT
schemas/vegbien.sql: tnrs_input_name: updated to use taxon_scrub.scrubbed_unique_taxon_name.*, to avoid further dependencies on AcceptedTaxon
inputs/.TNRS/schema.sql: removed no longer used ScrubbedTaxon. use taxon_scrub instead.
