Вашему вниманию предлагается пример организации данных типа Registry
в БД. В таблице хранятся разнородные данные, процедуры обеспечивают выбору
всей иерархии в нужном порядке и поиск по ключу. Хотя это и не самый быстрый
способ (безусловно, обращение к базе данных медленнее чем к Registry Windows
95 или Windows NT), зато есть возможность настойки системы .
Такую структуру можно использовать для хранения настроек пользователя
независимо от клиентского компьютера, с которого пользователь работает
с базой данных (естественно на каждого - свои настройки) и т.п.
CREATE EXCEPTION NOT_UNIQUE_NAME 'Not unique name in group';
CREATE EXCEPTION INTEGRITY_ERROR 'Error in hierarchy';
SET TERM !! ;
CREATE PROCEDURE GET_REGISTRY_SUBKEYS(parent_code integer, lev
smallint)
RETURNS( Key_code integer,
Key_name
varchar(64),
Key_type
smallint,
level_num
smallint)
AS
BEGIN
FOR SELECT Key_code, Key_name, Key_type
FROM REGISTRY
WHERE Key_parent = :parent_code
INTO :Key_code, :Key_name, :Key_type
DO BEGIN
level_num = lev;
SUSPEND;
FOR SELECT Key_code,
Key_name, level_num, Key_type
FROM GET_REGISTRY_SUBKEYS(:Key_code,
:lev+1)
INTO :Key_code, :Key_name,
:level_num, :Key_type
DO SUSPEND;
END
EXIT;
END!!
CREATE PROCEDURE GET_REGISTRY
RETURNS( Key_code integer,
Key_name
varchar(64),
Key_type
smallint,
level_num
smallint)
AS
BEGIN
FOR SELECT Key_code, Key_name, Key_type
FROM REGISTRY
WHERE Key_parent is null
INTO :Key_code, :Key_name, :Key_type
DO BEGIN
level_num = 0;
SUSPEND;
FOR SELECT Key_code,
Key_name, level_num, key_type
FROM GET_REGISTRY_SUBKEYS(:Key_code,
1)
INTO :Key_code, :Key_name,
:level_num, :key_type
DO SUSPEND;
END
EXIT;
END!!
CREATE PROCEDURE FIND_REGISTRY_SUBKEY( FullKey VarChar(1000),
PartialKey VarChar(1000),
Parent Integer)
RETURNS( Key_code integer,
Key_name
varchar(64),
Key_type
smallint )
AS
BEGIN
SELECT Key_code, Key_name, key_type
FROM REGISTRY
WHERE Key_parent = :parent
AND :FullKey LIKE :PartialKey||key_name||'\%'
INTO :Key_code, :Key_name, :key_type;
IF (Key_code is null) THEN EXIT;
IF (PartialKey||Key_name||'\' <> FullKey) THEN BEGIN
key_type = NULL;
SELECT Key_code, Key_name, key_type
FROM FIND_REGISTRY_SUBKEY( :FullKey, :PartialKey||:key_name||'\',
:Key_code)
INTO :Key_code, :Key_name, :key_type;
END
IF (Key_type is not null) THEN SUSPEND;
END!!
CREATE PROCEDURE FIND_REGISTRY_ENTRY( FullKey VarChar(1000))
RETURNS( Key_code integer,
Key_name
varchar(64),
Key_type
smallint)
AS
BEGIN
SELECT Key_code, Key_name, key_type
FROM REGISTRY
WHERE Key_parent is null
AND :FullKey LIKE key_name||'\%'
INTO :Key_code, :Key_name, :key_type;
IF (Key_code is null) THEN EXIT;
IF (Key_name||'\' <> FullKey) THEN BEGIN
key_type = NULL;
SELECT Key_code, Key_name, key_type
FROM FIND_REGISTRY_SUBKEY( :FullKey, :key_name||'\',
:Key_code)
INTO :Key_code, :Key_name, :key_type;
END
IF (Key_type is not null) THEN SUSPEND;
END!!
CREATE TRIGGER REGISTRY_INSERT FOR REGISTRY
BEFORE INSERT AS
BEGIN
IF (new.key_parent is null) THEN
IF (EXISTS(SELECT key_name FROM REGISTRY
WHERE key_parent is
null
AND key_name
= new.key_name)) THEN
EXCEPTION NOT_UNIQUE_NAME;
IF (new.key_parent is not null) THEN
IF (EXISTS(SELECT key_name FROM REGISTRY
WHERE key_parent=new.key_parent
AND key_name = new.key_name)) THEN
EXCEPTION NOT_UNIQUE_NAME;
SELECT MAX(key_code) FROM REGISTRY
INTO new.key_code;
IF (new.key_code is null) then new.key_code = 1;
ELSE new.key_code = new.key_code + 1;
END!!
CREATE TRIGGER REGISTRY_UPDATE FOR REGISTRY
BEFORE UPDATE
AS
BEGIN
IF (new.key_name <> old.key_name) THEN BEGIN
IF (new.key_parent is null) THEN
IF (EXISTS(SELECT key_name
FROM REGISTRY
WHERE key_parent is null AND key_name = new.key_name)) THEN
EXCEPTION NOT_UNIQUE_NAME;
IF (new.key_parent is not null) THEN
IF (EXISTS(SELECT key_name
FROM REGISTRY
WHERE key_parent=new.key_parent AND key_name = new.key_name))
THEN
EXCEPTION NOT_UNIQUE_NAME;
END
IF ((EXISTS(SELECT key_code
FROM GET_REGISTRY_SUBKEYS(new.key_code,0)
WHERE key_code = new.key_parent)) OR
(new.key_parent = new.key_code)) THEN
EXCEPTION INTEGRITY_ERROR;
END!!