Cannot load extension library
=============================
I occasionally use math functions defined in the extension library [`extension-functions.c`](https://www.sqlite.org/contrib/download/extension-functions.c?get=25). The library needs to be loaded into SQLite.
The official `sqlite3` executable allows me to load the extension in two ways (unless compiled with `SQLITE_OMIT_LOAD_EXTENSION`):
```sql
sqlite> SELECT load_extension('/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib');
sqlite> .load '/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib'
```
`litecli` however doesn't call `sqlite3_enable_load_extension`, so extension loading is forbidden:
```sql
db> SELECT load_extension('/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib');
not authorized
```
One solution is to add a `.load` command like the `sqlite3` one. Here's a quick patch:
```diff
diff --git a/litecli/packages/special/dbcommands.py b/litecli/packages/special/dbcommands.py
index 7307ad2..2e4f622 100644
--- a/litecli/packages/special/dbcommands.py
+++ b/litecli/packages/special/dbcommands.py
@@ -149,3 +149,24 @@ def status(cur, **_):
footer.append("--------------")
return [(None, None, "", "\n".join(footer))]
+
+
+@special_command(
+ ".load",
+ ".load",
+ "Load an extension library.",
+ arg_type=PARSED_QUERY,
+ case_sensitive=True,
+)
+def load_extension(cur, arg, **_):
+ # Strip quotes around the path, if any.
+ if (arg.startswith('"') and arg.endswith('"')) or (
+ arg.startswith("'") and arg.endswith("'")
+ ):
+ path = arg[1:-1]
+ else:
+ path = arg
+ conn = cur.connection
+ conn.enable_load_extension(True)
+ conn.load_extension(path)
+ return [(None, None, None, "")]
```
This way we can load an extension library (with or without quotes):
```sql
db> .load /usr/local/opt/sqlite/lib/libsqlitefunctions.dylib
Time: 0.002s
db> .load "/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib"
Time: 0.000s
db> .load '/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib'
Time: 0.000s
db> SELECT SQRT(2);
+--------------------+
| SQRT(2) |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set
Time: 0.012s
db> .load /usr/local/opt/sqlite/lib/libxxx.dylib
dlopen(/usr/local/opt/sqlite/lib/libxxx.dylib.dylib, 10): image not found
```
I can turn the patch into a PR if the new command is deemed acceptable.
(Note that the signature of `.load` in `sqlite3` is `.load FILE ?ENTRY?`; ~however, Python's `sqlite3` doesn't seem to support loading a single entry, and I've never used the optional `ENTRY` either. Probably okay to keep it simple.~ I was mistaken: `ENTRY` here is an entry point function, usually something like `sqlite3_extension_init`, not a specific entry... Still, not really supported by Python's sqlite3 API — it's [hard coded as 0](https://github.com/python/cpython/blob/6d0254bae4d739b487fcaa76705a2d309bce8e75/Modules/_sqlite/connection.c#L1087).)
Sure, I'm talking about statements like
```sql
INSERT INTO user (first_name, last_name, email, registered_at) VALUES (?,?,?,?);
```
```sql
SELECT * FROM user WHERE email = ?;
```
```sql
SELECT * FROM user WHERE registered_at BETWEEN ? AND ?;
```
You know, parametrized SQL that is commonly used in language bindings (e.g. Python's [`sqlite3` module](https://docs.python.org/3/library/sqlite3.html)). `?` is just the simplest form, and probably the easiest to implement.
---
https://litecli.com/favorites/ gives this example:
\fs user_by_name select * from users where name = '$1'
It would be nice to be able to write
\fs user_by_name select * from users where name = ?
instead.