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("'!'"))