On Tue, 5 Mar 2013 02:24:53 -0600
Nico Williams <n...@cryptonector.com> wrote:
> Lies! :) There's one more type of UDF in SQLite3: virtual tables.
> You could have a virtual table where selecting with an equality test
> for some column is "passing an argument" to a "table function". I
> know, I've done this. But it gets better! SQLite3 is re-entrant, so
> you can actually format a SQL statement in the virtual table given its
> "arguments" and execute it (being careful to not create a SQL
> injection vulnerability). IIRC FTS works this way.
Hmm, OK. I think you're right; this could work, even without parsing
any SQL. I'm imagining a virtual table like
CREATE VIRTUAL TABLE output
USING tfn( 'create table output ...', 'select ...' );
where the function executes the first argument to create a table,
solely to use the metadata to determine the names and types of its own
columns. (That saves reinventing a column-defintion language.) Then
it executes the SQL in the second argument, the output of which become
the rows in the table. It could even be executed recursively (3rd
argument).
> With a tiny bit of syntactic sugar we could have a way to define table
> functions given simple scalar functions, which would be very nice
> indeed.
I don't know if that's the route; perhaps the good doctor will offer
his views.
I think of a table function not as a disaggregator -- ick! -- but as a
parameterized view. Or, really, any function whose output can be
expressed as a relation using any combination of C and SQL.
Most "real" DBMSs have some form of stored procedure that's just a
named blob of SQL. Table functions could take the place of stored
procedures in SQLite in a more disciplined way. That would indeed be
very nice.
> +1 re: recursive queries. Once or twice I've resorted to a UNION ALL
> of LEFT OUTER self-JOINs, each sub-query having more and more
> self-joins -- this limits recursion depth effectively, but there's
> going to be a limit anyways.
I wrote a UDF that executes recursively and returns a string
indicating the number of recursions and total number of rows. You pass
in the query and target table (because we don't have table functions!)
and then select from it. That's not as bad as it sounds. Consider
for example a table "tree" representing a filesystem hierarchy:
create temporary table t
( p int -- parent
, c int -- child
, primary key (c,p)
);
insert into t
select * from tree
where inode in (
select p from t -- "recursion"
UNION
select inode from inodes -- seed
where filename = 'usr'
)
and not exists ( -- limit
select 1 from t
where p = pnode
and c = inode
)
can be invoked as
$ sqlite3 -list ../inodes.db "create temporary table t (p int, c int,
primary key (c,p)); select recurse('insert into t select tree.* from
tree where inode in (select p from t UNION select inode from inodes
where filename = ''usr'') and not exists (select 1 from t where p =
pnode and c = inode)'); \
select catname(parent_name, filename) \
from inodes where inode in \
(select c from t) \
order by catname(parent_name, filename);"
17 rows returned in 10 iterations
catname(parent_name, filename)
/usr/src/external
/usr/src/external/gpl3
/usr/src/external/gpl3/gcc
/usr/src/external/gpl3/gcc/dist
/usr/src/external/gpl3/gcc/dist/gcc
/usr/src/external/gpl3/gcc/dist/gcc/testsuite
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg/cpp
/usr/src/external/gpl3/gcc/dist/gcc/testsuite/gcc.dg/cpp/usr
/usr/src/gnu
/usr/src/gnu/dist
/usr/src/gnu/dist/gcc4
/usr/src/gnu/dist/gcc4/gcc
/usr/src/gnu/dist/gcc4/gcc/testsuite
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg/cpp
/usr/src/gnu/dist/gcc4/gcc/testsuite/gcc.dg/cpp/usr
Unlike a bunch of self-joins, this produces a correct result regardless
of depth; the recurse() function stops when an iteration produces zero
rows. But it bears the overhead of inserting into another table and
re-executing the query (and retesting the target) on every iteration.
Moving the recursion inside the query engine would avoid all that.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
