Создание деревьев. Хранение данных на примере категорий.

Создание деревьев. Хранение данных на примере категорий.
Способы хранения данных. Материализованный путь и смежные вершины.

В процессе разработки этого блога столкнулся с проблемой выбора способа хранения данных, в частности категорий для статей. В этой статье я хочу поделится моим скромным опытом в этом вопросе. Статья написана на примере базы данных SQLite, которая на мой взгляд вполне подходит для небольшого блога. 

На самом деле способов хранений деревьев в базе данных довольно много, и у каждого есть свои недостатки и преимущества, однако я выбрал способ: Adjacence List(список смежности) + Materialized Path(материализованный путь). 

Почему именно так, а не Nested Sets например? Ну во первых по операциям выборки ветвей Materialized Path не сильно уступает в производительности тому же Nested Sets, а на операциях изменении структуры, насколько мне известно, даже обходит его. Во вторых при таком способе хранения довольно  удобно  генерировать "Хлебные крошки" для навигации по сайту.

итак, для начала создадим структуру: 

CREATE TABLE tbl_category
(	
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	parent_id INTEGER,
	name VARCHAR(128) NOT NULL,	
	path VARCHAR(128) NOT NULL UNIQUE DEFAULT '',
	CONSTRAINT FK_parent_id FOREIGN KEY (parent_id)
		REFERENCES tbl_category (id) ON DELETE CASCADE ON UPDATE RESTRICT
);

Мы создали таблицу и поставили ограничение Foreign Keys на id родителя, которое нам понадобятся для каскадного удаления дочерних категорий. В этом случай надо быть внимательным при удалении родительских категорий, если вы хотите оставить все дочерние категории, то можете программно перенести ветку с детьми в корневую или любую другую категорию. Вы также можете убрать это ограничение и написать отдельный триггер, срабатывающий при удалении родителя.

далее добавляем триггер базы данных на событие insert:

CREATE TRIGGER set_path AFTER INSERT ON tbl_category
BEGIN    
    UPDATE tbl_category
        SET path = CASE
			WHEN parent_id IS NULL THEN NEW.id || '/'
			ELSE (
					SELECT path || NEW.id || '/'
						FROM tbl_category
						WHERE id = NEW.parent_id
			     )
		END
		WHERE id = NEW.id;
END;

теперь при добавлении категорий в поле path автоматически будет создаваться путь вида cat1/cat2/.../ отражающий текущую иерархию. 

ну и наконец добавляем триггер для группового обновления пути у дочерних категорий:

CREATE TRIGGER group_change_path AFTER UPDATE OF parent_id ON tbl_category
BEGIN
 SELECT
  CASE
   WHEN NEW.parent_id IS OLD.id 
     THEN RAISE(ABORT,'Поле parent_id ссылается на первичный ключ текущей записи!')
  END;    
  UPDATE tbl_category
        SET path = REPLACE(
                path,
                OLD.path,
                CASE
                    WHEN NEW.parent_id IS NULL THEN OLD.id || '/'
                    ELSE (
                            SELECT path || OLD.id || '/'
                            FROM tbl_category
                            WHERE id = NEW.parent_id
                         )
                END
            )
        WHERE path LIKE OLD.path || '%';
END;

 при смене родителя, этот триггер обновит путь у всех его детей. 

Одна из особенность базы данных SQLlite состоит в том что в ней по умолчанию отключены ограничения внешних ключей, и чтобы заставить их работать, при всех операциях с данными необходимо их включить принудительно используя команду:

PRAGMA foreign_keys = ON;

Что мы имеем в результате:

  • Удобная выборка подкатегорий без использования рекурсии(например выбрать все статьи категории и подкатегорий можно одним запросом: "SELECT * FROM tbl_category WHERE path LIKE path || '_%' )" 
  • Возможность легко переносить категории из одной ветки другую (достаточно сменить parent_id у категории)
  • Простая генерация "Хлебных Крошек" , достаточно разобрать поле path.

Я не буду приводить тут тестов производительности, это можете сделать самостоятельно, в следующей статье я опишу как можно использовать данную таблицу применительно к Yii Framework для дальнейших манипуляций с категориями.

Комментарии

Нет комментариев

Добавить комментарий