sql_gen.py: with_table(): Only set the table if the passed-in value is a Col or FunctionCall

sql_gen.py: Added Tuple

sql_gen.py: Added List and use it in Values.to_str()

sql.py: Added define_func()

Use sql_gen.SetOf where SETOF was previously manually prepended

sql_gen.py: Added SetOf

sql_gen.py: FunctionDef: Support return_types which are Code objects

Use sql_gen.ColType where %TYPE was previously manually appended

sql_gen.py: Added ColType

Use sql_gen.RowType where %ROWTYPE was previously manually appended

sql_gen.py: Added RowType

sql_gen.py: RowExcIgnore: Accept row types which are Code objects

sql_gen.py: TypedCol: Accept types which are Code objects

sql_io.py: data_exception_handler(): Documented that the invalid value must be in a local variable of type text

sql_io.py: data_exception_handler(): Documented that the invalid value must be in a local variable of type text

sql_io.py: put_table(): is_function: Creating empty pkeys table so its row type can be used: Don't do this if is_literals because special error handling does not apply to that

sql_io.py: put_table(): is_function: Create empty pkeys table before calling function on all rows so its row type can later be used in an error handling wrapper function

input.Makefile: Staging tables: import/install-%: Run csv2db with a nice increment of +5 to avoid interfering with the user's other processes

root map: Run bin/map with a nice increment of +5 to avoid interfering with the user's other processes

sql_io.py: put_table(): Handle psycopg2.extensions.TransactionRollbackError by retrying the last query

sql_io.py: Creating an empty output pkeys table: Assert that there are no join columns, so that the input pkeys table will be created correctly for the empty output pkeys table

sql_io.py: put_table(): Creating an empty output pkeys table: Added "output" to clarify that the created table contains just the output pkeys, and must be joined with the input pkeys table

sql_gen.py: FunctionDef: Renamed args to params

sql_gen.py: FunctionDef: Accept parameters as FunctionParam objects instead of strings

sql_gen.py: Added FunctionParam

sql_gen.py: Added plpythonu_error_handler

Autogenerated SQL code: Use new strings.indent() where needed

strings.py: Added indent()

sql_io.py: data_exception_handler(): Refactored to use sql_gen.RowExcIgnore

sql_io.py: cast(): Refactored to use sql_gen.FunctionDef

sql_gen.py: ExcHandler: Removed extra newline after handler

sql.py: mk_insert_select(): ignore: RETURN QUERY statement: Added back missing newline after ';'

sql_gen.py: FunctionDef: Added support for parameters

sql_io.py: cast(): Just use the first word of the type in the function name to help avoid name collisions. Note that type name collisions that may be introduced by this change are not a problem because the function name is versioned. (The caching mechanism prevents versioning when the function has the same name and definition as an already-defined function.)

sql_io.py: Added data_exception_handler() and use it in cast()

sql_gen.py: ExcHandler.to_str(): Removed extra newline after body

sql_gen.py: ExcHandler: Added repr() since it's not a Code object

sql_gen.py: FunctionDef: Support custom function modifiers

sql_gen.py: RowExcIgnore: Changed exc param to exc_handler to allow user to specify handler code for the exception

sql_gen.py: Added ExcHandler, unique_violation_handler

sql_gen.py: RowExcIgnore: Don't automatically add 'RETURN QUERY' before the with_row code or ';' after it

sql_gen.py: RowExcIgnore: Allow user to specify a custom row var name

sql_gen.py: FunctionDef: Don't automatically add 'SETOF ' before the return type

sql.py: mk_insert_select(): embeddable: Use new sql_gen.RowExcIgnore

sql_gen.py: Added RowExcIgnore

sql_gen.py: FunctionDef: Determine the lang from the body's Code object instead of receiving it as a parameter

sql_gen.py: as_Code(): Fixed bug where needed to handle inputs that are already Code objects

sql_gen.py: Code: Added lang instance var

sql_gen.py: Fixed bug where Code subclasses needed to call Code.__init__() in their init() function. BasicObject: Fixed bug where init() expected a value param, when in fact the value param is something added by certain subclasses.

sql_gen.py: FunctionDef: body param: Support Code inputs in addition to strings

sql.py: mk_insert_select(): embeddable: Use new sql_gen.FunctionDef

sql_gen.py: Added FunctionDef

README.TXT: Schema changes: Documented how to reinstall errors tables

csv2db: Creating errors table: Only drop existing errors table in errors_table_only mode, so that errors tables are not unintentionally deleted when `make inputs/install` is run. This helps to make `make install` idempotent.

README.TXT: Maintenance: Full DB: Changed commands to autorotate the created backup and then test and restore a rotated backup

backups/Makefile: Added %.backup/rotate

backups/Makefile: Rearranged sections so that backup targets, which apply to both Archived imports and Full DB, are at the top of a common Backups section

inputs/import.stats.xls: Updated with stats from latest import

schemas/py_functions.sql: Disabled _date() because it does not yet output errors in a format parsable by the import process, and the import process does not yet trap errors produced by SQL functions

sql_io.py: put_table(): Determining if can use optimization for only literal values: Fixed bug where needed initial value for reduce()

