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.
sql_io.py: import_csv(): Fixed bug where the added row number column needed to be named row_num instead of _row_num to be autodetected as the pkey column (sql.pkey_col) by sql.pkey() and to avoid name collisions with the row number column added in column-based import
sql.py: add_row_num(): Support custom row number column name
csv2db: Use new sql_io.import_csv()
sql_io.py: Added import_csv()
csv2db: Don't truncate the table before loading rows because it has just been created, and is therefore empty. This statement may be left over from a time when the table was created only once, and its creation was not rolled back if the import fails.
sql_io.py: cleanup_table(): Print 'Cleaning up table' log message
sql_io.py: cleanup_table(): Also vacuum and reanalyze table
tnrs_client: Use new tnrs.tnrs_request()
Added tnrs.py
tnrs_client: Factored TNRS request code into separate function tnrs_request()
inputs/VegBank/taxonimportance/map.csv: Documented that taxonimportance is not 1:1 with taxonobservation
mappings/VegCore-VegBIEN.csv: Removed unnecessary /_first/# suffix for multiple terms in the same _exists expression, because _exists() only checks whether its node is non-empty, and it does not matter how many child nodes it contains
schemas/vegbien.sql: taxonoccurrence: taxonoccurrence_unique_within_locationevent unique index: Fixed bug where locationevent_id needed to be enclosed in COALESCE so that the unique constraint also applies to rows with NULL locationevent_ids (there is no other unique constraint handling these rows)
README.TXT: Documented that if the row-based and column-based imports produce different inserted row counts, this usually means that a table is underconstrained (the unique indexes don't cover all possible rows). The inserted row count difference occurs because column-based import collapses empty table rows into one insert, while row-based import performs an insert of the empty row for each input row. Without a unique index to combine multiple row-based inserts, extra rows will be added.
sql_io.py: put_table(): Warn if inserting empty table rows
schemas/functions.sql, py_functions.sql: Added schema comment that functions must always return NULL in place of the empty string, to ensure that empty strings do not find their way into VegBIEN. Note that row-based import automatically removes empty strings because the intermediate values are stored in XML and our XML DOM traversing code auto-replaces the empty string with NULL. Column-based import, on the other hand, does not, because the intermediate data is stored in database temp tables instead of a DOM tree.
root map: Fixed custom public schema override to work with schemas lists that include public, by replacing public with the new public schema instead of just appending it
inputs/*/*/map.csv: Prefix a * to every term that's not in Veg+ for easy identification of unmapped terms when editing map.csv. Note that canon will remove the * when it finds a matching Veg+ term.
ins_col: Added column fill value param
inputs/VegBank/stemcount/map.csv: Fixed bug where taxonimportance_id needed to point to aggregateOccurrenceID instead of taxonOccurrenceID
mappings/VegCore-VegBIEN.csv: Don't forward individualID to taxonoccurrence.sourceaccessioncode when aggregateOccurrenceID is present
Regenerated vegbien.ERD exports
schemas/vegbien.sql: placepath.otherranks comment: Added analogous text from taxonpath.otherranks
schemas/vegbien.sql: taxonpath.author comment: Added equivalent Darwin Core term
schemas/vegbien.sql: taxon columns: Added descriptive comments for data dictionary
schemas/vegbien.sql: placepath: Added canon_placepath_id, analogous to taxonpath.canon_taxonpath_id
schemas/vegbien.sql: place, placepath descriptive comments: Added analogous text from taxon/taxonpath
schemas/vegbien.sql: taxonpath: descriptive comment: Changed "applicable taxon" to "identified taxon"
schemas/vegbien.sql: taxon: descriptive comment: Reworded to emphasize that this stores only one rank (e.g. family) of the full taxonomic name, in contrast to taxonpath, which stores all of them
schemas/vegbien.sql: taxonpath: descriptive comment: Clarified that this is the full path to a taxon, including all components of the taxonomic name
schemas/vegbien.sql: Replaced "scientific name" with "taxonomic name" for schema-wide consistency and for consistency with the taxon/taxonomic name vocabulary
schemas/vegbien.sql: taxonpath named ranks: Added descriptive comments for data dictionary
schemas/vegbien.sql: taxonpath columns other than named ranks: Added descriptive comments for data dictionary
schemas/vegbien.sql: taxonscope: descriptive comment: Reworded to make the first sentence a noun, for consistency with other descriptive table comments
schemas/vegbien.sql: taxon: descriptive comment: Added note that the taxonname stores only one rank (e.g. family) of the full identifying name
schemas/vegbien.sql: taxonpath: descriptive comment: Reworded to make the first sentence a noun, for consistency with other descriptive table comments. The convention is for the first "sentence" to be a noun which describes the entity that the table models.
schemas/vegbien.sql: comments: Removed units from comments on fields which already have a units suffix, to avoid having to keep the units in sync between the suffix and the comment. Note that the units were abbreviated equally in the suffixes and comments, so this did not result in a loss of information other than the ^ for a quantity squared (but it's obvious enough that m2 is m^2).
schemas/vegbien.sql: taxonscope: descriptive comment: Added period for consistency with other descriptive table comments
schemas/vegbien.sql: taxon: Added descriptive comment for data dictionary
schemas/vegbien.sql: VegBank-equivalent tables comments: Prepended "Equivalent to" before VegBank, so the equivalent tables statement can fit grammatically after a description of the table instead of having to be the first phrase in the descriptive table comment
schemas/vegbien.sql: taxon: VegBank-equivalent tables comment: Added plantName and applicable columns from plantStatus, which are also part of the taxon table
schemas/vegbien.sql: placepath: Added otherranks field, analogous to taxonpath.otherranks
schemas/vegbien.sql: taxonpath: Added descriptive comment for data dictionary
inputs/UNCC/Specimen/map.csv: accession: Documented that it's globally unique, although occasionally duplicated
inputs/REMIB/Specimen/map.csv: Remapped accession_number to catalogNumber because it is not globally unique, only (usually) unique within the institution providing the data ("acronym"). Note that there are nevertheless 11,869 rows where an accession_number appears multiple times within the same institution.
mappings/VegCore-VegBIEN.csv: Only use institutionCode+collectionCode+catalogNumber as the authorlocationcode (location-scoping ID) if there is actually a catalogNumber. Otherwise, the mapping process would attempt to create one location for each collection in the datasource, when there should be one location for each specimen.
schemas/py_functions.sql: _namePart(): Slice the first name from the beginning of the string to one word before the end, instead of one after the beginning, in order to avoid overlap with the last name, which starts one before the end, when there is only one word. Note that only one word means the name is assumed to be a last name. This assumption may not always be true, but when a datasource provides the name concatenated, an assumption must be made when not all name components are present.
schemas/vegbien.sql: party: Added check constraint to require at least an organizationname or surname. Previously, NULL entries for the collector or identifier incorrectly caused the creation of an empty party entry, hence the lower inserted row counts now that this is no longer created.
inputs/REMIB/Specimen/map.csv: Remapped acronym to institutionCode because this is an aggregator, and the field lists the datasource each record was aggregated from. Note that the inserted row count changes because of different duplicate elimination strategies in specimenreplicate and party (which institutionCode is placed in).
inputs/REMIB/Specimen/create.sql: Also filter out rows where acronym (collectionCode) is NULL because this is a required field for valid records
schemas/vegbien.sql: taxonpath: Renamed scientificnameauthor to author so the column name doesn't have "scientificname" in it, which made the term look confusingly like scientificname itself. Added descriptive comment that this is the author of the scientific name.
schemas/vegbien.sql: taxonpath: Renamed canon_id to canon_taxonpath_id to clarify that this is a recursive fkey. The convention is that a recursive fkey includes the table name plus a descriptive prefix.
schemas/filter_ERD.csv: Don't filter out fkeys from taxonpath to itself
schemas/vegbien.sql: taxonpath: Added canon_id for the canonical (scrubbed) taxonpath determined by TNRS
schemas/vegbien.sql: taxonpath: taxonpath_unique_within_datasource_by_name unique index: Added otherranks, so that ranks without a named column will be used in uniquely identifying the taxonpath
sql.py: DbConn.col_info(): Parse array types as sql_gen.ArrayType
sql_gen.py: EnsureNotNull: Support ArrayType types
strings.py: remove_prefix(), remove_suffix(): Added require param to raise aan exception if the string does not have the given prefix/suffix
sql.py: DbConn.col_info(): Moved parsing of user-defined datatypes to Python code, so that parsing for other composite types which also requires both data_type and udt_name can easily be added
sql_gen.py: Added ArrayType
schemas/vegbien.sql: Scope taxonpath instead of taxon with taxonscope, because a morphospecies name is specific to a datasource entity, so it should go in the datasource-specific taxonpath table instead of the datasource-general taxon table
schemas/vegbien.sql: taxonpath: Added otherranks array column to store ranked names without a named column. Documented that ranks with no named column should be stored in this new field instead of in a chain of taxons pointed to by taxon_id. This ensures that only the tree of life uses the taxon table.
schemas/vegbien.sql: Removed no longer used table stemtag, which has been replaced by stemobservation.tag, stemobservation.tags
inputs/ARIZ/Specimen/test.xml.ref: Updated after reinstalling staging table with new sql_io.null_strs
inputs/VegBank/: Added stemlocation/
inputs/VegBank/: Added stemcount/
sql_io.py: cleanup_table(): Fixed bug where couldn't run any update statement when no columns are text
csv2db: COPY FROM mode: Removed no longer needed explicit column list, now that the initial table has the exact width of the CSV (the row_num is added later)
csv2db: Add any row_num column after creating the table, so it does not interfere with row widths when using COPY FROM without explicit column names
csv2db: Fixed bug where tables without a row_num (such as *.src tables) were not properly supported when the CSV contained ragged rows, because the columns were truncated to # column names + 1 but there was no row_num to be the +1. This was solved by moving row_num to the end, so that it does not impact the column count whether it's there or not.
inputs/VegBank/: Added taxonimportance/
mappings/VegCore.csv: Added and mapped aggregateOccurrenceID
mappings/VegCore.csv: taxonOccurrenceID: Re-sourced to VegBank taxonobservation and DwC occurrenceID, because this is where the VegBIEN table name came from
tnrs_client: Support parsing multiple taxons at once, by specifying each as a command-line argument. Increased the max_pause to 10 min to support large batches. Limited the batch size to 5000 names, using the limit at <http://tnrs.iplantcollaborative.org/TNRSapp.html>. Note that when using xargs to pass many names, xargs will by default split its arguments into chunks of 5000. You can change this using the -n option.
Added tnrs_client. Note that obtaining an actual CSV requires four (!) steps: submit, retrieve, prepare download, and download. The output of the retrieve step is unusable because the array has different lengths depending on the taxonomic ranks present in the provided taxon name. This initial version runs one name at a time, but could later be expanded to batch process because TNRS can run multiple names at once.
streams.py: Line iteration: Added read_all()
inputs/Madidi/Plot/map.csv: Soil component measurements: Documented that units are assumed to be % based on the range of values
