| 1 |
import datetime |
| 2 |
import uuid |
| 3 |
from functools import lru_cache |
| 4 |
from itertools import chain |
| 5 |
|
| 6 |
from django.conf import settings |
| 7 |
from django.db import DatabaseError, NotSupportedError |
| 8 |
from django.db.backends.base.operations import BaseDatabaseOperations |
| 9 |
from django.db.backends.utils import split_tzname_delta, strip_quotes, truncate_name |
| 10 |
from django.db.models import ( |
| 11 |
AutoField, |
| 12 |
CompositePrimaryKey, |
| 13 |
Exists, |
| 14 |
ExpressionWrapper, |
| 15 |
Lookup, |
| 16 |
) |
| 17 |
from django.db.models.expressions import RawSQL |
| 18 |
from django.db.models.sql.where import WhereNode |
| 19 |
from django.utils import timezone |
| 20 |
from django.utils.encoding import force_bytes, force_str |
| 21 |
from django.utils.functional import cached_property |
| 22 |
from django.utils.regex_helper import _lazy_re_compile |
| 23 |
|
| 24 |
from .base import Database |
| 25 |
from .utils import BulkInsertMapper, InsertVar, Oracle_datetime |
| 26 |
|
| 27 |
|
| 28 |
class DatabaseOperations(BaseDatabaseOperations): |
| 29 |
# Oracle uses NUMBER(5), NUMBER(11), and NUMBER(19) for integer fields. |
| 30 |
# SmallIntegerField uses NUMBER(11) instead of NUMBER(5), which is used by |
| 31 |
# SmallAutoField, to preserve backward compatibility. |
| 32 |
integer_field_ranges = { |
| 33 |
"SmallIntegerField": (-99999999999, 99999999999), |
| 34 |
"IntegerField": (-99999999999, 99999999999), |
| 35 |
"BigIntegerField": (-9999999999999999999, 9999999999999999999), |
| 36 |
"PositiveBigIntegerField": (0, 9999999999999999999), |
| 37 |
"PositiveSmallIntegerField": (0, 99999999999), |
| 38 |
"PositiveIntegerField": (0, 99999999999), |
| 39 |
"SmallAutoField": (-99999, 99999), |
| 40 |
"AutoField": (-99999999999, 99999999999), |
| 41 |
"BigAutoField": (-9999999999999999999, 9999999999999999999), |
| 42 |
} |
| 43 |
set_operators = {**BaseDatabaseOperations.set_operators, "difference": "MINUS"} |
| 44 |
|
| 45 |
# TODO: colorize this SQL code with style.SQL_KEYWORD(), etc. |
| 46 |
_sequence_reset_sql = """ |
| 47 |
DECLARE |
| 48 |
table_value integer; |
| 49 |
seq_value integer; |
| 50 |
seq_name user_tab_identity_cols.sequence_name%%TYPE; |
| 51 |
BEGIN |
| 52 |
BEGIN |
| 53 |
SELECT sequence_name INTO seq_name FROM user_tab_identity_cols |
| 54 |
WHERE table_name = '%(table_name)s' AND |
| 55 |
column_name = '%(column_name)s'; |
| 56 |
EXCEPTION WHEN NO_DATA_FOUND THEN |
| 57 |
seq_name := '%(no_autofield_sequence_name)s'; |
| 58 |
END; |
| 59 |
|
| 60 |
SELECT NVL(MAX(%(column)s), 0) INTO table_value FROM %(table)s; |
| 61 |
SELECT NVL(last_number - cache_size, 0) INTO seq_value FROM user_sequences |
| 62 |
WHERE sequence_name = seq_name; |
| 63 |
WHILE table_value > seq_value LOOP |
| 64 |
EXECUTE IMMEDIATE 'SELECT "'||seq_name||'".nextval%(suffix)s' |
| 65 |
INTO seq_value; |
| 66 |
END LOOP; |
| 67 |
END; |
| 68 |
/""" |
| 69 |
|
| 70 |
# Oracle doesn't support string without precision; use the max string size. |
| 71 |
cast_char_field_without_max_length = "NVARCHAR2(2000)" |
| 72 |
cast_data_types = { |
| 73 |
"AutoField": "NUMBER(11)", |
| 74 |
"BigAutoField": "NUMBER(19)", |
| 75 |
"SmallAutoField": "NUMBER(5)", |
| 76 |
"TextField": cast_char_field_without_max_length, |
| 77 |
} |
| 78 |
|
| 79 |
def cache_key_culling_sql(self): |
| 80 |
cache_key = self.quote_name("cache_key") |
| 81 |
return ( |
| 82 |
f"SELECT {cache_key} " |
| 83 |
f"FROM %s " |
| 84 |
f"ORDER BY {cache_key} OFFSET %%s ROWS FETCH FIRST 1 ROWS ONLY" |
| 85 |
) |
| 86 |
|
| 87 |
# EXTRACT format cannot be passed in parameters. |
| 88 |
_extract_format_re = _lazy_re_compile(r"[A-Z_]+") |
| 89 |
|
| 90 |
def date_extract_sql(self, lookup_type, sql, params): |
| 91 |
extract_sql = f"TO_CHAR({sql}, %s)" |
| 92 |
extract_param = None |
| 93 |
if lookup_type == "week_day": |
| 94 |
# TO_CHAR(field, 'D') returns an integer from 1-7, where 1=Sunday. |
| 95 |
extract_param = "D" |
| 96 |
elif lookup_type == "iso_week_day": |
| 97 |
extract_sql = f"TO_CHAR({sql} - 1, %s)" |
| 98 |
extract_param = "D" |
| 99 |
elif lookup_type == "week": |
| 100 |
# IW = ISO week number |
| 101 |
extract_param = "IW" |
| 102 |
elif lookup_type == "quarter": |
| 103 |
extract_param = "Q" |
| 104 |
elif lookup_type == "iso_year": |
| 105 |
extract_param = "IYYY" |
| 106 |
else: |
| 107 |
lookup_type = lookup_type.upper() |
| 108 |
if not self._extract_format_re.fullmatch(lookup_type): |
| 109 |
raise ValueError(f"Invalid loookup type: {lookup_type!r}") |
| 110 |
# https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/EXTRACT-datetime.html |
| 111 |
return f"EXTRACT({lookup_type} FROM {sql})", params |
| 112 |
return extract_sql, (*params, extract_param) |
| 113 |
|
| 114 |
def date_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 115 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 116 |
# https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html |
| 117 |
trunc_param = None |
| 118 |
if lookup_type in ("year", "month"): |
| 119 |
trunc_param = lookup_type.upper() |
| 120 |
elif lookup_type == "quarter": |
| 121 |
trunc_param = "Q" |
| 122 |
elif lookup_type == "week": |
| 123 |
trunc_param = "IW" |
| 124 |
else: |
| 125 |
return f"TRUNC({sql})", params |
| 126 |
return f"TRUNC({sql}, %s)", (*params, trunc_param) |
| 127 |
|
| 128 |
# Oracle crashes with "ORA-03113: end-of-file on communication channel" |
| 129 |
# if the time zone name is passed in parameter. Use interpolation instead. |
| 130 |
# https://groups.google.com/forum/#!msg/django-developers/zwQju7hbG78/9l934yelwfsJ |
| 131 |
# This regexp matches all time zone names from the zoneinfo database. |
| 132 |
_tzname_re = _lazy_re_compile(r"^[\w/:+-]+$") |
| 133 |
|
| 134 |
def _prepare_tzname_delta(self, tzname): |
| 135 |
tzname, sign, offset = split_tzname_delta(tzname) |
| 136 |
return f"{sign}{offset}" if offset else tzname |
| 137 |
|
| 138 |
def _convert_sql_to_tz(self, sql, params, tzname): |
| 139 |
if not (settings.USE_TZ and tzname): |
| 140 |
return sql, params |
| 141 |
if not self._tzname_re.match(tzname): |
| 142 |
raise ValueError("Invalid time zone name: %s" % tzname) |
| 143 |
# Convert from connection timezone to the local time, returning |
| 144 |
# TIMESTAMP WITH TIME ZONE and cast it back to TIMESTAMP to strip the |
| 145 |
# TIME ZONE details. |
| 146 |
if self.connection.timezone_name != tzname: |
| 147 |
from_timezone_name = self.connection.timezone_name |
| 148 |
to_timezone_name = self._prepare_tzname_delta(tzname) |
| 149 |
return ( |
| 150 |
f"CAST((FROM_TZ({sql}, '{from_timezone_name}') AT TIME ZONE " |
| 151 |
f"'{to_timezone_name}') AS TIMESTAMP)", |
| 152 |
params, |
| 153 |
) |
| 154 |
return sql, params |
| 155 |
|
| 156 |
def datetime_cast_date_sql(self, sql, params, tzname): |
| 157 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 158 |
return f"TRUNC({sql})", params |
| 159 |
|
| 160 |
def datetime_cast_time_sql(self, sql, params, tzname): |
| 161 |
# Since `TimeField` values are stored as TIMESTAMP change to the |
| 162 |
# default date and convert the field to the specified timezone. |
| 163 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 164 |
convert_datetime_sql = ( |
| 165 |
f"TO_TIMESTAMP(CONCAT('1900-01-01 ', TO_CHAR({sql}, 'HH24:MI:SS.FF')), " |
| 166 |
f"'YYYY-MM-DD HH24:MI:SS.FF')" |
| 167 |
) |
| 168 |
return ( |
| 169 |
f"CASE WHEN {sql} IS NOT NULL THEN {convert_datetime_sql} ELSE NULL END", |
| 170 |
(*params, *params), |
| 171 |
) |
| 172 |
|
| 173 |
def datetime_extract_sql(self, lookup_type, sql, params, tzname): |
| 174 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 175 |
if lookup_type == "second": |
| 176 |
# Truncate fractional seconds. |
| 177 |
return f"FLOOR(EXTRACT(SECOND FROM {sql}))", params |
| 178 |
return self.date_extract_sql(lookup_type, sql, params) |
| 179 |
|
| 180 |
def datetime_trunc_sql(self, lookup_type, sql, params, tzname): |
| 181 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 182 |
# https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html |
| 183 |
trunc_param = None |
| 184 |
if lookup_type in ("year", "month"): |
| 185 |
trunc_param = lookup_type.upper() |
| 186 |
elif lookup_type == "quarter": |
| 187 |
trunc_param = "Q" |
| 188 |
elif lookup_type == "week": |
| 189 |
trunc_param = "IW" |
| 190 |
elif lookup_type == "hour": |
| 191 |
trunc_param = "HH24" |
| 192 |
elif lookup_type == "minute": |
| 193 |
trunc_param = "MI" |
| 194 |
elif lookup_type == "day": |
| 195 |
return f"TRUNC({sql})", params |
| 196 |
else: |
| 197 |
# Cast to DATE removes sub-second precision. |
| 198 |
return f"CAST({sql} AS DATE)", params |
| 199 |
return f"TRUNC({sql}, %s)", (*params, trunc_param) |
| 200 |
|
| 201 |
def time_extract_sql(self, lookup_type, sql, params): |
| 202 |
if lookup_type == "second": |
| 203 |
# Truncate fractional seconds. |
| 204 |
return f"FLOOR(EXTRACT(SECOND FROM {sql}))", params |
| 205 |
return self.date_extract_sql(lookup_type, sql, params) |
| 206 |
|
| 207 |
def time_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 208 |
# The implementation is similar to `datetime_trunc_sql` as both |
| 209 |
# `DateTimeField` and `TimeField` are stored as TIMESTAMP where |
| 210 |
# the date part of the later is ignored. |
| 211 |
sql, params = self._convert_sql_to_tz(sql, params, tzname) |
| 212 |
trunc_param = None |
| 213 |
if lookup_type == "hour": |
| 214 |
trunc_param = "HH24" |
| 215 |
elif lookup_type == "minute": |
| 216 |
trunc_param = "MI" |
| 217 |
elif lookup_type == "second": |
| 218 |
# Cast to DATE removes sub-second precision. |
| 219 |
return f"CAST({sql} AS DATE)", params |
| 220 |
return f"TRUNC({sql}, %s)", (*params, trunc_param) |
| 221 |
|
| 222 |
def get_db_converters(self, expression): |
| 223 |
converters = super().get_db_converters(expression) |
| 224 |
internal_type = expression.output_field.get_internal_type() |
| 225 |
if internal_type in ["JSONField", "TextField"]: |
| 226 |
converters.append(self.convert_textfield_value) |
| 227 |
elif internal_type == "BinaryField": |
| 228 |
converters.append(self.convert_binaryfield_value) |
| 229 |
elif internal_type == "BooleanField": |
| 230 |
converters.append(self.convert_booleanfield_value) |
| 231 |
elif internal_type == "DateTimeField": |
| 232 |
if settings.USE_TZ: |
| 233 |
converters.append(self.convert_datetimefield_value) |
| 234 |
elif internal_type == "DateField": |
| 235 |
converters.append(self.convert_datefield_value) |
| 236 |
elif internal_type == "TimeField": |
| 237 |
converters.append(self.convert_timefield_value) |
| 238 |
elif internal_type == "UUIDField": |
| 239 |
converters.append(self.convert_uuidfield_value) |
| 240 |
# Oracle stores empty strings as null. If the field accepts the empty |
| 241 |
# string, undo this to adhere to the Django convention of using |
| 242 |
# the empty string instead of null. |
| 243 |
if expression.output_field.empty_strings_allowed: |
| 244 |
converters.append( |
| 245 |
self.convert_empty_bytes |
| 246 |
if internal_type == "BinaryField" |
| 247 |
else self.convert_empty_string |
| 248 |
) |
| 249 |
return converters |
| 250 |
|
| 251 |
def convert_textfield_value(self, value, expression, connection): |
| 252 |
if isinstance(value, Database.LOB): |
| 253 |
value = value.read() |
| 254 |
return value |
| 255 |
|
| 256 |
def convert_binaryfield_value(self, value, expression, connection): |
| 257 |
if isinstance(value, Database.LOB): |
| 258 |
value = force_bytes(value.read()) |
| 259 |
return value |
| 260 |
|
| 261 |
def convert_booleanfield_value(self, value, expression, connection): |
| 262 |
if value in (0, 1): |
| 263 |
value = bool(value) |
| 264 |
return value |
| 265 |
|
| 266 |
# oracledb always returns datetime.datetime objects for |
| 267 |
# DATE and TIMESTAMP columns, but Django wants to see a |
| 268 |
# python datetime.date, .time, or .datetime. |
| 269 |
|
| 270 |
def convert_datetimefield_value(self, value, expression, connection): |
| 271 |
if value is not None: |
| 272 |
value = timezone.make_aware(value, self.connection.timezone) |
| 273 |
return value |
| 274 |
|
| 275 |
def convert_datefield_value(self, value, expression, connection): |
| 276 |
if isinstance(value, datetime.datetime): |
| 277 |
value = value.date() |
| 278 |
return value |
| 279 |
|
| 280 |
def convert_timefield_value(self, value, expression, connection): |
| 281 |
if isinstance(value, datetime.datetime): |
| 282 |
value = value.time() |
| 283 |
return value |
| 284 |
|
| 285 |
def convert_uuidfield_value(self, value, expression, connection): |
| 286 |
if value is not None: |
| 287 |
value = uuid.UUID(value) |
| 288 |
return value |
| 289 |
|
| 290 |
@staticmethod |
| 291 |
def convert_empty_string(value, expression, connection): |
| 292 |
return "" if value is None else value |
| 293 |
|
| 294 |
@staticmethod |
| 295 |
def convert_empty_bytes(value, expression, connection): |
| 296 |
return b"" if value is None else value |
| 297 |
|
| 298 |
def deferrable_sql(self): |
| 299 |
return " DEFERRABLE INITIALLY DEFERRED" |
| 300 |
|
| 301 |
def fetch_returned_insert_columns(self, cursor, returning_params): |
| 302 |
columns = [] |
| 303 |
for param in returning_params: |
| 304 |
value = param.get_value() |
| 305 |
# Can be removed when cx_Oracle is no longer supported and |
| 306 |
# python-oracle 2.1.2 becomes the minimum supported version. |
| 307 |
if value == []: |
| 308 |
raise DatabaseError( |
| 309 |
"The database did not return a new row id. Probably " |
| 310 |
'"ORA-1403: no data found" was raised internally but was ' |
| 311 |
"hidden by the Oracle OCI library (see " |
| 312 |
"https://code.djangoproject.com/ticket/28859)." |
| 313 |
) |
| 314 |
columns.append(value[0]) |
| 315 |
return tuple(columns) |
| 316 |
|
| 317 |
def no_limit_value(self): |
| 318 |
return None |
| 319 |
|
| 320 |
def limit_offset_sql(self, low_mark, high_mark): |
| 321 |
fetch, offset = self._get_limit_offset_params(low_mark, high_mark) |
| 322 |
return " ".join( |
| 323 |
sql |
| 324 |
for sql in ( |
| 325 |
("OFFSET %d ROWS" % offset) if offset else None, |
| 326 |
("FETCH FIRST %d ROWS ONLY" % fetch) if fetch else None, |
| 327 |
) |
| 328 |
if sql |
| 329 |
) |
| 330 |
|
| 331 |
def last_executed_query(self, cursor, sql, params): |
| 332 |
# https://python-oracledb.readthedocs.io/en/latest/api_manual/cursor.html#Cursor.statement |
| 333 |
# The DB API definition does not define this attribute. |
| 334 |
statement = cursor.statement |
| 335 |
# Unlike Psycopg's `query` and MySQLdb`'s `_executed`, oracledb's |
| 336 |
# `statement` doesn't contain the query parameters. Substitute |
| 337 |
# parameters manually. |
| 338 |
if statement and params: |
| 339 |
if isinstance(params, (tuple, list)): |
| 340 |
params = { |
| 341 |
f":arg{i}": param for i, param in enumerate(dict.fromkeys(params)) |
| 342 |
} |
| 343 |
elif isinstance(params, dict): |
| 344 |
params = {f":{key}": val for (key, val) in params.items()} |
| 345 |
for key in sorted(params, key=len, reverse=True): |
| 346 |
statement = statement.replace( |
| 347 |
key, force_str(params[key], errors="replace") |
| 348 |
) |
| 349 |
return statement |
| 350 |
|
| 351 |
def last_insert_id(self, cursor, table_name, pk_name): |
| 352 |
sq_name = self._get_sequence_name(cursor, strip_quotes(table_name), pk_name) |
| 353 |
cursor.execute('"%s".currval' % sq_name) |
| 354 |
return cursor.fetchone()[0] |
| 355 |
|
| 356 |
def lookup_cast(self, lookup_type, internal_type=None): |
| 357 |
if lookup_type in ("iexact", "icontains", "istartswith", "iendswith"): |
| 358 |
return "UPPER(%s)" |
| 359 |
if lookup_type != "isnull" and internal_type in ( |
| 360 |
"BinaryField", |
| 361 |
"TextField", |
| 362 |
): |
| 363 |
return "DBMS_LOB.SUBSTR(%s)" |
| 364 |
return "%s" |
| 365 |
|
| 366 |
def max_in_list_size(self): |
| 367 |
return 1000 |
| 368 |
|
| 369 |
def max_name_length(self): |
| 370 |
return 30 |
| 371 |
|
| 372 |
def pk_default_value(self): |
| 373 |
return "NULL" |
| 374 |
|
| 375 |
def prep_for_iexact_query(self, x): |
| 376 |
return x |
| 377 |
|
| 378 |
def process_clob(self, value): |
| 379 |
if value is None: |
| 380 |
return "" |
| 381 |
return value.read() |
| 382 |
|
| 383 |
def quote_name(self, name): |
| 384 |
# SQL92 requires delimited (quoted) names to be case-sensitive. When |
| 385 |
# not quoted, Oracle has case-insensitive behavior for identifiers, but |
| 386 |
# always defaults to uppercase. |
| 387 |
# We simplify things by making Oracle identifiers always uppercase. |
| 388 |
if not name.startswith('"') and not name.endswith('"'): |
| 389 |
name = '"%s"' % truncate_name(name, self.max_name_length()) |
| 390 |
# Oracle puts the query text into a (query % args) construct, so % signs |
| 391 |
# in names need to be escaped. The '%%' will be collapsed back to '%' at |
| 392 |
# that stage so we aren't really making the name longer here. |
| 393 |
name = name.replace("%", "%%") |
| 394 |
return name.upper() |
| 395 |
|
| 396 |
def regex_lookup(self, lookup_type): |
| 397 |
if lookup_type == "regex": |
| 398 |
match_option = "'c'" |
| 399 |
else: |
| 400 |
match_option = "'i'" |
| 401 |
return "REGEXP_LIKE(%%s, %%s, %s)" % match_option |
| 402 |
|
| 403 |
def return_insert_columns(self, fields): |
| 404 |
if not fields: |
| 405 |
return "", () |
| 406 |
field_names = [] |
| 407 |
params = [] |
| 408 |
for field in fields: |
| 409 |
field_names.append( |
| 410 |
"%s.%s" |
| 411 |
% ( |
| 412 |
self.quote_name(field.model._meta.db_table), |
| 413 |
self.quote_name(field.column), |
| 414 |
) |
| 415 |
) |
| 416 |
params.append(InsertVar(field)) |
| 417 |
return "RETURNING %s INTO %s" % ( |
| 418 |
", ".join(field_names), |
| 419 |
", ".join(["%s"] * len(params)), |
| 420 |
), tuple(params) |
| 421 |
|
| 422 |
def __foreign_key_constraints(self, table_name, recursive): |
| 423 |
with self.connection.cursor() as cursor: |
| 424 |
if recursive: |
| 425 |
cursor.execute( |
| 426 |
""" |
| 427 |
SELECT |
| 428 |
user_tables.table_name, rcons.constraint_name |
| 429 |
FROM |
| 430 |
user_tables |
| 431 |
JOIN |
| 432 |
user_constraints cons |
| 433 |
ON (user_tables.table_name = cons.table_name |
| 434 |
AND cons.constraint_type = ANY('P', 'U')) |
| 435 |
LEFT JOIN |
| 436 |
user_constraints rcons |
| 437 |
ON (user_tables.table_name = rcons.table_name |
| 438 |
AND rcons.constraint_type = 'R') |
| 439 |
START WITH user_tables.table_name = UPPER(%s) |
| 440 |
CONNECT BY |
| 441 |
NOCYCLE PRIOR cons.constraint_name = rcons.r_constraint_name |
| 442 |
GROUP BY |
| 443 |
user_tables.table_name, rcons.constraint_name |
| 444 |
HAVING user_tables.table_name != UPPER(%s) |
| 445 |
ORDER BY MAX(level) DESC |
| 446 |
""", |
| 447 |
(table_name, table_name), |
| 448 |
) |
| 449 |
else: |
| 450 |
cursor.execute( |
| 451 |
""" |
| 452 |
SELECT |
| 453 |
cons.table_name, cons.constraint_name |
| 454 |
FROM |
| 455 |
user_constraints cons |
| 456 |
WHERE |
| 457 |
cons.constraint_type = 'R' |
| 458 |
AND cons.table_name = UPPER(%s) |
| 459 |
""", |
| 460 |
(table_name,), |
| 461 |
) |
| 462 |
return cursor.fetchall() |
| 463 |
|
| 464 |
@cached_property |
| 465 |
def _foreign_key_constraints(self): |
| 466 |
# 512 is large enough to fit the ~330 tables (as of this writing) in |
| 467 |
# Django's test suite. |
| 468 |
return lru_cache(maxsize=512)(self.__foreign_key_constraints) |
| 469 |
|
| 470 |
def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False): |
| 471 |
if not tables: |
| 472 |
return [] |
| 473 |
|
| 474 |
truncated_tables = {table.upper() for table in tables} |
| 475 |
constraints = set() |
| 476 |
# Oracle's TRUNCATE CASCADE only works with ON DELETE CASCADE foreign |
| 477 |
# keys which Django doesn't define. Emulate the PostgreSQL behavior |
| 478 |
# which truncates all dependent tables by manually retrieving all |
| 479 |
# foreign key constraints and resolving dependencies. |
| 480 |
for table in tables: |
| 481 |
for foreign_table, constraint in self._foreign_key_constraints( |
| 482 |
table, recursive=allow_cascade |
| 483 |
): |
| 484 |
if allow_cascade: |
| 485 |
truncated_tables.add(foreign_table) |
| 486 |
constraints.add((foreign_table, constraint)) |
| 487 |
sql = ( |
| 488 |
[ |
| 489 |
"%s %s %s %s %s %s %s %s;" |
| 490 |
% ( |
| 491 |
style.SQL_KEYWORD("ALTER"), |
| 492 |
style.SQL_KEYWORD("TABLE"), |
| 493 |
style.SQL_FIELD(self.quote_name(table)), |
| 494 |
style.SQL_KEYWORD("DISABLE"), |
| 495 |
style.SQL_KEYWORD("CONSTRAINT"), |
| 496 |
style.SQL_FIELD(self.quote_name(constraint)), |
| 497 |
style.SQL_KEYWORD("KEEP"), |
| 498 |
style.SQL_KEYWORD("INDEX"), |
| 499 |
) |
| 500 |
for table, constraint in constraints |
| 501 |
] |
| 502 |
+ [ |
| 503 |
"%s %s %s;" |
| 504 |
% ( |
| 505 |
style.SQL_KEYWORD("TRUNCATE"), |
| 506 |
style.SQL_KEYWORD("TABLE"), |
| 507 |
style.SQL_FIELD(self.quote_name(table)), |
| 508 |
) |
| 509 |
for table in truncated_tables |
| 510 |
] |
| 511 |
+ [ |
| 512 |
"%s %s %s %s %s %s;" |
| 513 |
% ( |
| 514 |
style.SQL_KEYWORD("ALTER"), |
| 515 |
style.SQL_KEYWORD("TABLE"), |
| 516 |
style.SQL_FIELD(self.quote_name(table)), |
| 517 |
style.SQL_KEYWORD("ENABLE"), |
| 518 |
style.SQL_KEYWORD("CONSTRAINT"), |
| 519 |
style.SQL_FIELD(self.quote_name(constraint)), |
| 520 |
) |
| 521 |
for table, constraint in constraints |
| 522 |
] |
| 523 |
) |
| 524 |
if reset_sequences: |
| 525 |
sequences = [ |
| 526 |
sequence |
| 527 |
for sequence in self.connection.introspection.sequence_list() |
| 528 |
if sequence["table"].upper() in truncated_tables |
| 529 |
] |
| 530 |
# Since we've just deleted all the rows, running our sequence ALTER |
| 531 |
# code will reset the sequence to 0. |
| 532 |
sql.extend(self.sequence_reset_by_name_sql(style, sequences)) |
| 533 |
return sql |
| 534 |
|
| 535 |
def sequence_reset_by_name_sql(self, style, sequences): |
| 536 |
sql = [] |
| 537 |
for sequence_info in sequences: |
| 538 |
no_autofield_sequence_name = self._get_no_autofield_sequence_name( |
| 539 |
sequence_info["table"] |
| 540 |
) |
| 541 |
table = self.quote_name(sequence_info["table"]) |
| 542 |
column = self.quote_name(sequence_info["column"] or "id") |
| 543 |
query = self._sequence_reset_sql % { |
| 544 |
"no_autofield_sequence_name": no_autofield_sequence_name, |
| 545 |
"table": table, |
| 546 |
"column": column, |
| 547 |
"table_name": strip_quotes(table), |
| 548 |
"column_name": strip_quotes(column), |
| 549 |
"suffix": self.connection.features.bare_select_suffix, |
| 550 |
} |
| 551 |
sql.append(query) |
| 552 |
return sql |
| 553 |
|
| 554 |
def sequence_reset_sql(self, style, model_list): |
| 555 |
output = [] |
| 556 |
query = self._sequence_reset_sql |
| 557 |
for model in model_list: |
| 558 |
for f in model._meta.local_fields: |
| 559 |
if isinstance(f, AutoField): |
| 560 |
no_autofield_sequence_name = self._get_no_autofield_sequence_name( |
| 561 |
model._meta.db_table |
| 562 |
) |
| 563 |
table = self.quote_name(model._meta.db_table) |
| 564 |
column = self.quote_name(f.column) |
| 565 |
output.append( |
| 566 |
query |
| 567 |
% { |
| 568 |
"no_autofield_sequence_name": no_autofield_sequence_name, |
| 569 |
"table": table, |
| 570 |
"column": column, |
| 571 |
"table_name": strip_quotes(table), |
| 572 |
"column_name": strip_quotes(column), |
| 573 |
"suffix": self.connection.features.bare_select_suffix, |
| 574 |
} |
| 575 |
) |
| 576 |
# Only one AutoField is allowed per model, so don't |
| 577 |
# continue to loop |
| 578 |
break |
| 579 |
return output |
| 580 |
|
| 581 |
def start_transaction_sql(self): |
| 582 |
return "" |
| 583 |
|
| 584 |
def tablespace_sql(self, tablespace, inline=False): |
| 585 |
if inline: |
| 586 |
return "USING INDEX TABLESPACE %s" % self.quote_name(tablespace) |
| 587 |
else: |
| 588 |
return "TABLESPACE %s" % self.quote_name(tablespace) |
| 589 |
|
| 590 |
def adapt_datefield_value(self, value): |
| 591 |
""" |
| 592 |
Transform a date value to an object compatible with what is expected |
| 593 |
by the backend driver for date columns. |
| 594 |
The default implementation transforms the date to text, but that is not |
| 595 |
necessary for Oracle. |
| 596 |
""" |
| 597 |
return value |
| 598 |
|
| 599 |
def adapt_datetimefield_value(self, value): |
| 600 |
""" |
| 601 |
Transform a datetime value to an object compatible with what is expected |
| 602 |
by the backend driver for datetime columns. |
| 603 |
|
| 604 |
If naive datetime is passed assumes that is in UTC. Normally Django |
| 605 |
models.DateTimeField makes sure that if USE_TZ is True passed datetime |
| 606 |
is timezone aware. |
| 607 |
""" |
| 608 |
|
| 609 |
if value is None: |
| 610 |
return None |
| 611 |
|
| 612 |
# oracledb doesn't support tz-aware datetimes |
| 613 |
if timezone.is_aware(value): |
| 614 |
if settings.USE_TZ: |
| 615 |
value = timezone.make_naive(value, self.connection.timezone) |
| 616 |
else: |
| 617 |
raise ValueError( |
| 618 |
"Oracle backend does not support timezone-aware datetimes when " |
| 619 |
"USE_TZ is False." |
| 620 |
) |
| 621 |
|
| 622 |
return Oracle_datetime.from_datetime(value) |
| 623 |
|
| 624 |
def adapt_timefield_value(self, value): |
| 625 |
if value is None: |
| 626 |
return None |
| 627 |
|
| 628 |
if isinstance(value, str): |
| 629 |
return datetime.datetime.strptime(value, "%H:%M:%S") |
| 630 |
|
| 631 |
# Oracle doesn't support tz-aware times |
| 632 |
if timezone.is_aware(value): |
| 633 |
raise ValueError("Oracle backend does not support timezone-aware times.") |
| 634 |
|
| 635 |
return Oracle_datetime( |
| 636 |
1900, 1, 1, value.hour, value.minute, value.second, value.microsecond |
| 637 |
) |
| 638 |
|
| 639 |
def combine_expression(self, connector, sub_expressions): |
| 640 |
lhs, rhs = sub_expressions |
| 641 |
if connector == "%%": |
| 642 |
return "MOD(%s)" % ",".join(sub_expressions) |
| 643 |
elif connector == "&": |
| 644 |
return "BITAND(%s)" % ",".join(sub_expressions) |
| 645 |
elif connector == "|": |
| 646 |
return "BITAND(-%(lhs)s-1,%(rhs)s)+%(lhs)s" % {"lhs": lhs, "rhs": rhs} |
| 647 |
elif connector == "<<": |
| 648 |
return "(%(lhs)s * POWER(2, %(rhs)s))" % {"lhs": lhs, "rhs": rhs} |
| 649 |
elif connector == ">>": |
| 650 |
return "FLOOR(%(lhs)s / POWER(2, %(rhs)s))" % {"lhs": lhs, "rhs": rhs} |
| 651 |
elif connector == "^": |
| 652 |
return "POWER(%s)" % ",".join(sub_expressions) |
| 653 |
elif connector == "#": |
| 654 |
raise NotSupportedError("Bitwise XOR is not supported in Oracle.") |
| 655 |
return super().combine_expression(connector, sub_expressions) |
| 656 |
|
| 657 |
def _get_no_autofield_sequence_name(self, table): |
| 658 |
""" |
| 659 |
Manually created sequence name to keep backward compatibility for |
| 660 |
AutoFields that aren't Oracle identity columns. |
| 661 |
""" |
| 662 |
name_length = self.max_name_length() - 3 |
| 663 |
return "%s_SQ" % truncate_name(strip_quotes(table), name_length).upper() |
| 664 |
|
| 665 |
def _get_sequence_name(self, cursor, table, pk_name): |
| 666 |
cursor.execute( |
| 667 |
""" |
| 668 |
SELECT sequence_name |
| 669 |
FROM user_tab_identity_cols |
| 670 |
WHERE table_name = UPPER(%s) |
| 671 |
AND column_name = UPPER(%s)""", |
| 672 |
[table, pk_name], |
| 673 |
) |
| 674 |
row = cursor.fetchone() |
| 675 |
return self._get_no_autofield_sequence_name(table) if row is None else row[0] |
| 676 |
|
| 677 |
def bulk_insert_sql(self, fields, placeholder_rows): |
| 678 |
field_placeholders = [ |
| 679 |
BulkInsertMapper.types.get( |
| 680 |
getattr(field, "target_field", field).get_internal_type(), "%s" |
| 681 |
) |
| 682 |
for field in fields |
| 683 |
if field |
| 684 |
] |
| 685 |
query = [] |
| 686 |
for row in placeholder_rows: |
| 687 |
select = [] |
| 688 |
for i, placeholder in enumerate(row): |
| 689 |
# A model without any fields has fields=[None]. |
| 690 |
if fields[i]: |
| 691 |
placeholder = field_placeholders[i] % placeholder |
| 692 |
# Add columns aliases to the first select to avoid "ORA-00918: |
| 693 |
# column ambiguously defined" when two or more columns in the |
| 694 |
# first select have the same value. |
| 695 |
if not query: |
| 696 |
placeholder = "%s col_%s" % (placeholder, i) |
| 697 |
select.append(placeholder) |
| 698 |
suffix = self.connection.features.bare_select_suffix |
| 699 |
query.append(f"SELECT %s{suffix}" % ", ".join(select)) |
| 700 |
# Bulk insert to tables with Oracle identity columns causes Oracle to |
| 701 |
# add sequence.nextval to it. Sequence.nextval cannot be used with the |
| 702 |
# UNION operator. To prevent incorrect SQL, move UNION to a subquery. |
| 703 |
return "SELECT * FROM (%s)" % " UNION ALL ".join(query) |
| 704 |
|
| 705 |
def subtract_temporals(self, internal_type, lhs, rhs): |
| 706 |
if internal_type == "DateField": |
| 707 |
lhs_sql, lhs_params = lhs |
| 708 |
rhs_sql, rhs_params = rhs |
| 709 |
params = (*lhs_params, *rhs_params) |
| 710 |
return ( |
| 711 |
"NUMTODSINTERVAL(TO_NUMBER(%s - %s), 'DAY')" % (lhs_sql, rhs_sql), |
| 712 |
params, |
| 713 |
) |
| 714 |
return super().subtract_temporals(internal_type, lhs, rhs) |
| 715 |
|
| 716 |
def bulk_batch_size(self, fields, objs): |
| 717 |
"""Oracle restricts the number of parameters in a query.""" |
| 718 |
fields = list( |
| 719 |
chain.from_iterable( |
| 720 |
field.fields if isinstance(field, CompositePrimaryKey) else [field] |
| 721 |
for field in fields |
| 722 |
) |
| 723 |
) |
| 724 |
if fields: |
| 725 |
return self.connection.features.max_query_params // len(fields) |
| 726 |
return len(objs) |
| 727 |
|
| 728 |
def conditional_expression_supported_in_where_clause(self, expression): |
| 729 |
""" |
| 730 |
Oracle supports only EXISTS(...) or filters in the WHERE clause, others |
| 731 |
must be compared with True. |
| 732 |
""" |
| 733 |
if isinstance(expression, (Exists, Lookup, WhereNode)): |
| 734 |
return True |
| 735 |
if isinstance(expression, ExpressionWrapper) and expression.conditional: |
| 736 |
return self.conditional_expression_supported_in_where_clause( |
| 737 |
expression.expression |
| 738 |
) |
| 739 |
if isinstance(expression, RawSQL) and expression.conditional: |
| 740 |
return True |
| 741 |
return False |