inputs/VegBank/plantconcept_/header.csv: Updated for new renames in vegbank.~.clean_up.sql
inputs/VegBank/plantconcept_/create.sql: Use new plantconcept_plantnames()
inputs/VegBank/vegbank.~.utils.sql: plantconcept_plantnames(): Use SQL SELECT query and WITH clause (http://www.postgresql.org/docs/8.4/static/queries-with.html) instead of temp table, because PostgreSQL does not support using temp tables inside functions that are called repeatedly (http://archives.postgresql.org/pgsql-general/2006-02/msg00516.php; it results in an "out of shared memory" error)
inputs/VegBank/vegbank.~.utils.sql: Removed hardcoded schema name, which is set dynamically by input.Makefile using `SET search_path`
inputs/VegBank/vegbank.~.utils.sql: Added plantconcept_plantnames()
inputs/VegBank/vegbank.~.utils.sql: plantconcept_ancestors(): Made function STABLE instead of IMMUTABLE because it accesses DB tables
inputs/VegBank/vegbank.~.clean_up.sql: Fixed bug where the original plantconcept table's columns needed to be renamed, rather than the derived table plantconcept_'s. Note that this script runs before any derived tables are created, so this would be the wrong place for these statements if the derived table's columns did need to be renamed.
input.Makefile: Staging tables installation: $(dbExports): Sort each group of .sql files in lexical order, since $(wildcard) apparently does not sort them that way automatically on vegbiendev
inputs/import.stats.xls: Updated with stats from latest import. Corrected input row count of CTFS.TaxonOccurrence, which had been set to the inserted row count (which is right above it in the log file).
schemas/vegbien.sql: taxonrank: Added comment documenting source of values
inputs/VegBank/taxonobservation_/map.csv: Mapped observation_id to eventID
inputs/TEAM/: Added VL
inputs/VegBank/: Added taxonobservation_/
inputs/VegBank/: Added plantconcept_/
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Ignore errors if create.sql already added a primary key
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Provide the table name as a var (:table) to the query
inputs/VegBank/vegbank.~.clean_up.sql: Prevent "column name specified more than once" errors when tables are joined
to_do/timeline.doc: Updated to reflect additional time that validations will take, and analytical DB's dependency on it
Added validation/
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Time the install
inputs/VegBank/vegbank.~.utils.sql: plantconcept_ancestors(): Renamed ancestor_id output param to plantconcept_id for clarity and so it can be directly USING-joined with plantconcept on plantconcept_id
inputs/VegBank/: Added vegbank.~.utils.sql (which runs after vegbank.sql), for use by tables' create.sql scripts
inputs/import.stats.xls: Updated with stats from latest import
inputs/VegBank/: Added observation_/
inputs/VegBank/: Added vegbank.~.clean_up.sql (which runs after vegbank.sql), to prevent "cannot alter type of a column used by a view or rule" errors
inputs/VegBank/: Added plot_/
inputs/VegBank/: Added logs
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Log the output to the install log, just like for other %/install targets
vegbien_dest: schemas: Added public explicitly, even though it's already in the default search_path, in order to shadow any datasource's tables of the same name as a VegBIEN table (such as in VegBank). (VegBIEN tables are referenced without a schema, while datasource tables are referenced with a schema, so collisions are not a problem after this fix.)
input.Makefile: Staging tables installation: sql/install: Fixed bug where needed space before \ at end of line, because one is not automatically added in a recipe command (although it's added elsewhere)
sql.py: run_query(): DuplicateException: Also match "of relation" part of error message, so that parsed column name does not contain "of relation"
subtract: Made it case- and punctuation-insensitive
mappings/: Removed no longer needed Veg+.cs-VegBIEN.csv, which is now the same as Veg+-VegBIEN.csv which was derived from it
join: Documented that it's case- and punctuation-insensitive.
bin/map: map_table(): Refactored to map simplified to original column names first and then determine column index for each original name, in order to avoid trying to recover the original name from a simplified name where multiple original names might collide onto the same simplified name. Documented that it's case- and punctuation-insensitive.
intersect, union: Made case- and punctuation-insensitive. mappings/Veg+-VegBIEN.csv: Removed no longer needed duplicate entries for each first letter case, which must now be removed for case- and punctuation-insensitive intersect/union to work. Note that the SpeciesLink `svn diff` hides _alt entry 0, which contains one of the removed duplicate columns that appears in the diff.
bin/map: map_table(): Resolve all mappings and prefixes after applying maps.simplify()
inputs/SpeciesLink/Specimen/map.csv: _alt all scientificNameAuthorship synonyms together in one _alt
schemas/functions.sql: _alt(): Added extra numbered parameters. Eventually these will need to be converted to variadic args, but this will require special support from column-based import.
join: Use new maps.simplify()
maps.py: Added simplify()
join: Match terms with non-alphanumeric chars removed
join: Match terms case-insensitively
Added inputs/TEAM/
sql_io.py: put_table(): Creating the into table: into_out_pkey: If is_function, just use "result" as the output column name, without prefixing the function name. This shortens the table names of function calls on function calls, which need a fixed column name to detect which columns are function results and use just the table names for those columns.
input.Makefile: Documentation: $(steps): Fixed bug where import make target needed to be changed to new single-table import target
schemas/vegbien.sql: analytical_db_view: Changed LEFT JOINs to JOINs where tables contain information that's required for the analytical DB. This should also enable the PostgreSQL query planner to make additional join optimizations, in the hopes of avoiding disk-space-intensive hash joins.
Replaced repr() with strings.urepr() (or equivalent) everywhere needed, to avoid future UnicodeEncodeErrors
Replaced str() with strings.ustr() (or equivalent) everywhere needed, to avoid future UnicodeEncodeErrors
sql.py: map_expr(): Replacing without quotes: Don't match unquoted name where it's preceded or followed by '.', because this could be a '.' embedded in a punctuation-containing column name, such as those frequently used by column-based import. Note that because database-internal names currently do not contain punctuation, this situation only occurs when a database-internal expression (such as a check constraint condition) is replaced in two steps, and the first step introduces punctuation-containing column names into the expression.
schemas/vegbien.sql: project: Don't require projectname to be specified when sourceaccessioncode is provided
sql_gen.py: ensure_not_null(): If type_ is set, cast the column to it if needed
README.TXT: Data import: Added testing steps to perform on local machine before running the import
README.TXT: Documentation: Redmine-formatted list of steps for column-based import: Updated make command for new table subdir name
sql.py: run_query(): Parse "types cannot be matched" error as MissingCastException to type text
sql_io.py: put_table(): Creating the into table: Fixed bug where in_pkey and out_pkey names would collide if the output and input pkeys have the same name (as is the case for SALVIAS.projects). This entails changing out_pkey to new into_out_pkey wherever the into table's out_pkey is created or referenced.
sql_io.py: put_table(): Combining output and input pkeys in inserted order: Changed sql_gen.Table to sql_gen.Col when creating the column references (they have a similar effect, so using the wrong type did not cause any tests to fail)
README.TXT: Added steps before the import to `svn up` and update the schemas
README.TXT: Merged Backups > After a new import and Data import sections into one Data import section that contains the steps to perform and back up an import. Note that many `svn diff` lines result from a change in indentation.
sql_io.py: put_table(): Combining output and input pkeys in inserted order: Fixed bug where column references would be ambiguous if the output and input pkeys have the same name (as is the case for SALVIAS.projects)
schemas/functions.sql: Added _nullIf() overload where the type param has type text, to handle cases where row-based import auto-casts all args to text in response to a 'could not determine polymorphic type because input has type "unknown"' error
schemas/vegbien.sql: party: Removed party_datasource unique index because it was causing problems with column-based import (due to multiple unique indexes covering the same columns in different ways), and because it prevented creation of more than one party per organization
xml_func.py: _if(): Documented that it must be run to remove conditions that functions._if() can't handle
README.TXT: Datasource setup: Testing: Added step to test column-based import (by_col=1), because it is stricter about types than row-based import and sometimes fails when row-based import succeeds
schemas/functions.sql: _nullIf(): Polymorphically support other datatypes besides text
bin/map: Clearing errors table: Fixed bug where needed to check if sql_io.errors_table() returned None (indicating that the errors table didn't exist) before calling sql.drop_table()
bin/map: Clearing errors table: Fixed bug where needed to use sql.drop_table() instead of sql.truncate() now that errors tables are not created until column-based import runs
input.Makefile: Maps validation: $(missingMappingsCmd): Fixed bug where need to use system's sort, not bin/sort, now that bin/ is added to the PATH by this makefile
inputs/SALVIAS/verify/plots.ref: Regenerated on PostgreSQL staging tables. The orders have changed slightly because this is derived from a PostgreSQL translation of the queries, with corresponding changes in collations and NULL sort orders. The counts have also changed slightly, possibly due to the changes Brad made to the salvias_plots database on nimoy after the initial version was downloaded. (The current counts are correct according to the current salvias_plots database.)
inputs/SALVIAS/verify/plots.ref.sql: # locations: Fixed bug where a NULL value in LatDec or LongDec would propagate to the concatenated value, reducing its uniqueness
inputs/SALVIAS/verify/plots.ref.sql: Retrofitted to work with PostgreSQL staging tables
schemas/vegbien.sql: project: Added project_unique_name_date unique index for projects that don't have a sourceaccessioncode
inputs/SALVIAS/plotMetadata/map.csv: Remapped project_id to project.sourceaccessioncode
inputs/SALVIAS/: Added projects/
input.Makefile: Sources: $(catSrcs): Fixed bug where needed to use cat_csv even if subdir was not actually a CSV table, because this also cats the header.csv file created for a subdir that references an already-installed staging table
input.Makefile: Existing maps discovery: Fixed bug where top-level logs dir needed to be excluded from list of subdirs that are treated as tables
my2pg: Prepend 'SET standard_conforming_strings = off;' because this defaults to on starting with PostgreSQL 9.1
schemas/vegbien.sql: locationevent: Made location_id optional when sourceaccessioncode is provided, since a sourceaccessioncode is globally unique and does not require a location to scope it
input.Makefile: Staging tables installation: Store install logs for full-DB exports in new logs subdir of main dir. This also fixes a bug where the install log itself was considered a DB export, because its extension was .log.sql.
Added inputs/SALVIAS/logs/
input.Makefile: SVN: add: Also add logs subdir of main dir, to store install logs for full-DB exports
mappings/VegCore-VegBIEN.csv: if subplot: Also forward locationID and plotName to the location of the parent locationevent (in addition to the parent location of the location), in order to "complete the diamond" connecting subplot locationevent -> (parent plot locationevent, subplot location) -> parent plot location
sql_io.py: cleanup_table(): NullValueException: Log the caught exception so it's clear that the update is being retried
input.Makefile: Staging tables installation: %/install: Fixed bug where $(if $(isRef)) needed to be checked before $(if $(nonXml)) because a subdir referencing an already-installed staging table must be treated specially by ignoring its autogenerated header.csv file, and not trying to install that file as if it were itself CSV data
my2pg, my2pg.data: Fixed bug where replacement for '0000-00-00' date needed to be wrapped in single quotes
input.Makefile: sql/install: Log the installation of a full-DB export to a log file in the main dir
input.Makefile: Staging tables installation: %/install: Factored out stderr logging into $(logInstall)
input.Makefile: Support empty subdirs referencing an already-installed staging table everywhere, by replacing $(isCsv) with new $(nonXml) where needed
inputs/SALVIAS/: Switched to using the DB export's staging tables instead of the exported CSVs
input.Makefile: Staging tables installation: Treat empty subdirs as referencing an already-installed staging table, and run cleanup and header export operations on them
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Factored out cleanup and header export operations for reuse in other types of table subdirs
input.Makefile: Staging tables installation: `%/install: %/create.sql`: Removed deprecated (but benign) errors_table_only option to csv2db. Run csv2db without a command in order to clean up the created staging table.
sql_io.py: cleanup_table(): Removed no longer used cols param
csv2db: When no command is specified, just clean up the specified table
sql_io.py: cleanup_table(): Always clean up all columns in the table
sql_io.py: cleanup_table(): Handle NullValueExceptions (due to setting values to NULL in a NOT NULL column) by dropping the NOT NULL constraint
sql.py: Added drop_not_null()
sql_gen.py: is_text_col(): Also consider character varying to be a text type
