4
4
Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/
7
from django.db.backends import util
11
# Oracle takes client-side character set encoding from the environment.
12
os.environ['NLS_LANG'] = '.UTF8'
9
14
import cx_Oracle as Database
10
15
except ImportError, e:
11
16
from django.core.exceptions import ImproperlyConfigured
12
raise ImproperlyConfigured, "Error loading cx_Oracle module: %s" % e
14
DatabaseError = Database.Error
17
# Only exists in Python 2.4+
18
from threading import local
20
# Import copy of _thread_local.py from Python 2.4
21
from django.utils._threading_local import local
23
class DatabaseWrapper(local):
24
def __init__(self, **kwargs):
25
self.connection = None
17
raise ImproperlyConfigured("Error loading cx_Oracle module: %s" % e)
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
26
DatabaseError = Database.DatabaseError
27
IntegrityError = Database.IntegrityError
30
class DatabaseFeatures(BaseDatabaseFeatures):
31
empty_fetchmany_value = ()
32
needs_datetime_string_cast = False
33
uses_custom_query_class = True
34
interprets_empty_strings_as_nulls = True
37
class DatabaseOperations(BaseDatabaseOperations):
38
def autoinc_sql(self, table, column):
39
# To simulate auto-incrementing primary keys in Oracle, we have to
40
# create a sequence and a trigger.
41
sq_name = get_sequence_name(table)
42
tr_name = get_trigger_name(table)
43
tbl_name = self.quote_name(table)
44
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';
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;
66
return sequence_sql, trigger_sql
68
def date_extract_sql(self, lookup_type, field_name):
69
# 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)
72
def date_trunc_sql(self, lookup_type, field_name):
73
# Oracle uses TRUNC() for both dates and numbers.
74
# http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions155a.htm#SQLRF06151
75
if lookup_type == 'day':
76
sql = 'TRUNC(%s)' % field_name
78
sql = "TRUNC(%s, '%s')" % (field_name, lookup_type)
81
def datetime_cast_sql(self):
82
return "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')"
84
def deferrable_sql(self):
85
return " DEFERRABLE INITIALLY DEFERRED"
87
def drop_sequence_sql(self, table):
88
return "DROP SEQUENCE %s;" % self.quote_name(get_sequence_name(table))
90
def field_cast_sql(self, db_type):
91
if db_type and db_type.endswith('LOB'):
92
return "DBMS_LOB.SUBSTR(%s)"
96
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)
99
return cursor.fetchone()[0]
101
def lookup_cast(self, lookup_type):
102
if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'):
106
def max_name_length(self):
109
def prep_for_iexact_query(self, x):
112
def query_class(self, DefaultQueryClass):
113
return query.query_class(DefaultQueryClass, Database)
115
def quote_name(self, name):
116
# SQL92 requires delimited (quoted) names to be case-sensitive. When
117
# not quoted, Oracle has case-insensitive behavior for identifiers, but
118
# always defaults to uppercase.
119
# We simplify things by making Oracle identifiers always uppercase.
120
if not name.startswith('"') and not name.endswith('"'):
121
name = '"%s"' % util.truncate_name(name.upper(), self.max_name_length())
124
def random_function_sql(self):
125
return "DBMS_RANDOM.RANDOM"
127
def regex_lookup_9(self, lookup_type):
128
raise NotImplementedError("Regexes are not supported in Oracle before version 10g.")
130
def regex_lookup_10(self, lookup_type):
131
if lookup_type == 'regex':
135
return 'REGEXP_LIKE(%%s, %%s, %s)' % match_option
137
def regex_lookup(self, lookup_type):
138
# If regex_lookup is called before it's been initialized, then create
139
# a cursor to initialize it and recur.
140
from django.db import connection
142
return connection.ops.regex_lookup(lookup_type)
144
def sql_flush(self, style, tables, sequences):
145
# Return a list of 'TRUNCATE x;', 'TRUNCATE y;',
146
# 'TRUNCATE z;'... style SQL statements
148
# Oracle does support TRUNCATE, but it seems to get us into
149
# FK referential trouble, whereas DELETE FROM table works.
150
sql = ['%s %s %s;' % \
151
(style.SQL_KEYWORD('DELETE'),
152
style.SQL_KEYWORD('FROM'),
153
style.SQL_FIELD(self.quote_name(table))
154
) for table in tables]
155
# Since we've just deleted all the rows, running our sequence
156
# ALTER code will reset the sequence to 0.
157
for sequence_info in sequences:
158
sequence_name = get_sequence_name(sequence_info['table'])
159
table_name = self.quote_name(sequence_info['table'])
160
column_name = self.quote_name(sequence_info['column'] or 'id')
161
query = _get_sequence_reset_sql() % {'sequence': sequence_name,
163
'column': column_name}
169
def sequence_reset_sql(self, style, model_list):
170
from django.db import models
172
query = _get_sequence_reset_sql()
173
for model in model_list:
174
for f in model._meta.local_fields:
175
if isinstance(f, models.AutoField):
176
table_name = self.quote_name(model._meta.db_table)
177
sequence_name = get_sequence_name(model._meta.db_table)
178
column_name = self.quote_name(f.column)
179
output.append(query % {'sequence': sequence_name,
181
'column': column_name})
182
break # Only one AutoField is allowed per model, so don't bother continuing.
183
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})
192
def start_transaction_sql(self):
195
def tablespace_sql(self, tablespace, inline=False):
196
return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""), self.quote_name(tablespace))
198
def value_to_db_time(self, value):
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)
206
def year_lookup_bounds_for_date_field(self, value):
209
return [first % value, second % value]
212
class DatabaseWrapper(BaseDatabaseWrapper):
216
'iexact': '= UPPER(%s)',
217
'contains': "LIKEC %s ESCAPE '\\'",
218
'icontains': "LIKEC UPPER(%s) ESCAPE '\\'",
223
'startswith': "LIKEC %s ESCAPE '\\'",
224
'endswith': "LIKEC %s ESCAPE '\\'",
225
'istartswith': "LIKEC UPPER(%s) ESCAPE '\\'",
226
'iendswith': "LIKEC UPPER(%s) ESCAPE '\\'",
228
oracle_version = None
230
def __init__(self, *args, **kwargs):
231
super(DatabaseWrapper, self).__init__(*args, **kwargs)
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()
29
240
def _valid_connection(self):
30
241
return self.connection is not None
33
from django.conf import settings
243
def _cursor(self, settings):
34
245
if not self._valid_connection():
35
246
if len(settings.DATABASE_HOST.strip()) == 0:
36
247
settings.DATABASE_HOST = 'localhost'
41
252
conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
42
253
self.connection = Database.connect(conn_string, **self.options)
43
return FormatStylePlaceholderCursor(self.connection)
46
if self.connection is not None:
47
self.connection.commit()
50
if self.connection is not None:
52
self.connection.rollback()
53
except Database.NotSupportedError:
57
if self.connection is not None:
58
self.connection.close()
59
self.connection = None
61
supports_constraints = True
254
cursor = FormatStylePlaceholderCursor(self.connection)
255
# 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'")
260
self.oracle_version = int(self.connection.version.split('.')[0])
261
# There's no way for the DatabaseOperations class to know the
262
# currently active Oracle version, so we do some setups here.
263
# TODO: Multi-db support will need a better solution (a way to
264
# communicate the current version).
265
if self.oracle_version <= 9:
266
self.ops.regex_lookup = self.ops.regex_lookup_9
268
self.ops.regex_lookup = self.ops.regex_lookup_10
272
self.connection.stmtcachesize = 20
274
# Django docs specify cx_Oracle version 4.3.1 or higher, but
275
# stmtcachesize is available only in 4.3.2 and up.
278
cursor = FormatStylePlaceholderCursor(self.connection)
279
# Default arraysize of 1 is highly sub-optimal.
280
cursor.arraysize = 100
284
class OracleParam(object):
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
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
302
self.input_size = None
63
305
class FormatStylePlaceholderCursor(Database.Cursor):
65
Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" style.
66
This fixes it -- but note that if you want to use a literal "%s" in a query,
67
you'll need to use "%%s".
307
Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var"
308
style. This fixes it -- but note that if you want to use a literal "%s" in
309
a query, you'll need to use "%%s".
311
We also do automatic conversion between Unicode on the Python side and
312
UTF-8 -- for talking to Oracle -- in here.
316
def _format_params(self, params):
317
if isinstance(params, dict):
319
for key, value in params.items():
320
result[smart_str(key, self.charset)] = OracleParam(param, self.charset)
323
return tuple([OracleParam(p, self.charset, True) for p in params])
325
def _guess_input_sizes(self, params_list):
326
if isinstance(params_list[0], dict):
328
iterators = [params.iteritems() for params in params_list]
330
sizes = [None] * len(params_list[0])
331
iterators = [enumerate(params) for params in params_list]
332
for iterator in iterators:
333
for key, value in iterator:
334
if value.input_size: sizes[key] = value.input_size
335
if isinstance(sizes, dict):
336
self.setinputsizes(**sizes)
338
self.setinputsizes(*sizes)
340
def _param_generator(self, params):
341
if isinstance(params, dict):
342
return dict([(k, p.smart_str) for k, p in params.iteritems()])
344
return [p.smart_str for p in params]
69
346
def execute(self, query, params=None):
70
if params is None: params = []
71
query = self.convert_arguments(query, len(params))
72
return Database.Cursor.execute(self, query, params)
350
params = self._format_params(params)
351
args = [(':arg%d' % i) for i in range(len(params))]
352
# cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it
353
# it does want a trailing ';' but not a trailing '/'. However, these
354
# characters must be included in the original query in case the query
355
# is being passed to SQL*Plus.
356
if query.endswith(';') or query.endswith('/'):
358
query = smart_str(query, self.charset) % tuple(args)
359
self._guess_input_sizes([params])
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.message.code == 1400 and type(e) != IntegrityError:
365
e = IntegrityError(e.message)
74
368
def executemany(self, query, params=None):
75
if params is None: params = []
76
query = self.convert_arguments(query, len(params[0]))
77
return Database.Cursor.executemany(self, query, params)
79
def convert_arguments(self, query, num_params):
80
# replace occurances of "%s" with ":arg" - Oracle requires colons for parameter placeholders.
81
args = [':arg' for i in range(num_params)]
82
return query % tuple(args)
87
dictfetchone = util.dictfetchone
88
dictfetchmany = util.dictfetchmany
89
dictfetchall = util.dictfetchall
91
def get_last_insert_id(cursor, table_name, pk_name):
92
query = "SELECT %s_sq.currval from dual" % table_name
94
return cursor.fetchone()[0]
96
def get_date_extract_sql(lookup_type, table_name):
97
# lookup_type is 'year', 'month', 'day'
98
# http://www.psoug.org/reference/date_func.html
99
return "EXTRACT(%s FROM %s)" % (lookup_type, table_name)
101
def get_date_trunc_sql(lookup_type, field_name):
102
return "EXTRACT(%s FROM TRUNC(%s))" % (lookup_type, field_name)
104
def get_limit_offset_sql(limit, offset=None):
105
# Limits and offset are too complicated to be handled here.
106
# Instead, they are handled in django/db/query.py.
109
def get_random_function_sql():
110
return "DBMS_RANDOM.RANDOM"
112
def get_deferrable_sql():
113
return " DEFERRABLE INITIALLY DEFERRED"
115
def get_fulltext_search_sql(field_name):
116
raise NotImplementedError
118
def get_drop_foreignkey_sql():
119
return "DROP FOREIGN KEY"
121
def get_pk_default_value():
124
def get_sql_flush(style, tables, sequences):
125
"""Return a list of SQL statements required to remove all data from
126
all tables in the database (without actually removing the tables
127
themselves) and put the database in an empty 'initial' state
129
# Return a list of 'TRUNCATE x;', 'TRUNCATE y;', 'TRUNCATE z;'... style SQL statements
130
# TODO - SQL not actually tested against Oracle yet!
131
# TODO - autoincrement indices reset required? See other get_sql_flush() implementations
133
(style.SQL_KEYWORD('TRUNCATE'),
134
style.SQL_FIELD(quote_name(table))
135
) for table in tables]
141
'contains': 'LIKE %s',
142
'icontains': 'LIKE %s',
147
'startswith': 'LIKE %s',
148
'endswith': 'LIKE %s',
149
'istartswith': 'LIKE %s',
150
'iendswith': 'LIKE %s',
370
args = [(':arg%d' % i) for i in range(len(params[0]))]
371
except (IndexError, TypeError):
372
# No params given, nothing to do
374
# cx_Oracle wants no trailing ';' for SQL statements. For PL/SQL, it
375
# it does want a trailing ';' but not a trailing '/'. However, these
376
# characters must be included in the original query in case the query
377
# is being passed to SQL*Plus.
378
if query.endswith(';') or query.endswith('/'):
380
query = smart_str(query, self.charset) % tuple(args)
381
formatted = [self._format_params(i) for i in params]
382
self._guess_input_sizes(formatted)
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.message.code == 1400 and type(e) != IntegrityError:
388
e = IntegrityError(e.message)
392
row = Database.Cursor.fetchone(self)
395
return tuple([to_unicode(e) for e in row])
397
def fetchmany(self, size=None):
399
size = self.arraysize
400
return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchmany(self, size)])
403
return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchall(self)])
407
Convert strings to Unicode objects (and return all other data types
410
if isinstance(s, basestring):
411
return force_unicode(s)
414
def _get_sequence_reset_sql():
415
# TODO: colorize this SQL code with style.SQL_KEYWORD(), etc.
421
LOCK TABLE %(table)s IN SHARE MODE;
422
SELECT NVL(MAX(%(column)s), 0) INTO startvalue FROM %(table)s;
423
SELECT %(sequence)s.nextval INTO cval FROM dual;
424
cval := startvalue - cval;
426
EXECUTE IMMEDIATE 'ALTER SEQUENCE %(sequence)s MINVALUE 0 INCREMENT BY '||cval;
427
SELECT %(sequence)s.nextval INTO cval FROM dual;
428
EXECUTE IMMEDIATE 'ALTER SEQUENCE %(sequence)s INCREMENT BY 1';
434
def get_sequence_name(table):
435
name_length = DatabaseOperations().max_name_length() - 3
436
return '%s_SQ' % util.truncate_name(table, name_length).upper()
438
def get_trigger_name(table):
439
name_length = DatabaseOperations().max_name_length() - 3
440
return '%s_TR' % util.truncate_name(table, name_length).upper()
added
removed
django/db/backends/oracle/base.py
