STRICT tables seem to not be supported

[nguiard]

Hi,
As of version 3.37.0 (2021-11-27), SQLite supports STRICT tables. However, the LiteCLI client seems to choke on tables defined this way.

Steps to reproduce:

• have sqlite3 version >= 3.37.0
• sqlite3 test.db
• sqlite> CREATE TABLE u (name TEXT) STRICT;
• close sqlite3
• litecli test.db

This last command then gives:

xception in thread completion_refresh:
Traceback (most recent call last):
  File "/usr/lib/python3.9/threading.py", line 954, in _bootstrap_inner
    self.run()
  File "/usr/lib/python3.9/threading.py", line 892, in run
    self._target(*self._args, **self._kwargs)
  File "/usr/local/lib/python3.9/dist-packages/litecli/completion_refresher.py", line 77, in _bg_refresh
    refresher(completer, executor)
  File "/usr/local/lib/python3.9/dist-packages/litecli/completion_refresher.py", line 108, in refresh_databases
    completer.extend_database_names(executor.databases())
  File "/usr/local/lib/python3.9/dist-packages/litecli/sqlcompleter.py", line 295, in extend_database_names
    self.databases.extend(databases)
  File "/usr/local/lib/python3.9/dist-packages/litecli/sqlexecute.py", line 180, in databases
    for row in cur.execute(self.databases_query):
sqlite3.DatabaseError: malformed database schema (u) - near "strict": syntax error

[bjornasm]

Hey, I think this can be related to which sqlite3 versions you have on your system, and which litecli defaults to use. See here: https://discuss.python.org/t/how-to-use-the-new-sqlite-strict-keyword-with-python-3/13322/2

The following works for me:

litecli test.db
test.db> CREATE TABLE u (name TEXT) STRICT;
Query OK, 0 rows affected
Time: 0.013s

However this does not:

sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> CREATE TABLE u (name TEXT) STRICT;
Error: near "STRICT": syntax error

However the latter was done outside an environment, where the working example was inside an environment. Which litecli version are you on?

Interesting enough, even inside the environment this does not work:

sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> .tables
Error: malformed database schema (u) - near "STRICT": syntax error

Litecli uses pythons sqlite3 extension module which in my python, version 3.11 in my environment, requires SQLite 3.7.15 or newer. Outside of my environment my python is version 3.8.3. My guess is that in your case it uses the sqlite shipped with python3.9, which possibly will be an older version of SQLite than what you run from your terminal. What do you see in usr/lib/python3.9/sqlite3/__init.py__ (or wherever you have your python).

In any case I think installing Litecli from a newer python version, python3.11 -m pip install litecli (this is quite easier inside an venv) should solve this problem. Solution test:

Python 3.8

python3 -m venv "python38venv"
source python38venv/bin/activate
python -m pip install litecli
litecli test.db
test.db> CREATE TABLE u (name TEXT) STRICT;
near "STRICT": syntax error

Python 3.11

python3.11 -m venv "python311venv"
source python311venv/bin/activate
python -m pip install litecli
litecli test.db
test.db> CREATE TABLE u (name TEXT) STRICT;
Query OK, 0 rows affected
Time: 0.012s

Sorry for the rambling answer :)

[kracekumar]

Thanks for the answer @bjornasm, it was useful for me to test and verify.

The current litecli supports python version from 3.9 onwards. python 3.8 is not supported but you may be still able to install from PyPI

I tested the strict syntax in Python 3.8, 3.9 as well it worked.

Here is the example

uvx  --python 3.8 litecli  test.db
LiteCli: 1.13.2 (SQLite: 3.46.0)
GitHub: https://github.com/dbcli/litecli
test.db> CREATE TABLE u2 (name TEXT) STRICT;
Query OK, 0 rows affected
Time: 0.002s
test.db>
Goodbye!
kracekumar@Kraces-MacBook-Pro ~/code> uvx  --python 3.8 litecli  test.db
LiteCli: 1.13.2 (SQLite: 3.46.0)
GitHub: https://github.com/dbcli/litecli
test.db> .schema
+------------------------------------+
| sql                                |
+------------------------------------+
| CREATE TABLE u (name TEXT) STRICT  |
| CREATE TABLE u1 (name TEXT) STRICT |
| CREATE TABLE u2 (name TEXT) STRICT |
+------------------------------------+
Time: 0.009s
test.db>
kracekumar@Kraces-MacBook-Pro ~/code> uvx  --python 3.9 litecli  test.db
LiteCli: 1.14.4 (SQLite: 3.47.1)
GitHub: https://github.com/dbcli/litecli
test.db> CREATE TABLE u4 (name TEXT) STRICT;
Query OK
Time: 0.002s
test.db> .schema
+------------------------------------+
| sql                                |
+------------------------------------+
| CREATE TABLE u (name TEXT) STRICT  |
| CREATE TABLE u1 (name TEXT) STRICT |
| CREATE TABLE u2 (name TEXT) STRICT |
| CREATE TABLE u3 (name TEXT) STRICT |
| CREATE TABLE u4 (name TEXT) STRICT |
+------------------------------------+
Time: 0.010s
test.db>

thanks for reporting the issue @nguiard and the latest python version should solve your issue.