bugfix: inputs/*/Source/: use installed staging table (with blank-line data.csv) in order to also work with new-style import. this also fixes a benign diff between the by-row and by-col test outputs, where row-based import would not import the Source/ entries because there was not at least one row in the input. note that in order to ensure that all datasources are properly run, you need to check `svn st|sort` against the datasource schema names to see if any are missing.

inputs/*/logs: updated svn:ignore

inputs/*/*/logs: updated svn:ignore

bugfix: inputs/input.Makefile: SVN: add: don't add subdirs for datasources marked _no_import (e.g. datasources which only have an inputs/ dir to be listed in VegPath)

bugfix: inputs/*/Source/data.csv for new-style datasources: need to include a blank row (plus a blank header) so that the metadata values are imported at least once instead of zero times, now that there is an installed staging table that will be iterated over. the blank row did not used to be necessary, because db_xml.put_table() has a special case for metadata-only tables with no installed table, which avoids iterating over the table's rows.

lib/sql_io.py: put_table() (column-based import): complexity note: clarified that INSERT RETURNING throws an error on duplicate instead of returning the existing row. added blank line after ¶ for readability.

lib/sql_io.py: put_table() (column-based import): warning about triggers populating unique constraint-covered columns: corrected limitation to include only the unique constraint used to do the DISTINCT ON, since other unique constraints are not affected by column-based import. note that the primary key will normally not be the DISTINCT ON constraint, so trigger-populated natural keys are supported unless the input table contains duplicate rows for some generated keys.

inputs/*/Source/ for new-style datasources: use an actual staging table instead of a metadata-only table, so that metadata values can be stored in the staging table instead of the map.csv (as will be required by new-style import)

inputs/input.Makefile: SVN: $(svnFilesGlob): added data.csv, used to store versioned data (such as the empty data.csv used by Source/ tables which have their metadata in the map table instead)

schemas/util.sql: type_qual(), type_qual_name(): added comments to distinguish these similarly-named functions, one of which gets a type qualifier and the other of which gets a qualified name (not the name of a type qualifier, which one might otherwise assume)

schemas/util.sql: typeof(): support expressions that are not relative to a table (which do not have a table_ param). note that this requires removing the STRICT qualifier, so that NULL expressions will now produce an error instead of passing through as NULL.

schemas/VegCore/VegCore.ERD.mwb: relationships legend: removed inheritance of base_class from record, so that the IS-A label would not confusingly appear to apply to the record connector stub instead of to the solid line between base_class and derived_class

bugfix: schemas/util.sql: col_names(): need to exclude dropped columns (which remain included in the pg_attribute table until the next tuple rewrite), by filtering on `NOT attisdropped`. lib/sql.py table_col_names() is not affected by this because it is able to access the column names from the DB driver directly, after performing `SELECT * FROM table LIMIT 0`.

schemas/util.sql: set_col_names_with_metadata(): don't delete the metadata entries from the map table, because they are now added before the renames take place, so that the renames can simply be performed on the constant columns themselves. this does, however, require that the metadata entries are always listed last in the map.csv (which is currently the case).

lib/runscripts/table.run: map_table(): store the map table in the datasource schema, so that it can easily be referred to when using the staging tables. this also allows it to be found more easily when debugging its contents.

