Project

General

Profile

Statistics
| Revision:

# Date Author Comment
10223 07/10/2013 04:33 PM Aaron Marcuse-Kubitza

/Makefile: mysql-Linux: also install mysql-workbench, for use in modifying the VegCore ERD. (note that it has to be modified on Linux, because the Linux and Mac versions of MySQL Workbench position the lines differently.)

10222 07/10/2013 04:10 PM Aaron Marcuse-Kubitza

/README.TXT: Maintenance: to backup files not in Time Machine: removed VirtualBox VMs because they are now in Time Machine, and do not need to be backed up separately

10221 07/10/2013 04:08 PM Aaron Marcuse-Kubitza

/README.TXT: Maintenance: to synchronize a Mac's settings with my testing machine's: added steps to upload just the VirtualBox VMs

10220 07/10/2013 04:02 PM Aaron Marcuse-Kubitza

bugfix: /README.TXT: Maintenance: to synchronize a Mac's settings with my testing machine's: added overwrite=1 so that old snapshots, etc. are also deleted

10219 07/10/2013 04:01 PM Aaron Marcuse-Kubitza

/README.TXT: Maintenance: to synchronize a Mac's settings with my testing machine's: use better bin/sync_upload instead of put

10218 07/10/2013 03:59 PM Aaron Marcuse-Kubitza

/README.TXT: Maintenance: to synchronize a Mac's settings with my testing machine's: removed no longer needed inplace=1, because the VirtualBox VMs now all use a snapshot covering the full disk, so that the full disk is not altered (removing the need to optimize backing up a large file) and just the diff files need to be backed up each time

10217 07/10/2013 03:41 PM Aaron Marcuse-Kubitza

