inputs/CVS/_src/: added refresh from Mike Lee

fix: bin/map: put template: comment out the "Put template:" label so that the output is valid XML, and displays properly in a browser rather than showing a syntax error

planning/timeline/timeline.2013.xls: usability testing: added subtask to provide scientists with their requested data

planning/timeline/timeline.2013.xls: updated for progress

bugfix: bin/import_all: need to publish datasources that won't be published by `make .../import`, so that the per-datasource import XPaths that refer to TNRS/geoscrub will link up with the TNRS/geoscrub source entry instead of creating a new entry without the metadata (because the entry with the metadata was named TNRS.new/geoscrub.new)

schemas/vegbien.sql: datasource_publish(): use parameter names instead of $# because this is a PL/pgSQL function

bugfix: schemas/vegbien.sql: datasource_publish(): if the datasource to publish already has the published name, don't datasource_rm() it

bin/import_all: removed no longer needed import of geoscrub data, because analytical_stem_view is now joined to the geoscrub_output table directly, instead of using the imported canon_place entries

schemas/vegbien.sql: analytical_stem_view: join to the geoscrub_output table directly, instead of using the imported canon_place entries. this avoids the need to import geoscrub_output into VegBIEN (which is expected to take 2+ hours after the refresh), as well as the need to then refresh any datasources whose geoscrubbing input data has changed.

inputs/.geoscrub/geoscrub_output/postprocess.sql: added nullable unique index on the inputs, for use by analytical_stem_view. note that it must be nullable in order to create a match when not all of the input fields are populated. this uses array[] to create a nullable index, which is much better than column-based import and VegBIEN's use of COALESCE because the expression is the same for every type and no NULL sentinel value is needed.

schemas/VegCore/VegCore.ERD.mwb: fixed lines

schemas/VegCore/ERD/VegCore.ERD.mwb: regenerated exports and udpated image map

schemas/VegCore/ERD/VegCore.ERD.mwb: person: allow to have multiple organizations

schemas/VegCore/ERD/VegCore.ERD.mwb: split "2b. GNRS" label into two labels, one for each table GNRS is applied to

schemas/VegCore/ERD/VegCore.ERD.mwb: georeferencing: merged into geoplace, since this is actually information attached to a specific plot, etc. relating to the coordinates used in its geoplace subclass

schemas/VegCore/ERD/VegCore.ERD.mwb: geovalidatable_place: changed parent geoplace pointer to parent_boundary_WKT, since the immediate parent may not have an associated boundary to use for geovalidation (i.e. it may not be an official GADM geoplace), although ancestors further up likely will be

schemas/VegCore/ERD/VegCore.ERD.mwb: place.name: made it required because it's needed for the unique constraint to be populated properly (including for subclasses such as geoplace, which need to generate this from the coordinates)

schemas/VegCore/ERD/VegCore.ERD.mwb: place.rank: made it required, because every place should have some kind of rank indicating what type of place it is, including lower ranks (e.g. plot, individual)

schemas/VegCore/ERD/VegCore.ERD.mwb: place: added unique constraint on parent, rank, name

schemas/VegCore/ERD/VegCore.ERD.mwb: place.locality: moved to geopath, because this is actually a rank of place (i.e. below municipality) rather than a field that every place could have

schemas/VegCore/ERD/VegCore.ERD.mwb: place.locality: moved to geopath, because this is actually a rank of place (i.e. below municipality) rather than a field that every place could have

schemas/VegCore/ERD/VegCore.ERD.mwb: geoplace.official_name: renamed to name to merge with inherited field from place. documented that for geoplaces, this is the official, scrubbed name.

inputs/.geoscrub/geoscrub_output/postprocess.sql: added geovalid derived column, for use by analytical_stem_view

bin/with_all: $all: renamed to $hidden_srcs for clarity, since this now just adds the hidden (.*) datasources, rather than always using all datasources

