[fix] sqlitedb: create DB Schema (DDL) during app initialization (hardening) (#6187)

The initialization of the DB schema ("base schema") has so far been done on
demand, which causes race conditions with competing threads and processes.

The DDL statements for creating the "base schema" are now executed as part of
the initialization of the app.

Further improvements were made to harden the database applications:

- Wikidata & Radio-Browser engine perform their initialization only once (so far
  the initialization was carried out in each thread/process).

- If multiple processes try to set DB's WAL mode when opening the DB at the same
  time, this usually leads to another race condition, which is now also caught.

Related:

- https://github.com/searxng/searxng/issues/6181#issuecomment-4586705

Closes: #6181

Signed-off-by: Markus Heiser <markus.heiser@darmarit.de>

1 files changed, 8 insertions, 5 deletions

diff --git a/searx/cache.py b/searx/cache.py
index f7c256078..e6b6541d9 100644
--- a/searx/cache.py
+++ b/searx/cache.py
@@ -444,12 +444,10 @@ class ExpireCacheSQLite(sqlitedb.SQLiteAppl, ExpireCache):
     def get(self, key: str, default: typing.Any = None, ctx: str | None = None) -> typing.Any:
         """Get value of ``key`` from table given by argument ``ctx``.  If
         ``ctx`` argument is ``None`` (the default), a table name is generated
-        from the :py:obj:`ExpireCacheCfg.name`.  If ``key`` not exists (in
-        table), the ``default`` value is returned.
-
+        from the :py:obj:`ExpireCacheCfg.name`.  If ``key`` not exists in
+        the table or the table not exists, the ``default`` value is returned.
         """
         table = ctx
-        self.maintenance()
 
         if not table:
             table = self.normalize_name(self.cfg.name)
@@ -457,6 +455,9 @@ class ExpireCacheSQLite(sqlitedb.SQLiteAppl, ExpireCache):
         if table not in self.table_names:
             return default
 
+        # Before values are taken from the table, a maintenance interval may
+        # need to be carried out.
+        self.maintenance()
         sql = f"SELECT value FROM {table} WHERE key = ?"
         row = self.DB.execute(sql, (key,)).fetchone()
         if row is None:
@@ -469,12 +470,14 @@ class ExpireCacheSQLite(sqlitedb.SQLiteAppl, ExpireCache):
         If ``ctx`` argument is ``None`` (the default), a table name is
         generated from the :py:obj:`ExpireCacheCfg.name`."""
         table = ctx
-        self.maintenance()
 
         if not table:
             table = self.normalize_name(self.cfg.name)
 
         if table in self.table_names:
+            # Before values are taken from the table, a maintenance interval may
+            # need to be carried out.
+            self.maintenance()
             for row in self.DB.execute(f"SELECT key, value FROM {table}"):
                 yield row[0], self.deserialize(row[1])