[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Swithun Crowe]

Hello

DB> Hey all, just wanted to share this in case anybody is also looking for
DB> a very simple tutorial for CTE's in sqlite:

DB> 
http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

Yes, that was a great introduction to recursive SQL. Last week I wrote a
CTE to generate Lindenmayer (L) Systems drawings, and it helped to
understand why my code worked!

This cuts out dozens of lines of procedural code.

Thanks.

WITH RECURSIVE follow_rule(instruction_id, depth, position, pattern_id) AS
(
    SELECT instruction_id, 0, 0, pattern_id
      FROM patterns
INNER JOIN (SELECT instruction_id, pattern_id
              FROM seeds
          ORDER BY position) AS seeds USING (pattern_id)
 UNION ALL
    SELECT r.expansion_instruction_id, follow_rule.depth + 1, r.position, 
follow_rule.pattern_id
      FROM (SELECT instruction_id, expansion_instruction_id, position
              FROM instructions
        INNER JOIN expansions USING (instruction_id)
          ORDER BY expansions.position) AS r
INNER JOIN follow_rule USING (instruction_id)
INNER JOIN patterns USING (pattern_id)
     WHERE follow_rule.depth < patterns.depth
  ORDER BY 2 DESC, 3 ASC
)
SELECT pattern_id, type, value, class FROM follow_rule
INNER JOIN instructions USING (instruction_id);

DROP TABLE IF EXISTS instructions;
CREATE TABLE instructions (
  instruction_id INTEGER PRIMARY KEY AUTOINCREMENT,
  type INTEGER, -- draw, turn, push/pop, expand etc.
  value REAL DEFAULT 0, -- rotation, push or pop etc.
  class INTEGER DEFAULT 0 -- class for draw instructions
);

DROP TABLE IF EXISTS expansions;
CREATE TABLE expansions (
  instruction_id INTEGER, -- parent instruction ID
  expansion_instruction_id INTEGER,
  position INTEGER
);

DROP TABLE IF EXISTS patterns;
CREATE TABLE patterns (
  pattern_id INTEGER PRIMARY KEY AUTOINCREMENT,
  depth INTEGER
);

DROP TABLE IF EXISTS seeds;
CREATE TABLE seeds (
  pattern_id INTEGER,
  instruction_id INTEGER,
  position INTEGER
);

CREATE INDEX expansionIdx ON expansions(instruction_id);
CREATE INDEX seedsIdx ON seeds(pattern_id);