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