/lib/sql_gen.py - Annotate - BIEN 3 - NCEAS Projects

root/lib/sql_gen.py @ 2521

-            aaronmk
+# SQL code generation
-            aaronmk
+import operator
-            aaronmk
+import objects
-            aaronmk
+import strings
-            aaronmk
+import util
             aaronmk
-            aaronmk
+##### Escaping
 def esc_name(name, quote='"'):
     return quote + name.replace(quote, quote+quote) + quote
         # doubling an embedded quote escapes it in both PostgreSQL and MySQL
-            aaronmk
+def clean_name(name): return name.replace('"', '').replace('`', '')
-            aaronmk
+##### SQL code objects
-            aaronmk
+class MockDb:
-            aaronmk
+    def esc_value(self, value): return strings.repr_no_u(value)
             aaronmk
-            aaronmk
+    def esc_name(self, name): return esc_name(name)
-            aaronmk
+mockDb = MockDb()
-            aaronmk
+class BasicObject(objects.BasicObject):
     def __init__(self, value): self.value = value
     def __str__(self): return clean_name(strings.repr_no_u(self))
 class Code(BasicObject):
-            aaronmk
+    def to_str(self, db): raise NotImplemented()
             aaronmk
-            aaronmk
+    def __repr__(self): return self.to_str(mockDb)
             aaronmk
-            aaronmk
+class CustomCode(Code):
-            aaronmk
+    def __init__(self, str_): self.str_ = str_
     def to_str(self, db): return self.str_
-            aaronmk
+##### Literal values
-            aaronmk
+class Literal(Code):
-            aaronmk
+    def __init__(self, value): self.value = value
             aaronmk
     def to_str(self, db): return db.esc_value(self.value)
             aaronmk
-            aaronmk
+def as_Value(value):
     if isinstance(value, Code): return value
     else: return Literal(value)
-            aaronmk
+def is_null(value): return isinstance(value, Literal) and value.value == None
-            aaronmk
+##### Tables
-            aaronmk
+class Table(Code):
     def __init__(self, name, schema=None):
         '''
         @param schema str|None (for no schema)
         '''
         self.name = name
         self.schema = schema
-            aaronmk
+    def to_str(self, db):
         str_ = ''
         if self.schema != None: str_ += db.esc_name(self.schema)+'.'
         str_ += db.esc_name(self.name)
         return str_
             aaronmk
     def to_Table(self): return self
             aaronmk
-            aaronmk
+def as_Table(table):
-            aaronmk
+    if table == None or isinstance(table, Code): return table
-            aaronmk
+    else: return Table(table)
-            aaronmk
+class NamedTable(Table):
     def __init__(self, name, code, cols=None):
         Table.__init__(self, name)
         if not isinstance(code, Code): code = Table(code)
         self.code = code
         self.cols = cols
     def to_str(self, db):
-            aaronmk
+        str_ = self.code.to_str(db)+'\nAS '+Table.to_str(self, db)
-            aaronmk
+        if self.cols != None: str_ += ' ('+(', '.join(self.cols))+')'
         return str_
     def to_Table(self): return Table(self.name)
-            aaronmk
+##### Columns
-            aaronmk
+class Col(Code):
     def __init__(self, name, table=None):
         '''
         @param table Table|None (for no table)
         '''
-            aaronmk
+        if util.is_str(table): table = Table(table)
-            aaronmk
+        assert table == None or isinstance(table, Table)
         self.name = name
         self.table = table
     def to_str(self, db):
         str_ = ''
         if self.table != None: str_ += self.table.to_str(db)+'.'
-            aaronmk
+        str_ += db.esc_name(self.name)
-            aaronmk
+        return str_
             aaronmk
     def to_Col(self): return self
             aaronmk
-            aaronmk
+def is_table_col(col): return col.table != None
-            aaronmk
+def as_Col(col, table=None):
-            aaronmk
+    assert col != None
     if isinstance(col, Code): return col
-            aaronmk
+    else: return Col(col, table)
-            aaronmk
+def to_name_only_col(col, check_table=None):
     col = as_Col(col)
     if check_table != None:
         table = col.table
         assert table == None or table == check_table
     return Col(col.name)
-            aaronmk
+class NamedCol(Col):
-            aaronmk
+    def __init__(self, name, code):
-            aaronmk
+        Col.__init__(self, name)
-            aaronmk
+        if not isinstance(code, Code): code = Literal(code)
         self.code = code
     def to_str(self, db):
-            aaronmk
+        return self.code.to_str(db)+' AS '+Col.to_str(self, db)
             aaronmk
     def to_Col(self): return Col(self.name)
             aaronmk
-            aaronmk
+def remove_col_rename(col):
     if isinstance(col, NamedCol): col = col.code
     return col
-            aaronmk
+##### Conditions
             aaronmk
-            aaronmk
+class ColValueCond(Code):
     def __init__(self, col, value):
         value = as_ValueCond(value)
         self.col = col
         self.value = value
     def to_str(self, db): return self.value.to_str(db, self.col)
 ##### Condition column comparisons
-            aaronmk
+class ValueCond(BasicObject):
-            aaronmk
+    def __init__(self, value):
-            aaronmk
+        if not isinstance(value, Code): value = Literal(value)
-            aaronmk
+        value = remove_col_rename(value)
             aaronmk
         self.value = value
             aaronmk
