Ticket #87: unified_diff_for_django_oracle_support.txt

File unified_diff_for_django_oracle_support.txt, 17.2 KB (added by Jason Huggins, 15 years ago)

Unified diff showing all changes... The diff does not include oracle.py, of course, since it is a new file.

Line 
1Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py
2===================================================================
3--- C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (revision 477)
4+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/__init__.py (working copy)
5@@ -42,3 +42,4 @@
6 OPERATOR_MAPPING = dbmod.OPERATOR_MAPPING
7 DATA_TYPES = dbmod.DATA_TYPES
8 DATA_TYPES_REVERSE = dbmod.DATA_TYPES_REVERSE
9+EMPTY_STR_EQUIV = dbmod.EMPTY_STR_EQUIV
10
11Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py
12===================================================================
13--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py   (revision 477)
14+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/mysql.py   (working copy)
15@@ -146,3 +146,5 @@
16     FIELD_TYPE.LONG_BLOB: 'TextField',
17     FIELD_TYPE.VAR_STRING: 'CharField',
18 }
19+
20+EMPTY_STR_EQUIV = ''
21\ No newline at end of file
22
23
24Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py
25===================================================================
26--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py  (revision 477)
27+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/postgresql.py  (working copy)
28@@ -177,3 +177,5 @@
29     1266: 'TimeField',
30     1700: 'FloatField',
31 }
32+
33+EMPTY_STR_EQUIV = ''
34\ No newline at end of file
35
36
37Index: C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py
38===================================================================
39--- C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (revision 477)
40+++ C:/MyStuff/projects/django-svn/trunk/django/core/db/backends/sqlite3.py (working copy)
41@@ -165,3 +165,5 @@
42 }
43 
44 DATA_TYPES_REVERSE = {}
45+
46+EMPTY_STR_EQUIV = ''
47\ No newline at end of file
48
49
50Index: C:/MyStuff/projects/django-svn/trunk/django/core/management.py
51===================================================================
52--- C:/MyStuff/projects/django-svn/trunk/django/core/management.py  (revision 477)
53+++ C:/MyStuff/projects/django-svn/trunk/django/core/management.py  (working copy)
54@@ -20,7 +20,7 @@
55 ADMIN_TEMPLATE_DIR = os.path.join(django.__path__[0], 'conf/admin_templates')
56 
57 def _get_packages_insert(app_label):
58-    return "INSERT INTO packages (label, name) VALUES ('%s', '%s');" % (app_label, app_label)
59+    return "INSERT INTO packages (label, name) VALUES ('%s', '%s')" % (app_label, app_label)
60 
61 def _get_permission_codename(action, opts):
62     return '%s_%s' % (action, opts.object_name.lower())
63@@ -34,11 +34,11 @@
64     return perms + list(opts.permissions)
65 
66 def _get_permission_insert(name, codename, opts):
67-    return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s');" % \
68+    return "INSERT INTO auth_permissions (name, package, codename) VALUES ('%s', '%s', '%s')" % \
69         (name.replace("'", "''"), opts.app_label, codename)
70 
71 def _get_contenttype_insert(opts):
72-    return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s');" % \
73+    return "INSERT INTO content_types (name, package, python_module_name) VALUES ('%s', '%s', '%s')" % \
74         (opts.verbose_name, opts.app_label, opts.module_name)
75 
76 def _is_valid_dir_name(s):
77@@ -85,9 +85,26 @@
78         full_statement = ['CREATE TABLE %s (' % opts.db_table]
79         for i, line in enumerate(table_output): # Combine and add commas.
80             full_statement.append('    %s%s' % (line, i < len(table_output)-1 and ',' or ''))
81-        full_statement.append(');')
82+        full_statement.append(')')
83         final_output.append('\n'.join(full_statement))
84+       
85+        if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field):
86+            # To simulate auto-incrementing primary keys in Oracle
87+                       
88+            sequence_statement = 'CREATE SEQUENCE %s_sq\n' % opts.db_table
89+            final_output.append(sequence_statement)
90+           
91+            trigger_statement = "" + \
92+                "CREATE OR REPLACE trigger %s_tr\n"    % opts.db_table + \
93+                "  before insert on %s\n"           % opts.db_table + \
94+                "    for each row\n"  + \
95+                "      begin\n" + \
96+                "        select %s_sq.NEXTVAL into :new.id from DUAL;\n" % opts.db_table + \
97+                "      end;\n"
98+            final_output.append(trigger_statement)
99+                               
100 
101+   
102     for klass in mod._MODELS:
103         opts = klass._meta
104         for f in opts.many_to_many:
105@@ -98,8 +115,22 @@
106             table_output.append('    %s_id %s NOT NULL REFERENCES %s (%s),' % \
107                 (f.rel.to.object_name.lower(), db.DATA_TYPES['IntegerField'], f.rel.to.db_table, f.rel.to.pk.name))
108             table_output.append('    UNIQUE (%s_id, %s_id)' % (opts.object_name.lower(), f.rel.to.object_name.lower()))
109-            table_output.append(');')
110+            table_output.append(')')
111             final_output.append('\n'.join(table_output))
112+           
113+            if (db.DATABASE_ENGINE == 'oracle') & (opts.has_auto_field):
114+                sequence_statement = 'CREATE SEQUENCE %s_sq\n' % f.get_m2m_db_table(opts)
115+                final_output.append(sequence_statement)   
116+                               
117+                trigger_statement = "" + \
118+                    "CREATE OR REPLACE trigger %s_tr\n"    % f.get_m2m_db_table(opts) + \
119+                    "  before insert on %s\n"           % f.get_m2m_db_table(opts) + \
120+                    "    for each row\n"  + \
121+                    "      begin\n" + \
122+                    "        select %s_sq.NEXTVAL into :new.id from DUAL;\n" % f.get_m2m_db_table(opts) + \
123+                    "      end;\n"
124+                final_output.append(trigger_statement)               
125+           
126     return final_output
127 get_sql_create.help_doc = "Prints the CREATE TABLE SQL statements for the given app(s)."
128 get_sql_create.args = APP_ARGS
129@@ -116,23 +147,27 @@
130         try:
131             if cursor is not None:
132                 # Check whether the table exists.
133-                cursor.execute("SELECT 1 FROM %s LIMIT 1" % klass._meta.db_table)
134+                cursor.execute("SELECT COUNT(1) FROM %s" % klass._meta.db_table)
135         except:
136             # The table doesn't exist, so it doesn't need to be dropped.
137             db.db.rollback()
138         else:
139             output.append("DROP TABLE %s;" % klass._meta.db_table)
140+            if db.DATABASE_ENGINE == 'oracle':
141+                output.append("DROP SEQUENCE %s_sq" % klass._meta.db_table)
142+           
143     for klass in mod._MODELS:
144         opts = klass._meta
145         for f in opts.many_to_many:
146             try:
147                 if cursor is not None:
148-                    cursor.execute("SELECT 1 FROM %s LIMIT 1" % f.get_m2m_db_table(opts))
149+                    cursor.execute("SELECT COUNT(1) FROM %s" % f.get_m2m_db_table(opts))
150             except:
151                 db.db.rollback()
152             else:
153                 output.append("DROP TABLE %s;" % f.get_m2m_db_table(opts))
154-
155+                if db.DATABASE_ENGINE == 'oracle':
156+                    output.append("DROP SEQUENCE %s_sq" % f.get_m2m_db_table(opts))
157     app_label = mod._MODELS[0]._meta.app_label
158 
159     # Delete from packages, auth_permissions, content_types.
160@@ -181,12 +216,15 @@
161 
162 def get_sql_sequence_reset(mod):
163     "Returns a list of the SQL statements to reset PostgreSQL sequences for the given module."
164-    from django.core import meta
165+    from django.core import db, meta
166     output = []
167     for klass in mod._MODELS:
168         for f in klass._meta.fields:
169             if isinstance(f, meta.AutoField):
170-                output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table))
171+                if db.DATABASE_ENGINE == 'postgresql':
172+                    output.append("SELECT setval('%s_%s_seq', (SELECT max(%s) FROM %s));" % (klass._meta.db_table, f.name, f.name, klass._meta.db_table))
173+                else:
174+                    raise "Not implemented yet for %s!" % (db.DATABASE_ENGINE)
175     return output
176 get_sql_sequence_reset.help_doc = "Prints the SQL statements for resetting PostgreSQL sequences for the given app(s)."
177 get_sql_sequence_reset.args = APP_ARGS
178
179
180Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py
181===================================================================
182--- C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py   (revision 477)
183+++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/__init__.py   (working copy)
184@@ -1045,8 +1045,8 @@
185     kwargs['select'] = kwargs.get('select', {}).items()
186 
187     cursor = db.db.cursor()
188-    select, sql, params = function_get_sql_clause(opts, **kwargs)
189-    cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params)
190+    select, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
191+    cursor.execute(full_query, params)
192     fill_cache = kwargs.get('select_related')
193     index_end = len(opts.fields)
194     while 1:
195@@ -1070,7 +1070,7 @@
196     kwargs['offset'] = None
197     kwargs['limit'] = None
198     kwargs['select_related'] = False
199-    _, sql, params = function_get_sql_clause(opts, **kwargs)
200+    _, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
201     cursor = db.db.cursor()
202     cursor.execute("SELECT COUNT(*)" + sql, params)
203     return cursor.fetchone()[0]
204@@ -1087,7 +1087,7 @@
205         fields = [f.name for f in opts.fields]
206 
207     cursor = db.db.cursor()
208-    _, sql, params = function_get_sql_clause(opts, **kwargs)
209+    _, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
210     select = ['%s.%s' % (opts.db_table, f) for f in fields]
211     cursor.execute("SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql, params)
212     while 1:
213@@ -1116,7 +1116,7 @@
214                 db_table = new_prefix
215             cache_tables_seen.append(db_table)
216             where.append('%s.%s = %s.%s' % (old_prefix, f.name, db_table, f.rel.field_name))
217-            select.extend(['%s.%s' % (db_table, f2.name) for f2 in f.rel.to.fields])
218+            select.extend(['%s.%s as "%s.%s"' % (db_table, f2.name, db_table, f2.name) for f2 in f.rel.to.fields])
219             _fill_table_cache(f.rel.to, select, tables, where, db_table, cache_tables_seen)
220 
221 def _throw_bad_kwarg_error(kwarg):
222@@ -1266,16 +1266,58 @@
223                 order_by.append('%s%s ASC' % (table_prefix, f))
224     order_by = ", ".join(order_by)
225 
226-    # LIMIT and OFFSET clauses
227-    if kwargs.get('limit') is not None:
228-        limit_sql = " LIMIT %s " % kwargs['limit']
229-        if kwargs.get('offset') is not None and kwargs['offset'] != 0:
230-            limit_sql += "OFFSET %s " % kwargs['offset']
231+
232+    sql = " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "")
233+
234+    if (db.DATABASE_ENGINE != 'oracle'):
235+        # LIMIT and OFFSET clauses
236+        if kwargs.get('limit') is not None:           
237+            limit_sql = " LIMIT %s " % kwargs['limit']
238+            if kwargs.get('offset') is not None and kwargs['offset'] != 0:
239+                limit_sql += "OFFSET %s " % kwargs['offset']
240+        else:
241+            limit_sql = ""       
242+
243+        full_query = "SELECT " + (kwargs.get('distinct') and "DISTINCT " or "") + ",".join(select) + sql + limit_sql
244+        return select, sql + limit_sql, params, full_query
245     else:
246-        limit_sql = ""
247+        # To support limits and offsets, Oracle requires some funky rewriting of an otherwise normal looking query.
248+       
249+        select_clause = ",".join(select)
250+        distinct = (kwargs.get('distinct') and "DISTINCT " or "")
251+        from_clause = ",".join(tables)
252+        where_clause = (where and " WHERE " + " AND ".join(where) or "")
253+       
254+        if order_by:           
255+            order_by_clause = " OVER (ORDER BY %s )" % (order_by)
256+        else:
257+            #Oracle's row_number() function always requires an order-by clause.
258+            #So we need to define a default order-by, since none was provided.
259+            order_by_clause = " OVER (ORDER BY %s.%s)" % (opts.db_table, opts.fields[0].name)           
260+       
261+        # limit_and_offset_clause
262+        limit = kwargs.get('limit',0)
263+        offset = kwargs.get('offset',0)
264+       
265+        limit_and_offset_clause = ''
266+        if limit:
267+            limit = int(limit)
268+            offset = int(offset)
269+            limit_and_offset_clause = "WHERE rn > %s AND rn <= %s" % (offset, limit+offset)
270+        else:
271+            limit_and_offset_clause = "WHERE rn > %s" % (offset)
272+                                 
273+        full_query = """SELECT * FROM
274+                         (SELECT %s                 
275+                          %s,
276+                          ROW_NUMBER() %s AS rn
277+                          FROM %s
278+                          %s
279+                         )
280+                         %s
281+                     """ % (distinct, select_clause, order_by_clause, from_clause, where_clause, limit_and_offset_clause)
282+        return select, sql, params, full_query                               
283 
284-    return select, " FROM " + ",".join(tables) + (where and " WHERE " + " AND ".join(where) or "") + (order_by and " ORDER BY " + order_by or "") + limit_sql, params
285-
286 def function_get_in_bulk(opts, klass, *args, **kwargs):
287     id_list = args and args[0] or kwargs['id_list']
288     assert id_list != [], "get_in_bulk() cannot be passed an empty list."
289@@ -1300,7 +1342,7 @@
290     kwargs['order_by'] = [] # Clear this because it'll mess things up otherwise.
291     if field.null:
292         kwargs.setdefault('where', []).append('%s.%s IS NOT NULL' % (opts.db_table, field.name))
293-    select, sql, params = function_get_sql_clause(opts, **kwargs)
294+    select, sql, params, full_query = function_get_sql_clause(opts, **kwargs)
295     sql = 'SELECT %s %s GROUP BY 1 ORDER BY 1' % (db.get_date_trunc_sql(kind, '%s.%s' % (opts.db_table, field.name)), sql)
296     cursor = db.db.cursor()
297     cursor.execute(sql, params)
298
299
300Index: C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py
301===================================================================
302--- C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (revision 477)
303+++ C:/MyStuff/projects/django-svn/trunk/django/core/meta/fields.py (working copy)
304@@ -1,5 +1,6 @@
305 from django.conf import settings
306 from django.core import formfields, validators
307+from django.core import db
308 from django.core.exceptions import ObjectDoesNotExist
309 from django.utils.functional import curry
310 from django.utils.text import capfirst
311@@ -88,6 +89,12 @@
312 
313     def get_db_prep_save(self, value):
314         "Returns field's value prepared for saving into a database."
315+       
316+        # Oracle treats empty strings ('') the same as NULLs.
317+        # To get around this wart, we need to change it to something else...
318+        if value == '':
319+            string_replacement = getattr(db,'EMPTY_STR_EQUIV','')
320+            value = string_replacement
321         return value
322 
323     def get_db_prep_lookup(self, lookup_type, value):
324@@ -255,6 +262,14 @@
325         kwargs['blank'] = True
326         Field.__init__(self, *args, **kwargs)
327 
328+    def get_db_prep_lookup(self, lookup_type, value):
329+        if db.DATABASE_ENGINE == 'oracle':
330+            if value == 'True':
331+                value = 1
332+            elif value == 'False':
333+                value = 0
334+        return Field.get_db_prep_lookup(self, lookup_type, value)
335+
336     def get_manipulator_field_objs(self):
337         return [formfields.CheckboxField]
338 
339@@ -289,7 +304,9 @@
340     def get_db_prep_save(self, value):
341         # Casts dates into string format for entry into database.
342         if value is not None:
343-            value = value.strftime('%Y-%m-%d')
344+            if db.DATABASE_ENGINE != 'oracle':
345+            #Oracle does not need a string conversion
346+                value = value.strftime('%Y-%m-%d')
347         return Field.get_db_prep_save(self, value)
348 
349     def get_manipulator_field_objs(self):
350@@ -299,7 +316,9 @@
351     def get_db_prep_save(self, value):
352         # Casts dates into string format for entry into database.
353         if value is not None:
354-            value = value.strftime('%Y-%m-%d %H:%M:%S')
355+            if db.DATABASE_ENGINE != 'oracle':
356+            #Oracle does not need a string conversion       
357+                value = value.strftime('%Y-%m-%d %H:%M:%S')
358         return Field.get_db_prep_save(self, value)
359 
360     def get_manipulator_field_objs(self):
361@@ -493,7 +512,9 @@
362     def get_db_prep_save(self, value):
363         # Casts dates into string format for entry into database.
364         if value is not None:
365-            value = value.strftime('%H:%M:%S')
366+            if db.DATABASE_ENGINE != 'oracle':
367+            #Oracle does not need a string conversion           
368+                value = value.strftime('%H:%M:%S')
369         return Field.get_db_prep_save(self, value)
370 
371     def get_manipulator_field_objs(self):
372
Back to Top