diff options
Diffstat (limited to 'lib/sqlalchemy')
| -rw-r--r-- | lib/sqlalchemy/ansisql.py | 15 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/mysql.py | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/oracle.py | 22 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/postgres.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/databases/sqlite.py | 10 | ||||
| -rw-r--r-- | lib/sqlalchemy/mapping/properties.py | 30 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/util.py | 2 |
8 files changed, 95 insertions, 11 deletions
diff --git a/lib/sqlalchemy/ansisql.py b/lib/sqlalchemy/ansisql.py index 691106a71..79215dec7 100644 --- a/lib/sqlalchemy/ansisql.py +++ b/lib/sqlalchemy/ansisql.py @@ -269,7 +269,12 @@ class ANSICompiler(sql.Compiled): t = self.get_str(select.having) if t: text += " \nHAVING " + t - + + if select.limit is not None or select.offset is not None: + # TODO: ok, so this is a simple limit/offset thing. + # need to make this DB neutral for mysql, oracle + text += self.limit_clause(select) + if getattr(select, 'issubquery', False): self.strings[select] = "(" + text + ")" else: @@ -277,6 +282,14 @@ class ANSICompiler(sql.Compiled): self.froms[select] = "(" + text + ")" + def limit_clause(self, select): + if select.limit is not None: + return " \n LIMIT " + str(select.limit) + if select.offset is not None: + if select.limit is None: + return " \n LIMIT -1" + return " OFFSET " + str(select.offset) + def visit_table(self, table): self.froms[table] = table.fullname self.strings[table] = "" diff --git a/lib/sqlalchemy/databases/mysql.py b/lib/sqlalchemy/databases/mysql.py index 9e3f677d1..1eef6facb 100644 --- a/lib/sqlalchemy/databases/mysql.py +++ b/lib/sqlalchemy/databases/mysql.py @@ -175,7 +175,16 @@ class MySQLTableImpl(sql.TableImpl): rowid_column = property(lambda s: s._rowid_col()) class MySQLCompiler(ansisql.ANSICompiler): - pass + def limit_clause(self, select): + text = "" + if select.limit is not None: + text += " \n LIMIT " + str(select.limit) + if select.offset is not None: + if select.limit is None: + # striaght from the MySQL docs, I kid you not + text += " \n LIMIT 18446744073709551615" + text += " OFFSET " + str(select.offset) + return text class MySQLSchemaGenerator(ansisql.ANSISchemaGenerator): def get_column_specification(self, column, override_pk=False, first_pk=False): diff --git a/lib/sqlalchemy/databases/oracle.py b/lib/sqlalchemy/databases/oracle.py index 58d533b10..93670729d 100644 --- a/lib/sqlalchemy/databases/oracle.py +++ b/lib/sqlalchemy/databases/oracle.py @@ -195,6 +195,28 @@ class OracleCompiler(ansisql.ANSICompiler): self.bindparams[c.key] = None return ansisql.ANSICompiler.visit_insert(self, insert) + def visit_select(self, select): + """looks for LIMIT and OFFSET in a select statement, and if so tries to wrap it in a + subquery with rownum criterion.""" + if getattr(select, '_oracle_visit', False): + ansisql.ANSICompiler.visit_select(self, select) + return + if select.limit is not None or select.offset is not None: + select._oracle_visit = True + limitselect = select.select() + if select.limit is not None: + limitselect.append_whereclause("rownum<%d" % select.limit) + if select.offset is not None: + limitselect.append_whereclause("rownum>%d" % select.offset) + limitselect.accept_visitor(self) + self.strings[select] = self.strings[limitselect] + self.froms[select] = self.froms[limitselect] + else: + ansisql.ANSICompiler.visit_select(self, select) + + def limit_clause(self, select): + return "" + class OracleSchemaGenerator(ansisql.ANSISchemaGenerator): def get_column_specification(self, column, override_pk=False, **kwargs): colspec = column.name diff --git a/lib/sqlalchemy/databases/postgres.py b/lib/sqlalchemy/databases/postgres.py index 5a3d6388a..531e8af03 100644 --- a/lib/sqlalchemy/databases/postgres.py +++ b/lib/sqlalchemy/databases/postgres.py @@ -240,6 +240,16 @@ class PGCompiler(ansisql.ANSICompiler): if c.sequence is not None and not c.sequence.optional: self.bindparams[c.key] = None return ansisql.ANSICompiler.visit_insert(self, insert) + + def limit_clause(self, select): + text = "" + if select.limit is not None: + text += " \n LIMIT " + str(select.limit) + if select.offset is not None: + if select.limit is None: + text += " \n LIMIT ALL" + text += " OFFSET " + str(select.offset) + return text class PGSchemaGenerator(ansisql.ANSISchemaGenerator): def get_column_specification(self, column, override_pk=False, **kwargs): diff --git a/lib/sqlalchemy/databases/sqlite.py b/lib/sqlalchemy/databases/sqlite.py index 23a9d0483..fc85d92ac 100644 --- a/lib/sqlalchemy/databases/sqlite.py +++ b/lib/sqlalchemy/databases/sqlite.py @@ -188,7 +188,17 @@ class SQLiteCompiler(ansisql.ANSICompiler): def __init__(self, *args, **params): params.setdefault('paramstyle', 'named') ansisql.ANSICompiler.__init__(self, *args, **params) + def limit_clause(self, select): + text = "" + if select.limit is not None: + text += " \n LIMIT " + str(select.limit) + if select.offset is not None: + if select.limit is None: + text += " \n LIMIT -1" + text += " OFFSET " + str(select.offset) + return text + class SQLiteSchemaGenerator(ansisql.ANSISchemaGenerator): def get_column_specification(self, column, override_pk=False, **kwargs): colspec = column.name + " " + column.type.get_col_spec() diff --git a/lib/sqlalchemy/mapping/properties.py b/lib/sqlalchemy/mapping/properties.py index d1fd2315e..3429555d3 100644 --- a/lib/sqlalchemy/mapping/properties.py +++ b/lib/sqlalchemy/mapping/properties.py @@ -541,8 +541,8 @@ class EagerLoader(PropertyLoader): # if this eagermapper is to select using an "alias" to isolate it from other # eager mappers against the same table, we have to redefine our secondary - # or primary join condition to reference the aliased table. else - # we set up the target clause objects as what they are defined in the + # or primary join condition to reference the aliased table (and the order_by). + # else we set up the target clause objects as what they are defined in the # superclass. if self.selectalias is not None: self.eagertarget = self.target.alias(self.selectalias) @@ -554,11 +554,21 @@ class EagerLoader(PropertyLoader): else: self.eagerprimary = self.primaryjoin.copy_container() self.eagerprimary.accept_visitor(aliasizer) + if self.order_by is not None: + self.eager_order_by = [o.copy_container() for o in self.order_by] + for i in range(0, len(self.eager_order_by)): + if isinstance(self.eager_order_by[i], schema.Column): + self.eager_order_by[i] = self.eagertarget._get_col_by_original(self.eager_order_by[i]) + else: + self.eager_order_by[i].accept_visitor(aliasizer) + else: + self.eager_order_by = None else: self.eagertarget = self.target self.eagerprimary = self.primaryjoin self.eagersecondary = self.secondaryjoin - + self.eager_order_by = self.order_by + def setup(self, key, statement, recursion_stack = None, **options): """add a left outer join to the statement thats being constructed""" @@ -588,8 +598,8 @@ class EagerLoader(PropertyLoader): if self.order_by is None: statement.order_by(self.eagertarget.rowid_column) - if self.order_by is not None: - statement.order_by(*[self.eagertarget._get_col_by_original(c) for c in self.order_by]) + if self.eager_order_by is not None: + statement.order_by(*self.eager_order_by) statement.append_from(statement._outerjoin) statement.append_column(self.eagertarget) @@ -691,12 +701,18 @@ class Aliasizer(sql.ClauseVisitor): aliasname = table.name + "_" + hex(random.randint(0, 65535))[2:] return self.aliases.setdefault(table, sql.alias(table, aliasname)) + def visit_compound(self, compound): + for i in range(0, len(compound.clauses)): + if isinstance(compound.clauses[i], schema.Column) and self.tables.has_key(compound.clauses[i].table): + compound.clauses[i] = self.get_alias(compound.clauses[i].table)._get_col_by_original(compound.clauses[i]) + self.match = True + def visit_binary(self, binary): if isinstance(binary.left, schema.Column) and self.tables.has_key(binary.left.table): - binary.left = self.get_alias(binary.left.table).c[binary.left.name] + binary.left = self.get_alias(binary.left.table)._get_col_by_original(binary.left) self.match = True if isinstance(binary.right, schema.Column) and self.tables.has_key(binary.right.table): - binary.right = self.get_alias(binary.right.table).c[binary.right.name] + binary.right = self.get_alias(binary.right.table)._get_col_by_original(binary.right) self.match = True class BinaryVisitor(sql.ClauseVisitor): diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 71cbacdac..10e8f3e74 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -552,7 +552,7 @@ class CompoundClause(ClauseList): f += c._get_from_objects() return f def hash_key(self): - return string.join([c.hash_key() for c in self.clauses], self.operator) + return string.join([c.hash_key() for c in self.clauses], self.operator or " ") class Function(ClauseList, CompareMixin): """describes a SQL function. extends ClauseList to provide comparison operators.""" @@ -948,7 +948,7 @@ class CompoundSelect(Selectable, TailClauseMixin): class Select(Selectable, TailClauseMixin): """finally, represents a SELECT statement, with appendable clauses, as well as the ability to execute itself and return a result set.""" - def __init__(self, columns=None, whereclause = None, from_obj = [], order_by = None, group_by=None, having=None, use_labels = False, distinct=False, engine = None): + def __init__(self, columns=None, whereclause = None, from_obj = [], order_by = None, group_by=None, having=None, use_labels = False, distinct=False, engine = None, limit=None, offset=None): self._columns = util.OrderedProperties() self._froms = util.OrderedDict() self.use_labels = use_labels @@ -958,6 +958,8 @@ class Select(Selectable, TailClauseMixin): self.having = None self._engine = engine self.rowid_column = None + self.limit = limit + self.offset = offset # indicates if this select statement is a subquery inside another query self.issubquery = False diff --git a/lib/sqlalchemy/util.py b/lib/sqlalchemy/util.py index c247c86ed..d280368f6 100644 --- a/lib/sqlalchemy/util.py +++ b/lib/sqlalchemy/util.py @@ -23,6 +23,8 @@ def to_list(x): return None if not isinstance(x, list) and not isinstance(x, tuple): return [x] + else: + return x class OrderedProperties(object): """an object that maintains the order in which attributes are set upon it. |