Hi,

I would like to share my recent experience with Sqlite. I mush admit that the trip with this great library sometimes leads to unexpected destinations.

So recently I had a suggestion for Sqlite and even started to write it down. But eventually I saw that almost the same can be achieved with the virtual tables so probably the suggestion is not so actual even for myself. But anyway, the idea was to allow parameters in views and allow such views to be used with a tabled-valued syntax to pass actual parameters. So having a view

Create view DateQuery as select * from SomeTable where Date between ?1 and ?2

would allow querying something like

Select * from DateQuery('2011-01-01', '2021-01-01')

But having later some negative arguments about this idea talking to myself, I saw that there's a probably a way to implement something like this with the existing virtual tables mechanism of Sqlite (using "eponymous-only" feature of them). In its simplest form, there's a two-column table having a name column and a parametrized query column. After the db is opened, the code scans this table and creates eponymous-only modules, one for every row of this table. Each module has the same name as the first column of this table and every schema for a single row is prepared using the information about the parameters of the query in the second column. The "columns"-parameters are hidden (for the sake of table-valued syntax), remaining columns are from the underlining query itself. So the example above migrates to a row in this table (instead of hypothetical parametrized view)

'DateQuery' | 'select * from SomeTable where Date between ?1 and ?2'

and the usage of this query stays the same

Select * from DateQuery('2011-01-01', '2021-01-01')

Now I can confirm that this method works, at least for several tests I made after implementing this meta-module. There are some obstacles though for the broad usage. For example, since Sqlite caches virtual table after the first use, in order to see instant changes, changing queries should avoid anything related to schema (or reopen database after such changes.) Also inserting into this table will require addtional sync operation and deleting from it might involve newer sqlite3_drop_modules api. But this also is fixable with the database reopening.