schema.sql

CREATE TABLE `documents` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `id_category` INT NOT NULL,
    -- `id_department` INT NOT NULL,
    `title` VARCHAR(150) NOT NULL, 
    `comment` MEDIUMTEXT,
    `filename` VARCHAR(150),
    `ts` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_category`) REFERENCES categories(`id`) ON DELETE NO ACTION
    -- FOREIGN KEY (`id_department`) REFERENCES departments(`id`) ON DELETE CASCADE
    -- INDEX(`title`)
) ENGINE = InnoDB;

CREATE TABLE `departments` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(75) NOT NULL UNIQUE,
    PRIMARY KEY (`id`)
    -- INDEX(`name`)
) ENGINE = InnoDB;

CREATE TABLE `categories` (
    `id` INT NOT NULL,
    `id_parent` INT,
    `name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`id_parent`) REFERENCES categories(`id`) ON DELETE CASCADE
    -- INDEX(`name`)
) ENGINE = InnoDB;

-- categories closure table / triggers

DELIMITER //
CREATE TRIGGER `CategoryTree_Insert` AFTER INSERT ON `categories` FOR EACH ROW 
BEGIN
    INSERT INTO `categories_tree_paths` (`id_ancestor`, `id_descendant`, `depth`)
        SELECT `id_ancestor`, NEW.`id`, depth + 1 FROM `categories_tree_paths`
            WHERE `id_descendant` = NEW.`id_parent`
		UNION ALL 
        SELECT NEW.`id`, NEW.`id`, 0;
END; //
DELIMITER ;

DELIMITER //
CREATE TRIGGER `CategoryTree_Update` BEFORE UPDATE ON `categories` FOR EACH ROW 
BEGIN
    -- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
    IF OLD.`id_parent` != NEW.`id_parent` THEN
        -- Remove the node from its current parent
        DELETE a FROM `categories_tree_paths` AS a
			JOIN `categories_tree_paths` AS d ON a.`id_descendant` = d.`id_descendant`
			LEFT JOIN `categories_tree_paths` AS x
				ON x.`id_ancestor` = d.`id_ancestor` AND x.`id_descendant` = a.`id_ancestor`
        WHERE d.`id_ancestor` = OLD.`id` AND x.`id_ancestor` IS NULL;

        -- Add the node to its new parent
        INSERT `categories_tree_paths` (`id_ancestor`, `id_descendant`, `depth`)
			SELECT supertree.`id_ancestor`, subtree.`id_descendant`, supertree.`depth` + subtree.`depth` + 1
			FROM `categories_tree_paths` AS supertree JOIN `categories_tree_paths` AS subtree
			WHERE subtree.`id_ancestor` = OLD.`id`
				AND supertree.`id_descendant` = NEW.`id_parent`;
    END IF;
END; //
DELIMITER ;

CREATE TABLE `categories_tree_paths` (
    `id_ancestor` INT NOT NULL,
    `id_descendant` INT NOT NULL,
    `depth` INT NOT NULL,
    `order` INT NOT NULL,
    PRIMARY KEY (`id_ancestor`, `id_descendant`),
    FOREIGN KEY (`id_ancestor`) REFERENCES categories(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`id_descendant`) REFERENCES categories(`id`) ON DELETE CASCADE
) ENGINE = InnoDB;