operations.py 29 KB

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