Project

General

Profile

Statistics
| Revision:

# Date Author Comment
5690 10/19/2012 06:20 PM Aaron Marcuse-Kubitza

sql.py: parse_exception(): function MissingCastException: Support functions with named parameters

5689 10/19/2012 06:18 PM Aaron Marcuse-Kubitza

sql.py: parse_exception(): function MissingCastException: Support function names enclosed in quotes on the context line

5688 10/19/2012 06:15 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: taxonName: Place it in taxonomicname instead of taxonepithet for lower taxa, because the only datasource that currently provides this field (NCBI) actually provides the full taxonomicname instead of the epithet at the current rank for lower taxa. (taxonomicname is not applicable to higher taxa because their names are not guaranteed to be globally unique.) taxonName may need to be renamed and/or redefined to account for this ambiguity in NCBI's usage.

5687 10/19/2012 06:14 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Do not include the taxonName in the concatenated taxonomicname because it is NOT globally unique. The same name may be used at different taxonomic ranks and mean different things, and lower taxa may have the name appear in multiple genuses or species, meaning different things.

5686 10/19/2012 06:04 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Do not include the taxonName in the concatenated taxonomicname because it is NOT globally unique. The same name may be used at different taxonomic ranks and mean different things, and lower taxa may have the name appear in multiple genuses or species, meaning different things.

5685 10/19/2012 05:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added _is_higher_taxon()

5684 10/19/2012 05:52 PM Aaron Marcuse-Kubitza

README.TXT: Documentation: To import and scrub just the test taxonomic names: Added `make inputs/.TNRS/cleanup` after `make backups/TNRS.backup/restore` because the PostgreSQL collation may differ between vegbiendev's and the user's DB

5683 10/19/2012 05:50 PM Aaron Marcuse-Kubitza

sql.py: parse_exception(): DoesNotExistException: If item not found was a function and not found only because of a missing cast, raise MissingCastException instead. This should allow automatic casts to be added on function parameters as well as table columns.

5682 10/19/2012 05:28 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_db_view: Fixed bug where needed to join to taxonverbatim on taxonverbatim_id (the pkey) instead of taxonlabel_id, which used to be the pkey but is now an fkey

5681 10/19/2012 05:22 PM Aaron Marcuse-Kubitza

inputs/test_taxonomic_names/test_scrub: Remove any previous version of public.test_taxonomic_names before renaming public to it

5680 10/19/2012 05:19 PM Aaron Marcuse-Kubitza

inputs/test_taxonomic_names/test_scrub: Fixed bug where public.sql export did not include the "CREATE SCHEMA public" statement, because pg_dump doesn't add it to backups, by using new schemas/rename/% make target to first rename the public schema and then exporting it

5679 10/19/2012 05:12 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: Schemas: schemas/rotate: Use new schemas/rename/%

5678 10/19/2012 05:12 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: Schemas: Added schemas/rename/% to rename the public schema

5677 10/19/2012 04:54 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Removed filter preventing taxonomicStatus from being placed in taxonlabel if a morphospecies was provided, because the morphospecies actually never goes in the matched taxonlabel, only the verbatim taxonlabel

