sql_io.py: cleanup_table(): Handle NullValueExceptions (due to setting values to NULL in a NOT NULL column) by dropping the NOT NULL constraint
sql.py: Added drop_not_null()
sql_gen.py: is_text_col(): Also consider character varying to be a text type
csv2db: Removed no longer used errors_table_only option
README.TXT: Schema changes: Removed step to reinstall errors tables, because they are now created automatically by column-based import
csv2db: Removed no longer needed creation of errors table, because it is now created automatically by column-based import
input.Makefile: Staging tables installation: $(dbExports): Fixed bug where it would be non-empty even when the input contains no DB exports, because += adds extra whitespace. This caused sql/install to be incorrectly included as part of $(allInstalls).
db_xml.py: put_table(): Create errors table if it doesn't exist
sql_io.py: Added mk_errors_table()
inputs/Makefile: Input data: $(rsyncSrcs): Also exclude logs subdirs located at more than one level below the root, which occurs for example when a table subdir is moved into _archive/
input.Makefile: Staging tables installation: sql/install: Fixed bug where _always was part of $+, causing cat to try to cat this nonexistent file
Added inputs/SALVIAS/salvias_plots.schema.sql
Added inputs/SALVIAS/_MySQL/
input.Makefile: Staging tables installation: MySQL exports: Run all non-data-only exports through my2pg, not just schema-only exports. This supports transforming a combined schema+data export.
my2pg: Also perform data-only replacements, since default values can contain data-specific replacements. This also allows my2pg to transform a combined schema+data export.
input.Makefile: Staging tables installation: Also translate MySQL data to PostgreSQL
Added my2pg.data
input.Makefile: Staging tables installation: Place MySQL exports in separate _MySQL/ subdir so they don't clutter up the main dir, which will contain PostgreSQL translations
Added my2pg
input.Makefile: Staging tables installation: DB exports: Concatenate all exports together, with schemas first, so that any config options which were applied only in the schema export will remain active when the data is imported. Changed `%.pg.sql: .my.sql` to `.schema.sql: %.schema.my.sql` so there doesn't need to be a .pg suffix for PostgreSQL schemas and only the schema gets translated.
input.Makefile: Staging tables installation: $(dbExports): Don't consider MySQL DB exports as part of the DB exports that get installed, because they are not directly installable
input.Makefile: Staging tables installation: Added `%.pg.sql: %.my.sql` to translate MySQL DB schemas to PostgreSQL
inputs/SALVIAS/_src/: Added salvias_plots.sql.url to provide a link to where salvias_plots.sql was exported from (it was not a raw file given to us by the data provider)
Added cc_tty
inputs/input.Makefile: `%: %.make`: Don't automatically redirect stderr to a log file, because some .make scripts need to display password prompts, etc. on the TTY and output them to stderr instead of /dev/tty
inputs/REMIB/nodes.make: Fixed bin dir path for new subdir layout
inputs/SpeciesLink/tapir.make: Write log messages to a log file ($0.log) instead of to stderr, because the verbose log messages should not fill up stderr. To view the progress, you should instead tail the created log file.
inputs/REMIB/nodes.make: Updated path to node exports to use new subdir layout (in Specimen subdir, and without .specimens suffix)
inputs/REMIB/nodes.make: Fixed lib dir path in sys.path.append() for new subdir layout
inputs/REMIB/nodes.make: Write log messages to a log file ($0.log) instead of to sys.stderr, because the verbose log messages should not fill up stderr. To view the progress, you should instead tail the created log file.
input.Makefile: Add the bin folder to the PATH so .make scripts can easily use programs in it
input.Makefile: Staging tables installation: Support installing a DB export directly into the staging schema, without needing to first export it as CSVs
inputs/SALVIAS/: Added _src/ subdir to store original DB export (before re-export in a PostgreSQL-compatible form)
input.Makefile: `%: %.make`: Only remake if doesn't exist. This prevents unintentional remaking when the make script is newly checked out from svn (which sets the mod time to now) but the output is synced externally.
input.Makefile: `%: .make`: Removed no longer applicable comment, which applied when there were two separate `: %.make`-related rules
input.Makefile: Use $(inDatasrc) wherever its value was used
input.Makefile: Added $(inDatasrc)
sql_io.py: cleanup_table(): Only clean up text columns, to support staging tables with other column types
sql_gen.py: Added is_text_col()
sql_io.py: cleanup_table(): Add table to each column so its type can later be determined from the DB
inputs/NY/verify/specimens.ref: Regenerated from specimens.ref.sql. The counts have changed slightly because this is derived directly from the NY CSV file, rather than from the nybg_raw BIEN2 staging table.
inputs/NY/verify/specimens.ref.sql: Retrofitted to use PostgreSQL instead of MySQL syntax, since this now runs on the PostgreSQL staging tables
input.Makefile: Verification of import: Added `%.ref: %.ref.sql` rule to make datasource's summary statistics from its staging tables. (This was previously run on a MySQL installation of the datasource, and thus limited to MySQL inputs, but we are now able to use the staging tables for this.)
input.Makefile: Verification of import: $(verify): Factored psql command with output format settings into separate $(psqlExport) var
schemas/vegbien.sql: analytical_db_view: Switched join order of location and party (datasource) tables, to facilitate using a nested loop join to fill in the datasource names
schemas/vegbien.sql: party: Added party_datasource index on just the organizationname to facilitate querying just the datasources
schemas/vegbien.sql: make_analytical_db(): Removed explicit schema reference so that the function can be redirected to use the current (rotated) schema using the search_path
schemas/Makefile: Removed no longer needed analytical_db, which has been replaced by bin/make_analytical_db
README.TXT: After a new import: Use bin/make_analytical_db instead of `make schemas/analytical_db`, and run it asynchronously because it takes a long time
Added make_analytical_db
schemas/Makefile: Analytical DB: analytical_db: Time the creation of the analytical DB
README.TXT: After a new import: Added command to make the analytical DB
schemas/Makefile: Added analytical_db target
schemas/vegbien.sql: Added make_analytical_db() and helper view analytical_db_view. Note that adding a view which depends on other tables will cause those tables to be reordered in dependency order to appear before the view, causing the svn diff to change completely even though the DB structure has only been added to.
schemas/vegbien.sql: Removed OIDs from tables because we don't use them (tables have primary keys instead)
inputs/import.stats.xls: Updated with stats from latest import. This now includes CTFS.TaxonOccurrence (presence-only observations), FIA (11 million rows!), and Madidi.Organism. The addition of FIA almost doubles the # of rows to 26 million and increases the import time from 9.5 to 11.5 hours.
sql_io.py: null_strs: Added 'UNKNOWN'
Added inputs/FIA/
inputs/: Renamed subfolders to VegCSV names, using the steps at <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV_subfolders#Rename-subfolders-to-VegCSV-names>
inputs/Madidi/1.organisms/map.csv: Mapped columns
inputs/Madidi/0.plots/map.csv: Remapped DMS Latitude/Longitude to verbatimLatitude/verbatimLongitude, since this is not the decimalLatitude/decimalLongitude
input.Makefile: Testing: %-ok: Rename the test output to the accepted test output instead of copying it, because outputs of successful (including newly accepted) tests should be removed to reduce clutter (as $(runTest) does)
mappings/Veg+-VegCore.csv: Remapped CTFS QuadratID to subplot rather than subplotID, because it's only unique within the parent plot, not globally unique, in CTFS
inputs/import.stats.xls: Updated with stats from latest import. This now includes the core CTFS tables.
Added inputs/VegBank/ with DB export
input.Makefile: General targets: `%: %.make`: Don't always remake the target whenever it's visited, as other targets may depend on this file and it should not be remade whenever they are visited
input.Makefile: General targets: `%: %.make`: Changed log file suffix to .log, because this log does not necessarily contain SQL statements
input.Makefile: General targets: `%: %.make`: Time the creating command
input.Makefile: General targets: Removed duplicate `%: %.make` rule
inputs/CTFS/TaxonOccurrence/map.csv: Documented that InfraSpecificLevel is unused
mappings/Veg+-VegCore.csv: Mapped speciesInvID
mappings/Veg+.terms.csv: Added speciesInvID
mappings/VegCore-VegBIEN.csv: Mapped taxonOccurrenceID
mappings/Veg+.terms.csv: Added taxonOccurrenceID
inputs/CTFS/: Added TaxonOccurrence/ and its joined tables
inputs/CTFS/_archive/Organism.VegX/README.TXT: Added calculation of StemObservation rows distribution for each plot, which indicates that the bci plot actually contains 90% of the StemObservation rows. This brings the size inflation of VegX down to ~6x.
inputs/CTFS/_archive/Organism.VegX/: Added README.TXT describing that this VegX export includes only one of 157 CTFS plots. This is important, because it indicates that VegX creates a ~1000x (!) increase in storage size (613.6 MB for bci.sql with 157 plots vs. 3.78 GB for VegX_CTFS_row_*.xml with 1 plot, assuming roughly equal #s of stems per plot).
inputs/CTFS/StemObservation/map.csv: Remapped StemID to authorStemCode since it's only unique within the parent organism (Tree), not a globally unique ID as is required for stemID
mappings/VegCore-VegBIEN.csv: Mapped authorStemCode
mappings/Veg+.terms.csv: Added authorStemCode
mappings/VegCore-VegBIEN.csv: Mapped stemID
inputs/SALVIAS/2.stems/map.csv: Mapped stem_id
README.TXT: Datasource setup: Added steps to install any MySQL export
mappings/Veg+-VegCore.csv: Mapped stem_id
repl: Support treating all patterns as plain text (non-regexp)
mappings/Veg+.terms.csv: Added stem_id
mappings/Veg+.terms.csv: Added stemID
mappings/Veg+-VegCore.csv: Mapped speciesName, subSpeciesName
mappings/Veg+.terms.csv: Added CTFS taxonomic name columns
mappings/Veg+.terms.csv: Removed comments not applicable to the term itself
Inputs with multiple tables: Added explicit import_order.txt files, so that sort orders can later be removed from the subdir names
inputs/CTFS/: Added StemObservation/ and tables it is joined from
mappings/Veg+-VegCore.csv: Mapped stemTag
mappings/Veg+.terms.csv: Added stemTag
mappings/Veg+-VegCore.csv: Mapped DBH
mappings/Veg+.terms.csv: Added DBH
input.Makefile: Maps building: Added comment that you cannot make a subdir separately from the entire datasource dir
