Revision 2751
Added by Aaron Marcuse-Kubitza over 12 years ago
| lib/sql.py | ||
|---|---|---|
| 737 | 737 |
into=into) |
| 738 | 738 |
return dict(items) |
| 739 | 739 |
|
| 740 |
def mk_track_data_error(db, errors_table, cols, value, error_code, error): |
|
| 741 |
cols = map(sql_gen.to_name_only_col, cols) |
|
| 742 |
|
|
| 743 |
columns_cols = ['column'] |
|
| 744 |
columns = sql_gen.NamedValues('columns', columns_cols,
|
|
| 745 |
[[c.name] for c in cols]) |
|
| 746 |
values_cols = ['value', 'error_code', 'error'] |
|
| 747 |
values = sql_gen.NamedValues('values', values_cols,
|
|
| 748 |
[value, error_code, error]) |
|
| 749 |
|
|
| 750 |
select_cols = columns_cols+values_cols |
|
| 751 |
name_only_cols = map(sql_gen.to_name_only_col, select_cols) |
|
| 752 |
joins = [columns, sql_gen.Join(values, type_='CROSS'), |
|
| 753 |
sql_gen.Join(errors_table, dict(zip(name_only_cols, select_cols)), |
|
| 754 |
sql_gen.filter_out)] |
|
| 755 |
|
|
| 756 |
return mk_insert_select(db, errors_table, name_only_cols, |
|
| 757 |
*mk_select(db, joins, select_cols, order_by=None))[0] |
|
| 758 |
|
|
| 740 | 759 |
def cast(db, type_, col, errors_table=None): |
| 741 | 760 |
'''Casts an (unrenamed) column or value. |
| 742 | 761 |
If a column, converts any errors to warnings. |
| ... | ... | |
| 779 | 798 |
WHEN data_exception THEN |
| 780 | 799 |
''' |
| 781 | 800 |
if save_errors: |
| 782 |
cols = [sql_gen.Literal(c.name) for c in srcs] |
|
| 783 | 801 |
query += '''\ |
| 784 | 802 |
-- Save error in errors table. |
| 785 |
BEGIN |
|
| 786 |
-- Insert the value and error for *each* source column. |
|
| 787 |
INSERT INTO '''+errors_table.to_str(db)+''' |
|
| 788 |
("column", value, error_code, error)
|
|
| 789 |
SELECT * |
|
| 790 |
FROM (VALUES '''+(', '.join(('('+c.to_str(db)+')' for c in cols))
|
|
| 791 |
)+''') AS c |
|
| 792 |
CROSS JOIN (VALUES (value, SQLSTATE, SQLERRM)) AS v |
|
| 793 |
; |
|
| 794 |
EXCEPTION |
|
| 795 |
WHEN unique_violation THEN NULL; -- ignore duplicate key |
|
| 796 |
END; |
|
| 803 |
-- Insert the value and error for *each* source column. |
|
| 804 |
'''+mk_track_data_error(db, errors_table, srcs, |
|
| 805 |
*map(sql_gen.CustomCode, ['value', 'SQLSTATE', 'SQLERRM']))+'''; |
|
| 797 | 806 |
|
| 798 | 807 |
''' |
| 799 | 808 |
query += '''\ |
Also available in: Unified diff
sql.py: Added mk_track_data_error() and use it in cast(). This also ensures that if only one source column's row in the CROSS JOIN violates a unique constraint, other source columns' rows are still inserted.