UPD...
UPD, внес инменения на основании замечаний Deniz по организации каскадного удаления.
Deniz, спасибо за поддержку  !
jsa, как и обещал привожу пример работы с древовидными структурами на Firebird с использованием рекурсивных запросов.
Создадим БД, которая описывает файловую систему (очень и очень приблизительно ). На мой взгляд модель папок и файлов одна из самых наглядных для подобного рода примеров.
Создадим две таблицы: таблицу с директориями 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);
|
Для первичных ключей реализованы соответсвующие генераторы и триггеры для из заполнения автоинкрементными значениями.
Получим примерно следующее:
Папки:
Файлы:
Реализуем запрос на выборку всех объектов (как папок так и файлов) расположенных в заданой.
Создадим хранимую процедуру с рекурсивным запросом, которая принимает один параметр - папка в которой производиться поиск и возвращает две колонки данных, в первой будем отображать имена объектов, а во второй путь к объекту.
| Код |
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(':');
|
В результате выполнения запроса получим следующие данные:
Очевидно, что для того, что бы реализовать выбор только каталогов в указаной папке необходимо из хранимой процедуры удалить один из рекурсивных подзапросов:
| Код |
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');
|
Выполним вновь запрос на отображение данных
Вот примерно, что должно получиться:
Задача удаления папки (и конечно же всего её содержимого) может быть решена несколькими способами.
В обоих случаях процедура принимает одно значение - имя папки, которую необходимо удалить и не возвращает значений.
Наиболее простой (предложенный 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');
|
Должно получиться примерно следующее:
Выполним запрос на отображени папки 'system':
| Код |
select * from sffinf('system');
|
Получиться:
Реализация запросов на обновление данных всех объектов папки аналогична реализации запроса на выборку данных. Запрос на обновления приведен в прилагаемом скрипте.
Работает ...
Вот скрипт по созданию демонстрационной БД. |
)
.