860 |
860 |
if not create: raise
|
861 |
861 |
return put(db, table, row, pkey, row_ct_ref) # insert new row
|
862 |
862 |
|
863 |
|
def put_table(db, out_table, in_tables, mapping, row_ct_ref=None,
|
864 |
|
temp_prefix=None):
|
|
863 |
def put_table(db, out_table, in_tables, mapping, row_ct_ref=None, into=None):
|
865 |
864 |
'''Recovers from errors.
|
866 |
865 |
Only works under PostgreSQL (uses INSERT RETURNING).
|
867 |
866 |
@param in_tables The main input table to select from, followed by a list of
|
... | ... | |
869 |
868 |
@param mapping dict(out_table_col=in_table_col, ...)
|
870 |
869 |
* out_table_col: sql_gen.Col|str
|
871 |
870 |
* in_table_col: sql_gen.Col Wrap literal values in a sql_gen.NamedCol
|
872 |
|
@param temp_prefix The prefix of generated temp tables.
|
873 |
|
Defaults to out_table.name.
|
|
871 |
@param into The table to contain the output and input pkeys.
|
|
872 |
Defaults to `out_table.name+'_pkeys'`.
|
874 |
873 |
@return sql_gen.Col Where the output pkeys are made available
|
875 |
874 |
'''
|
876 |
875 |
out_table = sql_gen.as_Table(out_table)
|
877 |
876 |
for in_table_col in mapping.itervalues():
|
878 |
877 |
assert isinstance(in_table_col, sql_gen.Col)
|
879 |
|
if temp_prefix == None: temp_prefix = out_table.name
|
|
878 |
if into == None: into = out_table.name+'_pkeys'
|
|
879 |
into = sql_gen.as_Table(into)
|
880 |
880 |
|
881 |
881 |
def log_debug(msg): db.log_debug(msg, level=1.5)
|
882 |
882 |
|
... | ... | |
884 |
884 |
log_debug('Inserting these input columns into '
|
885 |
885 |
+strings.as_tt(out_table.to_str(db))+':\n'+strings.as_table(mapping))
|
886 |
886 |
|
887 |
|
pkeys = sql_gen.Table(temp_prefix+'_pkeys')
|
888 |
|
|
889 |
887 |
# Create input joins from list of input tables
|
890 |
888 |
in_tables_ = in_tables[:] # don't modify input!
|
891 |
889 |
in_tables0 = in_tables_.pop(0) # first table is separate
|
... | ... | |
896 |
894 |
|
897 |
895 |
log_debug('Joining together input tables into temp table')
|
898 |
896 |
# Place in new table for speed and so don't modify input if values edited
|
899 |
|
in_table = sql_gen.Table(temp_prefix+'_in')
|
|
897 |
in_table = sql_gen.Table(into.name+'_in')
|
900 |
898 |
flatten_cols = filter(sql_gen.is_table_col, mapping.values())
|
901 |
899 |
mapping = dicts.join(mapping, flatten(db, in_table, input_joins,
|
902 |
900 |
flatten_cols, preserve=[in_pkey_col], start=0))
|
... | ... | |
913 |
911 |
def insert_into_pkeys(joins, cols):
|
914 |
912 |
query, params = mk_select(db, joins, cols, order_by=None, start=0)
|
915 |
913 |
if pkeys_table_exists_ref[0]:
|
916 |
|
insert_select(db, pkeys, pkeys_names, query, params)
|
|
914 |
insert_select(db, into, pkeys_names, query, params)
|
917 |
915 |
else:
|
918 |
|
run_query_into(db, query, params, into=pkeys)
|
|
916 |
run_query_into(db, query, params, into=into)
|
919 |
917 |
pkeys_table_exists_ref[0] = True
|
920 |
918 |
|
921 |
919 |
limit_ref = [None]
|
... | ... | |
947 |
945 |
|
948 |
946 |
# Do inserts and selects
|
949 |
947 |
join_cols = {}
|
950 |
|
insert_out_pkeys = sql_gen.Table(temp_prefix+'_insert_out_pkeys')
|
951 |
|
insert_in_pkeys = sql_gen.Table(temp_prefix+'_insert_in_pkeys')
|
|
948 |
insert_out_pkeys = sql_gen.Table(into.name+'_insert_out_pkeys')
|
|
949 |
insert_in_pkeys = sql_gen.Table(into.name+'_insert_in_pkeys')
|
952 |
950 |
while True:
|
953 |
951 |
has_joins = join_cols != {}
|
954 |
952 |
|
... | ... | |
1023 |
1021 |
insert_into_pkeys(pkey_joins, pkeys_names)
|
1024 |
1022 |
|
1025 |
1023 |
db.log_debug('Adding pkey on pkeys table to enable fast joins', level=2.5)
|
1026 |
|
index_pkey(db, pkeys)
|
|
1024 |
index_pkey(db, into)
|
1027 |
1025 |
|
1028 |
1026 |
log_debug("Setting pkeys of missing rows to NULL")
|
1029 |
|
missing_rows_joins = input_joins+[sql_gen.Join(pkeys,
|
|
1027 |
missing_rows_joins = input_joins+[sql_gen.Join(into,
|
1030 |
1028 |
{in_pkey: sql_gen.join_same_not_null}, sql_gen.filter_out)]
|
1031 |
1029 |
# must use join_same_not_null or query will take forever
|
1032 |
1030 |
insert_into_pkeys(missing_rows_joins,
|
1033 |
1031 |
[in_pkey_col, sql_gen.NamedCol(out_pkey, None)])
|
1034 |
1032 |
|
1035 |
|
assert table_row_count(db, pkeys) == table_row_count(db, in_table)
|
|
1033 |
assert table_row_count(db, into) == table_row_count(db, in_table)
|
1036 |
1034 |
|
1037 |
|
return sql_gen.Col(out_pkey, pkeys)
|
|
1035 |
return sql_gen.Col(out_pkey, into)
|
1038 |
1036 |
|
1039 |
1037 |
##### Data cleanup
|
1040 |
1038 |
|
sql.py: put_table(): Renamed temp_prefix param to into and allow it to be a sql_gen.Table object. Use into directly as the pkeys table, and make its default value be `out_table.name+'_pkeys'`.