Code: Alles auswählen
CREATE TABLE `pages` (
`page_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL COLLATE 'utf8mb4_bin',
`parent` INT(11) UNSIGNED NOT NULL
PRIMARY KEY (`page_id`)
);
Closure Tables sind ein Hilfsmittel, um diverse Abfragen zu ermöglichen, die sonst relativ mühselig sind. (Bitte Suchmaschine der Wahl benutzen, um Details zu erfahren.) Hier speichert man zu jeder Seite, welche anderen Seiten ihr untergeordnet sind, und zwar egal in welcher Tiefe. Für BC könnte das so aussehen:
Code: Alles auswählen
CREATE TABLE `pages_closure` (
`ancestor` INT(11) UNSIGNED NOT NULL,
`descendant` INT(11) UNSIGNED NOT NULL,
`depth` INT(11) UNSIGNED NOT NULL,
PRIMARY KEY (`ancestor`, `descendant`),
UNIQUE INDEX `ancestor_descendant` (`ancestor`, `descendant`),
INDEX `FK_pages_closure_pages_2` (`descendant`),
CONSTRAINT `FK_pages_closure_pages` FOREIGN KEY (`ancestor`) REFERENCES `pages` (`page_id`) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT `FK_pages_closure_pages_2` FOREIGN KEY (`descendant`) REFERENCES `pages` (`page_id`) ON UPDATE CASCADE ON DELETE CASCADE
);
Code: Alles auswählen
CREATE PROCEDURE `populate_pages_closure`()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE distance int;
TRUNCATE TABLE pages_closure;
SET distance = 0;
-- seed closure with self-pairs (distance 0)
INSERT INTO pages_closure (ancestor, descendant, depth)
SELECT page_id, page_id, distance
FROM pages;
-- for each pair (root, leaf) in the closure,
-- add (root, leaf->child) from the base table
REPEAT
SET distance = distance + 1;
INSERT INTO pages_closure (ancestor, descendant, depth)
SELECT pages_closure.ancestor, pages.page_id, distance
FROM pages_closure, pages
WHERE pages_closure.descendant = pages.parent
AND pages_closure.depth = distance - 1;
UNTIL ROW_COUNT() = 0
END REPEAT;
END
Vielleicht hat ja jemand Lust, die Nuß zu knacken.
https://stackoverflow.com/questions/282 ... re-pattern