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] -----------------------------------------------------------------------------