Project

General

Profile

1
# SQL code generation
2

    
3
import operator
4

    
5
import sql
6
import strings
7
import util
8

    
9
##### SQL code objects
10

    
11
class Code:
12
    def to_str(self, db): raise NotImplemented()
13
    
14
    def __str__(self): return str(self.__dict__)
15

    
16
class CustomCode(Code):
17
    def __init__(self, str_): self.str_ = str_
18
    
19
    def to_str(self, db): return self.str_
20

    
21
class Literal(Code):
22
    def __init__(self, value): self.value = value
23
    
24
    def to_str(self, db): return db.esc_value(self.value)
25

    
26
def is_null(value): return isinstance(value, Literal) and value.value == None
27

    
28
class Table(Code):
29
    def __init__(self, name, schema=None):
30
        '''
31
        @param schema str|None (for no schema)
32
        '''
33
        self.name = name
34
        self.schema = schema
35
    
36
    def to_str(self, db): return sql.qual_name(db, self.schema, self.name)
37

    
38
def as_Table(table):
39
    if table == None or isinstance(table, Code): return table
40
    elif isinstance(table, tuple):
41
        schema, table = table
42
        return Table(table, schema)
43
    else: return Table(table)
44

    
45
class Col(Code):
46
    def __init__(self, name, table=None):
47
        '''
48
        @param table Table|None (for no table)
49
        '''
50
        if util.is_str(table): table = Table(table)
51
        assert table == None or isinstance(table, Table)
52
        
53
        self.name = name
54
        self.table = table
55
    
56
    def to_str(self, db):
57
        str_ = ''
58
        if self.table != None: str_ += self.table.to_str(db)+'.'
59
        str_ += sql.esc_name(db, self.name)
60
        return str_
61

    
62
def as_Col(col, table=None):
63
    if col == None or isinstance(col, Code): return col
64
    else: return Col(col, table)
65

    
66
class NamedCode(Code):
67
    def __init__(self, name, code):
68
        if not isinstance(code, Code): code = Literal(code)
69
        
70
        self.name = name
71
        self.code = code
72
    
73
    def to_str(self, db):
74
        return self.code.to_str(db)+' AS '+sql.esc_name(db, self.name)
75

    
76
##### Parameterized SQL code objects
77

    
78
class ValueCond:
79
    def __init__(self, value):
80
        if not isinstance(value, Code): value = Literal(value)
81
        
82
        self.value = value
83
    
84
    def to_str(self, db, left_value):
85
        '''
86
        @param left_value The Code object that the condition is being applied on
87
        '''
88
        raise NotImplemented()
89
    
90
    def __str__(self): return str(self.__dict__)
91

    
92
class CompareCond(ValueCond):
93
    def __init__(self, value, operator='='):
94
        '''
95
        @param operator By default, compares NULL values literally. Use '~=' or
96
            '~!=' to pass NULLs through.
97
        '''
98
        ValueCond.__init__(self, value)
99
        self.operator = operator
100
    
101
    def to_str(self, db, left_value):
102
        if not isinstance(left_value, Code): left_value = Col(left_value)
103
        
104
        right_value = self.value
105
        left = left_value.to_str(db)
106
        right = right_value.to_str(db)
107
        
108
        # Parse operator
109
        operator = self.operator
110
        passthru_null_ref = [False]
111
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
112
        neg_ref = [False]
113
        operator = strings.remove_prefix('!', operator, neg_ref)
114
        equals = operator.endswith('=')
115
        if equals and is_null(self.value): operator = 'IS'
116
        
117
        # Create str
118
        str_ = left+' '+operator+' '+right
119
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
120
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
121
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
122
        return str_
123

    
124
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
125
assume_literal = object()
126

    
127
def as_ValueCond(value, default_table=assume_literal):
128
    if not isinstance(value, ValueCond):
129
        if default_table is not assume_literal:
130
            value = as_Col(value, default_table)
131
        return CompareCond(value)
132
    else: return value
133

    
134
join_using = object() # tells Join to join the column with USING
135

    
136
filter_out = object() # tells Join to filter out rows that match the join
137

    
138
class Join(Code):
139
    def __init__(self, table, mapping, type_=None):
140
        '''
141
        @param mapping dict(right_table_col=left_table_col, ...)
142
            * if left_table_col is join_using: left_table_col = right_table_col
143
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
144
            * filter_out: equivalent to 'LEFT' with the query filtered by
145
              `table_pkey IS NULL` (indicating no match)
146
        '''
