Revision 3459
Added by Aaron Marcuse-Kubitza over 12 years ago
| lib/sql_io.py | ||
|---|---|---|
| 37 | 37 |
cacheable=True, log_level=4) |
| 38 | 38 |
except sql.DuplicateKeyException: pass |
| 39 | 39 |
|
| 40 |
def data_exception_handler(db, srcs=[], errors_table=None): |
|
| 41 |
'''Handles a data_exception by saving the error or converting it to a |
|
| 42 |
warning, and returning NULL. |
|
| 43 |
@param srcs The column names for the errors table |
|
| 44 |
@param errors_table None|sql_gen.Table |
|
| 45 |
''' |
|
| 46 |
save_errors = errors_table != None and srcs |
|
| 47 |
handler = '' |
|
| 48 |
if save_errors: |
|
| 49 |
errors_table_cols = map(sql_gen.Col, |
|
| 50 |
['column', 'value', 'error_code', 'error']) |
|
| 51 |
handler += '''\ |
|
| 52 |
-- Save error in errors table. |
|
| 53 |
DECLARE |
|
| 54 |
error_code text := SQLSTATE; |
|
| 55 |
error text := SQLERRM; |
|
| 56 |
"column" text; |
|
| 57 |
BEGIN |
|
| 58 |
-- Insert the value and error for *each* source column. |
|
| 59 |
FOR "column" IN |
|
| 60 |
'''+sql.mk_select(db, sql_gen.NamedValues('c', None, [[c.name] for c in srcs]),
|
|
| 61 |
order_by=None)+''' |
|
| 62 |
LOOP |
|
| 63 |
BEGIN |
|
| 64 |
'''+sql.mk_insert_select(db, errors_table, errors_table_cols, |
|
| 65 |
sql_gen.Values(errors_table_cols).to_str(db))+'''; |
|
| 66 |
EXCEPTION |
|
| 67 |
WHEN unique_violation THEN NULL; -- continue to next row |
|
| 68 |
END; |
|
| 69 |
END LOOP; |
|
| 70 |
END; |
|
| 71 |
''' |
|
| 72 |
else: |
|
| 73 |
handler += '''\ |
|
| 74 |
RAISE WARNING '%', SQLERRM; |
|
| 75 |
''' |
|
| 76 |
handler += '''\ |
|
| 77 |
RETURN NULL; |
|
| 78 |
''' |
|
| 79 |
return sql_gen.ExcHandler('data_exception', handler)
|
|
| 80 |
|
|
| 40 | 81 |
def cast(db, type_, col, errors_table=None): |
| 41 | 82 |
'''Casts an (unrenamed) column or value. |
| 42 | 83 |
If errors_table set and col has srcs, saves errors in errors_table (using |
| ... | ... | |
| 51 | 92 |
|
| 52 | 93 |
assert not isinstance(col, sql_gen.NamedCol) |
| 53 | 94 |
|
| 95 |
function_name = type_ |
|
| 96 |
srcs = col.srcs |
|
| 54 | 97 |
save_errors = (errors_table != None and isinstance(col, sql_gen.Col) |
| 55 | 98 |
and col.srcs != ()) |
| 56 |
function_name = type_ |
|
| 57 | 99 |
if save_errors: |
| 58 |
errors_table = sql_gen.as_Table(errors_table) |
|
| 59 |
|
|
| 60 | 100 |
srcs = map(sql_gen.to_name_only_col, col.srcs) |
| 61 | 101 |
function_name = str(sql_gen.FunctionCall(function_name, *srcs)) |
| 62 | 102 |
function = db.TempFunction(function_name) |
| ... | ... | |
| 72 | 112 |
query += '''\ |
| 73 | 113 |
STRICT |
| 74 | 114 |
AS $$ |
| 75 |
BEGIN |
|
| 115 |
''' |
|
| 116 |
query += data_exception_handler(db, srcs, errors_table).to_str(db, '''\ |
|
| 76 | 117 |
/* The explicit cast to the return type is needed to make the cast happen |
| 77 | 118 |
inside the try block. (Implicit casts to the return type happen at the end |
| 78 | 119 |
of the function, outside any block.) */ |
| 79 | 120 |
RETURN value::'''+type_+'''; |
| 80 |
EXCEPTION |
|
| 81 |
WHEN data_exception THEN |
|
| 82 |
''' |
|
| 83 |
if save_errors: |
|
| 84 |
errors_table_cols = map(sql_gen.Col, |
|
| 85 |
['column', 'value', 'error_code', 'error']) |
|
| 86 |
query += '''\ |
|
| 87 |
-- Save error in errors table. |
|
| 88 |
DECLARE |
|
| 89 |
error_code text := SQLSTATE; |
|
| 90 |
error text := SQLERRM; |
|
| 91 |
"column" text; |
|
| 92 |
BEGIN |
|
| 93 |
-- Insert the value and error for *each* source column. |
|
| 94 |
FOR "column" IN |
|
| 95 |
'''+sql.mk_select(db, sql_gen.NamedValues('c', None, [[c.name] for c in srcs]),
|
|
| 96 |
order_by=None)+''' |
|
| 97 |
LOOP |
|
| 98 |
BEGIN |
|
| 99 |
'''+sql.mk_insert_select(db, errors_table, errors_table_cols, |
|
| 100 |
sql_gen.Values(errors_table_cols).to_str(db))+'''; |
|
| 101 |
EXCEPTION |
|
| 102 |
WHEN unique_violation THEN NULL; -- continue to next row |
|
| 103 |
END; |
|
| 104 |
END LOOP; |
|
| 105 |
END; |
|
| 106 |
''' |
|
| 107 |
else: |
|
| 108 |
query += '''\ |
|
| 109 |
RAISE WARNING '%', SQLERRM; |
|
| 110 |
''' |
|
| 121 |
''') |
|
| 111 | 122 |
query += '''\ |
| 112 |
RETURN NULL; |
|
| 113 |
END; |
|
| 114 | 123 |
$$; |
| 115 | 124 |
''' |
| 116 | 125 |
|
Also available in: Unified diff
sql_io.py: Added data_exception_handler() and use it in cast()