SqLite：如何在一个树中删除从根到叶子的节点？

我们在SqLite中有三个表表示的树结构：

CREATE TABLE IF NOT EXISTS "root_nodes" (
    "id"    INTEGER NOT NULL,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "inner_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "leaf_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL,
    -- other stuff
    PRIMARY KEY("id")
);

字符串
比如，在这个例子中，树的高度是3（根节点、内部节点和叶节点层），但实际上可能有几个内部节点层，但数量不变。
给定一个根节点，我需要删除它的所有后代内部节点和叶节点，并返回删除的叶节点。理想情况下，在一个SQL语句中。
我的第一个尝试是做嵌套的CNOFFROM语句，像这样：

DELETE FROM leaf_nodes WHERE parent IN (
    DELETE FROM inner_nodes WHERE parent IN (
        DELETE FROM root_nodes WHERE id = ? RETURNING id
    ) RETURNING id
) RETURNING id

型
但后来我了解到RETURNING只在top-level DELETE , INSERT and UPDATE statements中可用，所以这似乎是一个死胡同。
我们目前使用触发器实现了这一点，但是如果我们需要收集删除的叶子节点，那么（AFAIK）就不起作用了。

也许可以考虑使用以下组合：
1.使用ON DELETE CASCADE自动删除子项的外键，以及
1.用于收集删除ID的表，以及

3个AFTER DELETE触发器自动将插入填充到集合表中。
这将允许被删除的id的，以获得作为和当想要的
下面是一个demo：

/* just in case cleanup */
DROP TABLE IF EXISTS leaf_nodes;
DROP TABLE IF EXISTS inner_nodes;
DROP TABLE IF EXISTS root_nodes;

/* Make sure that Foreign Key handling is enabled */
PRAGMA foreign_keys = on;

/* The original tables */
CREATE TABLE IF NOT EXISTS "root_nodes" (
    "id"    INTEGER NOT NULL,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "inner_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL /*>>>>>>>>>> ADDED >>>>>>>>>>*/ REFERENCES root_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- other stuff
    PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "leaf_nodes" (
    "id"    INTEGER NOT NULL,
    "parent"    INTEGER NOT NULL /*>>>>>>>>>> ADDED >>>>>>>>>>*/ REFERENCES inner_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- other stuff
    PRIMARY KEY("id")
);
/*<<<<<<<<<< NEW TABLE FOR THE COLLECTION OF DELETED IDs >>>>>>>>>>*/
CREATE TABLE IF NOT EXISTS deletion_log (timestamp INTEGER DEFAULT (strftime('%s','now')), type TEXT, deletedId INTEGER); 
/*<<<<<<<<< 3 AFTER DELETE TRIGGERS >>>>>>>>>>*/
CREATE TRIGGER IF NOT EXISTS leaf_nodes_delete_trigger AFTER DELETE ON leaf_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('3LN',old.id);
    END
;
CREATE TRIGGER IF NOT EXISTS inner_nodes_delete_trigger AFTER DELETE ON inner_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('2IN',old.id);
    END
;
CREATE TRIGGER IF NOT EXISTS root_nodes_delete_trigger AFTER DELETE ON root_nodes
    BEGIN
        INSERT INTO deletion_log (type,deletedid) VALUES('1RN',old.id);
    END
;
/* DEMONSTRATE THE ABOVE */
/* 1 Load some root_nodes */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 10)
INSERT OR IGNORE INTO root_nodes SELECT * FROM cte_count;

/* 2 Load some inner_nodes (approx 10 per root) */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 100)
INSERT OR IGNORE INTO inner_nodes SELECT *,(abs(random() % 10)) + 1 FROM cte_count;
/* 3 Load some leaf_nodes again approx 10 fir inner node */
WITH cte_count(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte_count LIMIT 1000)
INSERT OR IGNORE INTO leaf_nodes SELECT *,(abs(random() % 100)) + 1 FROM cte_count;

/* Show the data after the initial load */
SELECT * FROM deletion_log ORDER BY type ASC; /*<<<<<<<<<< RESULT 1*/
SELECT * FROM root_nodes 
    JOIN inner_nodes ON inner_nodes.parent = root_nodes.id
    JOIN leaf_nodes ON leaf_nodes.parent = inner_nodes.id
ORDER BY root_nodes.id,inner_nodes.id,leaf_nodes.id /*<<<<<<<<<< RESULT 2*/
;
/*!!!!!!!!!! DELETE SOME ROOT NODES !!!!!!!!!!*/
DELETE FROM root_nodes WHERE (id % 2) = 1;
/* Show the data after the deletions */
SELECT * FROM deletion_log ORDER BY type ASC; /*<<<<<<<<<< RESULT 3*/
SELECT * FROM root_nodes 
    JOIN inner_nodes ON inner_nodes.parent = root_nodes.id
    JOIN leaf_nodes ON leaf_nodes.parent = inner_nodes.id
ORDER BY root_nodes.id,inner_nodes.id,leaf_nodes.id /*<<<<<<<<<< RESULT 4 */
;

/* Cleanup after demo */
DROP TABLE IF EXISTS leaf_nodes;
DROP TABLE IF EXISTS inner_nodes;
DROP TABLE IF EXISTS root_nodes;
DROP TABLE IF EXISTS deletion_log;

字符串

运行结果