Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Firebird, Interbase > Версионность записей

Автор: Gluttton 28.10.2009, 00:49

Доброго времени суток!

Вдохновившись http://rsdn.ru/article/db/dbhistory.xml, решил попробовать реализовать версионность средствами Firebird.

Реализовал следующую модель:
Две таблицы, первая хранит данные, а вторая хранит предыдущее значение, дату изменения и пользователя, который изменил значение.

Мультики начались при попытке осуществить предложенным в статье методом версионирование при удалении данных.
Автор статьи предлогает ввести дополнительное поле - флаг удаления и при удалении записи запрещать удаление записи, а вместо этого поднимать флаг (например значением IsDeleted).

Проблема в том, что как я не пытался я не смог создать триггер, который одновременно и запрещал удаление и в тоже время вносил какие либо изменения в БД. Т.о. прихожу к выводу, что реализовать в Firebird модель версионирования, при которой бы удаление заменялось на изменение флага (признака) удаления не получится. Вижу несколько вариантов. Например можно все DELET'ы записей заменить на их UPDATE'ы... Но мне это не совсем нравится (почему то). Второй вариант заключается в том, что бы для удаленных записей создать отдельную таблицу, и при удалении переносить запись из таблицы неудаленных данных в таблицу удаленных.

Вопросы: хотелось бы услышать, приходилось ли кому сталкиваться с версионированием и как это было реализовано?

Автор: Deniz 28.10.2009, 06:45

Один из вариантов, создать вьюшку на таблицу (запретить весь доступ к таблице), на нее триггеры, и в триггере можно делать что угодно. В других СУБД такие триггеры называются "instead of" (вроде пока таких в FB нет, поправьте если не так)
Второй вариант, завернуть все операции в хранимые процедуры.

Автор: Akella 28.10.2009, 16:49

Цитата(Gluttton @ 28.10.2009, 00:49

)

Проблема в том, что как я не пытался я не смог создать триггер, который одновременно и запрещал удаление и в тоже время вносил какие либо изменения в БД.

Почему не мог создать такой триггер?

Автор: Gluttton 28.10.2009, 18:28

Цитата(Akella @ 28.10.2009, 16:49

)

Почему не мог создать такой триггер?

Если коротко, то... Что бы отменить удаления я должен сгенерировать исключение, при вызове исключения, если его не обработать, все операции выполненные в триггере откатываются (т.е. нет возможности внести updeate флага признака удаления), а если обработать исключение, то удаление не отменяется (т.е. происходит удаление той(тех) записей для которых вызван delete)...

Пытался разнести операции отмены удаления и обновления флага по триггерам before- и after- , но безрезультатно. На сколько я понял, то причина в том, что триггер after- не видит запись, которая удаляется.

Автор: Akella 28.10.2009, 21:38

Ну да, я так и предполагал.

Автор: Gluttton 28.10.2009, 22:50

Цитата(Deniz @ 28.10.2009, 06:45

)

Второй вариант, завернуть все операции в хранимые процедуры.

Можно конечно же, но... Точно так же можно заменить удаление обновлением... Не совсем то, что хотелось бы smile

, как то натянуто (хотя если по другому не получится, то очень даже вариант).

Цитата(Akella @ 28.10.2009, 21:38

)

Ну да, я так и предполагал.

Как

Цитата(Gluttton @ 28.10.2009, 18:28

)

Пытался разнести операции отмены удаления и обновления флага по триггерам before- и after- , но безрезультатно.

Что не получится smile

?

Вот как я делал...

Пусть есть таблица с исходными данными - SC:

Код


CREATE TABLE SC
(
    ID           BIGINT NOT NULL,
    FIELD        VARCHAR(50) NOT NULL,
    STATE        VARCHAR(20) NOT NULL
);

STATE - состояние ('NoD' - не удалено, 'IsD' - удалено).
Пусть нам необходимо отслеживать изменения поля FIELD, для этого есть таблица истории - HS:

Код


CREATE TABLE HS
(
    ID           BIGINT NOT NULL,
    FIELD        BIGINT NOT NULL,
    RECORD       VARCHAR(50) NOT NULL,
    ACT          VARCHAR(20) NOT NULL,
    D            DATE,
    T            TIME,
    OWNER        VARCHAR(50) NOT NULL
);

OWNER - имя пользователя, который внес изменения, D и T дата и время изменения, ACT - действие (удаление, обновление), RECORD - старое значение поля SC.FIELD, а FIELD - внешний ключ SC.ID.

Создадим триггер на обновление:

Код


SET TERM ^;

CREATE TRIGGER SC_MOD FOR SC
ACTIVE BEFORE UPDATE POSITION 1
AS
BEGIN
    INSERT INTO HS(FIELD, RECORD, ACT, D, T, OWNER)
    VALUES
    (
        OLD.ID,
        OLD.FIELD,
        'Update',
        CURRENT_DATE,
        CURRENT_TIME,
        CURRENT_USER
    );
END^

SET TERM ;^

Работает как часики smile

...

