Activity
From 01/27/2014 to 02/25/2014
02/25/2014
- 11:51 PM Revision 12495: schemas/util.sql: remake_diff_table(): prepend the "view this table in human-readable form" comment instead of appending because it is more important than the "contents generated from" comment previously added
- 11:49 PM Revision 12494: schemas/util.sql: added prepend_comment()
- 11:39 PM Revision 12493: schemas/util.sql: removed no longer used mk_set_search_path(VARIADIC schemas text[]). use mk_search_path() instead.
- 11:36 PM Revision 12492: schemas/util.sql, vegbien.sql: comment about "function option search_path": don't include `mk_set_search_path()` since that is no longer used
- 11:35 PM Revision 12491: schemas/util.sql: diff(text, text): always use the schema of col_type_null (the common base type) as the search_path, since any custom %== operator for it will always be in the same schema as it
- 11:28 PM Revision 12490: schemas/util.sql: use_own_schema(): renamed to use_schema() because this can be used for any type-linked schema
- 11:23 PM Revision 12489: bugfix: rematerialize_out_view(): set_search_path() does not take the same arguments as mk_set_search_path() did, so need to use mk_search_path() instead
- 11:19 PM Revision 12488: schemas/util.sql: use_own_schema(): auto-append util to the search_path to enable use of util operators
- 11:11 PM Revision 12487: schemas/util.sql: mk_set_search_path(): no need to debug_print_return_value() anymore because functions now use set_search_path() (or something that calls it), which debug-prints the statement (`EXECUTE util.mk_set_search_path()` did not)
- 11:06 PM Revision 12486: schemas/util.sql: mk_search_path(), mk_set_search_path(text...): auto-append util to the search_path to enable use of util operators
- 10:49 PM Revision 12485: schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.
- 10:48 PM Revision 12484: schemas/util.sql, vegbien.sql: use util.use_own_schema()/util.set_search_path() instead of EXECUTE util.mk_use_own_schema()/util.mk_set_search_path()
- 10:42 PM Revision 12483: schemas/util.sql: added use_own_schema()
- 10:40 PM Revision 12482: schemas/util.sql: added set_search_path()
- 10:36 PM Revision 12481: schemas/util.sql: runnable_sql(): don't output search_path before a SET statement
- 10:31 PM Revision 12480: schemas/util.sql: added is_set_stmt()
- 09:28 PM Revision 12479: schemas/util.sql: diff(text, text): use mk_diff_query()'s new cols param to avoid a nested SELECT
- 09:17 PM Revision 12478: schemas/util.sql: mk_diff_query(): parameterized the customizable parts of the query, to allow them to be replaced with their EXPLAIN expansion (which is what we ultimately want, so that the query does not refer to any internal tables or views)
- 09:02 PM Revision 12477: schemas/util.sql: diff(text, text): use util.mk_diff_query(). this splits the complex query-generation code apart from the query-execution code.
- 08:51 PM Revision 12476: bugfix: schemas/util.sql: undid r12472 because the command needed to limit the effects of any `SET LOCAL search_path` to the current function will unfortunately also clear any existing search_path, which may be needed for the eval query to execute
- 08:25 PM Revision 12475: schemas/util.sql: added mk_diff_query()
- 08:13 PM Revision 12474: schemas/util.sql: debug_print_sql(): use runnable_sql()
- 08:12 PM Revision 12473: schemas/util.sql: added runnable_sql()
- 08:07 PM Revision 12472: fix: schemas/util.sql: eval2*(): added search_path function option in order to limit the effects of any `SET LOCAL search_path` in the invoked query to the current function. however, plain eval() is not changed because it is often used to execute a `SET LOCAL search_path` in the calling function.
- 06:03 PM Revision 12471: schemas/util.sql: diff(text, text): removed unnecessary casts to the base type in the join condition and the WHERE filter. these had been presumed necessary due to errors, but the errors turned out to be caused by the operator not being in the search_path. note that the casts in the columns list are still needed, as described in the associated comment.
- 05:40 PM Revision 12470: schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from
- 05:39 PM Revision 12469: schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL
- 04:11 PM Revision 12468: schemas/util.sql: mk_set_search_path(text): include LOCAL commented out, because the user might want to run it with another statement *as a single command*, in which case it would be useful because they will be in the same transaction (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)
- 04:09 PM Revision 12467: schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)
- 04:04 PM Revision 12466: schemas/util.sql: mk_set_search_path(): support creating a SET search_path statement for display as well, which would exclude LOCAL because it doesn't work as a standalone command (http://www.postgresql.org/docs/9.3/static/sql-set.html#AEN81154)
- 02:48 PM Revision 12465: schemas/util.sql: debug_print_sql(): include the search_path in case the query contains search_path-dependent elements (such as operators)
- 02:45 PM Revision 12464: schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info
- 02:30 PM Revision 12463: schemas/util.sql: added mk_set_search_path() (which uses the current search_path)
- 02:08 PM Revision 12462: schemas/util.sql: diff(text, text): in the outputted FULL JOIN query, documented that you should refer to the EXPLAIN output for the expansion of %==
- 01:54 PM Revision 12461: bugfix: schemas/util.sql: first_word(): ignore leading whitespace: need to use util.ltrim_nl(), as ltrim() only removes spaces
- 01:46 PM Revision 12460: bugfix: schemas/util.sql: first_word(): need to ignore leading whitespace. this applies to many of our queries, which have a leading newline.
- 01:44 PM Revision 12459: schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output
- 01:41 PM Revision 12458: schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements
- 01:28 PM Revision 12457: schemas/util.sql: added is_explain()
- 01:25 PM Revision 12456: fix: schemas/util.sql: diff(text, text): FULL JOIN SELECT statement: don't put a comment at the very beginning, because this prevents it from being autoexplained (this is the query we particularly want EXPLAIN output for)
- 01:22 PM Revision 12455: fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any
- 01:19 PM Revision 12454: fix: schemas/util.sql: debug_print_sql(): need newline before and after EXPLAIN output to visually separate it from other debug info
- 01:06 PM Revision 12453: schemas/util.sql: util.debug_print_sql(): include the EXPLAIN output of applicable queries, for easier query plan debugging (just like lib/sql.py's db.autoexplain)
- 01:02 PM Revision 12452: schemas/util.sql: added explain2notice_msg_if_can()
- 12:57 PM Revision 12451: schemas/util.sql: added is_explainable(sql)
- 12:55 PM Revision 12450: schemas/util.sql: added first_word()
- 12:25 PM Revision 12449: schemas/util.sql: explain2notice(): use util.raise_notice()
- 12:23 PM Revision 12448: schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually
- 12:21 PM Revision 12447: fix: schemas/util.sql: schema comment: noted that avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won't mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements
- 12:19 PM Revision 12446: schemas/util.sql, vegbien.sql: do not declare *any* SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment). the STRICT qualifier has been preserved where its behavior is necessary, rather than just used for optimization.
- 12:09 PM Revision 12445: fix: schemas/util.sql: schema comment: indicate that *all* SQL-language functions should never be declared STRICT, not just IMMUTABLE ones, since inlining is not limited to IMMUTABLE (or STABLE) functions (although constant folding is)
- 12:07 PM Revision 12444: bugfix: schemas/util.sql: join_strs_transform(): added back STRICT qualifier because it must be declared STRICT to use the special handling of STRICT aggregating functions
- 11:56 AM Revision 12443: schemas/util.sql: %==(anyelement, anyelement): needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
- 11:49 AM Revision 12442: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: difference between STABLE and IMMUTABLE: functions that depend on the search_path: clarified that the confusing effects of using IMMUTABLE for one of these functions are only noticeable if the function is called on only *constant* values in a *PL/pgSQL* function (in which case the wrong search_path (the one in effect at *create* time) will be used)
- 11:37 AM Revision 12441: schemas/util.sql, vegbien.sql: do not declare IMMUTABLE SQL-language functions as STRICT, because this prevents them from being inlined (as documented in the util schema comment)
- 11:23 AM Revision 12440: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added explanation of the difference between STABLE and IMMUTABLE, and common pitfalls in using IMMUTABLE when you should use STABLE. svn: added troubleshooting steps for running `svn upgrade` after the last Mac system update, which upgrades svn.
- 10:55 AM Revision 12439: fix: schemas/util.sql: diff(text, text): use util.typeof() instead of pg_typeof() so the outputted query is runnable in any search_path
- 10:50 AM Revision 12438: schemas/util.sql: added typeof(), which unlike pg_typeof() uses util.qual_name() to create a search_path-independent name
- 10:33 AM Revision 12437: fix: schemas/util.sql: quote_typed(): schema-qualify the type so the expression does not depend on the search_path
- 10:31 AM Revision 12436: schemas/util.sql: %== : use custom keys() function for the type, so that the user only has to get the keys from their value, not also write the comparison of those keys
- 10:29 AM Revision 12435: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
- 10:12 AM Revision 12434: schemas/util.sql: diff(regclass, regclass): support custom %== by including the col_type_null's schema in the search_path
- 10:07 AM Revision 12433: schemas/util.sql: debug_print_return_value(): don't '-encode the value by default, because text values usually look better without the '-escaping
- 09:55 AM Revision 12432: bugfix: schemas/util.sql: mk_set_search_path(): debug_print_return_value() needs to be on mk_set_search_path(text), not mk_search_path(VARIADIC text[])
- 09:45 AM Revision 12431: schemas/util.sql: mk_search_path(): use debug_print_return_value() because this function is used with EXECUTE rather than util.eval() (in order to affect the calling function), so the search_path would not otherwise be printed
- 09:39 AM Revision 12430: schemas/util.sql: added debug_print_return_value()
- 09:34 AM Revision 12429: schemas/util.sql: diff(text, text): support custom search_path because custom %== operators are usually not in the util schema
- 09:28 AM Revision 12428: schemas/util.sql: diff(text, text): renamed IN params to be different from OUT params to support LANGUAGE plpgsql
- 09:04 AM Revision 12427: bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables
- 07:56 AM Revision 12426: /README.TXT: use full hostname for jupiter so the commands work outside of the NCEAS network as well
- 07:52 AM Revision 12425: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name
- 07:42 AM Revision 12424: bugfix: schemas/util.sql: diff(text, text) and types that use its custom FULL JOIN matching: need to use separate operator %== for determining which records to put on the same row, so that the = filter for identical rows only excludes rows that are actually the same, not all rows with the same *keys* (which would usually remove all rows)
- 07:21 AM Revision 12423: schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same
- 07:14 AM Revision 12422: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
- 12:24 AM Revision 12421: schemas/vegbien.sql: _plots_02_list_of_project_names: added = operator that causes FULL JOIN to only use some columns, so that rows for the same label column are put on the same row in the diff table
- 12:16 AM Revision 12420: schemas/util.sql: diff(): support custom = operator for the table type (to FULL JOIN on only some columns). this requires casting to the base type, to avoid an ambiguity with the default = operator for record.
02/24/2014
- 11:18 PM Revision 12419: schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type
- 11:17 PM Revision 12418: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
- 10:51 PM Revision 12417: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
- 05:16 PM Revision 12416: fix: schemas/vegbien.sql: rm_query_group(): schemas/vegbien.sql: util.drop_relations_like(): also need to drop ~type tables, so that they will be re-created for the new queries of that group
- 04:52 PM Revision 12415: schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix
- 04:50 PM Revision 12414: schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source
- 04:43 PM Revision 12413: schemas/util.sql: added drop_relations_like()
- 03:26 PM Revision 12412: fix: schemas/vegbien.sql: public_validations._plots_* that use projectname: re-alias to project_name to match input queries. (reexporting apparently changes the relative order of the ~type tables.)
- 02:34 PM Revision 12411: fix: schemas/vegbien.sql: _plots_03_count_of_all_plots_in_this_source: use top_plot instead of location because the corresponding input query includes just the top-level plots
- 02:33 PM Revision 12410: schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully
- 11:39 AM Revision 12409: web/links/index.htm: updated to Firefox bookmarks: Roundtable: virtual collaboration: annotated the linked pages
- 11:24 AM Revision 12408: web/links/index.htm: updated to Firefox bookmarks: Roundtable: added materials for virtual collaboration roundtable. Mac: added steps for customizing screen saver security settings beyond the options supported in the UI.
- 08:58 AM Revision 12407: inputs/SALVIAS/validations.sql: added Brad's comments from validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql
- 08:53 AM Revision 12406: added inputs/SALVIAS/validations*.sql
- 08:50 AM Revision 12405: bugfix: lib/common.Makefile: `svn add`: need to run with --force because the new version of svn in the latest Mac upgrade errors if the file is already under version control
- 08:37 AM Revision 12404: fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: renamed truncated views to match the output queries
- 12:30 AM Revision 12403: bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
- 12:28 AM Revision 12402: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: removed `LIMIT 10` to match output queries
- 12:26 AM Revision 12401: fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: updated table names to match our renamings
- 12:25 AM Revision 12400: schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables
- 12:24 AM Revision 12399: schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)
- 12:21 AM Revision 12398: bugfix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: can't double-JOIN like in MySQL (`JOIN a JOIN b ON a_conds AND b_conds`), so split double-JOIN into two JOINs w/ own ON clauses
- 12:16 AM Revision 12397: added validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql, modified from bien3_validations_salvias_db_original.sql using the steps at http://wiki.vegpath.org/Aggregating_validations_refactoring
02/23/2014
- 11:57 PM Revision 12396: fix: /README.TXT: use exact ssh command needed to connect to vegbiendev/jupiter (eg. `ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk`) instead of vaguely referring to "on vegbiendev"/"on jupiter"
- 11:28 PM Revision 12395: /README.TXT: Full database import: screen: run `unset TMOUT` first because it is most important, now that the remote servers have a TMOUT set for extra security
- 08:29 PM Revision 12394: bin/psql_verbose_vegbien: use \\ instead of \ inside '' because this is sh, not bash
- 08:26 PM Revision 12393: bin/psql_verbose_vegbien: changed prep-statement order to match lib/sh/db.sh psql()
- 08:26 PM Revision 12392: bin/psql_verbose_vegbien: use `\set VERBOSITY terse` to hide stack traces/DETAIL sections of error messages, like in lib/sh/db.sh psql()
- 08:11 PM Revision 12391: bin/make_analytical_db: added `public_validations.remake_diff_tables()`
- 08:03 PM Task #871 (New): grant bien_read access to validation result tables
- 08:02 PM Task #870 (New): grant bien_read access to publishable staging tables
- 07:57 PM Task #501 (Resolved): find out which datasources won't allow their data to be publicly accessible
- 07:44 PM Revision 12390: fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage
- 07:43 PM Revision 12389: schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources
- 07:40 PM Revision 12388: schemas/vegbien.sql: added validatable_datasources()
- 07:36 PM Revision 12387: schemas/vegbien.sql: added validatable_datasources()
- 07:31 PM Revision 12386: schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources
- 07:23 PM Revision 12385: schemas/util.sql: show_views_like(): take a schema regexp like util.show_relations_like(), to allow matching views in all schemas
- 07:19 PM Revision 12384: schemas/util.sql: added str_equality_regexp(literal text)
- 07:01 PM Revision 12383: schemas/vegbien.sql: public_validations.remake_diff_table(): put the type table in the public_validations schema to avoid cluttering up the datasource schema with internal tables
- 06:50 PM Revision 12382: schemas/util.sql: util.remake_diff_table(): don't force-re-create type_table. this allows type_table to be shared between multiple diff tables.
- 06:00 PM Revision 12381: /README.TXT: to back up the version history: added steps to sync git to the local machine
- 05:55 PM Revision 12380: added validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_complete.sql from Brad's e-mail
- 05:32 PM Revision 12379: schemas/vegbien.sql: public_validations.remake_diff_table(in_view regclass): document how to regenerate the diff table, in a place that's readily available to someone using the table
- 05:08 PM Revision 12378: schemas/util.sql: added curr_func(func text, schema_anchor anyelement)
- 03:50 PM Revision 12377: schemas/util.sql: removed unneeded regproc(regprocedure), regprocedure(regproc) because these can actually be directly cast to one another, not just via oid
- 03:47 PM Revision 12376: schemas/util.sql: added quote_func_call()
- 03:22 PM Revision 12375: schemas/util.sql: added regprocedure(text)
- 03:11 PM Revision 12374: schemas/util.sql: func2regproc*(): removed func2* so that this has the format of a cast function (which it is)
- 03:10 PM Revision 12373: schemas/util.sql: added func2regproc(regprocedure)
- 03:08 PM Revision 12372: schemas/util.sql: added func2regprocedure(regproc)
- 02:51 PM Revision 12371: schemas/util.sql: added quote_typed()
- 01:30 PM Revision 12370: schemas/util.sql: remake_diff_table(): use util.append_comment() instead of util.set_comment() to allow other functions to add comments to the diff table
- 01:18 PM Revision 12369: schemas/util.sql: added append_comment(regclass)
- 01:00 PM Revision 12368: schemas/util.sql: added comment(oid)
- 12:13 PM Revision 12367: fix: schemas/vegbien.sql: _traits_08_taxonname_trait_and_value_for_first_5000_records: renamed to _traits_08_taxonname_trait_and_value because this actually includes all the records, not just the first 5000. this uses the new public_validations.rename_query_view() to rename all associated tables and views, including handling truncated names.
- 12:08 PM Revision 12366: bugfix: schemas/vegbien.sql: query_relations(), new_query_view_relation_name(): need to take into account the prefix when determining truncation, using the max_prefix_len param
- 12:04 PM Revision 12365: schemas/vegbien.sql: added query_view_relation_max_prefix_len()
- 12:03 PM Revision 12364: fix: schemas/util.sql: drop_relation(text), rename_relation(text, text): appended _esc to params that should be passed in escaped, and _name to those that shouldn't
- 12:00 PM Revision 12363: bugfix: schemas/util.sql: rename_relation(): need quote_ident() around to_
- 11:39 AM Revision 12362: bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to match the entire string, so that the regexp can be used with util.replace_suffix()
- 11:37 AM Revision 12361: bugfix: schemas/util.sql: truncated_prefixed_name_regexp(): need to pass max_prefix_len to util.name_was_truncated() because although the name itself might not have been truncated, it might become truncated once prefixed
- 11:32 AM Revision 12360: schemas/util.sql: name_was_truncated(): support max_prefix_len param to determine truncation of something that will be prefixed
- 11:22 AM Revision 12359: fix: name_was_truncated(): should use util.namedatalen() instead of hardcoding the value
02/22/2014
- 08:35 PM Revision 12358: schemas/util.sql: replace_suffix(): support strings that have been truncated (eg. as a table name) due to the prepending of a prefix
- 08:32 PM Revision 12357: schemas/util.sql: added truncated_prefixed_name_regexp()
- 08:12 PM Revision 12356: schemas/util.sql: added rtrim_n()
- 07:58 PM Revision 12355: schemas/util.sql: added name_was_truncated()
- 07:56 PM Revision 12354: schemas/util.sql: added namedatalen()
- 07:21 PM Revision 12353: schemas/util.sql: drop_relation(regclass), rename_relation(regclass): use util.qual_name() instead of ::text so that the schema qualifier is always included in the debug SQL
- 07:18 PM Revision 12352: schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query
- 07:13 PM Revision 12351: schemas/vegbien.sql: added new_query_view_relation_name()
- 07:11 PM Revision 12350: schemas/util.sql: added replace_suffix()
- 06:47 PM Revision 12349: schemas/util.sql: added rename_relation(regclass/text, text)
- 06:23 PM Revision 12348: fix: schemas/util.sql: drop_relation(): documented that it is actually not idempotent, because the regclass will not be valid after the relation is dropped
- 06:21 PM Revision 12347: schemas/util.sql: drop_relation(relation text, type text): put type param first because it is usually constant
- 06:12 PM Revision 12346: schemas/util.sql: drop_table(), drop_view(): use drop_relation(relation text, type text)
- 06:10 PM Revision 12345: schemas/util.sql: drop_relation(regclass): use simpler drop_relation(relation text, type text)
- 06:06 PM Revision 12344: schemas/util.sql: added relation_type(regclass)
- 06:04 PM Revision 12343: schemas/util.sql: added drop_relation(relation text, type text)
- 06:01 PM Revision 12342: bugfix: schemas/util.sql: drop_relation(): need to pass force param to util.drop_*()
- 05:50 PM Revision 12341: schemas/util.sql: added relation_type_char(regclass)
- 05:50 PM Revision 12340: bugfix: schemas/util.sql: relation_type(character): must use "char" type for single character, as character actually allows multiple characters. corrected return type.
- 05:47 PM Revision 12339: schemas/util.sql: added relation_type(character)
- 04:04 PM Revision 12338: schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query
- 03:49 PM Revision 12337: fix: schemas/vegbien.sql: remake_diff_tables(): 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
- 03:48 PM Revision 12336: fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views
- 03:35 PM Revision 12335: schemas/vegbien.sql: added public_validations.query_views()
- 03:35 PM Revision 12334: schemas/util.sql: schema_matches(): should be IMMUTABLE, not STABLE, because it uses only parameter values
- 03:24 PM Revision 12333: schemas/util.sql: added regexp_quote()
- 03:18 PM Revision 12332: fix: schemas/util.sql: is_table(), is_view(): should be STABLE, not IMMUTABLE, because they reference tables
- 02:50 PM Revision 12331: schemas/util.sql: added drop_relation()
- 02:41 PM Revision 12330: schemas/util.sql: added is_table()
- 02:39 PM Revision 12329: schemas/util.sql: added is_view()
- 02:23 PM Revision 12328: bugfix: schemas/util.sql: show_views_like(): need to match the schema exactly, not via regexp like util.show_relations_like() does
- 02:14 PM Revision 12327: schemas/util.sql: show_views_like(): use util.show_relations_like()
- 02:03 PM Revision 12326: schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()
- 12:32 PM Revision 12325: schemas/util.sql: added show_relations_like()
- 12:21 PM Revision 12324: schemas/util.sql: added schema_matches()
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
Also available in: Atom