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)[source]

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[source]

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

init()[source]

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

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

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:

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)[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))
init()[source]

Initializes DB schema of the properties in the DB.

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

Last modification time of this property.

row(name, default=None)[source]

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

set(name, value)[source]

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:

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