Debian Codegraph @

django/db/backends/oracle/operations.py

raw
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