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
##### SQL code objects
16

    
17
class MockDb:
18
    def esc_value(self, value): return repr(value)
19
    
20
    def esc_name(self, name): return esc_name(name)
21
mockDb = MockDb()
22

    
23
class Code(objects.BasicObject):
24
    def to_str(self, db): raise NotImplemented()
25
    
26
    def __str__(self): return self.to_str(mockDb)
27

    
28
class CustomCode(Code):
29
    def __init__(self, str_): self.str_ = str_
30
    
31
    def to_str(self, db): return self.str_
32

    
33
##### Literal values
34

    
35
class Literal(Code):
36
    def __init__(self, value): self.value = value
37
    
38
    def to_str(self, db): return db.esc_value(self.value)
39

    
40
def as_Value(value):
41
    if isinstance(value, Code): return value
42
    else: return Literal(value)
43

    
44
def is_null(value): return isinstance(value, Literal) and value.value == None
45

    
46
##### Tables
47

    
48
class Table(Code):
49
    def __init__(self, name, schema=None):
50
        '''
51
        @param schema str|None (for no schema)
52
        '''
53
        self.name = name
54
        self.schema = schema
55
    
56
    def to_str(self, db):
57
        str_ = ''
58
        if self.schema != None: str_ += db.esc_name(self.schema)+'.'
59
        str_ += db.esc_name(self.name)
60
        return str_
61
    
62
    def to_Table(self): return self
63

    
64
def as_Table(table):
65
    if table == None or isinstance(table, Code): return table
66
    else: return Table(table)
67

    
68
class NamedTable(Table):
69
    def __init__(self, name, code, cols=None):
70
        Table.__init__(self, name)
71
        
72
        if not isinstance(code, Code): code = Table(code)
73
        
74
        self.code = code
75
        self.cols = cols
76
    
77
    def to_str(self, db):
78
        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
79
        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
80
        return str_
81
    
82
    def to_Table(self): return Table(self.name)
83

    
84
##### Columns
85

    
86
class Col(Code):
87
    def __init__(self, name, table=None):
88
        '''
89
        @param table Table|None (for no table)
90
        '''
91
        if util.is_str(table): table = Table(table)
92
        assert table == None or isinstance(table, Table)
93
        
94
        self.name = name
95
        self.table = table
96
    
97
    def to_str(self, db):
98
        str_ = ''
99
        if self.table != None: str_ += self.table.to_str(db)+'.'
100
        str_ += db.esc_name(self.name)
101
        return str_
102
    
103
    def to_Col(self): return self
104

    
105
def is_table_col(col): return col.table != None
106

    
107
def as_Col(col, table=None):
108
    assert col != None
109
    
110
    if isinstance(col, Code): return col
111
    else: return Col(col, table)
112

    
113
def to_name_only_col(col, check_table=None):
114
    col = as_Col(col)
115
    
116
    if check_table != None:
117
        table = col.table
118
        assert table == None or table == check_table
119
    return Col(col.name)
120

    
121
class NamedCol(Col):
122
    def __init__(self, name, code):
123
        Col.__init__(self, name)
124
        
125
        if not isinstance(code, Code): code = Literal(code)
126
        
127
        self.code = code
128
    
129
    def to_str(self, db):
130
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
131
    
132
    def to_Col(self): return Col(self.name)
133

    
134
def remove_col_rename(col):
135
    if isinstance(col, NamedCol): col = col.code
136
    return col
137

    
138
##### Conditions
139

    
140
class ColValueCond(Code):
141
    def __init__(self, col, value):
142
        value = as_ValueCond(value)
143
        
144
        self.col = col
145
        self.value = value
146
    
147
    def to_str(self, db): return self.value.to_str(db, self.col)
148

    
149
##### Condition column comparisons
150

    
151
class ValueCond(objects.BasicObject):
152
    def __init__(self, value):
153
        if not isinstance(value, Code): value = Literal(value)
154
        value = remove_col_rename(value)
155
        
156
        self.value = value
157
    
158
    def to_str(self, db, left_value):
159
        '''
160
        @param left_value The Code object that the condition is being applied on
161
        '''
162
        raise NotImplemented()
163
    
164
    def __str__(self): return self.to_str(mockDb, '<left_value>')
165

    
166
class CompareCond(ValueCond):
167
    def __init__(self, value, operator='='):
168
        '''
169
        @param operator By default, compares NULL values literally. Use '~=' or
170
            '~!=' to pass NULLs through.
171
        '''
172
        ValueCond.__init__(self, value)
173
        self.operator = operator
174
    
175
    def to_str(self, db, left_value):
176
        if not isinstance(left_value, Code): left_value = Col(left_value)
177
        left_value = remove_col_rename(left_value)
178
        
179
        right_value = self.value
180
        left = left_value.to_str(db)
181
        right = right_value.to_str(db)
182
        
183
        # Parse operator
184
        operator = self.operator
185
        passthru_null_ref = [False]
186
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
187
        neg_ref = [False]
188
        operator = strings.remove_prefix('!', operator, neg_ref)
189
        equals = operator.endswith('=')
190
        if equals and is_null(self.value): operator = 'IS'
191
        
192
        # Create str
193
        str_ = left+' '+operator+' '+right
194
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
195
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
196
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
197
        return str_
198

    
199
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
200
assume_literal = object()
201

    
202
def as_ValueCond(value, default_table=assume_literal):
203
    if not isinstance(value, ValueCond):
204
        if default_table is not assume_literal:
205
            value = as_Col(value, default_table)
206
        return CompareCond(value)
207
    else: return value
208

    
209
##### Joins
210

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

    
213
# Tells Join the left and right columns have the same name and are never NULL
214
join_same_not_null = object()
215

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

    
218
class Join(objects.BasicObject):
219
    def __init__(self, table, mapping, type_=None):
220
        '''
221
        @param mapping dict(right_table_col=left_table_col, ...)
222
            * if left_table_col is join_same: left_table_col = right_table_col
223
              * Note that right_table_col must be a string
224
            * if left_table_col is join_same_not_null:
225
              left_table_col = right_table_col and both have NOT NULL constraint
226
              * Note that right_table_col must be a string
227
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
228
            * filter_out: equivalent to 'LEFT' with the query filtered by
229
              `table_pkey IS NULL` (indicating no match)
230
        '''
231
        if util.is_str(table): table = Table(table)
232
        assert type_ == None or util.is_str(type_) or type_ is filter_out
233
        
234
        self.table = table
235
        self.mapping = mapping
236
        self.type_ = type_
237
    
238
    def to_str(self, db, left_table):
239
        # Switch order (left_table is on the right in the comparison)
240
        right_table = left_table
241
        left_table = self.table # note left_table is reassigned
242
        
243
        def join(entry):
244
            '''Parses non-USING joins'''
245
            right_table_col, left_table_col = entry
246
            
247
            # Switch order (right_table_col is on the left in the comparison)
248
            left = right_table_col
249
            right = left_table_col
250
            
251
            # Parse special values
252
            if right is join_same: right = left
253
            elif right is join_same_not_null:
254
                right = CompareCond(as_Col(left, right_table), '~=')
255
            
256
            right = as_ValueCond(right, right_table)
257
            return '('+right.to_str(db, as_Col(left, left_table))+')'
258
        
259
        # Create join condition
260
        type_ = self.type_
261
        joins = self.mapping
262
        if type_ is not filter_out and reduce(operator.and_,
263
            (v is join_same_not_null for v in joins.itervalues())):
264
            # all cols w/ USING, so can use simpler USING syntax
265
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
266
            join_cond = 'USING ('+(', '.join(cols))+')'
267
        else: join_cond = 'ON\n'+('\nAND\n'.join(map(join, joins.iteritems())))
268
        
269
        # Create join
270
        if type_ is filter_out: type_ = 'LEFT'
271
        str_ = ''
272
        if type_ != None: str_ += type_+' '
273
        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
274
        return str_
275
    
276
    def __str__(self): return self.to_str(mockDb, '<left_table>')
277

    
278
##### Value exprs
279

    
280
row_count = CustomCode('count(*)')
(24-24/35)