当前位置： 文江博客话题详情

MySQL分层数据帮助-闭表方法

发布于 2024-12-17 02:48:26 字数 14898 浏览 0 评论 0原文

我正在尝试在 MySQL 中实现一个系统来存储分层数据。我决定采用此处实现的系统，如下所述Bill Karwin 从第 40 号幻灯片开始。我正在尝试设置数据库，以便EntryPaths 表是自动维护的。

更新：我已经更新了数据库创建 SQL 一点。我想我已经完成了 1/2 的更新工作。运行数据库创建 SQL 后，尝试以下操作

首先看看这个条目的外观

-- Example query to return a full library entry (0x02 is the entry iD)
SELECT `Library`.* FROM `Library`
LEFT JOIN `EntryPaths` ON `Library`.`iD` = `EntryPaths`.`descendant`
WHERE `EntryPaths`.`ancestor` = 0x02
ORDER BY `Library`.`subsectionOf`, `Library`.`subsectionOrder`

这一个看起来如何

-- Example query to return a full library entry (0x08 is the entry iD)
SELECT `Library`.* FROM `Library`
LEFT JOIN `EntryPaths` ON `Library`.`iD` = `EntryPaths`.`descendant`
WHERE `EntryPaths`.`ancestor` = 0x08
ORDER BY `Library`.`subsectionOf`, `Library`.`subsectionOrder`

第一个条目有几个子项，第二个没有子项。运行以下更新将“College Years”节点（及其子节点）重新设置为 John Doe

UPDATE  `Library` SET  `subsectionOf` =  0x08 WHERE  `Library`.`iD` = 0x04;

如果您重新运行上述两个 select 语句，您将看到项目已从 中删除Jane Doe 但它们尚未按预期添加到 John Doe 中。 Library_Update 触发器有问题，但我已经没有办法尝试修复它了。

数据库使用示例数据创建 SQL：

-- MYSQL
SET FOREIGN_KEY_CHECKS=0;
DROP TRIGGER IF EXISTS Library_Insert;
DROP TRIGGER IF EXISTS Library_Update;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Attributes;
DROP TABLE IF EXISTS LibraryHistory;
DROP TABLE IF EXISTS EntryPaths;
DROP TABLE IF EXISTS Library;
SET FOREIGN_KEY_CHECKS=1;


CREATE TABLE `Users` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `libraryID`       VARBINARY(16),            -- Library UUID & FK (The library entry for this person)
    `email`           NVARCHAR(255) NOT NULL,   -- Email address
    `nickname`        NVARCHAR(255) NOT NULL,   -- Nickname used for display
    `firstname`       NVARCHAR(255),            -- Real first name
    `lastname`        NVARCHAR(255),            -- Real last name
    `joinDate`        DATETIME NOT NULL,        -- Date the account was created
    PRIMARY KEY (`iD`)
) ENGINE = MYISAM;


CREATE TABLE `Library` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `name`            NVARCHAR(500) NOT NULL,   -- Name for the entry
    `contentType`     NVARCHAR(50)  NOT NULL,   -- Mime type of data
    `content`         LONGBLOB      NOT NULL,   -- Data a for the entry
    `subsectionOf`    VARBINARY(16),            -- Library UUID & FK
    `subsectionOrder` INT,                      -- Oder of Subsections 
    `lastModifiedBy`  VARBINARY(16),            -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,   -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`subsectionOf`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`lastModifiedBy`) REFERENCES Users(`iD`),
    INDEX(`name`)
) ENGINE = MYISAM;

-- Trigger to update the EntryPaths table for new entries
DELIMITER //
CREATE TRIGGER `Library_Insert` AFTER INSERT ON `Library` FOR EACH ROW 
BEGIN
    INSERT INTO `EntryPaths` (`ancestor`, `descendant`, `len`)
        SELECT `ancestor`, NEW.`iD`, len + 1 FROM `EntryPaths`
            WHERE `descendant` = NEW.`subsectionOf`
            UNION ALL SELECT NEW.`iD`, NEW.`iD`, 0;
