dbcli / dbcli/litecli

Using extension function in a VIEW causes exception to be printed

Open
#265 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
3.3k
Forks
95
PR merge metrics
No merged PRs in 30d

Description

With current git:
```sql
.load ./crypto -- included in popular sqlean extension for sqlite

CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT NOT NULL
);

INSERT INTO products (product_name) VALUES ('Wireless Mouse');

CREATE VIEW v_hashed_product_id AS
SELECT
hex(crypto_blake3(product_id))
FROM products;
```

Running this in the REPL prints out this exception:
```python
File "site-packages/litecli/completion_refresher.py", line 85, in _bg_refresh
refresher(completer, executor)
~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
File "site-packages/litecli/completion_refresher.py", line 128, in refresh_tables
table_cols = list(executor.table_columns())
File "site-packages/litecli/sqlexecute.py", line 197, in table_columns
cur.execute(self.table_columns_query)
~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such function: crypto_blake3
```
but the view is created and works as expected.

Furthermore, existing and then opening the db in litecli also prints this exception:
```text
$ litecli ./my.db
File "site-packages/litecli/completion_refresher.py", line 85, in _bg_refresh
refresher(completer, executor)
~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
File "site-packages/litecli/completion_refresher.py", line 128, in refresh_tables
table_cols = list(executor.table_columns())
File "site-packages/litecli/sqlexecute.py", line 197, in table_columns
cur.execute(self.table_columns_query)
~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^
sqlite3.OperationalError: no such function: crypto_blake3
my.db>
```

Here's a link to sqlean [binaries](https://github.com/nalgeon/sqlean/releases/latest) for testing.

Contributor guide

Open the contributing guide

Research direction

Start by examining `litecli/completion_refresher.py` and `litecli/sqlexecute.py`, especially the background refresh and `table_columns()` query mentioned in the traceback. Reproduce with a SQLite view that uses an extension function unavailable to the connection, or open a database with such a view. Done means the CLI no longer prints an exception during completion refresh while the view remains usable.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlite
Domain
cli, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
66/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.