Генераторы и их использование

== Генераторы и их использование ==
 Источники: печатная документация и справочная информация по Borland InterBase, переписка листсервера esunix1.
 последние изменения: 2 июля 1999.
Большинство SQL-серверов имеет специальные механизмы для создания уникальных идентификаторов. В Borland Interbase для этого существует механизм генераторов.
В данной статье будут рассмотрены следующие темы:
* Создание генераторов
* Использование генераторов в триггерах и хранимых процедурах
* Изменение значения генератора
* Получение текущего значения генератора
* Удаление генераторов
* Нестандартное применение генераторов
=== Создание генераторов ===
Генератор - это специальный объект базы данных, который генерирует уникальные последовательные числа. Эти числа могут быть использованы в качестве идентификаторов (например код клиента, номер счета и т.п.). Для создания генератора необходимо использовать оператор DDL
CREATE GENERATOR generatorname;
При выполнении такой команды происходит 2 действия:
# На специальной странице БД отводится 4 байта для хранения значени генератора
# В системной таблице RDB$GENERATORS заводится запись, куда помещаетс имя генератора иего номер (фактически смещение на странице генераторов).
После создания генератора его значения можно получать при помощи функции
 GEN_ID(generatorname, inc_value)
где inc_value - число, на которое необходимо прирастить значение генератора.
Генераторы возвращают значения (и сохраняют свои значения на диске) вне контекста транзакции пользователя. Это означает, что если генератора было увеличено с 10 до 11 (инкремент 1), то даже при откате транзакции (ROLLBACK) значение генератора не вернется к предыдущему. Вместе с этим гарантируется что каждому пользователю будет возвращено уникальное значение генератора.
При выборке значения генератора запросом вида select gen_id(genname, x) from ... следует учитывать буферизацию выборки на клиенте. Т.е. в многопользовательской среде при выполнении двух таких запросов значения генератора будут увеличиваться "пачками", а не на величину x для каждой выбираемой записи.
=== Использование генераторов в триггерах и хранимых процедурах ===
Пример триггера, автоматически присваивающего уникальное значение ключевому полю таблицы:
создадим генератор для уникальной идентификации клиентов:
CREATE GENERATOR NEWCLIENT;
создадим триггер для таблицы CLIENTS :
CREATE TRIGGER TBI_CLIENTS FOR CLIENTS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  NEW.CLIENT_ID = GEN_ID(NEWCLIENT, 1);
END
В результате при создании новой записи полю CLIENT_ID будет автоматически присваиваться новое значение.
Однако при использовании генератора в триггере возникает проблема на клиентской стороне (например в BDE, используемом в Delphi, C++Builder ...), когда клиентское приложение пытается перечитать только-что вставленную запись. Поскольку триггер меняет значение первичного ключа вставляемой записи, BDE "теряет" такую запись и чаще всего выдает сообщение "Record/Key deleted". Поскольку SQL-сервер не может сообщить клиентскому приложению о новом значении ключевого поля, необходимо сначала запросить уникальное значение с сервера, и только затем использовать его во вставляемой записи. Сделать это можно при помощи хранимой процедуры
CREATE PROCEDURE GETNEWCLIENT
RETURNS (NID INTEGER)
AS
BEGIN
  NID = GEN_ID(NEWCLIENT, 1);
END
В Delphi, вы можете поместить компонент TStoredProc на форму, подсоединить его к данной процедуре, и например в методе таблицы BeforePost написать следующее
begin
  if DataSource.State = dsInsert then
  begin
    StoredProc1.ExecProc;
    ClientTable.FieldByName('CLIENT_ID').asInteger := StoredProc1.Params[0].asInteger;
  end;
end;
После этого вышеприведенный триггер TBI_CLIENTS можно либо удалить, либо переписать так, чтобы генератор использовался только когда поле первичного ключа случайно приобрело значение NULL (например когда к таблице CLIENTS доступ осуществляется не через ваше приложение):
ALTER TRIGGER TBI_CLIENTS
AS
BEGIN
  IF (NEW.CLIENT_ID IS NULL) THEN
    NEW.CLIENT_ID = GEN_ID(NEWCLIENT, 1);
END
Однако использование хранимой процедуры не всегда удобно - BDE может решить, что процедура вероятно изменяет какие-то данные на сервере, и в режиме autocommit завершит текущую транзакцию, что вызовет перечитывание данных TTable и TQuery. Более простым способом является получение значения генератора при помощи запроса:
SELECT GEN_ID(NEWCLIENT, 1) FROM RDB$DATABASE
При этом, если запрос помещен например в Query2, текст в BeforePost будет следующим:
begin
  if DataSource.State = dsInsert then
  begin
    Query2.Open;
    ClientTable.FieldByName('CLIENT_ID').AsInteger := Query2.Fields[0].AsInteger;
    Query2.Close;
  end;
end;
 
