As a proof of concept, I programmed a translation layer from "CTE" to
"SQLite" :
"with x as (y)... select z"
into
"drop view if exists x;create temp view x as y; ...; select z"
"with x(d) as (y), ... select z"
into
"drop table if exists x;create temp table x(d) as y;insert into x y; ...
;select z"
Issues discovered :
-----------------------------
* the use of a "table" in the "x1(d1)" syntax case
==> SQLite "ultra-light CTE" could accept "*only 'with x as (y)' syntax*"
?
* impossible to remove the existence of those temporary views/tables until
the last "select" of the CTE is closed.
==> SQLite "ultra-light CTE" could accept "*only **ONE active CTE per
session*" ?
* it's 200 hundred lines of application code (with no checks) :
. no less present in memory of a complete system system than the SQLite
DLL,
. not belonging to the "application" domain.
My hope
-------------
* The size of a true patch to get an SQLite "ultra-light CTE" (as a
translation layer) :
. is confirmed in the ballpark of acceptability,
. is considered in 2014 possible improvements of SQLite.
* 25% of SQLite users improve their life by switching their complex SQL
queries to simpler "CTE".
Happy new year and best whishes for 2014 !
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