bugfix: lib/sh/util.sh: sed: must use alias instead of function because function causes segfault in redir() subshell when used with make.sh make() filter (may be bug in bash?). this involves translating `unset LANG` to `env LANG=` (`env -u` to unset a var isn't supported on Mac, but fortunately sed treats LANG="" the same as unset LANG).

10216 07/10/2013 03:06 PM Aaron Marcuse-Kubitza

archived planning/goals/BIEN3_derived_data_products.docx and replaced with symlink to new BIEN_3_derived_data_products_NormalizedDB_only.docx

10215 07/10/2013 02:59 PM Aaron Marcuse-Kubitza

added planning/goals/BIEN_3_derived_data_products_NormalizedDB_only.docx from Brad's e-mail

10214 07/10/2013 02:42 PM Aaron Marcuse-Kubitza

bugfix: lib/sh/util.sh: sed: unset LANG to avoid "illegal byte sequence" errors on invalid UTF-8 for LANG=*.UTF-8. these occur e.g. with MySQL data that is in Latin-1.

10213 07/10/2013 02:36 PM Aaron Marcuse-Kubitza

lib/sh/util.sh: sed: use function instead of alias so that env can be set up before calling sed

10212 07/10/2013 02:15 PM Aaron Marcuse-Kubitza

planning/workflow/bien3_architecture.pptx: updated to Martha's revised version from 2013-7-3

10211 07/10/2013 04:13 AM Aaron Marcuse-Kubitza

lib/runscripts/table.run: map_table(): run map_table repeatedly until no more renames are made: added command to do this

10210 07/10/2013 03:53 AM Aaron Marcuse-Kubitza

lib/runscripts/table.run: map_table(): documented that collisions may prevent all renames from being made at once. if this is the case, map_table must be run repeatedly until no more renames are made. collisions may result if the staging table gets messed up (e.g. due to missing input columns in map.csv).

10209 07/10/2013 02:32 AM Aaron Marcuse-Kubitza

inputs/*/*/map.csv for CSV tables with a row_num column: added missing row_num entry, which is needed by the staging table column renaming to make the order of the map.csv columns match the order in the staging table

10208 07/10/2013 02:27 AM Aaron Marcuse-Kubitza

bugfix: inputs/*/Source/map.csv: added missing row_num entry, which is needed by the staging table column renaming to make the order of the map.csv columns match the order in the staging table. the staging table column renaming is now used by all Source tables.

10207 07/10/2013 02:18 AM Aaron Marcuse-Kubitza

bugfix: populated empty inputs/IUCN/European_Red_List_Plants/header.csv

10206 07/10/2013 02:17 AM Aaron Marcuse-Kubitza

inputs/CTFS/*/map.csv: added *.src.row_num from joined tables so that the map.csv input columns would match the staging table. this is needed for the staging table column renaming, which is positional rather than name-based to work with any existing column name.

10205 07/10/2013 01:50 AM Aaron Marcuse-Kubitza

bugfix: inputs/input.Makefile: map.csv and derived files: use $(tables) instead of $(importTables) when making them so that the mappings of those tables are still kept up-to-date even though they are marked _no_import (and not imported into the main DB)

10204 07/10/2013 01:46 AM Aaron Marcuse-Kubitza

inputs/CTFS/*/test.xml.ref: regenerated. these got out of date because even though these tables are included in import_order.txt, they are marked as _no_import, which prevents map.csvs and derived files from being kept up-to-date.

10203 07/10/2013 01:24 AM Aaron Marcuse-Kubitza

bugfix: inputs/CTFS/*/VegBIEN.csv: regenerated from map.csv. they may have gotten out of date because they are marked as _no_import, even though they are in import_order.txt.

10202 07/09/2013 05:33 PM Aaron Marcuse-Kubitza

bugfix: added missing inputs/MO/Specimen/header.csv

10201 07/09/2013 05:32 PM Aaron Marcuse-Kubitza

bugfix: added missing inputs/QFA/Specimen/header.csv

10200 07/09/2013 05:26 PM Aaron Marcuse-Kubitza

bugfix: inputs/TEX/Specimen/header.csv: generated from staging table (was empty previously)

10199 07/09/2013 04:44 PM Aaron Marcuse-Kubitza

bugfix: inputs/*/Source/map.csv: added missing row_num entry, which is needed by the staging table column renaming to make the order of the map.csv columns match the order in the staging table. the staging table column renaming is now used by all Source tables.

10198 07/09/2013 04:42 PM Aaron Marcuse-Kubitza

added inputs/newWorld/iso_code_gadm/header.csv

10197 07/09/2013 04:31 PM Aaron Marcuse-Kubitza

added inputs/analytical_db/table.run

10196 07/09/2013 02:59 PM Aaron Marcuse-Kubitza

bugfix: inputs/VASCAN/Taxon/map.csv: added missing row_num column added by bin/csv2db

10195 07/09/2013 02:50 PM Aaron Marcuse-Kubitza

lib/sql_io.py: cleanup_table(): added assertion that the table exists, so that if it doesn't, the error will occur as part of an assertion rather than as part of the util.table_nulls_mapped__get() call, which might confusingly lead users to believe that this is a bug in util.table_nulls_mapped__get() when in fact the problem is that the table is not installed

10194 07/09/2013 02:30 AM Aaron Marcuse-Kubitza

fix: inputs/import.stats.xls: removed spurious diff comment on total time, which only applied to the previous import

10193 07/09/2013 02:28 AM Aaron Marcuse-Kubitza

inputs/import.stats.xls: reformatted times longer than one day as a # of days instead of hours, for clarity. the days format is chosen automatically when the # hours exceeds one day.

10192 07/09/2013 01:04 AM Aaron Marcuse-Kubitza

bugfix: inputs/*/Source/: added missing ./run, which creates the new-style staging tables with the metadata fields as part of the table. this is needed now that these subdirs use installed staging tables instead of metadata-only map.csvs.

10191 07/09/2013 12:56 AM Aaron Marcuse-Kubitza

bin/map: removed no longer used support for map.csv input column prefixes (expand out the prefixes instead). this used to be used by SpeciesLink to use just one mapping for a single term with multiple DwC namespaces, but was replaced with an explicit, ordered rather than implicit, unordered /_alt-ing together of the terms.

10190 07/08/2013 11:47 PM Aaron Marcuse-Kubitza

bin/map: removed no longer accurate comment that this is case- and punctuation-insensitive, since the case- and punctuation-insensitivity is now instead handled by map.csv preprocessing scripts before the mappings are even provided to bin/map

10189 07/06/2013 07:29 PM Aaron Marcuse-Kubitza

inputs/.herbaria/: switched to new-style import, which renamed the columns to the VegCore names. this is done using the commands at wiki.vegpath.org/2013-06-27_conference_call#To-do-for-Aaron > "run the following for each datasource".

10188 07/06/2013 07:21 PM Aaron Marcuse-Kubitza

lib/sql_io.py: cleanup_table(): don't run the slow ALTER TABLE statement again if the table has already been cleaned up. documented that it is idempotent (and actually was before this change as well).

10187 07/06/2013 07:19 PM Aaron Marcuse-Kubitza

lib/sql_io.py: added table_nulls_mapped__set(), "__get() wrappers around the corresponding util schema functions

10186 07/06/2013 07:18 PM Aaron Marcuse-Kubitza

lib/sql_gen.py: added table2regclass_text()

10185 07/06/2013 07:07 PM Aaron Marcuse-Kubitza

schemas/util.sql: added table_nulls_mapped__get(), which gets whether a table's NULL-equivalent strings have been replaced with NULL

10184 07/06/2013 07:06 PM Aaron Marcuse-Kubitza

schemas/util.sql: added table_flag__get(), which gets whether a status flag is set by the presence of a table constraint

10183 07/06/2013 06:56 PM Aaron Marcuse-Kubitza

schemas/util.sql: added table_nulls_mapped__set(), which sets that a table's NULL-equivalent strings have been replaced with NULL

10182 07/06/2013 06:54 PM Aaron Marcuse-Kubitza

schemas/util.sql: added table_flag__set(), which stores a status flag by the presence of a table constraint

10181 07/06/2013 06:52 PM Aaron Marcuse-Kubitza

schemas/util.sql: create_if_not_exists(): also ignore duplicate_object exceptions, thrown when trying to add a duplicate constraint

10180 07/06/2013 06:00 PM Aaron Marcuse-Kubitza

inputs/input.Makefile: %/postprocess: removed no longer used invocation of $*/import (precursor to the runscripts used in FIA)

10179 07/06/2013 05:39 PM Aaron Marcuse-Kubitza

inputs/*/: added table.run for use by the table subdirs in new-style import. datasources without table subdirs do not need this.

10178 07/06/2013 05:35 PM Aaron Marcuse-Kubitza

inputs/*/: added top-level Makefile which includes inputs/input.Makefile, so that make can be run directly on the datasrc dir without needing to specify `--makefile=../input.Makefile` (see input.Makefile $(selfMake))

10177 07/06/2013 05:17 PM Aaron Marcuse-Kubitza

inputs/*/: added top-level Makefile which includes inputs/input.Makefile, so that make can be run directly on the datasrc dir without needing to specify `--makefile=../input.Makefile` (see input.Makefile $(selfMake))

10176 07/06/2013 05:05 PM Aaron Marcuse-Kubitza

added inputs/test_taxonomic_names/Taxon/header.csv

10175 07/06/2013 04:02 PM Aaron Marcuse-Kubitza

web/links/index.htm: updated to Firefox bookmarks. removed dead favicons. PostgreSQL: added bookmarks about triggers.

10174 07/06/2013 03:55 PM Aaron Marcuse-Kubitza

bugfix: inputs/input.Makefile: %/VegBIEN.csv: for new-style datasources, use a symlink to mappings/VegCore-VegBIEN.csv directly instead of prefiltering VegCore-VegBIEN.csv to include only the columns in map.csv. prefiltering used to be performed as part of mapping the map.csv VegCore output terms to VegBIEN using bin/join, but is no longer needed because the staging table columns are now VegCore terms. instead, the full VegCore-VegBIEN.csv is needed so that derived columns added in stage I or II validations are detected by bin/map (rather than just the original source columns in map.csv).

10173 07/06/2013 03:37 PM Aaron Marcuse-Kubitza

mappings/VegCore-VegBIEN.csv: cultivated, oldGrowth: use just cultivated if it's provided, rather than /_alt-ing it back with oldGrowth (which it was generated from)

10172 07/06/2013 03:30 PM Aaron Marcuse-Kubitza

bugfix: mappings/VegCore-VegBIEN.csv: fixed priority of cultivated and oldGrowth so cultivated is used first if it's available

10171 07/06/2013 02:41 PM Aaron Marcuse-Kubitza

bugfix: lib/runscripts/table.run: need to run remake_VegBIEN_mappings after mk_derived rather than before so the derived cols will be included in the automated test result

10170 07/06/2013 02:26 PM Aaron Marcuse-Kubitza

bugfix: inputs/*/Source/: use installed staging table (with blank-line data.csv) in order to also work with new-style import. this also fixes a benign diff between the by-row and by-col test outputs, where row-based import would not import the Source/ entries because there was not at least one row in the input. note that in order to ensure that all datasources are properly run, you need to check `svn st|sort` against the datasource schema names to see if any are missing.

10169 07/06/2013 02:22 PM Aaron Marcuse-Kubitza

inputs/*/logs: updated svn:ignore

10168 07/06/2013 02:22 PM Aaron Marcuse-Kubitza

inputs/*/*/logs: updated svn:ignore

10167 07/06/2013 01:45 PM Aaron Marcuse-Kubitza

bugfix: inputs/input.Makefile: SVN: add: don't add subdirs for datasources marked _no_import (e.g. datasources which only have an inputs/ dir to be listed in VegPath)

10166 07/06/2013 11:29 AM Aaron Marcuse-Kubitza

bugfix: inputs/*/Source/data.csv for new-style datasources: need to include a blank row (plus a blank header) so that the metadata values are imported at least once instead of zero times, now that there is an installed staging table that will be iterated over. the blank row did not used to be necessary, because db_xml.put_table() has a special case for metadata-only tables with no installed table, which avoids iterating over the table's rows.

10165 07/03/2013 10:48 PM Aaron Marcuse-Kubitza

lib/sql_io.py: put_table() (column-based import): complexity note: clarified that INSERT RETURNING throws an error on duplicate instead of returning the existing row. added blank line after ΒΆ for readability.

10164 07/03/2013 10:44 PM Aaron Marcuse-Kubitza

lib/sql_io.py: put_table() (column-based import): warning about triggers populating unique constraint-covered columns: corrected limitation to include only the unique constraint used to do the DISTINCT ON, since other unique constraints are not affected by column-based import. note that the primary key will normally not be the DISTINCT ON constraint, so trigger-populated natural keys are supported unless the input table contains duplicate rows for some generated keys.

10163 07/03/2013 10:20 PM Aaron Marcuse-Kubitza

inputs/*/Source/ for new-style datasources: use an actual staging table instead of a metadata-only table, so that metadata values can be stored in the staging table instead of the map.csv (as will be required by new-style import)

10162 07/03/2013 08:21 PM Aaron Marcuse-Kubitza

inputs/input.Makefile: SVN: $(svnFilesGlob): added data.csv, used to store versioned data (such as the empty data.csv used by Source/ tables which have their metadata in the map table instead)

10161 07/03/2013 07:45 PM Aaron Marcuse-Kubitza

schemas/util.sql: type_qual(), type_qual_name(): added comments to distinguish these similarly-named functions, one of which gets a type qualifier and the other of which gets a qualified name (not the name of a type qualifier, which one might otherwise assume)

10160 07/03/2013 07:39 PM Aaron Marcuse-Kubitza

schemas/util.sql: typeof(): support expressions that are not relative to a table (which do not have a table_ param). note that this requires removing the STRICT qualifier, so that NULL expressions will now produce an error instead of passing through as NULL.

10159 07/03/2013 07:10 PM Aaron Marcuse-Kubitza

schemas/VegCore/VegCore.ERD.mwb: relationships legend: removed inheritance of base_class from record, so that the IS-A label would not confusingly appear to apply to the record connector stub instead of to the solid line between base_class and derived_class

10158 07/03/2013 06:51 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: col_names(): need to exclude dropped columns (which remain included in the pg_attribute table until the next tuple rewrite), by filtering on `NOT attisdropped`. lib/sql.py table_col_names() is not affected by this because it is able to access the column names from the DB driver directly, after performing `SELECT * FROM table LIMIT 0`.

10157 07/03/2013 06:38 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_names_with_metadata(): don't delete the metadata entries from the map table, because they are now added before the renames take place, so that the renames can simply be performed on the constant columns themselves. this does, however, require that the metadata entries are always listed last in the map.csv (which is currently the case).

10156 07/03/2013 05:56 PM Aaron Marcuse-Kubitza

lib/runscripts/table.run: map_table(): store the map table in the datasource schema, so that it can easily be referred to when using the staging tables. this also allows it to be found more easily when debugging its contents.

10155 07/03/2013 05:26 PM Aaron Marcuse-Kubitza

lib/sh/db.sh: psql(): hide the verbose CONTEXT information that is output with each NOTICE by setting the VERBOSITY psql var to terse (postgresql.1045698.n5.nabble.com/Quiet-quot-CONTEXT-quot-td1906036.html#a1906037)

10154 07/03/2013 05:15 PM Aaron Marcuse-Kubitza

*{.sh,run}: use new log-() instead of log+() with a negative #

10153 07/03/2013 05:14 PM Aaron Marcuse-Kubitza

lib/sh/util.sh: added log-() because it's non-obvious that you would otherwise have to invoke log+() with a negative #

10152 07/03/2013 05:00 PM Aaron Marcuse-Kubitza

schemas/util.sql: reset_map_table(): drop the table and recreate it instead of just creating it if it doesn't exist, so that any change to the util.map table is propagated to persistent map tables whenever they are reloaded from the map.csv

10151 07/03/2013 05:00 PM Aaron Marcuse-Kubitza

lib/runscripts/table.run: map_table(): create the map table as a persistent table in the temp schema, so that its contents can be viewed for debugging

10150 07/03/2013 04:50 PM Aaron Marcuse-Kubitza

schemas/util.sql: added drop_table()

10149 07/03/2013 04:39 PM Aaron Marcuse-Kubitza

schemas/util.sql: set_col_names(): don't perform rename if the name is not changing, to avoid cluttering the debug output with unnecessary queries

10148 07/03/2013 04:21 PM Aaron Marcuse-Kubitza

lib/runscripts/table.run: use new util.set_col_names_with_metadata() instead of util.set_col_names() so that metadata values (beginning with : ) are automatically mapped to constant columns rather than needing to add a mk_const_col() call to postprocess.sql for each of them. there are a lot of metadata value entries, especially in the Source/ tables for each datasource, so this will save time in translating the datasources to new-style import. note that this requires disabling the map_filter_insert trigger on the map table to prevent it from filtering out the metadata entries before util.set_col_names_with_metadata() can use them.

10147 07/03/2013 03:55 PM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: set_col_names_with_metadata(): need `util.` before mk_const_col(). "to", "from" need to be referenced from row_. substring() needs to start from 2 rather than 1 because PostgreSQL string indexes are 1-based.

10146 07/03/2013 03:05 PM Aaron Marcuse-Kubitza

schemas/util.sql: try_create(), create_if_not_exists(): use eval() so the executed statement will be echoed for debugging

10145 07/03/2013 02:58 PM Aaron Marcuse-Kubitza

schemas/util.sql: added set_col_names_with_metadata()

10144 07/02/2013 05:42 PM Aaron Marcuse-Kubitza

bugfix: lib/sh/sync.sh: upload(): paths: don't dereference the path itself if it's a symlink; instead canonicalize just its parent dir. this allows syncing a specific file which is a symlink, rather than syncing the symlink's target.

10143 07/02/2013 05:40 PM Aaron Marcuse-Kubitza

lib/sh/util.sh: added canon_dir_rel_path(), which canonicalizes just the parent dir if the path is a symlink, to leave the symlink itself untouched

10142 07/02/2013 05:08 PM Aaron Marcuse-Kubitza

planning/workflow/validation/: archived BIEN2 validations documents which have been superseded by planning/goals/BIEN3_derived_data_products.docx, to avoid confusion

10141 07/02/2013 04:45 PM Aaron Marcuse-Kubitza

planning/workflow/bien3_architecture.pptx: updated with clarifications made in today's conference call

10140 07/02/2013 02:31 PM Aaron Marcuse-Kubitza

bugfix: bin/map: in_is_db: inline metadata value columns (used by new-style import) so that they can be compared by value in XML simplifying functions (lib/xml_func.py)

10139 07/02/2013 02:29 PM Aaron Marcuse-Kubitza

lib/sql.py: added col_default_value(), col_is_constant(), which interface with corresponding util-schema functions

10138 07/02/2013 02:28 PM Aaron Marcuse-Kubitza

lib/sql_gen.py: added col2col_ref() for interfacing with for SQL functions that take a util.col_ref

10137 07/02/2013 12:57 PM Aaron Marcuse-Kubitza

schemas/util.sql: added is_constant(col_ref), for checking if a column has been marked "constant"

10136 07/02/2013 12:54 PM Aaron Marcuse-Kubitza

schemas/util.sql: added col_comment()

10135 07/02/2013 12:53 PM Aaron Marcuse-Kubitza

schemas/util.sql: mk_const_col(): add column comment "constant" to mark column as inlinable (needed by some mappings to have a literal value to compare)

10134 07/02/2013 12:03 PM Aaron Marcuse-Kubitza

schemas/util.sql: added col_default_value(), which evaluates the col_default_sql() expression

10133 07/02/2013 11:51 AM Aaron Marcuse-Kubitza

schemas/util.sql: added eval_expr_passthru() (passes NULL SQL through)

10132 07/02/2013 11:45 AM Aaron Marcuse-Kubitza

bugfix: schemas/util.sql: eval_expr(): need to pass ret_type_null to eval2val()

10131 07/02/2013 11:42 AM Aaron Marcuse-Kubitza

schemas/util.sql: added eval_expr() (does not require `SELECT ` before expr)

10130 07/02/2013 11:33 AM Aaron Marcuse-Kubitza

schemas/util.sql: added col_default_sql()

10129 07/02/2013 11:26 AM Aaron Marcuse-Kubitza

schemas/util.sql: eval(text, anyelement): added default polymorphic type text (can't be unknown because this would cause a "could not determine polymorphic type because input has type "unknown"" error). renamed to eval2val() to avoid overloading conflicts with eval(text) when no polymorphic type param is specified.

10128 07/02/2013 11:15 AM Aaron Marcuse-Kubitza

schemas/util.sql: added value-returning eval()

10127 07/02/2013 11:02 AM Aaron Marcuse-Kubitza

bugfix: lib/common.Makefile: $(asAdmin): need to use _postgres instead on Mac for OS X 10.8 Mountain Lion

10126 07/02/2013 11:01 AM Aaron Marcuse-Kubitza

bugfix: *Makefile: $(asAdmin) invocations of Postgres commands: need to set DB user to postgres so that it won't default to the system user _postgres

10125 07/02/2013 10:57 AM Aaron Marcuse-Kubitza

*Makefile: removed $(psqlOpts), $(psqlAsAdmin), which are now set by lib/common.Makefile

10124 07/02/2013 10:57 AM Aaron Marcuse-Kubitza

lib/common.Makefile: added $(psqlOpts), $(psqlAsAdmin)