Debian Codegraph @

django/db/backends/sqlite3/operations.py

raw
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 []