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: 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_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)