schemas/util.sql: mk_search_path(), mk_set_search_path(text...): auto-append util to the search_path to enable use of util operators

schemas/util.sql: removed no longer used mk_use_own_schema(). use util.use_own_schema() instead.

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()

schemas/util.sql: added use_own_schema()

schemas/util.sql: added set_search_path()

schemas/util.sql: runnable_sql(): don't output search_path before a SET statement

schemas/util.sql: added is_set_stmt()

schemas/util.sql: diff(text, text): use mk_diff_query()'s new cols param to avoid a nested SELECT

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)

schemas/util.sql: diff(text, text): use util.mk_diff_query(). this splits the complex query-generation code apart from the query-execution code.

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

schemas/util.sql: added mk_diff_query()

schemas/util.sql: debug_print_sql(): use runnable_sql()

schemas/util.sql: added runnable_sql()

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.

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.

schemas/util.sql: materialize_query(): add a comment on the table with the query it was generated from

schemas/util.sql: debug_print_sql(): util.mk_set_search_path(): use for_printing := true to comment out LOCAL

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)

schemas/util.sql: mk_set_search_path(): added for_printing option like for mk_set_search_path(text)

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)

schemas/util.sql: debug_print_sql(): include the search_path in case the query contains search_path-dependent elements (such as operators)

schemas/util.sql: util.explain2notice_msg(): add newline before and after to visually separate it from other debug info

schemas/util.sql: added mk_set_search_path() (which uses the current search_path)

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 %==

bugfix: schemas/util.sql: first_word(): ignore leading whitespace: need to use util.ltrim_nl(), as ltrim() only removes spaces

bugfix: schemas/util.sql: first_word(): need to ignore leading whitespace. this applies to many of our queries, which have a leading newline.

schemas/util.sql: explain(): don't debug-print the EXPLAIN, to avoid cluttering up the debug output

schemas/util.sql: eval2set(): make debug-printing optional, for use with internal statements

schemas/util.sql: added is_explain()

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)

fix: schemas/util.sql: debug_print_sql(): only surround EXPLAIN output with newlines if there is any

fix: schemas/util.sql: debug_print_sql(): need newline before and after EXPLAIN output to visually separate it from other debug info

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)

schemas/util.sql: added explain2notice_msg_if_can()

schemas/util.sql: added is_explainable(sql)

schemas/util.sql: added first_word()

schemas/util.sql: explain2notice(): use util.raise_notice()

schemas/util.sql: explain2notice(): added helper function explain2notice_msg() which can also be used individually

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

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.

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)

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

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**)

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)

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)

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.

fix: schemas/util.sql: diff(text, text): use util.typeof() instead of pg_typeof() so the outputted query is runnable in any search_path

schemas/util.sql: added typeof(), which unlike pg_typeof() uses util.qual_name() to create a search_path-independent name

fix: schemas/util.sql: quote_typed(): schema-qualify the type so the expression does not depend on the search_path

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

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

schemas/util.sql: diff(regclass, regclass): support custom %== by including the col_type_null's schema in the search_path

schemas/util.sql: debug_print_return_value(): don't '-encode the value by default, because text values usually look better without the '-escaping

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[])

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

schemas/util.sql: added debug_print_return_value()

schemas/util.sql: diff(text, text): support custom search_path because custom %== operators are usually not in the util schema

schemas/util.sql: diff(text, text): renamed IN params to be different from OUT params to support LANGUAGE plpgsql

bugfix: schemas/util.sql: diff(*): changed from STABLE to VOLATILE because these functions create tables

/README.TXT: use full hostname for jupiter so the commands work outside of the NCEAS network as well

schemas/vegbien.sql: _plots_04_count_of_plots_in_each_project_in_this_source: added operator %== to match the rows by project_name

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)

schemas/util.sql: added operator %== , which returns whether the map-keys of the compared values are the same

schemas/vegbien.sql: synced from DB, which changes the order of the ~type tables

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

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.

schemas/util.sql: remake_diff_table(): use util.quote_typed() instead of hardcoding the type

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

fix: inputs/SALVIAS/validations.sql: _plots_02_list_of_project_names: altered column aliases to match output query

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

schemas/vegbien.sql: added rm_query_group(), which removes all validations queries with a particular group prefix

schemas/vegbien.sql: generated ~type for _plots_04_count_of_plots_in_each_project_in_this_source

schemas/util.sql: added drop_relations_like()

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.)

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

schemas/vegbien.sql: added ~type tables for plots queries that are able to be run successfully

web/links/index.htm: updated to Firefox bookmarks: Roundtable: virtual collaboration: annotated the linked pages

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.

inputs/SALVIAS/validations.sql: added Brad's comments from validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql

added inputs/SALVIAS/validations*.sql

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

fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: renamed truncated views to match the output queries

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

validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: removed `LIMIT 10` to match output queries

fix: validation/aggregating/plots/SALVIAS/bien3_validations_salvias_db_original.VegCore.sql: updated table names to match our renamings

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

schemas/vegbien.sql: synced to DB, which changes the sort order (due to system update?)

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

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

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"

/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

bin/psql_verbose_vegbien: use \\ instead of \ inside '' because this is sh, not bash

bin/psql_verbose_vegbien: changed prep-statement order to match lib/sh/db.sh psql()

bin/psql_verbose_vegbien: use `\set VERBOSITY terse` to hide stack traces/DETAIL sections of error messages, like in lib/sh/db.sh psql()

bin/make_analytical_db: added `public_validations.remake_diff_tables()`

fix: schemas/vegbien.sql: public_validations.rename_query_view(): fixed usage

schemas/vegbien.sql: added remake_diff_tables() with no args, which remakes all validatable datasources

schemas/vegbien.sql: added validatable_datasources()

schemas/vegbien.sql: added validatable_datasources()