operations.py 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413
  1. import json
  2. from functools import lru_cache, partial
  3. from django.conf import settings
  4. from django.db.backends.base.operations import BaseDatabaseOperations
  5. from django.db.backends.postgresql.psycopg_any import (
  6. Inet,
  7. Jsonb,
  8. errors,
  9. is_psycopg3,
  10. mogrify,
  11. )
  12. from django.db.backends.utils import split_tzname_delta
  13. from django.db.models.constants import OnConflict
  14. from django.db.models.functions import Cast
  15. from django.utils.regex_helper import _lazy_re_compile
  16. @lru_cache
  17. def get_json_dumps(encoder):
  18. if encoder is None:
  19. return json.dumps
  20. return partial(json.dumps, cls=encoder)
  21. class DatabaseOperations(BaseDatabaseOperations):
  22. cast_char_field_without_max_length = "varchar"
  23. explain_prefix = "EXPLAIN"
  24. explain_options = frozenset(
  25. [
  26. "ANALYZE",
  27. "BUFFERS",
  28. "COSTS",
  29. "GENERIC_PLAN",
  30. "SETTINGS",
  31. "SUMMARY",
  32. "TIMING",
  33. "VERBOSE",
  34. "WAL",
  35. ]
  36. )
  37. cast_data_types = {
  38. "AutoField": "integer",
  39. "BigAutoField": "bigint",
  40. "SmallAutoField": "smallint",
  41. }
  42. if is_psycopg3:
  43. from psycopg.types import numeric
  44. integerfield_type_map = {
  45. "SmallIntegerField": numeric.Int2,
  46. "IntegerField": numeric.Int4,
  47. "BigIntegerField": numeric.Int8,
  48. "PositiveSmallIntegerField": numeric.Int2,
  49. "PositiveIntegerField": numeric.Int4,
  50. "PositiveBigIntegerField": numeric.Int8,
  51. }
  52. def unification_cast_sql(self, output_field):
  53. internal_type = output_field.get_internal_type()
  54. if internal_type in (
  55. "GenericIPAddressField",
  56. "IPAddressField",
  57. "TimeField",
  58. "UUIDField",
  59. ):
  60. # PostgreSQL will resolve a union as type 'text' if input types are
  61. # 'unknown'.
  62. # https://www.postgresql.org/docs/current/typeconv-union-case.html
  63. # These fields cannot be implicitly cast back in the default
  64. # PostgreSQL configuration so we need to explicitly cast them.
  65. # We must also remove components of the type within brackets:
  66. # varchar(255) -> varchar.
  67. return (
  68. "CAST(%%s AS %s)" % output_field.db_type(self.connection).split("(")[0]
  69. )
  70. return "%s"
  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. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT
  75. if lookup_type == "week_day":
  76. # For consistency across backends, we return Sunday=1, Saturday=7.
  77. return f"EXTRACT(DOW FROM {sql}) + 1", params
  78. elif lookup_type == "iso_week_day":
  79. return f"EXTRACT(ISODOW FROM {sql})", params
  80. elif lookup_type == "iso_year":
  81. return f"EXTRACT(ISOYEAR FROM {sql})", params
  82. lookup_type = lookup_type.upper()
  83. if not self._extract_format_re.fullmatch(lookup_type):
  84. raise ValueError(f"Invalid lookup type: {lookup_type!r}")
  85. return f"EXTRACT({lookup_type} FROM {sql})", params
  86. def date_trunc_sql(self, lookup_type, sql, params, tzname=None):
  87. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  88. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
  89. return f"DATE_TRUNC(%s, {sql})", (lookup_type, *params)
  90. def _prepare_tzname_delta(self, tzname):
  91. tzname, sign, offset = split_tzname_delta(tzname)
  92. if offset:
  93. sign = "-" if sign == "+" else "+"
  94. return f"{tzname}{sign}{offset}"
  95. return tzname
  96. def _convert_sql_to_tz(self, sql, params, tzname):
  97. if tzname and settings.USE_TZ:
  98. tzname_param = self._prepare_tzname_delta(tzname)
  99. return f"{sql} AT TIME ZONE %s", (*params, tzname_param)
  100. return sql, params
  101. def datetime_cast_date_sql(self, sql, params, tzname):
  102. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  103. return f"({sql})::date", params
  104. def datetime_cast_time_sql(self, sql, params, tzname):
  105. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  106. return f"({sql})::time", params
  107. def datetime_extract_sql(self, lookup_type, sql, params, tzname):
  108. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  109. if lookup_type == "second":
  110. # Truncate fractional seconds.
  111. return f"EXTRACT(SECOND FROM DATE_TRUNC(%s, {sql}))", ("second", *params)
  112. return self.date_extract_sql(lookup_type, sql, params)
  113. def datetime_trunc_sql(self, lookup_type, sql, params, tzname):
  114. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  115. # https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
  116. return f"DATE_TRUNC(%s, {sql})", (lookup_type, *params)
  117. def time_extract_sql(self, lookup_type, sql, params):
  118. if lookup_type == "second":
  119. # Truncate fractional seconds.
  120. return f"EXTRACT(SECOND FROM DATE_TRUNC(%s, {sql}))", ("second", *params)
  121. return self.date_extract_sql(lookup_type, sql, params)
  122. def time_trunc_sql(self, lookup_type, sql, params, tzname=None):
  123. sql, params = self._convert_sql_to_tz(sql, params, tzname)
  124. return f"DATE_TRUNC(%s, {sql})::time", (lookup_type, *params)
  125. def deferrable_sql(self):
  126. return " DEFERRABLE INITIALLY DEFERRED"
  127. def fetch_returned_insert_rows(self, cursor):
  128. """
  129. Given a cursor object that has just performed an INSERT...RETURNING
  130. statement into a table, return the tuple of returned data.
  131. """
  132. return cursor.fetchall()
  133. def lookup_cast(self, lookup_type, internal_type=None):
  134. lookup = "%s"
  135. # Cast text lookups to text to allow things like filter(x__contains=4)
  136. if lookup_type in (
  137. "iexact",
  138. "contains",
  139. "icontains",
  140. "startswith",
  141. "istartswith",
  142. "endswith",
  143. "iendswith",
  144. "regex",
  145. "iregex",
  146. ):
  147. if internal_type in ("IPAddressField", "GenericIPAddressField"):
  148. lookup = "HOST(%s)"
  149. else:
  150. lookup = "%s::text"
  151. # Use UPPER(x) for case-insensitive lookups; it's faster.
  152. if lookup_type in ("iexact", "icontains", "istartswith", "iendswith"):
  153. lookup = "UPPER(%s)" % lookup
  154. return lookup
  155. def no_limit_value(self):
  156. return None
  157. def prepare_sql_script(self, sql):
  158. return [sql]
  159. def quote_name(self, name):
  160. if name.startswith('"') and name.endswith('"'):
  161. return name # Quoting once is enough.
  162. return '"%s"' % name
  163. def compose_sql(self, sql, params):
  164. return mogrify(sql, params, self.connection)
  165. def set_time_zone_sql(self):
  166. return "SELECT set_config('TimeZone', %s, false)"
  167. def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False):
  168. if not tables:
  169. return []
  170. # Perform a single SQL 'TRUNCATE x, y, z...;' statement. It allows us
  171. # to truncate tables referenced by a foreign key in any other table.
  172. sql_parts = [
  173. style.SQL_KEYWORD("TRUNCATE"),
  174. ", ".join(style.SQL_FIELD(self.quote_name(table)) for table in tables),
  175. ]
  176. if reset_sequences:
  177. sql_parts.append(style.SQL_KEYWORD("RESTART IDENTITY"))
  178. if allow_cascade:
  179. sql_parts.append(style.SQL_KEYWORD("CASCADE"))
  180. return ["%s;" % " ".join(sql_parts)]
  181. def sequence_reset_by_name_sql(self, style, sequences):
  182. # 'ALTER SEQUENCE sequence_name RESTART WITH 1;'... style SQL statements
  183. # to reset sequence indices
  184. sql = []
  185. for sequence_info in sequences:
  186. table_name = sequence_info["table"]
  187. # 'id' will be the case if it's an m2m using an autogenerated
  188. # intermediate table (see BaseDatabaseIntrospection.sequence_list).
  189. column_name = sequence_info["column"] or "id"
  190. sql.append(
  191. "%s setval(pg_get_serial_sequence('%s','%s'), 1, false);"
  192. % (
  193. style.SQL_KEYWORD("SELECT"),
  194. style.SQL_TABLE(self.quote_name(table_name)),
  195. style.SQL_FIELD(column_name),
  196. )
  197. )
  198. return sql
  199. def tablespace_sql(self, tablespace, inline=False):
  200. if inline:
  201. return "USING INDEX TABLESPACE %s" % self.quote_name(tablespace)
  202. else:
  203. return "TABLESPACE %s" % self.quote_name(tablespace)
  204. def sequence_reset_sql(self, style, model_list):
  205. from django.db import models
  206. output = []
  207. qn = self.quote_name
  208. for model in model_list:
  209. # Use `coalesce` to set the sequence for each model to the max pk
  210. # value if there are records, or 1 if there are none. Set the
  211. # `is_called` property (the third argument to `setval`) to true if
  212. # there are records (as the max pk value is already in use),
  213. # otherwise set it to false. Use pg_get_serial_sequence to get the
  214. # underlying sequence name from the table name and column name.
  215. for f in model._meta.local_fields:
  216. if isinstance(f, models.AutoField):
  217. output.append(
  218. "%s setval(pg_get_serial_sequence('%s','%s'), "
  219. "coalesce(max(%s), 1), max(%s) %s null) %s %s;"
  220. % (
  221. style.SQL_KEYWORD("SELECT"),
  222. style.SQL_TABLE(qn(model._meta.db_table)),
  223. style.SQL_FIELD(f.column),
  224. style.SQL_FIELD(qn(f.column)),
  225. style.SQL_FIELD(qn(f.column)),
  226. style.SQL_KEYWORD("IS NOT"),
  227. style.SQL_KEYWORD("FROM"),
  228. style.SQL_TABLE(qn(model._meta.db_table)),
  229. )
  230. )
  231. # Only one AutoField is allowed per model, so don't bother
  232. # continuing.
  233. break
  234. return output
  235. def prep_for_iexact_query(self, x):
  236. return x
  237. def max_name_length(self):
  238. """
  239. Return the maximum length of an identifier.
  240. The maximum length of an identifier is 63 by default, but can be
  241. changed by recompiling PostgreSQL after editing the NAMEDATALEN
  242. macro in src/include/pg_config_manual.h.
  243. This implementation returns 63, but can be overridden by a custom
  244. database backend that inherits most of its behavior from this one.
  245. """
  246. return 63
  247. def distinct_sql(self, fields, params):
  248. if fields:
  249. params = [param for param_list in params for param in param_list]
  250. return (["DISTINCT ON (%s)" % ", ".join(fields)], params)
  251. else:
  252. return ["DISTINCT"], []
  253. if is_psycopg3:
  254. def last_executed_query(self, cursor, sql, params):
  255. if self.connection.features.uses_server_side_binding:
  256. try:
  257. return self.compose_sql(sql, params)
  258. except errors.DataError:
  259. return None
  260. else:
  261. if cursor._query and cursor._query.query is not None:
  262. return cursor._query.query.decode()
  263. return None
  264. else:
  265. def last_executed_query(self, cursor, sql, params):
  266. # https://www.psycopg.org/docs/cursor.html#cursor.query
  267. # The query attribute is a Psycopg extension to the DB API 2.0.
  268. if cursor.query is not None:
  269. return cursor.query.decode()
  270. return None
  271. def return_insert_columns(self, fields):
  272. if not fields:
  273. return "", ()
  274. columns = [
  275. "%s.%s"
  276. % (
  277. self.quote_name(field.model._meta.db_table),
  278. self.quote_name(field.column),
  279. )
  280. for field in fields
  281. ]
  282. return "RETURNING %s" % ", ".join(columns), ()
  283. if is_psycopg3:
  284. def adapt_integerfield_value(self, value, internal_type):
  285. if value is None or hasattr(value, "resolve_expression"):
  286. return value
  287. return self.integerfield_type_map[internal_type](value)
  288. def adapt_datefield_value(self, value):
  289. return value
  290. def adapt_datetimefield_value(self, value):
  291. return value
  292. def adapt_timefield_value(self, value):
  293. return value
  294. def adapt_decimalfield_value(self, value, max_digits=None, decimal_places=None):
  295. return value
  296. def adapt_ipaddressfield_value(self, value):
  297. if value:
  298. return Inet(value)
  299. return None
  300. def adapt_json_value(self, value, encoder):
  301. return Jsonb(value, dumps=get_json_dumps(encoder))
  302. def subtract_temporals(self, internal_type, lhs, rhs):
  303. if internal_type == "DateField":
  304. lhs_sql, lhs_params = lhs
  305. rhs_sql, rhs_params = rhs
  306. params = (*lhs_params, *rhs_params)
  307. return "(interval '1 day' * (%s - %s))" % (lhs_sql, rhs_sql), params
  308. return super().subtract_temporals(internal_type, lhs, rhs)
  309. def explain_query_prefix(self, format=None, **options):
  310. extra = {}
  311. # Normalize options.
  312. if options:
  313. options = {
  314. name.upper(): "true" if value else "false"
  315. for name, value in options.items()
  316. }
  317. for valid_option in self.explain_options:
  318. value = options.pop(valid_option, None)
  319. if value is not None:
  320. extra[valid_option] = value
  321. prefix = super().explain_query_prefix(format, **options)
  322. if format:
  323. extra["FORMAT"] = format
  324. if extra:
  325. prefix += " (%s)" % ", ".join("%s %s" % i for i in extra.items())
  326. return prefix
  327. def on_conflict_suffix_sql(self, fields, on_conflict, update_fields, unique_fields):
  328. if on_conflict == OnConflict.IGNORE:
  329. return "ON CONFLICT DO NOTHING"
  330. if on_conflict == OnConflict.UPDATE:
  331. return "ON CONFLICT(%s) DO UPDATE SET %s" % (
  332. ", ".join(map(self.quote_name, unique_fields)),
  333. ", ".join(
  334. [
  335. f"{field} = EXCLUDED.{field}"
  336. for field in map(self.quote_name, update_fields)
  337. ]
  338. ),
  339. )
  340. return super().on_conflict_suffix_sql(
  341. fields,
  342. on_conflict,
  343. update_fields,
  344. unique_fields,
  345. )
  346. def prepare_join_on_clause(self, lhs_table, lhs_field, rhs_table, rhs_field):
  347. lhs_expr, rhs_expr = super().prepare_join_on_clause(
  348. lhs_table, lhs_field, rhs_table, rhs_field
  349. )
  350. if lhs_field.db_type(self.connection) != rhs_field.db_type(self.connection):
  351. rhs_expr = Cast(rhs_expr, lhs_field)
  352. return lhs_expr, rhs_expr