This would make a good entry for an Obfuscated SQL contest. Well done John
On 19 December 2011 21:43, Nico Williams <n...@cryptonector.com> wrote: > You can do conditionals via WHERE clauses, as others have pointed out. > You can also use WHEN clauses on triggers. > > Think of it as <statement> IF <condition>. And remember that the > WHERE clause can refer to all sorts of things, including parameters > from the application (something like WHERE @foo = 1). > > You can do iteration via INSERT/UPDATE/DELETE if you have tables with > rows to iterate over. > > You can make sub-routines (that return no values but have > side-effects) out of INSERTs on VIEWs or TABLEs with triggers that > will use the new row(s) as arguments, and but leave the VIEW/TABLE > itself unaltered. In the case of a TABLE you'd use a BEFORE INSERT > trigger that does a SELECT RAISE(IGNORE) at the end to make sure the > TABLE is left unaltered. > > You can use recursive triggers to implement recursion. > > This is really an abuse of SQL, of course, but it can be done :) And, > of course, you have SQLite3's various limits to keep in mind (e.g., on > recursion depth). > > I've done this sort of thing as a proof of concept, for the fun of it. > > Factorial() might be implemented as follows: > > PRAGMA recursive_triggers = 1; > CREATE TABLE factorials (n INTEGER PRIMARY KEY, n_factorial INTEGER); > CREATE VIEW factorial AS SELECT n, n_factorial FROM factorials; > CREATE TRIGGER factorial_ins INSTEAD OF INSERT ON factorial BEGIN > SELECT RAISE(FAIL, "n must be non-negative") WHERE NEW.n < 0; > SELECT RAISE(IGNORE) WHERE EXISTS (SELECT f.n FROM factorials f > WHERE f.n = NEW.n); > INSERT INTO factorials SELECT NEW.n, 1 WHERE NEW.n < 2 AND NOT > EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n); > INSERT INTO factorial SELECT NEW.n - 1, NULL WHERE NEW.n > 0 AND > NOT EXISTS (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1); > INSERT INTO factorials SELECT NEW.n, NEW.n * (SELECT f.n_factorial > FROM factorials f WHERE f.n = NEW.n - 1) WHERE NEW.n > 0 AND EXISTS > (SELECT f.n FROM factorials f WHERE f.n = NEW.n - 1) AND NOT EXISTS > (SELECT f.n FROM factorials f WHERE f.n = NEW.n); > END; > > sqlite> select * from factorial; > sqlite> insert into factorial select 5, null; > sqlite> select * from factorial; > 0|1 > 1|1 > 2|2 > 3|6 > 4|24 > 5|120 > sqlite> > > (With 64-bit signed integers you can compute up to 20!, but no further.) > > Here we have a trigger acting as a sub-routine that doesn't return any > results, but it does ensure that the results are available for a > subsequent query. Another way to think of it is as a method of > building recursive queries in the absence of recursive query support > in SQLite3. > > So there you have it: procedural programming in SQL. My advice: > follow the advice that you've been given, which is to say: do your > procedural programming in C or whatever host language you're using, > and use SQL as it was intended to be used. > > Cheers, > > Nico > -- > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
