nineinchnick/closure-table-manager

PHP library that helps maintain adjacency list SQL structures

closure-table-manager's Issues

Unit tests

Port unit tests.

Notice for mysql when trying to set path

Notice: Undefined variable: parent in [...]/vendor/nineinchnick/closure-table-manager/src/closureTable/mysql/Schema.php on line 104

should probably be $parentKey instead of $parent ?

Info:

Base table:

CREATE TABLE `page` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(20) NOT NULL,
  `title` varchar(255) NOT NULL COMMENT 'used for slugs',
  `slug` varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'generated from title',
  `path` text NOT NULL COMMENT 'generated by triggers',
  `position` int(11) NOT NULL COMMENT 'for sorting',
  PRIMARY KEY (`id`),
  KEY `parent_id` (`parent_id`),
  CONSTRAINT `page_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `page` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

ini_set('display_errors', 1);
error_reporting(-1);
$manager = new \nineinchnick\closureTable\Manager();

$queries = $manager->getQueries(
    $dsn = 'mysql',
    $tableName = 'page',
    $parentKey = 'parent_id',
    $primaryKey = 'id',
    $primaryKeyType = 'BIGINT',
    $path = 'path',
    $pathFrom = 'slug',
    $pathSeparator = '/',
    $tableNameSuffix = '_tree',
    $schemaName = null
);

The trigger that (not tested against db, just assuming) will fail:

DELIMITER $$
CREATE TRIGGER `page_tree_bi_path` BEFORE INSERT ON `page`
FOR EACH ROW BEGIN
  # WHY??
  IF NEW."" IS NULL THEN
    SET NEW."path" = NEW."slug";
  ELSE
    SELECT "path" || '/' || NEW."slug" INTO NEW."path"
    FROM "page"
    WHERE "id" = NEW."parent_id";
  END IF;
END;$$
DELIMITER ;

When using sqlite, I think this is the corresponding query:

CREATE TRIGGER "page_tree_ai_path_1" AFTER INSERT ON "page"
FOR EACH ROW WHEN NEW."parent_id" IS NULL
BEGIN
  UPDATE "page" SET "path" = "slug" WHERE "id" = NEW."id";
END;

and pgsql:

-- Generate path urls based on slug and position in the tree.

CREATE OR REPLACE FUNCTION "page_tree_bi_path"() RETURNS TRIGGER AS
$BODY$
BEGIN
  IF NEW."parent_id" IS NULL THEN
    NEW."path" := NEW."slug";
  ELSE
    SELECT "path" || '/' || NEW."slug" INTO NEW."path"
    FROM "page"
    WHERE "id" = NEW."parent_id";
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql'