Debian Codegraph @

django/db/backends/sqlite3/base.py

raw
1 """
2 SQLite backend for the sqlite3 module in the standard library.
3 """
4
5 import datetime
6 import decimal
7 import warnings
8 from collections.abc import Mapping
9 from itertools import chain, tee
10 from sqlite3 import dbapi2 as Database
11
12 from django.core.exceptions import ImproperlyConfigured
13 from django.db import IntegrityError
14 from django.db.backends.base.base import BaseDatabaseWrapper
15 from django.utils.asyncio import async_unsafe
16 from django.utils.dateparse import parse_date, parse_datetime, parse_time
17 from django.utils.regex_helper import _lazy_re_compile
18
19 from ._functions import register as register_functions
20 from .client import DatabaseClient
21 from .creation import DatabaseCreation
22 from .features import DatabaseFeatures
23 from .introspection import DatabaseIntrospection
24 from .operations import DatabaseOperations
25 from .schema import DatabaseSchemaEditor
26
27
28 def decoder(conv_func):
29 """
30 Convert bytestrings from Python's sqlite3 interface to a regular string.
31 """
32 return lambda s: conv_func(s.decode())
33
34
35 def adapt_date(val):
36 return val.isoformat()
37
38
39 def adapt_datetime(val):
40 return val.isoformat(" ")
41
42
43 def _get_varchar_column(data):
44 if data["max_length"] is None:
45 return "varchar"
46 return "varchar(%(max_length)s)" % data
47
48
49 Database.register_converter("bool", b"1".__eq__)
50 Database.register_converter("date", decoder(parse_date))
51 Database.register_converter("time", decoder(parse_time))
52 Database.register_converter("datetime", decoder(parse_datetime))
53 Database.register_converter("timestamp", decoder(parse_datetime))
54
55 Database.register_adapter(decimal.Decimal, str)
56 Database.register_adapter(datetime.date, adapt_date)
57 Database.register_adapter(datetime.datetime, adapt_datetime)
58
59
60 class DatabaseWrapper(BaseDatabaseWrapper):
61 vendor = "sqlite"
62 display_name = "SQLite"
63 # SQLite doesn't actually support most of these types, but it "does the right
64 # thing" given more verbose field definitions, so leave them as is so that
65 # schema inspection is more useful.
66 data_types = {
67 "AutoField": "integer",
68 "BigAutoField": "integer",
69 "BinaryField": "BLOB",
70 "BooleanField": "bool",
71 "CharField": _get_varchar_column,
72 "DateField": "date",
73 "DateTimeField": "datetime",
74 "DecimalField": "decimal",
75 "DurationField": "bigint",
76 "FileField": "varchar(%(max_length)s)",
77 "FilePathField": "varchar(%(max_length)s)",
78 "FloatField": "real",
79 "IntegerField": "integer",
80 "BigIntegerField": "bigint",
81 "IPAddressField": "char(15)",
82 "GenericIPAddressField": "char(39)",
83 "JSONField": "text",
84 "OneToOneField": "integer",
85 "PositiveBigIntegerField": "bigint unsigned",
86 "PositiveIntegerField": "integer unsigned",
87 "PositiveSmallIntegerField": "smallint unsigned",
88 "SlugField": "varchar(%(max_length)s)",
89 "SmallAutoField": "integer",
90 "SmallIntegerField": "smallint",
91 "TextField": "text",
92 "TimeField": "time",
93 "UUIDField": "char(32)",
94 }
95 data_type_check_constraints = {
96 "PositiveBigIntegerField": '"%(column)s" >= 0',
97 "JSONField": '(JSON_VALID("%(column)s") OR "%(column)s" IS NULL)',
98 "PositiveIntegerField": '"%(column)s" >= 0',
99 "PositiveSmallIntegerField": '"%(column)s" >= 0',
100 }
101 data_types_suffix = {
102 "AutoField": "AUTOINCREMENT",
103 "BigAutoField": "AUTOINCREMENT",
104 "SmallAutoField": "AUTOINCREMENT",
105 }
106 # SQLite requires LIKE statements to include an ESCAPE clause if the value
107 # being escaped has a percent or underscore in it.
108 # See https://www.sqlite.org/lang_expr.html for an explanation.
109 operators = {
110 "exact": "= %s",
111 "iexact": "LIKE %s ESCAPE '\\'",
112 "contains": "LIKE %s ESCAPE '\\'",
113 "icontains": "LIKE %s ESCAPE '\\'",
114 "regex": "REGEXP %s",
115 "iregex": "REGEXP '(?i)' || %s",
116 "gt": "> %s",
117 "gte": ">= %s",
118 "lt": "< %s",
119 "lte": "<= %s",
120 "startswith": "LIKE %s ESCAPE '\\'",
121 "endswith": "LIKE %s ESCAPE '\\'",
122 "istartswith": "LIKE %s ESCAPE '\\'",
123 "iendswith": "LIKE %s ESCAPE '\\'",
124 }
125
126 # The patterns below are used to generate SQL pattern lookup clauses when
127 # the right-hand side of the lookup isn't a raw string (it might be an expression
128 # or the result of a bilateral transformation).
129 # In those cases, special characters for LIKE operators (e.g. \, *, _) should be
130 # escaped on database side.
131 #
132 # Note: we use str.format() here for readability as '%' is used as a wildcard for
133 # the LIKE operator.
134 pattern_esc = r"REPLACE(REPLACE(REPLACE({}, '\', '\\'), '%%', '\%%'), '_', '\_')"
135 pattern_ops = {
136 "contains": r"LIKE '%%' || {} || '%%' ESCAPE '\'",
137 "icontains": r"LIKE '%%' || UPPER({}) || '%%' ESCAPE '\'",
138 "startswith": r"LIKE {} || '%%' ESCAPE '\'",
139 "istartswith": r"LIKE UPPER({}) || '%%' ESCAPE '\'",
140 "endswith": r"LIKE '%%' || {} ESCAPE '\'",
141 "iendswith": r"LIKE '%%' || UPPER({}) ESCAPE '\'",
142 }
143
144 transaction_modes = frozenset(["DEFERRED", "EXCLUSIVE", "IMMEDIATE"])
145
146 Database = Database
147 SchemaEditorClass = DatabaseSchemaEditor
148 # Classes instantiated in __init__().
149 client_class = DatabaseClient
150 creation_class = DatabaseCreation
151 features_class = DatabaseFeatures
152 introspection_class = DatabaseIntrospection
153 ops_class = DatabaseOperations
154
155 def get_connection_params(self):
156 settings_dict = self.settings_dict
157 if not settings_dict["NAME"]:
158 raise ImproperlyConfigured(
159 "settings.DATABASES is improperly configured. "
160 "Please supply the NAME value."
161 )
162 kwargs = {
163 "database": settings_dict["NAME"],
164 "detect_types": Database.PARSE_DECLTYPES | Database.PARSE_COLNAMES,
165 **settings_dict["OPTIONS"],
166 }
167 # Always allow the underlying SQLite connection to be shareable
168 # between multiple threads. The safe-guarding will be handled at a
169 # higher level by the `BaseDatabaseWrapper.allow_thread_sharing`
170 # property. This is necessary as the shareability is disabled by
171 # default in sqlite3 and it cannot be changed once a connection is
172 # opened.
173 if "check_same_thread" in kwargs and kwargs["check_same_thread"]:
174 warnings.warn(
175 "The `check_same_thread` option was provided and set to "
176 "True. It will be overridden with False. Use the "
177 "`DatabaseWrapper.allow_thread_sharing` property instead "
178 "for controlling thread shareability.",
179 RuntimeWarning,
180 )
181 kwargs.update({"check_same_thread": False, "uri": True})
182 transaction_mode = kwargs.pop("transaction_mode", None)
183 if (
184 transaction_mode is not None
185 and transaction_mode.upper() not in self.transaction_modes
186 ):
187 allowed_transaction_modes = ", ".join(
188 [f"{mode!r}" for mode in sorted(self.transaction_modes)]
189 )
190 raise ImproperlyConfigured(
191 f"settings.DATABASES[{self.alias!r}]['OPTIONS']['transaction_mode'] "
192 f"is improperly configured to '{transaction_mode}'. Use one of "
193 f"{allowed_transaction_modes}, or None."
194 )
195 self.transaction_mode = transaction_mode.upper() if transaction_mode else None
196
197 init_command = kwargs.pop("init_command", "")
198 self.init_commands = init_command.split(";")
199 return kwargs
200
201 def get_database_version(self):
202 return self.Database.sqlite_version_info
203
204 @async_unsafe
205 def get_new_connection(self, conn_params):
206 conn = Database.connect(**conn_params)
207 register_functions(conn)
208
209 conn.execute("PRAGMA foreign_keys = ON")
210 # The macOS bundled SQLite defaults legacy_alter_table ON, which
211 # prevents atomic table renames.
212 conn.execute("PRAGMA legacy_alter_table = OFF")
213 for init_command in self.init_commands:
214 if init_command := init_command.strip():
215 conn.execute(init_command)
216 return conn
217
218 def create_cursor(self, name=None):
219 return self.connection.cursor(factory=SQLiteCursorWrapper)
220
221 @async_unsafe
222 def close(self):
223 self.validate_thread_sharing()
224 # If database is in memory, closing the connection destroys the
225 # database. To prevent accidental data loss, ignore close requests on
226 # an in-memory db.
227 if not self.is_in_memory_db():
228 BaseDatabaseWrapper.close(self)
229
230 def _savepoint_allowed(self):
231 # When 'isolation_level' is not None, sqlite3 commits before each
232 # savepoint; it's a bug. When it is None, savepoints don't make sense
233 # because autocommit is enabled. The only exception is inside 'atomic'
234 # blocks. To work around that bug, on SQLite, 'atomic' starts a
235 # transaction explicitly rather than simply disable autocommit.
236 return self.in_atomic_block
237
238 def _set_autocommit(self, autocommit):
239 if autocommit:
240 level = None
241 else:
242 # sqlite3's internal default is ''. It's different from None.
243 # See Modules/_sqlite/connection.c.
244 level = ""
245 # 'isolation_level' is a misleading API.
246 # SQLite always runs at the SERIALIZABLE isolation level.
247 with self.wrap_database_errors:
248 self.connection.isolation_level = level
249
250 def disable_constraint_checking(self):
251 with self.cursor() as cursor:
252 cursor.execute("PRAGMA foreign_keys = OFF")
253 # Foreign key constraints cannot be turned off while in a multi-
254 # statement transaction. Fetch the current state of the pragma
255 # to determine if constraints are effectively disabled.
256 enabled = cursor.execute("PRAGMA foreign_keys").fetchone()[0]
257 return not bool(enabled)
258
259 def enable_constraint_checking(self):
260 with self.cursor() as cursor:
261 cursor.execute("PRAGMA foreign_keys = ON")
262
263 def check_constraints(self, table_names=None):
264 """
265 Check each table name in `table_names` for rows with invalid foreign
266 key references. This method is intended to be used in conjunction with
267 `disable_constraint_checking()` and `enable_constraint_checking()`, to
268 determine if rows with invalid references were entered while constraint
269 checks were off.
270 """
271 with self.cursor() as cursor:
272 if table_names is None:
273 violations = cursor.execute("PRAGMA foreign_key_check").fetchall()
274 else:
275 violations = chain.from_iterable(
276 cursor.execute(
277 "PRAGMA foreign_key_check(%s)" % self.ops.quote_name(table_name)
278 ).fetchall()
279 for table_name in table_names
280 )
281 # See https://www.sqlite.org/pragma.html#pragma_foreign_key_check
282 for (
283 table_name,
284 rowid,
285 referenced_table_name,
286 foreign_key_index,
287 ) in violations:
288 foreign_key = cursor.execute(
289 "PRAGMA foreign_key_list(%s)" % self.ops.quote_name(table_name)
290 ).fetchall()[foreign_key_index]
291 column_name, referenced_column_name = foreign_key[3:5]
292 primary_key_column_name = self.introspection.get_primary_key_column(
293 cursor, table_name
294 )
295 primary_key_value, bad_value = cursor.execute(
296 "SELECT %s, %s FROM %s WHERE rowid = %%s"
297 % (
298 self.ops.quote_name(primary_key_column_name),
299 self.ops.quote_name(column_name),
300 self.ops.quote_name(table_name),
301 ),
302 (rowid,),
303 ).fetchone()
304 raise IntegrityError(
305 "The row in table '%s' with primary key '%s' has an "
306 "invalid foreign key: %s.%s contains a value '%s' that "
307 "does not have a corresponding value in %s.%s."
308 % (
309 table_name,
310 primary_key_value,
311 table_name,
312 column_name,
313 bad_value,
314 referenced_table_name,
315 referenced_column_name,
316 )
317 )
318
319 def is_usable(self):
320 return True
321
322 def _start_transaction_under_autocommit(self):
323 """
324 Start a transaction explicitly in autocommit mode.
325
326 Staying in autocommit mode works around a bug of sqlite3 that breaks
327 savepoints when autocommit is disabled.
328 """
329 if self.transaction_mode is None:
330 self.cursor().execute("BEGIN")
331 else:
332 self.cursor().execute(f"BEGIN {self.transaction_mode}")
333
334 def is_in_memory_db(self):
335 return self.creation.is_in_memory_db(self.settings_dict["NAME"])
336
337
338 FORMAT_QMARK_REGEX = _lazy_re_compile(r"(?<!%)%s")
339
340
341 class SQLiteCursorWrapper(Database.Cursor):
342 """
343 Django uses the "format" and "pyformat" styles, but Python's sqlite3 module
344 supports neither of these styles.
345
346 This wrapper performs the following conversions:
347
348 - "format" style to "qmark" style
349 - "pyformat" style to "named" style
350
351 In both cases, if you want to use a literal "%s", you'll need to use "%%s".
352 """
353
354 def execute(self, query, params=None):
355 if params is None:
356 return super().execute(query)
357 # Extract names if params is a mapping, i.e. "pyformat" style is used.
358 param_names = list(params) if isinstance(params, Mapping) else None
359 query = self.convert_query(query, param_names=param_names)
360 return super().execute(query, params)
361
362 def executemany(self, query, param_list):
363 # Extract names if params is a mapping, i.e. "pyformat" style is used.
364 # Peek carefully as a generator can be passed instead of a list/tuple.
365 peekable, param_list = tee(iter(param_list))
366 if (params := next(peekable, None)) and isinstance(params, Mapping):
367 param_names = list(params)
368 else:
369 param_names = None
370 query = self.convert_query(query, param_names=param_names)
371 return super().executemany(query, param_list)
372
373 def convert_query(self, query, *, param_names=None):
374 if param_names is None:
375 # Convert from "format" style to "qmark" style.
376 return FORMAT_QMARK_REGEX.sub("?", query).replace("%%", "%")
377 else:
378 # Convert from "pyformat" style to "named" style.
379 return query % {name: f":{name}" for name in param_names}