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 MockDb:
12
    def esc_value(self, value): return strings.ustr(value)
13
    
14
    def esc_name(self, name): return name
15
mockDb = MockDb()
16

    
17
class Code(strings.DebugPrintable):
18
    def to_str(self, db): raise NotImplemented()
19
    
20
    def __str__(self): return self.to_str(mockDb)
21

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

    
27
##### Literal values
28

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

    
34
def is_null(value): return isinstance(value, Literal) and value.value == None
35

    
36
##### Tables
37

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

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

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

    
74
##### Columns
75

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

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

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

    
114
##### Conditions
115

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

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

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

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

    
173
##### Joins
174

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

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

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

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