Project

General

Profile

Statistics
| Revision:

# Date Author Comment
6111 11/09/2012 06:32 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Fixed bug where needed to join on new geoscrub_cultivated, not geoscrub, for all geoscrub-related information. geoscrub contains many duplicate records, causing one input row to match many rows in geoscrub, when there should only be one entry for each coordinate pair.

6110 11/09/2012 06:26 PM Aaron Marcuse-Kubitza

Added inputs/.geoscrub/geoscrub_cultivated/

6109 11/09/2012 06:04 PM Aaron Marcuse-Kubitza

inputs/.geoscrub/geoscrub_cleaned_unique/create.sql: Added index on latitudeDecimalVerbatim, longitudeDecimalVerbatim for use by analytical_stem_view

6108 11/09/2012 05:34 PM Aaron Marcuse-Kubitza

inputs/newWorld/geoscrub.schema.~.changes.sql: Change countryNameStd type to text to allow merge-joining with place.country in analytical_stem_view

6107 11/09/2012 05:28 PM Aaron Marcuse-Kubitza

inputs/newWorld/geoscrub.schema.~.changes.sql: ALTER TABLE ... ALTER COLUMN statement: Reformatted to allow adding additional ALTER COLUMN clauses

6106 11/09/2012 05:25 PM Aaron Marcuse-Kubitza

inputs/.geoscrub/geoscrub_cleaned_unique/create.sql: Change latitudeDecimalVerbatim, longitudeDecimalVerbatim types to double precision to allow merge-joining with coordinates.latitude_deg, longitude_deg in analytical_stem_view

6105 11/09/2012 05:12 PM Aaron Marcuse-Kubitza

README.TXT: Data import: Instead of using `make schemas/rotate` and then renaming the public schema to the correct name, just rename directly to the correct name using `make schemas/rename/...`. Use new import_name to determine the import name instead of manually finding the date in the first datasource's log file name.

6104 11/09/2012 05:06 PM Aaron Marcuse-Kubitza

Added import_name, which gets the name of an import based on its log file names

6103 11/09/2012 04:50 PM Aaron Marcuse-Kubitza

README.TXT: Data import: Moved checking that imports were successful before running make_analytical_db

6102 11/09/2012 04:41 PM Aaron Marcuse-Kubitza

root Makefile: Installation: Fixed bug where schemas/install needed to happen after inputs/install because some of the public schema's views now depend on inputs

6101 11/09/2012 04:07 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: cultivatedBasis: Concatenate ''::text to geoscrub.isCultivatedReason so it will be cast to a text field both on PostgreSQL 9.1.1 (local machine), which removes any explicit cast to text when creating the view, and 9.1.6 (vegbiendev), which requires an explicit cast to text

6100 11/09/2012 03:49 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: cultivatedBasis: Use geoscrub.isCultivatedReason instead when geoscrub.isCultivated is used as the source for cultivated

6099 11/09/2012 12:53 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Use geoscrub.isCultivated when taxonoccurrence.iscultivated is not provided (joining to geoscrub on the coordinates)

6098 11/08/2012 06:38 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: Schemas: Run all schema installs and uninstalls using no_search_path=1, so that the schemas in the automatic search_path are not required for the command to run

6097 11/08/2012 06:37 PM Aaron Marcuse-Kubitza

psql_vegbien: Added $no_search_path option to turn off the automatic SET search_path directive

6096 11/08/2012 06:11 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: taxonverbatim: Added growthform for growthform based on the taxon name rather than provided with the input data's taxonoccurrence

6095 11/08/2012 06:00 PM Aaron Marcuse-Kubitza

schemas/vegbien.ERD.mwb: Fixed lines

6094 11/08/2012 05:47 PM Aaron Marcuse-Kubitza

inputs/SALVIAS/plotMetadata/: LEFT JOINed with lookup_MethodCode to create plotMetadata_

6093 11/08/2012 04:52 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: threatened_taxonlabel_view: Fixed bug where needed DISTINCT on resulting taxonlabel_id because some descendants apparently appear in multiple threatened taxonlabels' subtrees

6092 11/08/2012 04:42 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_*: Added threatened, using new threatened_taxonlabel lookup table

6091 11/08/2012 04:12 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: reference_by_shortname(): Fixed bug where need to use $-syntax to reference params in sql-language functions

6090 11/08/2012 04:07 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: threatened_taxonlabel_view: Use new reference_by_shortname()

6089 11/08/2012 03:45 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: Schemas: public: schemas/public/uninstall: Fixed bug where need to run psql_vegbien without public in the search_path because it may have already been deleted

6088 11/08/2012 03:44 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: Schemas: public: schemas/public/install: Fixed bug where need to run psql_vegbien without public in the search_path because it doesn't exist, by setting public to the empty string (deleting it)

