sql.py: put_table(): Add index on columns with invalid values to enable fast filtering

sql.py: Added index_col()

sql.py: put_table(): Add pkey on returned pkeys table to enable fast joins

sql.py: Added index_pkey()

sql.py: mk_update(): When running sql_gen.to_name_only_col(), check that the col's table is table

sql.py: put_table(): Renamed pkeys to insert_pkeys to distinguish them from the full set of pkeys on the input table

sql.py: put_table(): FunctionValueException: Change invalid values to NULL using UPDATE instead of filtering them out using WHERE, to avoid adding lots of conditions to the SELECT statement

sql.py: Added mk_update() and update()

sql_gen.py: Added to_name_only_col()

sql_gen.py: Added as_Value()

sql.py: mk_select(): conds: Use new sql_gen.ColValueCond instead of sql_gen.as_ValueCond(). Documented that Code and ValueCond are sql_gen objects.

sql_gen.py: Added ColValueCond

sql.py: mk_flatten_mapping(): Filter str(col) through clean_name() to remove quotes, etc.

sql.py: Added clean_name()

sql.py: put_table(): Join together input tables into new table for speed and so don't modify input if values edited

sql.py: mk_flatten_mapping(): Take as_items param to return a list of dict items instead of a dict. Sort preserve cols before other cols. flatten(): Turn on as_items so that cols list is sorted in input order, with preserve cols first. This ensures that if a pkey is provided in preserve, it will be the first col in the generated table.

sql.py: mk_flatten_mapping(), flatten(): Take list of cols to select instead of using all cols in all tables to join

sql.py: mk_flatten_mapping(), flatten(): Renamed flat_table param to into to be consistent with run_query_into() and put it first because it is the output param

sql.py: Added flatten()

sql.py: mk_flatten_mapping(): preserve Col objects will have tables changed to flat_table to work with flattened table

sql.py: mk_flatten_mapping(): Added preserve param for list of columns not to rename

sql.py: esc_name_by_module(): Support module value None, and use default module psycopg2 for it

sql.py: put_table(): Renamed *pkeys_ref to *pkeys to reflect that they are now objects rather than an array-based references

sql.py: run_query_into(): Renamed into_ref param to into to reflect that it's now an object rather than an array-based reference

sql.py: run_query_into(): Made into_ref a sql_gen.Table instead of an array containing a table name to improve flexibility and clarity

dicts.py: Added join()

sql.py: Added mk_flatten_mapping()

sql.py: put_table(): Renamed the copy of in_tables that gets modified to in_tables_, so that the original list can eventually be reused in joining together the input tables into a temp table

sql.py: run_query(): FunctionValueException: Also match "date/time field value out of range" errors

sql.py: put_table(): conds: Use a set instead of a list for faster checking of the "cond not in conds" assertion

sql.py: mk_select(): conds: Support containers of any iterable type

sql.py: put_table(): Made conds a list so that there can be multiple conditions on the same column

sql.py: mk_select(): conds is list of (key, value) tuples instead of dict (dict still supported for compatibility), so that there can be multiple conditions on the same column

sql.py: mk_select(): conds is list of (key, value) tuples instead of dict (dict still supported for compatibility), so that there can be multiple conditions on the same column

util.py: NamedTuple inherits from objects.BasicObject so that it's comparable and hashable. This fixes a bug in dicts.make_hashable() where the NamedTuple created for a dict would appear to be hashable but would always compare as unequal.

sql.py: DbConn.esc_value(): Run strings.to_unicode() on the generated string so that if it contains unescaped non-ASCII characters, these will not cause problems when concatenated with plain strings

sql.py: run_query(): FunctionValueException: Unpack match.groups() into vars to make code clearer

exc.py: str_(): Avoid traceback exception-formatting functions when possible because they escape non-ASCII characters

sql.py: get_cur_query(): If no raw query: Use strings.ustr() instead of repr() to ensure that if the exception is parsed, embedded quotes will not be double-escaped. Prefix the query by [input] to show that it's not the raw query.

sql_gen.py: Non-Code objects: str() passes informative placeholder string to self.to_str() instead of empty string

sql.py: ExceptionWithNameValue: Use repr() instead of strings.ustr() on the value

sql.py: run_query(): Exception parsing: Use non-greedy qualifier "?" in regexps wherever possible to avoid matching closing quotes later in the error message

sql_gen.py: MockDb.esc_value(): Use repr() instead of strings.ustr() so the quotes around the value are included

sql_gen.py: ValueCond and Join class hierarchies inherit from objects.BasicObject like Code does

sql.py: put_table(): ignore(): Fixed bug where value needed to be filtered through repr(). NullValueException: Fixed bug where value passed to ignore() was the string 'NULL' instead of the value None.

mappings/DwC2-VegBIEN.specimens.csv: plantname.rank: Filter through _toTaxonrank

sql.py: put_table(): ignore(): Avoid infinite loops by asserting that in_col is not in conds

objects.py: BasicObject: Fixed bug where util needed to be imported. Added eq() and hash().

