On Fri, Mar 9, 2012 at 6:05 AM, Ryan Johnson <ryan.john...@cs.utoronto.ca> wrote: > Don't forget stored procedures.
I didn't need to pollute the language with procedural extensions in order to make my case, which is why I didn't mention stored procedures :) I figured using stored procs in my argument was going to detract from or at least distract people. Also, I don't care for stored procedures -- at least nowhere near as much as I care about the issue in this thread. As for stored procedures, I may not need them but you can emulate them via INSTEAD OF triggers on VIEWs. Stored procedure parameters then are just the columns of NEW :) and local variables can be rows in TEMP tables. You don't need IF ... THEN ... ELSE since you can achieve the same result with suitable WHERE ... AND <condition> on all the statements that go in the THEN/ELSE bodies. Looping can be achieved via recursive triggers or by using a table source to drive the equivalent of a loop in a SELECT. Calling stored procedures then becomes a matter of INSERTing into the corresponding VIEWs. A true IF..THEN..ELSE would make execution more efficient, but you can always cache the results of complex condition expressions in a TEMP table. This is the reason that I don't need stored procedures: if/when I find myself needing them I can just emulate them and it's only a slight inconvenience to have to do that. > I'd dearly love to have even a simple stored procedure language in > sqlite3... the VM supports *everything* it needs, even function call/return. And recursion, since it needs to in order to support recursive triggers. This means that recursive queries are also feasible with the SQLite3 VM. And you can emulate recursive queries via emulated stored procedures as described above :) > Triggers already connect multiple statements, joins open and iterate over > cursors, etc. There's just no parser support to expose the functionality in > a nice way. One could take the grammar for SQL from SQLite3 and write a re-writer that maps stored procedures onto SQLite3 as I describe above. This would require no changes to SQLite3. It would require changes to apps, but you might create wrappers around sqlite3_prepare*() and sqlite3_exec*() to avoid changing apps. > Perhaps a Google summer of code project or something? Perhaps. >> But, really, it's quite a hole that I can INSERT INTO table SELECT >> <complex query> but I can't UPDATE table<complex expressions>. > > ++, though I don't know of a non-kludgy syntax that would make this happen Allowing JOINs would be nice. But yes, INSERT..SELECT is much nicer than UPDATE, which leads me to: > >> INSERT OR REPLACE is almost a very good alternative. > > The semantics are subtle enough to worry me, just based on what we've > discussed so far. If it had the semantics that I proposed earlier then I think I'd prefer INSERT OR REPLACE to enhancing UPDATE, if that were the choice. The reason is that INSERT ... SELECT has much nicer syntax than UPDATE, and also, if you're generating code then having fewer grammars to generate code for would be nice. But ideally we get both, enhanced INSERT OR REPLACE semantics and enhanced UPDATE. Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
