def compile_set_expr_oracle(compile, expr, state): if isinstance(expr, Minus): # Build new set expression without arguments (order_by, etc). new_expr = expr.__class__() new_expr.exprs = expr.exprs new_expr.all = expr.all if expr.order_by is not Undef: # Make sure that state.aliases isn't None, since we want them to # compile our order_by statement below. no_aliases = state.aliases is None if no_aliases: state.push("aliases", {}) aliases = {} for subexpr in expr.exprs: if isinstance(subexpr, Select): columns = subexpr.columns if not isinstance(columns, (tuple, list)): columns = [columns] else: columns = list(columns) for i, column in enumerate(columns): if column not in aliases: if isinstance(column, Column): aliases[column] = columns[i] = Alias(column) elif isinstance(column, Alias): aliases[column.expr] = column subexpr.columns = columns aliases.update(state.aliases) state.aliases = aliases aliases = None set_stmt = SQLRaw('(%s)' % compile(expr.exprs, state, join=expr.oper)) if expr.order_by is not Undef: # Build order_by statement, using aliases. state.push("context", COLUMN_NAME) order_by_stmt = SQLRaw(compile(expr.order_by, state)) state.pop() else: order_by_stmt = Undef # Build wrapping select statement. select = Select(SQLRaw("*"), tables=Alias(set_stmt), limit=expr.limit, offset=expr.offset, order_by=order_by_stmt) return compile_select(compile, select, state) return compile_set_expr(compile, expr, state)
def test_case_insensitive_like(self): like = Like(SQLRaw("description"), u"%hullah%", case_sensitive=False) expr = Select(SQLRaw("id"), like, tables=["like_case_insensitive_test"]) result = self.connection.execute(expr) self.assertEquals(result.get_all(), [(1, ), (2, )]) like = Like(SQLRaw("description"), u"%HULLAH%", case_sensitive=False) expr = Select(SQLRaw("id"), like, tables=["like_case_insensitive_test"]) result = self.connection.execute(expr) self.assertEquals(result.get_all(), [(1, ), (2, )])
def test_case_default_like(self): like = Like(SQLRaw("description"), u"%hullah%") expr = Select(SQLRaw("id"), like, tables=["like_case_insensitive_test"]) result = self.connection.execute(expr) assert result.get_all() == [(1, )] like = Like(SQLRaw("description"), u"%HULLAH%") expr = Select(SQLRaw("id"), like, tables=["like_case_insensitive_test"]) result = self.connection.execute(expr) assert result.get_all() == [(2, )]
def test_expressions_in_union_order_by(self): # The following statement breaks in postgres: # SELECT 1 AS id UNION SELECT 1 ORDER BY id+1; # With the error: # ORDER BY on a UNION/INTERSECT/EXCEPT result must # be on one of the result columns column = SQLRaw("1") Alias.auto_counter = 0 alias = Alias(column, "id") expr = Union(Select(alias), Select(column), order_by=alias + 1, limit=1, offset=1, all=True) state = State() statement = compile(expr, state) self.assertEquals( statement, 'SELECT * FROM ' '((SELECT 1 AS id) UNION ALL (SELECT 1)) AS "_1" ' 'ORDER BY id+? LIMIT 1 OFFSET 1') self.assertVariablesEqual(state.parameters, [Variable(1)]) result = self.connection.execute(expr) self.assertEquals(result.get_one(), (1, ))
def compile_insert_postgres(compile, insert, state): # PostgreSQL fails with INSERT INTO table VALUES (), so we transform # that to INSERT INTO table (id) VALUES (DEFAULT). if not insert.map and insert.primary_columns is not Undef: insert.map.update( dict.fromkeys(insert.primary_columns, SQLRaw("DEFAULT"))) return compile_insert(compile, insert, state)
def get_insert_identity(self, primary_key, primary_variables): equals = [] for column, variable in zip(primary_key, primary_variables): if not variable.is_defined(): variable = SQLRaw(self._raw_cursor.lastrowid) equals.append(Eq(column, variable)) return And(*equals)
def compile_set_expr_postgres(compile, expr, state): if expr.order_by is not Undef: # The following statement breaks in postgres: # SELECT 1 AS id UNION SELECT 1 ORDER BY id+1 # With the error: # ORDER BY on a UNION/INTERSECT/EXCEPT result must # be on one of the result columns # So we transform it into something close to: # SELECT * FROM (SELECT 1 AS id UNION SELECT 1) AS a ORDER BY id+1 # Build new set expression without arguments (order_by, etc). new_expr = expr.__class__() new_expr.exprs = expr.exprs new_expr.all = expr.all # Make sure that state.aliases isn't None, since we want them to # compile our order_by statement below. no_aliases = state.aliases is None if no_aliases: state.push("aliases", {}) # Build set expression, collecting aliases. set_stmt = SQLRaw("(%s)" % compile_set_expr(compile, new_expr, state)) # Build order_by statement, using aliases. state.push("context", COLUMN_NAME) order_by_stmt = SQLRaw(compile(expr.order_by, state)) state.pop() # Discard aliases, if they were not being collected previously. if no_aliases: state.pop() # Build wrapping select statement. select = Select(SQLRaw("*"), tables=Alias(set_stmt), limit=expr.limit, offset=expr.offset, order_by=order_by_stmt) return compile_select(compile, select, state) else: return compile_set_expr(compile, expr, state)
def clear(self, *args, **kwargs): store = Store.of(self._local) if store is None: raise NoStoreError("Can't perform operation without a store") where = self._relation1.get_where_for_remote(self._local) if args or kwargs: filter = get_where_for_args(args, kwargs, self._target_cls) join = self._relation2.get_where_for_join() table = get_cls_info(self._target_cls).table where &= Exists(Select(SQLRaw("*"), join & filter, tables=table)) store.find(self._link_cls, where).remove()
def test_expressions_in_union_in_union_order_by(self): column = SQLRaw("1") alias = Alias(column, "id") expr = Union(Select(alias), Select(column), order_by=alias + 1, limit=1, offset=1, all=True) expr = Union(expr, expr, order_by=alias + 1, all=True) result = self.connection.execute(expr) self.assertEquals(result.get_all(), [(1, ), (1, )])
def test_comparable_expr_subclass(self): prop1 = self.SubClass.prop1 prop2 = self.SubClass.prop2 prop3 = self.SubClass.prop3 expr = Select(SQLRaw("*"), (prop1 == "value1") & (prop2 == "value2") & (prop3 == "value3")) state = State() statement = compile(expr, state) self.assertEquals( statement, "SELECT * FROM mysubtable WHERE " "mysubtable.column1 = ? AND " "mysubtable.prop2 = ? AND " "mysubtable.column3 = ?") self.assertVariablesEqual(state.parameters, [ CustomVariable("value1"), CustomVariable("value2"), CustomVariable("value3") ])
def test_comparable_expr(cls, table): prop1 = cls.prop1 prop2 = cls.prop2 prop3 = cls.prop3 expr = Select(SQLRaw("*"), (prop1 == "value1") & (prop2 == "value2") & (prop3 == "value3")) state = State() statement = compile(expr, state) assert statement == ("SELECT * FROM {table} WHERE " "{table}.column1 = ? AND " "{table}.prop2 = ? AND " "{table}.column3 = ?").format(table=table) assert_variables_equal(state.parameters, [ CustomVariable("value1"), CustomVariable("value2"), CustomVariable("value3") ])
def test_execute_expression_empty_params(self): result = self.connection.execute(Select(SQLRaw("1"))) self.assertTrue(result.get_one(), (1, ))
def compile_insert_oracle(compile, insert, state): # shamelessly copied from PostgreSQL if not insert.map and insert.primary_columns is not Undef: insert.map.update( dict.fromkeys(insert.primary_columns, SQLRaw("DEFAULT"))) return compile_insert(compile, insert, state)
def compile_select_oracle(compile, select, state): limit = select.limit offset = select.offset # make sure limit is Undef'ed select.offset = select.limit = Undef if select.default_tables is Undef: select.default_tables = ['DUAL'] if select.order_by is not Undef: # copied from expr.py's compile_set_expr aliases = {} columns = select.columns if not isinstance(columns, (tuple, list)): columns = [columns] else: columns = list(columns) for i, column in enumerate(columns): if column not in aliases: if isinstance(column, Column): aliases[column] = columns[i] = Alias(column) elif isinstance(column, Alias): aliases[column.expr] = column select.columns = columns # /copied from expr.py's compile_set_expr stmt = SQLRaw("(%s)" % compile_select(compile, select, state)) stmt_alias = Alias(stmt) # In order to force the alias to generate its auto-name. stmt_alias.get_name(state) select = Select(SQLRaw('*'), tables=stmt_alias) if (limit is not Undef) and (offset is not Undef): rownum_alias = Alias(SQLRaw('ROWNUM')) # if we have an SQLRaw here that is because we are dealing # with a subquery if isinstance(select.columns, SQLRaw): select.columns = [ SQLRaw('"' + select.tables.name + '".*'), rownum_alias ] else: select.columns.append(rownum_alias) where_expr = Le(SQLRaw('ROWNUM'), limit + offset) if select.where is Undef: select.where = where_expr else: select.where = And(select.where, where_expr) stmt = SQLRaw("(%s)" % compile_select(compile, select, state)) stmt_alias = Alias(stmt) # In order to force the alias to generate its auto-name. stmt_alias.get_name(state) select = Select(SQLRaw('*'), tables=stmt_alias, where=Gt(rownum_alias, offset)) elif limit is not Undef: expr = Le(SQLRaw('ROWNUM'), limit) if select.where is Undef: select.where = expr else: select.where = And(select.where, expr) elif offset is not Undef: rownum_alias = Alias(SQLRaw('ROWNUM')) # if we have an SQLRaw here that is because we are dealing # with a subquery if isinstance(select.columns, SQLRaw): select.columns = [ SQLRaw('"' + select.tables.name + '".*'), rownum_alias ] else: select.columns.append(rownum_alias) stmt = SQLRaw("(%s)" % compile_select(compile, select, state)) stmt_alias = Alias(stmt) # In order to force the alias to generate its auto-name. stmt_alias.get_name(state) select = Select(SQLRaw('*'), tables=stmt_alias, where=Gt(rownum_alias, offset)) return compile_select(compile, select, state)
def _create_final_query(self, info_ta): return Select(columns=[SQLRaw('*')], tables=info_ta, offset=P.offset, limit=P.limit)
def get_insert_identity(self, primary_key, primary_variables): return SQLRaw("(OID=%d)" % self._raw_cursor.lastrowid)
def __init__(self, expr, string): string = string.replace("!", "!!") \ .replace("_", "!_") \ .replace("%", "!%") Like.__init__(self, expr, "%" + string + "%", SQLRaw("'!'"))