From 338ca8e48827840ad5db4ee4f677e4d3fcd315c9 Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Thu, 24 Apr 2014 15:20:57 -0700 Subject: Proof-of-concept implementation of supporting bindparam for offset and limit on a query. --- lib/sqlalchemy/dialects/firebird/base.py | 6 +++--- lib/sqlalchemy/dialects/postgresql/base.py | 5 +++-- lib/sqlalchemy/dialects/sqlite/base.py | 5 +++-- 3 files changed, 9 insertions(+), 7 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index 21db57b68..fdadb61c1 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -76,7 +76,7 @@ from sqlalchemy import exc, types as sqltypes, sql, util from sqlalchemy.sql import expression from sqlalchemy.engine import base, default, reflection from sqlalchemy.sql import compiler - +from sqlalchemy.sql.elements import _literal_as_binds from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, Integer) @@ -301,9 +301,9 @@ class FBCompiler(sql.compiler.SQLCompiler): result = "" if select._limit: - result += "FIRST %s " % self.process(sql.literal(select._limit)) + result += "FIRST %s " % self.process(_literal_as_binds(select._limit)) if select._offset: - result += "SKIP %s " % self.process(sql.literal(select._offset)) + result += "SKIP %s " % self.process(_literal_as_binds(select._offset)) if select._distinct: result += "DISTINCT " return result diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index f69a6e010..d63e3ed87 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -346,6 +346,7 @@ from ... import sql, schema, exc, util from ...engine import default, reflection from ...sql import compiler, expression, operators from ... import types as sqltypes +from sqlalchemy.sql.elements import _literal_as_binds try: from uuid import UUID as _python_UUID @@ -1144,11 +1145,11 @@ class PGCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" if select._limit is not None: - text += " \n LIMIT " + self.process(sql.literal(select._limit)) + text += " \n LIMIT " + self.process(_literal_as_binds(select._limit)) if select._offset is not None: if select._limit is None: text += " \n LIMIT ALL" - text += " OFFSET " + self.process(sql.literal(select._offset)) + text += " OFFSET " + self.process(_literal_as_binds(select._offset)) return text def format_from_hint_text(self, sqltext, table, hint, iscrud): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 90df9c192..1ae565232 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -185,6 +185,7 @@ from ... import types as sqltypes, schema as sa_schema from ... import util from ...engine import default, reflection from ...sql import compiler +from sqlalchemy.sql.elements import _literal_as_binds from ...types import (BLOB, BOOLEAN, CHAR, DATE, DECIMAL, FLOAT, INTEGER, REAL, NUMERIC, SMALLINT, TEXT, TIMESTAMP, VARCHAR) @@ -520,11 +521,11 @@ class SQLiteCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" if select._limit is not None: - text += "\n LIMIT " + self.process(sql.literal(select._limit)) + text += "\n LIMIT " + self.process(_literal_as_binds(select._limit)) if select._offset is not None: if select._limit is None: text += "\n LIMIT " + self.process(sql.literal(-1)) - text += " OFFSET " + self.process(sql.literal(select._offset)) + text += " OFFSET " + self.process(_literal_as_binds(select._offset)) else: text += " OFFSET " + self.process(sql.literal(0)) return text -- cgit v1.2.1 From e9b398f8a6ecd5b68142ab334a81683eff966e09 Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:22:50 -0700 Subject: Extract limit/offset to variables --- lib/sqlalchemy/dialects/sybase/base.py | 12 +++++++----- 1 file changed, 7 insertions(+), 5 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/sybase/base.py b/lib/sqlalchemy/dialects/sybase/base.py index 501270778..3e61b5ba6 100644 --- a/lib/sqlalchemy/dialects/sybase/base.py +++ b/lib/sqlalchemy/dialects/sybase/base.py @@ -325,18 +325,20 @@ class SybaseSQLCompiler(compiler.SQLCompiler): s = select._distinct and "DISTINCT " or "" # TODO: don't think Sybase supports # bind params for FIRST / TOP - if select._limit: + limit = select._limit + if limit: #if select._limit == 1: #s += "FIRST " #else: #s += "TOP %s " % (select._limit,) - s += "TOP %s " % (select._limit,) - if select._offset: - if not select._limit: + s += "TOP %s " % (limit,) + offset = select._offset + if offset: + if not limit: # FIXME: sybase doesn't allow an offset without a limit # so use a huge value for TOP here s += "TOP 1000000 " - s += "START AT %s " % (select._offset + 1,) + s += "START AT %s " % (offset + 1,) return s def get_from_hint_text(self, table, text): -- cgit v1.2.1 From 4af172b644d90f1bcab3de2bd0501a9cf50dc1d5 Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:42:12 -0700 Subject: Use _offset_clause and _limit_clause, which are always Visitable and usually a BindParameter, instead of _offset and _limit in GenerativeSelect. --- lib/sqlalchemy/dialects/firebird/base.py | 8 ++++---- lib/sqlalchemy/dialects/mssql/base.py | 7 ++++--- lib/sqlalchemy/dialects/mysql/base.py | 16 ++++++++-------- lib/sqlalchemy/dialects/postgresql/base.py | 10 +++++----- lib/sqlalchemy/dialects/sqlite/base.py | 10 +++++----- 5 files changed, 26 insertions(+), 25 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index fdadb61c1..431a16a6e 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -300,10 +300,10 @@ class FBCompiler(sql.compiler.SQLCompiler): """ result = "" - if select._limit: - result += "FIRST %s " % self.process(_literal_as_binds(select._limit)) - if select._offset: - result += "SKIP %s " % self.process(_literal_as_binds(select._offset)) + if select._limit_clause: + result += "FIRST %s " % self.process(select._limit_clause) + if select._offset_clause: + result += "SKIP %s " % self.process(select._offset_clause) if select._distinct: result += "DISTINCT " return result diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 9a8cddd98..6a13d1dca 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -741,15 +741,16 @@ class MSSQLCompiler(compiler.SQLCompiler): def get_select_precolumns(self, select): """ MS-SQL puts TOP, it's version of LIMIT here """ - if select._distinct or select._limit is not None: + limit = select._limit + if select._distinct or limit is not None: s = select._distinct and "DISTINCT " or "" # ODBC drivers and possibly others # don't support bind params in the SELECT clause on SQL Server. # so have to use literal here. - if select._limit is not None: + if limit is not None: if not select._offset: - s += "TOP %d " % select._limit + s += "TOP %d " % limit return s return compiler.SQLCompiler.get_select_precolumns(self, select) diff --git a/lib/sqlalchemy/dialects/mysql/base.py b/lib/sqlalchemy/dialects/mysql/base.py index ba6e7b625..4ad45f935 100644 --- a/lib/sqlalchemy/dialects/mysql/base.py +++ b/lib/sqlalchemy/dialects/mysql/base.py @@ -1568,15 +1568,15 @@ class MySQLCompiler(compiler.SQLCompiler): # The latter is more readable for offsets but we're stuck with the # former until we can refine dialects by server revision. - limit, offset = select._limit, select._offset + limit_clause, offset_clause = select._limit_clause, select._offset_clause - if (limit, offset) == (None, None): + if (limit_clause, offset_clause) == (None, None): return '' - elif offset is not None: + elif offset_clause is not None: # As suggested by the MySQL docs, need to apply an # artificial limit if one wasn't provided # http://dev.mysql.com/doc/refman/5.0/en/select.html - if limit is None: + if limit_clause is None: # hardwire the upper limit. Currently # needed by OurSQL with Python 3 # (https://bugs.launchpad.net/oursql/+bug/686232), @@ -1584,15 +1584,15 @@ class MySQLCompiler(compiler.SQLCompiler): # bound as part of MySQL's "syntax" for OFFSET with # no LIMIT return ' \n LIMIT %s, %s' % ( - self.process(sql.literal(offset)), + self.process(offset_clause), "18446744073709551615") else: return ' \n LIMIT %s, %s' % ( - self.process(sql.literal(offset)), - self.process(sql.literal(limit))) + self.process(offset_clause), + self.process(limit_clause)) else: # No offset provided, so just use the limit - return ' \n LIMIT %s' % (self.process(sql.literal(limit)),) + return ' \n LIMIT %s' % (self.process(limit_clause),) def update_limit_clause(self, update_stmt): limit = update_stmt.kwargs.get('%s_limit' % self.dialect.name, None) diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index d63e3ed87..4e5f9d703 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -1144,12 +1144,12 @@ class PGCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" - if select._limit is not None: - text += " \n LIMIT " + self.process(_literal_as_binds(select._limit)) - if select._offset is not None: - if select._limit is None: + if select._limit_clause is not None: + text += " \n LIMIT " + self.process(select._limit_clause) + if select._offset_clause is not None: + if select._limit_clause is None: text += " \n LIMIT ALL" - text += " OFFSET " + self.process(_literal_as_binds(select._offset)) + text += " OFFSET " + self.process(select._offset_clause) return text def format_from_hint_text(self, sqltext, table, hint, iscrud): diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 1ae565232..02582709b 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -520,12 +520,12 @@ class SQLiteCompiler(compiler.SQLCompiler): def limit_clause(self, select): text = "" - if select._limit is not None: - text += "\n LIMIT " + self.process(_literal_as_binds(select._limit)) - if select._offset is not None: - if select._limit is None: + if select._limit_clause is not None: + text += "\n LIMIT " + self.process(select._limit_clause) + if select._offset_clause is not None: + if select._limit_clause is None: text += "\n LIMIT " + self.process(sql.literal(-1)) - text += " OFFSET " + self.process(_literal_as_binds(select._offset)) + text += " OFFSET " + self.process(select._offset_clause) else: text += " OFFSET " + self.process(sql.literal(0)) return text -- cgit v1.2.1 From 262fef0b4ac2a8d14117662786d3af8e9a4424eb Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:45:38 -0700 Subject: Remove unused import --- lib/sqlalchemy/dialects/firebird/base.py | 1 - 1 file changed, 1 deletion(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/firebird/base.py b/lib/sqlalchemy/dialects/firebird/base.py index 431a16a6e..a3867457e 100644 --- a/lib/sqlalchemy/dialects/firebird/base.py +++ b/lib/sqlalchemy/dialects/firebird/base.py @@ -76,7 +76,6 @@ from sqlalchemy import exc, types as sqltypes, sql, util from sqlalchemy.sql import expression from sqlalchemy.engine import base, default, reflection from sqlalchemy.sql import compiler -from sqlalchemy.sql.elements import _literal_as_binds from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC, SMALLINT, TEXT, TIME, TIMESTAMP, Integer) -- cgit v1.2.1 From a218eaee2b426496f682922bb9558bc3e581e77e Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:46:30 -0700 Subject: Pull out offset/limit to a local variable to reduce the impact of the inefficient select._offset and select._limit operations. --- lib/sqlalchemy/dialects/oracle/base.py | 18 ++++++++++-------- 1 file changed, 10 insertions(+), 8 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index 8bacb885f..ace7d80ca 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -652,24 +652,26 @@ class OracleCompiler(compiler.SQLCompiler): # Wrap the middle select and add the hint limitselect = sql.select([c for c in select.c]) - if select._limit and self.dialect.optimize_limits: - limitselect = limitselect.prefix_with("/*+ FIRST_ROWS(%d) */" % select._limit) + limit = select._limit + if limit and self.dialect.optimize_limits: + limitselect = limitselect.prefix_with("/*+ FIRST_ROWS(%d) */" % limit) limitselect._oracle_visit = True limitselect._is_wrapper = True # If needed, add the limiting clause - if select._limit is not None: - max_row = select._limit - if select._offset is not None: - max_row += select._offset + offset = select._offset + if limit is not None: + max_row = limit + if offset is not None: + max_row += offset if not self.dialect.use_binds_for_limits: max_row = sql.literal_column("%d" % max_row) limitselect.append_whereclause( sql.literal_column("ROWNUM") <= max_row) # If needed, add the ora_rn, and wrap again with offset. - if select._offset is None: + if offset is None: limitselect._for_update_arg = select._for_update_arg select = limitselect else: @@ -683,7 +685,7 @@ class OracleCompiler(compiler.SQLCompiler): offsetselect._oracle_visit = True offsetselect._is_wrapper = True - offset_value = select._offset + offset_value = offset if not self.dialect.use_binds_for_limits: offset_value = sql.literal_column("%d" % offset_value) offsetselect.append_whereclause( -- cgit v1.2.1 From d2643b4e541957d84a48c15b6e40fce30ef1988f Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:48:11 -0700 Subject: Remove unused import --- lib/sqlalchemy/dialects/postgresql/base.py | 1 - 1 file changed, 1 deletion(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 4e5f9d703..e778aacf8 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -346,7 +346,6 @@ from ... import sql, schema, exc, util from ...engine import default, reflection from ...sql import compiler, expression, operators from ... import types as sqltypes -from sqlalchemy.sql.elements import _literal_as_binds try: from uuid import UUID as _python_UUID -- cgit v1.2.1 From 9f437136a36c51808a2396e2e08a743957020e60 Mon Sep 17 00:00:00 2001 From: Dobes Vandermeer Date: Fri, 25 Apr 2014 10:48:38 -0700 Subject: Remove unused import --- lib/sqlalchemy/dialects/sqlite/base.py | 1 - 1 file changed, 1 deletion(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/sqlite/base.py b/lib/sqlalchemy/dialects/sqlite/base.py index 02582709b..777c53109 100644 --- a/lib/sqlalchemy/dialects/sqlite/base.py +++ b/lib/sqlalchemy/dialects/sqlite/base.py @@ -185,7 +185,6 @@ from ... import types as sqltypes, schema as sa_schema from ... import util from ...engine import default, reflection from ...sql import compiler -from sqlalchemy.sql.elements import _literal_as_binds from ...types import (BLOB, BOOLEAN, CHAR, DATE, DECIMAL, FLOAT, INTEGER, REAL, NUMERIC, SMALLINT, TEXT, TIMESTAMP, VARCHAR) -- cgit v1.2.1 From 81959af6d37be503a13ce9c53317d443e14ae570 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 16 May 2014 15:33:39 -0400 Subject: - more tests, including backend tests - implement for SQL server, use window functions when simple limit/offset not available --- lib/sqlalchemy/dialects/mssql/base.py | 51 +++++++++++++++++++++++------------ 1 file changed, 34 insertions(+), 17 deletions(-) (limited to 'lib/sqlalchemy/dialects') diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 6a13d1dca..59cbb80bb 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -741,18 +741,21 @@ class MSSQLCompiler(compiler.SQLCompiler): def get_select_precolumns(self, select): """ MS-SQL puts TOP, it's version of LIMIT here """ - limit = select._limit - if select._distinct or limit is not None: - s = select._distinct and "DISTINCT " or "" + s = "" + if select._distinct: + s += "DISTINCT " + + if select._simple_int_limit and not select._offset: # ODBC drivers and possibly others # don't support bind params in the SELECT clause on SQL Server. # so have to use literal here. - if limit is not None: - if not select._offset: - s += "TOP %d " % limit + s += "TOP %d " % select._limit + + if s: return s - return compiler.SQLCompiler.get_select_precolumns(self, select) + else: + return compiler.SQLCompiler.get_select_precolumns(self, select) def get_from_hint_text(self, table, text): return text @@ -769,28 +772,42 @@ class MSSQLCompiler(compiler.SQLCompiler): so tries to wrap it in a subquery with ``row_number()`` criterion. """ - if select._offset and not getattr(select, '_mssql_visit', None): + if ( + ( + not select._simple_int_limit and + select._limit_clause is not None + ) or ( + select._offset_clause is not None and + not select._simple_int_offset or select._offset + ) + ) and not getattr(select, '_mssql_visit', None): + # to use ROW_NUMBER(), an ORDER BY is required. if not select._order_by_clause.clauses: raise exc.CompileError('MSSQL requires an order_by when ' - 'using an offset.') + 'using an OFFSET or a non-simple ' + 'LIMIT clause') - _offset = select._offset - _limit = select._limit _order_by_clauses = select._order_by_clause.clauses + limit_clause = select._limit_clause + offset_clause = select._offset_clause select = select._generate() select._mssql_visit = True select = select.column( - sql.func.ROW_NUMBER().over(order_by=_order_by_clauses) - .label("mssql_rn") - ).order_by(None).alias() + sql.func.ROW_NUMBER().over(order_by=_order_by_clauses) + .label("mssql_rn")).order_by(None).alias() mssql_rn = sql.column('mssql_rn') limitselect = sql.select([c for c in select.c if c.key != 'mssql_rn']) - limitselect.append_whereclause(mssql_rn > _offset) - if _limit is not None: - limitselect.append_whereclause(mssql_rn <= (_limit + _offset)) + if offset_clause is not None: + limitselect.append_whereclause(mssql_rn > offset_clause) + if limit_clause is not None: + limitselect.append_whereclause( + mssql_rn <= (limit_clause + offset_clause)) + else: + limitselect.append_whereclause( + mssql_rn <= (limit_clause)) return self.process(limitselect, iswrapper=True, **kwargs) else: return compiler.SQLCompiler.visit_select(self, select, **kwargs) -- cgit v1.2.1