Activity
From 01/23/2014 to 02/21/2014
02/21/2014
02/20/2014
- 07:18 PM Revision 12322: fix: schemas/vegbien.sql: _traits_07_trait_value_and_units, _traits_08_taxonname_trait_and_value_for_first_5000_records: removed LIMIT to match the input queries
- 07:12 PM Revision 12321: schemas/util.sql: materialize_query(): ANALYZE the created table so that it has the correct estimated rowcount
- 07:08 PM Revision 12320: schemas/util.sql: added analyze_()
- 06:49 PM Revision 12319: schemas/vegbien.sql: remake_diff_tables(): updated bien2_traits runtime (45 min) to include the now-properly-renamed views 6-8
- 06:14 PM Revision 12318: schemas/vegbien.sql: remake_diff_tables(): documented runtime for bien2_traits (30 min)
- 05:52 PM Revision 12317: fix: schemas/vegbien.sql: renamed output views to match input views
- 04:52 PM Revision 12316: schemas/vegbien.sql: _traits_01_count_records (output side): documented runtime (4 min)
- 04:44 PM Task #869 (New): enable schema changes to be made on vegbiendev rather than the development machine
- h3. fix
allow refactoring the live public schema the same way as the live TNRS cache:
# rename constant tables ... - 04:33 PM Revision 12315: bugfix: schemas/vegbien.sql: source_by_shortname(): resolved ambiguous column references by renaming PL/pgSQL vars and adding table qualifiers
- 04:28 PM Revision 12314: bugfix: schemas/vegbien.sql: source_by_shortname(): changed from STABLE to VOLATILE because the search_path can only be set in VOLATILE functions
- 04:19 PM Revision 12313: bugfix: schemas/vegbien.sql: source_by_shortname(): can't be declared STRICT because it now has a 2nd param (schema_anchor) which is always NULL
- 08:42 AM Revision 12312: bugfix: schemas/vegbien.sql: remake_diff_tables(): can't raise exception at end if had errors, because this would roll back the transaction. instead use new util.raise_error_notice().
- 08:41 AM Revision 12311: schemas/util.sql: added raise_error_notice()
02/19/2014
02/18/2014
- 07:16 AM Revision 12309: schemas/vegbien.sql: added remake_diff_tables()
- 05:37 AM Revision 12308: bugfix: schemas/util.sql: diff(text, text): also need to include only different rows when comparing single-row tables
02/17/2014
- 09:06 PM Revision 12307: schemas/vegbien.sql: added validation_views()
- 08:59 PM Revision 12306: schemas/util.sql: show_views_like(): added predictable ordering
- 08:54 PM Revision 12305: schemas/util.sql: added show_views_like()
- 08:27 PM Revision 12304: schemas/util.sql: added schema_oid()
- 06:45 PM Revision 12303: schemas/util.sql: remake_diff_table(): add comment on the diff table showing how to view it in human-readable form. (the table unfortunately cannot be *materialized* in human-readable form, because this would create column name collisions between the two sides.)
- 06:38 PM Revision 12302: schemas/util.sql: added set_comment()
- 06:25 PM Revision 12301: schemas/util.sql: added eval2records()
- 05:48 PM Revision 12300: fix: schemas/util.sql: diff(regclass, regclass): display single row tables side-by-side
- 05:46 PM Revision 12299: schemas/util.sql: diff(text, text): implemented the single_row feature
- 05:40 PM Revision 12298: schemas/util.sql: diff(text, text): added single_row param (not implemented yet, to make reading the svn diff easier). to view this svn diff, it is helpful to use WinMerge, which offers moved block detection (/README.TXT > WinMerge setup).
- 04:54 PM Revision 12297: fix: schemas/util.sql: remake_diff_table(): util.diff() call: include explicit types on arguments to ensure that the correct diff() variant is used
- 04:32 PM Revision 12296: schemas/util.sql: added has_single_row()
- 04:08 PM Revision 12295: schemas/vegbien.sql: remake_diff_table(): prepend ~ to the type table so that it sorts at the end, away from the main tables
- 03:56 PM Revision 12294: bugfix: schemas/vegbien.sql: remake_diff_table(): can't have in_table/out_table inherit from *each other*, because inheritance also causes the rows of the parent table to be included in the child table. instead, they need to inherit from a common, empty table, as implemented by the new util.remake_diff_table().
- 03:55 PM Revision 12293: schemas/util.sql: added remake_diff_table()
- 03:51 PM Revision 12292: schemas/util.sql: drop_*(): added force param to use CASCADE mode
- 03:28 PM Revision 12291: fix: schemas/util.sql: diff(regclass, regclass): usage: col_type_null must actually be a shared base type of the tables
- 03:26 PM Revision 12290: fix: schemas/util.sql: diff(regclass, regclass): usage: text arguments must be cast to regclass to bind the right variant of diff()
- 03:24 PM Revision 12289: fix: schemas/util.sql: diff(regclass, regclass): renamed params to *_table because these actually can't be views, since views don't support inheritance
- 03:03 PM Revision 12288: schemas/util.sql: added copy_struct()
- 02:06 PM Revision 12287: schemas/vegbien.sql: added remake_diff_table()
- 01:58 PM Revision 12286: bugfix: inputs/bien2_traits/validations.sql: _traits_01_count_records: changed column names to match public_validations._traits_01_count_records
- 01:54 PM Revision 12285: schemas/util.sql: added inherit()
- 01:46 PM Revision 12284: bugfix: schemas/util.sql: diff(): need to explicitly cast each side to the return type because this does not happen automatically even when an implicit cast is available
- 01:32 PM Revision 12283: schemas/util.sql: renamed diff_views(), diff_any() to diff(...) because they are overloads of the same operation
- 01:22 PM Revision 12282: schemas/util.sql: diff_any(): documented how to run EXPLAIN on the FULL JOIN query
- 01:19 PM Revision 12281: schemas/util.sql: removed non-useful diff_queries() (it is not possible to match distinct `record` types to a common return type, so this would not have worked). use diff_views() instead.
- 01:14 PM Revision 12280: bugfix: schemas/util.sql: diff_queries(), diff_views(): embed views verbatim as FROM items, so that the type of their corresponding columns is the view's rowtype. this is necessary in order to match the types of the columns to the return type (set by col_type_null).
- 12:52 PM Revision 12279: schemas/util.sql: diff_cols(): renamed to diff_queries() because multiple columns are supported
- 12:26 PM Revision 12278: fix: schemas/util.sql: diff_cols(): don't assume that each side will have one column, as many of our validations queries have multiple columns
- 11:45 AM Revision 12277: bugfix: schemas/util.sql: diff_views(): need to use $2, not $1, for the 2nd query
- 11:40 AM Revision 12276: fix: schemas/util.sql: materialize_query(): remove any extra blank line before the query, to improve readability when newlines are used as a log message separator and are therefore confusing in the middle of a log message
- 11:37 AM Revision 12275: schemas/util.sql: added ltrim_nl()
- 11:25 AM Revision 12274: bugfix: schemas/vegbien.sql: setting search_path locally: also need to set function option search_path, in order to limit the effects of `SET LOCAL search_path` (mk_set_search_path()) to the current function (http://www.postgresql.org/docs/9.1/static/sql-set.html#AEN75423)
- 11:14 AM Revision 12273: bugfix: schemas/vegbien.sql: setting search_path locally: need to use `SET LOCAL search_path TO` instead of `PERFORM set_config(search_path, ..., is_local := true)` because the meaning of is_local refers to the entire transaction, not just the current function (as for LOCAL)
- 11:05 AM Revision 12272: schemas/util.sql: added mk_set_search_path(VARIADIC schemas text[])
- 10:54 AM Revision 12271: schemas/util.sql: added mk_use_own_schema()
- 10:52 AM Revision 12270: schemas/util.sql: added mk_set_search_path()
- 10:17 AM Revision 12269: schemas/util.sql: use builtin string_agg(..., ) instead of concat()
- 12:34 AM Revision 12268: schemas/util.sql: added qual_name(type unknown) to avoid needing to explicitly specify the type of an input string
- 12:31 AM Revision 12267: schemas/util.sql: qual_name(table_ regclass): use ::text with search_path=pg_temp like qual_name(type regtype), rather than casting to text and then using qual_name(type regtype)
- 12:25 AM Revision 12266: schemas/util.sql: type_qual_name(type regtype): renamed to qual_name(type regtype) to match qual_name(table_ regclass)
- 12:17 AM Revision 12265: schemas/util.sql: added rematerialize_query()
02/16/2014
- 11:59 PM Revision 12264: schemas/vegbien.sql: added rematerialize_in_view()
- 11:16 PM Revision 12263: schemas/vegbien.sql: rematerialize_out_view(): pass a schema-qualified name to util.rematerialize_view() to avoid depending on the search_path to create it in the right schema
- 11:08 PM Revision 12262: schemas/vegbien.sql: materialize_query(): support schema-qualified tables. this requires escaping tables in calls to this function.
- 10:52 PM Revision 12261: schemas/vegbien.sql: added rematerialize_out_view(in_view regclass)
- 10:45 PM Revision 12260: schemas/util.sql: added qual_name()
- 09:47 PM Revision 12259: schemas/vegbien.sql: added rematerialize_out_view(datasource_schema text, out_view regclass)
- 09:39 PM Revision 12258: bugfix: schemas/vegbien.sql: source_by_shortname(): added schema_anchor so that it always applies to the schema it's defined in, even if `public` is not in the search_path
- 09:20 PM Revision 12257: schemas/vegbien.sql: functions with schema_anchors: documented that they must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime changes of search_path (schema elements are bound at inline time rather than runtime)
- 09:14 PM Revision 12256: bugfix: schemas/vegbien.sql: functions with schema_anchors: must use plpgsql for these because the sql language does not support runtime changes of search_path (the schema elements they use are bound at inline time rather than runtime)
- 08:55 PM Revision 12255: schemas/vegbien.sql: schema_null param: renamed to schema_anchor to clarify what this is for
- 08:52 PM Revision 12254: fix: schemas/vegbien.sql: schema_null: clarified that this should *always* be left as the default value, not just usually
- 08:49 PM Revision 12253: schemas/vegbien.sql: schema_null params: use schema_anchor instead of source for this, since it is guaranteed to exist regardless of which tables are in the VegBIEN schema
- 08:46 PM Revision 12252: schemas/vegbien.sql: public: added schema_anchor (used with schema_null params)
- 07:54 PM Revision 12251: schemas/util.sql: use new util.debug_print_sql() instead of `RAISE NOTICE` to improve readability of the debug output (which is used throughout the runscripts)
- 07:41 PM Revision 12250: schemas/util.sql: added debug_print_sql()
- 07:31 PM Revision 12249: schemas/util.sql: added raise_notice(), to allow NOTICEs to be raised in sql-language functions
- 05:56 PM Revision 12248: schemas/vegbien.sql: removed no longer used concat() aggregate. use builtin string_agg() instead.
- 05:55 PM Revision 12247: schemas/util.sql: added rematerialize_view()
- 04:22 PM Revision 12246: bugfix: inputs/bien2_traits/validations.sql: use a wrapper function for util.ifnull() so that the views don't get dropped when the util schema is reinstalled
- 03:24 PM Revision 12245: schemas/util.sql: schema(regtype), schema(regclass): use new schema(pg_namespace_oid oid)
- 03:22 PM Revision 12244: schemas/util.sql: added schema(pg_namespace_oid oid)
- 03:15 PM Revision 12243: schemas/util.sql: schema(regclass): use joins instead of nested SELECTs for consistency with schema(regtype)
- 03:08 PM Revision 12242: schemas/util.sql: regenerated from DB, which reorders the functions
- 02:44 PM Revision 12241: schemas/util.sql: table_name(): renamed to name(regclass) for consistency with other name(...) functions
- 02:28 PM Revision 12240: schemas/util.sql: table_schema(): renamed to schema(regclass) for consistency with other schema(...) functions
- 01:29 PM Revision 12239: web/links/index.htm: updated to Firefox bookmarks: Redmine: added links to the REST API for exporting data. svn: added instructions for using svnsync to export a repository. PostgreSQL: removed duplicate pages that were linked for multiple versions of Postgres.
- 06:45 AM Revision 12238: schemas/util.sql: schema_ident(): renamed to schema_esc() for clarity
- 06:42 AM Revision 12237: schemas/vegbien.sql: added schema_anchor, for use with schema_null params
- 06:39 AM Revision 12236: schemas/util.sql: added mk_search_path()
- 02:14 AM Revision 12235: schemas/*.sql: make sure every COMMENT starts and ends on its own line, so that it appears correctly in the formats it's most likely to be read in (ie. in the DDL export, not the COMMENT edit box in pgAdmin)
02/15/2014
- 03:51 PM Revision 12234: schemas/util.sql: added materialize_view()
- 03:46 PM Revision 12233: schemas/util.sql: added table_name()
- 03:45 PM Revision 12232: schemas/util.sql: table_schema(): removed unneeded STRICT, which prevents inlining
- 03:41 PM Revision 12231: fix: schemas/util.sql: fix_array(): removed no longer accurate comment about effects of STRICT, which is no longer used
- 03:28 PM Revision 12230: fix: schemas/util.sql: materialize_query(): 1st param should be named table_, not view_
- 02:50 PM Revision 12229: schemas/util.sql: added drop_view()
- 02:46 PM Revision 12228: schemas/util.sql: added materialize_query()
- 04:08 AM Revision 12227: /README.TXT: Schema changes: clarified that the staging tables should only be reinstalled if needed
- 04:08 AM Revision 12226: /README.TXT: put **...** around all uppercased text, for consistency
02/14/2014
- 04:36 PM Revision 12225: _license/non-open-source/applies_to.txt: clarified that this is an exclusions list, not an inclusions list, in terms of what's open-source
- 03:09 PM Revision 12224: validation/aggregating/*/*.sql, schemas/vegbien.sql, lib/runscripts/validations.pg.sql.run, inputs/bien2_traits/validations.sql: added _ to beginning of each view name so the validation views would sort at the top in the datasource's tables list. this will also make the validation result sets easily distinguishable from the data tables.
- 02:34 PM Revision 12223: validation/aggregating/*/*.sql: renamed views to match the current naming scheme in the DB. these files still need to be kept up-to-date because there are some queries that haven't been implemented yet, and therefore exist only in these files, not the DB.
- 12:39 PM Revision 12222: schemas/util.sql: ifnull(): use COALESCE() instead, because it turns out that MySQL's IFNULL() is just a special case of this
- 12:20 PM Revision 12221: added inputs/bien2_traits/validations.sql, from validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql
- 12:20 PM Revision 12220: inputs/input.Makefile: $(svnFilesGlob): added validations.sql
- 12:03 PM Revision 12219: bugfix: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added util to search_path so that IFNULL() would link to util.ifnull()
- 11:55 AM Revision 12218: schemas/util.sql: added ifnull(), equivalent to MySQL's IFNULL() (Postgres auto-lowercases the name)
- 11:40 AM Revision 12217: lib/sh/util.sh: stderr_matches(): documented that the purpose of not redirecting fd 2 back to fd 2 is to allow log-filtering out an otherwise-confusing benign error
- 11:30 AM Revision 12216: lib/sh/util.sh: ignore_err_msg(): documented that unlike `|| true`, this suppresses only errors caused by a particular error *message*, rather than all error exit statuses
- 11:28 AM Revision 12215: bugfix: lib/runscripts/validations.pg.sql.run: updated table match pattern to include the type prefix that validations queries now contain
- 11:16 AM Revision 12214: bugfix: lib/runscripts/validations.pg.sql.run: --table: need to include explicit schema so that matching tables from other schemas are not included
- 11:00 AM Revision 12213: added inputs/bien2_traits/validations.sql.run
- 11:00 AM Revision 12212: added lib/runscripts/validations.pg.sql.run
- 10:59 AM Revision 12211: lib/sh/util.sh: stdout_contains(): usage: documented that this requires a `{ ... } 41>&1` wrapper
- 10:57 AM Revision 12210: lib/sh/util.sh: stderr2stdout(): clarified that fd 2 is *not* redirected back to fd 2
- 10:55 AM Revision 12209: bugfix: lib/sh/util.sh: stderr_matches(): need to avoid redirecting stderr and stdout to the same place, because this prevents redirecting stdout back to the original stdout after stderr has been filtered using |
- 10:23 AM Revision 12208: lib/sh/util.sh: local_export_array: renamed to just export_array because this was a replacement for export, not local_export
- 10:21 AM Revision 12207: lib/sh/util.sh: local_*array: don't need -a because that it's an array is autodetected by the ()
- 10:20 AM Revision 12206: lib/sh/util.sh: stderr_matches(): actually don't need to declare PIPESTATUS_ in a separate command, because local does support arrays
- 10:18 AM Revision 12205: lib/sh/util.sh: added local_export_array
- 10:17 AM Revision 12204: fix: lib/sh/util.sh: local_array: clarified that this is only needed for older versions of bash (the lack of support for arrays has apparently been fixed)
- 10:12 AM Revision 12203: lib/sh/util.sh: added local_array
- 10:07 AM Revision 12202: fix: lib/sh/util.sh: stderr_matches(): usage: documented that now need to manually rethrow any command error, if applicable
- 10:04 AM Revision 12201: *{.sh,run}: stderr_matches calls: don't need to wrap the command in `"try"` because stderr_matches now does this
- 09:55 AM Revision 12200: fix: lib/sh/util.sh: `shopt -s lastpipe`: suppress error message if not supported
- 09:50 AM Revision 12199: lib/sh/util.sh: ignore_err_msg(): use new stderr_matches alias, which includes prep_try (requires loading new aliases)
- 09:49 AM Revision 12198: bugfix: lib/sh/util.sh: stderr_matches(): need to save PIPESTATUS and then use the saved var because it's reset after each cmd
- 09:39 AM Revision 12197: lib/sh/util.sh: stderr_matches(): place exit status of cmd in $e for use with exception handling
- 09:21 AM Revision 12196: lib/sh/util.sh: set `shopt -s lastpipe`, to allow setting vars in the last command of a pipeline
- 08:43 AM Revision 12195: bugfix: lib/sh/util.sh: ignore_err_msg(): use `try` properly with prep_try and `"try"`
- 08:39 AM Revision 12194: lib/sh/util.sh: added prep_try and use it in try alias. try: documented how to run it with a wrapper command.
- 08:21 AM Revision 12193: lib/sh/db.sh: pg_dump(): ignore "No matching tables were found" error, using new ignore_err_msg()
- 08:20 AM Revision 12192: lib/sh/util.sh: added ignore_err_msg()
- 07:34 AM Revision 12191: lib/sh/db.sh: pg_schema_exists(): documented that `try` is used to suppress the error exit status
- 07:31 AM Revision 12190: lib/sh/util.sh: stderr_matches(): usage: surrounded command in if statement to indicate what context it would usually be used in
02/13/2014
- 05:01 PM Revision 12189: lib/runscripts/file.pg.sql.run, schema.pg.sql.run: support custom options to pg_dump in $@
- 04:10 PM Revision 12188: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: added view names using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 04:10 PM Revision 12187: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original_mysql.VegCore.sql: removed trailing whitespace
- 04:06 PM Revision 12186: schemas/vegbien.sql: public_validations_*: merged into single public_validations schema, with type-specific prefixes for each query, so that datasources can use validations queries from multiple type categories, and so that each datasource doesn't have to indicate which validations output schema it's using
- 02:23 PM Revision 12185: schemas/util.sql: diff_cols(): documented how to run EXPLAIN on the FULL JOIN query
- 02:14 PM Revision 12184: schemas/util.sql: diff_cols(): embed left_query/right_query directly in the main SQL statement, so that the query planner can take the sort order of the queries into account in planning the FULL JOIN (and where possible, use a fast merge join)
- 02:09 PM Revision 12183: schemas/vegbien.sql: regenerated from DB, which adds an autogenerated column alias
- 02:06 PM Revision 12182: schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*
- 02:05 PM Revision 12181: schemas/util.sql: added eval2col_pair()
- 01:23 PM Revision 12180: web/.phpPgAdmin/.htaccess: support linking to a function
- 01:08 PM Revision 12179: web/.phpPgAdmin/.htaccess: support linking to the functions list
- 01:05 PM Revision 12178: web/.phpPgAdmin/.htaccess: support linking to a view
- 12:58 PM Revision 12177: web/.phpPgAdmin/.htaccess: allow linking directly to the views list of a schema
- 12:54 PM Revision 12176: added backups/vegbien.r12025.backup.md5
- 12:54 PM Revision 12175: web/.phpPgAdmin/.htaccess: refactored to build the URL incrementally, extracting path components 1st->last, instead of handling each # of path components as a separate case (which was more rigid and created unnecessary duplication)
- 12:53 PM Revision 12174: backups/TNRS.backup.md5: updated
- 11:47 AM Revision 12173: bugfix: web/.phpPgAdmin/.htaccess: path RewriteRules: need to go directly to the applicable tab, rather than going via redirect.php, because redirect.php will use the *last selected tab*, rather than the first tab for that section, leading to unexpected results when a non-default tab was previously selected
- 10:41 AM Revision 12172: schemas/vegbien.sql, validation/aggregating/*/*.sql: renamed public_validation_* to plural public_validations_* since we refer to them as aggregating validation*s*
- 10:33 AM Revision 12171: validation/aggregating/pipeline/aggregating_validations_pipeline.odg: made "diff tables" the same size as the other major labels
- 10:30 AM Revision 12170: validation/aggregating/pipeline/aggregating_validations_pipeline.odg: added CSVs export arrows and import process arrows, as decided in conference call
- 08:34 AM Revision 12169: schemas/vegbien.sql: public_validation_plots: added Brad's notes as comments
- 08:29 AM Revision 12168: schemas/vegbien.sql: public_validation_plots: populated queries from validation/aggregating/plots/bien3_validations_salvias_vegbien.sql
- 08:26 AM Revision 12167: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: _12_count_of_verbatim_taxa_per_plot_in_each_project: removed typo (in the original queries, not added by the refactoring)
- 08:23 AM Revision 12166: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: shortened view names to fit in the 63-char limit
- 08:20 AM Revision 12165: schemas/vegbien.sql: public_validation_traits: added Brad's notes as comments
- 08:17 AM Revision 12164: schemas/vegbien.sql: public_validation_traits: populated queries from validation/aggregating/traits/bien3_validations_traits_bien3.sql
- 08:02 AM Revision 12163: validation/aggregating/*/*.sql: use "" to preserve case in column aliases, so that they match the input queries
- 07:58 AM Revision 12162: validation/aggregating/*/*.sql: use current_schema instead of a hardcoded datasource name or psql variable
- 07:49 AM Revision 12161: validation/aggregating/*/*.sql: search_path: added public
- 07:45 AM Revision 12160: validation/aggregating/*/*.sql: use the new type-specific validation schema names
- 07:41 AM Revision 12159: validation/aggregating/*/*.sql: removed type-specific prefix from the view name, which is now part of the validation schema
- 07:26 AM Revision 12158: inputs/import.stats.xls: updated import times
- 06:53 AM Revision 12157: inputs/import.stats.xls: updated import times
- 05:58 AM Revision 12156: schemas/vegbien.sql: analytical_stem.datasource: removed fkey to source.shortname because this prevents individually-reloaded datasources from being published
- 05:56 AM Revision 12155: schemas/vegbien.sql: publish(), rm(): documented runtime (1 min)
- 05:49 AM Revision 12154: schemas/vegbien.sql: added datasource_unpublish()
- 03:48 AM Revision 12153: lib/sql_io.py: automatic handling of input/output column type mismatches: also do this for identifying columns, which first cause an error in a join in sql.distinct_table() rather than in the main insert (and thus were not handled by the existing error handling). previously, the user would have had to manually cast the input column in postprocess.sql. this involves getting handle_MissingCastException() to update join_cols as well as mapping.
- 03:37 AM Revision 12152: lib/sql.py: distinct_table(): don't cache the table creation, because this prevents different distinct_tables from being created for the same input table
- 03:36 AM Revision 12151: lib/sql.py: create_table(), copy_table_struct(): support custom query options, such as cacheable
- 12:29 AM Revision 12150: lib/sql_io.py: put_table(): main loop MissingCastException handler: factored out into nested function so that it can also be used elsewhere
- 12:01 AM Revision 12149: lib/sql.py: parse_exception(): parse "operator does not exist" errors as MissingCastExceptions (these appear when a staging table column is of the wrong type)
02/08/2014
- 11:17 PM Revision 12148: bugfix: /README.TXT: Full database import: Check that source contains [# datasources] rows up through XAL: added alternative verification method when this is not the case (some datasources may be near the end depending on import order)
- 10:38 PM Revision 12147: schemas/vegbien.sql: public_validation: split into separate subschemas for plots, specimens, traits
- 10:33 PM Revision 12146: schemas/Makefile: vegbien.sql: support auxiliary schemas besides *_validation
- 10:03 PM Revision 12145: fix: schemas/Makefile: %/publish: removed no longer applicable instructions about running `make schemas/rotate`. you should not publish a schema until you are satisfied that it can replace the previous public schema, so that the previous public schema doesn't need to be saved.
- 10:00 PM Revision 12144: fix: schemas/vegbien.sql: public_validation comment: undid incorrect replacement of "validation" with "public_validation"
- 09:59 PM Revision 12143: bugfix: schemas/Makefile: "public": dont
- 09:37 PM Revision 12142: schemas/Makefile: %/publish: don't ignore errors if schema doesn't exist, because it should always exist if you are publishing it
- 09:36 PM Revision 12141: schemas/Makefile: `%/uninstall: vegbien.sql`: use util.schema_bundle_rm() instead of public.rm(), because when the public schema is incompletely imported, public.rm() will not yet exist
- 09:34 PM Revision 12140: schemas/Makefile: `%/install: vegbien.sql`: support auxiliary schemas besides *_validation
- 09:16 PM Revision 12139: schemas/Makefile: %/uninstall, %/publish: use new public.rm(), publish(), which encapsulate the process of renaming multiple related schemas and also allow additional auxiliary schemas beyond just *_validation
- 03:55 PM Revision 12138: schemas/vegbien.sql: added rm(), which deletes this schema
- 03:17 PM Revision 12137: schemas/vegbien.sql: added publish(), which publishes this schema
- 02:47 PM Revision 12136: schemas/vegbien.sql: added public_schema_publish()
- 02:21 PM Revision 12135: schemas/util.sql: added functions for maintaining schema bundles, groups of schemas with a common prefix
- 04:26 AM Revision 12134: schemas/util.sql: added schema_bundle_get_schemas()
02/07/2014
- 11:36 PM Revision 12133: schemas/util.sql: added schema_replace()
- 11:11 PM Revision 12132: schemas/util.sql: added schema_rm(), schema_rename()
- 08:30 PM Revision 12131: schemas/vegbien.sql: datasource_publish(): removed nested transaction, which is not needed because Postgres runs each top-level command in a transaction (including in autocommit mode)
- 06:58 PM Revision 12130: bugfix: schemas/Makefile,vegbien.sql: renamed validation to public_validation to match the suffixed name applied by `make schemas/public/install`
- 05:58 PM Revision 12129: schemas/Makefile: %/install, %/publish for public schema: added usage
- 08:37 AM Revision 12128: /README.TXT: to back up the version history: added back `git svn fetch` so we keep the git export up-to-date, too
- 08:29 AM Revision 12127: /README.TXT: to back up the version history: added runtimes (1.5 h for the initial svnsync)
- 08:24 AM Revision 12126: /README.TXT: to back up the version history: added trailing /s to dirs
- 08:24 AM Revision 12125: bugfix: /README.TXT: to back up the version history: fixed svn_repo/ path
- 08:16 AM Revision 12124: **/LICENSE.TXT: corrected "it recommended" to "it is recommended"
- 08:12 AM Revision 12123: _license/non-open-source/applies_to.txt: deleted items: tip revision needs to refer to the revision *at which* the problem was discovered, not the revision before that
- 08:10 AM Revision 12122: _license/non-open-source/applies_to.txt: deleted items: clarified that some things deleted after r11705 (when we started copyright scrubbing) might also be non-open-source, because they might have been deleted for reasons other than copyright scrubbing and thus would be missing the "copyright scrub" tag
- 08:09 AM Revision 12121: _license/non-open-source/applies_to.txt: deleted items: clarified that some things deleted after r11705 (when we started copyright scrubbing) might also be non-open-source, because they might have been deleted for reasons other than copyright scrubbing and thus would be missing the "copyright scrub" tag
- 08:00 AM Revision 12120: /LICENSE.TXT: removed separate non-open-source disclaimer about parts of the version history before r11705, because this is now listed in _license/non-open-source/applies_to.txt
- 07:59 AM Revision 12119: _license/non-open-source/applies_to.txt: included deleted items that are non-open-source, so that the list correctly applies to the entire repository (which includes the version history)
- 07:51 AM Revision 12118: **/LICENSE.TXT: disclaimer about things that are not open-source: removed "these are copyright their respective owners", because this is understood
- 07:22 AM Revision 12117: _license/README.TXT: changed "parts" to "components" for clarity
- 07:18 AM Revision 12116: _license/non-open-source/applies_to.txt: UArizona?: added BIEN2/traits_observation_counts.xls
- 06:52 AM Revision 12115: /README.TXT: to back up the version history: use svnsync instead of `git svn fetch`, so that the backup is in a format that can be directly reimported into an svn repo
- 05:53 AM Revision 12114: /LICENSE.TXT: parts of the repository that are not open-source: added ,s to clarify the meaning
- 05:39 AM Revision 12113: /LICENSE.TXT: documented that this license refers specifically to the repository hosted at https://code.nceas.ucsb.edu/code/projects/bien/trunk/, since we also have a publicly-accessible checkout at http://fs.vegpath.org/, which contains numerous non-open-source files (more than are listed in _license/non-open-source/applies_to.txt)
- 05:37 AM Revision 12112: fix: inputs/VegBIEN/Redmine/wiki/.htaccess: redirect to new main page when accessed without trailing /
- 05:26 AM Revision 12111: web/BIEN3: redirected to .wiki since the wiki homepage now contains the list of all BIEN3 components
- 05:26 AM Revision 12110: web/links/index.htm: updated to Firefox bookmarks. Quick links: use bien3.vegpath.org to link to the main BIEN3 page
- 05:05 AM Revision 12109: added web/BIEN3 symlink, with .bien3 case-insensitive alias
- 04:44 AM Revision 12108: _license/*/applies_to.txt: added exclusion for "UCSB modifications"
- 04:38 AM Revision 12107: added _license/GPL2.txt.url
- 04:36 AM Revision 12106: /LICENSE.TXT, _license/VegBank_schema/LICENSE.TXT: moved text of the GPL2 license into separate file _license/GPL2.txt to avoid cluttering up LICENSE.TXT
- 04:36 AM Revision 12105: _license/*/LICENSE.TXT: documented that the first section of the file is from license.eml.txt in each folder
- 04:33 AM Revision 12104: /LICENSE.TXT, _license/VegBank_schema/LICENSE.TXT: moved text of the GPL2 license into separate file _license/GPL2.txt to avoid cluttering up LICENSE.TXT
- 04:23 AM Revision 12103: /LICENSE.TXT: GPL text: include the full range of copyright years
- 04:17 AM Revision 12102: /LICENSE.TXT: added disclaimer that parts of the version history before r11705 (2013-11-21) are *NOT* open-source. this is the revision of the first copyright scrub.
- 04:02 AM Revision 12101: added issues/ with LICENSE.TXT for the issue tracker (BSD)
- 03:51 AM Revision 12100: wiki/_license/*/: symlink the contents instead of the folders, because the root dir's and wiki's applies_to.txt's will be different
- 03:49 AM Revision 12099: wiki/_license/*/: symlink the contents instead of the folders, because the root dir's and wiki's applies_to.txt's will be different
- 03:45 AM Revision 12098: wiki/LICENSE.TXT: documented what this license refers to (the wiki hosted at https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/)
- 03:43 AM Revision 12097: wiki/LICENSE.TXT: added message about including _license/ when redistributing, as for /LICENSE.TXT
- 03:42 AM Revision 12096: wiki/_license/LICENSE.TXT: replaced with README.TXT as in /_license
- 03:41 AM Revision 12095: wiki/LICENSE.TXT: changed "the page text of the wiki" to "the wiki's components" since some of the attachments will eventually be marked as open-source
- 03:39 AM Revision 12094: /LICENSE.TXT: moved emphasized sections to top
- 03:37 AM Revision 12093: /LICENSE.TXT: changed "the open-source parts of the repository" to "the repository's components" so that this sentence makes sense once the non-open-source exception is removed
- 03:36 AM Revision 12092: /LICENSE.TXT: documented that the non-open-source files are now listed in _license/non-open-source/applies_to.txt
- 03:33 AM Revision 12091: added _license/non-open-source/applies_to.txt
- 03:24 AM Revision 12090: removed schemas/VegX/run.py, which is a duplicate of _archive/bienDjango/trunk/bien/v/models.py (this may have ended up here because it was a wiki page attachment, but the initial revision did not indicate the source)
- 03:09 AM Revision 12089: added _license/VegBank_schema/applies_to.txt
- 03:02 AM Revision 12088: schemas/VegCore/vegbien_identifier_examples.xlsx: changed to symlink to Brad_Boyle/vegbien_identifier_examples.xlsx, to avoid duplicate copies
- 02:52 AM Revision 12087: added _license/TWiki/ (for inputs/VegBIEN/TWiki/)
- 02:38 AM Revision 12086: added _license/PostgreSQL_config (for schemas/*.conf)
- 02:24 AM Revision 12085: */LICENSE.TXT: GPL: removed divider between the copyright header and license text, since these are part of the same license
- 02:19 AM Revision 12084: _license/ESA/: renamed to VegBank_schema/ because this license applies only to the VegBank schema
- 02:18 AM Revision 12083: _license/README.TXT: merged ¶2 into ¶1
- 02:04 AM Revision 12082: _license/README.TXT: replaced "authority information proving the open-source status" with the simpler "open-source proof". clarified that each LICENSE.TXT is meant as a proof.
- 01:52 AM Revision 12081: /LICENSE.TXT: added note that when redistributing, it recommended to include _license/, so that the open-source proof is included along with the license text (this information should be here as well as in _license/README.TXT because redistributors might not think to look in that file)
- 01:49 AM Revision 12080: /LICENSE.TXT: removed "svn" before "repository" because this would apply equally to a git export of the repository
- 01:47 AM Revision 12079: /LICENSE.TXT: replaced "portions" with "parts" for consistency with the _license/README.TXT wording
- 01:44 AM Revision 12078: _license/README.TXT: clarified that this folder contains authority information proving the open-source status of parts of the codebase. added recommendation to include this folder when redistributing, so that the open-source proof is included along with the license text.
- 01:32 AM Revision 12077: _license/LICENSE.TXT: renamed to README.TXT because this actually contains a description of the folder, not license text
- 01:24 AM Revision 12076: schemas/Makefile: removed no longer used rename/%, rotate. use %/install, %/publish instead to manage public schema versions.
- 01:22 AM Revision 12075: schemas/Makefile: removed no longer needed public/uninstall, which is handled more generally by `%/uninstall: vegbien.sql` instead
- 01:21 AM Revision 12074: schemas/Makefile: `%/uninstall: vegbien.sql`: added support for *_validation schema
- 01:16 AM Revision 12073: schemas/Makefile: %/uninstall: split into 2 rules like for %/install, so that different actions can easily be added for the public schema
- 01:13 AM Revision 12072: schemas/Makefile: %/publish: support auxiliary *_validation schemas
- 01:12 AM Revision 12071: bugfix: schemas/Makefile: `%/install: vegbien.sql`: need -e before each s/// expression
02/06/2014
- 07:34 PM Revision 12070: schemas/Makefile: `%/install: vegbien.sql`: create a custom *_validation schema for *each* public schema, rather than one for the most recently-created public schema. this allows validations to continue to be run against a previous version of the DB while a new version is being imported.
- 04:48 PM Revision 12069: validation/aggregating/traits/bien3_validations_traits_bien3.sql: include only records from the bien2_traits datasource, to ensure that the scope of all queries is limited to the datasource
- 04:16 PM Revision 12068: validation/aggregating/*/*.sql (the output queries): added `SET search_path TO validation`
- 04:12 PM Revision 12067: validation/aggregating/specimens/qualitative_validations_specimens.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 04:11 PM Revision 12066: validation/aggregating/traits/bien3_validations_traits_bien3.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 04:03 PM Revision 12065: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 03:47 PM Revision 12064: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added CREATE VIEW prefixes using the steps at wiki.vegpath.org/Aggregating_validations_refactoring#prepend-CREATE-VIEW
- 02:48 PM Revision 12063: added validation/aggregating/specimens/qualitative_validations_specimens.sql template, with query descriptions from NY/qualitative_validations_source_db_NYBG.sql
- 02:08 PM Revision 12062: validation/**: removed old versions of by=-tagged files. these were not renamed in the last commit because there were newer versions that they collided with. note that because , sorts before . , the newer version (with more ",name@inst" tags) was correctly renamed instead of the older version.
- 01:56 PM Revision 12061: validation/**: removed by=... indicators so that we don't have to add an author name every time a file is changed. the author indicator is not needed now that both Brad's and my edits are open-source.
- 01:14 PM Revision 12060: inputs/bien2_traits/TraitObservation/postprocess.sql: remove rows with no taxon name, which are invalid, and which helps simplify the aggregating validations queries
- 12:33 PM Revision 12059: added validation/aggregating/pipeline/aggregating_validations_pipeline.odg with .png export
- 11:09 AM Revision 12058: added validation/aggregating/plots/FIA/bien3_validations_fia_input.sql.eml.txt. note that a .url file is not needed because this information is in the .eml.txt file.
- 10:57 AM Revision 12057: fix: validation/aggregating/traits/_archive/bien3_validations_traits_bien3.by=Brad@iPlant.sql: renamed to indicate that this is actually *not* Brad's original file, as the filename would seem to indicate
- 10:49 AM Revision 12056: added validation/aggregating/plots/FIA/
- 03:46 AM Revision 12055: validation/aggregating/*.by=Brad@iPlant*: placed under version control, since these are now open-source
- 03:41 AM Revision 12054: validation/**: removed svn:ignore exclusion of iPlant-owned files, since these are now open-source
- 03:05 AM Revision 12053: fix: inputs/VegBIEN/Redmine/svn/.htaccess: updated repository URL to point to trunk/
- 03:03 AM Revision 12052: added wiki/LICENSE.TXT,_license/, with license terms that apply specifically to the wiki (which is *BSD*, not GPL). note that the wiki attachments are *NOT* open-source, only the page text.
- 02:44 AM Revision 12051: schemas/vegbien.ERD.mwb: regenerated exports
- 02:41 AM Revision 12050: _license/VegBank/: renamed to ESA/ following the convention that each _license/ subfolder is a copyright owner
02/05/2014
- 06:03 PM Revision 12049: /LICENSE.TXT: added collective license conditions (GPL2+) for the svn repository as a whole, except the non-open-source portions
- 05:50 PM Revision 12048: added _license/VegBank/
- 05:24 PM Revision 12047: added LICENSE.TXT and related files (in _license/). the open-source portions of the svn repository are licensed as described in _license/. note that some portions of the svn repository are *NOT* open-source. these are copyright their respective owners.
- 06:05 AM Revision 12046: schemas/vegbien.ERD.mwb: synced to schemas/vegbien.my.sql, which adds the validation schema
- 05:51 AM Revision 12045: schemas/util.sql: added diff_views()
- 05:31 AM Revision 12044: schemas/util.sql: added diff_cols(), for use with the aggregating validations
- 01:16 AM Revision 12043: bugfix: schemas/Makefile: public/uninstall: need to uninstall the validation schema in addition to the public schema (deleting the public schema would only cascade to the *contents* of the validation schema)
- 01:02 AM Revision 12042: schemas/vegbien.sql: added validation schema, which contains queries used in the aggregating validations. schemas/Makefile: include schemas that depend on `public` (eg. `validation`) so they are restored along with it.
- 12:55 AM Revision 12041: bugfix: bin/pg_dump_vegbien: fixed arg-count check to allow passing command-line options to pg_dump via args
02/04/2014
- 10:42 AM Revision 12040: bugfix: inputs/SALVIAS/verify/plots.out.sql: fixed ' quoting syntax to use '' instead of \' to escape '
- 10:32 AM Revision 12039: inputs/input.Makefile: verify/%.out: use a *.sql file in the verify/ directory itself to generate *.out, so that each datasource can have its own set of output queries. for datasources that should share the same set of queries, they can instead be symlinked to the same file.
- 10:01 AM Revision 12038: fix: inputs/CVS/project/: added _no_import since this should not also be imported separately from taxon_observation.**
- 04:21 AM Revision 12037: fix: validation/aggregating/traits/_archive/bien3_validations_traits_bien3.by=Brad@iPlant,Aaron@UCSB,Brad@iPlant.sql: added missing .url extension
02/03/2014
- 05:50 PM Revision 12036: added validation/aggregating/traits/_archive/bien3_validations_traits_bien3.by=Brad@iPlant,Aaron@UCSB,Brad@iPlant.sql
- 05:37 PM Revision 12035: validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_original_mysql.by=Brad@iPlant,Aaron@UCSB,Brad@iPlant.sql.url: renamed to indicate that it's just by Brad (it was the other file, the output queries, that were created by both of us)
- 05:32 PM Revision 12034: added validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_original_mysql.by=Brad@iPlant,Aaron@UCSB,Brad@iPlant.sql.url
- 01:54 AM Revision 12033: validation/aggregating/plots/_archive/bien3_validations_salvias_vegbien.by=Brad@iPlant.sql.url: removed trailing @
- 01:53 AM Revision 12032: deleted validation/aggregating/traits/_archive/bien3_validations_traits_bien3.by=Brad@iPlant.sql.url@, since there is already a version without the trailing @
- 01:48 AM Revision 12031: validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_original.by=Brad@iPlant.sql.url: removed trailing @ (apparently, the @ escape for paths containing @ should only be used on the *input* path)
02/02/2014
- 11:18 PM Revision 12030: added inputs/XAL/Specimen/_no_import, since this is a demo-only datasource and there isn't a staging table for it
- 11:10 PM Revision 12029: inputs/.geoscrub/county_centroids/test.xml.ref, inputs/.NCBI/{names.src,nodes.src}/test.xml.ref: accepted test outputs (generated now that these tables are in import_order.txt)
- 10:31 PM Revision 12028: inputs/FIA/taxon_observation.**/header.csv: updated for new REF_RESEARCH_STATION.country metadata value col
- 10:27 PM Revision 12027: /README.TXT: Full database import: `make test by_col=1`: documented runtime (20 min)
- 10:27 PM Revision 12026: /README.TXT: Full database import: `. bin/import_all`: documented how to view progress
- 09:30 PM Revision 12025: fix: schemas/Makefile: %/uninstall: don't prompt to delete auxiliary schemas, as these do not contain data
- 07:52 PM Revision 12024: schemas/vegbien.sql: added top_plot view
- 06:22 PM Revision 12023: /README.TXT: to back up the version history: added steps to sync the git repository to jupiter and the local machine
- 05:44 PM Revision 12022: /README.TXT: added steps to back up the version history
- 05:30 PM Revision 12021: validation/aggregating/plots/bien3_validations_salvias_vegbien.by=Brad@iPlant.sql.url: moved to _archive/ so only the most recent version would be in the main plots/ directory
- 01:57 AM Revision 12020: planning/resources/dev_machine.specs/sys_info.spx+/system_power_settings.txt: updated: enabled "Automatic Restart on Power Loss" and "Wake on LAN" for UPS (this requires using pmset, as described at http://vegpath.org/links/#pmset%281%29%20Mac%20OS%20X%20Manual%20Page )
- 01:51 AM Revision 12019: web/links/index.htm: updated to Firefox bookmarks: wake for network access: added link to pmset man page, which contains commands for setting "wake for network access" for *all* power sources. note that "automatic restart on power loss" also needs to be set for *all* power sources, because the computer will always be in UPS mode at the time of a power failure.
- 12:49 AM Revision 12018: inputs/input.Makefile: add!: verify/: also svn:ignore *.tsv, *.txt
02/01/2014
- 11:57 PM Revision 12017: validation/aggregating/traits/bien3_validations_traits_bien3.by=Brad@iPlant.sql*: moved to _archive/
- 11:57 PM Revision 12016: validation/aggregating/traits/BIEN2_traits/bien3_validations_traits_original.by=Brad@iPlant.sql*: moved to _archive/
01/28/2014
- 05:02 PM Revision 12015: schemas/vegbien.sql: location_set_top_plot(): only do the lookup of the parent's top_plot when there is a parent_id
- 04:56 PM Revision 12014: schemas/vegbien.sql: location: added autopopulated top_plot
01/26/2014
- 11:09 AM Revision 12013: schemas/vegbien.sql: locationevent: added locationevent_pull_forward_from_parent() trigger which populates project_id from the parent locationevent
- 10:23 AM Revision 12012: web/links/index.htm: updated to Firefox bookmarks: NCEAS: added link to phone list, which contains the Balboa Building maintenance numbers
01/25/2014
- 09:18 PM Revision 12011: /README.TXT: Notes on running programs: added warning that you should always start with a clean shell to avoid spurious bugs
- 11:17 AM Revision 12010: schemas/vegbien.sql: locationevent__pull_forward_from_parent(): renamed to locationevent_pull_forward_from_parent_for_stratum() since it actually has a filter that causes it to apply only to stratum events
01/24/2014
- 11:22 PM Revision 12009: added validation/aggregating/, containing folders for Brad's scripts (the scripts themselves cannot be in the repository because they are not UCSB-owned)
- 04:38 PM Revision 12008: web/links/index.htm: updated to Firefox bookmarks: svn: added steps to add a file containing @ (append @ to the path)
01/23/2014
- 03:07 PM Revision 12007: web/links/index.htm: updated to Firefox bookmarks: `mysql`: added commands to get interactive formatting (including echoed queries) when inputting from a file, and to get raw formatting when inputting from the console. git: added equivalent to `svn update`.
- 11:21 AM Revision 12006: bugfix: schemas/vegbien.sql: commconcept: added missing unique constraint on commdescription, which is also an identifying field in addition to the ID (which is in commname.commname)
- 01:44 AM Revision 12005: inputs/publishable datasources.xlsx: updated
- 01:43 AM Revision 12004: inputs/publishable datasources.xlsx: updated
- 01:35 AM Revision 12003: fix: inputs/SALVIAS/projects/postprocess.sql: remove private data that should not be publicly visible: remove projects that do not have "There are no specific use conditions attached to this dataset"
- 01:22 AM Revision 12002: fix: inputs/SALVIAS/salvias_plots.~.clean_up.sql: Remove private data that should not be publicly visible: also need to remove metadata-only plots
- 01:11 AM Revision 12001: bugfix: inputs/SALVIAS/plotMetadata_/map.csv: things mapped to project_participant: remapped to event__participant because these actually relate to the *event*, not the project, even though they seem like project-related fields
- 01:04 AM Revision 12000: mappings/VegCore-VegBIEN.csv: event__participant: allow multiple event__participant columns
- 12:54 AM Revision 11999: fix: inputs/SALVIAS/plotMetadata_/map.csv, inputs/Madidi/LocationObservation/map.csv: things mapped to communityID: remapped to communityName, which is what's used in analytical_stem (communityID is for numeric IDs)
- 12:15 AM Revision 11998: bugfix: schemas/vegbien.sql: locationevent__communities(): inner-join to commdetermination instead of left-joining, so that this does not produce community entries (which occur because there is one commclass per locationevent, but only some commclasses will then have a commdetermination)
Also available in: Atom