3 |
3 |
4 Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/ |
4 Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/ |
5 """ |
5 """ |
6 |
6 |
7 import os |
7 import os |
8 |
8 import datetime |
9 from django.db.backends import BaseDatabaseWrapper, BaseDatabaseFeatures, BaseDatabaseOperations, util |
9 import time |
10 from django.db.backends.oracle import query |
|
11 from django.utils.datastructures import SortedDict |
|
12 from django.utils.encoding import smart_str, force_unicode |
|
13 |
10 |
14 # Oracle takes client-side character set encoding from the environment. |
11 # Oracle takes client-side character set encoding from the environment. |
15 os.environ['NLS_LANG'] = '.UTF8' |
12 os.environ['NLS_LANG'] = '.UTF8' |
16 try: |
13 try: |
17 import cx_Oracle as Database |
14 import cx_Oracle as Database |
18 except ImportError, e: |
15 except ImportError, e: |
19 from django.core.exceptions import ImproperlyConfigured |
16 from django.core.exceptions import ImproperlyConfigured |
20 raise ImproperlyConfigured("Error loading cx_Oracle module: %s" % e) |
17 raise ImproperlyConfigured("Error loading cx_Oracle module: %s" % e) |
21 |
18 |
22 DatabaseError = Database.Error |
19 from django.db.backends import * |
|
20 from django.db.backends.oracle import query |
|
21 from django.db.backends.oracle.client import DatabaseClient |
|
22 from django.db.backends.oracle.creation import DatabaseCreation |
|
23 from django.db.backends.oracle.introspection import DatabaseIntrospection |
|
24 from django.utils.encoding import smart_str, force_unicode |
|
25 |
|
26 DatabaseError = Database.DatabaseError |
23 IntegrityError = Database.IntegrityError |
27 IntegrityError = Database.IntegrityError |
24 |
28 |
|
29 |
25 class DatabaseFeatures(BaseDatabaseFeatures): |
30 class DatabaseFeatures(BaseDatabaseFeatures): |
26 allows_group_by_ordinal = False |
|
27 allows_unique_and_pk = False # Suppress UNIQUE/PK for Oracle (ORA-02259) |
|
28 empty_fetchmany_value = () |
31 empty_fetchmany_value = () |
29 needs_datetime_string_cast = False |
32 needs_datetime_string_cast = False |
30 needs_upper_for_iops = True |
|
31 supports_tablespaces = True |
|
32 uses_case_insensitive_names = True |
|
33 uses_custom_query_class = True |
33 uses_custom_query_class = True |
|
34 interprets_empty_strings_as_nulls = True |
|
35 |
34 |
36 |
35 class DatabaseOperations(BaseDatabaseOperations): |
37 class DatabaseOperations(BaseDatabaseOperations): |
36 def autoinc_sql(self, table, column): |
38 def autoinc_sql(self, table, column): |
37 # To simulate auto-incrementing primary keys in Oracle, we have to |
39 # To simulate auto-incrementing primary keys in Oracle, we have to |
38 # create a sequence and a trigger. |
40 # create a sequence and a trigger. |
39 sq_name = get_sequence_name(table) |
41 sq_name = get_sequence_name(table) |
40 tr_name = get_trigger_name(table) |
42 tr_name = get_trigger_name(table) |
41 tbl_name = self.quote_name(table) |
43 tbl_name = self.quote_name(table) |
42 col_name = self.quote_name(column) |
44 col_name = self.quote_name(column) |
43 sequence_sql = 'CREATE SEQUENCE %s;' % sq_name |
45 sequence_sql = """ |
|
46 DECLARE |
|
47 i INTEGER; |
|
48 BEGIN |
|
49 SELECT COUNT(*) INTO i FROM USER_CATALOG |
|
50 WHERE TABLE_NAME = '%(sq_name)s' AND TABLE_TYPE = 'SEQUENCE'; |
|
51 IF i = 0 THEN |
|
52 EXECUTE IMMEDIATE 'CREATE SEQUENCE %(sq_name)s'; |
|
53 END IF; |
|
54 END; |
|
55 /""" % locals() |
44 trigger_sql = """ |
56 trigger_sql = """ |
45 CREATE OR REPLACE TRIGGER %(tr_name)s |
57 CREATE OR REPLACE TRIGGER %(tr_name)s |
46 BEFORE INSERT ON %(tbl_name)s |
58 BEFORE INSERT ON %(tbl_name)s |
47 FOR EACH ROW |
59 FOR EACH ROW |
48 WHEN (new.%(col_name)s IS NULL) |
60 WHEN (new.%(col_name)s IS NULL) |
84 def last_insert_id(self, cursor, table_name, pk_name): |
96 def last_insert_id(self, cursor, table_name, pk_name): |
85 sq_name = util.truncate_name(table_name, self.max_name_length() - 3) |
97 sq_name = util.truncate_name(table_name, self.max_name_length() - 3) |
86 cursor.execute('SELECT %s_sq.currval FROM dual' % sq_name) |
98 cursor.execute('SELECT %s_sq.currval FROM dual' % sq_name) |
87 return cursor.fetchone()[0] |
99 return cursor.fetchone()[0] |
88 |
100 |
89 def limit_offset_sql(self, limit, offset=None): |
|
90 # Limits and offset are too complicated to be handled here. |
|
91 # Instead, they are handled in django/db/backends/oracle/query.py. |
|
92 return "" |
|
93 |
|
94 def lookup_cast(self, lookup_type): |
101 def lookup_cast(self, lookup_type): |
95 if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'): |
102 if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'): |
96 return "UPPER(%s)" |
103 return "UPPER(%s)" |
97 return "%s" |
104 return "%s" |
98 |
105 |
99 def max_name_length(self): |
106 def max_name_length(self): |
100 return 30 |
107 return 30 |
|
108 |
|
109 def prep_for_iexact_query(self, x): |
|
110 return x |
101 |
111 |
102 def query_class(self, DefaultQueryClass): |
112 def query_class(self, DefaultQueryClass): |
103 return query.query_class(DefaultQueryClass, Database) |
113 return query.query_class(DefaultQueryClass, Database) |
104 |
114 |
105 def quote_name(self, name): |
115 def quote_name(self, name): |
143 style.SQL_FIELD(self.quote_name(table)) |
153 style.SQL_FIELD(self.quote_name(table)) |
144 ) for table in tables] |
154 ) for table in tables] |
145 # Since we've just deleted all the rows, running our sequence |
155 # Since we've just deleted all the rows, running our sequence |
146 # ALTER code will reset the sequence to 0. |
156 # ALTER code will reset the sequence to 0. |
147 for sequence_info in sequences: |
157 for sequence_info in sequences: |
148 table_name = sequence_info['table'] |
158 sequence_name = get_sequence_name(sequence_info['table']) |
149 seq_name = get_sequence_name(table_name) |
159 table_name = self.quote_name(sequence_info['table']) |
150 column_name = self.quote_name(sequence_info['column'] or 'id') |
160 column_name = self.quote_name(sequence_info['column'] or 'id') |
151 query = _get_sequence_reset_sql() % {'sequence': seq_name, |
161 query = _get_sequence_reset_sql() % {'sequence': sequence_name, |
152 'table': self.quote_name(table_name), |
162 'table': table_name, |
153 'column': column_name} |
163 'column': column_name} |
154 sql.append(query) |
164 sql.append(query) |
155 return sql |
165 return sql |
156 else: |
166 else: |
157 return [] |
167 return [] |
159 def sequence_reset_sql(self, style, model_list): |
169 def sequence_reset_sql(self, style, model_list): |
160 from django.db import models |
170 from django.db import models |
161 output = [] |
171 output = [] |
162 query = _get_sequence_reset_sql() |
172 query = _get_sequence_reset_sql() |
163 for model in model_list: |
173 for model in model_list: |
164 for f in model._meta.fields: |
174 for f in model._meta.local_fields: |
165 if isinstance(f, models.AutoField): |
175 if isinstance(f, models.AutoField): |
|
176 table_name = self.quote_name(model._meta.db_table) |
166 sequence_name = get_sequence_name(model._meta.db_table) |
177 sequence_name = get_sequence_name(model._meta.db_table) |
167 column_name = self.quote_name(f.db_column or f.name) |
178 column_name = self.quote_name(f.column) |
168 output.append(query % {'sequence': sequence_name, |
179 output.append(query % {'sequence': sequence_name, |
169 'table': model._meta.db_table, |
180 'table': table_name, |
170 'column': column_name}) |
181 'column': column_name}) |
171 break # Only one AutoField is allowed per model, so don't bother continuing. |
182 break # Only one AutoField is allowed per model, so don't bother continuing. |
172 for f in model._meta.many_to_many: |
183 for f in model._meta.many_to_many: |
|
184 table_name = self.quote_name(f.m2m_db_table()) |
173 sequence_name = get_sequence_name(f.m2m_db_table()) |
185 sequence_name = get_sequence_name(f.m2m_db_table()) |
|
186 column_name = self.quote_name('id') |
174 output.append(query % {'sequence': sequence_name, |
187 output.append(query % {'sequence': sequence_name, |
175 'table': f.m2m_db_table(), |
188 'table': table_name, |
176 'column': self.quote_name('id')}) |
189 'column': column_name}) |
177 return output |
190 return output |
178 |
191 |
179 def start_transaction_sql(self): |
192 def start_transaction_sql(self): |
180 return '' |
193 return '' |
181 |
194 |
182 def tablespace_sql(self, tablespace, inline=False): |
195 def tablespace_sql(self, tablespace, inline=False): |
183 return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""), self.quote_name(tablespace)) |
196 return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""), self.quote_name(tablespace)) |
184 |
197 |
|
198 def value_to_db_time(self, value): |
|
199 if value is None: |
|
200 return None |
|
201 if isinstance(value, basestring): |
|
202 return datetime.datetime(*(time.strptime(value, '%H:%M:%S')[:6])) |
|
203 return datetime.datetime(1900, 1, 1, value.hour, value.minute, |
|
204 value.second, value.microsecond) |
|
205 |
|
206 def year_lookup_bounds_for_date_field(self, value): |
|
207 first = '%s-01-01' |
|
208 second = '%s-12-31' |
|
209 return [first % value, second % value] |
|
210 |
|
211 |
185 class DatabaseWrapper(BaseDatabaseWrapper): |
212 class DatabaseWrapper(BaseDatabaseWrapper): |
186 features = DatabaseFeatures() |
213 |
187 ops = DatabaseOperations() |
|
188 operators = { |
214 operators = { |
189 'exact': '= %s', |
215 'exact': '= %s', |
190 'iexact': '= UPPER(%s)', |
216 'iexact': '= UPPER(%s)', |
191 'contains': "LIKEC %s ESCAPE '\\'", |
217 'contains': "LIKEC %s ESCAPE '\\'", |
192 'icontains': "LIKEC UPPER(%s) ESCAPE '\\'", |
218 'icontains': "LIKEC UPPER(%s) ESCAPE '\\'", |
198 'endswith': "LIKEC %s ESCAPE '\\'", |
224 'endswith': "LIKEC %s ESCAPE '\\'", |
199 'istartswith': "LIKEC UPPER(%s) ESCAPE '\\'", |
225 'istartswith': "LIKEC UPPER(%s) ESCAPE '\\'", |
200 'iendswith': "LIKEC UPPER(%s) ESCAPE '\\'", |
226 'iendswith': "LIKEC UPPER(%s) ESCAPE '\\'", |
201 } |
227 } |
202 oracle_version = None |
228 oracle_version = None |
|
229 |
|
230 def __init__(self, *args, **kwargs): |
|
231 super(DatabaseWrapper, self).__init__(*args, **kwargs) |
|
232 |
|
233 self.features = DatabaseFeatures() |
|
234 self.ops = DatabaseOperations() |
|
235 self.client = DatabaseClient() |
|
236 self.creation = DatabaseCreation(self) |
|
237 self.introspection = DatabaseIntrospection(self) |
|
238 self.validation = BaseDatabaseValidation() |
203 |
239 |
204 def _valid_connection(self): |
240 def _valid_connection(self): |
205 return self.connection is not None |
241 return self.connection is not None |
206 |
242 |
207 def _cursor(self, settings): |
243 def _cursor(self, settings): |
242 cursor = FormatStylePlaceholderCursor(self.connection) |
278 cursor = FormatStylePlaceholderCursor(self.connection) |
243 # Default arraysize of 1 is highly sub-optimal. |
279 # Default arraysize of 1 is highly sub-optimal. |
244 cursor.arraysize = 100 |
280 cursor.arraysize = 100 |
245 return cursor |
281 return cursor |
246 |
282 |
|
283 |
|
284 class OracleParam(object): |
|
285 """ |
|
286 Wrapper object for formatting parameters for Oracle. If the string |
|
287 representation of the value is large enough (greater than 4000 characters) |
|
288 the input size needs to be set as NCLOB. Alternatively, if the parameter has |
|
289 an `input_size` attribute, then the value of the `input_size` attribute will |
|
290 be used instead. Otherwise, no input size will be set for the parameter when |
|
291 executing the query. |
|
292 """ |
|
293 def __init__(self, param, charset, strings_only=False): |
|
294 self.smart_str = smart_str(param, charset, strings_only) |
|
295 if hasattr(param, 'input_size'): |
|
296 # If parameter has `input_size` attribute, use that. |
|
297 self.input_size = param.input_size |
|
298 elif isinstance(param, basestring) and len(param) > 4000: |
|
299 # Mark any string parameter greater than 4000 characters as an NCLOB. |
|
300 self.input_size = Database.NCLOB |
|
301 else: |
|
302 self.input_size = None |
|
303 |
|
304 |
247 class FormatStylePlaceholderCursor(Database.Cursor): |
305 class FormatStylePlaceholderCursor(Database.Cursor): |
248 """ |
306 """ |
249 Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" |
307 Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" |
250 style. This fixes it -- but note that if you want to use a literal "%s" in |
308 style. This fixes it -- but note that if you want to use a literal "%s" in |
251 a query, you'll need to use "%%s". |
309 a query, you'll need to use "%%s". |
256 charset = 'utf-8' |
314 charset = 'utf-8' |
257 |
315 |
258 def _format_params(self, params): |
316 def _format_params(self, params): |
259 if isinstance(params, dict): |
317 if isinstance(params, dict): |
260 result = {} |
318 result = {} |
261 charset = self.charset |
|
262 for key, value in params.items(): |
319 for key, value in params.items(): |
263 result[smart_str(key, charset)] = smart_str(value, charset) |
320 result[smart_str(key, self.charset)] = OracleParam(param, self.charset) |
264 return result |
321 return result |
265 else: |
322 else: |
266 return tuple([smart_str(p, self.charset, True) for p in params]) |
323 return tuple([OracleParam(p, self.charset, True) for p in params]) |
267 |
324 |
268 def _guess_input_sizes(self, params_list): |
325 def _guess_input_sizes(self, params_list): |
269 # Mark any string parameter greater than 4000 characters as an NCLOB. |
|
270 if isinstance(params_list[0], dict): |
326 if isinstance(params_list[0], dict): |
271 sizes = {} |
327 sizes = {} |
272 iterators = [params.iteritems() for params in params_list] |
328 iterators = [params.iteritems() for params in params_list] |
273 else: |
329 else: |
274 sizes = [None] * len(params_list[0]) |
330 sizes = [None] * len(params_list[0]) |
275 iterators = [enumerate(params) for params in params_list] |
331 iterators = [enumerate(params) for params in params_list] |
276 for iterator in iterators: |
332 for iterator in iterators: |
277 for key, value in iterator: |
333 for key, value in iterator: |
278 if isinstance(value, basestring) and len(value) > 4000: |
334 if value.input_size: sizes[key] = value.input_size |
279 sizes[key] = Database.NCLOB |
|
280 if isinstance(sizes, dict): |
335 if isinstance(sizes, dict): |
281 self.setinputsizes(**sizes) |
336 self.setinputsizes(**sizes) |
282 else: |
337 else: |
283 self.setinputsizes(*sizes) |
338 self.setinputsizes(*sizes) |
|
339 |
|
340 def _param_generator(self, params): |
|
341 if isinstance(params, dict): |
|
342 return dict([(k, p.smart_str) for k, p in params.iteritems()]) |
|
343 else: |
|
344 return [p.smart_str for p in params] |
284 |
345 |
285 def execute(self, query, params=None): |
346 def execute(self, query, params=None): |
286 if params is None: |
347 if params is None: |
287 params = [] |
348 params = [] |
288 else: |
349 else: |
294 # is being passed to SQL*Plus. |
355 # is being passed to SQL*Plus. |
295 if query.endswith(';') or query.endswith('/'): |
356 if query.endswith(';') or query.endswith('/'): |
296 query = query[:-1] |
357 query = query[:-1] |
297 query = smart_str(query, self.charset) % tuple(args) |
358 query = smart_str(query, self.charset) % tuple(args) |
298 self._guess_input_sizes([params]) |
359 self._guess_input_sizes([params]) |
299 return Database.Cursor.execute(self, query, params) |
360 try: |
|
361 return Database.Cursor.execute(self, query, self._param_generator(params)) |
|
362 except DatabaseError, e: |
|
363 # cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400. |
|
364 if e.args[0].code == 1400 and not isinstance(e, IntegrityError): |
|
365 e = IntegrityError(e.args[0]) |
|
366 raise e |
300 |
367 |
301 def executemany(self, query, params=None): |
368 def executemany(self, query, params=None): |
302 try: |
369 try: |
303 args = [(':arg%d' % i) for i in range(len(params[0]))] |
370 args = [(':arg%d' % i) for i in range(len(params[0]))] |
304 except (IndexError, TypeError): |
371 except (IndexError, TypeError): |
309 # characters must be included in the original query in case the query |
376 # characters must be included in the original query in case the query |
310 # is being passed to SQL*Plus. |
377 # is being passed to SQL*Plus. |
311 if query.endswith(';') or query.endswith('/'): |
378 if query.endswith(';') or query.endswith('/'): |
312 query = query[:-1] |
379 query = query[:-1] |
313 query = smart_str(query, self.charset) % tuple(args) |
380 query = smart_str(query, self.charset) % tuple(args) |
314 new_param_list = [self._format_params(i) for i in params] |
381 formatted = [self._format_params(i) for i in params] |
315 self._guess_input_sizes(new_param_list) |
382 self._guess_input_sizes(formatted) |
316 return Database.Cursor.executemany(self, query, new_param_list) |
383 try: |
|
384 return Database.Cursor.executemany(self, query, [self._param_generator(p) for p in formatted]) |
|
385 except DatabaseError, e: |
|
386 # cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400. |
|
387 if e.args[0].code == 1400 and not isinstance(e, IntegrityError): |
|
388 e = IntegrityError(e.args[0]) |
|
389 raise e |
317 |
390 |
318 def fetchone(self): |
391 def fetchone(self): |
319 row = Database.Cursor.fetchone(self) |
392 row = Database.Cursor.fetchone(self) |
320 if row is None: |
393 if row is None: |
321 return row |
394 return row |