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 Code(strings.DebugPrintable):
12
    def to_str(self, db): raise NotImplemented()
13

    
14
class CustomCode(Code):
15
    def __init__(self, str_): self.str_ = str_
16
    
17
    def to_str(self, db): return self.str_
18

    
19
class Literal(Code):
20
    def __init__(self, value): self.value = value
21
    
22
    def to_str(self, db): return db.esc_value(self.value)
23

    
24
def is_null(value): return isinstance(value, Literal) and value.value == None
25

    
26
class Table(Code):
27
    def __init__(self, name, schema=None):
28
        '''
29
        @param schema str|None (for no schema)
30
        '''
31
        self.name = name
32
        self.schema = schema
33
    
34
    def to_str(self, db): return sql.qual_name(db, self.schema, self.name)
35

    
36
def as_Table(table):
37
    if table == None or isinstance(table, Code): return table
38
    else: return Table(table)
39

    
40
class Col(Code):
41
    def __init__(self, name, table=None):
42
        '''
43
        @param table Table|None (for no table)
44
        '''
45
        if util.is_str(table): table = Table(table)
46
        assert table == None or isinstance(table, Table)
47
        
48
        self.name = name
49
        self.table = table
50
    
51
    def to_str(self, db):
52
        str_ = ''
53
        if self.table != None: str_ += self.table.to_str(db)+'.'
54
        str_ += sql.esc_name(db, self.name)
55
        return str_
56
    
57
    def to_Col(self): return self
58

    
59
def as_Col(col, table=None):
60
    if col == None or isinstance(col, Code): return col
61
    else: return Col(col, table)
62

    
63
class NamedCode(Col):
64
    def __init__(self, name, code):
65
        Col.__init__(self, name)
66
        
67
        if not isinstance(code, Code): code = Literal(code)
68
        
69
        self.code = code
70
    
71
    def to_str(self, db):
72
        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
73
    
74
    def to_Col(self): return Col(self.name)
75

    
76
##### Parameterized SQL code objects
77

    
78
class ValueCond:
79
    def __init__(self, value):
80
        if not isinstance(value, Code): value = Literal(value)
81
        if isinstance(value, NamedCode): value = value.code
82
        
83
        self.value = value
84
    
85
    def to_str(self, db, left_value):
86
        '''
87
        @param left_value The Code object that the condition is being applied on
88
        '''
89
        raise NotImplemented()
90
    
91
    def __str__(self): return str(self.__dict__)
92

    
93
class CompareCond(ValueCond):
94
    def __init__(self, value, operator='='):
95
        '''
96
        @param operator By default, compares NULL values literally. Use '~=' or
97
            '~!=' to pass NULLs through.
98
        '''
99
        ValueCond.__init__(self, value)
100
        self.operator = operator
101
    
102
    def to_str(self, db, left_value):
103
        if not isinstance(left_value, Code): left_value = Col(left_value)
104
        
105
        right_value = self.value
106
        left = left_value.to_str(db)
107
        right = right_value.to_str(db)
108
        
109
        # Parse operator
110
        operator = self.operator
111
        passthru_null_ref = [False]
112
        operator = strings.remove_prefix('~', operator, passthru_null_ref)
113
        neg_ref = [False]
114
        operator = strings.remove_prefix('!', operator, neg_ref)
115
        equals = operator.endswith('=')
116
        if equals and is_null(self.value): operator = 'IS'
117
        
118
        # Create str
119
        str_ = left+' '+operator+' '+right
120
        if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
121
            str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
122
        if neg_ref[0]: str_ = 'NOT ('+str_+')'
123
        return str_
124

    
125
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
126
assume_literal = object()
127

    
128
def as_ValueCond(value, default_table=assume_literal):
129
    if not isinstance(value, ValueCond):
130
        if default_table is not assume_literal:
131
            value = as_Col(value, default_table)
132
        return CompareCond(value)
133
    else: return value
134

    
135
join_using = object() # tells Join to join the column with USING
136

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

    
139
class Join(Code):
140
    def __init__(self, table, mapping, type_=None):
141
        '''
142
        @param mapping dict(right_table_col=left_table_col, ...)
143
            * if left_table_col is join_using: left_table_col = right_table_col
144
        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
145
            * filter_out: equivalent to 'LEFT' with the query filtered by
146
              `table_pkey IS NULL` (indicating no match)
147
        '''
148
        if util.is_str(table): table = Table(table)
149
        assert type_ == None or util.is_str(type_) or type_ is filter_out
150
        
151
        self.table = table
152
        self.mapping = mapping
153
        self.type_ = type_
154
    
155
    def to_str(self, db, left_table):
156
        def join(entry):
157
            '''Parses non-USING joins'''
158
            right_table_col, left_table_col = entry
159
            # Note that right_table_col is on the left in the comparison
160
            
161
            # Parse special values
162
            if left_table_col is join_using: left_table_col = right_table_col
163
            
164
            cond = as_ValueCond(left_table_col, left_table)
165
            return cond.to_str(db, as_Col(right_table_col, self.table))
166
        
167
        # Create join condition
168
        type_ = self.type_
169
        joins = self.mapping
170
        if type_ is not filter_out and reduce(operator.and_,
171
            (v is join_using for v in joins.itervalues())):
172
            # all cols w/ USING, so can use simpler USING syntax
173
            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
174
            join_cond = 'USING ('+(', '.join(cols))+')'
175
        else: join_cond = 'ON '+(' AND '.join(map(join, joins.iteritems())))
176
        
177
        # Create join
178
        if type_ is filter_out: type_ = 'LEFT'
179
        str_ = ''
180
        if type_ != None: str_ += type_+' '
181
        str_ += 'JOIN '+self.table.to_str(db)+' '+join_cond
182
        return str_
(23-23/34)