Иерархические структуры в Базе Данных


\| Уроки \|

Иерархические структуры в Базе Данных FireBird и работа с ними из C++Builder.

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

Для написания этого примера, исходил из того, что полностью формировать и показывать дерево целиком вряд-ли когда может понадобиться, особенно если в таблице, содержащей иерархические данные несколько тысяч или десятков тысяч данных. Хотя предусмотрел и полное рисование дерева. Предусмотрены следующие возможности — формирование дерева на основании таблицы базы данных, редактирование,
добавление, удаление узлов дерева, быстрый поиск и перерисовка нужной части дерева, перенос мышкой узлов дерева.

Итак, исходные данные — база данных FireBird, отображать дерево будем в стандартном компоненте TTreeView,
работать с базой через компоненты доступа FIBPLus, хотя разумеется можно организовать работу и через стандартные компоненты доступа.

Проектирование базы данных.

База данных — FireBird, то есть поддерживает механизм триггеров и хранимых процедур, которые мы и будем использовать. Структура таблицы, содержащей иерархические данные стандартная, и у нас в примере будет следующая — таблица "Изделия и детали":

CREATE TABLE DETAIL (
ID     INTEGER NOT NULL,
PARENT INTEGER,
CCOUNT INTEGER DEFAULT 0,
NAME   VARCHAR(60) COLLATE PXW_CYRL
);
ALTER TABLE DETAIL ADD CONSTRAINT PK_DETAIL PRIMARY KEY (ID);
ALTER TABLE DETAIL ADD CONSTRAINT FK_DETAIL FOREIGN KEY (PARENT)
              REFERENCES DETAIL (ID) ON DELETE CASCADE ON UPDATE CASCADE;

Итак, с полями ID и PARENT все понятно — поле ID — первичный ключ таблицы, поле PARENT показывает родителя для данной детали и является внешним ключем этой же таблицы на поле ID. Кроме того, обратите внимание, что поле PARENT не задано как NOT NULL, то есть может быть NULL — таковым у нас и будет самая верхняя запись таблицы — "Изделия и детали".
Также обратите внимание на не очень очевидное поле CCOUNT — количество прямых потомков у данной записи, которое очень облегчит нам жизнь в дальнейшем. Постараемся переложить максимально большее количество правил базы данных, проверок и ограничений на саму базу данных — меньше придется писать в клиентском приложении и меньше ошибок при этом мы сможем допустить.
Итак — в поле CCOUNT у нас будет количество прямых потомков у нашей записи, и следить за этим полем мы поручим самой базе данных с помощью 3 триггеров(см. ниже).

Поскольку поле ID — ключ нашей таблицы, это поле будет AUTOINC,
создадим в базе данных для него генератор

CREATE GENERATOR GEN_DETAIL_ID;
SET GENERATOR GEN_DETAIL_ID TO 0;

Cоздадим исключение для вершины дерева.

CREATE EXCEPTION E_DET 'Эту запись удалить нельзя !';

Создадим 3 триггера для таблицы DETAIL:

CREATE TRIGGER DETAIL_BI0 FOR DETAIL
ACTIVE BEFORE INSERT POSITION 0
AS
begin
UPDATE DETAIL D SET D.CCOUNT = D.CCOUNT + 1 WHERE D.ID = new.PARENT;
end

CREATE TRIGGER DETAIL_BD0 FOR DETAIL
ACTIVE BEFORE DELETE POSITION 0
AS
begin
if(OLD.ID = 1) then exception "E_DET";
UPDATE DETAIL D SET D.CCOUNT = D.CCOUNT — 1 WHERE D.ID = old.PARENT;
end

CREATE TRIGGER DETAIL_BU0 FOR DETAIL
ACTIVE BEFORE UPDATE POSITION 0
AS
begin
if (old.PARENT <> new.PARENT) then
begin
UPDATE DETAIL D SET D.CCOUNT = D.CCOUNT — 1 WHERE D.ID = old.PARENT;
UPDATE DETAIL D SET D.CCOUNT = D.CCOUNT +1 WHERE D.ID = new.PARENT;
end
end

Вставим в таблицу первую запись — вершину нашего дерева:

Insert into DETAIL (ID, NAME) values(1, 'Изделия и детали')

