VPF::Отображение данных при древовидной структуре

FMA

Дата 16.10.2009, 17:03 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 38
Регистрация: 30.4.2008

Репутация: нет
Всего: нет

Разрабатываю БД на Firebird 2.1.3, для доступа использую компоненты FibPlus.
Используется древовидная структура:

Код


CREATE TABLE GROUP_DATA (
    ID_GRD      INTEGER NOT NULL,
    NAME_GROUP  VARCHAR(250),
    ENTRY       INTEGER,
    CONSTRAINT PK_GROUP_DATA PRIMARY KEY (ID_GRD));

CREATE TABLE U_DATA (
    ID_D         INTEGER NOT NULL,
    ID_GRD       INTEGER NOT NULL,
    NAME_DATA     VARCHAR(250),
   CONSTRAINT PK_U_DATA PRIMARY KEY (ID_D),
   CONSTRAINT FK_U_DATA_1 FOREIGN KEY (ID_GRD) REFERENCES GROUP_DATA (ID_GRD));

В интерфейсной части программы показывается полученное дерево и данные в виде таб-цы. При перемещении по узлам дерева, показываются данные которые входят конкретно в группу.
Суть проблемы состоит в том как лучше организовать обработку показа всех данных, входящих в подгруппы выделенной группы?
Одновременно с программой будут работать несколько пользователей. Количество записей будте около 10 тыс.

Кто-нибудь сталкивался с такой проблемой?

Gluttton

Дата 16.10.2009, 17:35 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(FMA @ 16.10.2009, 17:03

)

Используется древовидная структура

Глубина вложености жестко фиксирована и равна единице?
В чем же тогда проблема?
1. Пользователь выбирает узел дерева.
2. Клиентское приложение определяет, какому ID_GRD соответствует этот узел.
3. Клиентское приложение вызывает на сервере зарение подготовленную храниму процедуру, которая делает примерно вот это:

Код


SET TERM ^ ;

CREATE OR ALTER PROCEDURE GET_NOD_CHILD (SELECTED_NODE INTEGER)
RETURNS 
(
    R_ID_D,
    R_NAME_DATA
)
AS
BEGIN
    SELECT ID_D, NAME_DATA
    FROM U_DATA
        WHERE ID_GRD=:SELECTED_NODE
    INTO :R_ID_D, :R_NAME_DATA;
END^

SET TERM ; ^

Передавая в качестве входного параметра полученный ранее ID_GRD.
4. Клиентское приложение принимает от сервера данные, которые возвращает хранимая процедура.

--------------------

Слава Україні!

FMA

Дата 16.10.2009, 18:11 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 38
Регистрация: 30.4.2008

Репутация: нет
Всего: нет

Вложенность может быть какой угодно, т.е. у одной группы может быть сколько угодно своих подгрупп, у которых также могут быть подгруппы.
С простой выборкой конкретной группы - проблем нет.
Самое интересное, как организовать выборку всех данных из всех подгрупп входящих в выбранную группу.

Gluttton

Дата 16.10.2009, 18:24 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(FMA @ 16.10.2009, 18:11

)

Вложенность может быть какой угодно, т.е. у одной группы может быть сколько угодно своих подгрупп, у которых также могут быть подгруппы.

А вот тогда, я не согласен со структурой БД smile

...
Если элемент структуры, может выступать в качестве структуры, то я предлагаю вот это:

Код


Parent Table
ID node (PK)

Children Table
ID record (PK),
ID node (FK)

Заменить на одну таблицу:

Код


Record Table
ID record (PK),
ID parent

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

--------------------

Слава Україні!

FMA

Дата 16.10.2009, 20:01 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 38
Регистрация: 30.4.2008

Репутация: нет
Всего: нет

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

Gluttton

Дата 16.10.2009, 21:00 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(FMA @ 16.10.2009, 20:01

)

Не совсем хорошо, т.к. у группы есть свои параметры, а у данных входящих в нее свои.

ОК. Тогда так:

Код


Parent Table
ID node (PK)
ID parent

Children Table
ID record (PK),
ID node (FK)

