2
2
Oracle database backend for Django.
4
Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/
4
Requires cx_Oracle: http://cx-oracle.sourceforge.net/
11
from decimal import Decimal
13
from django.utils._decimal import Decimal
11
15
# Oracle takes client-side character set encoding from the environment.
12
16
os.environ['NLS_LANG'] = '.UTF8'
17
# This prevents unicode from getting mangled by getting encoded into the
18
# potentially non-unicode database character set.
19
os.environ['ORA_NCHAR_LITERAL_REPLACE'] = 'TRUE'
14
22
import cx_Oracle as Database
15
23
except ImportError, e:
32
41
needs_datetime_string_cast = False
33
42
uses_custom_query_class = True
34
43
interprets_empty_strings_as_nulls = True
44
uses_savepoints = True
45
can_return_id_from_insert = True
37
48
class DatabaseOperations(BaseDatabaseOperations):
38
50
def autoinc_sql(self, table, column):
39
51
# To simulate auto-incrementing primary keys in Oracle, we have to
40
52
# create a sequence and a trigger.
43
55
tbl_name = self.quote_name(table)
44
56
col_name = self.quote_name(column)
49
SELECT COUNT(*) INTO i FROM USER_CATALOG
50
WHERE TABLE_NAME = '%(sq_name)s' AND TABLE_TYPE = 'SEQUENCE';
52
EXECUTE IMMEDIATE 'CREATE SEQUENCE %(sq_name)s';
61
SELECT COUNT(*) INTO i FROM USER_CATALOG
62
WHERE TABLE_NAME = '%(sq_name)s' AND TABLE_TYPE = 'SEQUENCE';
64
EXECUTE IMMEDIATE 'CREATE SEQUENCE "%(sq_name)s"';
57
CREATE OR REPLACE TRIGGER %(tr_name)s
58
BEFORE INSERT ON %(tbl_name)s
60
WHEN (new.%(col_name)s IS NULL)
62
SELECT %(sq_name)s.nextval
63
INTO :new.%(col_name)s FROM dual;
69
CREATE OR REPLACE TRIGGER "%(tr_name)s"
70
BEFORE INSERT ON %(tbl_name)s
72
WHEN (new.%(col_name)s IS NULL)
74
SELECT "%(sq_name)s".nextval
75
INTO :new.%(col_name)s FROM dual;
66
78
return sequence_sql, trigger_sql
68
80
def date_extract_sql(self, lookup_type, field_name):
69
81
# http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163
70
return "EXTRACT(%s FROM %s)" % (lookup_type, field_name)
82
if lookup_type == 'week_day':
83
# TO_CHAR(field, 'D') returns an integer from 1-7, where 1=Sunday.
84
return "TO_CHAR(%s, 'D')" % field_name
86
return "EXTRACT(%s FROM %s)" % (lookup_type, field_name)
72
88
def date_trunc_sql(self, lookup_type, field_name):
73
89
# Oracle uses TRUNC() for both dates and numbers.
87
103
def drop_sequence_sql(self, table):
88
104
return "DROP SEQUENCE %s;" % self.quote_name(get_sequence_name(table))
106
def fetch_returned_insert_id(self, cursor):
107
return long(cursor._insert_id_var.getvalue())
90
109
def field_cast_sql(self, db_type):
91
110
if db_type and db_type.endswith('LOB'):
92
111
return "DBMS_LOB.SUBSTR(%s)"
96
115
def last_insert_id(self, cursor, table_name, pk_name):
97
sq_name = util.truncate_name(table_name, self.max_name_length() - 3)
98
cursor.execute('SELECT %s_sq.currval FROM dual' % sq_name)
116
sq_name = get_sequence_name(table_name)
117
cursor.execute('SELECT "%s".currval FROM dual' % sq_name)
99
118
return cursor.fetchone()[0]
101
120
def lookup_cast(self, lookup_type):
141
166
connection.cursor()
142
167
return connection.ops.regex_lookup(lookup_type)
169
def return_insert_id(self):
170
return "RETURNING %s INTO %%s", (InsertIdVar(),)
172
def savepoint_create_sql(self, sid):
173
return "SAVEPOINT " + self.quote_name(sid)
175
def savepoint_rollback_sql(self, sid):
176
return "ROLLBACK TO SAVEPOINT " + self.quote_name(sid)
144
178
def sql_flush(self, style, tables, sequences):
145
179
# Return a list of 'TRUNCATE x;', 'TRUNCATE y;',
146
180
# 'TRUNCATE z;'... style SQL statements
150
184
sql = ['%s %s %s;' % \
151
185
(style.SQL_KEYWORD('DELETE'),
152
186
style.SQL_KEYWORD('FROM'),
153
style.SQL_FIELD(self.quote_name(table))
154
) for table in tables]
187
style.SQL_FIELD(self.quote_name(table)))
155
189
# Since we've just deleted all the rows, running our sequence
156
190
# ALTER code will reset the sequence to 0.
157
191
for sequence_info in sequences:
179
213
output.append(query % {'sequence': sequence_name,
180
214
'table': table_name,
181
215
'column': column_name})
182
break # Only one AutoField is allowed per model, so don't bother continuing.
216
# Only one AutoField is allowed per model, so don't
183
219
for f in model._meta.many_to_many:
184
table_name = self.quote_name(f.m2m_db_table())
185
sequence_name = get_sequence_name(f.m2m_db_table())
186
column_name = self.quote_name('id')
187
output.append(query % {'sequence': sequence_name,
189
'column': column_name})
220
if not f.rel.through:
221
table_name = self.quote_name(f.m2m_db_table())
222
sequence_name = get_sequence_name(f.m2m_db_table())
223
column_name = self.quote_name('id')
224
output.append(query % {'sequence': sequence_name,
226
'column': column_name})
192
229
def start_transaction_sql(self):
195
232
def tablespace_sql(self, tablespace, inline=False):
196
return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""), self.quote_name(tablespace))
233
return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""),
234
self.quote_name(tablespace))
198
236
def value_to_db_time(self, value):
199
237
if value is None:
208
246
second = '%s-12-31'
209
247
return [first % value, second % value]
249
def combine_expression(self, connector, sub_expressions):
250
"Oracle requires special cases for %% and & operators in query expressions"
251
if connector == '%%':
252
return 'MOD(%s)' % ','.join(sub_expressions)
253
elif connector == '&':
254
return 'BITAND(%s)' % ','.join(sub_expressions)
255
elif connector == '|':
256
raise NotImplementedError("Bit-wise or is not supported in Oracle.")
257
return super(DatabaseOperations, self).combine_expression(connector, sub_expressions)
212
260
class DatabaseWrapper(BaseDatabaseWrapper):
240
288
def _valid_connection(self):
241
289
return self.connection is not None
243
def _cursor(self, settings):
291
def _connect_string(self):
292
settings_dict = self.settings_dict
293
if len(settings_dict['DATABASE_HOST'].strip()) == 0:
294
settings_dict['DATABASE_HOST'] = 'localhost'
295
if len(settings_dict['DATABASE_PORT'].strip()) != 0:
296
dsn = Database.makedsn(settings_dict['DATABASE_HOST'],
297
int(settings_dict['DATABASE_PORT']),
298
settings_dict['DATABASE_NAME'])
300
dsn = settings_dict['DATABASE_NAME']
301
return "%s/%s@%s" % (settings_dict['DATABASE_USER'],
302
settings_dict['DATABASE_PASSWORD'], dsn)
245
306
if not self._valid_connection():
246
if len(settings.DATABASE_HOST.strip()) == 0:
247
settings.DATABASE_HOST = 'localhost'
248
if len(settings.DATABASE_PORT.strip()) != 0:
249
dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME)
250
self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options)
252
conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
253
self.connection = Database.connect(conn_string, **self.options)
307
conn_string = self._connect_string()
308
self.connection = Database.connect(conn_string, **self.settings_dict['DATABASE_OPTIONS'])
254
309
cursor = FormatStylePlaceholderCursor(self.connection)
255
310
# Set oracle date to ansi date format. This only needs to execute
256
# once when we create a new connection.
257
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' "
258
"NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
311
# once when we create a new connection. We also set the Territory
312
# to 'AMERICA' which forces Sunday to evaluate to a '1' in TO_CHAR().
313
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' "
314
"NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' "
315
"NLS_TERRITORY = 'AMERICA'")
260
317
self.oracle_version = int(self.connection.version.split('.')[0])
261
318
# There's no way for the DatabaseOperations class to know the
274
331
# Django docs specify cx_Oracle version 4.3.1 or higher, but
275
332
# stmtcachesize is available only in 4.3.2 and up.
334
connection_created.send(sender=self.__class__)
278
336
cursor = FormatStylePlaceholderCursor(self.connection)
279
# Default arraysize of 1 is highly sub-optimal.
280
cursor.arraysize = 100
339
# Oracle doesn't support savepoint commits. Ignore them.
340
def _savepoint_commit(self, sid):
284
344
class OracleParam(object):
286
346
Wrapper object for formatting parameters for Oracle. If the string
287
347
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
348
the input size needs to be set as CLOB. Alternatively, if the parameter
349
has an `input_size` attribute, then the value of the `input_size` attribute
350
will be used instead. Otherwise, no input size will be set for the
351
parameter when executing the query.
293
def __init__(self, param, charset, strings_only=False):
294
self.smart_str = smart_str(param, charset, strings_only)
354
def __init__(self, param, cursor, strings_only=False):
355
if hasattr(param, 'bind_parameter'):
356
self.smart_str = param.bind_parameter(cursor)
358
self.smart_str = smart_str(param, cursor.charset, strings_only)
295
359
if hasattr(param, 'input_size'):
296
360
# If parameter has `input_size` attribute, use that.
297
361
self.input_size = param.input_size
298
362
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
363
# Mark any string param greater than 4000 characters as a CLOB.
364
self.input_size = Database.CLOB
302
366
self.input_size = None
305
class FormatStylePlaceholderCursor(Database.Cursor):
369
class InsertIdVar(object):
371
A late-binding cursor variable that can be passed to Cursor.execute
372
as a parameter, in order to receive the id of the row created by an
376
def bind_parameter(self, cursor):
377
param = cursor.var(Database.NUMBER)
378
cursor._insert_id_var = param
382
class FormatStylePlaceholderCursor(object):
307
384
Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var"
308
385
style. This fixes it -- but note that if you want to use a literal "%s" in
314
391
charset = 'utf-8'
393
def __init__(self, connection):
394
self.cursor = connection.cursor()
395
# Necessary to retrieve decimal values without rounding error.
396
self.cursor.numbersAsStrings = True
397
# Default arraysize of 1 is highly sub-optimal.
398
self.cursor.arraysize = 100
316
400
def _format_params(self, params):
317
return tuple([OracleParam(p, self.charset, True) for p in params])
401
return tuple([OracleParam(p, self, True) for p in params])
319
403
def _guess_input_sizes(self, params_list):
320
404
sizes = [None] * len(params_list[0])
321
405
for params in params_list:
322
406
for i, value in enumerate(params):
323
if value.input_size: sizes[i] = value.input_size
408
sizes[i] = value.input_size
324
409
self.setinputsizes(*sizes)
326
411
def _param_generator(self, params):
341
426
query = smart_str(query, self.charset) % tuple(args)
342
427
self._guess_input_sizes([params])
344
return Database.Cursor.execute(self, query, self._param_generator(params))
429
return self.cursor.execute(query, self._param_generator(params))
345
430
except DatabaseError, e:
346
431
# cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400.
347
432
if e.args[0].code == 1400 and not isinstance(e, IntegrityError):
351
436
def executemany(self, query, params=None):
353
args = [(':arg%d' % i) for i in range(len(params[0]))]
438
args = [(':arg%d' % i) for i in range(len(params[0]))]
354
439
except (IndexError, TypeError):
355
# No params given, nothing to do
440
# No params given, nothing to do
357
442
# cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it
358
443
# it does want a trailing ';' but not a trailing '/'. However, these
359
444
# characters must be included in the original query in case the query
364
449
formatted = [self._format_params(i) for i in params]
365
450
self._guess_input_sizes(formatted)
367
return Database.Cursor.executemany(self, query, [self._param_generator(p) for p in formatted])
452
return self.cursor.executemany(query,
453
[self._param_generator(p) for p in formatted])
368
454
except DatabaseError, e:
369
455
# cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400.
370
456
if e.args[0].code == 1400 and not isinstance(e, IntegrityError):
374
460
def fetchone(self):
375
row = Database.Cursor.fetchone(self)
461
row = self.cursor.fetchone()
378
return tuple([to_unicode(e) for e in row])
464
return self._rowfactory(row)
380
466
def fetchmany(self, size=None):
382
468
size = self.arraysize
383
return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchmany(self, size)])
469
return tuple([self._rowfactory(r)
470
for r in self.cursor.fetchmany(size)])
385
472
def fetchall(self):
386
return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchall(self)])
473
return tuple([self._rowfactory(r)
474
for r in self.cursor.fetchall()])
476
def _rowfactory(self, row):
477
# Cast numeric values as the appropriate Python type based upon the
478
# cursor description, and convert strings to unicode.
480
for value, desc in zip(row, self.cursor.description):
481
if value is not None and desc[1] is Database.NUMBER:
482
precision, scale = desc[4:6]
485
# NUMBER column: decimal-precision floating point
486
# This will normally be an integer from a sequence,
487
# but it could be a decimal value.
489
value = Decimal(value)
493
# FLOAT column: binary-precision floating point.
494
# This comes from FloatField columns.
497
# NUMBER(p,s) column: decimal-precision fixed point.
498
# This comes from IntField and DecimalField columns.
502
value = Decimal(value)
504
# No type information. This normally comes from a
505
# mathematical expression in the SELECT list. Guess int
506
# or Decimal based on whether it has a decimal point.
507
value = Decimal(value)
510
elif desc[1] in (Database.STRING, Database.FIXED_CHAR,
511
Database.LONG_STRING):
512
value = to_unicode(value)
516
def __getattr__(self, attr):
517
if attr in self.__dict__:
518
return self.__dict__[attr]
520
return getattr(self.cursor, attr)
523
return iter(self.cursor)
388
526
def to_unicode(s):
394
532
return force_unicode(s)
397
536
def _get_sequence_reset_sql():
398
537
# TODO: colorize this SQL code with style.SQL_KEYWORD(), etc.
404
LOCK TABLE %(table)s IN SHARE MODE;
405
SELECT NVL(MAX(%(column)s), 0) INTO startvalue FROM %(table)s;
406
SELECT %(sequence)s.nextval INTO cval FROM dual;
407
cval := startvalue - cval;
409
EXECUTE IMMEDIATE 'ALTER SEQUENCE %(sequence)s MINVALUE 0 INCREMENT BY '||cval;
410
SELECT %(sequence)s.nextval INTO cval FROM dual;
411
EXECUTE IMMEDIATE 'ALTER SEQUENCE %(sequence)s INCREMENT BY 1';
543
LOCK TABLE %(table)s IN SHARE MODE;
544
SELECT NVL(MAX(%(column)s), 0) INTO startvalue FROM %(table)s;
545
SELECT "%(sequence)s".nextval INTO cval FROM dual;
546
cval := startvalue - cval;
548
EXECUTE IMMEDIATE 'ALTER SEQUENCE "%(sequence)s" MINVALUE 0 INCREMENT BY '||cval;
549
SELECT "%(sequence)s".nextval INTO cval FROM dual;
550
EXECUTE IMMEDIATE 'ALTER SEQUENCE "%(sequence)s" INCREMENT BY 1';
417
557
def get_sequence_name(table):
418
558
name_length = DatabaseOperations().max_name_length() - 3
419
559
return '%s_SQ' % util.truncate_name(table, name_length).upper()
421
562
def get_trigger_name(table):
422
563
name_length = DatabaseOperations().max_name_length() - 3
423
564
return '%s_TR' % util.truncate_name(table, name_length).upper()