strings.py: Removed no longer used DebugPrintable (that functionality is now in objects.BasicObject)

sql_gen.py: Code: Inherit from new objects.BasicObject

Added objects.py

sql.py: put_table(): Renamed log_ignore() to ignore() and factored common conds-modifying code into it

sql.py: put_table(): Moved post-insert code outside while loop because it will now always be run (there are no longer special cases where the postprocessing doesn't happen)

sql.py: put_table(): Missing mapping for NOT NULL column: Just create an empty pkeys table, since the missing rows' pkeys will be set to NULL later

sql.py: put_table(): Joining together output and input pkeys: Use new sql_gen.join_same_not_null

sql.py: put_table(): Setting missing rows' pkeys to NULL: Use new sql_gen.join_same_not_null

sql_gen.py: Join: Added join_same_not_null. to_str(): Refactored to switch order of left and right tables and cols because left_table is on the right in the comparison, and using the sides of the comparison instead of the sides of the join makes the code clearer.

sql_gen.py: Renamed join_using to join_same to reflect that it can also be used without USING

sql.py: put_table(): Set missing rows' pkeys to NULL

sql.py: put_table(): NullValueException: no mapping for missing col: Fixed bug where run_query_into_pkeys() was still using insert_joins instead of input_joins

sql_gen.py: Added MockDb. All str() methods: Use self.to_str() with mockDb.

sql_gen.py: Use db.esc_name() instead of sql.esc_name(db, ...) so passed-in db can be a mock object

sql.py: DbConn: Added esc_name()

db_xml.py: put_table(): Debug-print which columns are being put

sql.py: ConstraintException, NullValueException: Improved error messages

sql.py: put_table(): FunctionValueException: Fixed bug where out_table was still assumed to be an escaped string, but is now a Table object

sql.py: mk_select(): joins: Use new table_not_null_col() instead of pkey() to get a non-NULL column to filter out on

exc.py: add_msg(): Fixed bug where msg needed to be converted to a unicode object before appending it to another unicode object

mappings/VegX-VegBIEN.stems.csv: Fixed bug where taxonfit was named taxonFit. (This was only recently discovered because column names are now escaped, causing them not to be case-insensitive.)

sql.py: Added table_not_null_col()

sql.py: Added table_cols() and use it in pkey()

schemas/vegbien.sql, schemas/functions.sql: Relational functions: Added dummy not_null column to provide a column to use in LEFT JOIN filter-out filters

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

sql_gen.py: Added NamedTable. Table: Added to_Table().

sql_gen.py: Added section labels for each type of SQL code object

sql.py: put_table(): DuplicateKeyException: Fixed bug where dict_subset_right_join() was used instead of dict_subset(), adding spurious None values for columns in the constraint which are not in the input tables

sql_gen.py: as_Col(): Don't allow None cols

schemas/vegbien.ERD.mwb: Synced with schemas/vegbien.sql

sql.py: Removed no longer used clean_name()

sql.py: mk_insert_select(): embeddable: Removed clean_name() because the function name is now escaped where it's used

sql.py: put_table(): Added support for out_table values that are Table objects

sql.py: mk_insert_select(): Fixed bug where table for creating the returning column Col object was the already-escaped string, instead of the Table object

sql.py: mk_insert_select(): Fixed bug where function name and returning col were not being escaped

sql.py: put_table(): log_ignore(): Fixed bug where in_col needed to be passed through str() because it's a column object

sql.py: put_table(): Fixed bug where the filter_out join should only be used in the insert, not in the select of existing/inserted rows. insert_select() call: Fixed compatibility bug where old versions of Python did not support mixing keyword args and ** args.

sql.py: put_table(): Fixed bug where "add_row_num(db, out_pkeys_ref⁰)" was mistakenly put under the "if row_ct_ref != None" if statement

sql_gen.py: Renamed NamedCode to NamedCol to better reflect its specific use

sql.py: Removed unnecessary calls to check_name()

sql.py: mk_insert_select(): Fixed bug where returning col was not being escaped

sql.py: add_row_num(): Fixed bug where table name was not being escaped

sql.py: run_query_into(): Fixed bug where into table name was not being escaped

sql.py: mk_insert_select(): Fixed bug where utput column names were not being escaped

sql.py: put_table(): Fixed bug where only string columns were being included in the distinct_on, but columns are now always sql_gen.Col instances

sql.py: put_table(): Put together varying insert_select() args using dict instead of individual vars

sql.py: mk_select(): Fixed bug where order_by needed to default to None if distinct_on was used. Fixed bug where cond values were being treated as %s params in addition to being parsed by sql_gen.as_ValueCond().to_str().

sql_gen.py: Col: Added to_Col()

db_xml.py: put_table(): Accept sql_gen.Table objects or strings instead of separate table and schema names

sql.py: put_table(): Require all in_table_cols to be sql_gen.Col objects

sql_gen.py: ValueCond: Unwrap NamedCode objects

sql_gen.py: NamedCode: Inherit from Col so that its name can be retrieved using the same attribute as Col's