[BUG] squlite_ns bug with recent sqlite packages

[csoeller]

Describe the bug
Starting PYMEClusterOfOne fails with recent sqlite packages (>3.45 or similar).

To Reproduce
Steps to reproduce the behavior:

1. Start PYMEClusterOfOne
2. See error

Error message

INFO:root:Serving from directory: /Users/userXXX/PYMEData
Traceback (most recent call last):
  File "/Users/userXXX/Documents/src/PYMEsrc/PYME-test-env/build-test-py3.10-conda_plugint2/python-microscopy-working-jan-2025/PYME/misc/sqlite_ns.py", line 125, in getNS
    ns = nsd[protocol]
KeyError: '_pyme-http'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/userXXX/miniconda3/envs/test-pyme-3.10-conda_plugint2/lib/python3.10/runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/Users/userXXX/miniconda3/envs/test-pyme-3.10-conda_plugint2/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "/Users/userXXX/Documents/src/PYMEsrc/PYME-test-env/build-test-py3.10-conda_plugint2/python-microscopy-working-jan-2025/PYME/cluster/HTTPDataServer.py", line 1063, in <module>
    main()
  File "/Users/userXXX/Documents/src/PYMEsrc/PYME-test-env/build-test-py3.10-conda_plugint2/python-microscopy-working-jan-2025/PYME/cluster/HTTPDataServer.py", line 984, in main
    ns = sqlite_ns.getNS('_pyme-http')
  File "/Users/userXXX/Documents/src/PYMEsrc/PYME-test-env/build-test-py3.10-conda_plugint2/python-microscopy-working-jan-2025/PYME/misc/sqlite_ns.py", line 127, in getNS
    ns = SQLiteNS(protocol)
  File "/Users/userXXX/Documents/src/PYMEsrc/PYME-test-env/build-test-py3.10-conda_plugint2/python-microscopy-working-jan-2025/PYME/misc/sqlite_ns.py", line 39, in __init__
    tableNames = [a[0] for a in conn.execute('SELECT name FROM sqlite_master WHERE type="table"').fetchall()]
sqlite3.OperationalError: no such column: "table" - should this be a string literal in single-quotes?
...

Patch to fix
The patch below fixes the behaviour and is in line with sqlite string literal requirements (see also this discussion). Tested ok on sqlite 3.45.1 (pre-quoting change) and 3.49.1 (post-quoting change).

diff --git a/PYME/misc/sqlite_ns.py b/PYME/misc/sqlite_ns.py
index b94c4a0..7704340 100644
--- a/PYME/misc/sqlite_ns.py
+++ b/PYME/misc/sqlite_ns.py
@@ -35,8 +35,9 @@ class SQLiteNS(object):
         self._protocol = protocol
         self._dbname = os.path.join(tempfile.gettempdir(), '%s.sqlite' %self._protocol)
         with sqlite3.connect(self._dbname) as conn:
-
-            tableNames = [a[0] for a in conn.execute('SELECT name FROM sqlite_master WHERE type="table"').fetchall()]
+            # sqlite wants single quotes around literal strings and the sqlite package just became pickier about this
+            # see also https://www.reddit.com/r/freebsd/comments/1chb82b/sqlite3_pkg_just_became_stricter_with_quoting/
+            tableNames = [a[0] for a in conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()]
             if not 'dns' in tableNames:
                 try:
                     conn.execute("CREATE TABLE dns (name TEXT, address TEXT, port INTEGER, creation_time FLOAT, URI TEXT)")

Platform (please complete the following information - or copy-paste from error dialog if shown):
Checked with latest PYME from github and sqlite versions as above.

[csoeller]

PS - issues with newer module versions are generally encountered when building new installs with PYME-test-env. Also in this case when helping a visitor from Otago to install a recent PYME build.

[csoeller]

close with #1579 merged