Suppose I want to add a user-defined function that may perform remote
lookups. E.g., a function that maps user names, e-mail addresses, or
what have you to internal ID forms (SIDs, POSIX UIDs, GUIDs, etc...) by
asking a remote server to perform this mapping.
Now suppose I wanted to do something like:
INTERT INTO FOO SELECT lookup(a) FROM BAR;
This should cause SQLite to call the user-defined function as many times
as there are rows in BAR.
Which would result is as many round-trips to the remote server, which
would be very slow.
Preferably one could batch up many calls to this function so that fewer
round-trips to the server should be needed.
I can't see a way to do this now, so I'm wondering if such a facility
could be added to SQLite.
[I expect some will answer "don't make user-defined functions that block
on I/O." Yes, I agree. But I have relational data some of which can
come from remote servers and which can't easily be pre-populated into
tables on a local DB; it'd be ever so much more comfortable to have one
way of handling such data regardless of its source than to have to write
code that batches such lookups and the writes multiple INSERT INTO
statements to store the results. A reaonable way of dealing with
timeouts is needed, yes.]
The number of calls to batch could be specified like so:
INTERT INTO FOO SELECT lookup(a) BATCH 10 FROM BAR;
or
INTERT INTO FOO SELECT batch(lookup(a), 10) FROM BAR;
I can see several ways to design the interface for defining batch-eable
user functions. The simplest perhaps would be to overload the existing
sqlite3_create_function() as follows: batcheable functions would consist
of xFunc and xStep functions, but no xFinal function. SQLite would call
xFunc N times to pass arguments to the function for N calls to be
batched, then it would call xStep N times to retrieve the results of the
N calls; the xStep function would execute the N batched calls when it is
first called after a call to the xFunc.
I'm guessing the complexity here would be in the compiler, in making it
deal with "suspended" state, so that something like this
INTERT INTO FOO SELECT a, lookup(a) BATCH 10 FROM BAR;
uses a temporary table to store the 'a' column of every 10 records from
BAR, and a termporary table to store the results of the corresponding
batched function calls for 'lookup(a)', followed by a join of the two
tables to produce N rows of the form {a, lookup(a)}.
Nico
--
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
