sql_io.py: append_csv(): In INSERT mode, print # rows read (different from # lines read if some fields contained embedded newlines) and # rows inserted (different from # rows read if some violated a constraint)
sql.py: insert(): Explicitly return None if the insert failed and a DuplicateKeyException or NullValueException was suppressed
input.Makefile: Staging tables installation: $(logInstall*Add): Fixed bug where the existing install log would be overwritten in quiet mode, even though this function should append its output to the log. Note that plain $(logInstall*) always overwrites the existing install log because it is used by the first install command.
strings.py: json_encode(): Fixed bug where '\n' and '\r' also needed to be encoded
tnrs.py: repeated_tnrs_request(): Also retry request in debug mode if an HTTPError is thrown, so that debugging info can also be obtained if there is a bug in the TNRS client
tnrs_db: Updated query for new three-level taxonpath hierarchy, where the concatenated name is now stored in identifyingtaxonomicname instead of taxonomicnamewithauthor
root map: Removed no longer needed public schema override, which is now handled by vegbien_dest
vegbien_dest: Allow user to specify a custom public schema in the $public env var. This makes custom public schema functionality available to all VegBIEN-accessing scripts, not just map.
tnrs_db: Adjusted pause, max_pause so the daemon waits longer before exiting, because after the initial TNRS run, most names have already been scrubbed and new names may not be added until the end of the import (in the case of a very large new datasource)
input.Makefile: Staging tables installation: Added cleanup, %/cleanup to clean up already-installed tables
tnrs.py: encode(): Also prepend special padding string to empty and whitespace-only strings because these names are otherwise ignored by TNRS (no response row)
tnrs_db: pause: Increased to 30 min because if no new names are available in TNRS.tnrs, there is no need to check every minute for new names (which clutters up the log file output). The pause feature is designed to allow tnrs_db to run in parallel with the import process, and process new names as they are made available, which only happens once for each partition of each datasource.
tnrs_db: Fixed bug where the new filtering out of already-scrubbed names caused names to be skipped, because the loop would both advance by the number of rows found and those rows would no longer be returned by the query, causing only every other set of rows to be processed
tnrs.py: tnrs_request(): Rewrapped lines (became >80 chars after adding profiling)
tnrs.py: tnrs_request(): Use new encode() and TnrsOutputStream to escape TNRS-invalid characters
tnrs.py: Added encode(), decode(), decode_for_tsv(), and TnrsOutputStream to handle escaping TNRS-invalid characters
strings.py: Added regexp_repl_esc()
strings.py: Added replace_all() and replace_all_re(), as well as flip_map() for use with maps for these functions
csvs.py: Added tsv_encode_map for use in creating TSVs parsed by TsvReader
csvs.py: TsvReader: Also interpret '\t' as a tab, to provide a mechanism for encoding embedded tabs
tnrs.py: gwt_encode(): Escape special characters in the string instead of removing them, so that TNRS receives the original name rather than a modified version. This will help make the submitted names match up with the returned Name_submitted.
strings.py: Added json_encode()
strings.py: Added esc_quotes()
schemas/vegbien.sql: placepath.canon_placepath_id: Changed hierarchy comment to match the taxonpath.canon_taxonpath_id comment, but with a two-level hierarchy of datasource name -> accepted name. This may later be changed to a three-level hierarchy like taxonpath.canon_taxonpath_id depending on how GNRS works.
schemas/vegbien.sql: taxonpath.canon_taxonpath_id: Changed comment to specify that taxonpaths should now be linked in a three-level hierarchy of datasource name -> concatenated name -> accepted name
schemas/vegbien.sql: taxonpath, placepath: Changed "scrubbed" to "accepted" to emphasize that the name is the accepted name returned by TNRS or GNRS, rather than merely the matched name
mappings/VegCore-VegBIEN.csv: non-TNRS taxonpaths: Store the concatenated identifyingtaxonomicname in a separate taxonpath owned by the TNRS datasource, so that it will match up with (and create a link to) the corresponding submitted TNRS name's taxonpath. This in turn is linked to the TNRS-determined accepted name, thus creating a three-level hierarchy of datasource name -> concatenated name -> accepted name.
mappings/VegCore-VegBIEN.csv: taxonomic terms: Remapped the concatenated taxonomic name to new identifyingtaxonomicname to use it directly to match up with the TNRS submitted name. Continue to map scientificNameWithAuthorship to taxonomicnamewithauthor.
schemas/vegbien.sql: taxonpath: Renamed plantcode to identifyingtaxonomicname so that it can be used to store the concatenated taxonomicname that gets scrubbed. This enables ignoring the name components when the full name is specified, so that when a TNRS submitted name's matched components are included in its taxonpath, this will not prevent a datasource's concatenated name (without the matched components) from matching up with the corresponding TNRS submitted name.
schemas/vegbien.sql: taxonpath: Made taxonomicnamewithauthor optional again and include all columns in the taxonpath_unique_within_datasource_by_name unique index so that the original name components can be stored in a separate taxonpath from the taxonpath with the concatenated taxonomic name. (The datasource's taxonpath would not always contain an entry for taxonomicnamewithauthor, so the other columns also need to be used in the unique index.)
schemas/vegbien.sql: taxonpath: Added back datasource_id, plantcode to make taxonpath datasource-specific again. This way, the original name components can still be stored in taxonpath, in addition to storing the concatenated name in a datasource-general taxonpath for use by TNRS.
inputs/.TNRS/tnrs/map.csv: Mapped columns for components of original, submitted name
mappings/VegCore-VegBIEN.csv, VegCore.csv: Removed no longer used verbatimScientificNameWithAuthorship. Use scientificNameWithAuthorship instead, and map accepted (scrubbed) names to acceptedScientificNameWithAuthorship to create the canon_taxonpath_id link.
inputs/.TNRS/tnrs/map.csv: Remapped to new accepted* taxonomic terms
mappings/VegCore-VegBIEN.csv: Mapped accepted* taxonomic terms
sql_io.py: cleanup_table(): Don't clean up the pkey, because the canonicalization involved may produce collisions (as it does for TNRS.tnrs)
sql.py: Added pkey_col_()
tnrs.py: tnrs_request(): Added comment that names containing only whitespace characters are ignored by TNRS and do not receive a response row. Our tnrs_db and reimport pipeline handles the necessary re-matching-up by just creating taxonpaths for each Name_submitted, and then letting the data import process on the following import attach to the prepopulated taxonpaths.
tnrs_db: Exclude taxonomic names which have already been scrubbed, by using a filter-out LEFT JOIN on TNRS.tnrs
tnrs.py: max_pause: Changed to 30 min because TNRS sometimes freezes for ~10 min. The freezing usually happens while the data is being uploaded rather than when it's being retrieved, so that the max_pause would not apply, but to be on the safe side, requests should not time out unnecessarily.
tnrs_db: tnrs_profiler: Use iter_text='name' for consistency with tnrs.tnrs_request()'s own profiler's iter_text
tnrs_db: Print cumulative profiling information after every TNRS request, rather than just at the end
inputs/.TNRS/tnrs/tnrs.make: Append to the log file instead of overwriting it, so that the TNRS scrubbing of each import's new taxonomic names can be included in one log file. Echo the command to the log file to identify separate runs.
TNRS-related programs: Use "names" instead of "taxons" for variable names because what's being submitted are actually verbatim taxonomic names, not official references to specific taxa
tnrs.py: tnrs_request(): Profile the TNRS request
tnrs.py: tnrs_request(): Fixed bug where initial_headers needed to be copied instead of just assigned to headers, because initial_headers is a global constant and should not be changed when the Cookie header is added
mappings/VegCore.csv: originalTaxonRank, acceptedTaxonRank: Fixed sources to use verbatimTaxonRank, not taxonRank
mappings/VegCore.csv: originalTaxonRank: Added source of the original* prefix
mappings/VegCore.csv: acceptedTaxonRank: Added source of the accepted prefix
mappings/VegCore.csv: accepted* taxonomic terms: Fixed sources of the accepted prefix to use acceptedNameUsage, not acceptedNameUsageID
mappings/VegCore.csv: original* taxonomic terms: Source the original prefix to DwC originalNameUsage, which is a more offical source than SALVIAS orig_species
mappings/VegCore.csv: Added accepted* taxonomic terms to store the scrubbed name
import_all: Clean up any new TNRS.tnrs entries before importing the TNRS data
inputs/.TNRS/tnrs/: Create using datasource schema.sql file instead of text header and postprocess.sql, for clarity and to enable using `make inputs/.TNRS/tnrs/install` to clean up the tnrs entries populated by tnrs_db
mappings/VegCore-VegBIEN.csv: Don't combine taxonRank with infraspecificEpithet if there is no infraspecificEpithet, because the taxonRank is only the infraspecificEpithet's prefix when there is an actual infraspecificEpithet. Often, taxonRank contains values like "genus" or "species" which cannot be used for this purpose.
tnrs.py: repeated_tnrs_request(): Just retry the request once with with debug turned on, to avoid cluttering the log output with the verbose debug info of multiple failed requests if the error is not resolved on retry
tnrs.py: tnrs_request(): repeated_tnrs_request(): Print all suppressed exceptions to stderr
tnrs.py: tnrs_request(): parse_response(): Include both the response headers and the response body in the InvalidResponse message
inputs/import.stats.xls: Updated import times
profiling.py: Profiler: Fixed bug where instance variable start had the same name as method start()
mappings/VegCore-VegBIEN.csv: verbatimScientificNameWithAuthorship: Set canon_taxonpath_id to 0 on the first, scrubbed taxonpath to auto-create the self reference that indicates a scrubbed taxonpath
mappings/VegCore-VegBIEN.csv: Don't forward scientificName to taxonoccurrence.authortaxoncode when importing just taxonpaths, as for TNRS
tnrs_db: Moved lower max_taxons limit to tnrs.py because it's really required to avoid crashing the TNRS server and should apply to all callers
tnrs_db: Print log message with # of taxonpaths being sent to TNRS
tnrs_db: Fixed bug where InvalidResponse was missing module name
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.
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.