6087 11/08/2012 03:42 PM Aaron Marcuse-Kubitza

vegbien_dest: $schemas: Don't include the , before $public if it has been set to the empty string (deleted)

6086 11/08/2012 03:27 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added reference_by_shortname(). Using this function instead of the manual query should force the query planner to evaluate this expression first, rather than complexly reordering joins to place this nested select as a filter condition.

6085 11/08/2012 03:00 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added threatened_taxonlabel derived table with generating view threatened_taxonlabel_view

6084 11/08/2012 02:48 PM Aaron Marcuse-Kubitza

Updated inputs/UNCC/Specimen/test.xml.ref inserted row count

6083 11/08/2012 01:38 PM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added threatened

6082 11/08/2012 01:21 PM Aaron Marcuse-Kubitza

inputs/VegBank/vegbank.~.clean_up.sql: Remove private columns (plot.reallatitude, reallongitude) that should not be publicly visible

6081 11/08/2012 01:13 PM Aaron Marcuse-Kubitza

inputs/CVS/Organism/map.csv: Removed now-dropped realLatitude, realLongitude

6080 11/08/2012 01:12 PM Aaron Marcuse-Kubitza

inputs/CVS/Organism/map.csv: Removed now-dropped realLatitude, realLongitude

6079 11/08/2012 01:12 PM Aaron Marcuse-Kubitza

Added inputs/CVS/Organism/postprocess.sql to drop private realLatitude, realLongitude columns

6078 11/08/2012 01:10 PM Aaron Marcuse-Kubitza

input.Makefile: Staging tables installation: Added back postprocess.sql, which is now used for one-time dropping of private columns that should not be publicly visible

6077 11/08/2012 12:47 PM Aaron Marcuse-Kubitza

input.Makefile: Maps building: %/.map.csv.last_cleanup: $(dict) canon/translate: Use new $(translate?)

6076 11/08/2012 12:45 PM Aaron Marcuse-Kubitza

input.Makefile: Maps building: %/.map.csv.last_cleanup: Added $(srcDict) as a prerequisite, so that .last_cleanup will be re-run if it changes. Added empty $(srcDict) target in case it doesn't exist.

6075 11/08/2012 12:39 PM Aaron Marcuse-Kubitza

inputs/bien_web/observation/map.csv: Omit *_index because they are placeholder columns created by the MySQL to PostgreSQL translation

6074 11/08/2012 12:37 PM Aaron Marcuse-Kubitza

input.Makefile: Maps building: %/.map.csv.last_cleanup: Fixed bug where can only canon/translate using $(srcDict) if it exists for the datasource

6073 11/08/2012 12:26 PM Aaron Marcuse-Kubitza

inputs/bien_web/observation/: Regenerated from actual bien_web.observation schema on nimoy, which has additional columns

6072 11/08/2012 12:24 PM Aaron Marcuse-Kubitza

input.Makefile: SVN: $(svnFilesGlob): Added top-level map.csv, which can be used to apply a datasource-global data dictionary to all tables

6071 11/08/2012 12:18 PM Aaron Marcuse-Kubitza

input.Makefile: Maps building: %/.map.csv.last_cleanup: Also apply any map.csv at the top level of the datasource directory. This can be used to apply a datasource-global data dictionary to all tables.

6070 11/08/2012 12:01 PM Aaron Marcuse-Kubitza

my2pg: Also remove column comments. Note that these cannot be translated by sed, because PostgreSQL only allows setting column comments in a separate statement, not inline with the column's entry in the CREATE TABLE statement, and sed can only make replacements contiguous with the input line.

6069 11/08/2012 11:28 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Removed incorrect duplicate entry for verbatimSubgenus

6068 11/08/2012 10:58 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: _taxon_family_require_std(): Fixed bug where name needed to be lowercased before checking if it ended in -aceae, to support family names that are uppercase. Note that this resulted in the family not being prepended to the TNRS input name for datasources with uppercase family names, so the next DB import will likely produce a number of unscrubbed TNRS input names which now have the uppercase family prepended.

6067 11/08/2012 10:17 AM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/tnrs.make: Fixed bug where need to reference the log file path relative to the make script itself, because otherwise the log file would go in inputs/.TNRS/logs/tnrs.make.log.sql

6066 11/08/2012 10:07 AM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/tnrs.make: Fixed bug where need to use just the basename of $0 as the log file name

6065 11/08/2012 09:51 AM Aaron Marcuse-Kubitza

Added inputs/IUCN/

6064 11/08/2012 09:51 AM Aaron Marcuse-Kubitza

input.Makefile: SVN: add: Added _src/ (when it exists). $(_svnFilesGlob): Added .url, .pdf files in _src/.

