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 语句中。

我的第一次尝试是做嵌套的DELETE FROM语句，如下所示：

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

但后来我了解到，这仅在顶级 DELETE、INSERT 和 UPDATE 语句中可用，所以这似乎是一条死胡同。RETURNING

我们目前使用触发器实现了这一点，但是（AFAIK）如果我们需要收集已删除的叶节点，则不起作用。

sql sqlite

/* 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;

运行结果