Activity
From 12/22/2012 to 01/20/2013
01/18/2013
- 02:37 PM Revision 7317: Added inputs/newWorld/newWorldCountries/_no_import
- 02:33 PM Revision 7316: to_do/timeline.2013.xls: Updated with Brad's modifications
- 02:18 PM Revision 7315: Added inputs/FIA/_src/FIA_summary.b-e.00079.pdf from Bob
- 02:07 PM Revision 7314: Added inputs/.herbaria/_archive/
- 01:02 PM Revision 7313: inputs/.herbaria/: Removed no longer needed geoscrub.*.sql, which has been replaced with bien3_adb.*.sql
- 01:00 PM Revision 7312: inputs/.herbaria/: Removed no longer needed herbaria/. Use ih/ instead.
- 12:58 PM Revision 7311: Added inputs/.herbaria/ih/ and corresponding bien3_adb MySQL export
- 12:43 PM Revision 7310: mappings/VegCore-VegBIEN.csv: Don't create NCBI crosslinks for the matched taxonomic name. These crosslinks are no longer needed now that TNRS provides a separate accepted name on which crosslinks can be made.
- 12:32 PM Revision 7309: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Include the accepted name's row next to the matched name's row instead of merging the two together into one TNRS row, to allow including separate taxondeterminations for the matched and accepted names. Added Max_score from TNRS.tnrs.
- 12:25 PM Revision 7308: schemas/vegbien.sql: taxondetermination_set_iscurrent(): Added new determinationtype accepted to sort order
- 12:01 PM Revision 7307: mappings/VegCore-VegBIEN.csv: Mapped accepted* taxonomic name, now to separate accepted taxondetermination
- 11:35 AM Revision 7306: mappings/VegCore.csv: Regenerated from wiki
- 11:20 AM Revision 7305: schemas/vegbien.sql: taxondetermination_set_iscurrent(): Changed TNRS determinationtype from computer to matched, to allow for a separate accepted determinationtype
- 10:57 AM Revision 7304: schemas/vegbien.sql: taxonlabel: Removed creationdate, which duplicates taxondetermination.determinationdate
- 10:08 AM Revision 7303: schemas/vegbien.sql: analytical_stem_view: isNewWorld: Removed no longer needed COALESCE() to false, because newWorldCountries now uses false where applicable instead of NULL. This also ensures that isNewWorld will be NULL if there is no country name to test, which was not the case in the previous workaround.
- 10:02 AM Revision 7302: Added inputs/newWorld/newWorldCountries/ with postprocess.sql that sets isNewWorld to false wherever it's NULL. (The input table only marks New World countries as true, but doesn't mark non-New World countries as false.)
- 09:50 AM Revision 7301: schemas/vegbien.sql: analytical_stem_view: isNewWorld: Fixed bug where need to COALESCE() "newWorldCountries"."isNewWorld" to false, because it is only set to a boolean for countries that are New World
- 09:19 AM Revision 7300: README.TXT: Full database import: freeing disk space: Updated import schema size, which is smaller due to the removed CTFS staging tables, removed duplicate rows, and possibly fewer index holes
- 08:56 AM Revision 7299: README.TXT: Full database import: After running `make schemas/$version/publish`, added `unset version` to make sure future version-dependent commands use the public schema
- 08:50 AM Revision 7298: schemas/vegbien.sql: taxon_trait_view: Fixed bug where measurementUnit needed to be set to trait.units, not name
- 08:42 AM Revision 7297: schemas/vegbien.sql: provider_count_view: Don't set default values for sourcetype/observationtype, because the appropriate values are now set for all top-level inputs and these defaults are not applicable for data owners not in geoscrub.herbaria
- 08:41 AM Revision 7296: inputs/bien2_traits/Source/map.csv: Mapped observationType
- 08:27 AM Revision 7295: schemas/vegbien.sql: taxondetermination: Removed taxondetermination_computer_min_fit CHECK constraint, whose functionality is now duplicated by unscrubbed_taxondetermination_view's Max_score filter condition. The score threshold value should only be maintained in one place, namely unscrubbed_taxondetermination_view.
- 08:23 AM Revision 7294: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Fixed bug where need to filter out any names that will be rejected by taxondetermination's constraints, because otherwise, these names will stay in unscrubbed_taxondetermination_view and be repeatedly reimported
- 07:38 AM Revision 7293: inputs/.TNRS/schema.sql: tnrs: Added Max_score column for use in filtering out names that will be rejected by taxondetermination's constraints
- 07:22 AM Revision 7292: inputs/.TNRS/schema.sql: Renamed tnrs_populate_accepted_scientific_name() trigger to tnrs_populate_derived_fields() to accommodate additional derived fields
- 07:14 AM Revision 7291: tnrs_db: Support multiple appended columns in the tnrs table
- 07:13 AM Revision 7290: csvs.py: ColInsertFilter: Support adding multiple, consecutive columns
- 06:30 AM Revision 7289: schemas/functions.sql: _max(), _min(): Put $n params all on one line to match other aggregating functions
- 06:28 AM Revision 7288: schemas/functions.sql: _max(), _min(): Use PostgreSQL built-in functions GREATEST(), LEAST() instead of a query with aggregating functions
- 06:02 AM Revision 7287: README.TXT: Added Single datasource import section with commands to import/reimport/scrub just a datasource rather than the full DB
- 05:54 AM Revision 7286: schemas/vegbien.sql: taxondetermination: taxondetermination_set_iscurrent_on_delete() trigger: Fixed bug where need to suppress any foreign key exception, which occurs during a cascading delete because the associated taxonoccurrence has already been deleted, preventing any other taxondeterminations of that taxonoccurrence from being updated
- 05:35 AM Revision 7285: input.Makefile: Taxonomic scrubbing: Added reimport_scrub
- 05:34 AM Revision 7284: input.Makefile: Import to VegBIEN: Added reimport
- 05:28 AM Revision 7283: input.Makefile: Taxonomic scrubbing: Added rescrub
- 05:21 AM Revision 7282: input.Makefile: Taxonomic scrubbing: Added scrub target and use it in import_scrub
- 05:18 AM Revision 7281: input.Makefile: Import to VegBIEN: Moved import, rm to top of section since they are top-level targets and don't depend on the variables defined for %/import
- 05:17 AM Revision 7280: input.Makefile: Moved rm to Import to VegBIEN section
- 05:16 AM Revision 7279: input.Makefile: Moved taxonomic scrubbing targets to separate Taxonomic scrubbing section
- 04:43 AM Revision 7278: inputs/import.stats.xls: Updated import times
- 03:34 AM Revision 7277: schemas/vegbien.sql: provider_count_view: Include only sources with at least one row. Currently (as of r7023), all entries in BIEN2's geoscrub.herbaria are also in VegBIEN, so the filter is not yet necessary, but switching to bien3_adb.ih could create source entries without data rows which should be excluded from the providers list.
- 03:25 AM Revision 7276: import_all: Output the PIDs of the import_scrub and after_import processes, so those processes can be managed without shell job control. This is useful if the connection is lost to the remote shell running the import, which prevents using job control on the import processes.
- 01:23 AM Revision 7275: input.Makefile: Import to VegBIEN: import_scrub: Run `make scrub` in the background, to allow the import to continue with the next table rather than having to wait for the current table to be scrubbed
- 12:53 AM Revision 7274: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Moved waitself call to top of script
- 12:52 AM Revision 7273: inputs/import.stats.xls: Updated import times
- 12:24 AM Revision 7272: inputs/import.stats.xls: Added Postprocessing section for use with the next import
- 12:05 AM Revision 7271: inputs/import.stats.xls: Updated import times. Total does not yet include postprocessing.
01/17/2013
- 11:29 PM Revision 7270: import_times: Add blank line before \"Postprocessing logs\" to separate it from the input logs
- 11:28 PM Revision 7269: import_times: Separate out the postprocessing logs (e.g. public.unscrubbed_taxondetermination_view), as the import times in these logs are not aggregated together (each input has its own run of the postprocessing script)
01/16/2013
- 02:55 PM Revision 7268: root Makefile: Datasources: import: Use new import_scrub instead of import (input.Makefile)
- 02:51 PM Revision 7267: import_all: Use new import_scrub (input.Makefile) instead of import, which avoids needing to start background processes for tnrs-remake and scrub-remake
- 02:50 PM Revision 7266: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Fixed bug where need to use tnrs.make's lockfile instead because can't be importing while tnrs.make is scrubbing. tnrs.make leaves tnrs in an incomplete state while running because the accepted names are parsed *after* their matched names. Using a separate lockfile would cause some accepted names to be missing.
- 02:27 PM Revision 7265: input.Makefile: Import to VegBIEN: Added import_scrub, which runs `make scrub` after the import
- 02:26 PM Revision 7264: root Makefile: Datasources: Added scrub, which runs tnrs-remake and scrub-remake
- 02:18 PM Revision 7263: inputs/.TNRS/*/*.make: Only allow one instance of the script to be running at any time, by using new waitself
- 02:15 PM Revision 7262: waitpid, lockfile: Changed $interval default to 5s to work with smaller imports, where less waiting is needed
- 02:14 PM Revision 7261: Added waitself
- 02:11 PM Revision 7260: bin/lockfile: Include the PID in the lockfile to avoid the need to manually remove lockfiles. On Mac, this requires using shlock instead of lockfile.
- 01:35 PM Revision 7259: Added bin/lockfile
- 01:34 PM Revision 7258: Added pid2name
- 01:33 PM Revision 7257: Added name2pids
- 01:33 PM Revision 7256: waitpid: Use `ps` instead of /proc to also work on Mac
- 01:07 PM Revision 7255: inputs/.TNRS/tnrs/tnrs.make: Fixed bug where need special handling to support being run as a .make script
- 11:59 AM Revision 7254: inputs/.geoscrub/_src/README.TXT: Added dates for e-mails from Jim
- 11:57 AM Revision 7253: inputs/.geoscrub/_src/README.TXT: Added e-mail from Jim about repository with scripts to generate the geoscrub_output table
- 11:02 AM Revision 7252: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Fixed bug where need to use tnrs_accepted.Name_submitted IS NOT NULL rather than tnrs_accepted.* IS NOT NULL, because tnrs_accepted.* (which plain tnrs_accepted gets changed to by PostgreSQL) checks *each field* of the tnrs_accepted tuple rather than checking if the tuple itself is NULL
- 10:23 AM Revision 7251: inputs/.TNRS/schema.sql: Added back tnrs+accepted view, which is useful for debugging the import of the TNRS results
- 09:21 AM Revision 7250: inputs/REMIB/Specimen/postprocess.sql: Added back ARIZ, NY because some REMIB specimens for these datasources are not yet in the datasources themselves
- 08:43 AM Revision 7249: Added inputs/REMIB/Specimen/postprocess.sql to remove institutions that we have direct data for
- 08:43 AM Revision 7248: Placed inputs/REMIB/_archive/ under version control
- 08:23 AM Revision 7247: Added inputs/SpeciesLink/Specimen/postprocess.sql to remove institutions that we have direct data for
- 08:21 AM Revision 7246: Placed inputs/SpeciesLink/_archive/ under version control
- 07:56 AM Revision 7245: input.Makefile: $(import?): Renamed $public_import option to $full_import because it applies to any import of all datasources, not just a public import on vegbiendev
- 07:23 AM Revision 7244: schemas/vegbien.sql: analytical_stem_view: Changed `WHERE COALESCE(taxondetermination.iscurrent, true)` to a join condition to enable using the taxondetermination_single_current_determination index, which produces the filtered rows directly. Note that this index will not be used for full-database imports, because the query planner uses hash joins everywhere instead of nested loops.
- 06:47 AM Revision 7243: db_xml.py: put_table(): Fixed bug where for views, shouldn't advance start (OFFSET clause) after each chunk, because views are typically dynamic and will contain a new set of rows after the first set is imported
- 06:41 AM Revision 7242: sql.py: Added view_exists()
- 06:16 AM Revision 7241: inputs/.TNRS/schema.sql: Removed no longer used tnrs_canon. unscrubbed_taxondetermination_view uses its definition directly instead.
- 06:14 AM Revision 7240: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Added comment from tnrs_canon
- 06:12 AM Revision 7239: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Added comment from tnrs_canon
- 06:09 AM Revision 7238: 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.
- 05:52 AM Revision 7237: 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
- 05:48 AM Revision 7236: 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
- 05:40 AM Revision 7235: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Fixed bug where need special handling to support being run as a .make script
- 03:35 AM Revision 7234: input.Makefile: Editing import: Added unscrub to remove TNRS taxondeterminations
- 03:34 AM Revision 7233: psql_script_vegbien: Added no_query_results option to hide results of calls to void functions
- 03:33 AM Revision 7232: schemas/vegbien.sql: Added delete_scrubbed_taxondeterminations()
- 01:43 AM Revision 7231: root Makefile: python-Darwin: Added instructions to install dateutil for Python 3 as well as Python 2, for use in PL/Python functions
- 01:42 AM Revision 7230: root Makefile: python-Darwin: Added note that Python 2 comes preinstalled
- 01:15 AM Revision 7229: Added inputs/GBIF/Specimen/postprocess.sql to remove institutions that we have direct data for
01/15/2013
01/14/2013
- 05:21 PM Revision 7227: 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.
- 05:14 PM Revision 7226: 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
- 05:13 PM Revision 7225: 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
- 05:12 PM Revision 7224: README.TXT: Data import: Checking free disk space: Updated import schema size to 110GB
- 04:37 PM Revision 7223: Added inputs/Madidi/_README.TXT
- 04:35 PM Revision 7222: new_terms.csv: Regenerated
- 04:34 PM Revision 7221: inputs/Madidi/new_terms.csv: Regenerated
- 04:19 PM Revision 7220: inputs/Madidi/_archive/2010-1-2/: Set svn:ignore
- 04:18 PM Revision 7219: inputs/Madidi/_README.TXT: Archived to _archive/2010-1-2/
- 03:43 PM Revision 7218: inputs/Madidi/: Refreshed. Note that new export has a completely new schema.
- 03:42 PM Revision 7217: inputs/Madidi/: Refreshed. Note that new export has a completely new schema.
- 01:53 PM Revision 7216: 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
- 01:18 PM Revision 7215: mappings/VegCore-VegBIEN.csv: fieldNumber (authorEventCode): Fixed bug where locationevent.authorlocationcode should be authoreventcode
- 12:19 PM Revision 7214: Added inputs/Madidi/map.csv, created from new_terms.csv
- 12:16 PM Revision 7213: inputs/Madidi/_archive/: Set svn:ignore
- 12:15 PM Revision 7212: csvs.py: sniff(): TSVs: Don't turn off quoting, because some TSVs (such as Madidi.IndividualObservation) do quote fields
- 12:13 PM Revision 7211: csvs.py: TsvReader: Use csv.reader.next() when possible to support quoted fields, such as in Madidi.IndividualObservation
- 11:43 AM Revision 7210: input.Makefile: Configuration: $(exts): Added .dat, which the new Madidi files use
- 08:39 AM Revision 7209: mappings/Makefile: VegCore.tables.csv: Removed no longer needed removal of Namespaces table, which is now marked as just a section, not a table
- 08:37 AM Revision 7208: mappings/VegCore.csv: Regenerated from wiki
- 07:39 AM Revision 7207: Added to_do/timeline.2013.xls (from Brad, converted to .xls)
- 07:30 AM Revision 7206: to_do/timeline.doc: Renamed to timeline.2012.doc to allow for a separate 2013 timeline
01/11/2013
- 05:05 PM Revision 7205: README.TXT: Data import: Deleting imports before the last: Added instructions to keep a previous import instead of deleting it
- 04:22 PM Revision 7204: 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
- 01:03 PM Revision 7203: schemas/vegbien.ERD.mwb: Regenerated exports
- 10:19 AM Revision 7202: 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
- 09:44 AM Revision 7201: **/new_terms.csv, **/unmapped_terms.csv: Regenerated using `make missing_mappings`
- 09:19 AM Revision 7200: mappings/VegCore-VegBIEN.csv: morphoname: Remapped to the original rather than current taxondetermination because this is the *original* name applied by the author
- 09:16 AM Revision 7199: 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
- 09:11 AM Revision 7198: mappings/VegCore-VegBIEN.csv: Mapped recordNumber to new specimenreplicate.collectionnumber
- 09:02 AM Revision 7197: mappings/VegCore-VegBIEN.csv: Also map recordNumber (collectionnumber) to the indirect voucher's specimenreplicate
- 08:48 AM Revision 7196: inputs/*/*/map.csv: Remapped recordNumber to new individualCode where applicable
- 08:44 AM Revision 7195: mappings/VegCore-VegBIEN.csv: Mapped individualCode. authortaxoncode: Prefer tag over recordNumber (collectionnumber), because this applies to the plant rather than the specimen.
- 08:17 AM Revision 7194: mappings/VegCore-VegBIEN.csv: Mapped morphoname
- 08:16 AM Revision 7193: mappings/VegCore.csv: Regenerated from wiki
- 08:14 AM Revision 7192: mappings/VegCore.csv: Regenerated from wiki
- 08:04 AM Revision 7191: schemas/vegbien.sql: taxonverbatim: Added morphoname (which is different from the morphospecies suffix)
- 07:33 AM Revision 7190: 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.
- 07:28 AM Revision 7189: 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.
- 07:23 AM Revision 7188: schemas/vegbien.sql: specimenreplicate: Added collectionnumber
- 07:17 AM Revision 7187: schemas/vegbien.sql: taxonlabel: Removed no longer used matched_label_fit_fraction. Use taxondetermination.taxonfit instead.
- 07:02 AM Revision 7186: inputs/*/*/test.xml.ref: Restored inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
- 06:55 AM Revision 7185: schemas/vegbien.ERD.mwb: Expanded analytical_stem to fit the width of all fields
- 06:53 AM Revision 7184: 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
- 06:38 AM Revision 7183: README.TXT: Debugging: Added instructions for "binary chop" debugging, which requires syncing the DB schema to the svn working copy
- 06:08 AM Revision 7182: mappings/VegCore-VegBIEN.csv: Removed no longer used mappings for verbatimScientificName in _if conditions
- 06:08 AM Revision 7181: inputs/.NCBI/nodes/test.xml.ref: Restored inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
- 06:06 AM Revision 7180: 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
- 06:01 AM Revision 7179: 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.
- 05:53 AM Revision 7178: 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
- 05:48 AM Revision 7177: Added inputs/ACAD/Specimen/logs/steps.by_col.log.sql
- 05:45 AM Revision 7176: sql_gen.py: Join: Added support for mapping values which are lists, for use in USING joins
- 05:40 AM Revision 7175: inputs/SALVIAS/*/test.xml.ref: Restored SALVIAS* inserted row counts, which had gotten auto-accepted from a test run on a non-empty DB
- 05:01 AM Revision 7174: schemas/vegbien.sql: analytical_stem: Added locationName (authorPlotCode), subplot, individualCode (authorPlantCode) for use in validation
- 04:57 AM Revision 7173: 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
- 04:56 AM Revision 7172: schemas/vegbien.sql: sync_analytical_stem_to_view(): Changed to PL/pgSQL function to allow adding PL/pgSQL commands
- 03:26 AM Revision 7171: schemas/vegbien.ERD.mwb: Moved family_higher_plant_group to leave room for analytical_stem to expand
- 03:08 AM Revision 7170: mappings/VegCore-VegBIEN.csv: Removed no longer used mappings for verbatimScientificName in _if conditions
- 02:59 AM Revision 7169: 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.
- 02:44 AM Revision 7168: 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
- 02:34 AM Revision 7167: Removed no longer used inputs/.TNRS/tnrs_accepted, tnrs_other. Use the tnrs_canon view instead.
- 02:22 AM Revision 7166: Removed no longer used inputs/.TNRS/tnrs_accepted, tnrs_other. Use the tnrs_canon view instead.
- 02:18 AM Revision 7165: Added inputs/.TNRS/_archive/
- 02:18 AM Revision 7164: 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)
- 02:07 AM Revision 7163: 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)
- 02:03 AM Revision 7162: 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
- 01:42 AM Revision 7161: 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
- 01:11 AM Revision 7160: 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)
- 12:49 AM Revision 7159: 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
- 12:18 AM Revision 7158: inputs/.TNRS/schema.sql: tnrs_canon: Annotations: Always use value from the matched name, because the accepted name does not have this
- 12:05 AM Revision 7157: 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
01/10/2013
- 11:52 PM Revision 7156: 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.
- 11:45 PM Revision 7155: 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.
- 11:34 PM Revision 7154: 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)
- 11:23 PM Revision 7153: inputs/.TNRS/schema.sql: Removed no longer used tnrs+accepted. Use tnrs_canon or a self-join of tnrs instead
- 11:22 PM Revision 7152: schemas/vegbien.sql: tnrs_input_name: Use TNRS.tnrs directly instead of the now-deprecated tnrs+accepted
- 11:12 PM Revision 7151: 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)
- 10:50 PM Revision 7150: 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
- 09:06 PM Revision 7149: mappings/VegCore-VegBIEN.csv: Remapped matched*Fit_fraction to taxondetermination.taxonfit when a taxondetermination, not just a taxonlabel, is provided
- 09:03 PM Revision 7148: bin/map: map_table(): Resolving prefixes: Fixed bug where need to use list instead of tuple for metadata value mappings
- 08:16 PM Revision 7147: 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
01/09/2013
- 05:34 PM Revision 7146: mappings/VegCore-VegBIEN.csv: Don't create a separate TNRS input taxonlabel if taxonIsCanonical exists
- 05:24 PM Revision 7145: inputs/.TNRS/schema.sql: tnrs_canon: Fixed bug where need to always use Unmatched_terms from tnrs rather than tnrs_accepted
- 05:07 PM Revision 7144: inputs/.TNRS/schema.sql: Added tnrs_canon, which stores the most canonicalized name output by TNRS
- 04:17 PM Revision 7143: 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.
- 03:59 PM Revision 7142: 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.
- 03:52 PM Revision 7141: xml_func.py: Simplifying functions: Merging: Added _merge_prefix() passthru
- 03:33 PM Revision 7140: schemas/functions.sql: Added _merge_prefix()
- 02:42 PM Revision 7139: 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
- 02:18 PM Revision 7138: inputs/.TNRS/schema.sql: tnrs+accepted: Added new Accepted_scientific_name column and mapped it in public.unscrubbed_taxondetermination_view
- 11:06 AM Revision 7137: schemas/vegbien.sql: tnrs_input_name: Fixed bug where need to filter out tnrs+accepted rows with NULL Accepted_scientific_name, because inputs to tnrs_db must be strings
- 10:53 AM Revision 7136: schemas/vegbien.sql: tnrs_input_name: Prepend TNRS accepted names that have not yet been parsed. This allows parsing TNRS accepted names without first needing to import them into taxonlabels, which may not occur until the next import.
- 10:09 AM Revision 7135: inputs/.TNRS/schema.sql: tnrs+accepted: Use new Accepted_scientific_name to join to tnrs_accepted.Name_submitted
- 10:05 AM Revision 7134: inputs/.TNRS/schema.sql: tnrs: Added tnrs_populate_accepted_scientific_name() trigger
- 09:57 AM Revision 7133: inputs/.TNRS/schema.sql: tnrs: Added Accepted_scientific_name field which will contain the joined-together accepted name that gets re-parsed by TNRS
- 09:13 AM Revision 7132: inputs/.TNRS/: Changed tnrs+accepted to a view (defined in schema.sql) so accepted names would automatically be populated as they are parsed by TNRS, rather than needing to run `make inputs/.TNRS/tnrs+accepted/reinstall` to populate them
- 08:16 AM Revision 7131: mappings/VegCore-VegBIEN.csv: Also map the morphospecies to the accepted taxonverbatim when an accepted name is provided
- 08:01 AM Revision 7130: schemas/vegbien.sql: taxonverbatim: taxonverbatim_unique: Added morphospecies so that there can be multiple taxonverbatims for the same taxonlabel, each with different morphospecies suffixes
- 04:17 AM Revision 7129: inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv: Mapped Accepted_name.*
- 03:02 AM Revision 7128: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Use new tnrs+accepted instead of tnrs so that the accepted name can be imported at the same time
- 02:23 AM Revision 7127: import_all: Reinstall tnrs+accepted, for eventual use by unscrubbed_taxondetermination_view
- 02:20 AM Revision 7126: Added inputs/.TNRS/tnrs+accepted/, which self-joins the TNRS results to their parsed accepted names
- 02:02 AM Revision 7125: import_all: Directly import just the TNRS tables that should be imported, because some TNRS tables are included in import_order.txt so that they are part of the automated testing, but should not be imported at the same time as tnrs_accepted/tnrs_other
- 12:45 AM Revision 7124: inputs/import.stats.xls: Updated import times
01/08/2013
- 11:24 PM Revision 7123: with_all: $all mode: Fixed bug where need " " before # for it to be interpreted as a comment (unlike in a Makefile, where the " " often needs to be left out to avoid it being treated as part of a variable value)
- 10:55 PM Revision 7122: bin/map: Made $redo flag default to off, because redo mode is slow (all tables have to be truncated) and is only needed when running tests on a public schema with data in it, which would not be the case on a development machine where tests are usually run
- 10:19 PM Revision 7121: import_all: Made temporary vars local, so they wouldn't affect the calling shell
- 09:45 PM Revision 7120: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Sort by taxondetermination.taxonoccurrence_id instead of taxondetermination_id to allow scanning the taxondetermination_single_current_determination index, which includes only current determinations and avoids needing to scan past many non-current determinations. Note that using taxonoccurrence_id does not create sort order ambiguity between taxondeterminations with the same taxonoccurrence_id, because there is only one current determination per taxonoccurrence.
- 09:32 PM Revision 7119: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Inner-join to taxonverbatim and taxonlabel instead of LEFT JOINing, because only taxondeterminations with a taxonlabel can have accepted taxondeterminations (otherwise there would be no name to scrub)
- 09:30 PM Revision 7118: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Inner-join to tnrs instead of LEFT JOINing, because only taxondeterminations whose taxonlabels have already been scrubbed by TNRS should have accepted taxondeterminations added. Removed now-unneeded filter by tnrs.Name_submitted IS NOT NULL, which is replaced by the inner join.
- 08:46 PM Revision 7117: sql_io.py: put_table(): ensure_cond(): Fixed bug where need to wrap strings used in the tracked error message in strings.ustr()
- 08:33 PM Revision 7116: xml_dom.py: replace_with_text(): Fixed bug where need to use scalar.is_nonnull_scalar() instead of is_scalar() to avoid converting None values to the string 'None'
- 08:32 PM Revision 7115: scalar.py: Added is_nonnull_scalar()
01/07/2013
- 08:17 PM Revision 7114: README.TXT: Data import: Fixed bug where `make inputs/upload` needs to be run on local machine, not vegbiendev
- 08:16 PM Revision 7113: sql.py: create_table(): Support creating a table like a view
- 08:04 PM Revision 7112: sql.py: Added InvalidTypeException and parse it in parse_exception()
- 07:39 PM Revision 7111: mappings/VegCore.csv: Regenerated from wiki
- 07:34 PM Revision 7110: schemas/vegbien.sql: taxondetermination_set_iscurrent(): Fixed bug where need to sort scrubbed determinations first for scrub.make to work. (Otherwise, a datasource determination might remain iscurrent even after a scrubbed determination was added, causing scrub.make to repeatedly attempt to re-add it.)
- 07:20 PM Revision 7109: inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv: Set dateIdentified to _now()
- 07:20 PM Revision 7108: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Unset $n to avoid limiting the # rows/iteration
- 07:15 PM Revision 7107: schemas/py_functions.sql: parse_date_range(): Don't parse strings containing a time, because - and ' ' don't have the same meaning as in a date range
- 07:03 PM Revision 7106: xml_dom.py: replace_with_text(): Support any scalar type recognized by scalar.is_scalar()
- 06:54 PM Revision 7105: scalar.py: is_scalar(): Added datetime.datetime
- 06:43 PM Revision 7104: schemas/functions.sql: Added _now()
- 06:39 PM Revision 7103: import_all: Make $dump_opts, $public_import local vars, so they will be automatically unset if the script is aborted
- 06:31 PM Revision 7102: mappings/VegCore-VegBIEN.csv: identificationType: Fixed bug in mapping where extra *_id/ needed to be removed
- 06:25 PM Revision 7101: inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv: Set taxonOccurrenceID to dummy value 0 to enable the taxonoccurrence CHECK constraint to pass. This is needed because the constraint must pass before the pkey (which should already exist) is even checked.
- 06:19 PM Revision 7100: inputs/.TNRS/public.unscrubbed_taxondetermination_view/map.csv: Set identificationType to computer
- 06:18 PM Revision 7099: mappings/VegCore-VegBIEN.csv: Mapped identificationType
- 06:15 PM Revision 7098: mappings/VegCore.csv: Regenerated from wiki
- 05:39 PM Revision 7097: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Use `SELECT source_id FROM source WHERE shortname = ...` instead of source_by_shortname() so that the source table is updated to point to the same schema as the view rather than pointing to whichever version (usually public) is first in the search_path
- 05:23 PM Revision 7096: schemas/vegbien.sql: unscrubbed_taxondetermination_view: Fixed bug where need to include only those taxondeterminations that already have a match in TNRS.tnrs, to avoid adding empty TNRS taxondeterminations. As the concurrent tnrs daemon runs, these taxondeterminations will gradually acquire matches in tnrs and then be processed by scrub.
- 05:00 PM Revision 7095: import_all: Make $import_source a local var, so it will be automatically unset if the script is aborted
- 04:49 PM Revision 7094: vegbien_dest: Schema override for referring to a table in the $public schema: Only process the override when $!schemaVar and $!tableVar are non-*empty*, to allow setting $schema=""
- 04:47 PM Revision 7093: schemas/Makefile: DDL generation: vegbien.sql: Unset $dump_opts so that pg_dump does not use env vars left after running import_all
- 04:44 PM Revision 7092: schemas/Makefile: DDL generation: vegbien.sql: Unset $version so that pg_dump always uses the public schema, even after running import_all
- 04:13 PM Revision 7091: mappings/VegCore.csv: Regenerated from wiki
- 04:13 PM Revision 7090: README.TXT: Testing: Added commands to put in .profile on a development machine
- 04:10 PM Revision 7089: import_all: Added command to add scrubbed taxondeterminations
- 04:09 PM Revision 7088: mappings/VegCore.csv: Regenerated from wiki
- 04:08 PM Revision 7087: import_all: Start tnrs-remake *after* starting the inputs, so that for subset imports (e.g. n=2), there will already be names to scrub when tnrs-remake starts up and it won't enter pause mode to wait for new rows (the pause is calibrated for full imports, and is too long for subset imports)
- 04:01 PM Revision 7086: with_all: Also exclude .archive/ from the subdirs to forward commands to
- 03:40 PM Revision 7085: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Added option to wait for new rows, in the same way tnrs_db does
- 03:38 PM Revision 7084: inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make: Factored new rows added test out into rowsAdded() function
- 03:09 PM Revision 7083: Added inputs/.TNRS/public.unscrubbed_taxondetermination_view/scrub.make, which adds scrubbed taxondeterminations to VegBIEN
- 02:00 PM Revision 7082: root Makefile: Removed $(subMake), which is now defined properly by lib/common.Makefile
- 01:59 PM Revision 7081: lib/common.Makefile: $(subMake): Removed `--makefile=../input.Makefile`, which is specific just to inputs/Makefile
- 01:43 PM Revision 7080: input.Makefile: Import to VegBIEN: $(import): Print the date at the beginning of the import, so successive imports to the same version can be distinguished
- 01:37 PM Revision 7079: input.Makefile: Import to VegBIEN: \$(import): Fixed bug where 2>&1 needs to come after >>$(log_) rather than before
- 01:35 PM Revision 7078: inputs/.TNRS/tnrs/tnrs.make: Usage: Added tnrs_db's $wait flag
- 01:34 PM Revision 7077: inputs/.TNRS/tnrs/tnrs.make: Fixed Usage message to use make, which is needed to set the PATH correctly
- 11:47 AM Revision 7076: Makefiles: Changed "Usage: `make -s ...`" to "Run with `make -s` to avoid echoing make commands"
- 11:44 AM Revision 7075: input.Makefile: Import to VegBIEN: Added %/log_file to view the import log file path
- 11:28 AM Revision 7074: input.Makefile: Import to VegBIEN: $(import): Append to the log file instead of replacing it, to avoid overwriting the log for a previous import to the same versioned schema. This allows a datasource to be (re-)imported multiple times, and is needed by the new method for linking taxonoccurrences to scrubbed taxonomic names.
- 11:22 AM Revision 7073: input.Makefile: Import to VegBIEN: $(import): Always output just to log file if $(log) is on, rather than also copying output to the terminal when $(n) is set. When $(log) is on, the output can still be viewed by tailing the log.
- 11:16 AM Revision 7072: input.Makefile: Import to VegBIEN: $(import): Merged consecutive $(if $(n),...)
- 11:14 AM Revision 7071: input.Makefile: Import to VegBIEN: $(import): Merged consecutive $(if $(log),...)
- 11:07 AM Revision 7070: Added inputs/.TNRS/public.unscrubbed_taxondetermination_view/
- 11:05 AM Revision 7069: mappings/VegCore-VegBIEN.csv: Mapped taxonOccurrencePkey
- 10:58 AM Revision 7068: mappings/VegCore.csv: Regenerated from wiki
- 10:41 AM Revision 7067: input.Makefile: Staging tables installation: Added %_view/install, to prevent trying to edit a view during installation
- 10:31 AM Revision 7066: vegbien_dest: Added schema override support for referring to a table in the $public schema
- 10:29 AM Revision 7065: input.Makefile: Staging tables installation: $(cleanup): Moved setting of $schema, $table before vegbien_dest is run, so it can modify them if needed
- 09:42 AM Revision 7064: inputs/.TNRS/tnrs/tnrs.make: Removed unnecessary setting of $prefix, which now defaults to ""
- 09:40 AM Revision 7063: schemas/vegbien.sql: Added unscrubbed_taxondetermination_view
01/04/2013
- 10:10 PM Revision 7062: inputs/import.stats.xls: Moved CTFS to Deleted section
- 10:03 PM Revision 7061: make_analytical_db: ANALYZE each table after its created so that queries use index scans instead of seq scans
- 09:40 PM Revision 7060: schemas/vegbien.sql: sync_analytical_*_to_view(): Added datasource fkey to source.shortname so removing a datasource will also remove the corresponding rows in the analytical views
- 09:36 PM Revision 7059: schemas/vegbien.sql: Regenerated analytical_stem using sync_analytical_stem_to_view()
- 09:19 PM Revision 7058: input.Makefile: Editing import: rm: Time the command
- 09:15 PM Task #549 (Resolved): add covering indexes on fkeys
- 07:09 PM Task #549: add covering indexes on fkeys
- Added for direct fkeys which are in use
- 05:56 PM Task #549 (Resolved): add covering indexes on fkeys
- * use instructions for *[[Postgres queries#Adding covering indexes on foreign keys|adding covering indexes on foreign...
- 09:14 PM Revision 7057: schemas/vegbien.sql: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>
- 09:11 PM Revision 7056: schemas/vegbien.sql: Fixed fkey constraint names
- 09:09 PM Revision 7055: schemas/vegbien.sql: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>
- 06:59 PM Revision 7054: schemas/vegbien.sql: fkeys to source: Added covering indexes where needed, as described at <https://projects.nceas.ucsb.edu/nceas/issues/549>
- 06:22 PM Revision 7053: schemas/vegbien.sql: commconcept: Renamed source_id back to reference_id (it was previously renamed to source_id in a bulk rename)
- 06:20 PM Revision 7052: schemas/vegbien.sql: taxondetermination: Added back reference_id, which is different than the scoping source_id (reference_id was previously renamed to source_id in a bulk rename)
- 06:04 PM Revision 7051: schemas/vegbien.sql: Renamed taxonconcept_concept_source_id_fkey back to taxonconcept_concept_reference_id_fkey
- 06:02 PM Revision 7050: schemas/vegbien.sql: Renamed *_reference_id_fkey fkeys to *_source_id_fkey
- 05:32 PM Revision 7049: inputs/CTFS/_no_import: Temporarily remove CTFS from the public DB per Rick Condit's request (due to validation issues)
- 05:25 PM Revision 7048: import_all: Run import with $public_import set in order to exclude excluded datasources
- 05:23 PM Revision 7047: input.Makefile: Import to VegBIEN: %/import: Don't run the import if $public_import flag is set and the datasource contains a _no_import file. This allows just excluding a datasource from the public DB, without also removing it from automated testing.
- 05:17 PM Revision 7046: lib/common.Makefile: Added $(and), $(or), $(not)
- 04:30 PM Revision 7045: mappings/VegCore.csv: Regenerated from wiki
- 04:12 PM Revision 7044: schemas/vegbien.sql: taxondetermination: Added scoping source_id field to allow other datasources (e.g. TNRS) to make taxondeterminations. (Repurposed existing non-scoping source_id.)
01/03/2013
- 08:25 AM Revision 7043: make_analytical_db: Fixed bug where can't give public_ select access to all analytical_db views because this apparently adds access rather than passing through the underlying table's permissions
- 08:18 AM Revision 7042: make_analytical_db: Give public_ select access to analytical_db views. This causes the actual access to depend on the underlying table's permissions.
- 07:43 AM Revision 7041: mappings/VegCore.csv: Regenerated from wiki
- 07:39 AM Revision 7040: mappings/Makefile: VegCore.csv: Include only terms that start with a lowercase letter or are all caps. This also avoids the need to filter out VegCore.tables.csv.
- 07:31 AM Revision 7039: mappings/VegCore.csv: Changed line endings to \n in preparation for not running filter_out_cs on the file (which changes line endings to \r\n)
- 02:31 AM Revision 7038: import_all: `make backups/vegbien.$version.backup/test`: Documented that this uses $dump_opts. $dump_opts must be manually set when running this command outside of import_all.
- 02:21 AM Revision 7037: backups/Makefile: Synchronization: %/download: Download the .md5 file first, so that the user is prompted right away for their password rather than after the main file has finished downloading, by which time the password prompt has timed out
- 12:02 AM Revision 7036: mappings/VegCore.csv: Regenerated from wiki
01/02/2013
- 11:34 PM Revision 7035: mappings/VegCore.csv: Regenerated from wiki
- 11:32 PM Revision 7034: mappings/Makefile: VegCore.csv: Fixed bug where need to filter out VegCore.tables.csv case-sensitively so that field names which are the same as a table name don't get filtered out
- 11:23 PM Revision 7033: Added filter_out_cs
- 09:21 PM Revision 7032: README.TXT: Data import: Added step to ensure there are no local modifications using `svn st`
- 08:56 PM Revision 7031: make_analytical_db: Also grant USAGE on the analytical_db schema itself to bien_read, public_
- 07:14 PM Revision 7030: README.TXT: Data import: After import: Also check that the provider_count table contains entries for all inputs
- 07:03 PM Revision 7029: Added inputs/.geoscrub/_src/geovalidity-table.txt, which was attached to Jim's geovalidation e-mail (provided in README.TXT)
- 06:43 PM Revision 7028: inputs/import.stats.xls: Updated import times
- 06:39 PM Revision 7027: README.TXT: Data import: recording the import times in inputs/import.stats.xls: Updated column group header to "By column"
- 06:36 PM Revision 7026: backups/Makefile: Removed no longer used $(psqlVerbose)
- 06:36 PM Revision 7025: backups/Makefile: Removed %.backup/rm_indexes, which is no longer needed because archived imports are now backed up instead of being stored without indexes in the live DB
- 06:31 PM Revision 7024: backups/Makefile: %.backup/remove: Fixed bug where need to use $no_search_path option to psql_script_vegbien
Also available in: Atom