bugfix: bin/with_all: in $all mode, just prepend the .* datasources to the user-selected (or default) @inputs, so that using $all to add these datasources doesn't inadvertently cause the action to be performed for all datasources

web/links/index.htm: updated to Firefox bookmarks. PostgreSQL: ALTER TABLE: added documentation about disabling of foreign key triggers, which is only possible by the superuser. note that marking a foreign key constraint as NOT VALID does not disable the trigger, so NOT VALID cannot be used for this purpose. this would be used to add fkeys from core VegBIEN tables to validation results tables such as the geoscrubbing results, without needing to import the validation results directly into core VegBIEN (which is time-consuming and currently must be done before input data is loaded, requiring a datasource reload to add geoscrubbing results).

bin/import_all: usage: documented that this can now be run with a custom datasources list (each of the form inputs/src/)

bin/with_all: added support for providing a custom list of inputs to run the command on

inputs/.geoscrub/geoscrub_output/postprocess.sql, run: updated runtimes

inputs/.geoscrub/geoscrub_output/run: documented full load_data() runtime (9 min @starscream)

inputs/.geoscrub/geoscrub_output/postprocess.sql: updated runtimes for refreshed data, which now has 4x as many rows (1,707,970->6,747,650)

inputs/.geoscrub/geoscrub_output/: refreshed geoscrub data. removed +header.csv because the extract now contains the header in the first row of the file.

bugfix: lib/sh/local.sh: psql(): $is_root: use `` around case statement instead of $(), because it contains an embedded unbalanced )

bugfix: inputs/.geoscrub/geoscrub_output/geoscrub.csv.run: include only the columns that Jim provided in his extract (the geoscrub table contains additional internal columns that are not part of the geovalidation data for VegBIEN). documented runtime (30 s) and upload time (1.5 min).

inputs/.geoscrub/geoscrub_output/geoscrub.csv.run: removed no longer needed setting of $local_server, $local_user (and use of $local_pg_database instead of $database) because the use_local bug in local.sh has been fixed

bugfix: lib/sh/local.sh: psql(): don't default the connection vars using use_local if running as the postgres user. in that case, connection must happen via a socket, with server="", and as the user running the command (postgres), with user="".

bugfix: inputs/.geoscrub/geoscrub_output/geoscrub.csv.run: need to manually set local_server, local_user to "" so that they do not default to their bien-user values

bugfix: lib/sh/db.sh: avoid outputting to /dev/fd/# when running as sudo on Linux, because this causes a "Permission denied" error (due to the /dev/fd/# file being owned by a different user). this is not a problem with normal redirects (>&#), because they do not use /dev/fd/# files which can have access permissions.

bugfix: lib/runscripts/util.run: to_top_file(): need to pass "$@" to to_file

lib/runscripts/util.run: to_top_file: added function for this (in addition to alias), so that this can be run from sudo in a wrap_fn command

lib/sh/db.sh: pg_as_root(): run sudo with echo_run to help debug

bugfix: lib/sh/db.sh: pg_cmd(): only set PG* connection/login env vars when the corresponding var is non-empty. there are some situations in which these must be unset (in order to use the default value), and other situations when the var must be set to something (i.e. "") to avoid it being defaulted to a value in local.sh > connection vars.

backups/TNRS.backup.md5: updated

bugfix: inputs/.geoscrub/geoscrub_output/geoscrub.csv.run: need to set $local_pg_database instead of $database because use_local (in psql()) does not currently avoid clobbering already-set versions of the applicable env vars

bugfix: lib/sh/local.sh: pg_as_root(): need to use -E (preserve environment) option to sudo, so that $schema, $table get passed through

bugfix: lib/sh/local.sh: psql(): only \set schema, table if $schema, $table are non-empty, because otherwise, you will get a "zero-length delimited identifier" error

added inputs/.geoscrub/geoscrub_output/geoscrub.csv.run to export the geoscrub table (must be run on vegbiendev)

