Project

General

Profile

« Previous | Next » 

Revision 2489

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'`.

View differences:

lib/sql.py
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

  
lib/db_xml.py
218 218
            row[out_col] = sql_gen.NamedCol(out_col, value)
219 219
    
220 220
    # Insert node
221
    temp_prefix = out_table
222
    if xml_func.is_func_name(out_table): temp_prefix += '_'+row['value'].name
221
    into = None
222
    if xml_func.is_func_name(out_table): into = out_table+'_'+row['value'].name
223 223
    pkeys_loc = sql.put_table(db, out_table, in_tables, row, row_ins_ct_ref,
224
        temp_prefix)
224
        into)
225 225
    if commit: db.db.commit()
226 226
    
227 227
    # Insert children with fkeys to parent

Also available in: Unified diff