lib/sh/db.sh: psql(): hide the verbose CONTEXT information that is output with each NOTICE by setting the VERBOSITY psql var to terse (postgresql.1045698.n5.nabble.com/Quiet-quot-CONTEXT-quot-td1906036.html#a1906037)

*{.sh,run}: use new log-() instead of log+() with a negative #

lib/sh/util.sh: added log-() because it's non-obvious that you would otherwise have to invoke log+() with a negative #

schemas/util.sql: reset_map_table(): drop the table and recreate it instead of just creating it if it doesn't exist, so that any change to the util.map table is propagated to persistent map tables whenever they are reloaded from the map.csv

lib/runscripts/table.run: map_table(): create the map table as a persistent table in the temp schema, so that its contents can be viewed for debugging

schemas/util.sql: added drop_table()

schemas/util.sql: set_col_names(): don't perform rename if the name is not changing, to avoid cluttering the debug output with unnecessary queries

lib/runscripts/table.run: use new util.set_col_names_with_metadata() instead of util.set_col_names() so that metadata values (beginning with : ) are automatically mapped to constant columns rather than needing to add a mk_const_col() call to postprocess.sql for each of them. there are a lot of metadata value entries, especially in the Source/ tables for each datasource, so this will save time in translating the datasources to new-style import. note that this requires disabling the map_filter_insert trigger on the map table to prevent it from filtering out the metadata entries before util.set_col_names_with_metadata() can use them.

bugfix: schemas/util.sql: set_col_names_with_metadata(): need `util.` before mk_const_col(). "to", "from" need to be referenced from row_. substring() needs to start from 2 rather than 1 because PostgreSQL string indexes are 1-based.

schemas/util.sql: try_create(), create_if_not_exists(): use eval() so the executed statement will be echoed for debugging

schemas/util.sql: added set_col_names_with_metadata()

bugfix: lib/sh/sync.sh: upload(): paths: don't dereference the path itself if it's a symlink; instead canonicalize just its parent dir. this allows syncing a specific file which is a symlink, rather than syncing the symlink's target.

lib/sh/util.sh: added canon_dir_rel_path(), which canonicalizes just the parent dir if the path is a symlink, to leave the symlink itself untouched

planning/workflow/validation/: archived BIEN2 validations documents which have been superseded by planning/goals/BIEN3_derived_data_products.docx, to avoid confusion

planning/workflow/bien3_architecture.pptx: updated with clarifications made in today's conference call

bugfix: bin/map: in_is_db: inline metadata value columns (used by new-style import) so that they can be compared by value in XML simplifying functions (lib/xml_func.py)

lib/sql.py: added col_default_value(), col_is_constant(), which interface with corresponding util-schema functions

lib/sql_gen.py: added col2col_ref() for interfacing with for SQL functions that take a util.col_ref

schemas/util.sql: added is_constant(col_ref), for checking if a column has been marked "constant"

schemas/util.sql: added col_comment()

schemas/util.sql: mk_const_col(): add column comment "constant" to mark column as inlinable (needed by some mappings to have a literal value to compare)

schemas/util.sql: added col_default_value(), which evaluates the col_default_sql() expression

schemas/util.sql: added eval_expr_passthru() (passes NULL SQL through)

bugfix: schemas/util.sql: eval_expr(): need to pass ret_type_null to eval2val()

schemas/util.sql: added eval_expr() (does not require `SELECT ` before expr)

schemas/util.sql: added col_default_sql()

schemas/util.sql: eval(text, anyelement): added default polymorphic type text (can't be unknown because this would cause a "could not determine polymorphic type because input has type "unknown"" error). renamed to eval2val() to avoid overloading conflicts with eval(text) when no polymorphic type param is specified.

schemas/util.sql: added value-returning eval()

bugfix: lib/common.Makefile: $(asAdmin): need to use _postgres instead on Mac for OS X 10.8 Mountain Lion

bugfix: *Makefile: $(asAdmin) invocations of Postgres commands: need to set DB user to postgres so that it won't default to the system user _postgres

*Makefile: removed $(psqlOpts), $(psqlAsAdmin), which are now set by lib/common.Makefile

lib/common.Makefile: added $(psqlOpts), $(psqlAsAdmin)

bugfix: schemas/pg_hba.Mac.conf: use new postgres ident map instead of changing user to _postgres, because the DB user is still named postgres

schemas/pg_ident.Mac.conf: added postgres map mapping the _postgres system user to the postgres DB user for ident authentication

/Makefile: $(postgresReload-Darwin): also install pg_ident.Mac.conf

placed pg_ident.conf under version control as schemas/pg_ident.Mac.conf

*Makefile: removed $(asAdmin), which is now set by lib/common.Makefile

lib/common.Makefile: added $(asAdmin)

bugfix: schemas/pg_hba.Mac.conf: changed postgres to _postgres for OS X 10.8 Mountain Lion

schemas/util.sql: added raise_undefined_column() for use in translating other exceptions to undefined_column

bin/map: map_table(): Resolve prefixes: combined db_xml.ColRef() constructor call with creation of args (as tuple) for clarity

bin/map: update_in_label(): use in_schema instead of the map spreadsheet column name when available, to allow using one spreadsheet for all datasources (which would not have a datasource-specific spreadsheet column name)

schemas/util.sql: added mk_source_col(), which uses the schema name instead of the map spreadsheet header to get the datasource name

schemas/util.sql: added table_schema()

added planning/goals/iPlant_BIEN_Proposal_Final.pdf with Mark's e-mail notes in iPlant_BIEN_Proposal_Final.pdf.notes.txt

bugfix: schemas/util.sql: mk_map_table(): add trigger to remove metadata value entries from the map spreadsheet as it's being imported, since these do not correspond to actual columns in the staging table and will produce an error when trying to rename nonexistent columns to them. note that these entries will still need to be included properly in VegBIEN.csv, which will require changes to the VegBIEN.csv make target.

empty inputs/*/import_order.txt: added subdirs in the order they are used by inputs/input.Makefile, by running make on the inputs to auto-populate import_order.txt. import_order.txt is needed by the runscripts to run the right set of subdirs in the right order.

added inputs/.TNRS/grants.sql, with statements to provide SELECT access to bien_read. these statements must be in grants.sql to avoid them being filtered out by pg_dump_limit.

inputs/input.Makefile: added support for separate grants.sql file, which may contain GRANT statements that would normally be filtered out by pg_dump_limit

inputs/input.Makefile: sql/install: added $debug option to run the *.sql import verbosely, to display which statements are being run. this should only be used for SQL files that use COPY FROM to import data, to avoid echoing pages of insert statements.

inputs/input.Makefile: keep $(sortFile) up-to-date: use sort_file_updated=1 flag to indicate that import_order.txt has already been checked, so that recursive invocations of make don't need to recheck it. also use this flag instead of an explicit $(MAKECMDGOALS) list to prevent the $(sortFile) check from being infinite-recursively reinvoked when input.Makefile is read as part of the $(sortFile) check itself.

inputs/input.Makefile: keep import_order.txt up-to-date by running `make $(sortFile)` each time make is run. this ensures that new datasources always have import_order.txt populated when make is first run. eventually, $(tables) can be always set to $(allTables) so that this auto-updating can also be used to ensure that new subdirs added by the user always make it into import_order.txt (so that they will be included in the subdirs that get remade, etc.). import_order.txt is primarily for specifying the order of the subdirs, but some datasources also use it to filter out subdirs, so it can't yet be always updated to include the full list of subdirs. however, the filter-out usage should no longer be necessary after the switch to new-style import.

inputs/input.Makefile: added $(filter_make), used to filter the output of embedded $(shell make ...) invocations

inputs/input.Makefile: $(sortFile): use $(filter-out)->then instead of $(filter)->else for clarity

inputs/input.Makefile: added $(sortFile) (import_order.txt) target which adds any missing tables to import_order.txt

inputs/input.Makefile: added list_tables to print $(tables) for use in populating import_order.txt

web/links/index.htm: updated to Firefox bookmarks. grouped version control systems into new version control folder.

inputs/.NCBI/: added new-style import runscripts, which renamed the staging table columns to VegCore

bugfix: lib/runscripts/datasrc_dir.run, subdir.run: need to remove leading . from dir name to get installed schema name, using new dir2schema()

lib/runscripts/datasrc_dir.run, subdir.run: use new lib/sh/datasrc.sh, which contains code in common to both datasrc-related dir runscripts

added lib/sh/datasrc.sh

inputs/.TNRS/schema.sql: AcceptedTaxon: removed Annotations entry because the accepted name only contains name elements, not additional text (vegpath.org/cf_aff)

bugfix: /README.TXT: Maintenance: syncing ~/bien to ~/Dropbox/svn: added overwrite=1 so that perms transfer from the authoritative ~/bien regardless of relative mtimes

removed no longer used lib/import.sh. use lib/runscripts/table.run instead.

added inputs/*/*/header.csv for CSV inputs, which are now generated by inputs/input.Makefile %/install

added inputs/FIA/*/{VegBIEN.csv,test.xml.ref}, which are now generated by the mapping process for the joined-together tables (even though they are not used by the import, because only occurrence_all is imported)

added inputs/GBIF/_archive/

removed inputs/GBIF/Specimen/, which has been replaced by the refresh in raw_occurrence_record_plants/

added inputs/GBIF/map.csv, used to regenerate inputs/GBIF/raw_occurrence_record_plants/map.csv when raw_occurrence_record_plants is resubset

inputs/FIA/*/postprocess.sql: removed svn:executable attribute using `svn pdel svn:executable ...` now that these are not shell scripts

removed no longer needed inputs/FIA/import. use inputs/FIA/run instead.

inputs/FIA/*/import: changed to postprocess.sql for use by the runscripts

added inputs/FIA/run

added inputs/FIA/*/run. these do not yet use the postprocessing operations in */import.

added inputs/FIA/table.run (for use by table subdirs) and helper Makefile

added lib/runscripts/view.run, for use with table subdirs for views, such as inputs/FIA/occurrence_all/

planning/timeline/timeline.2013.xls: added Reload analytical database checkmark for every Rebuild core database checkmark, because these are always done together as part of the import process

bugfix: inputs/FIA/occurrence_all/import: don't re-prepend * to terms because this is a view, and the underlying columns have already been mapped

bin/src_map: support custom (or no) new_term_prefix. no new_term_prefix is useful for views whose columns have already been renamed in the underlying tables and should not have * re-prepended.

planning/timeline/timeline.2013.xls: moved longer-term goals to new August column, leaving near-term goals in July

planning/timeline/timeline.2013.xls: erased cells where a task was planned but not worked on, so that all shaded cells in the past have check marks to indicate completion of a portion of the task, and empty shaded cells in the future indicate work left to do

planning/timeline/timeline.2013.xls: updated for current progress. renamed "Rerun species range models" to "Prepare to rerun species range models" because the range modeling itself is not part of the BIEN DB development. added a column for July with the tasks that are not yet complete.

bugfix: inputs/FIA/REF_SPECIES/import: PLANT_SYMBOL_TYPE: prepended * since it's a datasource column, and needs to match up with *PLANT_SYMBOL_TYPE in other table for joins

bugfix: inputs/FIA/REF_SPECIES/import: PLANT_SYMBOL_TYPE: prepended * since it's a datasource column, and needs to match up with *PLANT_SYMBOL_TYPE in other table for joins

schemas/util.sql: try_create(): also ignore wrong_object_type exceptions thrown when trying to alter a view's columns