query - consulta recursiva básica en sqlite3?

sqlite3 doc

Question

Tengo una tabla sqlite3 simple que se ve así:

Table: Part
Part    SuperPart
wk0Z    wk00
wk06    wk02
wk07    wk02
eZ01    eZ00
eZ02    eZ00
eZ03    eZ01
eZ04    eZ01

Necesito ejecutar una consulta recursiva para encontrar todos los pares de un SuperPart dado con todos sus subParts. Entonces digamos que tengo eZ00. eZ00 es una superparte de eZ01 y eZ01 es una superparte de eZ03. El resultado debe incluir no solo los pares (eZ00, eZ01) y (eZ01 y eZ03), sino también el par (eZ00, eZ03).

Sé que hay otras formas de definir la tabla, pero no tengo otra opción aquí. Sé que puedo usar varias uniones si conozco la profundidad de mi árbol, pero no siempre sabré qué tan profundo quiero ir. Ayudaría tener algo como WITH RECURSIVE o incluso simplemente WITH (,,) AS x pero para lo que he buscado, eso no es posible en sqlite, ¿verdad?

¿Hay alguna manera de hacer esta consulta recursiva en sqlite3?

ACTUALIZAR:

Cuando se hizo esta pregunta, SQLite no admitía las consultas recursivas, pero como lo indica @lunicon , SQLite ahora es compatible con CTE recursivo desde 3.8.3 sqlite.org/lang_with.html

Answer 1

En base a las muestras encontradas en sqlite.org/lang_with.html , la consulta

DROP TABLE IF EXISTS parts;
CREATE TABLE parts (part, superpart);
INSERT INTO parts VALUES("wk0Z", "wk00");
INSERT INTO parts VALUES("wk06", "wk02");
INSERT INTO parts VALUES("wk07", "wk02");
INSERT INTO parts VALUES("eZ01", "eZ00");
INSERT INTO parts VALUES("eZ02", "eZ00");
INSERT INTO parts VALUES("eZ03", "eZ01");
INSERT INTO parts VALUES("eZ04", "eZ01");

WITH RECURSIVE
  under_part(parent,part,level) AS (
     VALUES(''?'', ''eZ00'', 0)
     UNION ALL
     SELECT parts.superpart, parts.part, under_part.level+1 
        FROM parts, under_part
     WHERE parts.superpart=under_part.part
  )
  SELECT SUBSTR(''..........'',1,level*3) || "(" || parent || ", " || part || ")" FROM under_part
  ;

saldría

  (?, eZ00)
  ...(eZ00, eZ01)
  ...(eZ00, eZ02)
  ......(eZ01, eZ03)
  ......(eZ01, eZ04)

como "debería ser" esperado

el registro inicial de la tabla recursiva se puede reemplazar por

VALUES ((SELECT superpart FROM parts WHERE part=''eZ00''), ''eZ00'', 0)

para obtener también el padre de la superpartición inicial, aunque en este caso no hay padre en absoluto.

Answer 2

En esta versión de SQLite 3.8.3, el 2014-02-03 se agregó soporte para CTE. Aquí está la documentación CON cláusula Ejemplo:

WITH RECURSIVE
cnt(x) AS (
 SELECT 1
 UNION ALL
 SELECT x+1 FROM cnt
  LIMIT 1000000
)
SELECT x FROM cnt;

Answer 3

Esta es la consulta más básica que se me ocurrió, genera una serie en la que comenzamos con 1,2 y seguimos agregando 1 hasta llegar a 20. No es muy útil, pero jugar un poco con esto te ayudará a construir recursivas más complejas.

La serie más básica

WITH b(x,y) AS 
(
    SELECT 1,2 
    UNION ALL 
    SELECT x+ 1, y + 1 
    FROM b 
    WHERE x < 20
) SELECT * FROM b;

Huellas dactilares

1|2
2|3
3|4
4|5
5|6
6|7
7|8
8|9
9|10
10|11
11|12
12|13
13|14
14|15
15|16
16|17
17|18
18|19
19|20
20|21

Aquí hay otro ejemplo simple que genera números de Fibonacci: comenzamos con a = 0, b = 1 y luego vamos a = b, b = a + b tal como lo haríamos en cualquier lenguaje de programación

Serie Fibonacci

WITH b(x,y) AS 
(
    SELECT 0,1 
    UNION ALL 
    SELECT y, x + y 
    FROM b 
    WHERE x < 10000
) select * FROM b;

Huellas dactilares

0|1
1|1
1|2
2|3
3|5
5|8
8|13
13|21
21|34
34|55
55|89
89|144
144|233
233|377
377|610
610|987
987|1597
1597|2584
2584|4181
4181|6765
6765|10946
10946|17711

Answer 4

Si tiene la suerte de utilizar SQLite 3.8.3 o superior, entonces tiene acceso a CTE recursivos y no recursivos que utilizan sqlite.org/lang_with.html :

Gracias a lunicon por lunicon sobre esta actualización de SQLite.

En versiones anteriores a la 3.8.3 , SQLite no admitía CTE recursivas (o CTE en absoluto), por lo que no había WITH en SQLite . Como no sabes cuán profundo es, no puedes usar el truco JOIN estándar para simular el CTE recursivo. Tienes que hacerlo de la manera difícil e implementar la recursión en tu código de cliente:

• Agarra la fila inicial y las ID de las subpartes.
• Tome las filas y las ID de las subpartes para las subpartes.
• Repite hasta que nada vuelva.

Answer 5

hay un truco http://dje.me/2011/03/26/sqlite-data-trees.html

-- A method for storing and retrieving hierarchical data in sqlite3
-- by using a trigger and a temporary table.
-- I needed this but had trouble finding information on it.

-- This is for sqlite3, it mostly won''t work on anything else, however 
-- most databases have better ways to do this anyway.

PRAGMA recursive_triggers = TRUE; -- This is not possible before 3.6.18

-- When creating the Node table either use a primary key or some other 
-- identifier which the child node can reference.

CREATE TABLE Node (id INTEGER PRIMARY KEY, parent INTEGER, 
    label VARCHAR(16));

INSERT INTO Node (parent, label) VALUES(NULL, "root");
INSERT INTO Node (parent, label) VALUES(1, "a");
INSERT INTO Node (parent, label) VALUES(2, "b");
INSERT INTO Node (parent, label) VALUES(3, "c1");
INSERT INTO Node (parent, label) VALUES(3, "c2");

-- Create the temp table, note that node is not a primary key
-- which insures the order of the results when Node records are
-- inserted out of order

CREATE TEMP TABLE Path (node INTEGER, parent INTEGER, 
    label VARCHAR(16));

CREATE TRIGGER find_path AFTER INSERT ON Path BEGIN
    INSERT INTO Path SELECT Node.* FROM Node WHERE 
        Node.id = new.parent;
END;


-- The flaw here is that label must be unique, so when creating
-- the table there must be a unique reference for selection
-- This insert sets off the trigger find_path

INSERT INTO Path SELECT * FROM Node WHERE label = "c2";

-- Return the hierarchy in order from "root" to "c2"
SELECT * FROM Path ORDER BY node ASC;

DROP TABLE Path; -- Important if you are staying connected


-- To test this run:
-- sqlite3 -init tree.sql tree.db