schemas/vegbien.sql: unscrubbed_taxondetermination_view: Do the tnrs_canon joins manually instead of using tnrs_canon, to allow PostgreSQL to use a nested loop join on just the needed tnrs rows instead of a hash self-join of all tnrs rows. The query planner is not yet advanced enough to automatically integrate the select on the view into the top-level joins list, which would make this change automatically.
inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: rowsAdded(): Look at last 100 rows instead of last 10, because rows are added to the log file each time the script waits and the Inserted # new rows message must be in the tailed rows
inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: rowsAdded(): Fixed bug where need to test if log file exists before using it in tail, because if tail fails and causes rowsAdded to return false, this error exit status will be indistinguishable from false for no rows added and the script will keep going
inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Fixed bug where need special handling to support being run as a .make script
input.Makefile: Editing import: Added unscrub to remove TNRS taxondeterminations
psql_script_vegbien: Added no_query_results option to hide results of calls to void functions
schemas/vegbien.sql: Added delete_scrubbed_taxondeterminations()
root Makefile: python-Darwin: Added instructions to install dateutil for Python 3 as well as Python 2, for use in PL/Python functions
root Makefile: python-Darwin: Added note that Python 2 comes preinstalled
Added inputs/GBIF/Specimen/postprocess.sql to remove institutions that we have direct data for
import_all: Run disown_all after background processes have been created, so that they will not be aborted if the shell exits (e.g. due to a broken connection). Note that with_all processes are automatically disowned as they are created, but other processes, such as after_import, were not.
inputs/.TNRS/schema.sql: Removed no longer used array_to_string(). The IMMUTABLE wrapper is only needed for index conditions and other places that require an IMMUTABLE function.
input.Makefile: Maps validation: %/new_terms.csv: Filter out terms that map to UNUSED, because these are not mappings that are useful as VegCore synonyms
README.TXT: Data import: Checking free disk space: Updated import schema size to 110GB
Added inputs/Madidi/_README.TXT
new_terms.csv: Regenerated
inputs/Madidi/new_terms.csv: Regenerated
inputs/Madidi/_archive/2010-1-2/: Set svn:ignore
inputs/Madidi/_README.TXT: Archived to _archive/2010-1-2/
inputs/Madidi/: Refreshed. Note that new export has a completely new schema.
mappings/VegCore-VegBIEN.csv: fieldNumber (authorEventCode): Fixed bug where locationevent.authorlocationcode should be authoreventcode
Added inputs/Madidi/map.csv, created from new_terms.csv
inputs/Madidi/_archive/: Set svn:ignore
csvs.py: sniff(): TSVs: Don't turn off quoting, because some TSVs (such as Madidi.IndividualObservation) do quote fields
csvs.py: TsvReader: Use csv.reader.next() when possible to support quoted fields, such as in Madidi.IndividualObservation
input.Makefile: Configuration: $(exts): Added .dat, which the new Madidi files use
mappings/Makefile: VegCore.tables.csv: Removed no longer needed removal of Namespaces table, which is now marked as just a section, not a table
mappings/VegCore.csv: Regenerated from wiki
Added to_do/timeline.2013.xls (from Brad, converted to .xls)
to_do/timeline.doc: Renamed to timeline.2012.doc to allow for a separate 2013 timeline
README.TXT: Data import: Deleting imports before the last: Added instructions to keep a previous import instead of deleting it
input.Makefile: Staging tables installation: $(logInstall): Always log the installation, regardless of the $log env var, because $log is set by default on development machines but an install log should still be created
schemas/vegbien.ERD.mwb: Regenerated exports
schemas/vegbien.sql: unscrubbed_taxondetermination_view: Fixed bug where need to handle the case where (SELECT source.source_id FROM source WHERE source.shortname = 'TNRS') is NULL because no TNRS names have been imported yet
*/new_terms.csv, */unmapped_terms.csv: Regenerated using `make missing_mappings`
mappings/VegCore-VegBIEN.csv: morphoname: Remapped to the original rather than current taxondetermination because this is the original name applied by the author
inputs/SALVIAS*/Organism/map.csv: Remapped voucher_string/coll_number to recordNumber instead of catalogNumber, because this number is actually applied by the collector rather than by a herbarium
mappings/VegCore-VegBIEN.csv: Mapped recordNumber to new specimenreplicate.collectionnumber
mappings/VegCore-VegBIEN.csv: Also map recordNumber (collectionnumber) to the indirect voucher's specimenreplicate
inputs/*/*/map.csv: Remapped recordNumber to new individualCode where applicable
mappings/VegCore-VegBIEN.csv: Mapped individualCode. authortaxoncode: Prefer tag over recordNumber (collectionnumber), because this applies to the plant rather than the specimen.
mappings/VegCore-VegBIEN.csv: Mapped morphoname
schemas/vegbien.sql: taxonverbatim: Added morphoname (which is different from the morphospecies suffix)
schemas/vegbien.sql: plantobservation: Renamed collectionnumber to authorplantcode since this number, which identifies the plant, is actually different from the collectionnumber that identifies the specimen collected from it. This distinction is meaningful for plots data, but generally not for specimens data.
schemas/vegbien.sql: specimenreplicate: Added collectionnumber
schemas/vegbien.sql: taxonlabel: Removed no longer used matched_label_fit_fraction. Use taxondetermination.taxonfit instead.
inputs/*/*/test.xml.ref: Restored inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
schemas/vegbien.ERD.mwb: Expanded analytical_stem to fit the width of all fields
schemas/vegbien.sql: taxondetermination: taxondetermination_computer_min_fit CHECK constraint: Fixed bug where need to use CASE instead of OR when a branch of an OR shouldn't be evaluated, because PostgreSQL doesn't support short-circuit OR
README.TXT: Debugging: Added instructions for "binary chop" debugging, which requires syncing the DB schema to the svn working copy
mappings/VegCore-VegBIEN.csv: Removed no longer used mappings for verbatimScientificName in _if conditions
inputs/.NCBI/nodes/test.xml.ref: Restored inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
sql_io.py: put_table(): DuplicateKeyException: Uniquifying input table to avoid internal duplicate keys: Also filter out duplicate rows in the out_table, so that they don't create duplicate key errors and the resulting index holes
sql.py: distinct_table(): Added support for custom joins used in creating the new table. This can then be used by sql_io.put_table() to filter out duplicate rows in the out_table, so that they don't create duplicate key errors and the resulting index holes.
README.TXT: Documentation: Redmine-formatted list of steps for column-based import: Added step to reinstall public schema first, to reset the sequences so that they don't create a diff when the new steps.by_col.log.sql is committed
Added inputs/ACAD/Specimen/logs/steps.by_col.log.sql
sql_gen.py: Join: Added support for mapping values which are lists, for use in USING joins
inputs/SALVIAS/*/test.xml.ref: Restored SALVIAS* inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
schemas/vegbien.sql: analytical_stem: Added locationName (authorPlotCode), subplot, individualCode (authorPlantCode) for use in validation
schemas/vegbien.sql: sync_analytical_stem_to_view(): Drop and re-create dependent objects to avoid errors that analytical_stem can't be dropped because of dependents
schemas/vegbien.sql: sync_analytical_stem_to_view(): Changed to PL/pgSQL function to allow adding PL/pgSQL commands
schemas/vegbien.ERD.mwb: Moved family_higher_plant_group to leave room for analytical_stem to expand
mappings/VegCore-VegBIEN.csv: Removed taxonlabel for original taxondetermination, because the original taxondetermination is not scrubbed by scrub.make (only the most current taxondetermination gets scrubbed, because only a single scrubbed determination is added by scrub.make). This still leaves the original taxondetermination's taxonverbatim, which stores the taxonomic information for historical purposes.
mappings/VegCore-VegBIEN.csv: Removed no longer used accepted and verbatim (parsed) taxonlabels, which have been replaced by a single accepted or matched taxondetermination created by scrub.make
Removed no longer used inputs/.TNRS/tnrs_accepted, tnrs_other. Use the tnrs_canon view instead.
Added inputs/.TNRS/_archive/
Added inputs/.TNRS/tnrs/cleanup.sql to prevent running the default cleanup operations, which don't work on tables which have views referencing them (as is the case for tnrs, which is referenced by tnrs_canon)
import_all: Removed no longer needed TNRS import, which has been replaced by scrub.make (which adds TNRS taxondeterminations after the import instead of creating taxonlabel links before it)
mappings/VegCore-VegBIEN.csv: Removed TNRS input taxonlabels meant to cross-link to taxonlabels added by the TNRS import, because TNRS taxondeterminations are now created instead
schemas/vegbien.sql: analytical_stem_view: Use just the main taxonlabel created by scrub.make instead of all the additional taxonlabels created by the TNRS import
mappings/VegCore-VegBIEN.csv: main taxonverbatim.morphospecies "if has verbatim name" condition: Fixed bug where need to remove the taxonIsCanonical flag, because the TNRS.public.unscrubbed_taxondetermination_view table (which uses this flag) should include this field (although not other places where the morphospecies is stored by other TNRS tables)
schemas/vegbien.sql: taxondetermination: taxondetermination_set_iscurrent() trigger: Also run on delete, to mark another taxondetermination as the current one when a current taxondetermination is deleted
inputs/.TNRS/schema.sql: tnrs_canon: Annotations: Always use value from the matched name, because the accepted name does not have this
mappings/VegCore-VegBIEN.csv: primary taxonlabel's parent taxonlabel: Fixed bug where a taxonverbatim was incorrectly being created solely to store the taxonRank, even though it was already stored in the taxonlabel's rank field
mappings/VegCore-VegBIEN.csv: Don't map morphospecies to the parsed taxonlabel's taxonepithet, because this causes an extra, parsed taxonlabel to be created for TNRS.public.unscrubbed_taxondetermination_view. It is not needed by the other TNRS tables.
inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv: Omit Infraspecific_rank to help avoid creating a separate, parsed taxonlabel. Don't map to taxonRank because Name_matched_rank is populated more often.
inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Reduced $maxPause to 4 hr, because new taxondeterminations are being added throughout the import, so it is unlikely that more than more than 4 hr would pass between successive imports of taxondeterminations (causing scrub.make to stop prematurely)
inputs/.TNRS/schema.sql: Removed no longer used tnrs+accepted. Use tnrs_canon or a self-join of tnrs instead
schemas/vegbien.sql: tnrs_input_name: Use TNRS.tnrs directly instead of the now-deprecated tnrs+accepted
schemas/vegbien.sql: Use new TNRS.tnrs_canon instead of tnrs+accepted to avoid creating additional taxonlabels for the parsed, matched, and accepted names and instead just use the most-canonicalized name of the names output by TNRS (the accepted name if available, or the matched name otherwise)
mappings/VegCore-VegBIEN.csv: "if has verbatim name" _if statements that filter something out for TNRS mappings: Also assume true if taxonIsCanonical is specified, because some TNRS tables (eventually such as public.unscrubbed_taxondetermination_view) do not specify a separate "verbatim" taxondetermination but do provide taxonIsCanonical as a flag to turn various mappings on and off
mappings/VegCore-VegBIEN.csv: Remapped matched*Fit_fraction to taxondetermination.taxonfit when a taxondetermination, not just a taxonlabel, is provided
bin/map: map_table(): Resolving prefixes: Fixed bug where need to use list instead of tuple for metadata value mappings
schemas/vegbien.sql: taxondetermination: Added CHECK constraint to allow only taxondeterminations with a minimum fit fraction of 80%, analogous to taxonlabel's taxonlabel_1_matched_label_min_fit() trigger
mappings/VegCore-VegBIEN.csv: Don't create a separate TNRS input taxonlabel if taxonIsCanonical exists
inputs/.TNRS/schema.sql: tnrs_canon: Fixed bug where need to always use Unmatched_terms from tnrs rather than tnrs_accepted
inputs/.TNRS/schema.sql: Added tnrs_canon, which stores the most canonicalized name output by TNRS
schemas/vegbien.sql: analytical_stem_view: accepted_taxonverbatim: Fixed bug where need to join only to the taxonverbatim whose morphospecies is NULL, to avoid joining to multiple taxonverbatims at once. This extra filter is now needed because there can be multiple taxonverbatims for a taxonlabel with different morphospecies.
mappings/VegCore-VegBIEN.csv: taxonlabel.taxonomicname: Prepend the family to the rest of the name using new _merge_prefix() instead of _join_words()/_nullIf(), so that any input taxonomic name that includes the family will not have the family duplicated in the combined taxonomic name. Previously, the duplication was removed only when the rest of the input name was equal to the family. This change fixes a bug in the new TNRS import where a pre-concatenated taxonomic name (Accepted_scientific_name) which includes the family is now used instead of Accepted_name, which only includes it when it's equal to the family.
xml_func.py: Simplifying functions: Merging: Added _merge_prefix() passthru
schemas/functions.sql: Added _merge_prefix()
inputs/.TNRS/schema.sql: tnrs_populate_accepted_scientific_name(): Fixed bug where Accepted_name_family shouldn't be prefixed to Accepted_name if Accepted_name is itself the family, to avoid duplicating the family in the Accepted_scientific_name
