about summary refs log tree commit diff
diff options
context:
space:
mode:
authorFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-10-28 15:06:07 -0500
committerFrederick Muriuki Muriithi <fredmanglis@gmail.com>2026-10-28 15:06:07 -0500
commitcf930d1335d894b468e8e0d8f600a50515ecee36 (patch)
treeaa9cf89a1ff7717a2169af7f3204b73db24061e0
parent6c67a0ad5bb5fe18722556a2824921c9e6a28e87 (diff)
downloadgn-libs-cf930d1335d894b468e8e0d8f600a50515ecee36.tar.gz
Enable passing PRAGMA directives on connection string.
Enable passing of PRAGMA directives on the connection string to enable
setting different directives for different environments.
-rw-r--r--gn_libs/sqlite3.py58
-rw-r--r--tests/unit/test_sqlite3.py56
2 files changed, 112 insertions, 2 deletions
diff --git a/gn_libs/sqlite3.py b/gn_libs/sqlite3.py
index c8bef0d..0af160a 100644
--- a/gn_libs/sqlite3.py
+++ b/gn_libs/sqlite3.py
@@ -2,7 +2,9 @@
 import logging
 import traceback
 import contextlib
+from functools import reduce
 from typing import Callable, Iterator, Any
+from urllib.parse import urlparse, urlencode
 
 import sqlite3
 
@@ -11,14 +13,66 @@ from .protocols import DbCursor, DbConnection
 _logger_ = logging.getLogger(__name__)
 
 
+def _process_query(query_str: str) -> tuple[dict[str, str], dict[str, str], dict[str, str]]:
+    """Process the query parameters into 3 mappings
+
+    1. Standard, known, VFS layer parameters (if present),
+    2. Pragma directives: custom to our system (if present),
+    3. Anything that's not one of the two above.
+
+    :param query_str: The query string from the provided connection string/path
+    :type query_str: str
+
+    :returns: 3 mappings (standard VSF mappings, PRAGMA Directives, Others).
+    """
+    _vfs_params = ("cache", "immutable", "mode", "modeof", "nolock", "psow", "vfs")
+    def _organise(current, item):
+        _key, _val = item
+        if _key in _vfs_params:
+            return ({**current[0], _key: _val},) + current[1:]
+        if _key.startswith("pragma_"):
+            return (current[0], {**current[1], _key[len("pragma_"):]: _val}, current[2])
+        return current[:-1] + ({**current[-1], _key: _val},)
+
+    return reduce(_organise,
+                  ((item[0].strip().lower(), item[1].strip())
+                   for item in
+                   (param.split("=") for param in query_str.split("&"))
+                   if len(item) > 1),
+                  ({}, {}, {}))
+
+
+def _process_connection_string(db_path: str) -> tuple[str, dict[str, str]]:
+    """Process the path provided to ensure it is clean for SQLite.
+
+    :param db_path: A path or connection string to a SQLite3 database
+    :type db_path: str
+
+    :returns tuple: connection string and mapping of PRAGMA directives."""
+    if db_path.strip() == "":
+        raise ValueError("Connection string cannot be empty.")
+    uri = urlparse(db_path.strip())
+    _vfs, _pragmas, _others = _process_query(uri.query)
+    if uri.path.startswith("memdb") or uri.path == ":memory:":
+        return f"file:{uri.path}?{urlencode({**_vfs, **_others})}".strip("?"), _pragmas
+    return (
+        uri._replace(
+            scheme="file", query=urlencode({**_vfs, **_others})).geturl(),
+        _pragmas)
+
+
 @contextlib.contextmanager
 def connection(db_path: str, row_factory: Callable = sqlite3.Row) -> Iterator[DbConnection]:
     """Create the connection to the auth database."""
     logging.debug("SQLite3 DB Path: '%s'.", db_path)
-    conn = sqlite3.connect(db_path)
+    _url, _pragmas = _process_connection_string(str(db_path))
+    conn = sqlite3.connect(_url, uri=True)
     conn.row_factory = row_factory
     conn.set_trace_callback(logging.debug)
-    conn.execute("PRAGMA foreign_keys = ON")
+    conn.execute("PRAGMA foreign_keys = ON;")
+    for _pragma, _val in _pragmas.items():
+        conn.execute(f"PRAGMA {_pragma} = {_val};")
+
     try:
         yield conn
     except sqlite3.Error as exc:
diff --git a/tests/unit/test_sqlite3.py b/tests/unit/test_sqlite3.py
new file mode 100644
index 0000000..343f1e5
--- /dev/null
+++ b/tests/unit/test_sqlite3.py
@@ -0,0 +1,56 @@
+"""Test functions used with SQLite3 databases"""
+import pytest
+
+from gn_libs.sqlite3 import _process_query, _process_connection_string
+
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "query,expected",
+    (("", ({}, {}, {})),
+     ("cache=on",
+      ({"cache": "on"}, {}, {})),
+     ("cache=on&pragma_foreign_keys=on",
+      ({"cache": "on"}, {"foreign_keys": "on"}, {})),
+     ("cache=on&pragma_foreign_keys=on&unknown=1",
+      ({"cache": "on"}, {"foreign_keys": "on"}, {"unknown": "1"}))))
+def test__process_query_splits_query_parameters_correctly(query, expected):
+    """Test that _process_query splits the query string parameters correctly.
+
+    GIVEN: the query part of a valid url string,
+    WHEN: _process_query(...) is called with the query string
+    THEN: ensure the result is 3 mappings, VFS options, PRAGMA directives and
+          unknown options."""
+    assert _process_query(query) == expected
+
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "emptystr", ("", " ", "   "))
+def test__process_connection_string_raises_with_empty_string(emptystr):
+    """Test that _process_connection_string does not accept empty connection strings.
+
+    GIVEN: An empty string for a connection string
+    WHEN: that is passed to _process_connection_string
+    THEN: a ValueError is raised"""
+    with pytest.raises(ValueError):
+        _process_connection_string(emptystr)
+
+
+@pytest.mark.unit_test
+@pytest.mark.parametrize(
+    "configstr,expected",
+    ((":memory:", ("file::memory:", {})),
+     (":memory:?cache=private", ("file::memory:?cache=private", {})),
+     (":memory:?cache=private&pragma_temp_store=memory",
+      ("file::memory:?cache=private", {"temp_store": "memory"})),
+     ("/home/user/datastore.db?cache=private&pragma_temp_store=memory",
+      ("file:///home/user/datastore.db?cache=private", {"temp_store": "memory"}))))
+def test__process_connection_string_returns_correct_values(configstr, expected):
+    """Test that _process_connection_string parses the connection strings correctly.
+
+    GIVEN: A connection string to an SQLite3 database,
+    WHEN: _process_connection_string(...) is called with the connection string,
+    THEN: Ensure the connection string returned is valid, and the PRAGMA
+          directives are extracted ."""
+    assert _process_connection_string(configstr) == expected