README.TXT: Data import: Run import_all in the background, because it needs to import all the taxonomic names synchronously before it can start the datasource import in the background

Regenerated vegbien.ERD exports

inputs/.TNRS/tnrs/map.csv: Mapped Unmatched_terms to morphospecies because the morphospecies is what's left once named ranks are matched

mappings/VegCore-VegBIEN.csv: Mapped morphospecies

mappings/VegCore.csv: Added morphospecies

schemas/vegbien.sql: taxonpath: Added morphospecies

inputs/.TNRS/tnrs/test.xml.ref: Updated for latest TNRS output

inputs/.TNRS/tnrs/map.csv: Infraspecific_rank_2, Infraspecific_epithet_2_*: Mapped to UNUSED because they do not appear to be provided by TNRS (it just puts additional infraspecific names in Unmatched_terms)

inputs/.TNRS/tnrs/map.csv: Omit Infraspecific_rank because Name_matched_rank contains the unabbreviated rank and is provided more often

mappings/VegCore-VegBIEN.csv: Also map TNRS-parsed infraspecificEpithet (Infraspecific_epithet_matched) to taxon at the infraspecies rank

mappings/VegCore-VegBIEN.csv: Also map TNRS-parsed taxonomic ranks to the tree of life in the taxon table

schemas/vegbien.sql: taxon: Added comment that this table stores the tree of life

mappings/VegCore-VegBIEN.csv: accepted taxonomic terms: Use new _set_canon_taxonpath() to set the canon_taxonpath_id after the taxonpath has been inserted, so that if the taxonpath is an accepted name (scrubs to itself), it will link up to the just-inserted taxonpath with the taxonomic ranks parsed out, rather than to a new taxonpath containing only the few taxonomic ranks of the accepted name that TNRS provides. In particular, this (together with the tnrs_accepted_names sorting index on TNRS.tnrs) ensures that an accepted name is imported with its genus and species parsed out by TNRS instead of concatenated together in the Accepted_name_species field (genus+species). This enables the individual taxonomic ranks to be used in constructing the leaves of the tree of life (the taxon table).

sql_io.py: put_table(): Fixed bug where row_ct_ref was incorrectly being incremented when the iteration is a function call. This bug only occurred in row-based mode, because the DB cursor for a function call is not stored in column-based mode.

inputs/.TNRS/tnrs/map.csv: Use Name_matched_author/Name_matched_accepted_family instead of Author_matched/Family_matched because these fields are provided more often, due to being determined from the matched name itself rather than from the original string. This helps to fill in as many fields as possible. For accepted names (which scrub to themselves), this is especially important, because it adds the accepted name's family, which is not present in the input taxonomic name.

xml_func.py: process(): Fixed bug where need to preserve complex functions that have unevaluated XML nodes as arguments, because XML nodes are not accepted by sql_io.put() (they are handled by db_xml.put())

schemas/vegbien.sql: Renamed set_canon_taxonpath() to _set_canon_taxonpath() (adding _ prefix) so that db_xml.put() treats its arguments as arguments rather than as children with fkeys to parent

schemas/vegbien.sql: Added set_canon_taxonpath() to set a taxonpath's canon_taxonpath_id after it has been created

Added inputs/.TNRS/tnrs/cleanup.sql to cluster TNRS.tnrs on tnrs_accepted_names. This keeps TNRS.tnrs sorted with the accepted names first.

input.Makefile: Staging tables installation: %/cleanup: Also run any custom cleanup.sql provided in the subdir. %/install: Removed processing of postprocess.sql because no datasources are using it and because cleanup.sql can now be used for this purpose.

inputs/.TNRS/schema.sql: tnrs: Added tnrs_accepted_names index, which sorts accepted names first, and cluster the table on this index. This ensures that the component-parsed entries for accepted names are created before any verbatim names that point to them.

input.Makefile: Staging tables installation: %/cleanup: Documented that this removes any index comments, due to a PostgreSQL bug. (This occurs because ALTER TABLE recreates the index but not its comment.)

inputs/.TNRS/schema.sql: Removed hardcoded schema name

inputs/.TNRS/tnrs/map.csv: Changed Name_matched_accepted_family comment to match analogous Name_matched_author comment

inputs/.TNRS/tnrs/map.csv: Remapped Author_matched as the scientificNameAuthorship instead of Name_matched_author, because Name_matched_author contains the author based on the matched name, not the author in the original string, so it's not strictly from the original name

mappings/VegCore.csv: Added acceptedBinomial, originalBinomial

mappings/VegCore.csv: Added binomial

inputs/.TNRS/tnrs/map.csv: Mapped Specific_epithet_matched

Added inputs/test_taxonomic_names/

mappings/VegCore-VegBIEN.csv: taxonoccurrence.authortaxoncode: Only populate if needed to distinguish the taxonoccurrence within a plot

schemas/vegbien.sql: placepath: Removed no longer used placepath_unique constraint on place_id. Removed place_id from placepath_unique_within_datasource_by_name unique index because otherranks is now used to store custom ranks.

schemas/vegbien.sql: placepath: Removed no longer used placepath_unique constraint on place_id. Removed place_id from placepath_unique_within_datasource_by_name unique index because otherranks is now used to store custom ranks.

schemas/vegbien.sql: taxonpath, placepath: Added *_required_key check constraints to ensure that empty entries are not created when a row does not have taxonpath/placepath data

import_all: Use new dedicated cleanup make target to clean up TNRS.tnrs

tnrs.py: encode_map: Added hidden minus sign, which TNRS removes

csvs.py: tsv_encode_map: Escape \n as \n (instead of as a \ followed by a newline) for clarity. Added escape for \r by using strings.json_encode_map. TsvReader: Decode all escapes in tsv_encode_map.

tnrs.py: encode_map: Added × (times), which TNRS replaces with x

tnrs.py: encode_map: Added " and ', which TNRS removes when at the beginning or end

tnrs.py: encode_map: Documented why each character needs to be encoded

tnrs.py: encode_map: Removed '&', which is actually not a special character for TNRS (although ';' is)

tnrs.py: encode_map: Added '_', which TNRS replaces with space

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: 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