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.

примечание: обязательно учтитывайте буферизацию записей клиентской частью (gds32.dll) или сервером при выполнении подобных запросов. Например, приведенный выше запрос с проверкой генератора в where "выключится" не сразу, а через некоторое время.

Безусловно, в многопользовательской среде невозможно использовать в таких целях один и тот-же генератор. Для решения этой проблемы можно завести глобальный генератор, который будет выдавать уникальные идентификаторы пользователям при коннекте, а клиентское приложение будет запоминать его номер и хранить на локальном компьютере для последующего использования. Логика работы может быть следующая: