Project

General

Profile

1
# SQL code generation
2

    
3
import operator
4

    
5
import objects
6
import sql
7
import strings
8
import util
9

    
10
##### SQL code objects
11

    
12
class MockDb:
13
    def esc_value(self, value): return repr(value)
14
    
15
    def esc_name(self, name): return sql.esc_name_by_module(None, name)
16
mockDb = MockDb()
17

    
18
class Code(objects.BasicObject):
19
    def to_str(self, db): raise NotImplemented()
20
    
21
    def __str__(self): return self.to_str(mockDb)
22

    
23
class CustomCode(Code):
24
    def __init__(self, str_): self.str_ = str_
25
    
26
    def to_str(self, db): return self.str_
27

    
28
##### Literal values
29

    
30
class Literal(Code):
31
    def __init__(self, value): self.value = value
32
    
33
    def to_str(self, db): return db.esc_value(self.value)
34

    
35
def as_Value(value):
36
    if isinstance(value, Code): return value
37
    else: return Literal(value)
38

    
39
def is_null(value): return isinstance(value, Literal) and value.value == None
40

    
41
##### Tables
42

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

    
59
def as_Table(table):
60
    if table == None or isinstance(table, Code): return table
61
    else: return Table(table)
62

    
63
class NamedTable(Table):
64
    def __init__(self, name, code, cols=None):
65
        Table.__init__(self, name)
66
        
67
        if not isinstance(code, Code): code = Table(code)
68
        
69
        self.code = code
70
        self.cols = cols
71
    
72
    def to_str(self, db):
73
        str_ = self.code.to_str(db)+' AS '+Table.to_str(self, db)
74
        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
75
        return str_
76
    
77
    def to_Table(self): return Table(self.name)
78

    
79
##### Columns
80

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

    
100
def is_table_col(col): return col.table != None
101

    
102
def as_Col(col, table=None):
103
    assert col != None
104
    
105
    if isinstance(col, Code): return col
106
    else: return Col(col, table)
107

    
108
class NamedCol(Col):
109
    def __init__(self, name, code):
110
        Col.__init__(self, name)
111
        
112
        if not isinstance(code, Code): code = Literal(code)
113
        
114
        self.code = code
115
    
116
    def to_str(self, db):
117
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
118
    
119
    def to_Col(self): return Col(self.name)
120

    
121
##### Conditions
122

    
123
class ColValueCond(Code):
124
    def __init__(self, col, value):
125
        value = as_ValueCond(value)
126
        
127
        self.col = col
128
        self.value = value
129
    
130
    def to_str(self, db): return self.value.to_str(db, self.col)
131

    
132
##### Condition column comparisons
133

    
134
class ValueCond(objects.BasicObject):
135
    def __init__(self, value):
136
        if not isinstance(value, Code): value = Literal(value)
137
        if isinstance(value, NamedCol): value = value.code
138
        
139
        self.value = value
140
    
141
    def to_str(self, db, left_value):
142
        '''
143
        @param left_value The Code object that the condition is being applied on
144
        '''
145
        raise NotImplemented()
146
    
147
    def __str__(self): return self.to_str(mockDb, '<left_value>')
148

    
149
class CompareCond(ValueCond):
150
    def __init__(self, value, operator='='):
151
        '''
152
        @param operator By default, compares NULL values literally. Use '~=' or
153
            '~!=' to pass NULLs through.
154
        '''
155
        ValueCond.__init__(self, value)
156
        self.operator = operator
157
    
158
    def to_str(self, db, left_value):
159
        if not isinstance(left_value, Code): left_value = Col(left_value)
160
        
161
        right_value = self.value
162
        left = left_value.to_str(db)
163
        right = right_value.to_str(db)
164
        
165
        # Parse operator
166
        operator = self.operator
167
        passthru_null_ref = [False]
168
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
169
        neg_ref = [False]
170
        operator = strings.remove_prefix('!', operator, neg_ref)
171
        equals = operator.endswith('=')
172
        if equals and is_null(self.value): operator = 'IS'
173
        
174
        # Create str
175
        str_ = left+' '+operator+' '+right
176
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
177
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
178
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
179
        return str_
180

    
181
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
182
assume_literal = object()
183

    
184
def as_ValueCond(value, default_table=assume_literal):
185
    if not isinstance(value, ValueCond):
186
        if default_table is not assume_literal:
187
            value = as_Col(value, default_table)
188
        return CompareCond(value)
189
    else: return value
190

    
191
##### Joins
192

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

    
195
# Tells Join the left and right columns have the same name and are never NULL
196
join_same_not_null = object()
197

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

    
200
class Join(objects.BasicObject):
201
    def __init__(self, table, mapping, type_=None):
202
        '''
203
        @param mapping dict(right_table_col=left_table_col, ...)
204
            * if left_table_col is join_same: left_table_col = right_table_col
205
              * Note that right_table_col must be a string
206
            * if left_table_col is join_same_not_null:
207
              left_table_col = right_table_col and both have NOT NULL constraint
208
              * Note that right_table_col must be a string
209
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
210
            * filter_out: equivalent to 'LEFT' with the query filtered by
211
              `table_pkey IS NULL` (indicating no match)
212
        '''
213
        if util.is_str(table): table = Table(table)
214
        assert type_ == None or util.is_str(type_) or type_ is filter_out
215
        
216
        self.table = table
217
        self.mapping = mapping
218
        self.type_ = type_
219
    
220
    def to_str(self, db, left_table):
221
        # Switch order (left_table is on the right in the comparison)
222
        right_table = left_table
223
        left_table = self.table # note left_table is reassigned
224
        
225
        def join(entry):
226
            '''Parses non-USING joins'''
227
            right_table_col, left_table_col = entry
228
            
229
            # Switch order (right_table_col is on the left in the comparison)
230
            left = right_table_col
231
            right = left_table_col
232
            
233
            # Parse special values
234
            if right is join_same: right = left
235
            elif right is join_same_not_null:
236
                right = CompareCond(as_Col(left, right_table), '~=')
237
            
238
            right = as_ValueCond(right, right_table)
239
            return right.to_str(db, as_Col(left, left_table))
240
        
241
        # Create join condition
242
        type_ = self.type_
243
        joins = self.mapping
244
        if type_ is not filter_out and reduce(operator.and_,
245
            (v is join_same for v in joins.itervalues())):
246
            # all cols w/ USING, so can use simpler USING syntax
247
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
248
            join_cond = 'USING ('+(', '.join(cols))+')'
249
        else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
250
        
251
        # Create join
252
        if type_ is filter_out: type_ = 'LEFT'
253
        str_ = ''
254
        if type_ != None: str_ += type_+' '
255
        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
256
        return str_
257
    
258
    def __str__(self): return self.to_str(mockDb, '<left_table>')
(24-24/35)