tnrs_db: Profile the TNRS requests. This involves using a finally block to ensure that the profiling stats are printed even if the program exits with an error.

tnrs_db: Reduced the chunk size to avoid slowing down the TNRS server

inputs/.TNRS/tnrs/tnrs.make: Added log option which outputs to the terminal instead when set to ""

tnrs_db: Added log messages for Making TNRS request and Storing TNRS response data so that if the TNRS daemon pauses, it's obvious which step it's waiting on

sql.py: insert(): ignore optimization: Fixed bug where needed to run insert_select() recoverably so that the aborted transaction is rolled back after a DuplicateKeyException or NullValueException

tnrs_db: If tnrs.repeated_tnrs_request() stil throws InvalidResponse, skip the current set in case its data caused the error. Note that it will still be tried again the next time tnrs_db is run.

mappings/VegCore-VegBIEN.csv: Don't forward scientificName to taxonoccurrence.authortaxoncode when importing just taxonpaths, as for TNRS

repeated_tnrs_request(): When retrying after an invalid response, output protocol info for debugging

inputs/Makefile: Import logs: Don't download .TNRS/tnrs/tnrs.make.log by default because it changes each time `make inputs/.TNRS/tnrs/tnrs-remake` is run, and any version downloaded for debugging should be preserved. It can still be downloaded by setting the tnrs_log env var.

tnrs_client, tnrs_db: Use new tnrs.repeated_tnrs_request()

tnrs.py: Added repeated_tnrs_request() to retry a TNRS request which returned an invalid response

db_xml.py: put_table(): Fixed bug where pkeys_loc needed to be initialized. Note that this bug was only triggered when importing a table with zero rows (in this case, the initial empty TNRS.tnrs table), because otherwise it would be set in the loop.

inputs/Makefile: Import logs: Also download inputs/.TNRS/tnrs/tnrs.make.log

inputs/Makefile: Import logs: Use new $(rsync*) to also sync datasources starting with ., such as .TNRS

lib/common.Makefile: rsync: Added $(rsync*) to rsync all files, including those starting with "."

tnrs.py: parse_response(): Raise custom InvalidResponse exception instead of SystemExit, so callers can catch the exception and respond to it

mappings/VegCore-VegBIEN.csv: taxonpath.taxonomicnamewithauthor _join_words mappings: Added space after taxon rank prefix (var., etc.) for infraspecific ranks

import_all: Start the tnrs daemon using `make inputs/.TNRS/tnrs/tnrs-remake &`

Added inputs/.TNRS/tnrs/tnrs.make to run tnrs_db on VegBIEN

Added tnrs_db to scrub the taxonpaths in VegBIEN using TNRS

Regenerated vegbien.ERD exports

schemas/vegbien.sql: taxonpath: Made it datasource-general and uniquely identified only by its taxonomicnamewithauthor so that the taxonpaths imported by the TNRS datasource will be matched and used directly when the other datasources are imported

schemas/vegbien.sql: taxonpath: taxonpath_unique_within_datasource_by_name unique index: Just do duplicate elimination on the taxonomicnamewithauthor, since that is now a required field and is generated by concatenating all the other fields. Note that the inserted row counts change slightly because the concatenation makes some names equal that are split among the fields differently, such as when the genus is included in the species field.

db_xml.py: put(): Added _alt optimization that just returns the first arg if it's non-NULL

sql_gen.py: Added is_nullable()

schemas/vegbien.sql: taxonpath.taxonomicnamewithauthor: Made it NOT NULL, so that all taxonpaths would have a concatenated name to feed to TNRS

mappings/VegCore-VegBIEN.csv: taxonomic terms: Changed _first to _alt because some datasources have NULL values in scientificNameWithAuthorship or scientificName, so it can't just be used in place of the joined-together taxonomic ranks

db_xml.py: put(): Parse input columns and process values in separate loops, so that structural XML function optimization code can be inserted between them

sql_io.py: put_table(): Removed comment that can support in_tables of any fixed-size iterable type, because the iterable must be ordered so that the first table can be treated specially

sql_io.py: put_table(): Support in_tables of any fixed-size iterable type

mappings/Veg+-VegCore.csv: cationExchangeCapacity->cationExchangeCapacity_cmol_kg mapping: Removed ? prefix because a mapping to only one set of units is unambiguous (if additional units for cationExchangeCapacity are found, this will become an ambiguous mapping). Note that canon automatically removes punctuation from VegCore terms, so this mapping would previously have had the ? prefix autoremoved anyway (both in inputs/*/*/map.csv and recently also in Veg+-VegCore.csv).

mappings/Makefile: .Veg+-VegCore.csv.last_cleanup: Translate VegCore terms using itself so that any mapping to another Veg+ term automatically becomes a mapping to a VegCore term. .VegX-VegCore.csv.last_cleanup: Translate VegCore terms using Veg+-VegCore.csv to keep the terms up to date.

