Debian Codegraph @

django/db/backends/postgresql/operations.py

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