sql_io.py: put_table(): ignore_cond(): Changed "Ignoring rows where" message with the negated (filter-out) condition to "Ignoring rows that don't satisfy" with the filter condition for clarity
sql_io.py: put_table(): ignore_cond(): If cond simplifies to false, remove all rows instead of filtering out individual rows which will all be filtered out. This optimization should improve import times of tables, such as taxonconcept, which use a check constraint instead of NOT NULL constraints to prevent empty rows. The taxonomic schema refactoring caused the creation of many more levels of taxonconcepts, many of which (such as variety, forma, cultivar) are empty for most datasources, so this optimization should also reduce overall import times for datasources that have any empty levels of taxonconcept. Note that this optimization is only possible now that sql_gen.simplify_expr() is able to simplify all the way to a single boolean value for the taxonconcept_required_key constraint.
Moved expression transforming functions from sql.py to sql_gen.py because they do not manipulate an actual database and merely generate SQL
sql.py: Added true_expr, false_expr and use them where their values are used
sql.py: simplify_expr(): Also simplify "AND true" expressions
sql.py: simplify_expr(): Also simplify "AND false" expressions
sql.py: Added atom_re and use it in simplify_parens()
sql.py: Added or_re and use it in simplify_expr()
sql.py: logic_op_re(): Added expr_re param for an expr on the other side of the operator
sql.py: simplify_parens(): Use bool_re
sql.py: Removed no longer needed paren_re()
sql.py: true_re, false_re: Removed no longer needed paren_re() because simplify_parens() now handles this
sql.py: simplify_expr(): Removed final simplify_parens() because this is now done by simplify_recursive()
sql.py: simplify_expr(): Use new simplify_recursive(). This also fixes a bug where some logic expressions are not simplified because of extra parens.
sql.py: Added simplify_recursive()
sql.py: simplify_parens(): Also remove parens around true and false
regexp.py: sub_nested(): Use new sub_recursive()
regexp.py: Added sub_recursive()
sql.py: simplify_expr(): Use new simplify_parens()
sql.py: Added simplify_parens()
sql.py: simplify_expr(): Use new regexp.sub_nested()
Added regexp.py
sql.py: simplify_expr(): Use new logic_op_re()
sql.py: Added logic_op_re()
sql.py: bool_re: Use new true_re, false_re
sql.py: Added true_re, false_re
sql.py: bool_re: Use new paren_re()
sql.py: Added paren_re()
sql.py: simplify_expr(): Combined replacements of bool_re+' OR ' with the value in either order into one replacement
mappings/VegCore-VegBIEN.csv: verbatim* taxonconcept: Don't store Name_submitted in taxonomicnamewithauthor in addition to identifyingtaxonomicname, because the fields other than identifyingtaxonomicname are meant to store parsed values rather than raw, unscrubbed values and TNRS does not directly provide a concatenated taxonomic name with author
mappings/VegCore-VegBIEN.csv: verbatim* taxonconcept: Don't create hierarchy of parent taxonconcepts, because the parsed names (rather than the names for the matched taxonconcept) are from the input taxonomic name, rather than from the official tree of life used by TNRS. Otherwise, if a taxonomic name provides e.g. no family (common), a separate genus taxonconcept would have been created with no parent_id, which would not compare equal to the matched taxonconcept's genus with a parent_id. Continue to store the parsed family, genus, species in the family, genus, species cached fields, because the parsed family is often different from the matched taxonconcept's family when e.g. no family is provided in the taxonomic name.
sql.py: Renamed table_cols() to table_col_names() for clarity, because it does not return sql_gen.Col objects
inputs/.TNRS/tnrs/test.xml.ref: Accepted new inserted row count. The change is most likely from several revisions back, but the cause of the change is unknown (it is not due to the updated TNRS.tnrs table, which is still sorted with the same rows first).
sql_gen.py: is_text_col(): Use new is_text_type()
sql_gen.py: Added is_text_type()
sql_gen.py: ensure_not_null(): Documented that NULL has no type, hence the NoUnderlyingTableException being re-raised
sql_gen.py: ensure_not_null(): Just store the column type in col_type, instead of storing typed_col and using typed_col.type, now that other info in typed_col is no longer needed
sql_gen.py: ensure_not_null(): Use is_nullable() instead of determining nullability itself, for clarity
sql_gen.py: is_nullable(): Fixed bug where non-columns could not be sent to db.col_info()
sql_gen.py: ensure_not_null(): Always remove_col_rename() the column to ensure that it is acceptable by helper functions like is_nullable()
lib/PostgreSQL-MySQL.csv: COMMENT statement: Fixed bug where ending ; could match only when preceded by ' and followed by a newline, to avoid matching ; embedded in the comment
schemas/vegbien.sql: taxonconcept: family, genus, species comments: Changed "scoping" to "identifying" for clarity
schemas/vegbien.sql: taxonconcept: family, genus, species: Added comment that each is a cached field for easy querying and the scoping version of it is stored in the chain of parent_id ancestors
schemas/vegbien.sql: taxonconcept: taxonconcept_unique: Removed family, genus, species because these are now just cached fields for analytical_db_view rather than scoping fields. The scoping versions of these fields are stored in the chain of parent_id ancestors.
tnrs_db: Moved "Processing # taxonconcepts" log message to before waiting or exiting if no taxonconcepts left, so that it would be printed right after the query is run and say that no taxonconcepts were found
tnrs_db: Updated comments and log messages for schema changes
tnrs_db: Updated query for schema changes
README.TXT: Schema changes: files to update with renamings: Added bin/tnrs_db
inputs/import.stats.xls: Updated import times
README.TXT: Data import: Changed `inputs/*/*/logs` to `inputs/{.,}*/*/logs` to also include the TNRS names import log
import_all: Added commands to import TNRS names so the user doesn't have to do this manually
sql.py: map_expr(): Fixed bug where names were being matched inside punctuated names replaced in previous calls of map_expr()
schemas/vegbien.sql: party: party_required_key: Only allow NULL organizationname if party is not a root party (i.e. creator_id != party_id)
mappings/VegCore-VegBIEN.csv: Mapped to new taxonconcept.creationdate
schemas/vegbien.sql: taxonconcept: taxonconcept_required_key: Added creationdate as an allowable minimum field when parent_id (containing the associated hierarchical concept) is specified
schemas/vegbien.sql: taxonconcept: taxonconcept_required_key: Removed family and genus because these are now cached fields only, and are not used for scoping a taxonconcept. Instead, *taxonomicname and taxonname+parent_id are used for this purpose. This removes several leaf taxonconcepts with insufficient scoping information to create a taxonconcept separate from the main tree. With the upcoming population of creationdate, some of these taxonconcepts will reappear due to the date's additional distinguishing information.
schemas/vegbien.sql: taxonconcept: Added creationdate (the date the taxonconcept was created or defined), and include it in the taxonconcept_unique unique index
schemas/vegbien.sql: taxonconcept: Added comment with the definition of a taxon: "a group of one (or more) populations of organism(s), which a taxonomist adjudges to be a unit" (http://en.wikipedia.org/wiki/Taxon). This is useful in clarifying that our taxon concepts are intended to serve a similar purpose, by storing one person's defined taxon.
schemas/vegbien.sql: taxonconcept: taxonconcept_required_key: Removed family and genus because these are now cached fields only, and are not used for scoping a taxonconcept. Instead, *taxonomicname and taxonname+parent_id are used for this purpose.
schemas/vegbien.sql: taxonconcept: Moved identifyingtaxonomicname near other full-taxonomic-name-related fields, after the fields that contain just the current level's component of the full name
schemas/vegbien.sql: taxonconcept.canon_taxonconcept_id: Changed four-level hierarchy to use "parsed concept" and "matched concept" instead of concatenated and parsed, because the directly-parsed name components actually go in level 2 of the hierarchy (the TNRS input name), while the name components based on the matched taxon concept go in level 3
schemas/vegbien.sql: taxonconcept.parent_id: Documented that while a taxon name may have multiple parents, a taxon concept has only one, based on the creator's opinion of where that taxonconcept goes in the taxonomic hierarchy
mappings/VegCore-VegBIEN.csv: taxonconcept: Moved infraspecific taxonconcept to its own level, rather than combining it with the level that contains the full taxonomic name and author (as well as any morphospecies), for consistency with the storage of other ranked taxonomic name components, which each get their own taxonconcept. The infraspecific taxon concept is general to all parties making idenfitications (within a datasource), while the concatenated name and author and any morphospecies are specific to the person who defined the taxonconcept used by a taxondetermination.
schemas/vegbien.sql: taxonconcept: Removed no longer used higher- and infraspecific taxonomic rank fields because these terms are now stored in their own taxonconcepts. family, genus, and species have not been removed because these are used to cache names of parent taxa for fast access by analytical_db_view.
schemas/vegbien.sql: analytical_db_view: Changed taxonMorphospecies to use taxonconcept.taxonname, where any morphospecies is now stored
mappings/VegCore-VegBIEN.csv: infraspecific taxonomic terms: Removed mappings to first-class taxonconcept fields because these terms are now stored in their own taxonconcepts, or in the lowest-level taxonconcept as the taxonname and rank
mappings/VegCore-VegBIEN.csv: higher-level taxonomic terms: Removed mappings to first-class taxonconcept fields because these terms are now stored in their own taxonconcepts
schemas/vegbien.sql: taxonconcept: Merged taxonconcept_unique_within_creator_by_name unique index into taxonconcept_unique_within_parent, placed parent_id first, and removed index condition, so that this index can be used as a lookup index by taxonconcept_update_ancestors() (which requires no index condition in order to apply to all taxonconcepts) in addition to as a unique index. Note that an index condition should not be necessary for the index's uniquifying task, because if a set of taxonconcepts provides only the identifyingtaxonomicname, that should collide in the taxonconcept_unique_within_creator_by_identifying_name unique index before this index collides. This assumes that the collision order when multiple indexes collide is alphabetical by the index name.
schemas/vegbien.sql: taxonconcept: taxonconcept_required_key check constraint: Also allow a taxonconcept to have just an author when it has a parent_id, so that an author can uniquely identify a taxon within a more general taxon, such as a species name, that has no author
strings.py: concat(): Fixed bug where end index of returned str0 portion would wrap around to a negative number if str1 itself was too long, causing incorrect truncation
schemas/vegbien.sql: taxonconcept: Renamed taxonconcept_unique_within_parent to taxonconcept_unique because the index does not apply only to taxonconcepts with a parent, and because it's the primary unique index for taxonconcept
schemas/vegbien.sql: taxonconcept: Renamed taxonconcept_unique_within_creator_by_identifying_name to taxonconcept_0_unique_identifying_name to ensure that it is always applied before taxonconcept_unique_within_parent if both collide
mappings/VegCore-VegBIEN.csv: taxonconcepts: Also create the taxonconcept tree for taxonconcepts created from original*, verbatim*, and accepted* taxonomic terms
mappings/VegCore-VegBIEN.csv: taxonconcepts: Also create the taxonconcept tree if datasource provided separated components of the taxonomic name and/or its own tree of life with higher classifications. This enables storing the datasource's own tree of life to supplement any official tree (TROPICOS, USDA, etc.).
mappings/VegCore-VegBIEN.csv: taxonconcept tree: Don't map infraspecificEpithet+taxonRank to a taxonconcept in the tree of parent concepts because it has already been mapped to the primary, lowest-level taxonconcept
schemas/vegbien.sql: taxonconcept: taxonconcept_unique_within_creator_by_name unique index: Fixed bug where index filter overlapped with taxonconcept_unique_within_parent's index filter, causing these unique indexes to sometimes both apply at the same time and prevent column-based import from correctly choosing which index to use for each taxonconcept import
schemas/vegbien.ERD.mwb: Fixed lines
schemas/vegbien.sql: taxonconcept.canon_taxonconcept_id comment: Changed comment to use "concept" rather than "name" where applicable. Documented that a synonym between taxonconcepts of different sources is indicated by choosing one taxonconcept to be authoritative and pointing the other taxonconcept to it using this field.
sql_io.py: put_table(): Resolving default value column: Fixed bug where the default value col needed to have its table changed from in_table to full_in_table if it's a table column, and needed to have any column rename removed if it's a literal value
Regenerated vegbien.ERD exports
schemas/vegbien.sql: Renamed plant* taxonomic tables -> taxon*, as part of the taxonomic schema refactoring at <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/2012-10-03_conference_call#Taxonomic-schema-refactoring>
schemas/vegbien.ERD.mwb: Rearranged to fit more of location table on the diagram, using the newly available space from taxon
schemas/tree_cross-links.sql: Synced with schema, updating with new table names
schemas/vegbien.sql: Removed no longer used taxon table. Use taxonconcept instead.
schemas/vegbien.sql: taxonconcept.taxonname: comment: Stated that this is the name of the taxon within its parent taxon
schemas/vegbien.sql: taxonconcept: comment: Removed no longer accurate comment that an accepted taxonconcept points to the identified taxon in the tree of life, because it is the identified taxon in the tree of life
schemas/filter_ERD.csv: Changed the table with the visible fkey from plant* to taxon* to be plantstatus rather than plantusage, since it contains more core fields
schemas/vegbien.sql: taxonconcept: Removed taxon_id, since taxonconcept now contains all the information needed to represent a taxonomic hierarchy, including both conceptual and nomenclature information
schemas/vegbien.sql: plantusage: Point just to taxonconcept instead of both to taxonconcept and taxon
schemas/vegbien.sql: taxonconcept: rank, verbatimrank comments: Added info from corresponding fields in taxon that also applies to taxonconcept
schemas/vegbien.sql: taxonconcept: comment: Added info from taxon that also applies to taxonconcept
schemas/vegbien.sql: Added taxonconcept_ancestor cross-link table
schemas/vegbien.sql: taxonconcept: Added description field
mappings/VegCore-VegBIEN.csv: Remapped taxon hierarchy for accepted taxonconcepts to taxonconcept parent_id hierarchy
schemas/vegbien.sql: Fixed bug where taxonconcept.parent_id was missing a foreign key constraint
schemas/vegbien.sql: taxonconcept: Changed instructions for including a taxon name at a rank with no explicit column to create a parent taxonconcept for it and point to it using parent_id instead of using otherranks. Removed no longer used otherranks field.