6063 11/08/2012 07:47 AM Aaron Marcuse-Kubitza

psql_vegbien: Use bash because it supports substitutions

6062 11/08/2012 07:46 AM Aaron Marcuse-Kubitza

psql_vegbien: Set the search_path to $out_schemas set by vegbien_dest. This will enable running any psql_vegbien script on a schema other than public.

6061 11/08/2012 07:07 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Changed inner joins on non-datasource taxonlabels to LEFT JOINs, to ensure that an entry is included in the analytical DB even if there was no matched taxonlabel. In theory, this shouldn't be necessary, because every taxonlabel should have a canonical taxonlabel since canon_label_id is auto-populated to the taxonlabel_id if there is no matched_label_id; there should be a taxonverbatim for every datasource and accepted taxonlabel because datasources link to taxonlabel via taxonverbatim and TNRS populates a taxonverbatim for every accepted taxonlabel; and there should be a parsed taxonlabel for every datasource taxonlabel because the mappings populate it.

6060 11/08/2012 06:56 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_stem_view: Removed join on specimenreplicate, because it isn't used in the analytical DB. Each specimen will still get an entry in analytical_*, because it gets its own location.

6059 11/08/2012 06:45 AM Aaron Marcuse-Kubitza

README.TXT: Data import: Before starting import, added step to run `make inputs/upload` and reinstall newly-uploaded datasources

6058 11/08/2012 03:22 AM Aaron Marcuse-Kubitza

README.TXT: Schema changes: Remember to update the following files with any renamings: Added mappings/verify.*.sql

6057 11/08/2012 02:21 AM Aaron Marcuse-Kubitza

README.TXT: Data import: make_analytical_db: Documented how to view progress in log file

6056 11/08/2012 02:18 AM Aaron Marcuse-Kubitza

make_analytical_db: Run all commands synchronously so the log file output doesn't become jumbled

6055 11/08/2012 02:16 AM Aaron Marcuse-Kubitza

make_analytical_db: Fixed bug where log file needed to be appended to instead of overwritten

6054 11/08/2012 02:15 AM Aaron Marcuse-Kubitza

make_analytical_db: Wrap each individual command in `set -x` to avoid echoing low-level commands such as sleep, wait

6053 11/08/2012 02:02 AM Aaron Marcuse-Kubitza

make_analytical_db: Moved log file to inputs/analytical_db/logs/make_analytical_db.log.sql so it would be synced along with the other import logs

6052 11/08/2012 01:57 AM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/tnrs.make: Output the time at which it's run, so this is included in the log file

6051 11/08/2012 01:53 AM Aaron Marcuse-Kubitza

inputs/.TNRS/tnrs/tnrs.make: Moved log file to logs/tnrs.make.log.sql so it would automatically be synced along with the other import logs

6050 11/08/2012 01:49 AM Aaron Marcuse-Kubitza

make_analytical_db: Moved log file to inputs/analytical_db/logs/make_analytical_db.log.sql so it would be synced along with the other import logs

6049 11/08/2012 01:40 AM Aaron Marcuse-Kubitza

inputs/Makefile: Import logs: $(rsyncLogs): Always download the TNRS daemon log, rather than requiring tnrs_log=1 to be specified to download it

6048 11/08/2012 01:37 AM Aaron Marcuse-Kubitza

make_analytical_db: Output the time at which it's run, so this is included in the log file

6047 11/08/2012 01:36 AM Aaron Marcuse-Kubitza

make_analytical_db: Store output in schemas/make_analytical_db.log

6046 11/08/2012 01:24 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Removed no longer used make_analytical_db(). Use bin/make_analytical_db instead.

6045 11/08/2012 01:23 AM Aaron Marcuse-Kubitza

make_analytical_db: Use new psql_verbose_vegbien

6044 11/08/2012 01:22 AM Aaron Marcuse-Kubitza

Added psql_verbose_vegbien

6043 11/08/2012 01:18 AM Aaron Marcuse-Kubitza

make_analytical_db: Use psql_script_vegbien, which contains the necessary psq options, instead of setting those options manually

6042 11/08/2012 01:15 AM Aaron Marcuse-Kubitza

make_analytical_db: Run the SQL commands directly with psql instead of using the make_analytical_db() function. This provides incremental results and avoids running all commands in one transaction, thus preventing pgAdmin from freezing when the user attempts to access a table used in analytical DB creation (because the TRUNCATE statement fully locks the table until the entire analytical DB is built).

6041 11/08/2012 12:46 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: make_analytical_db(): Added make_family_higher_plant_group()

6040 11/08/2012 12:17 AM Aaron Marcuse-Kubitza

