Skip to content
Back to top
View this page
Edit this page

# 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)

  A thead-local DB session

  classmethod get_connect(app: SQLiteAppl) → Connection

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

class searx.sqlitedb.SQLiteAppl(db_url: str)

  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: str = '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: str = 'WAL'

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

  SQLITE_CONNECT_ARGS: dict[str, str | float | int | bool | None] = {'cached_statements': 0, 'check_same_thread': False, 'timeout': 3.0}

    Connection arguments (`sqlite3.connect`)

    `check_same_thread`: bool

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

      Is enabled when threadsafety is `single-thread` or `multi-thread` (when threads cannot share a connection PEP-0249).

    `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:

      - https://github.com/python/cpython/issues/118172

      - https://github.com/python/cpython/issues/123873

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

    `isolation_level`: unset

      If the connection attribute isolation_level is not `None`, new transactions are implicitly opened before `execute()` and `executemany()` executes SQL- INSERT, UPDATE, DELETE, or REPLACE statements [1].

      By default, the value is not set, which means the default from Python is used: Python’s default is `""`, which is an alias for `"DEFERRED"`.

    `autocommit`: unset

      Starting with Python 3.12 the DB connection has a `autocommit` attribute and the recommended way of controlling transaction behaviour is through this attribute [2].

      By default, the value is not set, which means the default from Python is used: Python’s default is the constant LEGACY_TRANSACTION_CONTROL: Pre-Python 3.12 (non-PEP 249-compliant) transaction control, see `isolation_level` above for more details.

  connect() → Connection

    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: Connection)

    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

    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)

  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: str = 'WAL'

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

  DDL_PROPERTIES: str = "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: dict[str, str | int | bool | None] = {'cached_statements': 0, 'check_same_thread': False, 'timeout': 3.0}

    Connection arguments (`sqlite3.connect`)

    `check_same_thread`: bool

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

      Is enabled when threadsafety is `single-thread` or `multi-thread` (when threads cannot share a connection PEP-0249).

    `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:

      - https://github.com/python/cpython/issues/118172

      - https://github.com/python/cpython/issues/123873

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

    `isolation_level`: unset

      If the connection attribute isolation_level is not `None`, new transactions are implicitly opened before `execute()` and `executemany()` executes SQL- INSERT, UPDATE, DELETE, or REPLACE statements [1].

      By default, the value is not set, which means the default from Python is used: Python’s default is `""`, which is an alias for `"DEFERRED"`.

    `autocommit`: unset

      Starting with Python 3.12 the DB connection has a `autocommit` attribute and the recommended way of controlling transaction behaviour is through this attribute [2].

      By default, the value is not set, which means the default from Python is used: Python’s default is the constant LEGACY_TRANSACTION_CONTROL: Pre-Python 3.12 (non-PEP 249-compliant) transaction control, see `isolation_level` above for more details.

  init(conn: Connection) → bool

    Initializes DB schema of the properties in the DB.

  set(name: str, value: str | int)

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

  delete(name: str) → int

    Delete of property `name` from DB.

  row(name: str, default: Any = None)

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

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

    Last modification time of this property.