147
        if util.is_str(table): table = Table(table)
148
        assert type_ == None or util.is_str(type_) or type_ is filter_out
149
        
150
        self.table = table
151
        self.mapping = mapping
152
        self.type_ = type_
153
    
154
    def to_str(self, db, left_table):
155
        def join(entry):
156
            '''Parses non-USING joins'''
157
            right_table_col, left_table_col = entry
158
            
159
            # Parse special values
160
            if left_table_col is join_using: left_table_col = right_table_col
161
            
162
            cond = as_ValueCond(right_table_col, self.table)
163
            return cond.to_str(db, as_Col(left_table_col, left_table))
164
        
165
        # Create join condition
166
        type_ = self.type_
167
        joins = self.mapping
168
        if type_ is not filter_out and reduce(operator.and_,
169
            (v is join_using for v in joins.itervalues())):
170
            # all cols w/ USING, so can use simpler USING syntax
171
            join_cond = 'USING ('+(', '.join(joins.iterkeys()))+')'
172
        else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
173
        
174
        # Create join
175
        if type_ is filter_out: type_ = 'LEFT'
176
        str_ = ''
177
        if type_ != None: str_ += type_+' '
178
        str_ += 'JOIN '+self.table.to_str(db)+' '+join_cond
179
        return str_
180

    
181
##### Old-style format support
182

    
183
def unescape_table(table):
184
    '''Currently only works with PostgreSQL.'''
185
    if table == None: return table
186
    
187
    assert table.count('.') <= 1
188
    parts = tuple((v.replace('"', '') for v in table.split('"."', 2)))
189
    if len(parts) == 1: parts, = parts
190
    return parts
191

    
192
def table2sql_gen(table, table_is_esc=False):
193
    '''Converts old-style (tuple-based) tables to sql_gen-compatible values.
194
    @param table_is_esc If False, assumes any table name is not escaped or that
195
        re-escaping it will produce the same value.
196
    '''
197
    if util.is_str(table) and table_is_esc: table = unescape_table(table)
198
    return as_Table(table)
199

    
200
def col2sql_gen(col, default_table=None, table_is_esc=False):
201
    '''Converts old-style (tuple-based) columns to sql_gen-compatible values.
202
    @param table_is_esc If False, assumes any table name is not escaped or that
203
        re-escaping it will produce the same value.
204
    '''
205
    if isinstance(col, Col): return col # already in sql_gen form
206
    
207
    table = default_table
208
    if isinstance(col, tuple): table, col = col
209
    return Col(col, table2sql_gen(table, table_is_esc))
210

    
211
def value2sql_gen(value, default_table=None, table_is_esc=False,
212
    assume_col=False):
213
    '''Converts old-style (tuple-based) values to sql_gen-compatible values.
214
    @param table_is_esc If False, assumes any table name is not escaped or that
215
        re-escaping it will produce the same value.
216
    '''
217
    if isinstance(value, Code): return value # already in sql_gen form
218
    
219
    is_tuple = isinstance(value, tuple)
220
    if is_tuple and len(value) == 1: return Literal(value[0])
221
    if is_tuple or (assume_col and util.is_str(value)):
222
        return col2sql_gen(value, default_table, table_is_esc)
223
    else: return Literal(value)
224

    
225
def cond2sql_gen(value, default_table=None, table_is_esc=False,
226
    assume_col=False):
227
    '''Converts old-style (tuple-based) conditions to sql_gen-compatible values.
228
    @param table_is_esc If False, assumes any table name is not escaped or that
229
        re-escaping it will produce the same value.
230
    '''
231
    if isinstance(value, ValueCond): return value # already in sql_gen form
232
    
233
    return as_ValueCond(value2sql_gen(value, default_table, table_is_esc,
234
        assume_col))
235

    
236
def join2sql_gen(value, table_is_esc=False):
237
    '''Converts old-style (tuple-based) joins to sql_gen-compatible values.
238
    @param table_is_esc If False, assumes any table name is not escaped or that
239
        re-escaping it will produce the same value.
240
    '''
241
    if isinstance(value, Join): return value # already in sql_gen form
242
    
243
    assert isinstance(value, tuple)
244
    table, joins = value
245
    return Join(table2sql_gen(table, table_is_esc), joins)
(23-23/34)