2"""Implementations to make access to SQLite databases a little more convenient.
5 Abstract class with which DB applications can be implemented.
7:py:obj:`SQLiteProperties`:
8 Class to manage properties stored in a database.
10Examplarical implementations based on :py:obj:`SQLiteAppl`:
12:py:obj:`searx.cache.ExpireCacheSQLite` :
13 Cache that manages key/value pairs in a SQLite DB, in which the key/value
14 pairs are deleted after an "expire" time. This type of cache is used, for
15 example, for the engines, see :py:obj:`searx.enginelib.EngineCache`.
17:py:obj:`searx.favicons.cache.FaviconCacheSQLite` :
18 Favicon cache that manages the favicon BLOBs in a SQLite DB.
32from searx
import logger
34logger = logger.getChild(
"sqlitedb")
36THREAD_LOCAL = threading.local()
40 """A *thead-local* DB session"""
43 def get_connect(cls, app:
"SQLiteAppl") -> sqlite3.Connection:
44 """Returns a thread local DB connection. The connection is only
45 established once per thread.
47 if getattr(THREAD_LOCAL,
"DBSession_map",
None)
is None:
48 url_to_session: dict[str, DBSession] = {}
49 THREAD_LOCAL.DBSession_map = url_to_session
51 session: DBSession |
None = THREAD_LOCAL.DBSession_map.get(app.db_url)
57 self.
uuid: uuid.UUID = uuid.uuid4()
58 self.
app: SQLiteAppl = app
59 self.
_conn: sqlite3.Connection |
None =
None
61 if getattr(THREAD_LOCAL,
"DBSession_map",
None)
is None:
62 url_to_session: dict[str, DBSession] = {}
63 THREAD_LOCAL.DBSession_map = url_to_session
64 THREAD_LOCAL.DBSession_map[self.
app.db_url] = self
67 def conn(self) -> sqlite3.Connection:
68 msg = f
"[{threading.current_thread().ident}] DBSession: " f
"{self.app.__class__.__name__}({self.app.db_url})"
69 if self.
_conn is None:
71 logger.debug(
"%s --> created new connection", msg)
79 if self.
_conn is not None:
92 """Abstract base class for implementing convenient DB access in SQLite
93 applications. In the constructor, a :py:obj:`SQLiteProperties` instance is
94 already aggregated under ``self.properties``."""
96 DDL_CREATE_TABLES: dict[str, str] = {}
99 """As soon as changes are made to the DB schema, the version number must be
100 increased. Changes to the version number require the DB to be recreated (or
101 migrated / if an migration path exists and is implemented)."""
103 SQLITE_THREADING_MODE: str = {
106 3:
"serialized"}[sqlite3.threadsafety]
107 """Threading mode of the SQLite library. Depends on the options used at
108 compile time and is different for different distributions and architectures.
110 Possible values are 0:``single-thread``, 1:``multi-thread``,
111 3:``serialized`` (see :py:obj:`sqlite3.threadsafety`). Pre- Python 3.11
112 this value was hard coded to 1.
114 Depending on this value, optimizations are made, e.g. in “serialized” mode
115 it is not necessary to create a separate DB connector for each thread.
118 SQLITE_JOURNAL_MODE: str =
"WAL"
119 """``SQLiteAppl`` applications are optimized for WAL_ mode, its not recommend
120 to change the journal mode (see :py:obj:`SQLiteAppl.tear_down`).
122 .. _WAL: https://sqlite.org/wal.html
124 SQLITE_CONNECT_ARGS: dict[str,str|int|bool|
None] = {
127 "check_same_thread": bool(SQLITE_THREADING_MODE !=
"serialized"),
128 "cached_statements": 0,
130 "isolation_level":
None,
132 """Connection arguments (:py:obj:`sqlite3.connect`)
134 ``check_same_thread``:
135 Is disabled by default when :py:obj:`SQLITE_THREADING_MODE` is
136 ``serialized``. The check is more of a hindrance in this case because it
137 would prevent a DB connector from being used in multiple threads.
139 ``cached_statements``:
140 Is set to ``0`` by default. Note: Python 3.12+ fetch result are not
141 consistent in multi-threading application and causing an API misuse error.
143 The multithreading use in SQLiteAppl is intended and supported if
144 threadsafety is set to 3 (aka "serialized"). CPython supports “serialized”
145 from version 3.12 on, but unfortunately only with errors:
147 - https://github.com/python/cpython/issues/118172
148 - https://github.com/python/cpython/issues/123873
150 The workaround for SQLite3 multithreading cache inconsistency is to set
151 option ``cached_statements`` to ``0`` by default.
173 self.
_DB: sqlite3.Connection |
None =
None
176 f
"SQLite library is compiled with {self.SQLITE_THREADING_MODE} mode,"
177 " read https://docs.python.org/3/library/sqlite3.html#sqlite3.threadsafety"
179 if threading.active_count() > 1:
184 if sqlite3.sqlite_version_info <= (3, 35):
187 "SQLite runtime library version %s is not supported (require >= 3.35)", sqlite3.sqlite_version
192 conn.execute(f
"PRAGMA journal_mode={self.SQLITE_JOURNAL_MODE}")
197 """Creates a new DB connection (:py:obj:`SQLITE_CONNECT_ARGS`). If not
198 already done, the DB schema is set up. The caller must take care of
199 closing the resource. Alternatively, :py:obj:`SQLiteAppl.DB` can also
200 be used (the resource behind `self.DB` is automatically closed when the
201 process or thread is terminated).
203 if sys.version_info < (3, 12):
208 f
"[{threading.current_thread().ident}] {self.__class__.__name__}({self.db_url})"
209 f
" {self.SQLITE_CONNECT_ARGS} // {self.SQLITE_JOURNAL_MODE}"
218 """Create user-defined_ SQL functions.
220 ``REGEXP(<pattern>, <field>)`` : 0 | 1
221 `re.search`_ returns (int) 1 for a match and 0 for none match of
222 ``<pattern>`` in ``<field>``.
226 SELECT '12' AS field WHERE REGEXP('^[0-9][0-9]$', field)
229 SELECT REGEXP('[0-9][0-9]', 'X12Y')
231 SELECT REGEXP('[0-9][0-9]', 'X1Y')
234 .. _user-defined: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.create_function
235 .. _deterministic: https://sqlite.org/deterministic.html
236 .. _re.search: https://docs.python.org/3/library/re.html#re.search
239 conn.create_function(
"regexp", 2,
lambda x, y: 1
if re.search(x, y)
else 0, deterministic=
True)
242 def DB(self) -> sqlite3.Connection:
243 """Provides a DB connection. The connection is a *singleton* and
244 therefore well suited for read access. If
245 :py:obj:`SQLITE_THREADING_MODE` is ``serialized`` only one DB connection
246 is created for all threads.
250 For dedicated `transaction control`_, it is recommended to create a
251 new connection (:py:obj:`SQLiteAppl.connect`).
253 .. _transaction control:
254 https://docs.python.org/3/library/sqlite3.html#sqlite3-controlling-transactions
257 conn: sqlite3.Connection
269 conn = DBSession.get_connect(self)
271 conn = DBSession.get_connect(self)
280 def init(self, conn: sqlite3.Connection) -> bool:
281 """Initializes the DB schema and properties, is only executed once even
282 if called several times.
284 If the initialization has not yet taken place, it is carried out and a
285 `True` is returned to the caller at the end. If the initialization has
286 already been carried out in the past, `False` is returned.
293 logger.debug(
"init DB: %s", self.
db_url)
303 raise sqlite3.DatabaseError(
"Expected DB schema v%s, DB schema is v%s" % (self.
DB_SCHEMA, ver))
304 logger.debug(
"DB_SCHEMA = %s", ver)
310 logger.debug(
"create schema ..")
316 self.
properties.set(f
"Table {table_name} created", table_name)
320 """Simple class to manage properties of a DB application in the DB. The
321 object has its own DB connection and transaction area.
325 CREATE TABLE IF NOT EXISTS properties (
328 m_time INTEGER DEFAULT (strftime('%s', 'now')),
333 SQLITE_JOURNAL_MODE: str =
"WAL"
335 DDL_PROPERTIES: str =
"""\
336CREATE TABLE IF NOT EXISTS properties (
339 m_time INTEGER DEFAULT (strftime('%s', 'now')), -- last modified (unix epoch) time in sec.
340 PRIMARY KEY (name))"""
342 """Table to store properties of the DB application"""
344 SQL_GET: str =
"SELECT value FROM properties WHERE name = ?"
345 SQL_M_TIME: str =
"SELECT m_time FROM properties WHERE name = ?"
347 "INSERT INTO properties (name, value) VALUES (?, ?)"
348 " ON CONFLICT(name) DO UPDATE"
349 " SET value=excluded.value, m_time=strftime('%s', 'now')"
351 SQL_DELETE: str =
"DELETE FROM properties WHERE name = ?"
352 SQL_TABLE_EXISTS: str = (
353 "SELECT name FROM sqlite_master"
354 " WHERE type='table' AND name='properties'"
356 SQLITE_CONNECT_ARGS: dict[str, str | int | bool |
None] = dict(SQLiteAppl.SQLITE_CONNECT_ARGS)
365 def init(self, conn: sqlite3.Connection) -> bool:
366 """Initializes DB schema of the properties in the DB."""
371 logger.debug(
"init properties of DB: %s", self.
db_url)
373 if res.fetchone()
is None:
377 def __call__(self, name: str, default: t.Any =
None) -> t.Any:
378 """Returns the value of the property ``name`` or ``default`` if property
381 res = self.
DB.execute(self.
SQL_GET, (name,)).fetchone()
386 def set(self, name: str, value: str | int):
387 """Set ``value`` of property ``name`` in DB. If property already
388 exists, update the ``m_time`` (and the value)."""
391 self.
DB.execute(self.
SQL_SET, (name, value))
394 """Delete of property ``name`` from DB."""
399 def row(self, name: str, default: t.Any =
None):
400 """Returns the DB row of property ``name`` or ``default`` if property
403 res = self.
DB.execute(
"SELECT * FROM properties WHERE name = ?", (name,))
408 col_names = [column[0]
for column
in row.description]
409 return dict(zip(col_names, row))
411 def m_time(self, name: str, default: int = 0) -> int:
412 """Last modification time of this property."""
424 lines: list[str] = []
425 for row
in self.
DB.execute(
"SELECT name, value, m_time FROM properties"):
426 name, value, m_time = row
427 m_time = datetime.datetime.fromtimestamp(m_time).strftime(
"%Y-%m-%d %H:%M:%S")
428 lines.append(f
"[last modified: {m_time}] {name:20s}: {value}")
429 return "\n".join(lines)
sqlite3.Connection|None _conn
__init__(self, "SQLiteAppl" app)
sqlite3.Connection get_connect(cls, "SQLiteAppl" app)
sqlite3.Connection conn(self)
sqlite3.Connection _connect(self)
sqlite3.Connection|None _DB
sqlite3.Connection connect(self)
__init__(self, str db_url)
SQLiteProperties properties
bool init(self, sqlite3.Connection conn)
create_schema(self, sqlite3.Connection conn)
register_functions(self, sqlite3.Connection conn)
dict SQLITE_THREADING_MODE
sqlite3.Connection DB(self)
create_schema(self, sqlite3.Connection conn)
int delete(self, str name)
set(self, str name, str|int value)
row(self, str name, t.Any default=None)
int m_time(self, str name, int default=0)
__init__(self, str db_url)
t.Any __call__(self, str name, t.Any default=None)
bool init(self, sqlite3.Connection conn)