Ticket #87: unified_diff_for_django_oracle_support.txt

File unified_diff_for_django_oracle_support.txt, 17.2 KB (added by Jason Huggins, 19 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