Managing Hierarchical Data With SQLite

This article is an extension of a similar article written about maintaining hiearchies for MySQL. I refer you to that article for a full discussion. This article merely updates the SQL for sqlite.

--tabledesign
CREATE  TABLE "categories" ("id" INTEGER PRIMARY KEY  NOT NULL , "name" CHAR(55) NOT NULL , "description" CHAR(255),  "lft" INTEGER NOT NULL , "rgt" INTEGER NOT NULL )

--inserts
INSERT INTO categories VALUES(null, "DUNGEONS AND DRAGONS", '', 1, 12);
INSERT INTO categories VALUES(null, "CLASSES", '', 2,5);
INSERT INTO categories VALUES(null, "PALADIN", '', 3,4);
INSERT INTO categories VALUES(null, "RACES", '', 6, 11);
INSERT INTO categories VALUES(null, "HUMAN", '', 7,8);
INSERT INTO categories VALUES(null, "ELF", '', 9,10);

--Order entire tree
SELECT node.name FROM categories AS node, categories AS parent
WHERE node.lft BETWEEN parent.lft and parent.rgt AND parent.name = 'DUNGEONS AND DRAGONS' ORDER BY node.lft;

--find all leaves
SELECT name FROM categories WHERE rgt = lft + 1;

--find single path
SELECT parent.name
FROM categories AS node,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'RACES'
ORDER BY parent.lft;

--find depth levels
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM categories AS node,
categories AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

--depth of subtree
--use unique aliases in queries with sqlite
SELECT node2.name, (COUNT(parent.name) - (subtree.depth + 1)) AS depth FROM  
categories AS node2,
categories AS parent,
categories AS sub_parent,
(
		SELECT node.name AS name, (COUNT(parent.name) - 1) AS depth
		FROM categories AS node,
		categories AS parent
		WHERE node.lft BETWEEN parent.lft AND parent.rgt
		AND node.name = 'RACES'
		GROUP BY node.name
		ORDER BY node.lft
) AS subtree
WHERE node2.lft BETWEEN parent.lft AND parent.rgt
	AND node2.lft BETWEEN sub_parent.lft AND sub_parent.rgt
	AND sub_parent.name = subtree.name
GROUP BY node2.name
ORDER BY node2.lft;

--Add Crap to Tree Where Parent/child exist

SELECT  lft FROM categories WHERE name = 'HUMAN';
--returns 7
BEGIN;
UPDATE categories SET rgt = rgt + 2 WHERE rgt > 7;
UPDATE categories SET lft = lft + 2 WHERE lft > 7;

INSERT INTO categories(name, lft, rgt) VALUES('GNOMES', 7 + 1, 7 + 2);
COMMIT;

--Add Crap To Parent With No Children

SELECT  lft FROM categories WHERE name = 'GNOME';
--returns 8

BEGIN TRANSACTION;
UPDATE categories SET rgt = rgt + 2 WHERE rgt > 8;
UPDATE categories SET lft = lft + 2 WHERE lft > 8;

INSERT INTO categories(id, name, lft, rgt) VALUES(null, 'SVIRFNEBLI', 8 + 1, 8 + 2);
COMMIT;

--Delete Crap with no leaves and node with no children

LOCK TABLE categories WRITE;


SELECT lft, rgt, rgt - lft + 1 FROM categories WHERE name = 'SVIRFNEBLI';
-- RETURNS 9, 10, 2 respectively