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 underself.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 | 1re.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
isserialized
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
isserialized
. 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
to0
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
(seesqlite3.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))
- row(name, default=None)[source]¶
Returns the DB row of property
name
ordefault
if property not exists in DB.
- set(name, value)[source]¶
Set
value
of propertyname
in DB. If property already exists, update them_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
isserialized
. 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
to0
by default.