эта запись будет единственной вечной вершиной, удалить ее мы не дадим, для этого
и было создано исключение и внесена соответствующая проверка в триггер перед удалением записи.

Реализуем несколько хранимых процедур, для работы с иерархическими стркутурами, которые и будем использовать в работе:

1 Хранимая процедура GETCHILDS выберет из таблицы всех потомков указанной записи.

CREATE PROCEDURE GETCHILDS (ID_P INTEGER)
RETURNS (ID_CHILD INTEGER)
AS
begin
       FOR SELECT D.ID FROM DETAIL D
       WHERE D.PARENT = :ID_P INTO :ID_CHILD
DO
begin
     suspend;
     IF(NOT EXISTS (SELECT * FROM DETAIL WHERE DETAIL.ID = :ID_CHILD)) THEN
           BEGIN SUSPEND; END
     ELSE
           BEGIN
                FOR SELECT ID_CHILD FROM GETCHILDS(:ID_CHILD) INTO :ID_CHILD
           DO BEGIN SUSPEND; END
     END
end
end

В качестве входного параметра она получает ID интересующей записи, и возвращает для нее список ID всех ее детей.

2. Хранимая процедура GETPARENTS выберет из таблицы всех родителей указанной записи вплоть до самой вершины (у нас это первая запись таблицы "Изделия и детали"), со всеми их данными.

ALTER PROCEDURE GETPARENTS (ID INTEGER)
RETURNS (DID INTEGER,OID INTEGER,NAME VARCHAR(60),CCOUNT INTEGER)
AS
BEGIN
        WHILE (:ID > 0) DO
            BEGIN
                 SELECT O.ID, O.PARENT, O.NAME, O.CCOUNT
                 FROM DETAIL O
                 WHERE O.ID = :ID
                 INTO :DID, :OID, :NAME , :CCOUNT;
                  ID = :OID;
                  SUSPEND;
            END
END

3. Хранимая процедура CHECK1_TREE проверяет, является-ли данная запись родителем некоторой другой записи, эта процедура нам понадобиться для проверки возможности перетаскивания записей в дереве при помощи технологии Drag and Drop

CREATE PROCEDURE CHECK1_TREE (S_ID INTEGER,D_ID INTEGER)
RETURNS (IS_CHILD INTEGER)
AS
DECLARE VARIABLE DID INTEGER;
begin
For Select ID_CHILD from GETCHILDS(:D_ID) into :DID
do begin
if(DID = S_ID) then
begin
IS_CHILD = 1;
suspend;
exit;
end
end
IS_CHILD = 0;
suspend;
end

То есть, если запись D_ID является родителем для записи S_ID, то в результате выполнения этой хранимой процедуры будет возвращено 1, если не является — будет возвращен 0.

Проектирование приложений.

Ну, вроде с базой данных разобрались. Теперь перейдем непосредственно к приложению на C++Builder
Разместите на форме компоненты TpFIBDatabase, TpFIBTransaction, 2 компонента типа
TpFIBDataSet. Свяжите их между собой и подключитесь к базе.
Компонент типа TpFIBDataSet работающий с таблицей DETAIL назовем Detail, назовем второй компонент типа TpFIBDataSet FTree.

Для вершины дерева, которое не имеет ничего над собой, поле PARENT равно NULL.
Формировать уровни дерева мы будем с помощью запроса к таблице (компонент FTree)

Разместите на форме компоненты TTreeView и TImageList, компонент для работы с деревом типа TTreeView назовем Tree, компонент типа TImageList назовите IList. В свойстве Images у Tree задайте IList
Поместите в IList 5 разных иконок, иконка с индексом 0 — будет иметь вид папки, с индексами 4 и 5 — для листьев дерева 5 — обычная иконка, 4 — при выделении данного листа.

Заполним свойства компонента Detail:
SelectSQL: Select * from DETAIL order by ID
и сгенерим остальные: UpdateSQL , InsertSQL, DeleteSQL, RefreshSQL
Заполним свойства AutoUpdateOptions компонента Detail

GeneratorName = GEN_DETAIL_ID
KeyFields = ID
Update Table = Detail
WhenGetGenID = wgOnNewRecord

