| 1 |
import datetime |
| 2 |
import decimal |
| 3 |
import uuid |
| 4 |
from functools import lru_cache |
| 5 |
from itertools import chain |
| 6 |
|
| 7 |
from django.conf import settings |
| 8 |
from django.core.exceptions import FieldError |
| 9 |
from django.db import DatabaseError, NotSupportedError, models |
| 10 |
from django.db.backends.base.operations import BaseDatabaseOperations |
| 11 |
from django.db.models.constants import OnConflict |
| 12 |
from django.db.models.expressions import Col |
| 13 |
from django.utils import timezone |
| 14 |
from django.utils.dateparse import parse_date, parse_datetime, parse_time |
| 15 |
from django.utils.functional import cached_property |
| 16 |
|
| 17 |
from .base import Database |
| 18 |
|
| 19 |
|
| 20 |
class DatabaseOperations(BaseDatabaseOperations): |
| 21 |
cast_char_field_without_max_length = "text" |
| 22 |
cast_data_types = { |
| 23 |
"DateField": "TEXT", |
| 24 |
"DateTimeField": "TEXT", |
| 25 |
} |
| 26 |
explain_prefix = "EXPLAIN QUERY PLAN" |
| 27 |
# List of datatypes to that cannot be extracted with JSON_EXTRACT() on |
| 28 |
# SQLite. Use JSON_TYPE() instead. |
| 29 |
jsonfield_datatype_values = frozenset(["null", "false", "true"]) |
| 30 |
|
| 31 |
def bulk_batch_size(self, fields, objs): |
| 32 |
""" |
| 33 |
SQLite has a compile-time default (SQLITE_LIMIT_VARIABLE_NUMBER) of |
| 34 |
999 variables per query. |
| 35 |
|
| 36 |
If there's only a single field to insert, the limit is 500 |
| 37 |
(SQLITE_MAX_COMPOUND_SELECT). |
| 38 |
""" |
| 39 |
fields = list( |
| 40 |
chain.from_iterable( |
| 41 |
( |
| 42 |
field.fields |
| 43 |
if isinstance(field, models.CompositePrimaryKey) |
| 44 |
else [field] |
| 45 |
) |
| 46 |
for field in fields |
| 47 |
) |
| 48 |
) |
| 49 |
if len(fields) == 1: |
| 50 |
return 500 |
| 51 |
elif len(fields) > 1: |
| 52 |
return self.connection.features.max_query_params // len(fields) |
| 53 |
else: |
| 54 |
return len(objs) |
| 55 |
|
| 56 |
def check_expression_support(self, expression): |
| 57 |
bad_fields = (models.DateField, models.DateTimeField, models.TimeField) |
| 58 |
bad_aggregates = (models.Sum, models.Avg, models.Variance, models.StdDev) |
| 59 |
if isinstance(expression, bad_aggregates): |
| 60 |
for expr in expression.get_source_expressions(): |
| 61 |
try: |
| 62 |
output_field = expr.output_field |
| 63 |
except (AttributeError, FieldError): |
| 64 |
# Not every subexpression has an output_field which is fine |
| 65 |
# to ignore. |
| 66 |
pass |
| 67 |
else: |
| 68 |
if isinstance(output_field, bad_fields): |
| 69 |
raise NotSupportedError( |
| 70 |
"You cannot use Sum, Avg, StdDev, and Variance " |
| 71 |
"aggregations on date/time fields in sqlite3 " |
| 72 |
"since date/time is saved as text." |
| 73 |
) |
| 74 |
if ( |
| 75 |
isinstance(expression, models.Aggregate) |
| 76 |
and expression.distinct |
| 77 |
and len(expression.source_expressions) > 1 |
| 78 |
): |
| 79 |
raise NotSupportedError( |
| 80 |
"SQLite doesn't support DISTINCT on aggregate functions " |
| 81 |
"accepting multiple arguments." |
| 82 |
) |
| 83 |
|
| 84 |
def date_extract_sql(self, lookup_type, sql, params): |
| 85 |
""" |
| 86 |
Support EXTRACT with a user-defined function django_date_extract() |
| 87 |
that's registered in connect(). Use single quotes because this is a |
| 88 |
string and could otherwise cause a collision with a field name. |
| 89 |
""" |
| 90 |
return f"django_date_extract(%s, {sql})", (lookup_type.lower(), *params) |
| 91 |
|
| 92 |
def fetch_returned_insert_rows(self, cursor): |
| 93 |
""" |
| 94 |
Given a cursor object that has just performed an INSERT...RETURNING |
| 95 |
statement into a table, return the list of returned data. |
| 96 |
""" |
| 97 |
return cursor.fetchall() |
| 98 |
|
| 99 |
def format_for_duration_arithmetic(self, sql): |
| 100 |
"""Do nothing since formatting is handled in the custom function.""" |
| 101 |
return sql |
| 102 |
|
| 103 |
def date_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 104 |
return f"django_date_trunc(%s, {sql}, %s, %s)", ( |
| 105 |
lookup_type.lower(), |
| 106 |
*params, |
| 107 |
*self._convert_tznames_to_sql(tzname), |
| 108 |
) |
| 109 |
|
| 110 |
def time_trunc_sql(self, lookup_type, sql, params, tzname=None): |
| 111 |
return f"django_time_trunc(%s, {sql}, %s, %s)", ( |
| 112 |
lookup_type.lower(), |
| 113 |
*params, |
| 114 |
*self._convert_tznames_to_sql(tzname), |
| 115 |
) |
| 116 |
|
| 117 |
def _convert_tznames_to_sql(self, tzname): |
| 118 |
if tzname and settings.USE_TZ: |
| 119 |
return tzname, self.connection.timezone_name |
| 120 |
return None, None |
| 121 |
|
| 122 |
def datetime_cast_date_sql(self, sql, params, tzname): |
| 123 |
return f"django_datetime_cast_date({sql}, %s, %s)", ( |
| 124 |
*params, |
| 125 |
*self._convert_tznames_to_sql(tzname), |
| 126 |
) |
| 127 |
|
| 128 |
def datetime_cast_time_sql(self, sql, params, tzname): |
| 129 |
return f"django_datetime_cast_time({sql}, %s, %s)", ( |
| 130 |
*params, |
| 131 |
*self._convert_tznames_to_sql(tzname), |
| 132 |
) |
| 133 |
|
| 134 |
def datetime_extract_sql(self, lookup_type, sql, params, tzname): |
| 135 |
return f"django_datetime_extract(%s, {sql}, %s, %s)", ( |
| 136 |
lookup_type.lower(), |
| 137 |
*params, |
| 138 |
*self._convert_tznames_to_sql(tzname), |
| 139 |
) |
| 140 |
|
| 141 |
def datetime_trunc_sql(self, lookup_type, sql, params, tzname): |
| 142 |
return f"django_datetime_trunc(%s, {sql}, %s, %s)", ( |
| 143 |
lookup_type.lower(), |
| 144 |
*params, |
| 145 |
*self._convert_tznames_to_sql(tzname), |
| 146 |
) |
| 147 |
|
| 148 |
def time_extract_sql(self, lookup_type, sql, params): |
| 149 |
return f"django_time_extract(%s, {sql})", (lookup_type.lower(), *params) |
| 150 |
|
| 151 |
def pk_default_value(self): |
| 152 |
return "NULL" |
| 153 |
|
| 154 |
def _quote_params_for_last_executed_query(self, params): |
| 155 |
""" |
| 156 |
Only for last_executed_query! Don't use this to execute SQL queries! |
| 157 |
""" |
| 158 |
# This function is limited both by SQLITE_LIMIT_VARIABLE_NUMBER (the |
| 159 |
# number of parameters, default = 999) and SQLITE_MAX_COLUMN (the |
| 160 |
# number of return values, default = 2000). Since Python's sqlite3 |
| 161 |
# module doesn't expose the get_limit() C API, assume the default |
| 162 |
# limits are in effect and split the work in batches if needed. |
| 163 |
BATCH_SIZE = 999 |
| 164 |
if len(params) > BATCH_SIZE: |
| 165 |
results = () |
| 166 |
for index in range(0, len(params), BATCH_SIZE): |
| 167 |
chunk = params[index : index + BATCH_SIZE] |
| 168 |
results += self._quote_params_for_last_executed_query(chunk) |
| 169 |
return results |
| 170 |
|
| 171 |
sql = "SELECT " + ", ".join(["QUOTE(?)"] * len(params)) |
| 172 |
# Bypass Django's wrappers and use the underlying sqlite3 connection |
| 173 |
# to avoid logging this query - it would trigger infinite recursion. |
| 174 |
cursor = self.connection.connection.cursor() |
| 175 |
# Native sqlite3 cursors cannot be used as context managers. |
| 176 |
try: |
| 177 |
return cursor.execute(sql, params).fetchone() |
| 178 |
finally: |
| 179 |
cursor.close() |
| 180 |
|
| 181 |
def last_executed_query(self, cursor, sql, params): |
| 182 |
# Python substitutes parameters in Modules/_sqlite/cursor.c with: |
| 183 |
# bind_parameters(state, self->statement, parameters); |
| 184 |
# Unfortunately there is no way to reach self->statement from Python, |
| 185 |
# so we quote and substitute parameters manually. |
| 186 |
if params: |
| 187 |
if isinstance(params, (list, tuple)): |
| 188 |
params = self._quote_params_for_last_executed_query(params) |
| 189 |
else: |
| 190 |
values = tuple(params.values()) |
| 191 |
values = self._quote_params_for_last_executed_query(values) |
| 192 |
params = dict(zip(params, values)) |
| 193 |
return sql % params |
| 194 |
# For consistency with SQLiteCursorWrapper.execute(), just return sql |
| 195 |
# when there are no parameters. See #13648 and #17158. |
| 196 |
else: |
| 197 |
return sql |
| 198 |
|
| 199 |
def quote_name(self, name): |
| 200 |
if name.startswith('"') and name.endswith('"'): |
| 201 |
return name # Quoting once is enough. |
| 202 |
return '"%s"' % name |
| 203 |
|
| 204 |
def no_limit_value(self): |
| 205 |
return -1 |
| 206 |
|
| 207 |
def __references_graph(self, table_name): |
| 208 |
query = """ |
| 209 |
WITH tables AS ( |
| 210 |
SELECT %s name |
| 211 |
UNION |
| 212 |
SELECT sqlite_master.name |
| 213 |
FROM sqlite_master |
| 214 |
JOIN tables ON (sql REGEXP %s || tables.name || %s) |
| 215 |
) SELECT name FROM tables; |
| 216 |
""" |
| 217 |
params = ( |
| 218 |
table_name, |
| 219 |
r'(?i)\s+references\s+("|\')?', |
| 220 |
r'("|\')?\s*\(', |
| 221 |
) |
| 222 |
with self.connection.cursor() as cursor: |
| 223 |
results = cursor.execute(query, params) |
| 224 |
return [row[0] for row in results.fetchall()] |
| 225 |
|
| 226 |
@cached_property |
| 227 |
def _references_graph(self): |
| 228 |
# 512 is large enough to fit the ~330 tables (as of this writing) in |
| 229 |
# Django's test suite. |
| 230 |
return lru_cache(maxsize=512)(self.__references_graph) |
| 231 |
|
| 232 |
def sql_flush(self, style, tables, *, reset_sequences=False, allow_cascade=False): |
| 233 |
if tables and allow_cascade: |
| 234 |
# Simulate TRUNCATE CASCADE by recursively collecting the tables |
| 235 |
# referencing the tables to be flushed. |
| 236 |
tables = set( |
| 237 |
chain.from_iterable(self._references_graph(table) for table in tables) |
| 238 |
) |
| 239 |
sql = [ |
| 240 |
"%s %s %s;" |
| 241 |
% ( |
| 242 |
style.SQL_KEYWORD("DELETE"), |
| 243 |
style.SQL_KEYWORD("FROM"), |
| 244 |
style.SQL_FIELD(self.quote_name(table)), |
| 245 |
) |
| 246 |
for table in tables |
| 247 |
] |
| 248 |
if reset_sequences: |
| 249 |
sequences = [{"table": table} for table in tables] |
| 250 |
sql.extend(self.sequence_reset_by_name_sql(style, sequences)) |
| 251 |
return sql |
| 252 |
|
| 253 |
def sequence_reset_by_name_sql(self, style, sequences): |
| 254 |
if not sequences: |
| 255 |
return [] |
| 256 |
return [ |
| 257 |
"%s %s %s %s = 0 %s %s %s (%s);" |
| 258 |
% ( |
| 259 |
style.SQL_KEYWORD("UPDATE"), |
| 260 |
style.SQL_TABLE(self.quote_name("sqlite_sequence")), |
| 261 |
style.SQL_KEYWORD("SET"), |
| 262 |
style.SQL_FIELD(self.quote_name("seq")), |
| 263 |
style.SQL_KEYWORD("WHERE"), |
| 264 |
style.SQL_FIELD(self.quote_name("name")), |
| 265 |
style.SQL_KEYWORD("IN"), |
| 266 |
", ".join( |
| 267 |
["'%s'" % sequence_info["table"] for sequence_info in sequences] |
| 268 |
), |
| 269 |
), |
| 270 |
] |
| 271 |
|
| 272 |
def adapt_datetimefield_value(self, value): |
| 273 |
if value is None: |
| 274 |
return None |
| 275 |
|
| 276 |
# SQLite doesn't support tz-aware datetimes |
| 277 |
if timezone.is_aware(value): |
| 278 |
if settings.USE_TZ: |
| 279 |
value = timezone.make_naive(value, self.connection.timezone) |
| 280 |
else: |
| 281 |
raise ValueError( |
| 282 |
"SQLite backend does not support timezone-aware datetimes when " |
| 283 |
"USE_TZ is False." |
| 284 |
) |
| 285 |
|
| 286 |
return str(value) |
| 287 |
|
| 288 |
def adapt_timefield_value(self, value): |
| 289 |
if value is None: |
| 290 |
return None |
| 291 |
|
| 292 |
# SQLite doesn't support tz-aware datetimes |
| 293 |
if timezone.is_aware(value): |
| 294 |
raise ValueError("SQLite backend does not support timezone-aware times.") |
| 295 |
|
| 296 |
return str(value) |
| 297 |
|
| 298 |
def get_db_converters(self, expression): |
| 299 |
converters = super().get_db_converters(expression) |
| 300 |
internal_type = expression.output_field.get_internal_type() |
| 301 |
if internal_type == "DateTimeField": |
| 302 |
converters.append(self.convert_datetimefield_value) |
| 303 |
elif internal_type == "DateField": |
| 304 |
converters.append(self.convert_datefield_value) |
| 305 |
elif internal_type == "TimeField": |
| 306 |
converters.append(self.convert_timefield_value) |
| 307 |
elif internal_type == "DecimalField": |
| 308 |
converters.append(self.get_decimalfield_converter(expression)) |
| 309 |
elif internal_type == "UUIDField": |
| 310 |
converters.append(self.convert_uuidfield_value) |
| 311 |
elif internal_type == "BooleanField": |
| 312 |
converters.append(self.convert_booleanfield_value) |
| 313 |
return converters |
| 314 |
|
| 315 |
def convert_datetimefield_value(self, value, expression, connection): |
| 316 |
if value is not None: |
| 317 |
if not isinstance(value, datetime.datetime): |
| 318 |
value = parse_datetime(value) |
| 319 |
if settings.USE_TZ and not timezone.is_aware(value): |
| 320 |
value = timezone.make_aware(value, self.connection.timezone) |
| 321 |
return value |
| 322 |
|
| 323 |
def convert_datefield_value(self, value, expression, connection): |
| 324 |
if value is not None: |
| 325 |
if not isinstance(value, datetime.date): |
| 326 |
value = parse_date(value) |
| 327 |
return value |
| 328 |
|
| 329 |
def convert_timefield_value(self, value, expression, connection): |
| 330 |
if value is not None: |
| 331 |
if not isinstance(value, datetime.time): |
| 332 |
value = parse_time(value) |
| 333 |
return value |
| 334 |
|
| 335 |
def get_decimalfield_converter(self, expression): |
| 336 |
# SQLite stores only 15 significant digits. Digits coming from |
| 337 |
# float inaccuracy must be removed. |
| 338 |
create_decimal = decimal.Context(prec=15).create_decimal_from_float |
| 339 |
if isinstance(expression, Col): |
| 340 |
quantize_value = decimal.Decimal(1).scaleb( |
| 341 |
-expression.output_field.decimal_places |
| 342 |
) |
| 343 |
|
| 344 |
def converter(value, expression, connection): |
| 345 |
if value is not None: |
| 346 |
return create_decimal(value).quantize( |
| 347 |
quantize_value, context=expression.output_field.context |
| 348 |
) |
| 349 |
|
| 350 |
else: |
| 351 |
|
| 352 |
def converter(value, expression, connection): |
| 353 |
if value is not None: |
| 354 |
return create_decimal(value) |
| 355 |
|
| 356 |
return converter |
| 357 |
|
| 358 |
def convert_uuidfield_value(self, value, expression, connection): |
| 359 |
if value is not None: |
| 360 |
value = uuid.UUID(value) |
| 361 |
return value |
| 362 |
|
| 363 |
def convert_booleanfield_value(self, value, expression, connection): |
| 364 |
return bool(value) if value in (1, 0) else value |
| 365 |
|
| 366 |
def combine_expression(self, connector, sub_expressions): |
| 367 |
# SQLite doesn't have a ^ operator, so use the user-defined POWER |
| 368 |
# function that's registered in connect(). |
| 369 |
if connector == "^": |
| 370 |
return "POWER(%s)" % ",".join(sub_expressions) |
| 371 |
elif connector == "#": |
| 372 |
return "BITXOR(%s)" % ",".join(sub_expressions) |
| 373 |
return super().combine_expression(connector, sub_expressions) |
| 374 |
|
| 375 |
def combine_duration_expression(self, connector, sub_expressions): |
| 376 |
if connector not in ["+", "-", "*", "/"]: |
| 377 |
raise DatabaseError("Invalid connector for timedelta: %s." % connector) |
| 378 |
fn_params = ["'%s'" % connector] + sub_expressions |
| 379 |
if len(fn_params) > 3: |
| 380 |
raise ValueError("Too many params for timedelta operations.") |
| 381 |
return "django_format_dtdelta(%s)" % ", ".join(fn_params) |
| 382 |
|
| 383 |
def integer_field_range(self, internal_type): |
| 384 |
# SQLite doesn't enforce any integer constraints, but sqlite3 supports |
| 385 |
# integers up to 64 bits. |
| 386 |
if internal_type in [ |
| 387 |
"PositiveBigIntegerField", |
| 388 |
"PositiveIntegerField", |
| 389 |
"PositiveSmallIntegerField", |
| 390 |
]: |
| 391 |
return (0, 9223372036854775807) |
| 392 |
return (-9223372036854775808, 9223372036854775807) |
| 393 |
|
| 394 |
def subtract_temporals(self, internal_type, lhs, rhs): |
| 395 |
lhs_sql, lhs_params = lhs |
| 396 |
rhs_sql, rhs_params = rhs |
| 397 |
params = (*lhs_params, *rhs_params) |
| 398 |
if internal_type == "TimeField": |
| 399 |
return "django_time_diff(%s, %s)" % (lhs_sql, rhs_sql), params |
| 400 |
return "django_timestamp_diff(%s, %s)" % (lhs_sql, rhs_sql), params |
| 401 |
|
| 402 |
def insert_statement(self, on_conflict=None): |
| 403 |
if on_conflict == OnConflict.IGNORE: |
| 404 |
return "INSERT OR IGNORE INTO" |
| 405 |
return super().insert_statement(on_conflict=on_conflict) |
| 406 |
|
| 407 |
def return_insert_columns(self, fields): |
| 408 |
# SQLite < 3.35 doesn't support an INSERT...RETURNING statement. |
| 409 |
if not fields: |
| 410 |
return "", () |
| 411 |
columns = [ |
| 412 |
"%s.%s" |
| 413 |
% ( |
| 414 |
self.quote_name(field.model._meta.db_table), |
| 415 |
self.quote_name(field.column), |
| 416 |
) |
| 417 |
for field in fields |
| 418 |
] |
| 419 |
return "RETURNING %s" % ", ".join(columns), () |
| 420 |
|
| 421 |
def on_conflict_suffix_sql(self, fields, on_conflict, update_fields, unique_fields): |
| 422 |
if ( |
| 423 |
on_conflict == OnConflict.UPDATE |
| 424 |
and self.connection.features.supports_update_conflicts_with_target |
| 425 |
): |
| 426 |
return "ON CONFLICT(%s) DO UPDATE SET %s" % ( |
| 427 |
", ".join(map(self.quote_name, unique_fields)), |
| 428 |
", ".join( |
| 429 |
[ |
| 430 |
f"{field} = EXCLUDED.{field}" |
| 431 |
for field in map(self.quote_name, update_fields) |
| 432 |
] |
| 433 |
), |
| 434 |
) |
| 435 |
return super().on_conflict_suffix_sql( |
| 436 |
fields, |
| 437 |
on_conflict, |
| 438 |
update_fields, |
| 439 |
unique_fields, |
| 440 |
) |
| 441 |
|
| 442 |
def force_group_by(self): |
| 443 |
return ["GROUP BY TRUE"] if Database.sqlite_version_info < (3, 39) else [] |