SQLite DB

Implementations to make access to SQLite databases a little more convenient.

SQLiteAppl

Abstract class with which DB applications can be implemented.

SQLiteProperties:

Class to manage properties stored in a database.

Examplarical implementations based on SQLiteAppl:

searx.cache.ExpireCacheSQLite :

Cache that manages key/value pairs in a SQLite DB, in which the key/value pairs are deleted after an “expire” time. This type of cache is used, for example, for the engines, see searx.enginelib.EngineCache.

searx.favicons.cache.FaviconCacheSQLite :

Favicon cache that manages the favicon BLOBs in a SQLite DB.

class searx.sqlitedb.DBSession(app: SQLiteAppl)[source]

A thead-local DB session

classmethod get_connect(app: SQLiteAppl) → Connection[source]

Returns a thread local DB connection. The connection is only established once per thread.

class searx.sqlitedb.SQLiteAppl(db_url)[source]

Abstract base class for implementing convenient DB access in SQLite applications. In the constructor, a SQLiteProperties instance is already aggregated under self.properties.

DB_SCHEMA: int = 1

As soon as changes are made to the DB schema, the version number must be increased. Changes to the version number require the DB to be recreated (or migrated / if an migration path exists and is implemented).

SQLITE_THREADING_MODE = 'serialized'

Threading mode of the SQLite library. Depends on the options used at compile time and is different for different distributions and architectures.

Possible values are 0:single-thread, 1:multi-thread, 3:serialized (see sqlite3.threadsafety). Pre- Python 3.11 this value was hard coded to 1.

Depending on this value, optimizations are made, e.g. in “serialized” mode it is not necessary to create a separate DB connector for each thread.

SQLITE_JOURNAL_MODE = 'WAL'

SQLiteAppl applications are optimzed for WAL mode, its not recommend to change the journal mode (see SQLiteAppl.tear_down).

SQLITE_CONNECT_ARGS = {'cached_statements': 0, 'check_same_thread': False, 'isolation_level': None}

Connection arguments (sqlite3.connect)

check_same_thread:

Is disabled by default when SQLITE_THREADING_MODE is serialized. The check is more of a hindrance in this case because it would prevent a DB connector from being used in multiple threads.

cached_statements:

Is set to 0 by default. Note: Python 3.12+ fetch result are not consistent in multi-threading application and causing an API misuse error.

The multithreading use in SQLiteAppl is intended and supported if threadsafety is set to 3 (aka “serialized”). CPython supports “serialized” from version 3.12 on, but unfortunately only with errors:

The workaround for SQLite3 multithreading cache inconsistency ist to set option cached_statements to 0 by default.

connect() → Connection[source]

Creates a new DB connection (SQLITE_CONNECT_ARGS). If not already done, the DB schema is set up. The caller must take care of closing the resource. Alternatively, SQLiteAppl.DB can also be used (the resource behind self.DB is automatically closed when the process or thread is terminated).

register_functions(conn)[source]

Create user-defined SQL functions.

REGEXP(<pattern>, <field>)0 | 1

re.search returns (int) 1 for a match and 0 for none match of <pattern> in <field>.

SELECT '12' AS field WHERE REGEXP('^[0-9][0-9]$', field)
-- 12

SELECT REGEXP('[0-9][0-9]', 'X12Y')
-- 1
SELECT REGEXP('[0-9][0-9]', 'X1Y')
-- 0
property DB: Connection

Provides a DB connection. The connection is a singleton and therefore well suited for read access. If SQLITE_THREADING_MODE is serialized only one DB connection is created for all threads.

Note

For dedicated transaction control, it is recommended to create a new connection (SQLiteAppl.connect).

init(conn: Connection) → bool[source]

Initializes the DB schema and properties, is only executed once even if called several times.

If the initialization has not yet taken place, it is carried out and a True is returned to the caller at the end. If the initialization has already been carried out in the past, False is returned.

class searx.sqlitedb.SQLiteProperties(db_url: str)[source]

Simple class to manage properties of a DB application in the DB. The object has its own DB connection and transaction area.

CREATE TABLE IF NOT EXISTS properties (
  name       TEXT,
  value      TEXT,
  m_time     INTEGER DEFAULT (strftime('%s', 'now')),
  PRIMARY KEY (name))
SQLITE_JOURNAL_MODE = 'WAL'

SQLiteAppl applications are optimzed for WAL mode, its not recommend to change the journal mode (see SQLiteAppl.tear_down).

DDL_PROPERTIES = "CREATE TABLE IF NOT EXISTS properties (\n  name       TEXT,\n  value      TEXT,\n  m_time     INTEGER DEFAULT (strftime('%s', 'now')),  -- last modified (unix epoch) time in sec.\n  PRIMARY KEY (name))"

Table to store properties of the DB application

SQLITE_CONNECT_ARGS = {'cached_statements': 0, 'check_same_thread': False, 'isolation_level': None}

Connection arguments (sqlite3.connect)

check_same_thread:

Is disabled by default when SQLITE_THREADING_MODE is serialized. The check is more of a hindrance in this case because it would prevent a DB connector from being used in multiple threads.

cached_statements:

Is set to 0 by default. Note: Python 3.12+ fetch result are not consistent in multi-threading application and causing an API misuse error.

The multithreading use in SQLiteAppl is intended and supported if threadsafety is set to 3 (aka “serialized”). CPython supports “serialized” from version 3.12 on, but unfortunately only with errors:

The workaround for SQLite3 multithreading cache inconsistency ist to set option cached_statements to 0 by default.

init(conn: Connection) → bool[source]

Initializes DB schema of the properties in the DB.

set(name: str, value: str | int)[source]

Set value of property name in DB. If property already exists, update the m_time (and the value).

delete(name: str) → int[source]

Delete of property name from DB.

row(name: str, default=None)[source]

Returns the DB row of property name or default if property not exists in DB.

m_time(name: str, default: int = 0) → int[source]

Last modification time of this property.