Project

General

Profile

Activity

From 02/22/2014 to 03/23/2014

03/23/2014

11:43 PM Revision 12873: bugfix: inputs/GBIF/table.run: switched to using lib/runscripts/table.run instead of mysql.table.run because some subdirs (Source/) need the regular table.run to work properly. mysql.table.run should instead be used directly by subdirs that use the MySQL install.
Aaron Marcuse-Kubitza

03/22/2014

06:20 AM Revision 12872: bugfix: lib/sh/util.sh: **DON'T** do `shopt -s lastpipe` because this causes a segfault on Linux in stderr_matches(). (it also isn't supported on Mac.) use @PIPESTATUS instead. note that we do not currently need lastpipe, since we use @PIPESTATUS (which actually provides more functionality for our purposes).
Aaron Marcuse-Kubitza
06:02 AM Revision 12871: fix: lib/sh/util.sh: echo_func(): file/line #: display with regular color because the lighter color actually draws attention *to* rather than *away from* the faded text
Aaron Marcuse-Kubitza
05:59 AM Revision 12870: lib/sh/util.sh: added plain()
Aaron Marcuse-Kubitza
05:56 AM Revision 12869: inputs/XAL/Specimen/test.xml.ref: updated for sample data.csv, which contains the columns as a CSV. this fixes a bug where a map.csv must be used on a table that contains the same set of columns (ie. not one with no columns if there are any mappings).
Aaron Marcuse-Kubitza
05:50 AM Revision 12868: bugfix: lib/sql_io.py: put_table(): is_literals: `return sql.value(cur): need to use sql.value_or_none() instead to support multi-row functions, such as _split() used in specimens data`
Aaron Marcuse-Kubitza
05:06 AM Revision 12867: fix: inputs/input.Makefile: don't treat *.xml as data files since these are not currently supported
Aaron Marcuse-Kubitza
04:55 AM Revision 12866: lib/runscripts/util.run: on_exit(): documented that users can also override gateway()/fallback() to perform other commands (or no commands) after the script is read
Aaron Marcuse-Kubitza
04:53 AM Revision 12865: bugfix: lib/sh/db.sh: pg_table_exists(): need ! to negate boolean result
Aaron Marcuse-Kubitza
04:44 AM Revision 12864: fix: lib/runscripts/table.run: table_make_install(): need to inform the user when it skips installing a table, because this is often unexpected
Aaron Marcuse-Kubitza
04:43 AM Revision 12863: fix: lib/runscripts/util.run: run_args_cmd(): need to indent the output of the target that it's running
Aaron Marcuse-Kubitza
04:15 AM Revision 12862: lib/runscripts/table.run: removed no longer used datasrc_make_install()
Aaron Marcuse-Kubitza
04:07 AM Revision 12861: fix: lib/sh/util.sh: fade(): use medium gray instead of light gray because it fades on white *and* black backgrounds
Aaron Marcuse-Kubitza
03:54 AM Revision 12860: lib/sh/util.sh: echo_func(): fade the file/line # to avoid distracting from the function call in the default log output
Aaron Marcuse-Kubitza
03:51 AM Revision 12859: lib/sh/util.sh: added fade()
Aaron Marcuse-Kubitza
03:37 AM Revision 12858: lib/sh/util.sh: highlight_msg(): renamed to highlight_log_msg() to clarify that this contains log++-specific functionality
Aaron Marcuse-Kubitza
03:35 AM Revision 12857: lib/sh/util.sh: moved terminal formatting commands to own section
Aaron Marcuse-Kubitza
03:34 AM Revision 12856: lib/sh/util.sh: highlight_msg(): moved formatting code into separate format() function
Aaron Marcuse-Kubitza
03:21 AM Revision 12855: lib/sh/util.sh: dp(): renamed to ps() to corresponding with pv/pf
Aaron Marcuse-Kubitza
03:19 AM Revision 12854: lib/sh/make.sh: echo_target: use `log-- echo_func`, which now puts the target name first but also provides much-needed indentation
Aaron Marcuse-Kubitza
03:16 AM Revision 12853: lib/sh/util.sh: echo_func(): put file/line # *after* function call instead of before so the function name is listed first
Aaron Marcuse-Kubitza
03:13 AM Revision 12852: lib/sh/util.sh: echo_func(): usage: removed no longer used/implemented minor=1 switch. use log++ instead.
Aaron Marcuse-Kubitza
03:07 AM Revision 12851: lib/runscripts/datasrc_dir.run: import(): use new schema/make, schema/rm
Aaron Marcuse-Kubitza
02:59 AM Revision 12850: lib/runscripts/table.run: load_data(): use the much simpler `schema/make` run target, rather than outsourcing to the legacy Makefile via the convoluted datasrc_make_install()/table_make_install()
Aaron Marcuse-Kubitza
02:26 AM Revision 12849: lib/runscripts/datasrc_dir.run: added schema/rm(), schema/make()
Aaron Marcuse-Kubitza
02:19 AM Revision 12848: lib/sh/util.sh: ignore_err_msg(): usage: added $ignore_e param from stderr_matches()
Aaron Marcuse-Kubitza
02:14 AM Revision 12847: lib/runscripts/table.run: psql: always include ; at end of statement
Aaron Marcuse-Kubitza
01:39 AM Revision 12846: fix: lib/sh/db.sh: pg_cmd(): hide PGPASSWORD at the normal verbosity so that the value of it doesn't appear in any log files
Aaron Marcuse-Kubitza
01:08 AM Revision 12845: lib/sh/util.sh: log_hint(): renamed to log_err_hint() for clarity, because this applies only to hints for errors
Aaron Marcuse-Kubitza
01:06 AM Revision 12844: bugfix: lib/sh/util.sh: log_hint!(): use log_err instead of log_info because hints as used here are attached to (possibly benign) errors. for other uses, use mk_hint().
Aaron Marcuse-Kubitza
01:00 AM Revision 12843: fix: lib/sh/util.sh: highlight_msg(): don't ' '-pad already-formatted text
Aaron Marcuse-Kubitza
12:57 AM Revision 12842: lib/sh/util.sh: manual terminal escape sequences: use highlight_msg() instead
Aaron Marcuse-Kubitza
12:53 AM Revision 12841: lib/sh/util.sh: highlight_msg(): auto-add padding around text if there is a background
Aaron Marcuse-Kubitza
12:51 AM Revision 12840: lib/sh/util.sh: highlight_msg(): use $format itself as the $highlight boolean
Aaron Marcuse-Kubitza
12:48 AM Revision 12839: lib/sh/util.sh: highlight_msg(): split apart the testing of $format and can_highlight_msg
Aaron Marcuse-Kubitza
12:39 AM Revision 12838: lib/sh/util.sh: added has_bg()
Aaron Marcuse-Kubitza
12:28 AM Revision 12837: bugfix: lib/sh/util.sh: highlight_msg(): need to reset any existing formatting before applying new formatting
Aaron Marcuse-Kubitza
12:25 AM Revision 12836: lib/sh/util.sh: added mk_hint() and use it in log_hint!()
Aaron Marcuse-Kubitza
12:16 AM Revision 12835: lib/sh/util.sh: bg_cmd(): also log the command being run
Aaron Marcuse-Kubitza
12:07 AM Revision 12834: fix: lib/sh/util.sh: need `function` before functions that have an alias with the same name
Aaron Marcuse-Kubitza
12:04 AM Revision 12833: lib/sh/util.sh: log!(): use new log:()
Aaron Marcuse-Kubitza
12:00 AM Revision 12832: lib/sh/util.sh: added log:(), which sets an explicit log_level. this also simplifies log+().
Aaron Marcuse-Kubitza

03/21/2014

