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

class searx.sqlitedb.SQLiteAppl(db_url)

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

  connect() → Connection

    Creates a new DB connection (`SQLITE_CONNECT_ARGS`). If not already done, the DB schema is set up

  init()

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

  register_functions(conn)

    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`).

  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_CONNECT_ARGS = {'autocommit': False, 'cached_statements': 0, 'check_same_thread': False}

    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.

    `autocommit`:

      Is disabled by default. Note: autocommit option has been added in Python 3.12.

    `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 ist to set option `cached_statements` to `0` by default.

  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.

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

  init()

    Initializes DB schema of the properties in the DB.

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

    Last modification time of this property.

  row(name, default=None)

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

  set(name, value)

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

  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 = {'autocommit': True, 'cached_statements': 0, 'check_same_thread': False}

    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.

    `autocommit`:

      Is disabled by default. Note: autocommit option has been added in Python 3.12.

    `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 ist to set option `cached_statements` to `0` by default.