// Откроем таблицу, нарисуем вершину дерева
void __fastcall TForm1::FormShow(TObject *Sender)
{
Detail->Open();
ExpandLevel(NULL, -1); // рисуем дерево — верхний уровень
}
//---------------------------------------------------------------------------

Итак, мы решили, что считывать и формировать все дерево сразу нет смысла, значит будем делать это частями по мере необходимости — по запросу пользователя, то есть по счелчку на [+] у нужного узла дерева. Для этого напишем функцию раскрытия узла дерева ExpandLevel

// раскрыть указанный уровень
TTreeNode* __fastcall TForm1::ExpandLevel(TTreeNode* Node, int searchID)
{
TTreeNode* TreeNode, *searchNode=NULL;
int ID = (Node == NULL)? 0 : (int)Node->Data;
AnsiString sql = "Select * from DETAIL where PARENT ";
if(ID) sql = sql + "= "+IntToStr(ID); else sql = sql + "is NULL"; FTree->Close();
FTree->SelectSQL->Clear(); FTree->SelectSQL->Add(sql);
FTree->Open(); Tree->Items->BeginUpdate();
while(!FTree->Eof){
      // Запомним в поле Data ветки ее идентификационный номер (ID) в таблице
      TreeNode = Tree->Items->AddChildObject(Node , FTreeNAME->AsString, (void*)FTreeID->AsInteger);
      TreeNode->ImageIndex = 0; TreeNode->SelectedIndex = 0;
      // если задан также режим поиска, вернем искомый Node
      if(searchID != -1 && FTreeID->AsInteger == searchID) searchNode = TreeNode;
      // Добавим фиктивную (пустую) дочернюю ветвь только для того,
      // чтобы был отрисован [+] на ветке и ее можно было бы раскрыть,
      // у узла у которогое дети реально есть != 0 (вот оно — пригодилось поле CCOUNT)
      if(FTree->FieldByName("CCOUNT")->AsInteger)
          Tree->Items->AddChildObject(TreeNode , "" , NULL);
      else { // это лист — устанавливаем другую иконку для листа
            TreeNode->ImageIndex = 5; TreeNode->SelectedIndex = 4;
           }
      FTree->Next();
      }
Tree->Items->EndUpdate();
FTree->Close();
return searchNode;
}
//---------------------------------------------------------------------------

//На событие OnExpanding сформируем реальную ветку, предварительно удалив фиктивную.
void __fastcall TForm1::TreeExpanding(TObject *Sender, TTreeNode *Node,
bool &AllowExpansion)
{
     Node->DeleteChildren(); ExpandLevel(Node, -1);
}
//---------------------------------------------------------------------------

В общем, это немного переработанный и дополненный пример функции из статьи.
А дополнен этот пример вот чем: у моего примера нет [+] у каждого узла, в отличие от примера из этой статьи,
с помощью поля CCOUNT мы всегда ! знаем, есть ли реально дочерние ветви или нет, и нужно ли нам рисовать [+] или нет.
Также эта функция стала выполнять и другую работу — если задан режим поиска
(SearchID != -1), то фунция также вернет узел TTreeNode для искомого searchID -
идентификационного номера детали, если он содержиться в прямых потомках данного узла.

Напишем и другие необходимые функции.

Движение по дереву.

// двигаемся по дереву (например стрелками, мышкой и т.д.)
void __fastcall TForm1::TreeChange(TObject *Sender, TTreeNode *Node)
{
   if(Tree->Selected && Node){
        int ID = (int)Node->Data;
        Detail->Locate("ID",ID,TLocateOptions());
       }
}
//---------------------------------------------------------------------------

Редактирование текста узла дерева.

// редактирование текста узла дерева
void __fastcall TForm1::TreeEdited(TObject *Sender, TTreeNode *Node,
AnsiString &S)
{
   Detail->Locate("ID", (int)Node->Data, TLocateOptions());
   Detail->Edit();
      Detail->FieldByName("NAME")->Value = S;
   Detail->Post();
}
//---------------------------------------------------------------------------

Быстрая перерисовка дерева по ID.

