Как правильно вставить ссылающуюся строку в закрывающую таблицу
Я читал о таблицах закрытия, и у меня возникла проблема. Эта вставка не будет работать, если в таблице закрытия нет самоссылающейся строки для комментария с идентификатором 1, поэтому мне нужно сначала вставить ее с помощью insert into [TreePaths] values(1,1,1). Но после этого, если я хочу добавить предка к комментарию с идентификатором 2, мне не нужно вставлять для него самоссылающуюся строку, поскольку она была добавлена на этапе, когда вопрос 2 использовался в качестве предка. Итак, мой вопрос: нужно ли мне просто проверить, существует ли самоссылающаяся строка для потомка, если да, продолжить, если нет, создать ее, или есть правильный способ сделать это, который я пропустил?
INSERT INTO TreePaths (ancestor, descendant, level)
SELECT t.ancestor, 2, t.level + 1
FROM TreePaths AS t
WHERE t.descendant = 1
UNION ALL
SELECT 2, 2, 1;
Есть код для воссоздания
CREATE TABLE [dbo].[Comments] (
[comment_id] [int] NOT NULL PRIMARY KEY,
[comment] [text] NOT NULL
);
CREATE TABLE [dbo].[TreePaths] (
[ancestor] [int] NOT NULL,
[descendant] [int] NOT NULL,
[Level] [int] NULL,
PRIMARY KEY (ancestor, descendant),
FOREIGN KEY (ancestor) REFERENCES [dbo].[Comments](comment_id),
FOREIGN KEY (descendant) REFERENCES [dbo].[Comments](comment_id)
);
INSERT INTO [dbo].[Comments] (comment_id, comment)
VALUES (1, 'This is the first comment.'),
(2, 'This is the second comment.'),
(3, 'This is the third comment.');
Что касается проблемы, я протестировал ваш код, и он работал нормально; мы что-то здесь упускаем? db<>рабочий пример
Если вы добавите SELECT * FROM TreePaths; в конце, вы увидите, что была создана только одна строка 2,2,1, однако код должен был добавить комментарий 2 в качестве предка для комментария 1. Таким образом, правильный результат будет 1, 2, 2 и 2,2,1.
Потому что вы фильтруете данные, которые хотите вставить; у тебя есть WHERE. На момент INSERT нет строк, в которых descendant имеет значение 1; почему вы ожидаете, что такой запрос вернет результаты, если таких строк не существует?
Да, я знаю, почему это работает именно так. Мой вопрос был о шаблоне таблицы закрытия. Нужно ли мне проверять наличие самоссылающейся строки перед вставкой. Это пример из книги и примечаний о проверке там нет. И поскольку это работало неправильно, если раньше не было создано ссылающейся на себя строки, мне было интересно, не пропустил ли я что-то
Если строка отсутствует, это ошибка исходной вставки? Если у тебя частично плохие данные, ты уже накурился, и все разваливается на мой взгляд. Вставки в комментарии и TreePaths должны быть в транзакции, поэтому это не проблема. Хотя, если честно, TreePath мне кажется плохим дизайном, обычно не сохраняется самоссылающаяся строка, а просто имеется иерархия строк, т.е. Родитель=>Ребенок. Всегда легко материализовать дочернюю, дочернюю строку, если это необходимо.
Похоже, что книга может быть не лучшим ресурсом; Я согласен с @siggemannen, необходимость начинать строку со ссылкой на себя звучит неправильно.
Несколько не по теме: но на прошлой неделе я потратил 150 долларов на 3-е издание книги «Информационное моделирование и реляционные базы данных» (выпущенное в прошлом месяце) — и в указателе книги нет упоминания о «замыкающей таблице» (но есть множество другой претенциозно звучащей терминологии! ) — Я хотел бы узнать больше об этом шаблоне, но даже поиск в Google «таблицы закрытия» не возвращает столько релевантных страниц; он известен под другим именем? ОП сказал, что они используют книгу, могут ли они поделиться названием своей книги?
@Dai — Таблица закрытия, также известная как таблица Bridge.
Я нашел статью о Red Gate (написанную Филом Фактором, не меньше), просто выполнив поиск «SQL Server таблицы закрытия», @Dai. Кажется, там есть ресурсы.
Книга Билла Карвина называется «Антипаттерны SQL», и таблица замыканий представляет собой один из четырех способов (список смежности, перечисление путей, вложенные наборы, таблица замыканий), описанных для управления иерархическими данными.
Не следует ли вам больше узнать о паттернах, а не об анти-паттернах?
Ну, это шаблон, и, возможно, он лучший (конечно, исходя из требований)
Ответы 1
Что ж, ответ на ваш вопрос — да. Согласно дизайну, строка самоссылки для потомка должна существовать. В этом случае записи TreePaths должны быть такими, как показано ниже.
И вы можете выбрать все записи с помощью запроса ниже.
SELECT c.*,t.*
FROM [Comments] c
INNER JOIN [TreePaths] t ON c.comment_id = t.descendant
WHERE t.ancestor = 1;
К вашему сведению,
textустарел еще в 2005 году; вы уже давно должны были прекратить использовать этот тип данных.