lib/sh/local.sh: added require_remote()

lib/sh/db.sh: added pg_as_root()

lib/runscripts/util.run: added $wrap_fn to run any function via sudo, etc.

Added instructions for dependencies in the README.

Added indexes to speed up geonames-to-gadm.sql.

Without these indexes, these queries could take hours to complete.
With them, the times more closely matched the times Jim noted in the sql
comments.

Fixed a couple of syntax errors in geovalidate.sh.

Fixed a sql syntax error and a bash syntax error in the next line.

planning/timeline/timeline.2013.xls: "geoscrubbing automated pipeline": scheduled for after Paul's current set of tasks on the geoscrubbing re-run is complete. i'm budgeting several weeks for this since my understanding is that Paul is doing this part-time.

planning/timeline/timeline.2013.xls: moved "geoscrubbing automated pipeline" under "simplify import process for easier maintainability"

planning/timeline/timeline.2013.xls: geoscrubbing re-run: added subtask to spot-check reloaded geoscrubbing data

planning/timeline/timeline.2013.xls: geoscrubbing re-run: added separate subtask for "geoscrubbing data reload", since apparently it was not clear that of course the new data will need to be imported into VegBIEN before the results of the re-run are available. this is currently scheduled to happen in the next full-database import, which is the week of 10/28 in order to include further validations fixes.

planning/timeline/timeline.2013.xls: CVS validation: use timespan dot ◦ for supertask

planning/timeline/timeline.2013.xls: CVS validation: added subtasks that are similar to for FIA validation (create validation subset, create extract)

planning/timeline/timeline.2013.xls: FIA validation: split apart into subtasks, including "decide which columns to validate", which has to happen ahead of time before the extract can be generated

planning/timeline/timeline.2013.xls: fixed check marks for past (hidden) weeks, which had gotten duplicated when rows were copied together with their check marks

planning/timeline/timeline.2013.xls: fixed line heights

planning/timeline/timeline.2013.xls: fixed column width so the dates display properly in MS Excel

planning/timeline/timeline.2013.xls: right-aligned legend so it isn't too close to the "During week of:" label

planning/timeline/timeline.2013.xls: added legend:
• task
◦ timespan
✓ task progress
☑ timespan progress

planning/timeline/timeline.2013.xls: attribution/conditions of use: made it a subtask of "add missing columns" because this is related to data needed for published analyses. added dots because this is an ongoing task, that depends on data providers getting their use conditions to us.

planning/timeline/timeline.2013.xls: reload core & analytical database: moved next reload ahead to last week of October so that we can include the updated geovalidation data for the 10/31 deadline. added additional reload so that they are spaced <= 1 month apart.

planning/timeline/timeline.2013.xls: receive feedback from documentation tester: added an extra week to receive additional feedback from them in response to documentation fixes made

planning/timeline/timeline.2013.xls: attribution/conditions of use: made this a top-level task instead of a subtask of "data provider metadata", to avoid including lower-priority tasks (i.e. in the later column) in the same section as higher-priority tasks

planning/timeline/timeline.2013.xls: datasource validations: regrouped by subtask instead of by datasource, so that the high-priority subtasks get done for all datasources before moving on to lower-priority subtasks for any datasources

planning/timeline/timeline.2013.xls: reduced width of Milestone column to make room to fit an additional week on the printed page

planning/timeline/timeline.2013.xls: attribution/conditions of use: removed "(Brad/Brian/Bob/etc.)" because these are from everyone who provided or obtained data, not just Brad/Brian/Bob

planning/timeline/timeline.2013.xls: rescheduled tasks to accommodate the separate non-critical feature requests subtasks

planning/timeline/timeline.2013.xls: datasource validations: split "fix feature requests" into separate "fix critical feature requests" and "fix non-critical feature requests" tasks. rescheduled non-critical feature requests until after the other validation tasks have been completed.

