diff options
author | Boulder Sprinters <boulder-sprinters@djangoproject.com> | 2006-11-05 18:01:25 +0000 |
---|---|---|
committer | Boulder Sprinters <boulder-sprinters@djangoproject.com> | 2006-11-05 18:01:25 +0000 |
commit | 9acae362cf03ebb951a3b8ad3fd70c19b1990836 (patch) | |
tree | 414790902712e930ceeb5b6ca43a91f776ab1ddb /django/db | |
parent | f2b6570db165ec5cf317f24082fd96629f11b5d4 (diff) | |
download | django-9acae362cf03ebb951a3b8ad3fd70c19b1990836.tar.gz |
[boulder-oracle-sprint] Refactoring to move some oracle conditional code
into the backend
git-svn-id: http://code.djangoproject.com/svn/django/branches/boulder-oracle-sprint@4021 bcc190cf-cafb-0310-a4f2-bffc1f526a37
Diffstat (limited to 'django/db')
-rw-r--r-- | django/db/backends/oracle/query.py | 126 | ||||
-rw-r--r-- | django/db/models/query.py | 54 |
2 files changed, 122 insertions, 58 deletions
diff --git a/django/db/backends/oracle/query.py b/django/db/backends/oracle/query.py index ab4d7d43b6..ed72540632 100644 --- a/django/db/backends/oracle/query.py +++ b/django/db/backends/oracle/query.py @@ -1,6 +1,6 @@ -# NOTE: still dependent on other code that Matt Boersma is working on, not yet tested!!! - Jim Baker - from django.db import backend, connection +from django.db.models.query import handle_legacy_orderlist +from django.utils.datastructures import SortedDict import cx_Oracle as Database @@ -17,15 +17,15 @@ def get_query_set_class(DefaultQuerySet): # undefined, so we convert it to a list of tuples. extra_select = self._select.items() - cursor = connection.cursor() - full_query = None - select, sql, params, full_query = self._get_sql_clause() - - if not full_query: - cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) - else: - cursor.execute(full_query, params) + select, sql, params, full_query = self._get_sql_clause() + if not full_query: + full_query = "SELECT %s%s\n%s" % \ + ((self._distinct and "DISTINCT " or ""), + ', '.join(select), sql) + + cursor = connection.cursor() + cursor.execute(full_query, params) fill_cache = self._select_related index_end = len(self.model._meta.fields) @@ -51,5 +51,111 @@ def get_query_set_class(DefaultQuerySet): setattr(obj, k[0], row[index_end+i]) yield obj + def _get_sql_clause(self): + opts = self.model._meta + + # Construct the fundamental parts of the query: SELECT X FROM Y WHERE Z. + select = ["%s.%s" % (backend.quote_name(opts.db_table), backend.quote_name(f.column)) for f in opts.fields] + tables = [quote_only_if_word(t) for t in self._tables] + joins = SortedDict() + where = self._where[:] + params = self._params[:] + + # Convert self._filters into SQL. + joins2, where2, params2 = self._filters.get_sql(opts) + joins.update(joins2) + where.extend(where2) + params.extend(params2) + + # Add additional tables and WHERE clauses based on select_related. + if self._select_related: + fill_table_cache(opts, select, tables, where, opts.db_table, [opts.db_table]) + + # Add any additional SELECTs. + if self._select: + select.extend(['(%s) AS %s' % (quote_only_if_word(s[1]), backend.quote_name(s[0])) for s in self._select.items()]) + + # Start composing the body of the SQL statement. + sql = [" FROM", backend.quote_name(opts.db_table)] + + # Compose the join dictionary into SQL describing the joins. + if joins: + sql.append(" ".join(["%s %s %s ON %s" % (join_type, table, alias, condition) + for (alias, (table, join_type, condition)) in joins.items()])) + + # Compose the tables clause into SQL. + if tables: + sql.append(", " + ", ".join(tables)) + + # Compose the where clause into SQL. + if where: + sql.append(where and "WHERE " + " AND ".join(where)) + + # ORDER BY clause + order_by = [] + if self._order_by is not None: + ordering_to_use = self._order_by + else: + ordering_to_use = opts.ordering + for f in handle_legacy_orderlist(ordering_to_use): + if f == '?': # Special case. + order_by.append(backend.get_random_function_sql()) + else: + if f.startswith('-'): + col_name = f[1:] + order = "DESC" + else: + col_name = f + order = "ASC" + if "." in col_name: + table_prefix, col_name = col_name.split('.', 1) + table_prefix = backend.quote_name(table_prefix) + '.' + else: + # Use the database table as a column prefix if it wasn't given, + # and if the requested column isn't a custom SELECT. + if "." not in col_name and col_name not in (self._select or ()): + table_prefix = backend.quote_name(opts.db_table) + '.' + else: + table_prefix = '' + order_by.append('%s%s %s' % (table_prefix, backend.quote_name(orderfield2column(col_name, opts)), order)) + if order_by: + sql.append("ORDER BY " + ", ".join(order_by)) + + # LIMIT and OFFSET clauses + # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query. + select_clause = ",".join(select) + distinct = (self._distinct and "DISTINCT " or "") + + if order_by: + order_by_clause = " OVER (ORDER BY %s )" % (", ".join(order_by)) + else: + #Oracle's row_number() function always requires an order-by clause. + #So we need to define a default order-by, since none was provided. + order_by_clause = " OVER (ORDER BY %s.%s)" % \ + (backend.quote_name(opts.db_table), + backend.quote_name(opts.fields[0].db_column or opts.fields[0].column)) + # limit_and_offset_clause + offset = self._offset and int(self._offset) or 0 + limit = self._limit and int(self._limit) or None + limit_and_offset_clause = '' + if limit: + limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset) + elif offset: + limit_and_offset_clause = "WHERE rn > %s" % (offset) + + if len(limit_and_offset_clause) > 0: + full_query = """SELECT * FROM + (SELECT %s + %s, + ROW_NUMBER() %s AS rn + %s + ) + %s + """ % (distinct, select_clause, order_by_clause, " ".join(sql), limit_and_offset_clause) + else: + full_query = None + + return select, " ".join(sql), params, full_query + return OracleQuerySet diff --git a/django/db/models/query.py b/django/db/models/query.py index 19bd504617..d49438c525 100644 --- a/django/db/models/query.py +++ b/django/db/models/query.py @@ -172,15 +172,9 @@ class _QuerySet(object): cursor = connection.cursor() full_query = None - if settings.DATABASE_ENGINE == 'oracle': - select, sql, params, full_query = self._get_sql_clause() - else: - select, sql, params = self._get_sql_clause() + select, sql, params = self._get_sql_clause() + cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) - if not full_query: - cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) - else: - cursor.execute(full_query, params) fill_cache = self._select_related index_end = len(self.model._meta.fields) while 1: @@ -515,48 +509,12 @@ class _QuerySet(object): sql.append("ORDER BY " + ", ".join(order_by)) # LIMIT and OFFSET clauses - if settings.DATABASE_ENGINE != 'oracle': - if self._limit is not None: - sql.append("%s " % backend.get_limit_offset_sql(self._limit, self._offset)) - else: - assert self._offset is None, "'offset' is not allowed without 'limit'" - - return select, " ".join(sql), params + if self._limit is not None: + sql.append("%s " % backend.get_limit_offset_sql(self._limit, self._offset)) else: - # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query. - select_clause = ",".join(select) - distinct = (self._distinct and "DISTINCT " or "") + assert self._offset is None, "'offset' is not allowed without 'limit'" - if order_by: - order_by_clause = " OVER (ORDER BY %s )" % (", ".join(order_by)) - else: - #Oracle's row_number() function always requires an order-by clause. - #So we need to define a default order-by, since none was provided. - order_by_clause = " OVER (ORDER BY %s.%s)" % \ - (backend.quote_name(opts.db_table), - backend.quote_name(opts.fields[0].db_column or opts.fields[0].column)) - # limit_and_offset_clause - offset = self._offset and int(self._offset) or 0 - limit = self._limit and int(self._limit) or None - limit_and_offset_clause = '' - if limit: - limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset) - elif offset: - limit_and_offset_clause = "WHERE rn > %s" % (offset) - - if len(limit_and_offset_clause) > 0: - full_query = """SELECT * FROM - (SELECT %s - %s, - ROW_NUMBER() %s AS rn - %s - ) - %s - """ % (distinct, select_clause, order_by_clause, " ".join(sql), limit_and_offset_clause) - else: - full_query = None - - return select, " ".join(sql), params, full_query + return select, " ".join(sql), params # Check to see if the DB backend would like to define its own QuerySet class # and otherwise use the default. |