Такой способ более предпочтителен, чем использование хранимой процедуры для получения значения генератора, особенно при большом количестве генераторов.
=== Изменение значения генератора ===
Значение генератора можно переустановить при помощи оператора DDL
SET GENERATOR generatorname TO value;
Однако вы не сможете использовать такое выражение в теле триггера или хранимой процедуры, т.к. там можно использовать только операторы DML (а не DDL).
Если вы хотите обнулить генератор, или присвоить ему определенное значение в теле хранимой процедуры, то вы можете это сделать используя функцию GEN_ID:
(В данном примере генератор NEWCLIENT увеличивается на свое-же значение с отрицательным знаком.)
TEMPVAR = GEN_ID(NEWCLIENT, -GEN_ID(NEWCLIENT, 0);
Будьте внимательны при выполнении таких операций в многопользовательских средах. Приложения, процедуры и триггеры, которые в данный момент используют этот генератор, могут предполагать что он не будет "обнулен". Обязательно проверяйте "обнуление" генератора на возникновение конфликтных ситуаций при работе 2-х и более пользователей.
=== Получение текущего значения генераторов ===
Текущее значение генератора можно получить, вызвав функцию GEN_ID с нулевым увеличением значения генератора. Это можно сделать не только в триггере или хранимой процедуре, но и оператором SELECT
SELECT GEN_ID(NEWCLIENT, 0) FROM RDB$DATABASE
Результатом выполнения запроса будет одна запись с одним полем, содержащим текущее значение генератора. Таблица RDB$DATABASES выбрана как содержаща в большинстве случаев одну запись, хотя использовать можно и любую другую таблицу.
При работе в многопользовательских средах будьте внимательны - в то время как вы получили "текущее" значение генератора, другое приложение может его изменить, и таким образом "текущее" значение окажется устаревшим. Тем более не рекомендуется использовать "текущее" значение генератора для его последующего изменения.
=== Удаление генераторов ===
В языке DDL Borland Interbase нет оператора для удаления генератора. Неизвестно, чем это вызвано, но серьезной проблемы не представляет. В самом начале статьи было упомянуто, что запись о генераторе создается в таблице RDB$GENERATORS. Эту запись, безусловно, можно удалить. Однако место, распределенное на странице генераторов, освобождено не будет. Оно будет освобождено только после того, как вы сделаете вашей БД BACKUP/RESTORE.
=== Нестандартное применение генераторов ===
Вы уже видели, что функцию GEN_ID можно использовать в операторе SELECT.
Вот как можно получить количество записей, выбранных запросом:
SET GENERATOR MYGEN TO 0;
SELECT GEN_ID(MYGEN, 1), FIELD1, FIELD2, FIELD3, ... FROM MYTABLE.
Такой запрос вернет в качестве первого поля порядковый номер записи, и после выполнения запроса генератор MYGEN будет содержать количество возвращенных записей. Кроме этого, во время выполнения этого запроса любой другой пользователь этой-же БД может получить текущее значение генератора MYGEN и узнать сколько записей уже выбрано запросом на текущий момент (нечто вроде ProgressBar, однако число записей все-равно неизвестно до окончания выполнения запроса).
Функцию GEN_ID можно также использовать и как "выключатель" длительных запросов. Пример приведен для БД EMPLOYEE.GDB.
SET GENERATOR EMP_NO_GEN TO 0;
SELECT * FROM EMPLOYEE, EMPLOYEE, EMPLOYEE
WHERE GEN_ID(EMP_NO_GEN, 0) = 0;
Фактически такой запрос означает - "выбирать записи пока значение генератора = 0". Как только другой пользователь или ваше приложение в другом коннекте выполнит операцию
SET GENERATOR EMP_NO_GEN TO 1;
запрос прекратится, т.к. условие WHERE станет равным FALSE.
(то-же самое, и даже в более сложных вариантах, можно делать при помощи UDF в Borland InterBase 4.2. см "Особенности версии 4.2")
; Примечание: обязательно учититывайте буферизацию записей клиентской частью (gds32.dll) или сервером при выполнении подобных запросов. Например, приведенный выше запрос с проверкой генератора в where "выключится" не сразу, а через некоторое время.
Безусловно, в многопользовательской среде невозможно использовать в таких целях один и тот-же генератор. Для решения этой проблемы можно завести глобальный генератор, который будет выдавать уникальные идентификаторы пользователям при коннекте, а клиентское приложение будет запоминать его номер и хранить на локальном компьютере для последующего использования. Логика работы может быть следующая:
* Клиентское приложение при запуске определяет, есть-ли для него (например в Registry или INI-файле) "именной" генератор.
* Если нет, то оно операцией SELECT GEN_ID(GlobalGen, 1) FROM RDB$DATABASE получает идентификатор (например 150), создает на сервере собственный генератор операцией CREATE GENERATOR USER_N; (например USER150). После чего сохраняет имя этого генератора на локальном диске.
* Если да, то приложение обнуляет "именной" генератор операцией SET GENERATOR ... TO 0; (в нашем примере SET GENERATOR USER150 TO 0;), и выдает запросы с использованием данного генератора.
 
 
При помощи генераторов можно также решить проблему с отсутствием временных таблиц в Borland Interbase. Вы создаете таблицу, например TEMP_TBL, и в качестве первого поля, входящего в первичный ключ, указываете поле типа INTEGER. Пользователь при соединении с сервером получает собственный идентификатор у некоторого общего генератора, и затем использует его при помещении записей в такую "временную" таблицу. В результате, даже если несколько пользователей будут работать с такой таблицей, они никогда не "пересекутся" по значению первого поля первичного ключа "временной" таблицы..
Если вам недостаточно одного генератора в первичном ключе, и первичный ключ должен состоять из двух и более полей, значения которых устанавливаются генераторами, то решить такую задачу только при помощи генераторов невозможно. Для этого необходимо скомбинировать генераторы, триггеры и UDF
пояснения:
* DDL - Data Definition Language, язык определения данных
* DML - Data Manipulation Language, язык обработки данных
 Copyright © 1996 Epsylon Technologies (095)-913-5608; (095)-913-2934; (095)-535-5349
Author: Дмитрий Кузьменко (http://www.ibase.ru)
Source: Взято из FAQ Epsylon Technologies
ID: 02929
Category:Базы данных
Category:Interbase