11:55 PM Revision 12831: lib/sh/util.sh: log+(): set log_level before PS4 so that the PS4 expr doesn't also need to add to log_level
Aaron Marcuse-Kubitza
11:51 PM Revision 12830: lib/sh/util.sh: removed no longer needed log+ alias (which had been renamed from clog+)
Aaron Marcuse-Kubitza
11:48 PM Revision 12829: lib/sh/util.sh: clog*: renamed to log* for clarity (possible now that log* is no longer used for function-local log_level setting)
Aaron Marcuse-Kubitza
11:44 PM Revision 12828: *{.sh,run}: local setting of log_level: use log_local instead of relying on the log* aliases, so that these aliases can instead be used for wrapping commands (the more common use case)
Aaron Marcuse-Kubitza
11:40 PM Revision 12827: bugfix: lib/sh/util.sh: verbosity_compat alias: need to use `declare verbosity="$verbosity"` instead of `declare verbosity`, which would just clear $verbosity
Aaron Marcuse-Kubitza
11:38 PM Revision 12826: bugfix: lib/sh/util.sh: verbosity_min alias: need to use `declare verbosity="$verbosity"` instead of log_local now that verbosity is not one of the vars changed by log++
Aaron Marcuse-Kubitza
11:30 PM Revision 12825: lib/sh/util.sh: log+(): use easier-to-understand log_local instead of prefix-assignments to limit assignments to the invoked command
Aaron Marcuse-Kubitza
11:30 PM Revision 12824: lib/sh/util.sh: log+(): use easier-to-understand log_local instead of prefix-assignments to limit assignments to the invoked command
Aaron Marcuse-Kubitza
10:57 PM Revision 12823: *{.sh,run}: use clog* instead of "log*"
Aaron Marcuse-Kubitza
10:45 PM Revision 12822: bugfix: lib/sh/util.sh: log+(): removed spurious ; between setting of PS4 and log_level, which was causing erratic mismatches between PS4 and log_level. (the ; caused $PS4 to be set in the *caller* when invoked via one of the clog* aliases, rather than being passed as a command-specific env var.)
Aaron Marcuse-Kubitza
10:30 PM Revision 12821: lib/sh/util.sh: $verbosity: stay constant at what the user set it to instead of changing in tandem with $log_level, to facilitate debugging verbosity/log_level-related issues
Aaron Marcuse-Kubitza
10:11 PM Revision 12820: lib/sh/util.sh: log+(): usage: use aliases instead of ""-ed function names
Aaron Marcuse-Kubitza
06:58 PM Revision 12819: added schemas/VegCore.ERD.pdf symlink for easy access
Aaron Marcuse-Kubitza
06:50 PM Revision 12818: lib/sh/util.sh: log_err(): use red background for better visibility of errors, in the same way that lib/exc.py print_ex() does for column-based import
Aaron Marcuse-Kubitza
06:44 PM Revision 12817: bugfix: lib/sh/util.sh: removed echo_func in functions used by log++, to avoid spurious highlighted output
Aaron Marcuse-Kubitza
06:40 PM Revision 12816: lib/sh/util.sh: added missing clog+ alias
Aaron Marcuse-Kubitza
06:35 PM Revision 12815: bugfix: lib/sh/util.sh: log_hint(): use the standard log_fd and log_info() format, not err_fd and log_err() format, for hint messages
Aaron Marcuse-Kubitza
06:27 PM Revision 12814: fix: lib/sh/util.sh: log_msg!(): indent each line, not just the first
Aaron Marcuse-Kubitza
06:26 PM Revision 12813: lib/sh/util.sh: added split_lines()
Aaron Marcuse-Kubitza
06:05 PM Revision 12812: lib/sh/util.sh: log(): factored out helper function log_msg!()
Aaron Marcuse-Kubitza
06:00 PM Revision 12811: fix: lib/sh/util.sh: highlight_msg(): bold instead of underlining because the underlining interferes with the readability of the commands
Aaron Marcuse-Kubitza
05:57 PM Revision 12810: lib/sh/util.sh: highlight_msg(): allow turning off formatting w/ empty $format
Aaron Marcuse-Kubitza
05:53 PM Revision 12809: fix: lib/sh/util.sh: log_err() calls: removed manual highlighting
Aaron Marcuse-Kubitza
05:51 PM Revision 12808: lib/sh/util.sh: log_err(): highlight all error messages using highlight_msg()'s new $format
Aaron Marcuse-Kubitza
05:45 PM Revision 12807: lib/sh/util.sh: highlight_msg(): support custom format
Aaron Marcuse-Kubitza
05:35 PM Revision 12806: lib/sh/db.sh: pg_*_exists(): log the DB statements to check this at a higher log_level so that they don't clutter up the log output
Aaron Marcuse-Kubitza
05:25 PM Revision 12805: lib/sh/util.sh: log(): highlight log_level 1 messages to stand out against other output, for easier debugging
Aaron Marcuse-Kubitza
04:31 PM Revision 12804: *{.sh,run}: stderr_matches() wrapper calls: removed no longer needed prep_try/rethrow
Aaron Marcuse-Kubitza
04:12 PM Revision 12803: bugfix: catch(): also need to support $1='' because this is a now a use case of ignore_e()
Aaron Marcuse-Kubitza
04:02 PM Revision 12802: bugfix: lib/sh/util.sh: ignore_err_msg(): also need to ignore false exit status on no match
Aaron Marcuse-Kubitza
03:49 PM Revision 12801: lib/sh/util.sh: stderr_matches(): moved prep_try/rethrow into the function itself so that callers don't have to wrap this function in a complex sequence of prep_try/rethrow statements
Aaron Marcuse-Kubitza
03:42 PM Revision 12800: *{.sh,run}: stderr_matches() wrapper calls: removed no longer needed prep_try/rethrow
Aaron Marcuse-Kubitza
03:42 PM Revision 12799: lib/sh/util.sh: stderr_matches(): moved prep_try/rethrow into the function itself so that callers don't have to wrap this function in a complex sequence of prep_try/rethrow statements
Aaron Marcuse-Kubitza
03:25 PM Revision 12798: lib/sh/util.sh: added rethrow_exit alias
Aaron Marcuse-Kubitza
03:10 PM Revision 12797: fix: lib/sh/db.sh: pg_table_exists(): use stderr_matches() rather than just the exit status. this also avoids highlighting the benign error.
Aaron Marcuse-Kubitza
03:00 PM Revision 12796: fix: lib/sh/db.sh: pg_table_exists(): use stderr_matches() rather than just the exit status. this also avoids highlighting the benign error.
Aaron Marcuse-Kubitza
02:16 AM Revision 12795: fix: inputs/input.Makefile: removed no longer used special handling of XML inputs, support for which was never added to the Makefile. (bin/map, however, does support importing an XML file into a database.) this fixes a bug in XAL, which used to abort with an error but now just imports an empty table.
Aaron Marcuse-Kubitza
12:34 AM Revision 12794: fix: inputs/input.Makefile: %/install: don't ignore errors if table does not exist, to ensure a proper errexit. this is now possible because every dir that this target is being run on should be a data dir. (Source/ used to be a metadata-only dir.)
Aaron Marcuse-Kubitza
12:31 AM Revision 12793: bugfix: inputs/input.Makefile: $(cleanup): need `set -o pipefail`
Aaron Marcuse-Kubitza
12:02 AM Revision 12792: inputs/VegBank/run: `rm=1 import()`: updated runtime (1 h)
Aaron Marcuse-Kubitza

03/20/2014

11:54 PM Revision 12791: inputs/VegBank/taxon_observation.**/test.xml.ref: updated inserted row count
Aaron Marcuse-Kubitza
11:54 PM Revision 12790: inputs/VegBank/projectcontributor_/test.xml.ref: updated inserted row count
Aaron Marcuse-Kubitza
10:53 PM Revision 12789: bugfix: schemas/util.sql: is_constant(util.col_ref): updated to include standard newline at beginning of comment (applies to newly-imported staging tables)
Aaron Marcuse-Kubitza
10:44 PM Revision 12788: bugfix: inputs/VegBank/import_order.txt: added missing project, needed to trigger the staging table renaming for the project table
Aaron Marcuse-Kubitza
10:42 PM Revision 12787: inputs/VegBank/run: documented `rm=1 import()` runtime (>1.5 h)
Aaron Marcuse-Kubitza
10:40 PM Revision 12786: inputs/VegBank/run: documented `datasrc_make sql/install` runtime (25 min)
Aaron Marcuse-Kubitza
08:27 PM Revision 12785: inputs/MO/Specimen/test.xml.ref: updated, which adds dateCollected mappings
Aaron Marcuse-Kubitza
08:20 PM Revision 12784: inputs/WIN/Specimen/test.xml.ref: updated to map.csv, which has eventDate->dateCollected
Aaron Marcuse-Kubitza
08:13 PM Revision 12783: inputs/VegBank/plantconcept_/create.sql: updated runtime (25 min, ~same)
Aaron Marcuse-Kubitza
08:08 PM Revision 12782: lib/sh/make.sh: begin_target: echo all targets to facilitate debugging without needing the verbose stack trace mode
Aaron Marcuse-Kubitza
08:06 PM Revision 12781: bugfix: lib/sh/make.sh: echo_target: don't include filename/line #, since this is not for the stack trace mode
Aaron Marcuse-Kubitza
07:59 PM Revision 12780: lib/sh/make.sh: added echo_target
Aaron Marcuse-Kubitza
07:58 PM Revision 12779: *{.sh,run}: use new begin_target instead of `echo_func; set_make_vars`
Aaron Marcuse-Kubitza
07:51 PM Revision 12778: lib/runscripts/util.run: runscript template: added sample make target, using new make target template
Aaron Marcuse-Kubitza
07:48 PM Revision 12777: lib/sh/make.sh: added make target template
Aaron Marcuse-Kubitza
07:47 PM Revision 12776: inputs/VegBank/plot/postprocess.sql: remove institutions that we have direct data for: CVS: updated runtime (same)
Aaron Marcuse-Kubitza
07:41 PM Revision 12775: lib/sh/make.sh: added begin_target alias
Aaron Marcuse-Kubitza
07:17 PM Revision 12774: lib/runscripts/datasrc_dir.run: documented how to reinstall staging tables (`rm=1 .../run import`)
Aaron Marcuse-Kubitza
07:13 PM Revision 12773: bugfix: *{.sh,run}: stderr_matches() wrapper callers: use the required wrapper caller usage, which now includes rethrow and prep_try
Aaron Marcuse-Kubitza
07:09 PM Revision 12772: bugfix: lib/sh/util.sh: rethrow*: only `return` if $e is actually nonzero, because rethrow is now being used as a catch-all in situations where there might not be an error
Aaron Marcuse-Kubitza
07:06 PM Revision 12771: lib/sh/util.sh: prep_try: initialize $e to 0 to simplify error-handling coding
Aaron Marcuse-Kubitza
06:56 PM Revision 12770: stderr_matches(): wrapper caller usage: added alternative usage when using `||`
Aaron Marcuse-Kubitza
06:50 PM Revision 12769: lib/sh/util.sh: stderr_matches(): wrapper caller usage: documented usage for a negated condition (ie. prefixed w/ !)
Aaron Marcuse-Kubitza
06:48 PM Revision 12768: lib/sh/util.sh: stderr_matches(): usage: split into wrapper usage and wrapper caller usage for clarity
Aaron Marcuse-Kubitza
06:45 PM Revision 12767: fix: *{.sh,run}: stderr_matches() wrappers: usage: added `rethrow`
Aaron Marcuse-Kubitza
06:45 PM Revision 12766: fix: *{.sh,run}: stderr_matches() wrappers: usage: added `rethrow`
Aaron Marcuse-Kubitza
06:43 PM Revision 12765: fix: lib/sh/util.sh: stderr_matches(): usage: `rethrow` must be called right after stderr_matches(), to avoid calling running other commands if there is an error
Aaron Marcuse-Kubitza
06:40 PM Revision 12764: fix: lib/sh/util.sh: stderr_matches(): when using $ignore_e, also set benign_error=1 to suppress the highlighting of the error
Aaron Marcuse-Kubitza
06:36 PM Revision 12763: bugfix: lib/sh/db.sh: pg_schema_exists(): need to ignore benign error exit status from the "cannot create temporary relation in non-temporary schema" error
Aaron Marcuse-Kubitza
06:34 PM Revision 12762: lib/sh/util.sh: stderr_matches(): supporting ignoring any benign error exit status associated with the error message being tested for
Aaron Marcuse-Kubitza
06:18 PM Revision 12761: lib/sh/util.sh: stderr_matches(): usage: documented where any ignore_e statement would go
Aaron Marcuse-Kubitza
05:37 PM Revision 12760: bugfix: lib/sh/util.sh: stderr_matches(): can't use `try` because this clears the exit status, which is needed for @PIPESTATUS to work. to support this, also need to avoid errexiting since @PIPESTATUS will be used instead.
Aaron Marcuse-Kubitza
01:25 AM Task #878 (New): fix crow's foot notation in ERD
* when there is an open circle on the straight end, also put an open circle on the crow's foot end, so that the outgo... Aaron Marcuse-Kubitza

