On 07/30/2015 01:32 AM, Richard Hipp wrote: > On 7/29/15, R.Smith <rsmith at rsweb.co.za> wrote: >> >> I just wish to ... display my gratitude ... for ... CTE's. >> > > Glad you find them useful. > > I'm looking for real-world (open-source) use cases for CTEs. Please > send me links find any.
We use CTE in Skylable SX's job manager to track dependencies in a chain of jobs: http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5944 http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5949 These chains are quite short (so that temp B-tree is not a performance problem) and could probably have been implemented with a loop in C and transactions, but it is very convenient to do it with just a single query. EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (SELECT job FROM jobs WHERE parent = :job UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET result = :res, reason = :reason, complete = 1, lock = NULL WHERE job IN (SELECT * FROM descendents_of) AND result = 0 0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 0 2|0|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 3|0|1|SCAN TABLE descendents_of 3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (VALUES(:job) UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET expiry_time = datetime(expiry_time, :ttldiff) WHERE job IN (SELECT * FROM descendents_of) 0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 0 3|0|1|SCAN TABLE descendents_of 3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 Thanks, --Edwin
