def compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxint
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
def compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxint
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
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 compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxint
# SQLite does not support SELECT ... FOR UPDATE
# Can we just ignore it?
if select.for_update:
select.for_update = False
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
def compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxint
# SQLite does not support SELECT ... FOR UPDATE
# Can we just ignore it?
if select.for_update:
select.for_update = False
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
def compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
if sys.platform.startswith('darwin'):
# On OS X sqlite doesn't like maxint as LIMIT. See also
# https://lists.ubuntu.com/archives/storm/2013-June/001492.html
select.limit = sys.maxint - 1
else:
select.limit = sys.maxint
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
def compile_select_sqlite(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
if sys.maxsize > 2**32:
# On 64-bit platforms sqlite doesn't like maxint as LIMIT. See also
# https://lists.ubuntu.com/archives/storm/2013-June/001492.html
select.limit = sys.maxsize - 1
else:
select.limit = sys.maxsize
statement = compile_select(compile, select, state)
if state.context is SELECT:
# SQLite breaks with (SELECT ...) UNION (SELECT ...), so we
# do SELECT * FROM (SELECT ...) instead. This is important
# because SELECT ... UNION SELECT ... ORDER BY binds the ORDER BY
# to the UNION instead of SELECT.
return "SELECT * FROM (%s)" % statement
return statement
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 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 compile_select_mysql(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxint
return compile_select(compile, select, 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 compile_select_mysql(compile, select, state):
if select.offset is not Undef and select.limit is Undef:
select.limit = sys.maxsize
return compile_select(compile, select, state)