Project

General

Profile

1
# SQL code generation
2

    
3
import operator
4

    
5
import objects
6
import strings
7
import util
8

    
9
##### Escaping
10

    
11
def esc_name(name, quote='"'):
12
    return quote + name.replace(quote, quote+quote) + quote
13
        # doubling an embedded quote escapes it in both PostgreSQL and MySQL
14

    
15
def clean_name(name): return name.replace('"', '').replace('`', '')
16

    
17
##### SQL code objects
18

    
19
class MockDb:
20
    def esc_value(self, value): return strings.repr_no_u(value)
21
    
22
    def esc_name(self, name): return esc_name(name)
23
mockDb = MockDb()
24

    
25
class BasicObject(objects.BasicObject):
26
    def __init__(self, value): self.value = value
27
    
28
    def __str__(self): return clean_name(strings.repr_no_u(self))
29

    
30
class Code(BasicObject):
31
    def to_str(self, db): raise NotImplemented()
32
    
33
    def __repr__(self): return self.to_str(mockDb)
34

    
35
class CustomCode(Code):
36
    def __init__(self, str_): self.str_ = str_
37
    
38
    def to_str(self, db): return self.str_
39

    
40
##### Literal values
41

    
42
class Literal(Code):
43
    def __init__(self, value): self.value = value
44
    
45
    def to_str(self, db): return db.esc_value(self.value)
46

    
47
def as_Value(value):
48
    if isinstance(value, Code): return value
49
    else: return Literal(value)
50

    
51
def is_null(value): return isinstance(value, Literal) and value.value == None
52

    
53
##### Tables
54

    
55
class Table(Code):
56
    def __init__(self, name, schema=None):
57
        '''
58
        @param schema str|None (for no schema)
59
        '''
60
        self.name = name
61
        self.schema = schema
62
    
63
    def to_str(self, db):
64
        str_ = ''
65
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
66
        str_ += db.esc_name(self.name)
67
        return str_
68
    
69
    def to_Table(self): return self
70

    
71
def as_Table(table):
72
    if table == None or isinstance(table, Code): return table
73
    else: return Table(table)
74

    
75
class NamedTable(Table):
76
    def __init__(self, name, code, cols=None):
77
        Table.__init__(self, name)
78
        
79
        if not isinstance(code, Code): code = Table(code)
80
        
81
        self.code = code
82
        self.cols = cols
83
    
84
    def to_str(self, db):
85
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
86
        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
87
        return str_
88
    
89
    def to_Table(self): return Table(self.name)
90

    
91
##### Columns
92

    
93
class Col(Code):
94
    def __init__(self, name, table=None):
95
        '''
96
        @param table Table|None (for no table)
97
        '''
98
        if util.is_str(table): table = Table(table)
99
        assert table == None or isinstance(table, Table)
100
        
101
        self.name = name
102
        self.table = table
103
    
104
    def to_str(self, db):
105
        str_ = ''
106
        if self.table != None: str_ += self.table.to_str(db)+'.'
107
        str_ += db.esc_name(self.name)
108
        return str_
109
    
110
    def to_Col(self): return self
111

    
112
def is_table_col(col): return col.table != None
113

    
114
def as_Col(col, table=None):
115
    assert col != None
116
    
117
    if isinstance(col, Code): return col
118
    else: return Col(col, table)
119

    
120
def to_name_only_col(col, check_table=None):
121
    col = as_Col(col)
122
    
123
    if check_table != None:
124
        table = col.table
125
        assert table == None or table == check_table
126
    return Col(col.name)
127

    
128
class NamedCol(Col):
129
    def __init__(self, name, code):
130
        Col.__init__(self, name)
131
        
132
        if not isinstance(code, Code): code = Literal(code)
133
        
134
        self.code = code
135
    
136
    def to_str(self, db):
137
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
138
    
139
    def to_Col(self): return Col(self.name)
140

    
141
def remove_col_rename(col):
142
    if isinstance(col, NamedCol): col = col.code
143
    return col
144

    
145
##### Functions
146

    
147
class Function(Table): pass
148

    
149
class FunctionCall(Code):
150
    def __init__(self, function, *args):
151
        '''
152
        @param args [Code...] The function's arguments
153
        '''
154
        if not isinstance(function, Code): function = Function(function)
155
        
156
        self.function = function
157
        self.args = args
158
    
159
    def to_str(self, db):
160
        args_str = ', '.join((v.to_str(db) for v in self.args))
161
        return self.function.to_str(db)+'('+args_str+')'
162
    
163
    def __repr__(self): return self.to_str(mockDb, '<args>')
164

    
165
##### Conditions
166

    
167
class ColValueCond(Code):
168
    def __init__(self, col, value):
