Siblings (in my case) are nodes have the same parent - the NextIDX and
PrevIDX are only used for ordering sibling nodes. Every node may be
parent of other nodes. The ParentIDX is the downward ID of the parent node.
Yes, you are right: If i delete a node (parent node) all childs of the
node will be deleted too and the prev-/next-sibling-node of the deleted
"parent node" have to be relinked. I will do this with by updating the
NextIDX and PrevIDX of the sibling-nodes.
I'm looking for a solution to get a ordered ID list of the siblings (or
childs of a parent node).
e.g. ordered child list the parent node ID=1 -> 2, 5, 3
1...2
.
.
5...4
. 6
.
3
You might like to consider writing the phrase INTEGER PRIMARY KEY to make ID an alias for
the rowid, or adding the phrase WITHOUT ROWID to make ID the "true" primary key.
What is your definition of "sibling"? Is it not the set of nodes reachable via the
PrevIdx and (respecitvely in the case of a circularyl linked list, or) NextIdx links? Or more
simply, having hte same parent? Or maybe you are looking for "cousins" (same level but
different parents) too?
Linking each node upwards, but none downwards makes traversal difficult. Also, I am not
sure what ON DELETE CASCADE on the "parent" link is for, as it will orphan the
siblings of a deleted node and CASCADE right up to the root of the tree.
SELECT ID FROM Tree WHERE ParentIDX = (SELECT ParentIDX FROM Tree WHERE ID=?);
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von heribert
Gesendet: Montag, 11. März 2019 09:08
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Recursive CTE on tree with doubly linked items
I've a tree with doubly linked items. I want to get all siblings of a tree node
(e.g. ID=2 or harder to implement ID=3).
I tried to solve this problem with CTE of SQLite by myself - but I can not find
the solution. I looked for any exemplary solution - but do not find some.
DROP TABLE IF EXISTS "Tree";
CREATE TABLE "Tree" (
"ID" INTEGER,
"PrevIDX" INTEGER DEFAULT NULL,
"NextIDX" INTEGER DEFAULT NULL,
"ParentIDX" INTEGER DEFAULT NULL,
PRIMARY KEY ("ID"),
FOREIGN KEY ("PrevIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("NextIDX") REFERENCES "Tree" ("ID"),
FOREIGN KEY ("ParentIDX") REFERENCES "Tree" ("ID") ON DELETE CASCADE );
INSERT INTO "Tree" VALUES (1, NULL, NULL, NULL); INSERT INTO "Tree" VALUES (2, NULL, 3, 1); INSERT
INTO "Tree" VALUES (3, 2, 4, 1); INSERT INTO "Tree" VALUES (4, 3, NULL, 1);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
