On Fri, Jan 14, 2011 at 10:53:56AM -0600, Nicolas Williams wrote:
> But that function would have to track those changed rowids somewhere.
> The trigger I posted does exactly that, using SQLite3's own primitives
> (a temp table in this case):
Speaking of which, the lack of procedural programming in SQLite3 is not
really a barrier, and it's not necessarily the case that it's best to
code procedural logic in C, Tcl, Python, or... With a little ingenuity
one can realize that SQLite3 provides much of what is needed for
procedural programming:
- variables? -> INSERT/SELECT on a special-purpose table, preferably a
TEMP one;
- conditionals? -> Where you need "IF" statements use WHEN clauses on
TRIGGERS or INSERTs/UPDATEs/DELETEs with WHERE
clauses having your conditional expression, SELECT
CASE where you need something like the C ? :
operator;
- iteration? -> Unroll loops or use recursive TRIGGERS (either way
you have a fairly tight maximum number of
iterations);
- recursion? -> Use recursive TRIGGERS, see above;
- functions? -> Use VIEWs for side-effect free functions that
require no iteration, else INSERT into some table
with a trigger that computes the function.
I will grant that writing procedural code in that fashion will result in
a fair amount of disjointed looking SQL. But that can happen in any
language (e.g., when one must write callback functions in languages
lacking closures). If you can keep the code size manageable then for
some problems you might be able to keep the SQL code size significantly
smaller than the non-SQL code size; SQL is quite expressive.
For example, I just implemented a loop detection feature that works by
having a trigger on a table used by the app, that inserts into another a
row that kicks off a recursive search for references, inserting into
this same table each reference found in a path, and raising an exception
if duplicates are found. And that table is self-cleaning too. One
could use the same technique to implement recursive queries...
Would I recommend this? In most cases no -- there's too many problems:
a) limits on trigger recursion, b) readability/maintainability, c)
SQLite3 will almost certainly be slower than any other language for
implementing CPU-bound functions. You'll need to write code in C or
some other language anyways, thus you can implement procedural logic in
that language.
But sometimes the above can be handy. I'd recommend these techniques in
the case this thread is covering, for example, since they are far
simpler to use than it is to write a SQL function that has side-effects.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
