1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
|
"""
Oracle database backend for Django.
Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/
"""
from django.conf import settings
from django.db.backends import util
try:
import cx_Oracle as Database
except ImportError, e:
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured, "Error loading cx_Oracle module: %s" % e
DatabaseError = Database.Error
try:
# Only exists in Python 2.4+
from threading import local
except ImportError:
# Import copy of _thread_local.py from Python 2.4
from django.utils._threading_local import local
class DatabaseWrapper(local):
def __init__(self, **kwargs):
self.connection = None
self.queries = []
self.options = kwargs
def _valid_connection(self):
return self.connection is not None
def cursor(self):
if not self._valid_connection():
if len(settings.DATABASE_HOST.strip()) == 0:
settings.DATABASE_HOST = 'localhost'
if len(settings.DATABASE_PORT.strip()) != 0:
dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME)
self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options)
else:
conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
self.connection = Database.connect(conn_string, **self.options)
cursor = FormatStylePlaceholderCursor(self.connection)
# default arraysize of 1 is highly sub-optimal
cursor.arraysize = 256
# set oracle date to ansi date format
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'")
cursor.execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
return cursor
def _commit(self):
if self.connection is not None:
self.connection.commit()
def _rollback(self):
if self.connection is not None:
try:
self.connection.rollback()
except Database.NotSupportedError:
pass
def close(self):
if self.connection is not None:
self.connection.close()
self.connection = None
allows_group_by_ordinal = False
allows_unique_and_pk = False # Suppress UNIQUE/PK for Oracle (ORA-02259)
needs_datetime_string_cast = False
supports_constraints = True
uses_case_insensitive_names = True
class FormatStylePlaceholderCursor(Database.Cursor):
"""
Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" style.
This fixes it -- but note that if you want to use a literal "%s" in a query,
you'll need to use "%%s".
"""
def _rewrite_args(self, query, params=None):
if params is None:
params = []
else:
# cx_Oracle can't handle unicode parameters, so cast to str for now
for i, param in enumerate(params):
if type(param) == unicode:
try:
params[i] = param.encode('utf-8')
except UnicodeError:
params[i] = str(param)
args = [(':arg%d' % i) for i in range(len(params))]
query = query % tuple(args)
# cx_Oracle cannot execute a query with the closing ';'
if query.endswith(';'):
query = query[:-1]
return query, params
def execute(self, query, params=None):
query, params = self._rewrite_args(query, params)
return Database.Cursor.execute(self, query, params)
def executemany(self, query, params=None):
query, params = self._rewrite_args(query, params)
return Database.Cursor.executemany(self, query, params)
def quote_name(name):
# Oracle requires that quoted names be uppercase.
name = name.upper()
if not name.startswith('"') and not name.endswith('"'):
name = '"%s"' % util.truncate_name(name.upper(), get_max_name_length())
return name
dictfetchone = util.dictfetchone
dictfetchmany = util.dictfetchmany
dictfetchall = util.dictfetchall
def get_last_insert_id(cursor, table_name, pk_name):
sq_name = util.truncate_name(table_name, get_max_name_length()-3)
cursor.execute('SELECT %s_sq.currval FROM dual' % sq_name)
return cursor.fetchone()[0]
def get_date_extract_sql(lookup_type, table_name):
# lookup_type is 'year', 'month', 'day'
# http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163
return "EXTRACT(%s FROM %s)" % (lookup_type, table_name)
def get_date_trunc_sql(lookup_type, field_name):
# lookup_type is 'year', 'month', 'day'
# Oracle uses TRUNC() for both dates and numbers.
# http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions155a.htm#SQLRF06151
if lookup_type == 'day':
sql = 'TRUNC(%s)' % (field_name,)
else:
sql = "TRUNC(%s, '%s')" % (field_name, lookup_type)
return sql
def get_datetime_cast_sql():
return "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS')"
def get_limit_offset_sql(limit, offset=None):
# Limits and offset are too complicated to be handled here.
# Instead, they are handled in django/db/backends/oracle/query.py.
raise NotImplementedError
def get_random_function_sql():
return "DBMS_RANDOM.RANDOM"
def get_deferrable_sql():
return " DEFERRABLE INITIALLY DEFERRED"
def get_fulltext_search_sql(field_name):
raise NotImplementedError
def get_drop_foreignkey_sql():
return "DROP CONSTRAINT"
def get_pk_default_value():
return "DEFAULT"
def get_max_name_length():
return 30
def get_autoinc_sql(table):
# To simulate auto-incrementing primary keys in Oracle, we have to
# create a sequence and a trigger.
name_length = get_max_name_length() - 3
sq_name = '%s_sq' % util.truncate_name(table, name_length)
tr_name = '%s_tr' % util.truncate_name(table, name_length)
sequence_sql = 'CREATE SEQUENCE %s;' % sq_name
trigger_sql = """CREATE OR REPLACE TRIGGER %s
BEFORE INSERT ON %s
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
SELECT %s.nextval INTO :new.id FROM dual;
END;\n""" % (tr_name, quote_name(table), sq_name)
return sequence_sql, trigger_sql
def get_sql_flush(style, tables, sequences):
"""Return a list of SQL statements required to remove all data from
all tables in the database (without actually removing the tables
themselves) and put the database in an empty 'initial' state
"""
# Return a list of 'TRUNCATE x;', 'TRUNCATE y;',
# 'TRUNCATE z;'... style SQL statements
if tables:
# Oracle does support TRUNCATE, but it seems to get us into
# FK referential trouble, whereas DELETE FROM table works.
sql = ['%s %s %s;' % \
(style.SQL_KEYWORD('DELETE'),
style.SQL_KEYWORD('FROM'),
style.SQL_FIELD(quote_name(table))
) for table in tables]
# You can't ALTER SEQUENCE back to 1 in Oracle. You must DROP and
# CREATE the sequence.
# What? You got something better to do than marching up and
# down the square?
for sequence_info in sequences:
table_name = sequence_info['table']
column_name = sequence_info['column']
if column_name and len(column_name):
# sequence name in this case will be <table>_<column>_seq
seq_name = '%s_%s_seq' % (table_name, column_name)
else:
# sequence name in this case will be <table>_id_seq
seq_name = '%s_id_seq' % table_name
sql.append('%s %s %s;' % \
(style.SQL_KEYWORD('DROP'),
style.SQL_KEYWORD('SEQUENCE'),
style.SQL_FIELD(seq_name))
)
sql.append('%s %s %s;' % \
(style.SQL_KEYWORD('CREATE'),
style.SQL_KEYWORD('SEQUENCE'),
style.SQL_FIELD(seq_name))
)
return sql
else:
return []
def get_drop_sequence(table):
name_length = get_max_name_length() - 3
sq_name = '%s_sq' % util.truncate_name(table, name_length)
drop_sequence_sql = 'DROP SEQUENCE %s;' % sq_name
return drop_sequence_sql
OPERATOR_MAPPING = {
'exact': '= %s',
'iexact': "LIKE %s ESCAPE '\\'",
'contains': "LIKE %s ESCAPE '\\'",
'icontains': "LIKE LOWER(%s) ESCAPE '\\'",
'gt': '> %s',
'gte': '>= %s',
'lt': '< %s',
'lte': '<= %s',
'startswith': "LIKE %s ESCAPE '\\'",
'endswith': "LIKE %s ESCAPE '\\'",
'istartswith': "LIKE %s ESCAPE '\\'",
'iendswith': "LIKE %s ESCAPE '\\'",
}
|