5676 10/19/2012 04:50 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: morphospecies: Also place it in the verbatim (input name's) taxonlabel. Note that it does not go in the matched name's taxonlabel, because that contains only fields from the matched name. The verbatim taxonlabel is thus a synonym of the matched taxonlabel where there is no morphospecies, or a child of it if there is a morphospecies.

5675 10/19/2012 04:36 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Do not place taxonomicStatus in taxonlabel if a morphospecies was provided, to prevent it from being incorrectly marked as accepted

5674 10/19/2012 04:25 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: morphospecies -> taxonverbatim.morphospecies: Fixed bug where needed suffix with _if statement then clause

5673 10/19/2012 04:23 PM Aaron Marcuse-Kubitza

inputs/test_taxonomic_names/_scrub/public.sql, TNRS.sql: Regenerated with schema changes

5672 10/19/2012 03:45 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: Added opts env var to allow specifying options to a Makefile command, which does not take positional arguments

5671 10/19/2012 03:37 PM Aaron Marcuse-Kubitza

README.TXT: Schema changes: files to update with any renamings: Removed tnrs_db because that is now abstracted from the schema through the tnrs_input_name view. Note that PostgreSQL will automatically update tnrs_input_name with any table or column renames, which is the significant advantage of using a view rather than a hardcoded query.

5670 10/19/2012 03:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: tnrs_input_name: Use DISTINCT instead of DISTINCT ON because there is only one column

5669 10/19/2012 03:34 PM Aaron Marcuse-Kubitza

tnrs_db: Use new tnrs_input_name view to avoid hardcoding changing schema information

5668 10/19/2012 03:25 PM Aaron Marcuse-Kubitza

inputs/test_taxonomic_names/test_scrub, README.TXT: Documented that `make schemas/public/reinstall` must come after TNRS restore to recreate the tnrs_input_name view, which has a dependency on the TNRS schema

5667 10/19/2012 03:23 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added tnrs_input_name view for use by tnrs_db

5666 10/19/2012 12:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel, taxonverbatim: Updated comments for new taxonlabel/taxonverbatim split

5665 10/19/2012 12:42 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel_update_ancestors(): Use aliased types (http://www.postgresql.org/docs/8.3/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE) where possible

5664 10/19/2012 12:37 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel_update_ancestors(): Adding new parent's ancestors: Change unique_violations to warnings so they don't abort the import. unique_violations should never happen unless there are cycles of two or mode nodes, but they seem to be happening nevertheless, so this will provide a workaround to that problem.

5663 10/19/2012 12:18 PM Aaron Marcuse-Kubitza

inputs/import.stats.xls: Updated import times

5662 10/18/2012 04:58 PM Aaron Marcuse-Kubitza

Regenerated vegbien.ERD exports

5661 10/18/2012 04:55 PM Aaron Marcuse-Kubitza

tnrs_db: Updated with schema changes

5660 10/18/2012 04:54 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonverbatim: Removed subclass relationship to taxonlabel in order to allow multiple taxonverbatims to point to the same taxonlabel. This involves adding a taxonverbatim_id serial column and pointing all fkeys to taxonverbatim to that column.

5659 10/18/2012 04:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_db_view: Fixed bug where needed to join on taxonverbatim before joining on taxonlabel, now that taxondetermination is linked directly to taxonverbatim. Interestingly, PostgreSQL did not flag this error when the schema was changed, but only when the schema was reloaded from the DDL.

5658 10/18/2012 04:30 PM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: Moved taxonlabel to the right of taxonverbatim to make room for taxonverbatim to expand

5657 10/18/2012 04:21 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Link taxondetermination to taxonverbatim (which is a subclass of taxonlabel) instead of directly to taxonlabel. This will enable later having multiple taxonverbatims for one taxonlabel.

5656 10/18/2012 04:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Renamed identifyingtaxonomicname to taxonomicname because the taxonomicname provided by the datasource is now in taxonverbatim, so there is no name collision. Note that both of these fields store the same type of information, but taxonlabel's is autogenerated while taxonverbatim's is verbatim (and is only set if provided by the datasource).

5655 10/18/2012 03:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Moved non-scoping fields to new taxonverbatim subclass table, which contains the component parts of the taxonlabel

5654 10/18/2012 03:06 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Renamed taxonlabel_2_propagate_canon_label_id() to taxonlabel_2_set_canon_label_id() for clarity

5653 10/18/2012 03:04 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel_2_propagate_canon_label_id(): If no matched taxonlabel, make self-reference. This fixes a bug in analytical_db_view where rows without a canon_label_id were excluded because they did not have a corresponding canonical taxonlabel.

5652 10/18/2012 02:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel_unique unique index: Removed binomial, author, taxonomicname, and morphospecies because these are now part of the identifyingtaxonomicname, which is also in the unique index

5651 10/18/2012 02:44 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Require either an identifyingtaxonomicname or a taxonepithet. The NCBI inserted row count decreases by one because this prunes off a taxonlabel created for a parent node which was not contained in the first two rows (remember that NCBI taxa are not in dependency order, so parents are often imported after children).

5650 10/18/2012 02:41 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Also generate the identifyingtaxonomicname for the original* taxondetermination's taxonlabel

5649 10/18/2012 02:31 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Renamed taxonomicnamewithauthor to taxonomicname because it is equivalent to Darwin Core's scientificName

5648 10/18/2012 02:25 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Also include morphospecies in the identifyingtaxonomicname, except for the matched TNRS taxonlabel, which should not contain morphospecies information

5647 10/18/2012 02:14 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Mapped acceptedScientificName

5646 10/18/2012 01:51 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Also create the identifyingtaxonomicname on the verbatim taxonlabel supplied by the datasource, in addition to on the TNRS input taxonlabel that the verbatim taxonlabel is matched up with

5645 10/18/2012 01:46 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Expanded brace expressions for putting together the identifyingtaxonomicname

5644 10/18/2012 01:21 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Always generate the concatenated identifyingtaxonomicname, even for higher taxa, to ensure that this field is always populated. Note that this will cause names of higher taxa to be scrubbed by TNRS, but this is usually not a problem because such names either have no match or not a close enough match based on the name only. Naming conventions generally cause names at different ranks to be different, so that collisions with lower ranks should not be a problem.

5643 10/18/2012 01:05 PM Aaron Marcuse-Kubitza

tnrs_db: Fixed bug where needed to remove internal identifyingtaxonomicname duplicates as well as duplicates with existing Name_submitted values, to avoid violating the TNRS.tnrs pkey constraint when the scrubbed names are later inserted. Note that the taxonlabel_0_unique_identifying_name unique index is not sufficient to prevent internal duplicates, because it includes the creator_id (and thus allows multiple instances of the same name defined by different creators).

5642 10/18/2012 01:01 PM Aaron Marcuse-Kubitza

sql.py: mk_select(): Don't add table0 to order_by with no table, because this could cause it not to match a corresponding DISTINCT ON column with no explicit table. PostgreSQL apparently does not treat a column with no explicit table and a column with the applicable table as identical for purposes of ORDER BY/DISTINCT ON checking, even when they refer to the same physical column.

5641 10/18/2012 12:53 PM Aaron Marcuse-Kubitza

sql.py: mk_select(): order_by defaults to first distinct_on column when distinct_on provided

5640 10/18/2012 12:36 PM Aaron Marcuse-Kubitza

tnrs_db: Updated with schema changes

5639 10/18/2012 12:33 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Renamed taxonomicnamewithauthor to taxonomicname because it is equivalent to Darwin Core's scientificName

5638 10/18/2012 12:25 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Renamed taxonomicname to binomial because it excludes the author

5637 10/18/2012 12:15 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel.taxonomicname, taxonomicnamewithauthor comments: Corrected to show that taxonomicnamewithauthor is actually scientificName, while taxonomicname does not directly correspond to a DwC term (but would be the binomial)

5636 10/18/2012 12:13 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel.taxonomicnamewithauthor comment: Removed no longer applicable 'Equivalent to "Name sec. x"'. The "sec" is now stored in taxonconcept.concept_reference_id.

5635 10/18/2012 12:10 PM Aaron Marcuse-Kubitza

mappings/Makefile: .VegCore.csv.last_cleanup: Remove duplicate entries using uniq

5634 10/18/2012 12:09 PM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Removed duplicate entries using uniq

5633 10/18/2012 12:06 PM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Removed *scientificNameWithAuthorship, which are now represented by *scientificName

5632 10/18/2012 12:04 PM Aaron Marcuse-Kubitza

mappings: Renamed *scientificNameWithAuthorship to *scientificName because scientificNameWithAuthorship is actually a synonym of DwC's scientificName ("The full scientific name, with authorship and date information if known" <http://rs.tdwg.org/dwc/terms/#scientificName&gt;)

5631 10/18/2012 11:57 AM Aaron Marcuse-Kubitza

mappings: Renamed *scientificName to *binomial because DwC defines the scientificName as "The full scientific name, with authorship and date information if known", but many datasources do not include the author in their scientific name, and the fields scientificName is mapped to in VegBIEN assume it does not include the author

5630 10/18/2012 11:44 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added verbatimBinomial

5629 10/18/2012 11:41 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Redefined *binomial to "Taxonomic name without author", rather than genus+species

5628 10/18/2012 11:32 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonconcept.taxonlabel_id: Changed type from serial to integer because this is a subclass, and therefore each taxonconcept must first have a corresponding entry in taxonlabel

5627 10/18/2012 11:29 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Moved taxonlabel.concept_reference_id to new taxonconcept table, which is a subclass of taxonlabel that adds information about who the taxon concept is according to

5626 10/18/2012 11:13 AM Aaron Marcuse-Kubitza

taxonlabel: Renamed accepted_label_id to canon_label_id to allow any taxonlabel to be the canonical taxonlabel for this taxonlabel, whether or not its status is accepted

5625 10/18/2012 11:01 AM Aaron Marcuse-Kubitza

schemas/filter_ERD.csv: Remove the methodtaxonclass.submethod_id fkey to taxonlabel, to make room in the ERD for additional taxon tables

5624 10/18/2012 10:52 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: establishmentmeans_dwc: Corrected source comment

5623 10/18/2012 10:51 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonomic_status enum: Added source comment

5622 10/18/2012 10:49 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel_relationship: Added relationship, with relationship enum

5621 10/18/2012 10:21 AM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Mapped taxonomicStatus

5620 10/18/2012 10:20 AM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/test.xml.ref: Updated inserted row count

5619 10/18/2012 10:15 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Removed duplicate entry for taxonomicStatus, which is also a DwC term

5618 10/18/2012 10:14 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added taxonomicStatus

5617 10/18/2012 10:13 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Added taxonstatus, with taxonomic_status enum

5616 10/18/2012 09:40 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel.creator_id comment: Removed no longer accurate comment that this is the "according to" and "Name sec. x", which is now stored in concept_reference_id

5615 10/18/2012 09:37 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Added concept_reference_id, which is the entity that defined the taxon concept (who the taxon label is according to)

5614 10/18/2012 09:22 AM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: Moved taxonlabel_relationship to the right of taxonlabel to provide room for taxonlabel to grow

5613 10/17/2012 04:27 PM Aaron Marcuse-Kubitza

Regenerated vegbien.ERD exports

5612 10/17/2012 04:25 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Remapped morphospecies to new taxonlabel.morphospecies per today's conference call

5611 10/17/2012 04:23 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonlabel: Added separate morphospecies field per today's conference call, where it was decided it could not go in taxonepithet (the lowest-rank component of the name)

5610 10/17/2012 04:17 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Deleted taxonusage table per today's conference call, where it was decided that it was not needed

5609 10/17/2012 04:14 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Renamed taxonlabel_ancestor to taxonlabel_relationship per today's conference call, where it was decided that it would eventually contain asserted relationships (such as synonym and parent) in addition to autopopulated ancestor relationships

5608 10/17/2012 04:12 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Renamed taxonconcept to taxonlabel per today's conference call, where it was decided that taxonconcept contained too many unrelated fields to be purely a taxon concept

5607 10/17/2012 04:01 PM Aaron Marcuse-Kubitza

inputs/import.stats.xls: Updated import times

5606 10/17/2012 04:01 PM Aaron Marcuse-Kubitza

inputs/test_taxonomic_names/_scrub/public.sql, TNRS.sql: Regenerated with schema changes

5605 10/17/2012 01:47 PM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: Fixed lines

5604 10/17/2012 01:45 PM Aaron Marcuse-Kubitza

Regenerated vegbien.ERD exports

5603 10/17/2012 01:44 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonconcept_ancestor: Renamed taxonconcept_id to descendant_id to emphasize the direction of the relationship between the two taxonconcepts

5602 10/17/2012 01:35 PM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: Added taxonconcept_ancestor to the diagram since it is now a core table for storing taxonomic information

5601 10/17/2012 01:15 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Mapped accordingTo to taxonconcept.creator_id, and have it take the place of identifiedBy when both are present

5600 10/17/2012 01:12 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Remapped people's names split apart into name components in party to new party.fullname, which does not require splitting or make assumptions about the number of people who may be listed in a particular name field and which components of their name(s) are present

5599 10/17/2012 01:02 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: party: Added fullname

5598 10/17/2012 12:55 PM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added accordingTo

5597 10/17/2012 12:47 PM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/map.csv: Mapped Name_matched_url to scientificNameID, since the URL uniquely identifies the matched taxonconcept

5596 10/17/2012 12:43 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonconcept: Renamed taxonname to taxonepithet for clarity and to be consistent with TCS's use of "epithet" to denote what the taxonname was intended to be (http://www.tdwg.org/standards/117/download/#/UserGuidev_1.3.pdf)

5595 10/17/2012 12:18 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonconcept.creator_id: Documented that this is the concept reference for a taxon concept with an "according to", or the identifier's name for a nominal concept, and is equivalent to "Name sec. x"

5594 10/17/2012 11:50 AM Aaron Marcuse-Kubitza

sql_io.py: import_csv(): Add a row_num column at the beginning of the table, which is autopopulated by csvs.RowNumFilter (it cannot be autopopulated by the serial datatype, because this does not support COPY FROM with a NULL-equivalent value in the serial field). This fixes a bug in csv2db where rows would not stay in inserted order upon querying the table, and would be returned in a different order each query, which prevented LIMIT/OFFSET based subsetting from returning consistent, nonoverlapping results. This occurs because PostgreSQL unfortunately does not return rows in inserted order (or any stable order: "If sorting is not chosen, the rows will be returned in an unspecified order [which] must not be relied on" <http://www.postgresql.org/docs/8.3/static/queries-order.html&gt;), so an explicit ORDER BY is always needed to ensure staging table rows are retrievable in the order they were inserted.

5593 10/17/2012 11:43 AM Aaron Marcuse-Kubitza

csvs.py: Added RowNumFilter, which adds a row # column at the beginning of each row

5592 10/17/2012 11:42 AM Aaron Marcuse-Kubitza

streams.py: LineCountStream, LineCountInputStream: Fixed bug where line_num was 1 too high because it started at 1 and was incremented before each line is returned. It now properly starts at 1, but the initial line_num value is 0 to increment to 1 upon encountering the first line. This off-by-one behavior may have been needed for code that associates an error message with a line #, but such code should add 1 to the line_num to get the line # of the error if the error prevents the next line from being read by the LineCount*Stream.

5591 10/17/2012 11:04 AM Aaron Marcuse-Kubitza

sql_io.py: import_csv(): Take a reader and header rather than a stream to allow callers to pass in a wrapped CSV reader for filtering, etc.