// перерисовка дерева по известному ID номеру
// воспользуемся хранимой процедурой GETPARENTS для получения пути вверх,
// для этого разместим на форме еще один компонент DSet типа TpFIBDataSet
// в его свойстве SelectSQL напишем: Select * from GETPARENTS(:ID)
void __fastcall TForm1::PaintTree(int ID)
{
TTreeNode* Node = NULL;

DSet->ParamByName("ID")->Value = ID;
DSet->Prepare();
DSet->Open(); // получим список всех PARENT до самой вершины
int CCount = DSet->FieldByName("CCOUNT")->AsInteger; // кол-во детей
DSet->Last(); // начинаем с последнего, то есть с вершины
Tree->Items->Clear();           // очищаем дерево
Tree->OnExpanding = NULL; // отключаем обработчик
Tree->Items->BeginUpdate();
while(!DSet->Bof){
   Node = ExpandLevel(Node, DSet->FieldByName("DID")->AsInteger);
   if(Node){
        if(Node->HasChildren) Node->DeleteChildren();
        Node->Expanded = true;
        Node->Selected = true;
        }
   DSet->Prior();
   }
DSet->Close();
Tree->Items->EndUpdate();
Tree->OnExpanding = TreeExpanding; // включаем обработчик
// есть дети? — рисуем у узла[+]
if(CCount) ExpandLevel(Node, DSet-FieldByName("DID")->AsInteger);
}
//--------------------------------------------------------------------------

Поиск по базе с быстрой перерисовкой дерева.

// вышеуказанная функция теперь позволит нам организовать быстрый поиск
// по дереву, с перерисовкой лишь неободимой части дерева
// бросьте на форму компонент eSearch типа TEdit,
// в его обработчик OnChange напишем:

void __fastcall TForm1::eSearchChange(TObject *Sender)
{
if(eSearch->Text == "") return;
if(Detail->Locate("NAME", eSearch->Text, TLocateOptions()<<loPartialKey<<loCaseInsensitive))
   {
    PaintTree(Detail->FieldByName("ID")->AsInteger);
    eSearch->SetFocus();                                                          // вернуться в окно поиска
    eSearch->SelStart = eSearch->Text.Length(); // в конец слова
   }
}
//---------------------------------------------------------------------------

При наборе первых символов, курсор в базе и в дереве будет становиться на ближайшую похожую запись, максимально быстро перерисовывая лишь путь до искомой части дерева.
Если Вы в таблице DETAIL также создадите и другие поля — номер детали, описание, цена, и т.д. то быстрый поиск по этим полям организовать будет теперь также просто !
Создадим эти поля:
Alter table DETAIL add NUM integer
Alter table DETAIL add ARTICUL varchar(20)
Alter table DETAIL add PRICE double precision
...
Поместите на форму компонент CBox типа TComboBox, впишите в его свойство Items русские названия этих полей, типа "Название детали", "Номер", "Артикул","Цена" и т.д., создайте массив с именами реально созданных полей таблицы DETAIL:
AnsiString sField[]={"NAME", "NUM", "ARTICUL", "PRICE"};
Тогда текст нашего обработчика eSearchChange изменится незначительно:

