Project

General

Profile

« Previous | Next » 

Revision 2227

Added by Aaron Marcuse-Kubitza over 12 years ago

sql.py: mk_select() (and sql_gen.py): Fixed bugs where literal strings were treated as literal values when they should have been treated as column names. Take default_table param to determine default table to use if a column doesn't have an explicit table. put_table(): mk_main_select(): Pass in_tables0 as mk_select()'s default_table.

View differences:

lib/sql_gen.py
2 2 

  		




  3
  3
  
    
import sql


  		




  4
  4
  
    
import strings


  		




  
  5
  
    
import util


  		




  5
  6
  
    

  		




  6
  7
  
    
##### SQL code objects


  		




  7
  8
  
    

  		




  ......




  119
  120
  
    
    if table_is_esc: table = unescape_table(table)


  		




  120
  121
  
    
    return Col(col, as_Table(table))


  		




  121
  122
  
    

  		




  122
  
  
    
def value2sql_gen(value, default_table=None, table_is_esc=False):


  		




  
  123
  
    
def value2sql_gen(value, default_table=None, table_is_esc=False,


  		




  
  124
  
    
    assume_col=False):


  		




  123
  125
  
    
    '''Converts old-style (tuple-based) values to sql_gen-compatible values.


  		




  124
  126
  
    
    @param table_is_esc If False, assumes any table name is not escaped or that


  		




  125
  127
  
    
        re-escaping it will produce the same value.


  		




  126
  128
  
    
    '''


  		




  127
  129
  
    
    if isinstance(value, Code): return value # already in sql_gen form


  		




  128
  130
  
    
    


  		




  129
  
  
    
    if isinstance(value, tuple) and len(value) == 1: return Literal(value[0])


  		




  130
  
  
    
    else: return col2sql_gen(value, default_table, table_is_esc)


  		




  
  131
  
    
    is_tuple = isinstance(value, tuple)


  		




  
  132
  
    
    if is_tuple and len(value) == 1: return Literal(value[0])


  		




  
  133
  
    
    if is_tuple or (assume_col and util.is_str(value)):


  		




  
  134
  
    
        return col2sql_gen(value, default_table, table_is_esc)


  		




  
  135
  
    
    else: return Literal(value)


  		




  131
  136
  
    

  		




  132
  137
  
    
def cond2sql_gen(value, default_table=None, table_is_esc=False):


  		




  133
  138
  
    
    '''Converts old-style (tuple-based) conditions to sql_gen-compatible values.


  		












  

    
      lib/sql.py
    
  





  400
  400
  
    
distinct_on_all = object() # tells mk_select() to SELECT DISTINCT ON all columns


  		




  401
  401
  
    

  		




  402
  402
  
    
def mk_select(db, tables, fields=None, conds=None, distinct_on=None, limit=None,


  		




  403
  
  
    
    start=None, order_by=order_by_pkey, table_is_esc=False):


  		




  
  403
  
    
    start=None, order_by=order_by_pkey, table_is_esc=False, default_table=None):


  		




  404
  404
  
    
    '''


  		




  405
  405
  
    
    @param tables The single table to select from, or a list of tables to join


  		




  406
  406
  
    
        together: [table0, (table1, joins), ...]


  		




  ......




  433
  433
  
    
    


  		




  434
  434
  
    
    params = []


  		




  435
  435
  
    
    


  		




  436
  
  
    
    def parse_col(field, default_table=None):


  		




  
  436
  
    
    def parse_col(field):


  		




  437
  437
  
    
        '''Parses fields'''


  		




  438
  
  
    
        return sql_gen.value2sql_gen(field, default_table,


  		




  439
  
  
    
            table_is_esc).to_str(db)


  		




  
  438
  
    
        return sql_gen.value2sql_gen(field, default_table, table_is_esc,


  		




  
  439
  
    
            assume_col=True).to_str(db)


  		




  440
  440
  
    
    def cond(entry):


  		




  441
  441
  
    
        '''Parses conditions'''


  		




  442
  442
  
    
        left, right = entry


  		




  ......




  478
  478
  
    
                left_join_ref[0] = True


  		




  479
  479
  
    
                conds[(table, right_col)] = None # filter query by no match


  		




  480
  480
  
    
            


  		




  481
  
  
    
            return cond((right_col, left_col))


  		




  
  481
  
    
            return cond(((table, right_col),


  		




  
  482
  
    
                sql_gen.value2sql_gen(left_col, left_table,


  		




  
  483
  
    
                    table_is_esc=table_is_esc, assume_col=True)))


  		




  482
  484
  
    
        


  		




  483
  485
  
    
        # Create join condition and determine join type


  		




  484
  486
  
    
        if reduce(operator.and_, (v == join_using for v in joins.itervalues())):


  		




  ......




  497
  499
  
    
        query += ' WHERE '+(' AND '.join(map(cond, conds.iteritems())))


  		




  498
  500
  
    
        params += conds.values()


  		




  499
  501
  
    
        missing = False


  		




  500
  
  
    
    if order_by != None: query += ' ORDER BY '+parse_col(order_by, table0)


  		




  
  502
  
    
    if order_by != None:


  		




  
  503
  
    
        query += ' ORDER BY '+sql_gen.col2sql_gen(order_by, table0,


  		




  
  504
  
    
            table_is_esc).to_str(db)


  		




  501
  505
  
    
    if limit != None: query += ' LIMIT '+str(limit); missing = False


  		




  502
  506
  
    
    if start != None:


  		




  503
  507
  
    
        if start != 0: query += ' OFFSET '+str(start)


  		




  ......




  772
  776
  
    
    distinct_on = None


  		




  773
  777
  
    
    def mk_main_select(cols):


  		




  774
  778
  
    
        return mk_select(db, insert_joins, cols, conds, distinct_on,


  		




  775
  
  
    
            order_by=None, limit=limit, start=start, table_is_esc=table_is_esc)


  		




  
  779
  
    
            order_by=None, limit=limit, start=start, table_is_esc=table_is_esc,


  		




  
  780
  
    
            default_table=in_tables0)


  		




  776
  781
  
    
    


  		




  777
  782
  
    
    # Do inserts and selects


  		




  778
  783
  
    
    out_pkeys_ref = ['out_pkeys_'+temp_suffix]


  		












Also available in:  Unified diff