diff options
| author | Frederick Muriuki Muriithi <fredmanglis@gmail.com> | 2026-10-28 15:06:07 -0500 |
|---|---|---|
| committer | Frederick Muriuki Muriithi <fredmanglis@gmail.com> | 2026-10-28 15:06:07 -0500 |
| commit | cf930d1335d894b468e8e0d8f600a50515ecee36 (patch) | |
| tree | aa9cf89a1ff7717a2169af7f3204b73db24061e0 | |
| parent | 6c67a0ad5bb5fe18722556a2824921c9e6a28e87 (diff) | |
| download | gn-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.py | 58 | ||||
| -rw-r--r-- | tests/unit/test_sqlite3.py | 56 |
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 |