-            aaronmk
+    def to_str(self, db, left_value):
-            aaronmk
+        '''
-            aaronmk
+        @param left_value The Code object that the condition is being applied on
-            aaronmk
+        '''
         raise NotImplemented()
             aaronmk
-            aaronmk
+    def __repr__(self): return self.to_str(mockDb, '<left_value>')
             aaronmk
 class CompareCond(ValueCond):
     def __init__(self, value, operator='='):
-            aaronmk
+        '''
         @param operator By default, compares NULL values literally. Use '~=' or
             '~!=' to pass NULLs through.
         '''
-            aaronmk
+        ValueCond.__init__(self, value)
         self.operator = operator
-            aaronmk
+    def to_str(self, db, left_value):
         if not isinstance(left_value, Code): left_value = Col(left_value)
-            aaronmk
+        left_value = remove_col_rename(left_value)
             aaronmk
-            aaronmk
+        right_value = self.value
         left = left_value.to_str(db)
         right = right_value.to_str(db)
         # Parse operator
-            aaronmk
+        operator = self.operator
-            aaronmk
+        passthru_null_ref = [False]
         operator = strings.remove_prefix('~', operator, passthru_null_ref)
         neg_ref = [False]
         operator = strings.remove_prefix('!', operator, neg_ref)
         equals = operator.endswith('=')
         if equals and is_null(self.value): operator = 'IS'
         # Create str
         str_ = left+' '+operator+' '+right
         if equals and not passthru_null_ref[0] and isinstance(right_value, Col):
             str_ += ' OR ('+left+' IS NULL AND '+right+' IS NULL)'
         if neg_ref[0]: str_ = 'NOT ('+str_+')'
         return str_
             aaronmk
-            aaronmk
+# Tells as_ValueCond() to assume a non-ValueCond is a literal value
 assume_literal = object()
 def as_ValueCond(value, default_table=assume_literal):
     if not isinstance(value, ValueCond):
         if default_table is not assume_literal:
             value = as_Col(value, default_table)
         return CompareCond(value)
-            aaronmk
+    else: return value
             aaronmk
-            aaronmk
+##### Joins
-            aaronmk
+join_same = object() # tells Join the left and right columns have the same name
             aaronmk
-            aaronmk
+# Tells Join the left and right columns have the same name and are never NULL
 join_same_not_null = object()
-            aaronmk
+filter_out = object() # tells Join to filter out rows that match the join
-            aaronmk
+class Join(BasicObject):
-            aaronmk
+    def __init__(self, table, mapping, type_=None):
         '''
         @param mapping dict(right_table_col=left_table_col, ...)
-            aaronmk
+            * if left_table_col is join_same: left_table_col = right_table_col
-            aaronmk
+              * Note that right_table_col must be a string
             * if left_table_col is join_same_not_null:
               left_table_col = right_table_col and both have NOT NULL constraint
               * Note that right_table_col must be a string
-            aaronmk
+        @param type_ None (for plain join)|str (e.g. 'LEFT')|filter_out
             * filter_out: equivalent to 'LEFT' with the query filtered by
               `table_pkey IS NULL` (indicating no match)
         '''
         if util.is_str(table): table = Table(table)
         assert type_ == None or util.is_str(type_) or type_ is filter_out
         self.table = table
         self.mapping = mapping
         self.type_ = type_
     def to_str(self, db, left_table):
-            aaronmk
+        # Switch order (left_table is on the right in the comparison)
         right_table = left_table
         left_table = self.table # note left_table is reassigned
-            aaronmk
+        def join(entry):
             '''Parses non-USING joins'''
             right_table_col, left_table_col = entry
-            aaronmk
+            # Switch order (right_table_col is on the left in the comparison)
             left = right_table_col
             right = left_table_col
-            aaronmk
+            # Parse special values
-            aaronmk
+            if right is join_same: right = left
             elif right is join_same_not_null:
                 right = CompareCond(as_Col(left, right_table), '~=')
             aaronmk
-            aaronmk
+            right = as_ValueCond(right, right_table)
-            aaronmk
+            return '('+right.to_str(db, as_Col(left, left_table))+')'
             aaronmk
-            aaronmk
+        # Create join condition
         type_ = self.type_
-            aaronmk
+        joins = self.mapping
-            aaronmk
+        if type_ is not filter_out and reduce(operator.and_,
-            aaronmk
+            (v is join_same_not_null for v in joins.itervalues())):
-            aaronmk
+            # all cols w/ USING, so can use simpler USING syntax
-            aaronmk
+            cols = (as_Col(v).to_str(db) for v in joins.iterkeys())
             join_cond = 'USING ('+(', '.join(cols))+')'
-            aaronmk
+        else: join_cond = 'ON\n'+('\nAND\n'.join(map(join, joins.iteritems())))
             aaronmk
         # Create join
         if type_ is filter_out: type_ = 'LEFT'
-            aaronmk
+        str_ = ''
         if type_ != None: str_ += type_+' '
-            aaronmk
+        str_ += 'JOIN '+left_table.to_str(db)+' '+join_cond
-            aaronmk
+        return str_
             aaronmk
-            aaronmk
+    def __repr__(self): return self.to_str(mockDb, '<left_table>')
             aaronmk
 ##### Value exprs
 row_count = CustomCode('count(*)')

Project

General

Profile