mappings/VegX-VegCore.csv: Translated VegCore terms using Veg+-VegCore.csv

mappings/Makefile: .VegCore.csv.last_cleanup, .VegCore-VegBIEN.csv.last_cleanup: Apply Veg+-VegCore.csv so that terms can easily be renamed just by adding a mapping in Veg+-VegCore.csv, which will auto-translate all places that use the term. .VegCore-VegBIEN.csv.last_cleanup: Canonicalize to VegCore.csv so case changes in VegCore terms will automatically propagate to VegCore-VegBIEN.csv.

mappings/VegCore-VegBIEN.csv: Mapped verbatimScientificNameWithAuthorship, so that it links a verbatim taxonpath to the scrubbed taxonpath created from the primary taxonomic terms

mappings/VegCore.csv: Renamed unscrubbedScientificNameWithAuthorship to the more standard verbatimScientificNameWithAuthorship, which is available now that the original taxondetermination terms use the original* prefix

mappings/VegCore.csv: Renamed verbatim* taxonomic terms to original* because in most datasources, they are in fact for the original taxon determination of the organism (which can be a completely different name than the primary determination), rather than merely unscrubbed versions of the primary taxonomic name elements. Note that SALVIAS's orig_* terms do appear to be merely unscrubbed versions, but it's not a problem to add an additional taxon determination for them.

sql.py: pkey(): Get the table's actual primary key column, rather than just using the first column in the table. Continue to return the first column in the table if the table has no primary key.

inputs/.TNRS/tnrs/postprocess.sql: Use :table var instead of hardcoding the table name

inputs/.TNRS/tnrs/postprocess.sql: Also add a primary key on Name_submitted, to prevent duplicate entries

inputs/.TNRS/tnrs/: Added postprocess.sql which makes Name_submitted NOT NULL

sql.py: insert(): ignore mode: Also ignore NullValueException

input.Makefile: Staging tables installation: %/install: Support custom postprocess.sql which specifies commands to run after the table is imported

import_all: Added import of .TNRS datasource, which happens synchronously before other datasources are imported

Moved tnrs table from public (schemas/vegbien.sql) to its own TNRS schema, which is created by a new .TNRS datasource. Note that .TNRS is included in the automated testing, but not yet in the import.

mappings/VegCore-VegBIEN.csv: Restored subplotID -> if subplot cond mapping, which had been overwritten

inputs/ACAD/Specimen/map.csv: Remapped scientificName to scientificNameWithAuthorship

sql_io.py: append_csv(): Using INSERT: Use ignore mode to support inserting rows into a table with a unique constraint

sql.py: insert(): Added ignore optimization that just suppresses any DuplicateKeyException on the client side, to avoid needing to create a wrapper function just to insert-ignore one row

mappings/VegCore-VegBIEN.csv: Synchronized verbatim* and non-verbatim taxonomic terms' mappings

mappings/VegCore.csv: Added special term unscrubbedScientificNameWithAuthorship

mappings/VegCore.csv: Added verbatimSubspecies, verbatimVariety, verbatimForma, verbatimCultivar (already mapped in VegCore-VegBIEN.csv)

mappings/Makefile: .VegCore.csv.last_cleanup: Also remake VegCore-VegBIEN.unsourced_terms.csv here, not just in .VegCore-VegBIEN.csv.last_cleanup, so that the unsourced_terms.csv will be remade if the user adds the missing sources to VegCore.csv

mappings/Makefile: VegCore-VegBIEN.unsourced_terms.csv: Factored remake code into its own make target

mappings/VegCore-VegBIEN.csv: verbatim* taxonomic terms: Added taxonomicnamewithauthor mappings analogous to those for the non-verbatim taxonomic terms

mappings/VegCore.csv: Added verbatimScientificNameWithAuthorship

Added inputs/.public/, which stores mappings that manipulate VegBIEN itself

forwarding.Makefile: Differentiate between subdirs which can be sent a command and subdirs which will receive a command broadcast to "all" subdirs

README.TXT: Data import: Starting column-based import: Use import_all, which now supports passing custom vars like by_col=1

import_all: Pass any args, such as vars, through to with_all

with_all: Support additional command-line args for the make target, such as vars

sql_io.py: append_csv(): Check that the CSV's header matches the table's columns

schemas/vegbien.sql: Added tnrs table to hold contents of TNRS response

input.Makefile: Existing maps discovery: $(anyMap): Inlined patterns used because they are only used here

schemas/vegbien.sql: taxonpath_canon_taxonpath_id_self_ref(), placepath_canon_placepath_id_self_ref(): Fixed bug where the pkey could only be prepopulated if it was not already set, in order to support UPDATE as well as INSERT statements

schemas/vegbien.sql: taxonpath.canon_taxonpath_id, placepath.canon_placepath_id: Fixed comment describing that the special value 0 creates an automatic self-reference

