SQLite Forum

Latest .expert fails when any table schema includes extension function
Login

Latest .expert fails when any table schema includes extension function

(1) By SeverKetor on 2021-08-01 03:21:17 [link] [source]

I was trying to use .expert earlier when I found out that if you have a function from an extension in any table's schema, it just does not work. Typing .expert would just immediately result in "sqlite3_expert_new: no such function: {function name}" even when the extension the function comes from is loaded.

Maybe that error is necessary when the function would be called during the query, but in my case I have generated columns on tables which are stored. It shouldn't matter if my SELECT query references one of those columns since it already has the value. It especially shouldn't matter if I'm not even referencing one of those tables, but the .expert call doesn't even get to that point before failing.

Example:

SQLite version 3.37.0 2021-07-31 20:30:41
Enter ".help" for usage hints.
sqlite> .load ./Extensions/sql_functions
sqlite> .expert
sqlite3_expert_new: no such function: IP_TO_INT
sqlite> SELECT IP_TO_INT('127.0.0.1');
2130706433

As a workaround I was able to just create an in-memory version of the database with the generated columns replaced by regular columns, but I figured I should mention it here.

(2) By Larry Brasfield (larrybr) on 2021-08-01 17:13:11 in reply to 1 [link] [source]

Because the .expert facility creates new database connections to achieve some of its functionality, those extension functions need to be available for those connections too, along with the one you explicitly create.

The extension load/usage options described in Automatically Load Statically Linked Extensions and Persistent Loadable Extensions can be used to arrange that availability and cure the problem you see without having to modify your schema.

(3) By SeverKetor on 2021-08-01 20:02:33 in reply to 2 [link] [source]

Huh, neat. Thanks for letting me know.

I think it might be worth mentioning this in the .expert documentation. Just a message like "Note: ".expert" creates new database connections which will not retain loaded extensions unless they are Automatically Load Statically Linked Extensions or Persistent Loadable Extensions. A database schema that uses functions from an extension that is not persistent will cause the ".expert" command to fail"

On the topic of the .expert docs, I just spotted a missing 'e' in the last line. "Th functionality described[...]"

(4) By Larry Brasfield (larrybr) on 2021-08-01 21:06:14 in reply to 3 [source]

I agree that such improvements are due. I'll see to them soon.