END; //
DELIMITER ;


DELIMITER //
CREATE TRIGGER `Library_Update` BEFORE UPDATE ON `Library` FOR EACH ROW 
BEGIN
    -- Add the old entry into the history table
    INSERT INTO `LibraryHistory` VALUES(UNHEX(REPLACE(UUID(),'-','')),
        OLD.`iD`, OLD.`name`, OLD.`contentType`, OLD.`content`,
        OLD.`subsectionOf`, OLD.`subsectionOrder`, OLD.`lastModifiedBy`,
        OLD.`lastModified`);

    -- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
    IF OLD.`subsectionOf` != NEW.`subsectionOf` THEN
        -- Remove the node from its current parent
        DELETE a FROM `EntryPaths` AS a
        JOIN `EntryPaths` AS d ON a.`descendant` = d.`descendant`
        LEFT JOIN `EntryPaths` AS x
        ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`
        WHERE d.`ancestor` = OLD.`iD` AND x.`ancestor` IS NULL;

        -- Add the node to its new parent
        -- FIXME: Not Working yet
        INSERT `EntryPaths` (`ancestor`, `descendant`, `len`)
        SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1
        FROM `EntryPaths` AS supertree JOIN `EntryPaths` AS subtree
        WHERE subtree.`ancestor` = NEW.`iD`
        AND supertree.`descendant` = NEW.`subsectionOf`;
    END IF;
END; //
DELIMITER ;


CREATE TABLE `EntryPaths` (
    `ancestor`      VARBINARY(16) NOT NULL,
    `descendant`    VARBINARY(16) NOT NULL,
    `len`           VARBINARY(16) NOT NULL,
    PRIMARY KEY (`ancestor`, `descendant`),
    FOREIGN KEY (`ancestor`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`descendant`) REFERENCES Library(`iD`) ON DELETE CASCADE
) ENGINE = MYISAM;


CREATE TABLE `LibraryHistory` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `libraryID`       VARBINARY(16) NOT NULL,   -- Library UUID & FK
    `name`            NVARCHAR(500) NOT NULL,   -- Name for the entry
    `contentType`     NVARCHAR(50)  NOT NULL,   -- Mime type of data
    `content`         LONGBLOB      NOT NULL,   -- Data a for the entry
    `subsectionOf`    VARBINARY(16),            -- Library UUID & FK
    `subsectionOrder` INT,                      -- Oder of Subsections 
    `lastModifiedBy`  VARBINARY(16),            -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,   -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`lastModifiedBy`) REFERENCES Users(`iD`)
) ENGINE = MYISAM;


CREATE TABLE `Attributes` (
    `iD`              VARBINARY(16) NOT NULL,  -- UUID & PK  (Potentially could be removed)
    `libraryID`       VARBINARY(16) NOT NULL,  -- Library UUID & FK
    `name`            NVARCHAR(500) NOT NULL,  -- Name of attribute
    `dataType`        INT           NOT NULL,  -- The type of data the attribute holds (int, date, string, etc.)
    `data`            NVARCHAR(500) NOT NULL,  -- Value of attribute
    `lastModifiedBy`  VARBINARY(16),           -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,  -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    INDEX (`name`)
) ENGINE = MYISAM;

ALTER TABLE `Users` ADD CONSTRAINT FK_User_Library FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`);

