Оригинал статьи: relib.com

Импортирование информации с удаленного сайта средствами PHP

Автор: Александр Тотоев

Вашему вниманию предлагается пример организации данных типа Registry в БД. В таблице хранятся разнородные данные, процедуры обеспечивают выбору всей иерархии в нужном порядке и поиск по ключу. Хотя это и не самый быстрый способ (безусловно, обращение к базе данных медленнее чем к Registry Windows 95 или Windows NT), зато есть возможность настойки системы .
Такую структуру можно использовать для хранения настроек пользователя независимо от клиентского компьютера, с которого пользователь работает с базой данных (естественно на каждого - свои настройки) и т.п.



CREATE TABLE REGISTRY (
    Key_code           integer not null,
    Key_parent         integer ,
    Key_name          char(64)     not null,
    Key_type           smallint        not null,
    Key_string         char(255) ,
    Key_integer       integer,
    Key_date          date ,
    Key_float          double precision,
    Key_blob           blob,
    constraint pk_registry primary key(Key_code)
    );
ALTER TABLE REGISTRY ADD CONSTRAINT FK_REGISTRY
FOREIGN KEY (key_parent)  references REGISTRY;

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!!