schemas/public_.sql: viewFullOccurrence_*: renamed to view_full_occurrence_* at Brian M's and Martha's request (e-mails from Martha on 2014-8-12 at 17:37PT, and from Brian M on 2014-8-13 at 16:21PT). note that this change has already been made on vegbiendev.
bin/make_analytical_db: materialize viewFullOccurrence_individual_view instead of analytical_stem_view because analytical_stem_view is now generatable via a simple join onto viewFullOccurrence_individual_view. this avoids running into potential disk space constraints when materializing and backing up both tables (~50 GB/table * 2 tables * 2 copies (incl. the backup) = 200 GB, which is very close to the available disk space).
bin/make_analytical_db: removed extra () around psql_verbose_vegbien
bin/make_analytical_db: removed no longer used mk_table()
bin/make_analytical_db: use more up-to-date *_view_modify() functions instead of mk_table()
bin/make_analytical_db: removed threatened_taxonlabel because this is now handled by iucn_red_list
bin/make_analytical_db: added iucn_red_list_view_modify()
bin/make_analytical_db: removed unused code to create views in the analytical_db schema
bin/make_analytical_db: merged mk_table and mk_analytical_table since they now do the same thing
bin/make_analytical_db: removed remake_diff_tables() because this is now done for each datasource in inputs/input.Makefile
bin/make_analytical_db: removed no longer needed "${public}_validations" schema qualifier, now that it is in the search_path
bin/make_analytical_db: added `public_validations.remake_diff_tables()`
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).
bin/make_analytical_db: don't regenerate family_higher_plant_group from the NCBI data because the lookup table is now prepopulated as part of the schema
bin/make_analytical_db: removed no longer needed setting of $schema to $public, because this is now done by psql()
bugfix: bin/make_analytical_db: when running into a public schema other than "public", also pass this to `/run export_` (which currently uses $schema instead of $public)
bugfix: bin/make_analytical_db: `/run export_`: don't take input from the terminal, because this causes rm to prompt the user (from a background task) about overwriting the previous export
bin/make_analytical_db: use new mk_table() instead of TRUNCATE/INSERT
bin/make_analytical_db: added mk_table() and use it in mk_analytical_table()
bin/make_analytical_db: added `/run export_` to make the geoscrub_input CSV export
bin/make_analytical_db: Commented out export_analytical_db because we are not yet using the analytical DB in MySQL, and it doesn't make sense to generate a large, unused CSV export each time
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.
make_analytical_db: Support the $log flag, which toggles output to the terminal or a log file
make_analytical_db: Wait for tnrs.make to acquire the tnrs.make lock if being run right after import
Moved wait on tnrs.make lock from import_all to make_analytical_db, so that running make_analytical_db for a one-time import also waits on the lock
make_analytical_db: Don't run export_analytical_db if the SQL script exits with an error
make_analytical_db: ANALYZE each table after its created so that queries use index scans instead of seq scans
make_analytical_db: Fixed bug where can't give public_ select access to all analytical_db views because this apparently adds access rather than passing through the underlying table's permissions
make_analytical_db: Give public_ select access to analytical_db views. This causes the actual access to depend on the underlying table's permissions.
make_analytical_db: Also grant USAGE on the analytical_db schema itself to bien_read, public_
make_analytical_db: Automatically call export_analytical_db when finished
vegbien_dest callers: Removed no longer needed explicit setting $prefix to "", because this is now the default value
schemas/vegbien.sql: Merged provider_view, provider_count, and owner_count into provider_count, using the combining query for Brad's data providers page at <http://bien.nceas.ucsb.edu/bien/people/data-providers/>
make_analytical_db: Also create taxon_trait materialized view
make_analytical_db: Also populate owner_count
make_analytical_db: Generate provider_count before analytical_aggregate because it's much faster
make_analytical_db: Also populate provider_count
schemas/vegbien.sql: Removed no longer needed darwin_core table. Use analytical_stem instead, which is now identical.
make_analytical_db: Added step to create darwin_core materialized view
make_analytical_db: mk_analytical_table(): Use explicit schema references everywhere. This fixes a bug where the TRUNCATE/INSERT steps on the public schema's table would reference the analytical_db view instead because they were not schema-scoped.
make_analytical_db: mk_analytical_table(): Factored table references in different schemas out into vars
make_analytical_db: Moved set -x () around just psql_verbose_vegbien so embedded $() expressions wouldn't also be in set -x (verbose) mode
make_analytical_db: Fixed bug where need to use bash instead of sh because vegbien_dest requires it
make_analytical_db: Factored analytical_* table creation code out into mk_analytical_table() function
make_analytical_db: Create analytical_db views pointing to the analytical_* versions in the public schema
schemas/vegbien.sql: Added threatened_taxonlabel derived table with generating view threatened_taxonlabel_view
make_analytical_db: Run all commands synchronously so the log file output doesn't become jumbled
make_analytical_db: Fixed bug where log file needed to be appended to instead of overwritten
make_analytical_db: Wrap each individual command in `set -x` to avoid echoing low-level commands such as sleep, wait
make_analytical_db: Moved log file to inputs/analytical_db/logs/make_analytical_db.log.sql so it would be synced along with the other import logs
make_analytical_db: Output the time at which it's run, so this is included in the log file
make_analytical_db: Store output in schemas/make_analytical_db.log
make_analytical_db: Use new psql_verbose_vegbien
make_analytical_db: Use psql_script_vegbien, which contains the necessary psq options, instead of setting those options manually
make_analytical_db: Run the SQL commands directly with psql instead of using the make_analytical_db() function. This provides incremental results and avoids running all commands in one transaction, thus preventing pgAdmin from freezing when the user attempts to access a table used in analytical DB creation (because the TRUNCATE statement fully locks the table until the entire analytical DB is built).
Added make_analytical_db