03/18/2014

06:18 PM Revision 12759: lib/sh/util.sh: added dp(), which debug-prints a message
Aaron Marcuse-Kubitza
05:47 PM Revision 12758: bugfix: inputs/VegBank/plot/postprocess.sql: use CVS.plot_ instead because that has the renamed staging table columns, and is compatible with auto-renaming of the SQL script columns
Aaron Marcuse-Kubitza
05:41 PM Revision 12757: inputs/CVS/plot_/postprocess.sql: add unique constraint on locationName (analogous to the unique constraint in plot), for use by inputs/VegBank/plot/postprocess.sql in removing inter-datasource duplication
Aaron Marcuse-Kubitza
05:26 PM Revision 12756: fix: schemas/util.sql: explain2notice_msg(): don't include EXPLAIN output for simple, single-value queries, to avoid cluttering up the log output
Aaron Marcuse-Kubitza
05:22 PM Revision 12755: schemas/util.sql: added fold_explain_msg()
Aaron Marcuse-Kubitza
05:22 PM Revision 12754: bugfix: bin/repl: only use excluded_prefix_re/excluded_suffix_re in text mode (used in renaming columns in SQL scripts), to prevent the special coding for column renames from also affecting regular regexp/word replacements
Aaron Marcuse-Kubitza
05:10 PM Revision 12753: inputs/VegBank/taxon_observation.**/test.xml.ref: updated inserted row count
Aaron Marcuse-Kubitza
05:34 AM Revision 12752: inputs/run: postprocess(): documented runtime (30 min)
Aaron Marcuse-Kubitza
05:16 AM Revision 12751: bugfix: inputs/input.Makefile: %/postprocess.sql: don't perform replacements using map.csv, because map.csv is not idempotent. this functionality was only there to facilitate switching to new-style import, which is now largely done. (the remaining datasources NVS, SALVIAS, TEAM contain only 1 postprocess.sql: inputs/SALVIAS/projects/postprocess.sql (`st inputs/{NVS,SALVIAS,TEAM}/*/postprocess.sql`).)
Aaron Marcuse-Kubitza
04:59 AM Revision 12750: bugfix: bin/repl: text mode: also don't match if it's part of a '-'-separated identifier
Aaron Marcuse-Kubitza
04:57 AM Revision 12749: bugfix: bin/repl: text mode: also don't match if it's a word in a sentence
Aaron Marcuse-Kubitza
04:42 AM Revision 12748: bugfix: bin/repl: text mode: turned off the suffix matching, because there are cases where a mapping adds a suffix which would cause the same replacement to be performed repeatedly
Aaron Marcuse-Kubitza
04:33 AM Revision 12747: inputs/input.Makefile: %/postprocess.sql: *always* run this, not just if the associated map spreadsheets change, to avoid needing to `touch` them to cause %/postprocess.sql to run
Aaron Marcuse-Kubitza
04:25 AM Revision 12746: bin/repl: text mode: exclude prefixes that should not cause replacement, to avoid doubling leading *
Aaron Marcuse-Kubitza
04:24 AM Revision 12745: fix: inputs/*/*/postprocess.sql: un-doubled *
Aaron Marcuse-Kubitza
04:06 AM Revision 12744: bugfix: inputs/input.Makefile: %/postprocess.sql: also need to apply renames from mappings/VegCore.thesaurus.csv, as these have been applied to map.csv
Aaron Marcuse-Kubitza
04:04 AM Revision 12743: bugfix: lib/runscripts/table.run: custom_postprocess(): need to apply renames to SQL statements in postprocess.sql before it can be run
Aaron Marcuse-Kubitza
04:03 AM Revision 12742: bin/repl: text mode: also match w/ suffix (eg. _verbatim)
Aaron Marcuse-Kubitza
03:10 AM Task #577 (Rejected): use views instead of map spreadsheets to store the datasource mappings
the staging table columns are now renamed instead of creating a view that maps the columns Aaron Marcuse-Kubitza
03:07 AM Task #584 (Resolved): enable running all the import steps from one runscript
Aaron Marcuse-Kubitza
02:59 AM Revision 12741: bugfix: /README.TXT: Maintenance: VegCore data dictionary: apply new data dict mappings: need to use postprocess rather than import runscript target, so that the command also works on an svn checkout without the flat files (the flat files are not needed for the staging table renaming)
Aaron Marcuse-Kubitza

03/15/2014

