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 as_Col(col, table=None):
58
    if col == None or isinstance(col, Code): return col
59
    else: return Col(col, table)
60

    
61
class NamedCode(Code):
62
    def __init__(self, name, code):
63
        if not isinstance(code, Code): code = Literal(code)
64
        
65
        self.name = name
66
        self.code = code
67
    
68
    def to_str(self, db):
69
        return self.code.to_str(db)+' AS '+sql.esc_name(db, self.name)
70

    
71
##### Parameterized SQL code objects
72

    
73
class ValueCond:
74
    def __init__(self, value):
75
        if not isinstance(value, Code): value = Literal(value)
76
        
77
        self.value = value
78
    
79
    def to_str(self, db, left_value):
80
        '''
81
        @param left_value The Code object that the condition is being applied on
82
        '''
83
        raise NotImplemented()
84
    
85
    def __str__(self): return str(self.__dict__)
86

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

    
119
# Tells as_ValueCond() to assume a non-ValueCond is a literal value
120
assume_literal = object()
121

    
122
def as_ValueCond(value, default_table=assume_literal):
123
    if not isinstance(value, ValueCond):
124
        if default_table is not assume_literal:
125
            value = as_Col(value, default_table)
126
        return CompareCond(value)
127
    else: return value
128

    
129
join_using = object() # tells Join to join the column with USING
130

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

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