-- Example Data
INSERT INTO `Library` VALUES(0x01, 'People', 'text/plain', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x02, 'Jane Doe', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:29:13');
INSERT INTO `Library` VALUES(0x03, 'Younger Years', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 1, NULL, '2011-11-16 00:00:00');
INSERT INTO `Library` VALUES(0x04, 'College Years', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 2, NULL, '2011-11-16 20:31:52');
INSERT INTO `Library` VALUES(0x05, 'Yale', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x04, 2, NULL, '2011-11-16 20:32:44');
INSERT INTO `Library` VALUES(0x06, 'Old Age', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar find me here scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 3, NULL, '2011-11-16 20:31:52');
INSERT INTO `Library` VALUES(0x07, 'Community College', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x04, 1, NULL, '2011-11-16 20:33:11');
INSERT INTO `Library` VALUES(0x08, 'John Doe', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:34:40');
INSERT INTO `Library` VALUES(0x09, 'Planets', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x10, 'Earth', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x11, 'Mars', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Attributes` VALUES(0x01, 0x02, 'TypeOf', 1, 0x01, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x02, 0x02, 'BirthDate', 2, '19770521', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x03, 0x02, 'EyeColor', 3, 'Brown', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x04, 0x08, 'TypeOf', 1, 0x01, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x05, 0x08, 'BirthDate', 2, '19740521', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x06, 0x10, 'TypeOf', 1, 0x08, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x07, 0x11, 'TypeOf', 1, 0x08, NULL, '2011-11-16 20:34:40');
原文

I'm trying to implement a system in MySQL to store hierarchical data. I've decided to go with the system implemented here as described by Bill Karwin starting on slide number 40. I'm trying to setup the database so the EntryPaths table is maintained automatically.

Update: I've updated the database create SQL a bit. I've got things 1/2 working for an update, I think. After running the database create SQL try the following

First see how this entry looks

-- Example query to return a full library entry (0x02 is the entry iD)
SELECT `Library`.* FROM `Library`
LEFT JOIN `EntryPaths` ON `Library`.`iD` = `EntryPaths`.`descendant`
WHERE `EntryPaths`.`ancestor` = 0x02
ORDER BY `Library`.`subsectionOf`, `Library`.`subsectionOrder`

And how this one looks

-- Example query to return a full library entry (0x08 is the entry iD)
SELECT `Library`.* FROM `Library`
LEFT JOIN `EntryPaths` ON `Library`.`iD` = `EntryPaths`.`descendant`
WHERE `EntryPaths`.`ancestor` = 0x08
ORDER BY `Library`.`subsectionOf`, `Library`.`subsectionOrder`

The 1st entry viewed has several children, the second has no children. Run the following update to reparent the 'College Years' node (and its children) to John Doe

UPDATE  `Library` SET  `subsectionOf` =  0x08 WHERE  `Library`.`iD` = 0x04;

If you re-run the above two select statements you'll see that items have been removed from Jane Doe but they have not been added to John Doe as expected. The Library_Update trigger is at fault but I'm running out of ideas to try to fix it.

The database create SQL with sample data:

-- MYSQL
SET FOREIGN_KEY_CHECKS=0;
DROP TRIGGER IF EXISTS Library_Insert;
DROP TRIGGER IF EXISTS Library_Update;
DROP TABLE IF EXISTS Users;
DROP TABLE IF EXISTS Attributes;
DROP TABLE IF EXISTS LibraryHistory;
DROP TABLE IF EXISTS EntryPaths;
DROP TABLE IF EXISTS Library;
SET FOREIGN_KEY_CHECKS=1;


CREATE TABLE `Users` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `libraryID`       VARBINARY(16),            -- Library UUID & FK (The library entry for this person)
    `email`           NVARCHAR(255) NOT NULL,   -- Email address
    `nickname`        NVARCHAR(255) NOT NULL,   -- Nickname used for display
    `firstname`       NVARCHAR(255),            -- Real first name
    `lastname`        NVARCHAR(255),            -- Real last name
    `joinDate`        DATETIME NOT NULL,        -- Date the account was created
    PRIMARY KEY (`iD`)
) ENGINE = MYISAM;


CREATE TABLE `Library` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `name`            NVARCHAR(500) NOT NULL,   -- Name for the entry
    `contentType`     NVARCHAR(50)  NOT NULL,   -- Mime type of data
    `content`         LONGBLOB      NOT NULL,   -- Data a for the entry
    `subsectionOf`    VARBINARY(16),            -- Library UUID & FK
    `subsectionOrder` INT,                      -- Oder of Subsections 
    `lastModifiedBy`  VARBINARY(16),            -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,   -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`subsectionOf`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`lastModifiedBy`) REFERENCES Users(`iD`),
    INDEX(`name`)
) ENGINE = MYISAM;

-- Trigger to update the EntryPaths table for new entries
DELIMITER //
CREATE TRIGGER `Library_Insert` AFTER INSERT ON `Library` FOR EACH ROW 
BEGIN
    INSERT INTO `EntryPaths` (`ancestor`, `descendant`, `len`)
        SELECT `ancestor`, NEW.`iD`, len + 1 FROM `EntryPaths`
            WHERE `descendant` = NEW.`subsectionOf`
            UNION ALL SELECT NEW.`iD`, NEW.`iD`, 0;
END; //
DELIMITER ;


DELIMITER //
CREATE TRIGGER `Library_Update` BEFORE UPDATE ON `Library` FOR EACH ROW 
BEGIN
    -- Add the old entry into the history table
    INSERT INTO `LibraryHistory` VALUES(UNHEX(REPLACE(UUID(),'-','')),
        OLD.`iD`, OLD.`name`, OLD.`contentType`, OLD.`content`,
        OLD.`subsectionOf`, OLD.`subsectionOrder`, OLD.`lastModifiedBy`,
        OLD.`lastModified`);

    -- From http://www.mysqlperformanceblog.com/2011/02/14/moving-subtrees-in-closure-table/
    IF OLD.`subsectionOf` != NEW.`subsectionOf` THEN
        -- Remove the node from its current parent
        DELETE a FROM `EntryPaths` AS a
        JOIN `EntryPaths` AS d ON a.`descendant` = d.`descendant`
        LEFT JOIN `EntryPaths` AS x
        ON x.`ancestor` = d.`ancestor` AND x.`descendant` = a.`ancestor`
        WHERE d.`ancestor` = OLD.`iD` AND x.`ancestor` IS NULL;

        -- Add the node to its new parent
        -- FIXME: Not Working yet
        INSERT `EntryPaths` (`ancestor`, `descendant`, `len`)
        SELECT supertree.`ancestor`, subtree.`descendant`, supertree.`len`+subtree.`len`+1
        FROM `EntryPaths` AS supertree JOIN `EntryPaths` AS subtree
        WHERE subtree.`ancestor` = NEW.`iD`
        AND supertree.`descendant` = NEW.`subsectionOf`;
    END IF;
END; //
DELIMITER ;


CREATE TABLE `EntryPaths` (
    `ancestor`      VARBINARY(16) NOT NULL,
    `descendant`    VARBINARY(16) NOT NULL,
    `len`           VARBINARY(16) NOT NULL,
    PRIMARY KEY (`ancestor`, `descendant`),
    FOREIGN KEY (`ancestor`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`descendant`) REFERENCES Library(`iD`) ON DELETE CASCADE
) ENGINE = MYISAM;


CREATE TABLE `LibraryHistory` (
    `iD`              VARBINARY(16) NOT NULL,   -- UUID & PK
    `libraryID`       VARBINARY(16) NOT NULL,   -- Library UUID & FK
    `name`            NVARCHAR(500) NOT NULL,   -- Name for the entry
    `contentType`     NVARCHAR(50)  NOT NULL,   -- Mime type of data
    `content`         LONGBLOB      NOT NULL,   -- Data a for the entry
    `subsectionOf`    VARBINARY(16),            -- Library UUID & FK
    `subsectionOrder` INT,                      -- Oder of Subsections 
    `lastModifiedBy`  VARBINARY(16),            -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,   -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    FOREIGN KEY (`lastModifiedBy`) REFERENCES Users(`iD`)
) ENGINE = MYISAM;


CREATE TABLE `Attributes` (
    `iD`              VARBINARY(16) NOT NULL,  -- UUID & PK  (Potentially could be removed)
    `libraryID`       VARBINARY(16) NOT NULL,  -- Library UUID & FK
    `name`            NVARCHAR(500) NOT NULL,  -- Name of attribute
    `dataType`        INT           NOT NULL,  -- The type of data the attribute holds (int, date, string, etc.)
    `data`            NVARCHAR(500) NOT NULL,  -- Value of attribute
    `lastModifiedBy`  VARBINARY(16),           -- User UUID & FK
    `lastModified`    DATETIME      NOT NULL,  -- Last time the record was updated
    PRIMARY KEY (`iD`),
    FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`) ON DELETE CASCADE,
    INDEX (`name`)
) ENGINE = MYISAM;

ALTER TABLE `Users` ADD CONSTRAINT FK_User_Library FOREIGN KEY (`libraryID`) REFERENCES Library(`iD`);

-- Example Data
INSERT INTO `Library` VALUES(0x01, 'People', 'text/plain', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x02, 'Jane Doe', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:29:13');
INSERT INTO `Library` VALUES(0x03, 'Younger Years', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 1, NULL, '2011-11-16 00:00:00');
INSERT INTO `Library` VALUES(0x04, 'College Years', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 2, NULL, '2011-11-16 20:31:52');
INSERT INTO `Library` VALUES(0x05, 'Yale', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x04, 2, NULL, '2011-11-16 20:32:44');
INSERT INTO `Library` VALUES(0x06, 'Old Age', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar find me here scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x02, 3, NULL, '2011-11-16 20:31:52');
INSERT INTO `Library` VALUES(0x07, 'Community College', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', 0x04, 1, NULL, '2011-11-16 20:33:11');
INSERT INTO `Library` VALUES(0x08, 'John Doe', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:34:40');
INSERT INTO `Library` VALUES(0x09, 'Planets', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x10, 'Earth', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Library` VALUES(0x11, 'Mars', 'text/x-markup', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam pulvinar scelerisque quam, vel convallis turpis porttitor in. Curabitur pulvinar fermentum pulvinar. Mauris lorem lacus, gravida porta lacinia vitae, dictum ac eros. Aliquam magna arcu, lacinia ac dictum sed, euismod eu elit. Sed semper nulla at velit pulvinar in vehicula risus tempus. Phasellus id nisl libero, id porttitor purus. Integer aliquet semper aliquam. Morbi elit mi, pellentesque et ornare nec, iaculis gravida elit. Sed in luctus lorem. Maecenas a purus at lectus condimentum congue.', NULL, NULL, NULL, '2011-11-16 20:27:54');
INSERT INTO `Attributes` VALUES(0x01, 0x02, 'TypeOf', 1, 0x01, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x02, 0x02, 'BirthDate', 2, '19770521', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x03, 0x02, 'EyeColor', 3, 'Brown', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x04, 0x08, 'TypeOf', 1, 0x01, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x05, 0x08, 'BirthDate', 2, '19740521', NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x06, 0x10, 'TypeOf', 1, 0x08, NULL, '2011-11-16 20:34:40');
INSERT INTO `Attributes` VALUES(0x07, 0x11, 'TypeOf', 1, 0x08, NULL, '2011-11-16 20:34:40');

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

小忆控 2024-12-24 02:48:26

在 Libary_Update 触发器的 INSERT 语句中，您有以下行：

WHERE subtree.`ancestor` = NEW.`iD`

但您没有更新 ID 字段，因此我认为您不会有 NEW.iD 值。该行是否应该使用 OLD.iD 代替？

In your INSERT statement in the Libary_Update trigger you have the following line:

WHERE subtree.`ancestor` = NEW.`iD`

but you aren't updating the ID field so I don't think you will have a NEW.iD value. Should that line possibly use OLD.iD instead?

回复 原文
~没有更多了~

关于作者

暖风昔人

暂无简介

0 文章
0 评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

亚希

文章 0 评论 0

cyp

文章 0 评论 0

北漠

文章 0 评论 0

11223456

文章 0 评论 0

坠似风落

文章 0 评论 0

游魂

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文