Вот несколько примеров для работы с суррогатными ключами.
Для начала, нужно создать механизм поддержки уникальности значений суррогатного ключа.CREATEGENERATORGET_IZMER_NAMES_NUM;
Этот оператор создает т.н. генератор, где будет хранится предыдущее значение нашей уникальной последовательности целых чисел. Механизм гарантирует, что только один пользователь может иметь доступ к генератору в один момент времени. Остальные будут ждать, пока генератор не освободится.
SET GENERATOR GET_IZMER_NAMES_NUM TO 50;
Этим оператором мы установили начальное значение генератора. Далее, можно либо создать триггер, который сработает при добавлении новой записи в таблицу, либо создать простенькую процедуру, которая вернет очередное значение из генератора:
SET TERM !! ;
CREATE PROCEDURE SET_IZMER_NAMES_NUM
RETURNS(NUM INTEGER)
AS
BEGINNUM = GEN_ID(GET_IZMER_NAMES_NUM, 1);
END!!
SET TERM ; !!
GEN_ID - это встроенная процедура, которая просто увеличивает значение генератора на величину, переданную во втором параметре и возвращает результат. Если Вы используете триггер, то после добавления новой записи, Вам придется обновлять весь набор данных, чтобы знать значение первичного ключа, поэтому лучше использовать процедуру.
"Деревянные" списки
Бывают случаи, когда отношение главный-подчиненный присуще записям одной и той же таблице, например, отношения между отделами организации или между структурами госаппарата и т.д. и т.п. Одна запись может быть главной для нескольких других, которые в свою очередь могут быть главными для следующих. Такая структура напоминает дерево с ветвями, расположенными вниз по таблице. Первая запись (записи) - главный узел (узлы) от которых идут ветви (подчиненные записи). Если эти записи имеют свои подчиненные (вложенные) записи, то они образуют следующие по иерархическому списку узлы. Проще всего, представить это в пространстве в виде слоев записей. Каждая запись может содержать в себе вложенный слой с записями. Несмотря на всю кажущуюся сложность, реализация такой структуры очень проста. Для этого нужно иметь, как минимум, два столбика в таблице: первый столбик - это суррогатный первичный ключ, а второй - ссылка на первый столбик со значением первичного ключа записи - владельца. Вот реализация такой таблицы:
CREATE TABLE ACTIVITIES
(
ID_NUM ACTIVITIES_NUM,
ID_OWNER ACTIVITIES_NUM,
ID_IZMER_NAMES IZMER_NUM,
POZITION INTEGER_TYPE,
NAME NAMES_TYPE,
USER_NAME BY_USER,
CHANGE_DATE BY_DATE,
PRIMARY KEY(ID_NUM),
FOREIGN KEY(ID_IZMER_NAMES) REFERENCES IZMER_NAMES(ID_NUM));
Таблица содержит первичный ключ в поле ID_NUM, ссылку на главную запись в поле ID_OWNER, ссылку на единицу измерения в поле ID_IZMER, поле POZITION целого типа, определяющее позицию записи, для возможности перемещения записи вверх и низ, наименование вида дечтельности в поле NAME. Далее, идут счетчик и процедура для работы с первичным ключом.
CREATE GENERATOR GET_ACTIVITIES_NUM;
SET GENERATOR GET_ACTIVITIES_NUM TO 50;
SET TERM !! ;
CREATE PROCEDURE SET_ACTIVITIES_NUM
RETURNS(NUM INTEGER)
AS
BEGINNUM = GEN_ID(GET_ACTIVITIES_NUM, 1);
END!!
SET TERM ; !!
Далее, идет индекс для сортировки строк по позиции. Имя POZITION принято мной не потому, что я не знаю о английском слове POSITION, а потому, что POSITION - зарезервированный идентификатор SQL.
CREATE UNIQUE INDEX ACTIVITIES_POSITION ON ACTIVITIES(ID_OWNER, POZITION);
Триггер UPDATE_ACTIVITIES обновляет значения полей, идентифицирующиз пользователя внесшего последние изменения.
SET TERM !! ;
CREATE TRIGGER UPDATE_ACTIVITIES FOR ACTIVITIES
BEFORE UPDATE AS
BEGIN
NEW.USER_NAME = USER;
NEW.CHANGE_DATE = 'now'
END!!
SET TERM ; !!
Наконец, добавлен внешний индекс таблицы на саму себя. В описании таблице это нельзя было сделать,т.к. ни поля ID_OWNER, ни поля ID_NUM, ни самой таблицы не существовало.
ALTER TABLE ACTIVITIES
ADD
FOREIGN KEY (ID_OWNER) REFERENCES ACTIVITIES(ID_NUM) ON DELETE CASCADE;
Далее, идет процедура перемещения строки в слое данных вверх или низ. Подразумевается, что в слое не более 2147483646 строк.
SET TERM !! ;
CREATE PROCEDURE SET_ACTIVITIES_POSITION(OWNER_NUM INTEGER, OLD_POSITION INTEGER, NEW_POSITION INTEGER)
AS
BEGINUPDATE ACTIVITIES
SET
POZITION = 2147483647
WHERE
POZITION = :NEW_POSITION AND
ID_OWNER = :OWNER_NUM;
UPDATE ACTIVITIES
SET
POZITION = :NEW_POSITION
WHERE
POZITION = :OLD_POSITION AND
ID_OWNER = :OWNER_NUM;
UPDATE ACTIVITIES
SET
POZITION = :OLD_POSITION
WHERE
POZITION = 2147483647 AND
ID_OWNER = :OWNER_NUM;
END!!
SET TERM ; !!
Тут не хватает только триггера для начального определения значения поля POZITION. Я думаю, что Вы сможете самостоятельно создать триггер в качестве пробы сил.
Работа с событиями
Это совсем просто:
SET TERM !! ;
CREATE TRIGGER CHANGE_ACTIVITIES FOR ACTIVITIES
AFTER UPDATE POSITION 0 AS
BEGIN
POST_EVENT 'Update Activities !';
END!!
SET TERM ; !!
Осталось только зарегистрировать это событие в приложении пользователя, и если оно произойдет на сервере, то приложение пользователя его получит. Так можно, например, наблюдать за изменениями курсов валют на бирже. При изменении курса, клиент получает событие и пере открывает запрос, чтобы увидеть изменения.
Работа с исключениями
Для начала, исключение нужно определить в БД.
CREATE EXCEPTION DELETE_MAIN_PARENT' DO NOT DELETE THIS RECORD ! THIS RECOCT IS PARENT FOR ALL RECORDS. ';
Далее, нужно определить триггер, который поймает исключительную ситуацию. Например, при удалении главного узда дерева, удалится вся БД целиком. Понятно, что такого быть не должно. Давайте поймаем это исключение.
SET TERM !! ;
CREATE TRIGGER CHECK_DELETE_TYPES FOR ACTIVITIES
BEFORE DELETE POSITION 0 AS
BEGIN
IF (ACTIVITIES.ID_NUM = ACTIVITIES.ID_OWNER) THEN
EXCEPTION DELETE_MAIN_PARENT;
END!!
SET TERM ; !!
Если исключительная ситуация наступит, то пользователю ничего не останется сделать, кроме как отменить транзакцию.
Процедуры, триггеры
Понятия процедур и триггеров должно, прежде всего, ассоциироваться с понятием бизнес-логика. Процедуры реализуют документированный интерфейс к данным в БД, а триггеры - проверку корректности вводимых данных и закулисную работу. Если у Вас есть возможность переложить всю бизнес-логику на сервер в виде триггеров и процедур, то так и нужно поступать. Даже если Вы в программе контролируете правильность вводимых данных, не забудьте в БД продублировать это же в триггере. Такой подход гарантирует, что при написании дополнительного модуля или еще одной программы, оперирующей с данными БД, Вам не удастся нарушить правила работы с данными. Я думаю, что примеров триггеров и процедур было достаточно. Но, начинающие программисты часто отказываются от использования этого мощнейшего механизма БД из за досадных ошибок в синтаксисе запросов. Им кажется, что в приложении пользователя легче сделать то же самое, к тому же и работает оно быстрее... Это заблуждение. Одно дело, когда Вы пишете и тестируете программу локально, и совсем другое, когда к БД подключены пользователи. Никакая программа не сделает изменения в БД так же быстро и корректно, как встроенные механизмы. Вот тогда они будут работать локально, а ваша программа - по сети. Поэтому я дам без комментариев пример процедуры с большим количеством операторов. Из этого примера будет ясно где ставить, а где нет точки с запятыми, двоеточия и т.д. Думаю, что это поможет Вам в Ваших разработках.
SET TERM !! ;
CREATE PROCEDURE CHECK_USER_SECURITY(ID_USER INTEGER, ID_DOC INTEGER, UP_TREE INTEGER)
RETURNS(IS_SHOW CHAR(1), IS_EDIT CHAR(1), IS_APPEND CHAR(1), IS_DELETE CHAR(1))
AS
DECLARE VARIABLE TREE_NUMBER INTEGER;
DECLARE VARIABLE TREE_OWNER INTEGER;
DECLARE VARIABLE USER_NUM INTEGER;
DECLARE VARIABLE DOC_NUM INTEGER;
DECLARE VARIABLE EDITING CHAR(1);
DECLARE VARIABLE APPENDING CHAR(1);
DECLARE VARIABLE DELETING CHAR(1);
BEGINIS_EDIT = 'F';
IS_APPEND = 'F';
IS_DELETE = 'F';
IS_SHOW = 'F';
FOR SELECT ID_NUM, ID_OWNERFROM DATA_LIST
WHERE DATA_LIST.ID_NUM = :ID_DOC
INTO TREE_NUMBER, TREE_OWNER
DO
BEGIN
IF ( TREE_NUMBER = UP_TREE ) THEN EXIT;
FOR SELECT ID_USER, ID_DOC, IS_EDIT, IS_APPEND, IS_DELETE
FROM DOCS_USERS
WHERE DOCS_USERS.ID_USER = :ID_USER
INTO USER_NUM, DOC_NUM, EDITING, APPENDING, DELETING
DO
BEGIN
IF ( TREE_NUMBER = DOC_NUM ) THEN
BEGIN
IS_EDIT = EDITING;
IS_APPEND = APPENDING;
IS_DELETE = DELETING;
IS_SHOW = 'T';
EXIT;END
END
ID_DOC = TREE_OWNER;END
END!!
SET TERM ; !!
Эта процедура используется сервером приложений для проверки прав пользователя в таблице в виде иерархического дерева. Понятно, что определить права пользователя к отдельной записи стандартными путями нельзя, поэтому вся БД работает под управлением сервера приложений и посредством DCOM дает интерфейсы клиентам. Т.к. сервер приложений запущен в адресном пространстве сервера, то такой подход к Security можно считать оправданным.
UDF функции
Обычно, тут дают пример, как посчитать какую-нибудь математическую формулу, и вернуть её результат как столбик ответа на запрос. Я же решил показать пример со строками, т.к. это первое, на чем обычно впервые спотыкаются. Это только пример. В реальной БД такого не делают. Итак, добавим в таблицу ACTIVITIES поле TREE_INFO VARCHAR(255). Будем в нем хранить путь от главного узла. Этот путь проще всего строить в триггере по добавлению записи в таблицу. Но сама строка с путем будет создаваться в DLL. Для начала объявим нащу функцию в DLL:
DECLARE EXTERNAL FUNCTION CREATEPATH(CSTRING(256), INTEGER)
RETURNS CSTRING(256)
ENTRY_POINT "CreatePath"
MODULE_NAME "UDF_INCL";
Мы указали имя в БД, передаваемые переметры, возвращаемое значение, имя в DLL, и имя самой DLL. Эта библиотека должна находится в каталоге UDF. Уменяэто D:\Program Files\Borland\InterBase\UDF. А использовать функцию будем так:
SET TERM !! ;
CREATE TRIGGER INSERT_ACTIVITIES FOR ACTIVITIES
BEFORE INSERT
AS
DECLARE VARIABLE PATH_TREE VARCHAR(256);
BEGIN
SELECT TREE_INFO
FROM ACTIVITIESWHERE (NEW.ID_OWNER = ID_NUM)
INTO PATH_TREE;
NEW.TREE_INFO = CREATEPATH(PATH_TREE, NEW.ID_NUM);
END!!
SET TERM ; !!
В InterBase все UDF передают в параметрах ссылки, поэтому строку передают как указатель. Используются VARCHAR строки, т.к. они явно не дополняются пробелами до максимальной длины. Иначе, Вы бы уже ничего к ней не прибавили. Вот реализация DLL в Delphi:
library UDF_INCL;
//
//
// Copyright 2000 Bannikov N.A. Stikriz Technology
//
//
uses
SysUtils,
Classes;
{$R *.RES}
function CreatePath(MainPath: PChar; var IntVal: LongInt): PChar; cdecl; export;
begin
Result:=PChar(AnsiString(MainPath)+IntToStr(IntVal)+'\');
end;
exports
CreatePath;
begin
end.
Список литературы
Банников Н.А. Создание баз данных в InterBase SQL Server.