07:20 PM Revision 12740: lib/sh/db.sh: psql(): $verbose_ok: renamed to $bypass_ok for clarity, because this applies only to the `--output /dev/fd/41` bypass (which when not possible, requires turning off verbose output
Aaron Marcuse-Kubitza
07:15 PM Revision 12739: fix: lib/sh/db.sh: psql(): added $output_data switch analogous to what mysql() has. this causes query results of eg. void-returning functions to be correctly filtered by the logging mechanism, rather than output to stdout.
Aaron Marcuse-Kubitza
06:42 PM Revision 12738: fix: lib/sh/db.sh: psql(): verbosity=0 (errors only) mode: use `SET client_min_messages = WARNING;` instead of NOTICE to hide verbose messages within psql as well
Aaron Marcuse-Kubitza
06:31 PM Revision 12737: lib/sh/db.sh: psql(): replaced `test "$verbose_ok" && can_log` with bool var $verbose_
Aaron Marcuse-Kubitza
06:29 PM Revision 12736: fix: lib/sh/db.sh: psql(): $verbose_: renamed to $verbose_ok for clarity
Aaron Marcuse-Kubitza
06:13 PM Revision 12735: fix: lib/sh/util.sh: stdout_contains(): add another pipe_delay because the `grep` statement was sometimes getting printed before its filtered output
Aaron Marcuse-Kubitza
05:47 PM Revision 12734: bugfix: schemas/util.sql: set_col_types(): need to COALESCE() the executed SQL to '' because util.eval() does not support NULL (and shouldn't, because this indicates a missing COALESCE() in constructing the statement)
Aaron Marcuse-Kubitza
05:43 PM Revision 12733: schemas/util.sql: set_col_types(): use simpler util.eval() instead of manual EXECUTE/util.debug_print_sql()
Aaron Marcuse-Kubitza
05:37 PM Revision 12732: schemas/util.sql: set_col_types(): use string_agg() instead of array_to_string(ARRAY(...)) for clarity
Aaron Marcuse-Kubitza
05:28 PM Revision 12731: bugfix: lib/sh/util.sh: die_error_hidden(): min verbosity to display error should not be hardcoded
Aaron Marcuse-Kubitza
05:18 PM Revision 12730: lib/sh/db.sh: psql(): "to see error details" msg: use new die_error_hidden()
Aaron Marcuse-Kubitza
05:18 PM Revision 12729: lib/sh/util.sh: added die_error_hidden()
Aaron Marcuse-Kubitza
05:13 PM Revision 12728: lib/sh/db.sh: psql(): "to see error details" msg: use new log_hint()
Aaron Marcuse-Kubitza
05:13 PM Revision 12727: lib/sh/util.sh: added log_hint(), whose msg is only displayed if not a benign error
Aaron Marcuse-Kubitza
05:03 PM Revision 12726: bugfix: lib/sh/db.sh: psql(): "to see error details" msg: also don't print it for benign errors ($benign_error)
Aaron Marcuse-Kubitza
05:00 PM Revision 12725: schemas/util.sql: added mk_not_null()
Aaron Marcuse-Kubitza
04:42 PM Revision 12724: lib/sh/db.sh: psql(): on error, display message describing how to see error details (prepend `vb=2` to the command)
Aaron Marcuse-Kubitza
04:31 PM Revision 12723: bugfix: lib/sh/util.sh: log_err(): don't override verbosity manually, as this will not set log_level or PS4. instead, use new log! , which sets these correctly.
Aaron Marcuse-Kubitza
04:24 PM Revision 12722: lib/sh/util.sh: added log! , which force-displays next log message
Aaron Marcuse-Kubitza
03:59 PM Revision 12721: lib/sh/util.sh: save_e: made it idempotent so that it also works if save_e was already called
Aaron Marcuse-Kubitza
03:57 PM Revision 12720: lib/sh/util.sh: save_e: made it idempotent so that it also works if save_e was already called
Aaron Marcuse-Kubitza
03:37 PM Revision 12719: lib/sh/util.sh: rethrow: documented why can't use `(exit "$e")` (bash bug that prevents errexit)
Aaron Marcuse-Kubitza

03/14/2014

09:09 PM Revision 12718: bugfix: /README.TXT: Maintenance: VegCore data dictionary: apply new data dict mappings: need to use import rather than mappings runscript target, to rename the staging tables
Aaron Marcuse-Kubitza
09:06 PM Revision 12717: bugfix: /README.TXT: Maintenance: VegCore data dictionary: also need to apply new data dict mappings on vegbiendev
Aaron Marcuse-Kubitza
08:19 PM Revision 12716: fix: /README.TXT: Maintenance: VegCore data dictionary: added steps to apply the new data dictionary mappings to the datasource mappings and staging tables
Aaron Marcuse-Kubitza
07:53 PM Revision 12715: bugfix: lib/runscripts/util.run: $auto_ignore: need to unexport it so don't pass this to invoked scripts except through fwd()
Aaron Marcuse-Kubitza
07:35 PM Revision 12714: added inputs/run, which runs all the inputs' runscripts using the new auto-forwarding
Aaron Marcuse-Kubitza
07:34 PM Revision 12713: bugfix: lib/runscripts/util.run: auto_fwd's fallback() must be set *after* auto_ignore's fallback() to overwrite it (auto_ignore should only apply if an error would otherwise have been generated by the fallback)
Aaron Marcuse-Kubitza
07:30 PM Revision 12712: lib/runscripts/util.run: fwd(): support subdirs that don't contain a runscript, so that the default value of @subdirs will work in most cases
Aaron Marcuse-Kubitza
07:29 PM Revision 12711: lib/runscripts/util.run: fwd(): set default @subdirs (`{.,}*/`)
Aaron Marcuse-Kubitza
07:26 PM Revision 12710: lib/sh/util.sh: added enter_top_dir and use it in in_top_dir
Aaron Marcuse-Kubitza
07:12 PM Revision 12709: fix: lib/sh/util.sh: commands run inside $(...): need to run with log++ so that these aren't normally debug-printed
Aaron Marcuse-Kubitza
06:41 PM Revision 12708: lib/sh/util.sh: added pv(), which debug-prints var(s)
Aaron Marcuse-Kubitza
06:40 PM Revision 12707: lib/sh/util.sh: added wildcard.()
Aaron Marcuse-Kubitza
06:40 PM Revision 12706: lib/sh/util.sh: added wildcard/()
Aaron Marcuse-Kubitza
06:40 PM Revision 12705: lib/sh/util.sh: added esc_args()
Aaron Marcuse-Kubitza
06:33 PM Revision 12704: web/links/index.htm: updated to Firefox bookmarks: Google Drive: listed bugs that make it very difficult to use (the need to re-download all files when reconnecting a client to an account). added recommendation not to use it (unstable).
Aaron Marcuse-Kubitza
05:25 PM Revision 12703: removed unused inputs/table.run. inputs/*/table.run include lib/runscripts/table.run directly.
Aaron Marcuse-Kubitza
05:02 PM Revision 12702: lib/runscripts/datasrc_dir.run: removed postprocess(), which now does the same thing its auto-forwarded equivalent would
Aaron Marcuse-Kubitza
05:01 PM Revision 12701: lib/runscripts/datasrc_dir.run: removed separate @table_subdirs, because the table-only targets can now safely be invoked on all subdirs, being auto-ignored in subdirs that don't support them
Aaron Marcuse-Kubitza
04:53 PM Revision 12700: lib/runscripts/util.run: fwd(): enable $auto_ignore so that each subdir doesn't have to have a definition for the forwarded target
Aaron Marcuse-Kubitza
04:52 PM Revision 12699: lib/runscripts/util.run: added $auto_ignore switch, which causes fallback() not to generate an error that a non-existant target doesn't exist
Aaron Marcuse-Kubitza
03:55 PM Revision 12698: lib/runscripts/datasrc_dir.run: use new fwd_self alias
Aaron Marcuse-Kubitza
03:55 PM Revision 12697: lib/runscripts/util.run: added fwd_self alias
Aaron Marcuse-Kubitza
03:49 PM Revision 12696: lib/runscripts/datasrc_dir.run: enable $auto_fwd, to create the functionality of lib/forwarding.Makefile's `%` target
Aaron Marcuse-Kubitza
03:47 PM Revision 12695: lib/runscripts/util.run: added $auto_fwd switch
Aaron Marcuse-Kubitza
03:36 PM Revision 12694: bugfix: lib/runscripts/util.run: gateway(): need to use is_callable() rather than func_exists() to check whether the target exists, because external commands (eg. echo) are supported as targets, too
Aaron Marcuse-Kubitza
03:32 PM Revision 12693: lib/sh/util.sh: added is_callable()
Aaron Marcuse-Kubitza
03:23 PM Revision 12692: lib/runscripts/util.run: support custom handlers for *all* targets (gateway()) as well as targets w/o function (fallback())
Aaron Marcuse-Kubitza
03:03 PM Revision 12691: lib/runscripts/table.run: remake_VegBIEN_mappings(): renamed to just mappings() since action make targets should be short names
Aaron Marcuse-Kubitza
07:32 AM Revision 12690: lib/sh/util.sh: stderr_matches(): inline the stderr_matches alias to avoid needing to quote stderr_matches as "stderr_matches" in the most common use case (with pattern as a prefix env var)
Aaron Marcuse-Kubitza
07:29 AM Revision 12689: bugfix: lib/sh/util.sh: stderr_matches(): when passing `pattern=...` as a prefix env var, must be invoked as `"stderr_matches"` to avoid the env var applying to the prep_try portion of the stderr_matches alias
Aaron Marcuse-Kubitza
06:38 AM Revision 12688: added schemas/VegCore/Brad_Boyle/bien3_data_provenance_use_cases.docx* from e-mail from Brad
Aaron Marcuse-Kubitza

03/13/2014

06:53 PM Revision 12687: schemas/vegbien.sql: _plots_08_list_of_plots_which_use_percent_cover, _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: reran with fixes, which removes the incorrectly auto-added copies columns. (they were only able to be auto-added because the tables had no rows.)
Aaron Marcuse-Kubitza
06:42 PM Revision 12686: bugfix: drop_column(regclass[]): need to run `SELECT NULL::void;` at end of function to avoid folding away functions called in previous query
Aaron Marcuse-Kubitza
06:40 PM Revision 12685: fix: schemas/util.sql: diff(regclass, regclass): moved try_create() of copies column in parent table to auto_rm_freq() so that it would only happen if both tables actually contain a copies column (otherwise, the try_create() will create an empty copies column if both tables are empty)
Aaron Marcuse-Kubitza
06:33 PM Revision 12684: schemas/util.sql: try_create(): also handle "child table is missing column" errors
Aaron Marcuse-Kubitza
05:33 PM Revision 12683: schemas/util.sql: added coalesce(anyarray), which can be used to force evaluation of all values of a COALESCE()
Aaron Marcuse-Kubitza
05:14 PM Revision 12682: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
05:13 PM Revision 12681: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
Aaron Marcuse-Kubitza
05:04 PM Revision 12680: schemas/vegbien.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
Aaron Marcuse-Kubitza
05:03 PM Revision 12679: inputs/SALVIAS/validations.sql: implemented _plots_19_count_of_censuses_per_plot_in_each_project
Aaron Marcuse-Kubitza
09:08 AM Revision 12678: validation/aggregating/plots/FIA/bien3_validations_fia_input.sql: _plots_19_count_of_inventories_per_plot_in_each_project: renamed to _plots_19_count_of_censuses_per_plot_in_each_project for clarity
Aaron Marcuse-Kubitza
09:00 AM Revision 12677: validation/aggregating/plots/FIA/bien3_validations_fia_input.sql*: updated from Brad's latest e-mail
Aaron Marcuse-Kubitza
02:06 AM Revision 12676: schemas/util.sql: EXCEPTION blocks with multiple exception types: use OR to merge exception types into one WHEN block
Aaron Marcuse-Kubitza
01:50 AM Revision 12675: schemas/vegbien.sql: public_validations: schema comment: changed "to sync the queries with schemas/vegbien.sql" to "to reset the queries to what's in schemas/vegbien.sql" for clarity
Aaron Marcuse-Kubitza
01:46 AM Revision 12674: fix: schemas/vegbien.sql: schema comment: to reset the key and value columns for all validations queries: updated running of custom keys() functions to use keys() types instead
Aaron Marcuse-Kubitza
01:14 AM Revision 12673: schemas/vegbien.sql: schema comment: to sync the queries with schemas/vegbien.sql: use new public_validations.rm_output_queries() instead of rm_all_queries() to leave the input queries in place
Aaron Marcuse-Kubitza
01:12 AM Revision 12672: schemas/vegbien.sql: schema comment: documented how to reset the key and value columns for all validations queries
Aaron Marcuse-Kubitza

03/12/2014

11:56 PM Revision 12671: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): indicate in the type comment that the keys() type is autogenerated, so it can be distinguished from custom keys() types when bulk-regenerating keys() types
Aaron Marcuse-Kubitza
11:53 PM Revision 12670: bugfix: schemas/util.sql: show_relations_like(): also need to include composite types, as these are also relations (and are expected to be included by callers of show_relations_like())
Aaron Marcuse-Kubitza
11:49 PM Revision 12669: bugfix: schemas/vegbien.sql: rm_output_queries(): also need to include keys_* and values__* types, as these are also associated with the query
Aaron Marcuse-Kubitza
11:40 PM Revision 12668: schemas/util.sql: added debug_print_func_call(text) and use it where applicable
Aaron Marcuse-Kubitza
11:33 PM Revision 12667: schemas/util.sql: drop_relations_like(): debug-print the regexps so that you can tell which tables it's trying to match
Aaron Marcuse-Kubitza
06:26 PM Revision 12666: schemas/vegbien.sql: public_validations: regenerated ~type tables, which adds `copies` columns for queries with a mismatch in the # of occurrences of each row
Aaron Marcuse-Kubitza
06:18 PM Revision 12665: bugfix: schemas/vegbien.sql: public_validations.validation_views(): need to include views with letters after the query # (eg. _plots_06a_list_of_stems)
Aaron Marcuse-Kubitza
05:41 PM Revision 12664: schemas/util.sql: removed no longer used to_freq(regclass, drop_if_always_1). use to_freq(regclass) and auto_rm_freq() instead.
Aaron Marcuse-Kubitza
05:40 PM Revision 12663: bugfix: schemas/util.sql: diff(regclass, regclass): only drop freq column if *all* tables have all 1s
Aaron Marcuse-Kubitza
05:38 PM Revision 12662: schemas/util.sql: auto_rm_freq(): accept multiple tables, so the freq column is only dropped if *all* tables have all 1s
Aaron Marcuse-Kubitza
05:36 PM Revision 12661: schemas/util.sql: added freq_always_1(regclass[])
Aaron Marcuse-Kubitza
05:35 PM Revision 12660: schemas/util.sql: added drop_column(regclass[])
Aaron Marcuse-Kubitza
05:04 PM Revision 12659: schemas/util.sql: added parent(regclass)
Aaron Marcuse-Kubitza
04:48 PM Revision 12658: schemas/util.sql: try_create(): also handle not_null_violation, which is thrown when trying to add a NOT NULL column to a parent table, which cascades to a child table whose values for the new column will be NULL
Aaron Marcuse-Kubitza
04:44 PM Revision 12657: bugfix: schemas/util.sql: diff(text, text): also need to cast left_/right_ to base type for the IS DISTINCT FROM filter, because the WHERE clause apparently does *not* use columns from the SELECT list, even though GROUP BY and ORDER BY do
Aaron Marcuse-Kubitza
04:13 PM Revision 12656: schemas/util.sql: added to_freq(regclass, drop_if_always_1)
Aaron Marcuse-Kubitza
04:04 PM Revision 12655: schemas/util.sql: added auto_rm_freq(regclass)
Aaron Marcuse-Kubitza
03:53 PM Revision 12654: schemas/util.sql: added freq_always_1(regclass)
Aaron Marcuse-Kubitza
03:00 PM Revision 12653: bugfix: schemas/util.sql: diff(regclass, regclass): need to create a diff when the # of copies of a row differs between the tables. this uses new util.to_freq().
Aaron Marcuse-Kubitza
02:44 PM Revision 12652: schemas/util.sql: added to_freq(regclass)
Aaron Marcuse-Kubitza
02:43 PM Revision 12651: schemas/util.sql: added populate_table(regclass, text)
Aaron Marcuse-Kubitza
01:11 PM Revision 12650: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
12:53 PM Revision 12649: schemas/util.sql: added copy_types_and_data(regclass, text)
Aaron Marcuse-Kubitza
04:44 AM Revision 12648: schemas/vegbien.sql: public_validations schema comment: added instructions to change the key and value columns for a validations query
Aaron Marcuse-Kubitza
04:41 AM Revision 12647: schemas/vegbien.sql: implemented _plots_16_intercepts_for_each_verb_taxon_in_each_plot_each_proj
Aaron Marcuse-Kubitza
03:44 AM Revision 12646: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
03:44 AM Revision 12645: fix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `public.` qualifier
Aaron Marcuse-Kubitza
03:35 AM Revision 12644: schemas/vegbien.sql: implemented _plots_09_list_of_plots_which_use_line_intercept
Aaron Marcuse-Kubitza
03:20 AM Revision 12643: schemas/vegbien.sql: public_validations: queries that use EXISTS(): join locationevent.plot_id to plot.plot_id directly instead of going via location.plot_location_id
Aaron Marcuse-Kubitza
03:04 AM Revision 12642: schemas/vegbien.sql: implemented _plots_08_list_of_plots_which_use_percent_cover
Aaron Marcuse-Kubitza
12:04 AM Revision 12641: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
12:01 AM Revision 12640: schemas/vegbien.sql: implemented _plots_07_list_of_plots_which_use_counts_of_indiv_per_species
Aaron Marcuse-Kubitza

03/11/2014

09:57 PM Revision 12639: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
09:56 PM Revision 12638: bugfix: inputs/SALVIAS/validations.sql: _plots_07_list_of_plots_with_counts_of_individuals_per_species: renamed to _plots_07_list_of_plots_*which_use*_... because this query is not intended to include the actual counts, just to say which plots have them (the correct "which use" wording is also used in queries #8, 9)
Aaron Marcuse-Kubitza
04:05 PM Revision 12637: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented how to prevent incorrect query plans (`SET enable_seqscan = off;`, etc.)
Aaron Marcuse-Kubitza
03:38 PM Revision 12636: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: query planner: documented that incorrect query plans are an ongoing bug in Postgres, because it does not support index hints and by default does not follow the join order. specifically, Postgres often does the following things in query plans which should normally never be done:
* performs a sequential scan when an index is available (because it incorrectly thinks there are too many dead rows i... Aaron Marcuse-Kubitza

03/07/2014

10:49 PM Revision 12635: schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_06a_list_of_stems, for use in figuring out the diff in _plots_06_list_of_plots_with_stem_measurements
Aaron Marcuse-Kubitza
09:53 PM Revision 12634: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
09:50 PM Revision 12633: schemas/vegbien.sql: plot: removed explicit column lists added in the autorename of plot.location_id->plot_id
Aaron Marcuse-Kubitza
09:41 PM Revision 12632: schemas/vegbien.sql: plot: renamed pkey to plot_id. note that the field is autorenamed in all validation views which use it.
Aaron Marcuse-Kubitza
09:18 PM Revision 12631: schemas/vegbien.sql: locationevent: added autopopulated plot_id column which points to the outermost plot of the locationevent's location
Aaron Marcuse-Kubitza
08:55 PM Revision 12630: bugfix: schemas/vegbien.sql: locationevent: added missing fkey on place_visit_id
Aaron Marcuse-Kubitza
04:42 PM Revision 12629: bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: only include stemobservation records which have actual stem IDs, not merely stem-related measurements (DBH, etc.)
Aaron Marcuse-Kubitza
05:51 AM Revision 12628: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: added `SET enable_seqscan = off;` to match what is done by rematerialize_out_view() to run the queries properly
Aaron Marcuse-Kubitza
05:42 AM Revision 12627: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated to DB
Aaron Marcuse-Kubitza
05:35 AM Revision 12626: bugfix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: LEFT JOIN to project instead of inner joining, to get Postgres to use the right query plan. this is the last change needed to make query #6 runnable.
Aaron Marcuse-Kubitza
05:25 AM Revision 12625: bugfix: schemas/vegbien.sql: rematerialize_out_view(): run all queries with `SET enable_seqscan = off` to avoid slow query plans. this fixes _plots_06_list_of_plots_with_stem_measurements and significantly speeds up _plots_10_count_of_individuals_per_plot_in_each_project (and possibly others).
Aaron Marcuse-Kubitza
05:23 AM Revision 12624: schemas/vegbien.sql: locationevent: documented `CREATE INDEX locationevent_place_visit_id` runtime (3 min)
Aaron Marcuse-Kubitza
04:53 AM Revision 12623: fix: schemas/vegbien.sql: locationevent: added locationevent_place_visit_id index to facilitate joins to place_visit_id in the validations queries
Aaron Marcuse-Kubitza
02:26 AM Revision 12622: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added description of join_collapse_limit config param (which should be turned off, although it is on by default). added links for using TIDs ("the fastest possible access to a single row").
Aaron Marcuse-Kubitza

03/06/2014

10:45 PM Revision 12621: bugfix: schemas/vegbien.sql: source_by_shortname(): documented that in some cases, it is actually a bad idea to use a nested SELECT, because this will prevent Postgres from using an index scan (causing an equally bad slowdown as not inlining in cases where a nested SELECT is required).
Aaron Marcuse-Kubitza
10:26 PM Revision 12620: schemas/postgresql.conf: log_min_messages: dropped the verbosity back down to the default, to avoid clogging up the logs
Aaron Marcuse-Kubitza
10:21 PM Revision 12619: schemas/vegbien.sql: locationevent: documented `VACUUM ANALYZE` runtime (20 min)
Aaron Marcuse-Kubitza
09:51 PM Revision 12618: schemas/postgresql.conf: log_min_messages: show what autovacuum is doing
Aaron Marcuse-Kubitza
09:40 PM Revision 12617: fix: schemas/postgresql.conf: disable autovacuum_vacuum_cost_delay to avoid stalling autovacuuming due to a concurrent query, as this can prevent autovacuuming from happening altogether (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Resource%20Consumption:%2018.4.4.%20Cost-based%20Vacuum%20Delay)
Aaron Marcuse-Kubitza
09:37 PM Revision 12616: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for troubleshooting autovacuuming (which can slow queries down significantly when it isn't happening for any tables)
Aaron Marcuse-Kubitza
07:35 PM Revision 12615: schemas/vegbien.sql: location: documented `CREATE INDEX plot_source_id` runtime (5 min)
Aaron Marcuse-Kubitza
07:30 PM Revision 12614: fix: schemas/vegbien.sql: location: added plot_source_id index to provide the equivalent of the location.source_id index for outer plots. this will help Postgres choose the right query plans in queries involving outer plots.
Aaron Marcuse-Kubitza
11:30 AM Revision 12613: planning/meetings/BIEN conference call availability.xlsx: updated
Aaron Marcuse-Kubitza
11:06 AM Revision 12612: bugfix: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: need to escape the quotes in \set ... 'SALVIAS'
Aaron Marcuse-Kubitza
11:04 AM Revision 12611: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `SET search_path TO public;` since this is the default
Aaron Marcuse-Kubitza
11:03 AM Revision 12610: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: search_path: removed public_validations since we are not creating views
Aaron Marcuse-Kubitza
11:02 AM Revision 12609: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: use psql var :datasource instead of current_schema() so that the queries are runnable without special configuration of the search_path
Aaron Marcuse-Kubitza
10:59 AM Revision 12608: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: removed `CREATE OR REPLACE VIEW` so the validations views are not unintentionally replaced when running this file
Aaron Marcuse-Kubitza
10:57 AM Revision 12607: validation/aggregating/plots/bien3_validations_salvias_vegbien.sql: updated from DB
Aaron Marcuse-Kubitza
08:57 AM Revision 12606: schemas/vegbien.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: added ~type table
Aaron Marcuse-Kubitza
08:52 AM Revision 12605: fix: inputs/SALVIAS/validations.sql: _plots_18_list_of_subplots_codes_for_each_plot_for_each_project: changed columns to match output query
Aaron Marcuse-Kubitza
08:31 AM Revision 12604: schemas/vegbien.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: added ~type table
Aaron Marcuse-Kubitza
08:29 AM Revision 12603: fix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed types to match output query
Aaron Marcuse-Kubitza
08:14 AM Revision 12602: bugfix: inputs/SALVIAS/validations.sql: _plots_15_pct_cover_of_each_verb_taxon_in_each_plot_in_each_pro: changed summarizing column from mean_cover->totalpercentcover to match output query
Aaron Marcuse-Kubitza
08:12 AM Revision 12601: bugfix: inputs/SALVIAS/validations.sql: _plots_10a_aggregate_observation_individual_counts: changed individual_id type to match output query
Aaron Marcuse-Kubitza
02:18 AM Revision 12600: bugfix: schemas/Makefile: `%/install: vegbien.sql`: also need to match `public_validations` when used as a schema-qualifier (public_validations._), and after a cast (::) to a schema-qualified type. these occur in schema-qualified casts to the custom return type in the keys() functions.
Aaron Marcuse-Kubitza
01:59 AM Revision 12599: bugfix: schemas/Makefile: `%/install: vegbien.sql`: sed expr: need to use '' instead of "" because $(*q) may contain "
Aaron Marcuse-Kubitza
12:33 AM Revision 12598: bugfix: schemas/vegbien.sql: _plots_10a_aggregate_observation_individual_counts: need to use taxonoccurrence.sourceaccessioncode, not aggregateoccurrence.sourceaccessioncode, because aggregateoccurrence.sourceaccessioncode is not populated
Aaron Marcuse-Kubitza
12:09 AM Revision 12597: schemas/vegbien.sql: public_validations schema comment: documented how to remove a validations query so its columns can be changed (use public_validations.rm_query_view())
Aaron Marcuse-Kubitza
12:07 AM Revision 12596: schemas/vegbien.sql, inputs/SALVIAS/validations.sql: added _plots_10a_aggregate_observation_individual_counts, for use in debugging diffs in _plots_10_count_of_individuals_per_plot_in_each_proj
Aaron Marcuse-Kubitza
12:00 AM Revision 12595: schemas/util.sql: create_if_not_exists(): also support `CREATE FUNCTION` (by handling duplicate_function exceptions)
Aaron Marcuse-Kubitza

03/05/2014

11:53 PM Revision 12594: schemas/util.sql: mk_keys_func(): create the keys() function directly from the keys record type, to support creating just a custom keys record type rather than also a custom keys() function (which is fairly complicated to do, thanks to the need to define a separate custom return type)
Aaron Marcuse-Kubitza
01:01 PM Revision 12593: bugfix: schemas/util.sql: relation_type(relation_type_char "char"): need to handle TYPEs as well, because these are also listed in pg_class (and should be deleted by drop_relations_like())
Aaron Marcuse-Kubitza
12:50 PM Revision 12592: schemas/util.sql: added show_types_like()
Aaron Marcuse-Kubitza
12:42 PM Revision 12591: bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
Aaron Marcuse-Kubitza
12:25 PM Revision 12590: bugfix: schemas/util.sql: functions marked IMMUTABLE: changed to STABLE or VOLATILE where IMMUTABLE would cause preemptive inlining (http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
Aaron Marcuse-Kubitza
12:13 PM Revision 12589: schemas/vegbien.sql: locationevent: documented `UPDATE locationevent SET place_visit_id = place_visit_id` runtime (>~1 h)
Aaron Marcuse-Kubitza
07:28 AM Revision 12588: schemas/util.sql: added relation_type(regtype) so drop_relation() would work on TYPEs, too. TYPEs are sometimes used as a function return type linked to a particular table (eg. in the keys() functions), and should be dropped along with the table by util.drop_relations_like().
Aaron Marcuse-Kubitza
07:24 AM Revision 12587: schemas/util.sql: drop_relation(regclass): support any type that has a util.relation_type()
Aaron Marcuse-Kubitza
07:23 AM Revision 12586: fix: schemas/vegbien.sql: _plots_10_count_of_individuals_per_plot_in_each_project: fixed to handle subplots properly, using new locationevent.place_visit_id
Aaron Marcuse-Kubitza
01:02 AM Revision 12585: schemas/vegbien.sql: added place_visit view, analogous to plot but for top-level locationevents. this is needed by the SALVIAS validation queries.
Aaron Marcuse-Kubitza
12:59 AM Revision 12584: schemas/vegbien.sql: locationevent: added locationevent_set_place_visit_id() trigger, analogous to location_set_plot_location_id() but for the locationevent nesting hierarchy. this is needed by the SALVIAS validation queries.
Aaron Marcuse-Kubitza
12:32 AM Revision 12583: fix: schemas/vegbien.sql: location_set_plot_location_id(): removed incorrect comment that this is a pull-forward trigger
Aaron Marcuse-Kubitza

03/04/2014

05:56 PM Revision 12582: bugfix: schemas/vegbien.sql: public_validations.query_view_relation_max_prefix_len(): added values__ prefix
Aaron Marcuse-Kubitza
05:52 PM Revision 12581: bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use `CREATE FUNCTION` instead of `CREATE OR REPLACE FUNCTION` so that the function is only created if it does not exist, to avoid overwriting a custom keys() function
Aaron Marcuse-Kubitza
05:48 PM Revision 12580: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
Aaron Marcuse-Kubitza
05:47 PM Revision 12579: bugfix: schemas/util.sql: uses of util.col_cast.col_name: need to wrap in quote_ident()
Aaron Marcuse-Kubitza
05:40 PM Revision 12578: schemas/vegbien.sql: public_validations: re-auto-added keys(), values_() functions for all queries
Aaron Marcuse-Kubitza
05:39 PM Revision 12577: bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use named return type to avoid complicated edge cases with 1-column records, which prevent (values_(...)).* from working correctly
Aaron Marcuse-Kubitza
05:22 PM Revision 12576: schemas/util.sql: added mk_typed_cols_list(col_cast[])
Aaron Marcuse-Kubitza
05:20 PM Revision 12575: schemas/util.sql: added prefixed_name()
Aaron Marcuse-Kubitza
05:18 PM Revision 12574: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.qual_name() to create the function name, for clarity
Aaron Marcuse-Kubitza
04:56 PM Revision 12573: schemas/vegbien.sql: public_validations: auto-added values_() functions for all queries
Aaron Marcuse-Kubitza
04:53 PM Revision 12572: schemas/util.sql: util.remake_diff_table(): viewing the table in human-readable form: use just the output side's values_() columns, to avoid repeating columns on both sides that are the same and put just the changed columns side-by-side
Aaron Marcuse-Kubitza
04:45 PM Revision 12571: bugfix: schemas/util.sql: mk_keys_func(regtype): values() function: must be called values_() instead because `values` is a keyword
Aaron Marcuse-Kubitza
04:31 PM Revision 12570: schemas/util.sql: mk_keys_func(regtype): also add values() function for use in displaying the diff table
Aaron Marcuse-Kubitza
04:19 PM Revision 12569: schemas/util.sql: mk_keys_func(regtype, col_cast[]): allow changing the name of the generated function
Aaron Marcuse-Kubitza
03:55 PM Revision 12568: schemas/util.sql: `RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM`: use simpler `RAISE` to rethrow error
Aaron Marcuse-Kubitza
03:53 PM Revision 12567: bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): use util.create_if_not_exists() to avoid trying to overwrite a custom keys() function the user has written
Aaron Marcuse-Kubitza
03:49 PM Revision 12566: schemas/vegbien.sql: public_validations: auto-added keys() functions for all queries
Aaron Marcuse-Kubitza
03:41 PM Revision 12565: schemas/util.sql: diff(text, text): automatically create a keys() function for the base type. this avoids the need to create keys() functions manually for the numerous queries that need them.
Aaron Marcuse-Kubitza
03:35 PM Revision 12564: schemas/util.sql: added mk_keys_func(regtype)
Aaron Marcuse-Kubitza
03:34 PM Revision 12563: bugfix: schemas/util.sql: mk_keys_func(regtype, util.col_cast[]): need to handle 1-column records specially, because Postgres does not allow returning a 1-column record when there are OUT params
Aaron Marcuse-Kubitza
03:02 PM Revision 12562: schemas/util.sql: added typed_cols(regtype)
Aaron Marcuse-Kubitza
02:29 PM Revision 12561: schemas/util.sql: added mk_keys_func(regtype, col_cast[])
Aaron Marcuse-Kubitza
02:27 PM Revision 12560: bugfix: schemas/util.sql: raise(): need to use $__BODY1$ in case msg contains $BODY1$ (in SQL)
Aaron Marcuse-Kubitza
02:23 PM Revision 12559: bugfix: schemas/util.sql: mk_out_params(): need COALESCE(..., ) around string_agg()
Aaron Marcuse-Kubitza
02:22 PM Revision 12558: bugfix: schemas/util.sql: eval(): don't declare it STRICT because it should throw an error if you try to execute NULL
Aaron Marcuse-Kubitza
02:20 PM Revision 12557: schemas/vegbien.sql: synced with DB, which reorders ~type tables
Aaron Marcuse-Kubitza
01:59 PM Revision 12556: schemas/util.sql: added mk_out_params()
Aaron Marcuse-Kubitza
01:42 AM Revision 12555: schemas/util.sql: mk_diff_query(): indented left_ table to line up vertically with right_, for easier comparison of the left_/right_ table names
Aaron Marcuse-Kubitza
01:40 AM Revision 12554: schemas/util.sql: mk_diff_query(): removed special handling for CROSS JOIN because this is now handled by diff(text, text) using `FULL JOIN ON true`. this simplification allows mk_diff_query() to contain just the template structure of the FULL JOIN, without _if() calls that decrease readability.
Aaron Marcuse-Kubitza
01:34 AM Revision 12553: schemas/util.sql: diff(text, text): handle the CROSS JOIN special case using `FULL JOIN ON true`, to allow mk_diff_query() to support just a straighforward FULL JOIN. this also ensures that *all* diff queries use the same FULL JOIN template.
Aaron Marcuse-Kubitza

02/28/2014

11:17 PM Revision 12552: bugfix: schemas/vegbien.sql: public_validations: schema comment: also need to re-create keys() functions after running public_validations.rm_all_queries()
Aaron Marcuse-Kubitza
11:13 PM Revision 12551: bugfix: schemas/vegbien.sql: added back keys() matchup functions, which get cascadingly deleted when the queries are re-created to change column names, etc. note that this causes the associated ~type tables to sort before them.
Aaron Marcuse-Kubitza
11:11 PM Revision 12550: schemas/vegbien.sql: added new ~type tables
Aaron Marcuse-Kubitza
10:45 PM Revision 12549: fix: lib/phpPgAdmin.login.php.diff: "For user public_, leave password blank" instruction: moved this to right under the Password field and increased the font size so people would be more likely to see it
Aaron Marcuse-Kubitza
10:28 PM Revision 12548: /Makefile: added separate phppgadmin-Linux target to avoid needing to run the entire postgres-Linux target whenever http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down (after some system updates)
Aaron Marcuse-Kubitza
10:03 PM Revision 12547: schemas/vegbien.sql: rm_all_queries(schema): documented this function's unexpected behavior when schema = public_validations (it removes in *all* schemas, not just public_validations). this is because this uses rm_query_view().
Aaron Marcuse-Kubitza
09:49 PM Revision 12546: fix: schemas/vegbien.sql: use plot (which includes only outer plots) instead of location, to match the input queries
Aaron Marcuse-Kubitza
08:15 PM Revision 12545: schemas/vegbien.sql: rm_query_group(): support removing all validations as well as just those with a particular group prefix (and renamed to rm_output_queries())
Aaron Marcuse-Kubitza
07:57 PM Revision 12544: fix: schemas/vegbien.sql: public_validations: schema comment: removed incorrect "" in `SET search_path = "public_validations", pg_catalog;` now that they are no longer auto-added
Aaron Marcuse-Kubitza
07:56 PM Revision 12543: bugfix: schemas/Makefile: `%/install: vegbien.sql`: don't enclose public_validations in "" because this causes a comment that says to search for `SET search_path = public_validations, pg_catalog;` to become mangled
Aaron Marcuse-Kubitza
07:51 PM Revision 12542: schemas/vegbien.sql: synced with DB, which reorders ~type tables
Aaron Marcuse-Kubitza
07:34 PM Revision 12541: fix: schemas/Makefile: moved comment about publishing a schema to %/publish
Aaron Marcuse-Kubitza
05:27 PM Revision 12540: lib/Firefox_bookmarks.reformat.csv: "page's description": changed to "page's own description" to clarify that this is a description provided by the page itself
Aaron Marcuse-Kubitza
05:25 PM Revision 12539: web/links/index.htm: updated to Firefox bookmarks: PostgreSQL: added links for error reporting levels and how to hide stack traces in psql. SQL: added links for recursive queries, which can be used to traverse hierarchical (parent-pointer) tables.
Aaron Marcuse-Kubitza

02/27/2014

07:56 PM Revision 12538: fix: inputs/SALVIAS/validations.sql: renamed SiteCode to plot_code to match output queries
Aaron Marcuse-Kubitza
07:53 PM Revision 12537: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
Aaron Marcuse-Kubitza
07:49 PM Revision 12536: schemas/util.sql: raise_error_notice(): raise a WARNING instead because this is for errors, and rename to raise_error_warning() to match
Aaron Marcuse-Kubitza
07:45 PM Revision 12535: schemas/util.sql: removed no longer used raise_notice(). use util.raise(NOTICE, ...) instead.
Aaron Marcuse-Kubitza
07:41 PM Revision 12534: schemas/util.sql: use util.raise(NOTICE, ...) instead of util.raise_notice()
Aaron Marcuse-Kubitza
07:37 PM Revision 12533: schemas/util.sql: raise(type text, msg text): documented the possible options for the type param
Aaron Marcuse-Kubitza
07:34 PM Revision 12532: fix: schemas/util.sql: raise(): util.eval(): turn off verbose_ mode to avoid printing debug statements about debug statements
Aaron Marcuse-Kubitza
07:32 PM Revision 12531: schemas/util.sql: eval(): added verbose_ option like for eval2set()
Aaron Marcuse-Kubitza
07:28 PM Revision 12530: schemas/util.sql: added raise(type text, msg text)
Aaron Marcuse-Kubitza
07:09 PM Revision 12529: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
Aaron Marcuse-Kubitza
07:08 PM Revision 12528: fix: schemas/vegbien.sql: added back _traits_* ~type tables
Aaron Marcuse-Kubitza
07:02 PM Revision 12527: fix: schemas/vegbien.sql: _plots_* validation queries: renamed plot_code columns (with names plotcode, plotCode, SiteCode) to match the input queries (plot_code)
Aaron Marcuse-Kubitza
06:58 PM Revision 12526: inputs/SALVIAS/validations.sql: use plot_code instead of plotcode for easier readability
Aaron Marcuse-Kubitza
06:42 PM Revision 12525: fix: schemas/vegbien.sql: rm_all_queries(): all-schemas mode: also remove queries that are only present on the input side, so that the input side can also be re-created from the DDL file
Aaron Marcuse-Kubitza
06:36 PM Revision 12524: fix: schemas/vegbien.sql: rm_all_queries(): documented that this actually *does* remove queries in *all* schemas when schema is NULL, because rm_query_view(public_validations.view) searches *all* schemas
Aaron Marcuse-Kubitza
04:37 PM Revision 12523: schemas/vegbien.sql: top_plot view: renamed to plot, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
Aaron Marcuse-Kubitza
04:12 PM Revision 12522: schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
Aaron Marcuse-Kubitza
03:51 PM Revision 12521: schemas/vegbien.sql: location.top_plot: renamed to plot_location_id, as requested by Brad (wiki.vegpath.org/2014-02-27_conference_call#schema-changes)
Aaron Marcuse-Kubitza
02:20 PM Revision 12520: schemas/vegbien.sql: source_by_shortname(): documented exact time that the slow query ran for (5013s = ~1.5 hours)
Aaron Marcuse-Kubitza
02:07 PM Revision 12519: schemas/vegbien.sql: public_validations: schema comment: documented how to sync the queries with schemas/vegbien.sql
Aaron Marcuse-Kubitza
02:05 PM Revision 12518: schemas/vegbien.sql: public_validations.rm_all_queries(): when called with no args, operate on public_validations instead of all schemas, since this is the more common use case (replacing just the output queries, not both input and output)
Aaron Marcuse-Kubitza
01:49 PM Revision 12517: schemas/vegbien.sql: rm_query_group(): documented that this only removes validations *output* queries (in public_validations), as it is designed for use in updating all output queries at once from the testing DB
Aaron Marcuse-Kubitza
01:27 PM Revision 12516: bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .
Aaron Marcuse-Kubitza
12:43 PM Revision 12515: schemas/vegbien.sql: source_by_shortname(): documented that it *must* be run as a nested SELECT, because otherwise Postgres will not inline it. not inlining causes the query to be run for *each* row in a table of potentially millions, and creates a significant slowdown (eg. >1.5 hours for SALVIAS._plots_06_list_of_plots_with_stem_measurements)
Aaron Marcuse-Kubitza
07:59 AM Revision 12514: schemas/util.sql: eval2val(): made it STABLE so that functions that use it can themselves be inlined. (Postgres apparently ignores the STABLE qualifier, without warning, if the function contains any VOLATILE statements.)
Aaron Marcuse-Kubitza
06:51 AM Revision 12513: fix: schemas/vegbien.sql: source_by_shortname(): needs to be declared STABLE so it can be constant-folded and only run once per query. (it had previously been VOLATILE because of constraints imposed on functions that run `SET LOCAL search_path`.)
Aaron Marcuse-Kubitza
01:11 AM Revision 12512: fix: schemas/vegbien.sql: location: added index on top_plot
Aaron Marcuse-Kubitza
01:11 AM Revision 12511: fix: schemas/vegbien.sql: location: added index on top_plot
Aaron Marcuse-Kubitza
01:06 AM Revision 12510: fix: schemas/vegbien.sql: _plots_06_list_of_plots_with_stem_measurements: changed columns to match input query
Aaron Marcuse-Kubitza
01:04 AM Revision 12509: schemas/vegbien.sql: added more ~type tables for plots queries
Aaron Marcuse-Kubitza

02/26/2014

11:58 PM Revision 12508: fix: inputs/SALVIAS/validations.sql: plotMetadata.SiteCode: need to match types with the output query column
Aaron Marcuse-Kubitza
11:44 PM Revision 12507: schemas/vegbien.sql: added public_validations.rm_all_queries(schema), which removes all validations queries in a schema, or in all schemas (useful when recreating validations queries from the DDL export file)
Aaron Marcuse-Kubitza
11:29 PM Revision 12506: schemas/util.sql: validation_views(): moved filtering out of *_validations schemas to validatable_datasources() so that calling this function with no args gives the validation views in *all* schemas as one would expect
Aaron Marcuse-Kubitza
10:58 PM Revision 12505: schemas/util.sql: query_relations(): support passing in an input query instead of an output query, which will remove only in the datasource's schema. (note that it was not clear that passing in an *input-side* query was not previously supported.)
Aaron Marcuse-Kubitza
10:50 PM Revision 12504: schemas/util.sql: added schema_regexp(regclass)
Aaron Marcuse-Kubitza
10:30 PM Revision 12503: schemas/vegbien.sql: schema_anchor: clarified that this identifies this function's schema (for use in locating helper functions), but is not necessarily the schema operated on, as the comment implied
Aaron Marcuse-Kubitza
10:05 PM Revision 12502: schemas/util.sql: drop_relations_like(): use util.schema_regexp()
Aaron Marcuse-Kubitza
10:02 PM Revision 12501: schemas/util.sql: added schema_regexp(schema_anchor)
Aaron Marcuse-Kubitza
01:16 PM Revision 12500: fix: schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: count *top-level* plots to match the input query
Aaron Marcuse-Kubitza
12:29 PM Revision 12499: schemas/vegbien.sql: remake_diff_table(): instructions for how to regenerate the diff table: put these at the beginning of the table comment instead of the end, because they are the most important info about the table that a user needs to know
Aaron Marcuse-Kubitza
11:58 AM Revision 12498: schemas/util.sql: diff(text, text): documented how to write a custom keys() function to match up rows using a subset of the columns (including a sample keys() function template)
Aaron Marcuse-Kubitza
11:37 AM Revision 12497: schemas/vegbien.sql: replaced custom %== operators with much simpler custom keys() functions. this avoids both the need to write out an = comparison for each field, and especially, also the need to write both an operator *and* a function implementing that operator.
Aaron Marcuse-Kubitza
10:42 AM Revision 12496: bugfix: schemas/util.sql: diff(text, text): in the %== comparison, it turns out you *do* need to cast the values to the *same* base type, *even though* this is optional when using a custom %==
Aaron Marcuse-Kubitza

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
Aaron Marcuse-Kubitza
11:49 PM Revision 12494: schemas/util.sql: added prepend_comment()
Aaron Marcuse-Kubitza
11:39 PM Revision 12493: schemas/util.sql: removed no longer used mk_set_search_path(VARIADIC schemas text[]). use mk_search_path() instead.
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
11:19 PM Revision 12488: schemas/util.sql: use_own_schema(): auto-append util to the search_path to enable use of util operators
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
10:49 PM Revision 12485: schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.
Aaron Marcuse-Kubitza
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()
Aaron Marcuse-Kubitza
10:42 PM Revision 12483: schemas/util.sql: added use_own_schema()
Aaron Marcuse-Kubitza
10:40 PM Revision 12482: schemas/util.sql: added set_search_path()
Aaron Marcuse-Kubitza
10:36 PM Revision 12481: schemas/util.sql: runnable_sql(): don't output search_path before a SET statement
Aaron Marcuse-Kubitza
10:31 PM Revision 12480: schemas/util.sql: added is_set_stmt()
Aaron Marcuse-Kubitza
09:28 PM Revision 12479: schemas/util.sql: diff(text, text): use mk_diff_query()'s new cols param to avoid a nested SELECT
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
08:25 PM Revision 12475: schemas/util.sql: added mk_diff_query()
Aaron Marcuse-Kubitza
08:13 PM Revision 12474: schemas/util.sql: debug_print_sql(): use runnable_sql()
Aaron Marcuse-Kubitza
08:12 PM Revision 12473: schemas/util.sql: added runnable_sql()
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
05:40 PM Revision 12470: schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from
Aaron Marcuse-Kubitza
05:39 PM Revision 12469: schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
04:09 PM Revision 12467: schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
02:45 PM Revision 12464: schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info
Aaron Marcuse-Kubitza
02:30 PM Revision 12463: schemas/util.sql: added mk_set_search_path() (which uses the current search_path)
Aaron Marcuse-Kubitza
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 %==
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
01:44 PM Revision 12459: schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output
Aaron Marcuse-Kubitza
01:41 PM Revision 12458: schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements
Aaron Marcuse-Kubitza
01:28 PM Revision 12457: schemas/util.sql: added is_explain()
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
01:22 PM Revision 12455: fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
01:02 PM Revision 12452: schemas/util.sql: added explain2notice_msg_if_can()
Aaron Marcuse-Kubitza
12:57 PM Revision 12451: schemas/util.sql: added is_explainable(sql)
Aaron Marcuse-Kubitza
12:55 PM Revision 12450: schemas/util.sql: added first_word()
Aaron Marcuse-Kubitza
12:25 PM Revision 12449: schemas/util.sql: explain2notice(): use util.raise_notice()
Aaron Marcuse-Kubitza
12:23 PM Revision 12448: schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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**)
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
10:29 AM Revision 12435: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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[])
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
09:39 AM Revision 12430: schemas/util.sql: added debug_print_return_value()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
09:28 AM Revision 12428: schemas/util.sql: diff(text, text): renamed IN params to be different from OUT params to support LANGUAGE plpgsql
Aaron Marcuse-Kubitza
09:04 AM Revision 12427: bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables
Aaron Marcuse-Kubitza
07:56 AM Revision 12426: /README.TXT: use full hostname for jupiter so the commands work outside of the NCEAS network as well
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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)
Aaron Marcuse-Kubitza
07:21 AM Revision 12423: schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same
Aaron Marcuse-Kubitza
07:14 AM Revision 12422: schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza

02/24/2014

11:18 PM Revision 12419: schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type
Aaron Marcuse-Kubitza
11:17 PM Revision 12418: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
Aaron Marcuse-Kubitza
10:51 PM Revision 12417: fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
04:52 PM Revision 12415: schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix
Aaron Marcuse-Kubitza
04:50 PM Revision 12414: schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source
Aaron Marcuse-Kubitza
04:43 PM Revision 12413: schemas/util.sql: added drop_relations_like()
Aaron Marcuse-Kubitza
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.)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
02:33 PM Revision 12410: schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully
Aaron Marcuse-Kubitza
11:39 AM Revision 12409: web/links/index.htm: updated to Firefox bookmarks: Roundtable: virtual collaboration: annotated the linked pages
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
08:53 AM Revision 12406: added inputs/SALVIAS/validations*.sql
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
12:28 AM Revision 12402: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: removed `LIMIT 10` to match output queries
Aaron Marcuse-Kubitza
12:26 AM Revision 12401: fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: updated table names to match our renamings
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
12:24 AM Revision 12399: schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza

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"
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
08:29 PM Revision 12394: bin/psql_verbose_vegbien: use \\ instead of \ inside '' because this is sh, not bash
Aaron Marcuse-Kubitza
08:26 PM Revision 12393: bin/psql_verbose_vegbien: changed prep-statement order to match lib/sh/db.sh psql()
Aaron Marcuse-Kubitza
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()
Aaron Marcuse-Kubitza
08:11 PM Revision 12391: bin/make_analytical_db: added `public_validations.remake_diff_tables()`
Aaron Marcuse-Kubitza
08:03 PM Task #871 (New): grant bien_read access to validation result tables
Aaron Marcuse-Kubitza
08:02 PM Task #870 (New): grant bien_read access to publishable staging tables
Aaron Marcuse-Kubitza
07:57 PM Task #501 (Resolved): find out which datasources won't allow their data to be publicly accessible
Aaron Marcuse-Kubitza
07:44 PM Revision 12390: fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage
Aaron Marcuse-Kubitza
07:43 PM Revision 12389: schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources
Aaron Marcuse-Kubitza
07:40 PM Revision 12388: schemas/vegbien.sql: added validatable_datasources()
Aaron Marcuse-Kubitza
07:36 PM Revision 12387: schemas/vegbien.sql: added validatable_datasources()
Aaron Marcuse-Kubitza
07:31 PM Revision 12386: schemas/vegbien.sql: validation_views(): allow leaving out schema to match all datasources
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
07:19 PM Revision 12384: schemas/util.sql: added str_equality_regexp(literal text)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
06:00 PM Revision 12381: /README.TXT: to back up the version history: added steps to sync git to the local machine
Aaron Marcuse-Kubitza
05:55 PM Revision 12380: added validation/aggregating/traits/BIEN2_traits/_archive/bien3_validations_traits_complete.sql from Brad's e-mail
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
05:08 PM Revision 12378: schemas/util.sql: added curr_func(func text, schema_anchor anyelement)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
03:47 PM Revision 12376: schemas/util.sql: added quote_func_call()
Aaron Marcuse-Kubitza
03:22 PM Revision 12375: schemas/util.sql: added regprocedure(text)
Aaron Marcuse-Kubitza
03:11 PM Revision 12374: schemas/util.sql: func2regproc*(): removed func2* so that this has the format of a cast function (which it is)
Aaron Marcuse-Kubitza
03:10 PM Revision 12373: schemas/util.sql: added func2regproc(regprocedure)
Aaron Marcuse-Kubitza
03:08 PM Revision 12372: schemas/util.sql: added func2regprocedure(regproc)
Aaron Marcuse-Kubitza
02:51 PM Revision 12371: schemas/util.sql: added quote_typed()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
01:18 PM Revision 12369: schemas/util.sql: added append_comment(regclass)
Aaron Marcuse-Kubitza
01:00 PM Revision 12368: schemas/util.sql: added comment(oid)
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
12:04 PM Revision 12365: schemas/vegbien.sql: added query_view_relation_max_prefix_len()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
12:00 PM Revision 12363: bugfix: schemas/util.sql: rename_relation(): need quote_ident() around to_
Aaron Marcuse-Kubitza
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()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
11:22 AM Revision 12359: fix: name_was_truncated(): should use util.namedatalen() instead of hardcoding the value
Aaron Marcuse-Kubitza

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
Aaron Marcuse-Kubitza
08:32 PM Revision 12357: schemas/util.sql: added truncated_prefixed_name_regexp()
Aaron Marcuse-Kubitza
08:12 PM Revision 12356: schemas/util.sql: added rtrim_n()
Aaron Marcuse-Kubitza
07:58 PM Revision 12355: schemas/util.sql: added name_was_truncated()
Aaron Marcuse-Kubitza
07:56 PM Revision 12354: schemas/util.sql: added namedatalen()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
07:18 PM Revision 12352: schemas/vegbien.sql: added rename_query_view(), which renames all DB objects related to a particular validations query
Aaron Marcuse-Kubitza
07:13 PM Revision 12351: schemas/vegbien.sql: added new_query_view_relation_name()
Aaron Marcuse-Kubitza
07:11 PM Revision 12350: schemas/util.sql: added replace_suffix()
Aaron Marcuse-Kubitza
06:47 PM Revision 12349: schemas/util.sql: added rename_relation(regclass/text, text)
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
06:21 PM Revision 12347: schemas/util.sql: drop_relation(relation text, type text): put type param first because it is usually constant
Aaron Marcuse-Kubitza
06:12 PM Revision 12346: schemas/util.sql: drop_table(), drop_view(): use drop_relation(relation text, type text)
Aaron Marcuse-Kubitza
06:10 PM Revision 12345: schemas/util.sql: drop_relation(regclass): use simpler drop_relation(relation text, type text)
Aaron Marcuse-Kubitza
06:06 PM Revision 12344: schemas/util.sql: added relation_type(regclass)
Aaron Marcuse-Kubitza
06:04 PM Revision 12343: schemas/util.sql: added drop_relation(relation text, type text)
Aaron Marcuse-Kubitza
06:01 PM Revision 12342: bugfix: schemas/util.sql: drop_relation(): need to pass force param to util.drop_*()
Aaron Marcuse-Kubitza
05:50 PM Revision 12341: schemas/util.sql: added relation_type_char(regclass)
Aaron Marcuse-Kubitza
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.
Aaron Marcuse-Kubitza
05:47 PM Revision 12339: schemas/util.sql: added relation_type(character)
Aaron Marcuse-Kubitza
04:04 PM Revision 12338: schemas/vegbien.sql: added rm_query_view(), which removes all DB objects related to a particular validations query
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
03:48 PM Revision 12336: fix: schemas/vegbien.sql: query_views(): renamed to query_relations() because this also returns non-views
Aaron Marcuse-Kubitza
03:35 PM Revision 12335: schemas/vegbien.sql: added public_validations.query_views()
Aaron Marcuse-Kubitza
03:35 PM Revision 12334: schemas/util.sql: schema_matches(): should be IMMUTABLE, not STABLE, because it uses only parameter values
Aaron Marcuse-Kubitza
03:24 PM Revision 12333: schemas/util.sql: added regexp_quote()
Aaron Marcuse-Kubitza
03:18 PM Revision 12332: fix: schemas/util.sql: is_table(), is_view(): should be STABLE, not IMMUTABLE, because they reference tables
Aaron Marcuse-Kubitza
02:50 PM Revision 12331: schemas/util.sql: added drop_relation()
Aaron Marcuse-Kubitza
02:41 PM Revision 12330: schemas/util.sql: added is_table()
Aaron Marcuse-Kubitza
02:39 PM Revision 12329: schemas/util.sql: added is_view()
Aaron Marcuse-Kubitza
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
Aaron Marcuse-Kubitza
02:14 PM Revision 12327: schemas/util.sql: show_views_like(): use util.show_relations_like()
Aaron Marcuse-Kubitza
02:03 PM Revision 12326: schemas/util.sql: show_views_like(): reversed argument order to match show_relations_like()
Aaron Marcuse-Kubitza
12:32 PM Revision 12325: schemas/util.sql: added show_relations_like()
Aaron Marcuse-Kubitza
12:21 PM Revision 12324: schemas/util.sql: added schema_matches()
Aaron Marcuse-Kubitza
 

Also available in: Atom