On Wed, Oct 16, 2013 at 1:28 PM, Richard Hipp <d...@sqlite.org> wrote:
> Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75 Thanks. Being familiar with vtables, I had imagined as much, even though stopped short of doing it in practice. This takes care of hiding the DML statements from auth/trace hooks, by inserting/deleting behind the scene in native code. But it does not hide from SQL the DDL for creating the vtable or dropping it OTOH. Those will be seen by hooks still. I realize I'm pushing my luck here Dr Hipp, but thinking about this more, what I think SQLite is missing is some kind of unprotected sqlite3_table_value, a subtype of sqlite3_value, with APIs to define the columns, and fill in the values of a "table value". Once you have such a beast, you can bind such "table value" for the << in %1 >> case I was describing above, since there's already sqlite3_bind_value(). But you can also now create "table functions", i.e. custom SQLite functions that do not return scalars but anonymous temporary "tables", returning these sqlite3_table_values via sqlite3_result_value(). vtables can already do pretty much the same thing, except that * vtables cannot be used "inline" to a given statement (i.e. created on the fly), and * vtables cannot be used in an anonymous manner (the vtable must have a name) * vtables cannot dynamically process "document cells" that belong to other tables (real or virtual) in a statement. (you can explicit insert stuff into them like FTS does, but it's more a custom index than a custom table). You can easily create a virtual table that parses a comma separated list, and return one row per string between commans, but you must name the vtable, and pass it the comma separated list explicitly, "hardcoding" its rows. But if you make it a table function, you can select from that function, passing arbitrary strings to parse, each time returning a new unnamed result-table (i.e. a table), and you're not limited to literals, you can also "join" to another table to process specific strings (in a given column) of that other table and have an implicit union-all of those anonymous per-string-value result-sets. In pseudo-code, this would look something like this: sqlite> select * from parse_csv('a, b, a'); a b a sqlite> create table s (name text, csv text); sqlite> insert into s values ('dec', '1, 2, 3'), ('hex', '1, A'), ('alpha', 'a, B, TT'); sqlite> select s.name, p* from s, parse_csv(s.csv) p; dec|1 dec|2 dec|3 hex|1 hex|A alpha|a alpha|B alpha|TT sqlite> select s.name, count(parse_csv(s.csv)) from s; dec|3 hex|2 alpha|3 With such table functions, you can imagine all sorts of interesting scenarios, like getting info out of XML or JSON documents stored in table cells (UnQL anyone?), or getting a list of doubles as rows from a blob cell value (since SQLite lacks array support, any user-defined-type is basically a blob or a string that aggregate denormalized info). OK, it's probably fever-induced wandering. I'll stop there ;) --DD Oracle XMLTable http://stackoverflow.com/questions/12690868 http://www.oracle-base.com/articles/misc/pipelined-table-functions.php PS: BTW, this taught me the << in table >> alternative to << in (list) >> I was not aware of. Thanks for that. sqlite> create table t (name text, type text); sqlite> insert into t values ('foo', 'en'), ('bar', 'en'); sqlite> insert into t values ('toto', 'fr'), ('titi', 'fr'); sqlite> insert into t values ('furtch', 'gr'); sqlite> create table type_sel (type text); sqlite> insert into type_sel values ('gr'), ('fr'); sqlite> select * from t where type in type_sel; toto|fr titi|fr furtch|gr sqlite> delete from type_sel; sqlite> insert into type_sel values ('en'); sqlite> select * from t where type in type_sel; foo|en bar|en _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