inputs/import.stats.xls: Updated import times. Fixed input row counts and import times to include derived data, such as TNRS and geoscrub, which adds to the import time and therefore should be considered in the import's speed. (TNRS was already being included in the import time for some, but not all, imports.)

6039 11/06/2012 05:02 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_*: Added higherPlantGroup

6038 11/06/2012 04:06 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: When not in data mode, use --inserts so the INSERT statements generated can be run directly in pgAdmin, they can be directly translated to MySQL, and an empty COPY FROM statement isn't generated for every table

6037 11/06/2012 04:05 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added family_higher_plant_group lookup table, generated with make_family_higher_plant_group() using the contents of higher_plant_group_nodes

6036 11/06/2012 03:40 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added family_higher_plant_group lookup table, generated with make_family_higher_plant_group() using the contents of higher_plant_group_nodes

6035 11/06/2012 03:23 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: Always map taxonNameOrEpithet to taxonomicname, now that it's globally unique at all ranks in the datasource that provides it (NCBI)

6034 11/06/2012 03:18 PM Aaron Marcuse-Kubitza

inputs/.NCBI/nodes/create.sql: Make name_txt completely globally unique by removing all duplicates, not just duplicate genera

6033 11/06/2012 03:17 PM Aaron Marcuse-Kubitza

inputs/.NCBI/nodes/create.sql: Make name_txt (mostly) globally unique by removing several other kingdoms/superkingdoms, not just Animalia

6032 11/06/2012 02:56 PM Aaron Marcuse-Kubitza

inputs/.NCBI/nodes/create.sql: Making genus globally unique: Moved comment with kingdom name to line with DELETE, and put "delete cascades to descendants" comment on its own line

6031 11/06/2012 01:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added higher_plant_group_nodes lookup table

6030 11/06/2012 01:57 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added higher_plant_group_nodes lookup table

6029 11/06/2012 01:49 PM Aaron Marcuse-Kubitza

schemas/Makefile: DDLs: $(pg_dump): Turn off schema-only mode so that lookup table contents are included as well

6028 11/06/2012 01:47 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: Except in (raw) data mode, filter out pg_catalog.setval() statements (only lookup table contents should be preserved)

6027 11/06/2012 01:39 PM Aaron Marcuse-Kubitza

lib/PostgreSQL-MySQL.csv: Remove SELECT pg_catalog.setval() statements

6026 11/06/2012 01:27 PM Aaron Marcuse-Kubitza

lib/PostgreSQL-MySQL.csv: Remove COPY statements

6025 11/06/2012 01:18 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: $schema flag defaults to on to export only schema

6024 11/06/2012 01:08 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: Fixed bug where schema name var can't be named $schema because that would conflict with the $schema flag

6023 11/06/2012 01:03 PM Aaron Marcuse-Kubitza

root Makefile: VegBIEN DB: DB and bien user: db: Run inputs/.TNRS/install before schemas/install because public.tnrs_input_name depends on TNRS.tnrs

6022 11/06/2012 12:33 PM Aaron Marcuse-Kubitza

pg_dump_vegbien: Only include just the schema if new $schema flag is set. This causes constant lookup tables to automatically be exported for DB schemas, which was not fully supported by the $data flag because it also forced $owners to be enabled.

6021 11/06/2012 12:17 PM Aaron Marcuse-Kubitza

schemas/vegbien.sql: Added higher_plant_group enum

6020 11/06/2012 11:25 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added higherPlantGroup

6019 11/06/2012 10:40 AM Aaron Marcuse-Kubitza

schemas/vegbien.sql: analytical_*: Added isNewWorld

6018 11/06/2012 10:32 AM Aaron Marcuse-Kubitza

mappings/VegCore.csv: Added isNewWorld

6017 11/06/2012 10:21 AM Aaron Marcuse-Kubitza

inputs/newWorld/: Added geoscrub.schema.~.changes.sql to add a unique constraint on countryNameStd and change isNewWorld's type to boolean

6016 11/06/2012 09:59 AM Aaron Marcuse-Kubitza

Added inputs/newWorld/

6015 11/06/2012 09:55 AM Aaron Marcuse-Kubitza

README.TXT: Datasource setup: Moved Auto-create the map spreadsheets after Install the staging tables, because for DB-only tables, creating the map spreadsheets requires the header.csv generated by the staging tables install

6014 11/06/2012 09:48 AM Aaron Marcuse-Kubitza

input.Makefile: SVN: add: Also set _MySQL/'s svn:ignore

6013 11/06/2012 09:30 AM Aaron Marcuse-Kubitza

inputs/SALVIAS/_MySQL/salvias_plots.*.sql.make: Use new my2pg_export

6012 11/06/2012 09:27 AM Aaron Marcuse-Kubitza

inputs/.geoscrub/_MySQL/geoscrub.*.sql.make: Use new my2pg_export