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 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 is_null(value): return isinstance(value, Literal) and value.value == None
36

    
37
##### Tables
38

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

    
55
def as_Table(table):
56
    if table == None or isinstance(table, Code): return table
57
    else: return Table(table)
58

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

    
75
##### Columns
76

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

    
96
def as_Col(col, table=None):
97
    assert col != None
98
    
99
    if isinstance(col, Code): return col
100
    else: return Col(col, table)
101

    
102
class NamedCol(Col):
103
    def __init__(self, name, code):
104
        Col.__init__(self, name)
105
        
106
        if not isinstance(code, Code): code = Literal(code)
107
        
108
        self.code = code
109
    
110
    def to_str(self, db):
111
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
112
    
113
    def to_Col(self): return Col(self.name)
114

    
115
##### Conditions
116

    
117
class ValueCond(objects.BasicObject):
118
    def __init__(self, value):
119
        if not isinstance(value, Code): value = Literal(value)
120
        if isinstance(value, NamedCol): value = value.code
121
        
122
        self.value = value
123
    
124
    def to_str(self, db, left_value):
125
        '''
126
        @param left_value The Code object that the condition is being applied on
127
        '''
128
        raise NotImplemented()
129
    
130
    def __str__(self): return self.to_str(mockDb, '<left_value>')
131

    
132
class CompareCond(ValueCond):
133
    def __init__(self, value, operator='='):
134
        '''
135
        @param operator By default, compares NULL values literally. Use '~=' or
136
            '~!=' to pass NULLs through.
137
        '''
138
        ValueCond.__init__(self, value)
139
        self.operator = operator
140
    
141
    def to_str(self, db, left_value):
142
        if not isinstance(left_value, Code): left_value = Col(left_value)
143
        
144
        right_value = self.value
145
        left = left_value.to_str(db)
146
        right = right_value.to_str(db)
147
        
148
        # Parse operator
149
        operator = self.operator
150
        passthru_null_ref = [False]
151
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
152
        neg_ref = [False]
153
        operator = strings.remove_prefix('!', operator, neg_ref)
154
        equals = operator.endswith('=')
155
        if equals and is_null(self.value): operator = 'IS'
156
        
157
        # Create str
158
        str_ = left+' '+operator+' '+right
159
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
160
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
161
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
162
        return str_
163

    
164
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
165
assume_literal = object()
166

    
167
def as_ValueCond(value, default_table=assume_literal):
168
    if not isinstance(value, ValueCond):
169
        if default_table is not assume_literal:
170
            value = as_Col(value, default_table)
171
        return CompareCond(value)
172
    else: return value
173

    
174
##### Joins
175

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

    
178
# Tells Join the left and right columns have the same name and are never NULL
179
join_same_not_null = object()
180

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

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