Цитата(FMA @ 16.10.2009, 20:01

)

Насчет отображения пользователю всех данных - не повесит ли он сетку при быстром переходе с одного узла на другой?

Не советчик в практических аспектах :( ...
Но в голову приходит следующее...
Можно сделать несколько хранимых процедур...
Первая - отрабатывает быстро и выбирает первые N записей.
Вторая - стартует в отдельном потоке и не зависимо от того потребуются ли пользователю другие данные или нет подгружает их в локальный DataSet...
Это могло бы быть оправдоно в том случае, если среднестатистического пользователя в большинстве случаев устраивали бы данные по умолчанию (т.е. результат первого запроса). Трафик от этого не уменьшится, но как бы удобнее... наверное... А можно ещё поток второго запроса периодически усыплять... А если например через какое то время t пользователь не "затребовал" других данных, то поток второго запроса можно убить... Это всё при том, что нет критической необходимости предоставлять пользователю обнавленные данные. А если есть, то всё намного сложнее smile

или в этой ветке или в соседней вопрос поддержания данных в актуальном состоянии уже обсуждался (я ничего не понял smile

)...
Это так... Пятничные мысли smile

...

--------------------

Слава Україні!

Gluttton

Дата 17.10.2009, 14:22 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(Gluttton @ 16.10.2009, 18:24

)

А вот тогда, я не согласен со структурой БД ...

Я это к тому, что не совсем понятно, как при существующей структуре БД реализовать под-узел?
Как узнать является ли данный узел корневым, или вложен в какой-нибудь узел?

А сделать это можно добавив поле ID parent, в котором бы хранился ID родительского узла или же 0 для корневых узлов...
Ещё раз обращу внимание на то, что при такой реализации составление рекурсивного запроса на обход узла является задачей тривиальной...

--------------------

Слава Україні!

jsa

Дата 18.10.2009, 16:00 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 704
Регистрация: 19.1.2006
Где: Новосибирск

Репутация: 1
Всего: 20

FMA, почитай статью, там много интересно описывается, я думаю многие вопросы отпадут сами собой

--------------------

Все мы, на перине с песней, строим небо на земле © Ю. Шевчук

FMA

Дата 18.10.2009, 18:19 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 38
Регистрация: 30.4.2008

Репутация: нет
Всего: нет

Цитата(Gluttton @ 17.10.2009, 14:22

)

Я это к тому, что не совсем понятно, как при существующей структуре БД реализовать под-узел?Как узнать является ли данный узел корневым, или вложен в какой-нибудь узел?

А в таблице дерева TABLE GROUP_DATA у меня есть поле ENTRY, в котором и хранится номер "родителя" данной подгруппы. (Надо было, его попроще обозвать. Извиняюсь).

Цитата(jsa @ 18.10.2009, 16:00

)

почитай статью, там много интересно описывается

Большое спасибо за ссылочку, статья и вправду полезная.

Gluttton

Дата 18.10.2009, 18:40 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

jsa, не совсем согласен...
Вот тут похожая тема обсуждалась...
Nested Sets необходимы там, гне нет возможности испльзовать рекурсию, а в Firebird такая возможность есть...
В Nested Sets удобно делать запросы на выборку, но запросы на вставку или изменение очень ресурсоемкие.
Так что это альтернатива, но не панацея smile

--------------------

Слава Україні!

jsa

Дата 19.10.2009, 04:48 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 704
Регистрация: 19.1.2006
Где: Новосибирск

Репутация: 1
Всего: 20

Цитата(Gluttton @ 18.10.2009, 23:40

)

Nested Sets необходимы там, гне нет возможности испльзовать рекурсию, а в Firebird такая возможность есть...

наверное я пропустил эту возможность
Gluttton, можно пожалуйста поподробнее, как использовать рекурсию в Firebird

Цитата(Gluttton @ 18.10.2009, 23:40

)

jsa, не совсем согласен...

не согласен с чем?
статья - очередной взгляд на проблему хранения деревьев в sql, я никого не принуждал, использовать способы, описанные в статье, и не говорил что это панацея

Добавлено через 1 минуту и 19 секунд

Цитата(Gluttton @ 18.10.2009, 23:40

)

В Nested Sets удобно делать запросы на выборку

дык это и была суть этого топика smile

--------------------

Все мы, на перине с песней, строим небо на земле © Ю. Шевчук

Deniz

Дата 19.10.2009, 06:21 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 22
Всего: 44

Цитата(jsa @ 19.10.2009, 07:48

)

наверное я пропустил эту возможность

сам пока не пользуюсь, но ...
Выдержка из этого документа

Код

WITH RECURSIVE
  R_TREE (ID, LEVEL, PATH) AS
  (
    SELECT ID, 0, CAST(ID AS VARCHAR(255))
      FROM TREE
      WHERE PARENT_ID IS NULL
      UNION ALL
      SELECT TREE.ID, R_TREE.LEVEL + 1,
      R_TREE.PATH || ‘.’ ||
      CAST(TREE.ID AS VARCHAR(8))
      FROM TREE, R_TREE
      WHERE TREE.PARENT_ID = R_TREE.ID
      AND R_TREE.LEVEL < 10
  )
SELECT TREE.*, R_TREE.LEVEL, R_TREE.PATH
  FROM TREE, R_TREE
  WHERE TREE.ID = R_TREE.ID

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

jsa

Дата 19.10.2009, 07:49 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 704
Регистрация: 19.1.2006
Где: Новосибирск

Репутация: 1
Всего: 20

Цитата(Deniz @ 19.10.2009, 11:21

)

сам пока не пользуюсь, но ...
Выдержка из этого документа

да... действительно пропустил smile

--------------------

Все мы, на перине с песней, строим небо на земле © Ю. Шевчук

Gluttton

Дата 19.10.2009, 07:56 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(jsa @ 19.10.2009, 04:48

)

Gluttton, можно пожалуйста поподробнее, как использовать рекурсию в Firebird

Приведу пример, но позднее...

Цитата(Gluttton @ 18.10.2009, 18:40

)

jsa, не совсем согласен...

Прошу не обращать внимания smile

!
Это я наверное сам с собой smile

...

Цитата(jsa @ 19.10.2009, 04:48

)

статья - очередной взгляд на проблему хранения деревьев в sql, я никого не принуждал, использовать способы, описанные в статье, и не говорил что это панацея

Согласен.

Цитата(jsa @ 19.10.2009, 04:48

)

дык это и была суть этого топика

Как бы да, но я не думаю, что топикстартер при работе с БД ограничится только выборкой, хотя это топикстартеру виднее smile

...

--------------------

Слава Україні!

Gluttton

Дата 19.10.2009, 17:02 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

UPD...
UPD, внес инменения на основании замечаний Deniz по организации каскадного удаления.
Deniz, спасибо за поддержку smile

!
jsa, как и обещал привожу пример работы с древовидными структурами на Firebird с использованием рекурсивных запросов.

Создадим БД, которая описывает файловую систему (очень и очень приблизительно smile

). На мой взгляд модель папок и файлов одна из самых наглядных для подобного рода примеров.
Создадим две таблицы: таблицу с директориями D и таблицу с файлами F. следующим образом:

Код


create table D 
(
    id        integer not null,           -- Первичный (суррогатный) ключ.
    name      varchar(255),               -- Имя папки.
    entry     integer default 0 not null, -- Код родительской папки.
    property  varchar(255)                -- Свойства папки.
);

Код


create table F (
    id         integer not null, -- Первичный (суррогатный) ключ.
    name       varchar(255),     -- Имя файла.
    directory  integer not null, -- Внешний ключ (код папки в которой расположен файл).
    property   varchar(255)      -- Свойства файла.
);

Создадим внешние ключи:

Код


alter table f  
    add constraint fk_f_d 
    foreign key(directory)
    references d(id) on delete cascade on update cascade;

alter table d
    add constraint fk_d_d
    foreign key(entry)
    references d(id) on delete cascade on update cascade;

Заполним БД тестовыми данными:

Код


insert into d(name, entry) values (':', 1);
insert into d(name) values ('system');
insert into d(name) values ('media');
insert into d(name) values ('games');
insert into d(name, entry) values ('video', 3);
insert into d(name, entry) values ('music', 3);
insert into d(name, entry) values ('action', 5);
insert into d(name, entry) values ('comedy', 5);

commit;

insert into f(name, directory) values ('system.sys', 2);
insert into f(name, directory) values ('readme.txt', 3);
insert into f(name, directory) values ('readme.txt', 4);
insert into f(name, directory) values ('films.xls', 5);
insert into f(name, directory) values ('actionfilm.avi', 7);
insert into f(name, directory) values ('veryactionfilm.avi', 7);
insert into f(name, directory) values ('noactionfilm.avi', 7);
insert into f(name, directory) values ('comedyfilm.avi', 7);

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

Получим примерно следующее:
Папки:
user posted image

Файлы:

Реализуем запрос на выборку всех объектов (как папок так и файлов) расположенных в заданой.
Создадим хранимую процедуру с рекурсивным запросом, которая принимает один параметр - папка в которой производиться поиск и возвращает две колонки данных, в первой будем отображать имена объектов, а во второй путь к объекту.

Код


set term ^ ;

recreate procedure sffinf
(
    folder varchar(255)
)
returns
(
    n varchar(255),
    p varchar(255)
)
as
begin
    for select h.name, h.path
    from
    (
        with recursive
        sub as
        (
            select D.id, D.name, D.entry, D.name as path
            from D
                where D.name=:folder
            union all
            select D.id, D.name, D.entry, sub.path||'\'||D.name as path
            from sub, D
                where D.entry=sub.id
            union all
            select null, F.name, null, sub.path||'\'||F.name as path
            from sub, F
                where F.directory=sub.id
        )
        select distinct
            sub.name,
            sub.path
        from D, sub
            order by sub.path
    )   as h
        into :n, :p
    do
    suspend;
end ^

set term ; ^

commit;

Теперь обратимся к БД с запросом:

Код


select * from sffinf(':');

В результате выполнения запроса получим следующие данные:
user posted image

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

Код


            select D.id, D.name, D.entry, sub.path||'\'||D.name as path
            from sub, D
                where D.entry=sub.id

Для выбора только файлов в указанной папке и ее подпапках, необходимо заменить код рекурсивного запроса на следующий:

Код


...
        with recursive
        sub as
        (
            select d.id, d.name, d.entry, d.name as path
            from d
                where d.name=:folder
            union all
            select d.id, null, d.entry, sub.path||'\'||d.name as path
            from sub, d
                where d.entry=sub.id
            union all
            select null, f.name, null, sub.path||'\'||f.name as path
            from sub, f
                where f.directory=sub.id
        )
        select distinct
            sub.name,
            sub.path
        from d, sub
            where sub.name is not null
            order by sub.path
...

Задача вставки данных решается относительно просто. Для этого необходимо создать хранимую процедуру без использования рекурсии, которая будет принимать например имя родительской папки и, не возвращая никаких параметров, создавать в ней папку (задача вставки файлов решается аналогично):

Код


set term ^ ;

recreate procedure ifinf
(
    folder varchar(255),
    entry varchar(255),
    property varchar(255)
)
as
begin
    insert into D (name, entry, property)
    select :folder, D.id, :property
    from D
        where D.name=:entry;
end ^

set term ; ^

commit;

Теперь можно вставлять данные в БД использую следующий запрос:

Код


execute procedure ifinf('pictures', 'media', 'inserted');

Выполним вновь запрос на отображение данных
Вот примерно, что должно получиться:
user posted image

Задача удаления папки (и конечно же всего её содержимого) может быть решена несколькими способами.
В обоих случаях процедура принимает одно значение - имя папки, которую необходимо удалить и не возвращает значений.
Наиболее простой (предложенный Deniz) способ - это использовать ограничения каскадного удаления. Запрос на создание хранимой процедуры может выглядеть так:

Код


recreate procedure dffinf
(
    folder varchar(255)
)
as
begin
    delete from d
    where d.name=:folder;
end ^

Второй способ, более громоздкий и ресурсоёмкий – это без использования ограничений. Я удалил его из поста, дабы не захламлять последний, но оставил в закомментированном виде в скрипте, который присоединен к посту. Он может быть полезен как шаблон для решения задачи удаления только файлов в выбранной папке и её подпапках (для этого необходимо из приведенного запроса удалить первый оператор delete).
Выполним следующий запрос:

Код


execute procedure dfinf('media');

И вновь проверим содержимое папки 'media':

Код


select * from sffinf('media');

Должно получиться примерно следующее:
user posted image

Выполним запрос на отображени папки 'system':

Код


select * from sffinf('system');

Получиться:
user posted image

Реализация запросов на обновление данных всех объектов папки аналогична реализации запроса на выборку данных. Запрос на обновления приведен в прилагаемом скрипте.

Работает smile

...

Вот скрипт по созданию демонстрационной БД.

Это сообщение отредактировал(а) Gluttton - 20.10.2009, 10:30

Присоединённый файл ( Кол-во скачиваний: 10 )

FS.sql 11,02 Kb

--------------------

Слава Україні!

jsa

Дата 20.10.2009, 07:40 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 704
Регистрация: 19.1.2006
Где: Новосибирск

Репутация: 1
Всего: 20

Gluttton, отличный пример smile

--------------------

Deniz

Дата 20.10.2009, 07:47 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 22
Всего: 44

Цитата(Gluttton @ 19.10.2009, 20:02

)

Задача удаления папки (и конечно же всего её содержимого) может быть решена следующим образом.
Процедура принимает одно значение - имя папки, которую необходимо удалить и не возвращает значений.
Создатим хранимую процедуру, аналогичную процедуре веборке:

а зачем так сложно?
Создать ссылочную целостность и on delete cascade и файлов и папок.

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

Gluttton

Дата 20.10.2009, 08:00 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(jsa @ 20.10.2009, 07:40

)

Gluttton, отличный пример

Спасибо!

Цитата(Deniz @ 20.10.2009, 07:47

)

а зачем так сложно?Создать ссылочную целостность и on delete cascade и файлов и папок.

Хм... Об этом как то и не подумал... Попробую реализовать удаление с использованием ссылочной целосности. Спасибо за рецензирование smile

... Это ж какое терпение нужно иметь, что бы такой пост длинючий осилить smile

--------------------

Слава Україні!

jsa

Дата 20.10.2009, 08:30 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 704
Регистрация: 19.1.2006
Где: Новосибирск

Репутация: 1
Всего: 20

Цитата(Deniz @ 20.10.2009, 12:47

)

Создать ссылочную целостность и on delete cascade и файлов и папок.

вот только будет ли работать delete cascade в рекурсии

--------------------

Deniz

Дата 20.10.2009, 08:44 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1251
Регистрация: 16.10.2004
Где: Новый Уренгой

Репутация: 22
Всего: 44

Цитата(jsa @ 20.10.2009, 11:30

)

вот только будет ли работать delete cascade в рекурсии

будет, проверено опытом.
Если использовать пример Gluttton, то запрос

Код

delete from d where name = ':';

очистит таблицу D и F

--------------------

"Для того чтобы сделать шаг вперед, достаточно пинка сзади" (с)

Gluttton

Дата 20.10.2009, 10:34 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 7
Всего: 54

Цитата(jsa @ 20.10.2009, 08:30

)

вот только будет ли работать delete cascade в рекурсии

Проверил, будет smile

.

Deniz, спасибо за подсказку в этой и соседней теме smile

... Внёс соответсвтующие изменения в свой пример.

--------------------

Слава Україні!

FMA

Дата 26.10.2009, 20:15 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 38
Регистрация: 30.4.2008

Репутация: нет
Всего: нет

Gluttton, спасибо за пример с рекурсией.
И всем остальным тоже Большое спасибо за помощь.

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Firebird, Interbase \| Следующая тема »