169
        value = as_ValueCond(value)
170
        
171
        self.col = col
172
        self.value = value
173
    
174
    def to_str(self, db): return self.value.to_str(db, self.col)
175

    
176
##### Condition column comparisons
177

    
178
class ValueCond(BasicObject):
179
    def __init__(self, value):
180
        if not isinstance(value, Code): value = Literal(value)
181
        value = remove_col_rename(value)
182
        
183
        self.value = value
184
    
185
    def to_str(self, db, left_value):
186
        '''
187
        @param left_value The Code object that the condition is being applied on
188
        '''
189
        raise NotImplemented()
190
    
191
    def __repr__(self): return self.to_str(mockDb, '<left_value>')
192

    
193
class CompareCond(ValueCond):
194
    def __init__(self, value, operator='='):
195
        '''
196
        @param operator By default, compares NULL values literally. Use '~=' or
197
            '~!=' to pass NULLs through.
198
        '''
199
        ValueCond.__init__(self, value)
200
        self.operator = operator
201
    
202
    def to_str(self, db, left_value):
203
        if not isinstance(left_value, Code): left_value = Col(left_value)
204
        left_value = remove_col_rename(left_value)
205
        
206
        right_value = self.value
207
        left = left_value.to_str(db)
208
        right = right_value.to_str(db)
209
        
210
        # Parse operator
211
        operator = self.operator
212
        passthru_null_ref = [False]
213
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
214
        neg_ref = [False]
215
        operator = strings.remove_prefix('!', operator, neg_ref)
216
        equals = operator.endswith('=')
217
        if equals and is_null(self.value): operator = 'IS'
218
        
219
        # Create str
220
        str_ = left+' '+operator+' '+right
221
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
222
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
223
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
224
        return str_
225

    
226
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
227
assume_literal = object()
228

    
229
def as_ValueCond(value, default_table=assume_literal):
230
    if not isinstance(value, ValueCond):
231
        if default_table is not assume_literal:
232
            value = as_Col(value, default_table)
233
        return CompareCond(value)
234
    else: return value
235

    
236
##### Joins
237

    
238
join_same = object() # tells Join the left and right columns have the same name
239

    
240
# Tells Join the left and right columns have the same name and are never NULL
241
join_same_not_null = object()
242

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

    
245
class Join(BasicObject):
246
    def __init__(self, table, mapping, type_=None):
247
        '''
248
        @param mapping dict(right_table_col=left_table_col, ...)
249
            * if left_table_col is join_same: left_table_col = right_table_col
250
              * Note that right_table_col must be a string
251
            * if left_table_col is join_same_not_null:
252
              left_table_col = right_table_col and both have NOT NULL constraint
253
              * Note that right_table_col must be a string
254
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
255
            * filter_out: equivalent to 'LEFT' with the query filtered by
256
              `table_pkey IS NULL` (indicating no match)
257
        '''
258
        if util.is_str(table): table = Table(table)
259
        assert type_ == None or util.is_str(type_) or type_ is filter_out
260
        
261
        self.table = table
262
        self.mapping = mapping
263
        self.type_ = type_
264
    
265
    def to_str(self, db, left_table):
266
        # Switch order (left_table is on the right in the comparison)
267
        right_table = left_table
268
        left_table = self.table # note left_table is reassigned
269
        
270
        def join(entry):
271
            '''Parses non-USING joins'''
272
            right_table_col, left_table_col = entry
273
            
274
            # Switch order (right_table_col is on the left in the comparison)
275
            left = right_table_col
276
            right = left_table_col
277
            
278
            # Parse special values
279
            if right is join_same: right = left
280
            elif right is join_same_not_null:
281
                right = CompareCond(as_Col(left, right_table), '~=')
282
            
283
            right = as_ValueCond(right, right_table)
284
            return '('+right.to_str(db, as_Col(left, left_table))+')'
285
        
286
        # Create join condition
287
        type_ = self.type_
288
        joins = self.mapping
289
        if type_ is not filter_out and reduce(operator.and_,
290
            (v is join_same_not_null for v in joins.itervalues())):
291
            # all cols w/ USING, so can use simpler USING syntax
292
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
293
            join_cond = 'USING ('+(', '.join(cols))+')'
294
        else: join_cond = 'ON\n'+('\nAND\n'.join(map(join, joins.iteritems())))
295
        
296
        # Create join
297
        if type_ is filter_out: type_ = 'LEFT'
298
        str_ = ''
299
        if type_ != None: str_ += type_+' '
300
        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
301
        return str_
302
    
303
    def __repr__(self): return self.to_str(mockDb, '<left_table>')
304

    
305
##### Value exprs
306

    
307
row_count = CustomCode('count(*)')
(25-25/36)