А теперь пробую создать триггеры на удаление, для этого потребуется создать исключение:

Код


CREATE EXCEPTION SC_DEL 'Deleting was canceled!';

Первый триггер:

Код


SET TERM ^;

CREATE OR ALTER TRIGGER SC_ADEL FOR SC
ACTIVE AFTER DELETE POSITION 1
AS
BEGIN
    EXCEPTION SC_DEL;
END^

SET TERM ;^

Второй триггер:

Код


SET TERM ^;

CREATE OR ALTER TRIGGER SC_BDEL FOR SC
ACTIVE BEFORE DELETE POSITION 0
AS
BEGIN
    INSERT INTO HS(FIELD, RECORD, ACT, D, T, OWNER)
    VALUES
    (
        OLD.ID,
        OLD.FIELD,
        'Delete',
        CURRENT_DATE,
        CURRENT_TIME,
        CURRENT_USER
    );
    UPDATE SC SET STATE='IsD'
    WHERE ID=OLD.ID;
END^

SET TERM ;^

При попытке удалить запись из исходной таблицы отмена удаления срабатывает, а обновления и логгирования не происходит...

На всякий случай скрипт тестовой БД.

Автор: Deniz 29.10.2009, 07:16

Gluttton, вроде уже говорил, но повторюсь.

Цитата(Gluttton @ 29.10.2009, 01:50

)

D и T дата и время изменения

используй одно поле в этом случае

Код

dt timestamp default current_timestamp

Добавлено через 4 минуты и 30 секунд
Gluttton, сделал у себя тест с вьюхой см. вложение. для сервера версии 1.5
Запрос во вьюхе необходимо сделать необновляемым, иначе триггер на удаление удаляет запись в таблице sc

Автор: Akella 29.10.2009, 11:43

Цитата(Gluttton @ 28.10.2009, 22:50

)

Ну да, я так и предполагал.

Как smile?

так как удаление выполняется в рамках одной транзакции, то при вызове исключения транзакция будет откачена, что повлечёт за собой и откат действий по записи данных в другую таблицу

Добавлено через 1 минуту и 31 секунду

Цитата(Gluttton @ 28.10.2009, 22:50

)

При попытке удалить запись из исходной таблицы отмена удаления срабатывает, а обновления и логгирования не происходит...

и я о том же

Автор: Gluttton 29.10.2009, 21:58

Deniz,
Спасибо за скрипт!

Akella,
Т.е. это безисходность?
Триггер на уделение, выполняющий любое действие вместе с отменой удаления, никак не создать?

Тогда думаю будет симпатичнее создать таблицу для хранения удаленных записей, т.е. триггеру прийдется не отменяя удаление копировать удаленную запись в другую таблицу (в которой хранятся удаленные записи)...

Автор: Akella 15.11.2009, 01:30

Цитата(Gluttton @ 29.10.2009, 21:58

)

Т.е. это безисходность?

Ну в версии 2,5 ввели автономные транзакции.

Код


create table log (
  logdate timestamp,
  msg varchar(60)
);

create exception e_conn 'Connection rejected';

set term !;

create trigger t_conn on connect
as
begin
  if (current_user = 'BAD_USER') then
  begin







    in autonomous transaction
    do
    begin
      insert into log (logdate, msg) values (current_timestamp, 'Connection rejected');
    end






    exception e_conn;
  end
end!

set term ;!

Автор: Gluttton 16.11.2009, 00:59

Akella, спасибо за ценный совет smile

!
Используя автономные транзакции сумел одним триггером и внести записть в таблицу истории (логов) и отменить само удаление!

Но! Изначально задумывалось так, что при удалении запись в исходной таблице не удаляется, а помечается как удаленная.

Цитата(Gluttton @ 28.10.2009, 22:50

)

Пусть есть таблица с исходными данными - SC:

Код


CREATE TABLE SC
(
    ID           BIGINT NOT NULL,
    FIELD        VARCHAR(50) NOT NULL,
    STATE        VARCHAR(20) NOT NULL
);

STATE - состояние ('NoD' - не удалено, 'IsD' - удалено).

Так вот изменить (выполнить UPDATE) с той записью, которая удаляется никак не получается, т.к. до вызова исключения она удалена, а после вызова исключения уже ничего не выполняется...
Вот это уже безисходность smile

Автор: Akella 16.11.2009, 12:28

1. В автономной транзакции помечай запись, как удалённую.
2. Вызывай исключение.

В итоге, запись будет помечена, как удалённая. И удалена не будет.

Автор: Gluttton 17.11.2009, 00:27

Akella, smile

, я так думаю, бубен на аватар нужно было тебе поставить, а не мне smile

!

Ну честное слово вчера пробовал, не получалось! Сегодня, хотел повторить, что бы всё грамотно описать, что не получается, а тут бац - и всё работает!

Итак по сути...
Тема закрыта smile

, спасибо всем!

Для тех, кого в эту ветку приведет поиск, выкладываю конечный вариант скрипта для Firebird 2.5 (не эталон, но для примера, думаю, сгодится smile