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

Материал из DRKB

Генераторы и их использование[править | править код]

Источники: печатная документация и справочная информация по Borland InterBase, переписка листсервера esunix1.
последние изменения: 2 июля 1999.

Большинство SQL-серверов имеет специальные механизмы для создания уникальных идентификаторов. В Borland Interbase для этого существует механизм генераторов.

В данной статье будут рассмотрены следующие темы:

  • Создание генераторов
  • Использование генераторов в триггерах и хранимых процедурах
  • Изменение значения генератора
  • Получение текущего значения генератора
  • Удаление генераторов
  • Нестандартное применение генераторов


Создание генераторов[править | править код]

Генератор - это специальный объект базы данных, который генерирует уникальные последовательные числа. Эти числа могут быть использованы в качестве идентификаторов (например код клиента, номер счета и т.п.). Для создания генератора необходимо использовать оператор DDL

CREATE GENERATOR generatorname;


При выполнении такой команды происходит 2 действия:

  1. На специальной странице БД отводится 4 байта для хранения значени генератора
  2. В системной таблице 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