...
if(Detail->Locate(sField[CBox->ItemIndex], Variant(eSearch->Text),
TLocateOptions()<<loPartialKey<<loCaseInsensitive))
{
...

Осталось рассмотреть еще 2 очень важных вопроса -
1. Полное рисование дерева — мало-ли что нужно клиенту ???
2. Возможность перемещения узлов в дереве с помощью технологии Drag&&Drop

Полное рисование дерева.

// -------------------------------------------------------------------------
// полное рисование и раскрытие дерева по нажатию кнопки
// b_TreeFullExpand типа TButton
// бросьте на форму кнопку, назовите ее b_TreeFullExpand в ее обработчик
// OnClick впишем:
void __fastcall TForm1::b_TreeFullExpandClick(TObject *Sender)
{
int p;
Tree->Items->Clear(); // очищаем дерево
Tree->Items->BeginUpdate(); // запрещаем перерисовку

Detail->First();
while(!Detail->Eof){ // формируем дерево
p = Detail->FieldByName("PARENT")->AsInteger;
Tree->Items->AddChildObject(FindData(p),
Detail->FieldByName("NAME")->AsString,
(void*)(Detail->FieldByName("ID")->AsInteger));
Detail->Next();
}
Tree->FullExpand(); // полностью раскрываем нарисованное дерево
Tree->Items->EndUpdate(); // включаем перерисовку
Detail->First();
Tree->SetFocus();
Tree->Items->Item[0]->Selected = true;
}
//---------------------------------------------------------------------------
TTreeNode* TForm1::FindData(int aData)
{
TTreeNode* Res = NULL;
if(Tree->Items->Count == 0) return NULL;
Res = Tree->Items->Item[0];
while(Res){
if((int)(Res->Data) == aData) return Res;
else Res = Res->GetNext();
}
return Res;
}
//---------------------------------------------------------------------------

Перемещения узлов в дереве с помощью технологии Drag&&Drop

Перемещать узлы будем мышкой, нажав предварительно клавишу <Ctrl>

TTreeNode* ddNode;
//---------------------------------------------------------------------------
// перемещение узлов дерева по CTRL + mbLeft
void __fastcall TForm1::TreeMouseDown(TObject *Sender, TMouseButton Button,
TShiftState Shift, int X, int Y)
{
  if(Button == mbLeft && Shift.Contains(ssCtrl)){
      ddNode = Tree->GetNodeAt(X,Y);                              // запоминаем перемещаемый узел
      if(ddNode == Tree->Items->Item[0]) return; // вершину никогда не трогаем
      Tree->BeginDrag(true, 1);                                         // включаем режим переноса
     }
}
//---------------------------------------------------------------------------
// бросьте на форму компонент Ds типа TpFIBDataSet
// проверяем — разрешено ли перемещение в данный узел
// 1. перенесен откуда-то еще — нельзя 2. сам в себя — нельзя
// 3. сверху вниз в себя — нельзя
void __fastcall TForm1::TreeDragOver(TObject *Sender, TObject *Source,
int X, int Y, TDragState State, bool &Accept)
{
if(Sender != Source) { Accept = false; return; } // 1. принесли извне — отказать
TTreeNode* SourceNode = Tree->GetNodeAt(X,Y);
if(SourceNode == NULL) { Accept = false; return; } // вне Node — отказать
if(SourceNode == ddNode){ Accept = false; return; } // 2. сам в себя — отказать

// 3. сверху вниз в себя — нельзя
Ds->SelectSQL->Clear();
AnsiString sql = "Select IS_CHILD from CHECK1_TREE(:S_ID,:D_ID)";
Ds->SelectSQL->Add(sql);
Ds->ParamByName("S_ID")->Value = (int)SourceNode->Data;
Ds->ParamByName("D_ID")->Value = (int)ddNode->Data;
Ds->Prepare();
Ds->Open();
int ddCheck = Ds->FieldByName("IS_CHILD")->Value;
Ds->Close();
if(ddCheck == 1) Accept = false; else Accept = true;
}
//---------------------------------------------------------------------------

// собственно сам перенос (Qr — компонент типа TpFIBQuery)
void __fastcall TForm1::TreeDragDrop(TObject *Sender, TObject *Source,
int X, int Y)
{
TTreeNode* SourceNode = Tree->GetNodeAt(X,Y);
if(SourceNode == ddNode) return; // сам в себя
int ddID = (int)ddNode->Data;         // ID переносимого узла
int sID = (int)SourceNode->Data; // ID нового родителя
Qr->SQL->Clear();
AnsiString sql = "Update DETAIL set PARENT = :sID where ID = :ddID";
Qr->SQL->Add(sql);
Qr->ParamByName("sID")->Value = sID;
Qr->ParamByName("ddID")->Value = ddID;
Qr->Prepare();
Qr->ExecQuery();
Qr->Close(); Detail->CloseOpen(false);
Detail->Locate("ID", ddID, TLocateOptions());
PaintTree(ddID);
}
//---------------------------------------------------------------------------

Вот собственно и все.

Код работающего примера к С++Builder 6 и базу данных можно скачать здесь (50 Кб).
Учтите, что в C++Builder 6 у Вас должны быть установлены компоненты FIBPlus
и в системе должен быть установлен сервер FireBird 1.5
Если у Вас есть какие-либо замечания, пожелания, уточнения — пишите.
Статьи, на основе которых был создан данный пример — вот они:

http://www.ibase.ru/devinfo/treedb.htm
http://www.ibase.ru/devinfo/treedb2.htm
http://dynamic.nm.ru/Articles/extreeview.htm
http://www.delphikingdom.com/asp/viewitem.asp?catalogid=488