sql_gen.py: Added reraise_exc

schemas/py_functions.sql: _date(): Raise (or pass through) ValueErrors directly instead of wrapping them in FormatExceptions, to simplify the code. This will also enable later translation of ValueErrors to data_exceptions. When year is required and missing, output a parsable 'null value in column year violates not-null constraint' error.

sql_io.py: put_table(): log_exc(): Handle infinite loops from repeated exceptions by removing all rows, instead of just aborting with a failed assertion

sql_io.py: put_table(): is_function: Fixed bug where special case for unrecoverable errors needed to avoid creating an empty output pkeys table because function mode defines the returned pkeys table separately

sql_io.py: put_table(): is_function: Factored defining the error handling wrapper function out of the main loop because it only needs to run once. Don't log "Trying to insert new rows" in function mode because it's inaccurate.

sql_gen.py: Exceptions: Added suppress_exc and use it in ExcHandler.to_str()

README.TXT: Backups: After a new import: Added step to delete previous imports so they won't bloat the full DB backup. (Note that these imports have already been backed up, and only the most recent import needs to be live in the DB.)

README.TXT: Backups: Documented what to do after a new import

backups/Makefile: Full DB: Added vegbien.backup/all to run both test and rotate

README.TXT: Renamed Maintenance section to Backups for clarity

backups/Makefile: .sql: When testing, turn it off so make won't skip `.sql: %` in favor of it

backups/Makefile: Split %.backup and %.sql into separate targets for clarity

inputs/import.stats.xls: Updated with stats from latest import. Note that this import adds data provider feedback for SQL functions as well as additional date processing using _date().

schemas/py_functions.sql: _date(): Re-enabled now that exceptions thrown are properly handled. FormatException: Support raising parsable data_exceptions when provided with the value that was invalid. Date parsing mode: Return date as the value in FormatException so it can be filtered out automatically by column-based import.

sql_io.py: put_table(): is_function: Creating error handling wrapper function: Fixed bug where needed to cast NULL returned in error handler to appropriate type, because it's contained within a SELECT query which does not do implicit casts from type unknown

sql_gen.py: Cast: Support types which are Code objects

sql_io.py: func_wrapper_exception_handler(): Use new sql_gen.merge_not_null() to try to ensure that NULL values are not folded (which would cause the concatenated values not to match up with the concatenated column names). Note that this adds a dependency on the db object, which callers must now provide.

sql_gen.py: Added merge_not_null()

sql_gen.py: Added try_mk_not_null()

sql_gen.py: Renamed ArrayJoin to ArrayMerge to avoid confusion with Join (a SQL construct)

sql_io.py: put_table(): is_function: Creating error handling wrapper function: Set srcs on row_var so that the column type and nullability info of row_var's columns can be retrieved for use with sql_gen.ensure_not_null()

sql_gen.py: RowExcIgnore.to_str(): Compare self.row_var to global const row_var using == to allow caller to provide a copy of row_var with the underlying table set appropriately

sql_gen.py: underlying_table(): Support derived tables and row vars by obtaining the underlying table from the srcs

sql_io.py: put_table(): Setting pkeys of missing rows: Fixed bug where also needed to do this when is_function if an empty pkeys table was created (due to an error that could not be localized to a row)

sql_io.py: put_table(): After main loop: If is_literals, return immediately to avoid needing to test for is_literals in all the code that follows (which only applies to the normal case)

sql_gen.py: RowExcIgnore: If a custom row_var is used, require it to already be defined. This also allows sql_io.ExcToErrorsTable to place the column var definition in the outer DECLARE, eliminating the extra DECLARE block.

sql_io.py: put_table(): is_function: Creating error handling wrapper function: Use new sql_gen.row_var

sql_gen.py: RowExcIgnore: Created global constant for default row_var for callers to use

sql_gen.py: RowExcIgnore.to_str(): Moved SQL comment explaining the use of an EXCEPTION block for each individual row to Python code to avoid cluttering the logged SQL code

sql_io.py: put_table(): is_function: Creating error handling wrapper function: Handle errors using new func_wrapper_exception_handler(), which saves any data_exceptions in the errors table in addition to handling PL/Python errors

sql_io.py: Added func_wrapper_exception_handler()

sql_gen.py: Added ArrayJoin

sql_gen.py: Added Array and to_Array()

sql_gen.py: Added List and inherit from it in Tuple

sql_gen.py: Renamed Tuple to Row and List to Tuple to more accurately reflect the datatype generated by each class (a Tuple being merely a grouping of values)

sql_gen.py: Moved Composite types to Literal values section as a subsection, since Composite types was really about just the input syntaxes for these types

sql_gen.py: Replaced srcs_str() with cross_join_srcs() which more correctly combines the srcs of each column using a Cartesian product. Eventually, the entire tree of srcs will need to be preserved instead of flattened in order to properly attribute errors to a specific column or set of columns.

sql_gen.py: srcs_str(): Fixed bug where needed to filter out columns with no srcs so that there aren't empty elements in the ","-separated list

sql_gen.py: Added has_srcs()

sql_gen.py: Added NestedExcHandler

sql_gen.py: Added srcs_str()

sql_gen.py: as_Col(): Support non-Code, non-string inputs by making them Literals

sql_gen.py: Added is_col() and use it in is_table_col()

sql_io.py: ExcToErrorsTable: Require users to explicitly specify an expression for the value that caused the error, instead of assuming that a variable named "value" already exists. This allows a value expression to be computed only if needed for error handling.

sql_gen.py: Moved repr() from ExcHandler to BaseExcHandler

sql_gen.py: Added BaseExcHandler and inherit from it in ExcHandlers

sql_io.py: cast(): Determining if will be saving errors: Don't add extra check if isinstance(col, sql_gen.Col) because the special case for sql_gen.Literal handles supported non-columns

sql_io.py: data_exception_handler(): Removed no longer needed db param

sql_io.py: Added ExcToErrorsTable, which separates out the errors table inserting code from the exception handling code. data_exception_handler(): Refactored to use new sql_gen.data_exception_handler() and ExcToErrorsTable.

sql_gen.py: Added data_exception_handler

sql_io.py: data_exception_handler(): Refactored to use new sql_gen.ExcToWarning when not using an errors table

sql_gen.py: Added ExcToWarning

schemas/vegbien.sql: taxondetermination: taxondetermination_taxonoccurrence_id_fkey(): Fixed bug where string containing a \-escape needed an "E" prefix

sql_io.py: data_exception_handler(): Require the caller to provide a statement to return a default value in case of error, rather than assuming the caller can accept a return value of NULL

sql_io.py: data_exception_handler(): Refactored to use new sql.define_func()

sql_io.py: put_table(): is_function: Calling function on input rows: Convert PL/Python exceptions (internal_errors) to data_exceptions using sql_gen.plpythonu_error_handler and an error handling wrapper function

debug2redmine.csv: EXPLAIN comments: Fixed bug where needed to also match whitespace at beginning of line (indent)

Use sql_gen.ReturnQuery where RETURN QUERY was previously manually prepended

sql_gen.py: Added ReturnQuery

sql.py: define_func(): Fixed bug where next_version() needed to have module name removed since it's in the same module

sql.py: mk_select(): Added explain param to turn off automatically running EXPLAIN on the created query. This is useful for SELECT statements which use local variables in PL/pgSQL functions.

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