| 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 |