sql_io.py: put_table(): Needing >= one column for INSERT SELECT: Fixed bug where can't add pkey column if calling a function instead of outputting to a table

sql_io.py: put_table(): Optimization for only literal values: Also support an empty in_tables list, for use by put()

sql_io.py: put_table(): Added optimization for only literal values, which does the same operations as put() but with the additional error handling of put_table()

pg_dump_vegbien: Don't use SET SESSION AUTHORIZATION because it doesn't work with the py_functions schema (it requires PL/Python functions to be created as user postgres and then the owner changed to bien, which SET SESSION AUTHORIZATION won't do)

sql_gen.py: Added is_literal() and use it where isinstance(..., Literal) is used

db_xml.py: put_table(): Divide fields into input columns and literal values: Translate values: Allow literal values other than strings or None (from the XML parsing), because sql_io.put_table() is getting an optimization for iterations containing only literal values, which just returns the pkey of the single row for these values (which is usually an integer) instead of a temp table with the same value in each row

bin/map: by_col: Stripping XML functions not in the DB: Remove DB functions based on whether a plain SQL function of that name exists, rather than whether a relational function (i.e. a table) of that name exists. This will allow column-based import to use plain SQL functions that don't have a corresponding relational function.

db_xml.py: Don't remove any explicit pkey because the output table may be a SQL function, which does not have a pkey. This feature only existed to support importing VegBank XML exports, which we don't use (and which would be incompatible with the schema anyway).

sql.py: function_exists(): Fixed bug where select() needed to be run with auto-rollback in case it raised an exception

xml_func.py: process(): Changed rel_funcs param to a callback is_rel_func, so that caller can specify any dynamic function to determine if a name is a relational function rather than having to list out all known relational functions

sql.py: function_exists(): Use simpler cast to regproc instead of query of information_schema.routines to determine if function exists. When the schema is not specified, this also limits the schemas checked to the search_path instead of the whole DB.

schemas/functions.sql, py_functions.sql: Renamed trigger functions to avoid collisions with plain SQL functions of the same name but different signatures, so that the plain SQL functions can be uniquely identified by their name without also requiring their signature

sql.py: mk_select(): In queries without a FROM clause, don't order by pkey

sql.py: mk_select(): Support queries without a FROM clause

sql.py: Added DoesNotExistException and parse it in run_query()

sql_io.py: put_table(): Removed no longer used conds var (invalid rows are removed from the in_table using sql.delete() instead of being filtered out in the main select)

sql_io.py: put_table(): Removed no longer used distinct_on var (sql.distinct_table() handles filtering the join_cols)

schemas/py_functions.sql: _date(): Just run str() on the returned datetime because it will usually be converted to a PostgreSQL timestamp anyway, so excluding the time from the string isn't necessary

schemas/py_functions.sql: Added _date()

sql.py: run_query(): Exception parsing: Remove PL/Python prefix from exception message so that the regexps can match at the beginning of the message

sql_io.py: put_table(): Handle sql.InvalidValueExceptions by filtering the value out of all input columns. This will be useful for SQL functions that raise exceptions.

schemas/vegbien.sql: namedplace, plantname: *_unique UNIQUE INDEX: Reordered columns to put rank after parent_id and plantname so that these columns, which are usually input table columns, can be used in a merge join index scan, while rank, which is usually a literal value, can applied as an index filter condition after the merge join

sql.py: distinct_table(): Removed literal values from UNIQUE INDEXes because the query planner did not seem to use them to do a merge join

README.TXT: Maintenance: Full DB: Documented how to test full DB backup

backups/Makefile: Added %.backup/test

README.TXT: Documented maintenance of full DB (back up/restore)

backups/Makefile: Full DB backups: Added vegbien.backup

pg_dump_vegbien: If first arg is "all", dump entire DB. Require a first arg so that Usage message will be displayed if run with no args.

Always output Usage messages to stderr and word-wrap them using `fold -s`

backups/Makefile: Factored backup command into $(backup) for later use by full DB backups. Made Backups, Archived imports sections subsections of Archived imports so Full DB backups can have its own section.

backups/Makefile: Fixed bug where $(SHELL) needed to be set to bash so that $'\n' would be interpreted correctly

backups/Makefile: Fixed bug where *.sql files needed to be restored using psql because pg_restore only supports "non-plain-text formats"

pg_dump_vegbien: For consistency with setting the --schema option, use `set -- "$" args...` to append options to $ which are then passed to pg_dump, instead of specifying several variables which are then included in the pg_dump command

pg_dump_vegbien: Pass command line options directly to pg_dump after parsing out any schema name

backups/Makefile: Don't log stderr or run the command verbosely and instead just output the command and run time to the terminal. This matches what we do for pg_dump, which works better because it just prints the useful information when it's done running.

backups/Makefile: Remove log files after successful restore/extraction because they are only useful for tail -f when the restore operation is running in the background

pg_dump_vegbien: Save owners when saving data (for full export)

pg_dump_vegbien: Use SET SESSION AUTHORIZATION to ensure that owners are always recorded in the same format. This will help make plain text backups comparable using diff.

backups/Makefile: Backups: Fixed bug where `%.sql: %` needed to come before %.sql with no prerequisites to be matched first

Moved archived imports and make targets to maintain them to new backups dir