Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (working copy) @@ -42,3 +42,4 @@ OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING DATA_TYPES = dbmod.DATA_TYPES DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE +EMPTY_STR_EQUIV = dbmod.EMPTY_STR_EQUIV Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py (working copy) @@ -146,3 +146,5 @@ FIELD_TYPE.LONG_BLOB: 'TextField', FIELD_TYPE.VAR_STRING: 'CharField', } + +EMPTY_STR_EQUIV = '' \ No newline at end of file Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py (working copy) @@ -177,3 +177,5 @@ 1266: 'TimeField', 1700: 'FloatField', } + +EMPTY_STR_EQUIV = '' \ No newline at end of file Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (working copy) @@ -165,3 +165,5 @@ } DATA_TYPES_REVERSE = {} + +EMPTY_STR_EQUIV = '' \ No newline at end of file Index: C:/MyStuff/projects/django-svn/trunk/django/core/management.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/management.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/management.py (working copy) @@ -20,7 +20,7 @@ ADMIN_TEMPLATE_DIR = os.path.join(django.__path__[0], 'conf/admin_templates') def _get_packages_insert(app_label): - return "INSERT INTO packages (label, name) VALUES ('%s', '%s');" % (app_label, app_label) + return "INSERT INTO packages (label, name) VALUES ('%s', '%s')" % (app_label, app_label) def _get_permission_codename(action, opts): return '%s_%s' % (action, opts.object_name.lower()) @@ -34,11 +34,11 @@ return perms + list(opts.permissions) def _get_permission_insert(name, codename, opts): - return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s');" % \ + return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s')" % \ (name.replace("'", "''"), opts.app_label, codename) def _get_contenttype_insert(opts): - return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s');" % \ + return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s')" % \ (opts.verbose_name, opts.app_label, opts.module_name) def _is_valid_dir_name(s): @@ -85,9 +85,26 @@ full_statement = ['CREATE TABLE %s (' % opts.db_table] for i, line in enumerate(table_output): # Combine and add commas. full_statement.append(' %s%s' % (line, i < len(table_output)-1 and ',' or '')) - full_statement.append(');') + full_statement.append(')') final_output.append('\n'.join(full_statement)) + + if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field): + # To simulate auto-incrementing primary keys in Oracle + + sequence_statement = 'CREATE SEQUENCE %s_sq\n' % opts.db_table + final_output.append(sequence_statement) + + trigger_statement = "" + \ + "CREATE OR REPLACE trigger %s_tr\n" % opts.db_table + \ + " before insert on %s\n" % opts.db_table + \ + " for each row\n" + \ + " begin\n" + \ + " select %s_sq.NEXTVAL into :new.id from DUAL;\n" % opts.db_table + \ + " end;\n" + final_output.append(trigger_statement) + + for klass in mod._MODELS: opts = klass._meta for f in opts.many_to_many: @@ -98,8 +115,22 @@ table_output.append(' %s_id %s NOT NULL REFERENCES %s (%s),' % \ (f.rel.to.object_name.lower(), db.DATA_TYPES['IntegerField'], f.rel.to.db_table, f.rel.to.pk.name)) table_output.append(' UNIQUE (%s_id, %s_id)' % (opts.object_name.lower(), f.rel.to.object_name.lower())) - table_output.append(');') + table_output.append(')') final_output.append('\n'.join(table_output)) + + if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field): + sequence_statement = 'CREATE SEQUENCE %s_sq\n' % f.get_m2m_db_table(opts) + final_output.append(sequence_statement) + + trigger_statement = "" + \ + "CREATE OR REPLACE trigger %s_tr\n" % f.get_m2m_db_table(opts) + \ + " before insert on %s\n" % f.get_m2m_db_table(opts) + \ + " for each row\n" + \ + " begin\n" + \ + " select %s_sq.NEXTVAL into :new.id from DUAL;\n" % f.get_m2m_db_table(opts) + \ + " end;\n" + final_output.append(trigger_statement) + return final_output get_sql_create.help_doc = "Prints the CREATE TABLE SQL statements for the given app(s)." get_sql_create.args = APP_ARGS @@ -116,23 +147,27 @@ try: if cursor is not None: # Check whether the table exists. - cursor.execute("SELECT 1 FROM %s LIMIT 1" % klass._meta.db_table) + cursor.execute("SELECT COUNT(1) FROM %s" % klass._meta.db_table) except: # The table doesn't exist, so it doesn't need to be dropped. db.db.rollback() else: output.append("DROP TABLE %s;" % klass._meta.db_table) + if db.DATABASE_ENGINE == 'oracle': + output.append("DROP SEQUENCE %s_sq" % klass._meta.db_table) + for klass in mod._MODELS: opts = klass._meta for f in opts.many_to_many: try: if cursor is not None: - cursor.execute("SELECT 1 FROM %s LIMIT 1" % f.get_m2m_db_table(opts)) + cursor.execute("SELECT COUNT(1) FROM %s" % f.get_m2m_db_table(opts)) except: db.db.rollback() else: output.append("DROP TABLE %s;" % f.get_m2m_db_table(opts)) - + if db.DATABASE_ENGINE == 'oracle': + output.append("DROP SEQUENCE %s_sq" % f.get_m2m_db_table(opts)) app_label = mod._MODELS[0]._meta.app_label # Delete from packages, auth_permissions, content_types. @@ -181,12 +216,15 @@ def get_sql_sequence_reset(mod): "Returns a list of the SQL statements to reset PostgreSQL sequences for the given module." - from django.core import meta + from django.core import db, meta output = [] for klass in mod._MODELS: for f in klass._meta.fields: if isinstance(f, meta.AutoField): - output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table)) + if db.DATABASE_ENGINE == 'postgresql': + output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table)) + else: + raise "Not implemented yet for %s!" % (db.DATABASE_ENGINE) return output get_sql_sequence_reset.help_doc = "Prints the SQL statements for resetting PostgreSQL sequences for the given app(s)." get_sql_sequence_reset.args = APP_ARGS Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py (working copy) @@ -1045,8 +1045,8 @@ kwargs['select'] = kwargs.get('select', {}).items() cursor = db.db.cursor() - select, sql, params = function_get_sql_clause(opts, **kwargs) - cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params) + select, sql, params, full_query = function_get_sql_clause(opts, **kwargs) + cursor.execute(full_query, params) fill_cache = kwargs.get('select_related') index_end = len(opts.fields) while 1: @@ -1070,7 +1070,7 @@ kwargs['offset'] = None kwargs['limit'] = None kwargs['select_related'] = False - _, sql, params = function_get_sql_clause(opts, **kwargs) + _, sql, params, full_query = function_get_sql_clause(opts, **kwargs) cursor = db.db.cursor() cursor.execute("SELECT COUNT(*)" + sql, params) return cursor.fetchone()[0] @@ -1087,7 +1087,7 @@ fields = [f.name for f in opts.fields] cursor = db.db.cursor() - _, sql, params = function_get_sql_clause(opts, **kwargs) + _, sql, params, full_query = function_get_sql_clause(opts, **kwargs) select = ['%s.%s' % (opts.db_table, f) for f in fields] cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params) while 1: @@ -1116,7 +1116,7 @@ db_table = new_prefix cache_tables_seen.append(db_table) where.append('%s.%s = %s.%s' % (old_prefix, f.name, db_table, f.rel.field_name)) - select.extend(['%s.%s' % (db_table, f2.name) for f2 in f.rel.to.fields]) + select.extend(['%s.%s as "%s.%s"' % (db_table, f2.name, db_table, f2.name) for f2 in f.rel.to.fields]) _fill_table_cache(f.rel.to, select, tables, where, db_table, cache_tables_seen) def _throw_bad_kwarg_error(kwarg): @@ -1266,16 +1266,58 @@ order_by.append('%s%s ASC' % (table_prefix, f)) order_by = ", ".join(order_by) - # LIMIT and OFFSET clauses - if kwargs.get('limit') is not None: - limit_sql = " LIMIT %s " % kwargs['limit'] - if kwargs.get('offset') is not None and kwargs['offset'] != 0: - limit_sql += "OFFSET %s " % kwargs['offset'] + + sql = " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "") + + if (db.DATABASE_ENGINE != 'oracle'): + # LIMIT and OFFSET clauses + if kwargs.get('limit') is not None: + limit_sql = " LIMIT %s " % kwargs['limit'] + if kwargs.get('offset') is not None and kwargs['offset'] != 0: + limit_sql += "OFFSET %s " % kwargs['offset'] + else: + limit_sql = "" + + full_query = "SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql + limit_sql + return select, sql + limit_sql, params, full_query else: - limit_sql = "" + # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query. + + select_clause = ",".join(select) + distinct = (kwargs.get('distinct') and "DISTINCT " or "") + from_clause = ",".join(tables) + where_clause = (where and " WHERE " + " AND ".join(where) or "") + + if order_by: + order_by_clause = " OVER (ORDER BY %s )" % (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)" % (opts.db_table, opts.fields[0].name) + + # limit_and_offset_clause + limit = kwargs.get('limit',0) + offset = kwargs.get('offset',0) + + limit_and_offset_clause = '' + if limit: + limit = int(limit) + offset = int(offset) + limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset) + else: + limit_and_offset_clause = "WHERE rn > %s" % (offset) + + full_query = """SELECT * FROM + (SELECT %s + %s, + ROW_NUMBER() %s AS rn + FROM %s + %s + ) + %s + """ % (distinct, select_clause, order_by_clause, from_clause, where_clause, limit_and_offset_clause) + return select, sql, params, full_query - return select, " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "") + limit_sql, params - def function_get_in_bulk(opts, klass, *args, **kwargs): id_list = args and args[0] or kwargs['id_list'] assert id_list != [], "get_in_bulk() cannot be passed an empty list." @@ -1300,7 +1342,7 @@ kwargs['order_by'] = [] # Clear this because it'll mess things up otherwise. if field.null: kwargs.setdefault('where', []).append('%s.%s IS NOT NULL' % (opts.db_table, field.name)) - select, sql, params = function_get_sql_clause(opts, **kwargs) + select, sql, params, full_query = function_get_sql_clause(opts, **kwargs) sql = 'SELECT %s %s GROUP BY 1 ORDER BY 1' % (db.get_date_trunc_sql(kind, '%s.%s' % (opts.db_table, field.name)), sql) cursor = db.db.cursor() cursor.execute(sql, params) Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py =================================================================== --- C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (revision 477) +++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (working copy) @@ -1,5 +1,6 @@ from django.conf import settings from django.core import formfields, validators +from django.core import db from django.core.exceptions import ObjectDoesNotExist from django.utils.functional import curry from django.utils.text import capfirst @@ -88,6 +89,12 @@ def get_db_prep_save(self, value): "Returns field's value prepared for saving into a database." + + # Oracle treats empty strings ('') the same as NULLs. + # To get around this wart, we need to change it to something else... + if value == '': + string_replacement = getattr(db,'EMPTY_STR_EQUIV','') + value = string_replacement return value def get_db_prep_lookup(self, lookup_type, value): @@ -255,6 +262,14 @@ kwargs['blank'] = True Field.__init__(self, *args, **kwargs) + def get_db_prep_lookup(self, lookup_type, value): + if db.DATABASE_ENGINE == 'oracle': + if value == 'True': + value = 1 + elif value == 'False': + value = 0 + return Field.get_db_prep_lookup(self, lookup_type, value) + def get_manipulator_field_objs(self): return [formfields.CheckboxField] @@ -289,7 +304,9 @@ def get_db_prep_save(self, value): # Casts dates into string format for entry into database. if value is not None: - value = value.strftime('%Y-%m-%d') + if db.DATABASE_ENGINE != 'oracle': + #Oracle does not need a string conversion + value = value.strftime('%Y-%m-%d') return Field.get_db_prep_save(self, value) def get_manipulator_field_objs(self): @@ -299,7 +316,9 @@ def get_db_prep_save(self, value): # Casts dates into string format for entry into database. if value is not None: - value = value.strftime('%Y-%m-%d %H:%M:%S') + if db.DATABASE_ENGINE != 'oracle': + #Oracle does not need a string conversion + value = value.strftime('%Y-%m-%d %H:%M:%S') return Field.get_db_prep_save(self, value) def get_manipulator_field_objs(self): @@ -493,7 +512,9 @@ def get_db_prep_save(self, value): # Casts dates into string format for entry into database. if value is not None: - value = value.strftime('%H:%M:%S') + if db.DATABASE_ENGINE != 'oracle': + #Oracle does not need a string conversion + value = value.strftime('%H:%M:%S') return Field.get_db_prep_save(self, value) def get_manipulator_field_objs(self):