sql_gen.py: Join.to_str(): Fixed bug where USING syntax could not be used for filter_out join type, because a separate right column is required for filtering
sql_gen.py: Use new table2sql_gen() in col2sql_gen(), join2sql_gen()
sql.py: mk_select(): joins: Convert all joins to sql_gen format using join2sql_gen()
sql_gen.py: Added table2sql_gen()
sql_gen.py: Added join2sql_gen()
sql_gen.py: Added as_Col(). as_ValueCond(): Added support for assuming the value is a column rather than a literal value, using the default_table param. Added Join.
sql_gen.py: Put parameterized SQL code objects in separate section
sql.py: put_table(): DuplicateKeyException: Assert that join_cols has changed to avoid infinite loops
sql.py: put_table(): Moved getting pkeys of already existing rows from DuplicateKeyException to try clause, so that it always runs if there are join_cols. DuplicateKeyException: Add new duplicate key cols to join_cols instead of replacing join_cols so that multiple unique constraints being violated causes the union of their columns to be used for join_cols.
sql_gen.py: Added CustomCode
sql.py: mk_select(): joins: Fixed bug where joins dict was being modified without first being copied, causing the input value to be modified
Compare object()-based sentinel values using is. Where sentinel values must be compared using ==, use rand.rand_int() instead.
sql.py: put_table(): Added debug messages for every action performed
sql.py: put_table(): Moved assignment of in_pkeys_ref outside loop so it wouldn't need to be re-versioned every iteration
sql.py: put_table(): Changed temp_suffix to temp_prefix so all temp tables for a given out_table would have the same prefix. (Existing name collisions due to truncated names are not a problem because version prefixes are automatically added.)
mappings/DwC2-VegBIEN.specimens.csv: Filter dates through _toTimestamp
schemas/functions.sql: Added _toTimestamp
mappings/DwC2-VegBIEN.specimens.csv: Filter coordsaccuracy through _toDouble
sql.py: FunctionValueException parsing: Support values containing non-word and non-ASCII characters
exc.py: Support exception messages containing non-ASCII characters
sql.py: put_table(): Print debug messages about how exceptions are being handled
sql.py: put_table(): After getting pkeys of already existing rows, insert new rows
sql.py: put_table(): Handle FunctionValueExceptions by excluding rows with the invalid value in their "value" column
sql.py: run_query(): Also parse "invalid input syntax at assignment" errors as FunctionValueExceptions
sql_gen.py: Col: Convert string table names to Table objects
sql.py: run_query(): Parse "invalid input value at assignment" errors' values as well
sql.py: run_query(): Parse "invalid input value at assignment" errors as FunctionValueExceptions
sql.py: mk_select(): joins: filter_out: Pass NULLs through. Use sql_gen.*2sql_gen() to add the left and right table names to the columns.
sql_gen.py: cond2sql_gen(): Take assume_col param and pass it to value2sql_gen()
sql.py: put_table(): Use table-qualified pkey col names whenever possible, to avoid ambiguous column references
mappings/DwC2-VegBIEN.specimens.csv: placenames: Convert ranks using _toPlacerank to work with multi-inserts
sql.py: DbConn._db(): Fixed bug where the isolation level was not set to "SERIALIZABLE" in a portable way
sql.py: mk_select(): distinct_on is turned off when distinct_on == [] rather than when it's None
schemas/vegbien.sql: Added _toPlacerank
schemas/vegbien.sql: Added _toTaxonrank
sql.py: put_table(): Handle NullValueExceptions by removing invalid rows
sql_gen.py: Added NamedCode
sql_gen.py: Added str() to base classes for debugging
sql.py: mk_select() (and sql_gen.py): Fixed bugs where literal strings were treated as literal values when they should have been treated as column names. Take default_table param to determine default table to use if a column doesn't have an explicit table. put_table(): mk_main_select(): Pass in_tables0 as mk_select()'s default_table.
sql.py: mk_select(): cond(): Run additional sql_gen translation functions cond2sql_gen() and col2sql_gen() on the left and right sides of the comparison
sql_gen.py: ValueCond: Fixed bug where values which are Code objects were being converted to Literals. Added cond2sql_gen().
sql.py: mk_select(): join(): Use cond() now that it supports sql_gen format
sql_gen.py: Added col2sql_gen() and use it in value2sql_gen()
sql_gen.py: CompareCond: By default, compare NULL values literally. Support operator values to pass NULLs through.
strings.py: remove_prefix(), remove_suffix(): Added removed_ref param
sql.py: mk_select(): parse_col(): Use sql_gen.value2sql_gen().to_str()
sql_gen.py: Added as_Table(), unescape_table(), value2sql_gen()
sql.py: mk_select(): Documented conds param
sql.py: mk_select(): cond(): Switched to using sql_gen so that custom conds would be supported
sql_gen.py: ValueCond.to_str(): Made value_code a Code object instead of a string, and renamed it to left_value to reflect where it goes. Added as_ValueCond().
sql.py: esc_value(): Fixed bug where db needed to be referenced through self
sql_gen.py: ValueCond.to_str(): Added value_code param
sql_gen.py: Literal, CompareCond: Implemented to_str(). ValueCond: Autoconvert literal values to Literals.
sql.py: DbConn: Added esc_value()
Moved SQL code generation classes from sql.py to new sql_gen.py. sql_gen.py: Added Code, Literal, ValueCond, and CompareCond. sql.py: Removed Query because we will use a different approach.
sql.py: Added Query, Table, Col
sql.py: get(): Fixed bug where limit=1 needs to be passed to select() as a keyword arg now that the distinct_on param comes before it
sql.py: put_table(): mk_main_select(): Pass outer var conds to mk_select()
sql.py: put_table(): mk_select_(): Fixed bug where it was sometimes being called without distinct_on, causing it to return a different # of rows. Renamed mk_select_() to mk_main_select() for clarity.
sql.py: put_table(): Do inserts and selects in a loop so that it will keep retrying the operation with additional constraints until it succeeds
sql.py: put_table(): mk_select_(): Fixed bug where order_by needed to be None because otherwise it wouldn't match the distinct_on cols if they were specified
sql.py: put_table(): insert_(): Fixed bug where distinct_on was not passed to mk_select_()
sql.py: put_table(): mk_select_(): Fixed bug where distinct_on needed to be passed as a keyword param
sql.py: put_table(): insert_() and mk_select_() take distinct_on param
sql.py: put_table(): Factored out code that inserts into pkeys table into run_query_into_pkeys() helper function
sql.py: mk_select(): Implemented DISTINCT ON according to the distinct_on param
sql.py: mk_select(): Added distinct_on param to set the columns to SELECT DISTINCT ON
sql.py: clean_name(): Convert names to lowercase so that PostgreSQL will behave the same whether the name is escaped with "" or not. This will help avoid bugs in code that uses temp tables created by the sql module.
sql.py: put_table(): Added order_by=None wherever rows were not supposed to be re-ordered. On DuplicateKeyException: Save existing pkeys in temp table for joining on.
db_xml.py: put_table(): Pass limit and start to sql.put_table()
db_xml.py: put_table(): Added limit and start options
sql.py: When creating a temporary entity (table, function, etc.), instead create it as a permanent entity in debug mode so it can be viewed after the program is run
sql.py: DbConn: Store whether in debug mode (log_debug != log_debug_none) for easy use by methods
bin/map: connect_db(): Turn on autocommit mode in debug mode if commit is on, so that incremental results can be seen in the DB
sql.py: DbConn: Use internal autocommit handling instead of DB connection autocommit attr to avoid autocommits inside a savepoint
sql.py: DbConn: Added autocommit option to turn on autocommit mode. Use set_session() instead of SQL command to set isolation level.
sql.py: mk_insert_select(): embeddable: Fixed bug where the function may do different things when run, because the function (and other statements whose cached strings depend on the function name) may be run after the function definition would have changed, by versioning the function name and using CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION so that its definition never changes
sql.py: Parse "function already exists" errors as DuplicateFunctionException
sql.py: mk_select(): joins: Fixed bug where join_not_equal did not do what it was designed for, which is filtering out matches of the join condition (before the bug fix, it effectively did a cross join with matching rows excluded, causing duplication of rows). Renamed join_not_equal to filter_out to reflect its intended use. Support table-scoped column names in the WHERE conds list.
sql.py: put_table(): Fixed bug where ORDER BY column needed to have table0 name prefixed (if it didn't already have a table name), to avoid ambiguous column references
sql.py: mk_select(): Fixed bug in joins where right_col had the table name prepended before it was copied for use with a different table name in join_using and join_not_equal
Mapped some unmapped fields in DwC inputs
Added mappings/for_review/DwC2-VegBIEN.specimens.fields.csv
db_xml.py: put_table(): Fixed bug where didn't commit right after inserting node, but instead waited until children with fkeys to parent (independent of the node itself) were inserted
sql.py: put_table(): insert_(): Use insert_select() instead of run_query_into() if new option pkeys_table_exists is on
sql.py: mk_select(): Support joins with !=
sql.py: mk_select(): Support only some join columns being join_using
sql.py: put_table(): Renamed in_joins to insert_joins and joins to select_joins for clarity
db_xml.py: put_table(): Support children with fkeys to parent
sql.py: mk_select(): Make tuple optional for None literal values
sql.py: put_table(): Removed "SELECT statement missing a WHERE, LIMIT, or OFFSET clause" warnings
bin/map: by_col: row_ct = 0 because it's unknown for now
mk_select(): Support join conditions with literal values
sql.py: mk_insert_select(): embeddable: Don't cache function_query because function def could change and then change back
sql.py: with_savepoint(): Renamed savepoints to have "level" prefix, since the # indicates the level #
sql.py: get_cur_query(): Also accept input params to combine with input_query, and pass input params when get_cur_query() is called
sql.py: DbConn.run_query(): Pass input query to get_cur_query()
sql.py: get_cur_query() and _add_cursor_info(): Support input_query param that will be used if the raw query is None. Pass input_query in DbConn.execute().
sql.py: DbConn.run_query(): Check that query != None
bin/map: out_is_db: Only rollback() and close() out_db if it was connected