schemas/vegbien.sql: taxonpath.canon_taxonpath_id, placepath.canon_placepath_id: Added trigger to automatically create a self-reference (indicating a scrubbed name) when set to the special value 0

input.Makefile: Staging tables installation: `%/install: %/create.sql`: Don't add a row number column to the created table because it is now added automatically to the temp table by column-based import (row-based import now also does not require a pkey for DB inputs)

bin/map, db_xml.put_table() (row-based and column-based import): Don't sort the input table by its pkey, in order to support input tables with no pkey. Note that reading the input table in table order and having this match the input flat file's order is only possible with sql_io.import_csv()'s truncation of the table on a failed import, which ensures that the rows will be stored in inserted order.

input.Makefile: Staging tables installation: Removed no longer used $(isJoinedTable). Note that it is no longer necessary for joined tables to be suffixed with ".src" to prevent the creation of a row_num column, which collided during joins.

csv2db: Removed no longer used has_row_num param

sql_io.py: import_csv(): Don't add a row number column to the created table because it is now added automatically to the temp table by column-based import (row-based import now also does not require a pkey for DB inputs)

bin/map, db_xml.put_table() (row-based and column-based import): Don't sort the input table by its pkey, in order to support input tables with no pkey. Note that reading the input table in table order and having this match the input flat file's order is only possible with sql_io.import_csv()'s truncation of the table on a failed import, which ensures that the rows will be stored in inserted order.

sql_io.py: import_csv(): Only do the import in a savepoint if using COPY FROM, to allow autocommits after each insert and thus make rows visible immediately after they are inserted

db_xml.py: put_table(): Subsetting in_table: Add a row number column if in_table does not already have a pkey

db_xml.py: put_table(): Subsetting in_table: Copy all of in_table's structure, rather than just the column types, by using sql.copy_table_struct() and sql.insert_select(). This preserves pkeys and NOT NULL constraints, which are useful for column-based import.

db_xml.py: put_table(): Subsetting in_table: Create in_table as a completely new sql_gen.Table instead of copying full_in_table and relying on sql.run_query_into() to set is_temp and remove the schema

sql.py: add_row_num(): Use if_not_exists in order to abort if the column already exists rather than adding a version #

sql.py: add_col(): Added if_not_exists param to abort if the column already exists rather than adding a version #

db_xml.py: put_table(): Removed no longer accurate comment that full_in_table will be shadowed (hidden) by the created temp table. (The temp table is now named differently, so the shadowing does not occur.)

db_xml.py: put_table(): Replaced no longer accurate Recurse comment with Import data. Rewrapped lines.

sql_io.py: import_csv(): Factored insertion code out into new append_csv()

README.TXT: Data import: `make test by_col=1`: Replaced errors explanation with pointer to updated explanation in the Testing section

xml_func.py: Removed no longer used _name(). Use _join_words() instead.

mappings/VegCore-VegBIEN.csv: Use new, more general _join_words() instead of _name()

mappings/Veg+-VegCore.csv: Prefix ambiguous terms' VegCore replacement with "?" so it's visually flagged in map.csv, in the same way that unmatched terms are flagged with a "*" prefix

mappings/VegCore-VegBIEN.csv: Taxonomic terms: Also join terms together in taxonomicnamewithauthor if scientificNameWithAuthorship is not provided, for use by TNRS

xml_func.py: Simplifying functions: Merging: Added _join_words()

inputs/ARIZ/Specimen/map.csv: Remapped ScientificNameAuthor to scientificNameWithAuthorship because it contains the binomial in addition to the authority

schemas/functions.sql: Added _join_words()

input.Makefile: Paths: $(datasrc): Remove any "." prefix from the subdir name. The "." prefix allows a subdir to be hidden from the normal import process.

db_xml.py: put_table(): Allow caller to specify custom partition_size

tnrs.py: tnrs_request(): Return the CSV stream directly instead of reading it into a string

tnrs.py: tnrs_request(): Moved CSV-download-specific functionality from do_request() to the Download section

inputs/import.stats.xls: Updated import times

tnrs.py: tnrs_request(): Return the response instead of printing it to stdout

schemas/py_functions.sql: _namePart(): Fixed bug where it was returning the empty string instead of NULL

sql_io.py: import_csv(): Documented that sql.truncate() MUST be run so that the rows will be stored in inserted order, and the row_num added after import will match up with the CSV's row order

sql.py: add_row_num(): Add distinguishing comment to ADD COLUMN statement so that it will be cached. The distinguishing comment is required because sometimes column names are truncated, leading to unwanted collisions with previously-cached ADD COLUMN statements. It provides a way of distinguishing the full column name behind a particular ADD COLUMN statement.

sql_io.py: import_csv(): Free memory used by deleted rows from any failed import. Documented that sql.create_table() is not rolled back if the import fails, but instead is cached, and will not be re-run if the import is retried.