Suggestion: Allow binding Vec in SQLite with something like carray

[phiresky]

This is related to #656, #875, probably others. The problem is that Vec binding is currently only supported in PostgreSQL. In Sqlite this currently requires manually building a huge query string that can't by type-checked or cached.

SQLite includes a extension called carray that allows binding a list of values of primitives to a single bind parameter. It's basically the same thing as unnest() in PostgreSQL. It's implemented as an sqlite "virtual table" or table-valued-function and can be used like select * from foo where x in carray(?) or select * from carray(?) (for INSERTs). In Rust I guess either the C implementation could be integrated or a custom vtable could be registered. Rusqlite has an easy-to-use implementation of the same thing: https://docs.rs/rusqlite/0.24.2/rusqlite/vtab/array/index.html

[phiresky]

As a comparison, the integrated table valued function json_each is similar and can be used similarily (but requires serialization):

create table test (a,b);
insert into test select a.value,b.value from json_each('[1,2,3]') a, json_each('[1,2,3]') b where a.key = b.key;
-- the where condition is needed since otherwise it uses the cartesian product

select * from test where x in (select value from json_each('[2,3]'));

[Hans-Wu-cn]

I also encountered a similar problem. Using vec as a parameter is inconvenient when using the in keyword