planning/timeline/timeline.2013.xls: add globally-unique occurrenceID: moved up to next week because we would like to be able to get this done for the 10/31 deadline

planning/timeline/timeline.2013.xls: updated for progress

planning/timeline/timeline.2013.xls: moved "data provider metadata" before "datasource validations (spot-checking)" because conditions of use are necessary for scientists who want to publish papers based on the data (which is a key use case)

planning/timeline/timeline.2013.xls: moved "usability testing" before "datasource validations (spot-checking)" because this is most important towards reaching our goal of a useful information resource

planning/timeline/timeline.2013.xls: moved "geoscrubbing re-run", "add globally-unique occurrenceID" back under "usability testing" > "add missing columns" because these are in fact part of the usability testing

planning/timeline/timeline.2013.xls: updated for progress

planning/timeline/timeline.2013.xls: updated for progress

planning/timeline/timeline.2013.xls: "flatten the datasources to a common schema": moved to later column because the complex tasks "switching to new-style import" and "create interactive scripts for each import step" are also scheduled then. (it's unlikely we would have much time over winter break anyway, considering that there is ~1 week's worth of holidays then.)

planning/timeline/timeline.2013.xls: scheduled "simplify import process for easier maintainability"

planning/timeline/timeline.2013.xls: tasks performed by someone else (geoscrubbing re-run): changed solid check marks ✓ to open check marks ☑ to match the solid • vs. open ◦ dot convention

planning/timeline/timeline.2013.xls: documentation testing: added supertask dots. removed later dots for scheduled tasks.

planning/timeline/timeline.2013.xls: scheduled "documentation testing"

planning/timeline/timeline.2013.xls: scheduled "simplify process of mapping/adding a new datasource"

planning/timeline/timeline.2013.xls: "add globally-unique occurrenceID": moved it up to the first week when we're no longer fixing existing issues in datasources, since this has similar priority to adding missing columns discovered during usability testing (which is scheduled as an ongoing task)

planning/timeline/timeline.2013.xls: usability testing: did task breakdown (find scientists who want to use BIEN3 data, etc.) and scheduled subtasks

planning/timeline/timeline.2013.xls: moved "add missing columns" to its own supertask. used outline check mark ☑ (analogous to open circle ◦) to mark supertasks as completed which were split up into subtasks.

planning/timeline/timeline.2013.xls: later column: removed dots from scheduled items

planning/timeline/timeline.2013.xls: moved "switching to new-style import"-related steps (other than for CVS) to separate "simplify import process for easier maintainability" supertask, since this is not part of the "simplify process of mapping/adding a new datasource" task

planning/timeline/timeline.2013.xls: add any missing columns: added and scheduled step to add globally-unique occurrenceID

planning/timeline/timeline.2013.xls: geoscrubbing re-run: added dots ◦ for this for the time when it can be worked on asynchronously by Paul Sarando

planning/timeline/timeline.2013.xls: data provider metadata: added dots ◦ for the portion of "attribution and conditions of use" that can be worked on asynchronously by Brad/Brian/Bob

planning/timeline/timeline.2013.xls: scheduled "aggregated validations" during the last 2 weeks of "datasource validations (spot-checking)", because these weeks are only spent fixing issues uncovered in the remaining datasources, so there may be extra time then

planning/timeline/timeline.2013.xls: scheduled other tasks after "datasource validations (spot-checking)" is complete

planning/timeline/timeline.2013.xls: datasource validations (spot-checking): each datasource's validation supertask: added open circles ◦ spanning the length of the subtasks

planning/timeline/timeline.2013.xls: use an open circle ◦ instead of a bullet • for supertasks that have been fully split into subtasks (not just itemizing a few subtasks), so that these don't count towards the bullets (estimated workload) in each week

planning/timeline/timeline.2013.xls: use an open circle ◦ instead of a bullet • for tasks that are performed by someone other than me, so that these don't count towards the bullets (estimated workload) in each week