EnОбновление клиентских наборов данных в InterBase
Юрий Плотников, plotn@euro.ru
В своей первой статье по InterBase хочется остановиться на достаточно нетривиальном вопросе. Тем, кто переходит с локальных СУБД типа Paradox, DBISAM и т. д. возможно, как и мне не хватает автоматического и немедленного обновление данных в таблицах (на стороне клиента) при изменениях, производимыми одновременно несколькими пользователями.
Решение, которое приходит сразу кажется простым – реализовать события в триггерах, при получении которых набор данных обновляется. Также возможно запоминание курсора и возвращения его на место. Но… не гибко – если есть Calculated, Lookup поля, не самый маленький объем данных в таблице – не особо быстро выходит открытие при частых изменениях. Другое решение, предложенное мной сложнее, но, на мой взгляд, гораздо эффективнее. Суть в том, что при получении события обновлять набор данных у других клиентов посредством Delete, Edit, Append, но не генерируя SQL-запросов при этом.
Инструментарий: Firebird 1.0 (см. http://www.ibase.ru – там найдете), FIBPlus 4.4.2 (http://www.devrace.ru). Библиотека FIBPlus последнее время мне все больше и больше нравится и даже не по скоростным характеристикам, что приводятся у них на сайте, а по удобству и возможностям. IBX уступает намного, особенно TpFIBDataSet и его 2 транзакции (UpdateTransaction) и возможность восстановления соединения с базой, куча всяких приятных мелочей. С помощью нее такая картина и получается. Итак, приступим по шагам. Повторю “задание”: при обновлении набора данных одним клиентом, эти обновления должны отражаться сразу и у других клиентов.
Возьмем наипростейшую таблицу с одним полем-ключем и одним информативным полем. Условимся, что при изменении строки, ключ мы менять не будем (вообще то я считаю это хорошим тоном). Итак:
CREATE TABLE T1 (
INTF INTEGER NOT NULL, //это будет ключ
STRF CHAR(20), // а это одно текстовое поле
USERID INTEGER, // идентификатор пользователя
PRIMARY KEY (INTF));
INTF INTEGER NOT NULL, //это будет ключ
STRF CHAR(20), // а это одно текстовое поле
USERID INTEGER, // идентификатор пользователя
PRIMARY KEY (INTF));
Остановимся подробно на идентификаторе пользователя – целое число, он нам в дальнейшем будет говорить, кто из пользователей изменил или добавил запись (но не удалил!). Обычно я его прописываю каждому пользователю в INI-файл или в отдельную таблицу. Заполняется оно на событии BeforePost или OnNewRecord. В тестовом проекте сделайте TEdit с цифрой, чтобы запустить программу 2 раза, а в TEdit’ах установить 1 и 2 соответственно. Также сделаем автоматически изменяемую таблицу для регистрации действий, вот она:
CREATE TABLE AU_T1 (
INTF INTEGER NOT NULL, // все поля идентичны, но нет индексов, даже первичного
STRF CHAR(20),
USERID INTEGER,
AU_NUM INTEGER, // “возраст” изменения. Станет ясно ниже.
AU_ACTION CHAR(1)); // Действие: “I”, “U”, “D” = Insert, Update, Delete
INTF INTEGER NOT NULL, // все поля идентичны, но нет индексов, даже первичного
STRF CHAR(20),
USERID INTEGER,
AU_NUM INTEGER, // “возраст” изменения. Станет ясно ниже.
AU_ACTION CHAR(1)); // Действие: “I”, “U”, “D” = Insert, Update, Delete
Теперь начнем длинный разговор о триггерах. Основная идея – таблица AU_T1 обновляется (растет) при работе с таблицей T1. Ключевым тут является событие t1, которое обрабатывают все клиенты. Также используется генератор: CREATE GENERATOR GEN_T1.
SET TERM ^ ;
/* Triggers definition */
/* Trigger: "trig_T1_ad" */
CREATE TRIGGER "trig_T1_ad" FOR T1
ACTIVE AFTER DELETE POSITION 0
as
begin
post_event 't1';
end
^
/* Trigger: "trig_t1_ai" */
CREATE TRIGGER "trig_t1_ai" FOR T1
ACTIVE AFTER INSERT POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
new.intf,new.strf,new.userid,GEN_ID(gen_t1, 1),'I');
post_event 't1';
end
^
/* Trigger: "trig_t1_au" */
CREATE TRIGGER "trig_t1_au" FOR T1
ACTIVE AFTER UPDATE POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
(new.intf,new.strf,new.userid,GEN_ID(gen_t1, 1),'U');
post_event 't1';
end
^
/* Trigger: "trig_t1_bd" */
CREATE TRIGGER "trig_t1_bd" FOR T1
ACTIVE BEFORE DELETE POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
(old.intf,old.strf,old.userid,GEN_ID(gen_t1, 1),'D');
end
^
SET TERM ; ^
/* Triggers definition */
/* Trigger: "trig_T1_ad" */
CREATE TRIGGER "trig_T1_ad" FOR T1
ACTIVE AFTER DELETE POSITION 0
as
begin
post_event 't1';
end
^
/* Trigger: "trig_t1_ai" */
CREATE TRIGGER "trig_t1_ai" FOR T1
ACTIVE AFTER INSERT POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
new.intf,new.strf,new.userid,GEN_ID(gen_t1, 1),'I');
post_event 't1';
end
^
/* Trigger: "trig_t1_au" */
CREATE TRIGGER "trig_t1_au" FOR T1
ACTIVE AFTER UPDATE POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
(new.intf,new.strf,new.userid,GEN_ID(gen_t1, 1),'U');
post_event 't1';
end
^
/* Trigger: "trig_t1_bd" */
CREATE TRIGGER "trig_t1_bd" FOR T1
ACTIVE BEFORE DELETE POSITION 0
as
begin
insert into au_t1 (intf,strf,userid,au_num,au_action) values
(old.intf,old.strf,old.userid,GEN_ID(gen_t1, 1),'D');
end
^
SET TERM ; ^
Также есть триггер и у таблицы AU_T1:
CREATE TRIGGER "trig_AU_T1_ai" FOR AU_T1
ACTIVE AFTER INSERT POSITION 0
as
declare variable max_num integer;
begin
select max(au_num) from au_t1 into :max_num;
delete from au_t1 where au_num < :max_num - 50;
end
ACTIVE AFTER INSERT POSITION 0
as
declare variable max_num integer;
begin
select max(au_num) from au_t1 into :max_num;
delete from au_t1 where au_num < :max_num - 50;
end
Обратите внимание на число 50 – это максимальное число записей в этой таблице. Данный триггер не дает ей разрастаться. 50 записей мне хватает за глаза. Суть в том, что если клиент редактирует набор данных, он обновлений не увидит, он увидит их сразу после того, как сделает Post. В принципе для этого хватит и 5 записей запаса, но вдруг, перейдя в режим редактирования, клиент пойдет попить чай. А хотя все это не смертельно: кнопку “освежить набор данных” все-таки лучше предусмотреть. Все, переходим к разработке клиентской части.
Создаем проект, ставим на форму: базу данных (fibDB), 2 транзакции (fibtrT1, fibtrT1W), 2 датасета – TpFIBDataSet (fibdsT1, fibdsAU_T1), DataSource, DBGrid, pFIBEventer (не забудьте скомпилировать пакет библиотеки FIBPlus с этим компонентом – по умолчанию он отключен – в *.inc файле). Связываем все так: fibdsT1 – DataSource – DBGrid.
Транзакции. У обоих наборов данных свойство Transaction:=fibtrT1, у fibdsT1 свойство UpdateTransaction:= fibtrT1W. Вообще-то лучше было бы посадить fibdsAU_T1 на собственную транзакцию, но, … и так работает. Транзакции я сделал read-committed. У fibtrT1W установим TimeoutAction:=TACommit. Итак, все изменения в таблице будут проходить в короткой транзакции, она будет автоматически стартовать и завершаться (Commit). Свойства для этого: fibdsT1.AutoCommit:=true; fibdsT1.Options.poStartTransaction:=true; poAllowChangeSQL:=true;
Теперь сгенерируем SQL’ы для таблицы fibdsT1, а впрочем, они стандартные:
Delete:
DELETE FROM T1
WHERE
INTF = ?OLD_INTF
WHERE
INTF = ?OLD_INTF
Insert:
INSERT INTO T1 (INTF, STRF, USERID)
VALUES (?INTF, ?STRF, ?USERID)
VALUES (?INTF, ?STRF, ?USERID)
Refresh:
SELECT T1.INTF, T1.STRF, T1.USERID
FROM T1
WHERE (T1.INTF = ?OLD_INTF)
FROM T1
WHERE (T1.INTF = ?OLD_INTF)
Select:
SELECT T1.INTF, T1.STRF, T1.USERID
FROM T1
FROM T1
Update:
UPDATE T1 SET
STRF = ?STRF,
USERID = ?USERID
WHERE
INTF = ?OLD_INTF
STRF = ?STRF,
USERID = ?USERID
WHERE
INTF = ?OLD_INTF
Для fibdsAU_T1 ничего больше делать не будем. Что еще? Ах да, установим у pFIBEventer'а в Events одну строку “t1” – мы будем получать это событие.
Глобальные переменные:
var
t1_max_num: integer;
t1_event_locked: boolean;
t1_non_upd_rec: integer;
t1_max_num: integer;
t1_event_locked: boolean;
t1_non_upd_rec: integer;
События:
События на кнопку “Открыть таблицу” (по умолчанию она закрыта):
fibdsAU_T1.Active := false;
fibdsT1.Active := false;
// Выбираем максимальный возраст записей.
// Вся суть возраста – это то что каждая новая запись помечается новым числом,
// большим на 1 предыдущего, а на каждом клиенте хранится номер последнего
// обновленного. Таким образом мы не выбираем дважды одни и те же записи
fibdsAU_T1.SelectSQL.Text := 'SELECT MAX(AU_NUM) FROM AU_T1';
fibdsAU_T1.Open;
t1_max_num := 0;
if not fibdsAU_T1.IsEmpty then t1_max_num :=
ibdsAU_T1.Fields[0].AsInteger;
fibdsT1.Open;
fibdsT1.Active := false;
// Выбираем максимальный возраст записей.
// Вся суть возраста – это то что каждая новая запись помечается новым числом,
// большим на 1 предыдущего, а на каждом клиенте хранится номер последнего
// обновленного. Таким образом мы не выбираем дважды одни и те же записи
fibdsAU_T1.SelectSQL.Text := 'SELECT MAX(AU_NUM) FROM AU_T1';
fibdsAU_T1.Open;
t1_max_num := 0;
if not fibdsAU_T1.IsEmpty then t1_max_num :=
ibdsAU_T1.Fields[0].AsInteger;
fibdsT1.Open;
procedure TForm1.fibdsT1BeforePost(DataSet: TDataSet);
begin
// Если мы не вносим “чужие” записи, то устанавливаем владельца.
if Not t1_event_locked then
fibdsT1.FN('userid').asinteger:=strtoint(Edit1.text);
end;
procedure TForm1.pFIBEventer1EventAlert(Sender: TObject;
EventName: string; EventCount: Integer; var CancelAlerts: Boolean);
var updrec: integer;
sS, sU, sD: string;
iKey: integer;
begin
// t1_event_locked введен мною для того, чтобы предотвратить
// повторный вызов этого события, если находимся в нем.
// Может и излишняя предосторожность, но пусть,
// к тому же пригодилось в событии выше
if t1_event_locked then exit;
if EventName = 't1' then
begin
t1_event_locked := true;
fibdsAU_T1.Active := false;
// выбираем записи введенные или измененные не этим пользователем,
// это не относится к удаленным записям – они выбираются все.
fibdsAU_T1.SelectSQL.Text := 'SELECT * FROM AU_T1 where au_num>' + IntToStr(t1_max_num) +
' AND (USERID<>' + Edit1.Text+' or AU_ACTION=''D'') order by au_num';
fibdsAU_T1.Open;
fibdsAU_T1.Last; // Это для определения реального числа записей в RecordCount
fibdsAU_T1.First;
updrec := fibdsAU_T1.RecordCount;
//если не все записи будут обновлены
t1_non_upd_rec := updrec;
// Если редактируем, то уходим – сделаем это в другой раз,
// можно здесь пользователя предупредить об обновлениях
if fibdsT1.State <> dsBrowse then
begin
t1_event_locked := False;
fibdsT1.EnableControls;
exit;
end;
try
// Запоминаем значение ключа, чтобы потом поставить
// пользователя на нужную запись
iKey := fibdsT1.FN('INTF').AsInteger;
fibdsT1.DisableControls;
// Гуляя по исходникам библиотеки FIBPlus, я обнаружил, что если
// Поставить “No Action”, то SQL-операторы на сервер не передаются.
// Основная проблема была в том, что если оставить их пустыми, то
// методы Append, Edit будут недоступны. Однако при Delete этот фокус
// не пройдет, см. ниже.
sS := fibdsT1.InsertSQL.Text; fibdsT1.InsertSQL.Text:='No Action';
sU := fibdsT1.UpdateSQL.Text; fibdsT1.UpdateSQL.Text:='No Action';
sD := fibdsT1.DeleteSQL.Text; fibdsT1.DeleteSQL.Text:='No Action';
while not fibdsAU_T1.Eof do
begin
//Добавляем
if fibdsAU_T1.FN('AU_ACTION').AsString = 'I' then
begin
fibdsT1.Append; fibdsT1.FN('INTF').AsInteger := fibdsAU_T1.FN('INTF').AsInteger;
fibdsT1.FN('STRF').AsString := fibdsAU_T1.FN('STRF').AsString;
fibdsT1.FN('USERID').AsInteger := fibdsAU_T1.FN('USERID').AsInteger;
try
fibdsT1.Post;
dec(t1_non_upd_rec);
except
fibdsT1.Cancel;
end;
end;
// При удалении мы не можем четко узнать число записей,
// нужных для обновления – к ним примешиваются записи, удаленные и
// этим клиентом, но они, в таком случае, не будут найдены по Locate
if fibdsAU_T1.FN('AU_ACTION').AsString = 'D' then
begin
dec(t1_non_upd_rec);
dec(updrec);
end;
if fibdsT1.Locate('INTF', fibdsAU_T1.FN('INTF').AsInteger, []) then
begin
//удаляем, запись была не наша.
try
if fibdsAU_T1.FN('AU_ACTION').AsString = 'D' then
begin
fibdsT1.Delete;
inc(updrec);
end;
except end;
//Обновляем
if fibdsAU_T1.FN('AU_ACTION').AsString = 'U' then
begin
fibdsT1.Edit;
fibdsT1.FN('STRF').AsString := fibdsAU_T1.FN('STRF').AsString;
fibdsT1.FN('USERID').AsInteger := fibdsAU_T1.FN('USERID').AsInteger;
try
fibdsT1.Post;
dec(t1_non_upd_rec);
except
fibdsT1.Cancel;
end;
end;
end;
// Обновляем “возраст”, обработанные записи выбраны больше не будут
t1_max_num:=fibdsAU_T1.FN('AU_NUM').AsInteger;
fibdsAU_T1.Next;
end;
finally
// восстанавливаем все обратно
fibdsT1.InsertSQL.Text := sS;
fibdsT1.UpdateSQL.Text := sU;
fibdsT1.DeleteSQL.Text := sD;
if fibdsT1.State=dsBrowse then
fibdsT1.Locate('INTF',iKey,[]);
fibdsT1.EnableControls;
Label2.Caption:='';
if t1_non_upd_rec<>0 then
Label2.Caption:= 'Невозможно обновить '+ IntToStr(t1_non_upd_rec)+' записей'
else
Label2.Caption:='Другими пользователями обновлено '+ IntToStr(updrec)+' записей';
t1_event_locked := False;
end;
end;
end;
EventName: string; EventCount: Integer; var CancelAlerts: Boolean);
var updrec: integer;
sS, sU, sD: string;
iKey: integer;
begin
// t1_event_locked введен мною для того, чтобы предотвратить
// повторный вызов этого события, если находимся в нем.
// Может и излишняя предосторожность, но пусть,
// к тому же пригодилось в событии выше
if t1_event_locked then exit;
if EventName = 't1' then
begin
t1_event_locked := true;
fibdsAU_T1.Active := false;
// выбираем записи введенные или измененные не этим пользователем,
// это не относится к удаленным записям – они выбираются все.
fibdsAU_T1.SelectSQL.Text := 'SELECT * FROM AU_T1 where au_num>' + IntToStr(t1_max_num) +
' AND (USERID<>' + Edit1.Text+' or AU_ACTION=''D'') order by au_num';
fibdsAU_T1.Open;
fibdsAU_T1.Last; // Это для определения реального числа записей в RecordCount
fibdsAU_T1.First;
updrec := fibdsAU_T1.RecordCount;
//если не все записи будут обновлены
t1_non_upd_rec := updrec;
// Если редактируем, то уходим – сделаем это в другой раз,
// можно здесь пользователя предупредить об обновлениях
if fibdsT1.State <> dsBrowse then
begin
t1_event_locked := False;
fibdsT1.EnableControls;
exit;
end;
try
// Запоминаем значение ключа, чтобы потом поставить
// пользователя на нужную запись
iKey := fibdsT1.FN('INTF').AsInteger;
fibdsT1.DisableControls;
// Гуляя по исходникам библиотеки FIBPlus, я обнаружил, что если
// Поставить “No Action”, то SQL-операторы на сервер не передаются.
// Основная проблема была в том, что если оставить их пустыми, то
// методы Append, Edit будут недоступны. Однако при Delete этот фокус
// не пройдет, см. ниже.
sS := fibdsT1.InsertSQL.Text; fibdsT1.InsertSQL.Text:='No Action';
sU := fibdsT1.UpdateSQL.Text; fibdsT1.UpdateSQL.Text:='No Action';
sD := fibdsT1.DeleteSQL.Text; fibdsT1.DeleteSQL.Text:='No Action';
while not fibdsAU_T1.Eof do
begin
//Добавляем
if fibdsAU_T1.FN('AU_ACTION').AsString = 'I' then
begin
fibdsT1.Append; fibdsT1.FN('INTF').AsInteger := fibdsAU_T1.FN('INTF').AsInteger;
fibdsT1.FN('STRF').AsString := fibdsAU_T1.FN('STRF').AsString;
fibdsT1.FN('USERID').AsInteger := fibdsAU_T1.FN('USERID').AsInteger;
try
fibdsT1.Post;
dec(t1_non_upd_rec);
except
fibdsT1.Cancel;
end;
end;
// При удалении мы не можем четко узнать число записей,
// нужных для обновления – к ним примешиваются записи, удаленные и
// этим клиентом, но они, в таком случае, не будут найдены по Locate
if fibdsAU_T1.FN('AU_ACTION').AsString = 'D' then
begin
dec(t1_non_upd_rec);
dec(updrec);
end;
if fibdsT1.Locate('INTF', fibdsAU_T1.FN('INTF').AsInteger, []) then
begin
//удаляем, запись была не наша.
try
if fibdsAU_T1.FN('AU_ACTION').AsString = 'D' then
begin
fibdsT1.Delete;
inc(updrec);
end;
except end;
//Обновляем
if fibdsAU_T1.FN('AU_ACTION').AsString = 'U' then
begin
fibdsT1.Edit;
fibdsT1.FN('STRF').AsString := fibdsAU_T1.FN('STRF').AsString;
fibdsT1.FN('USERID').AsInteger := fibdsAU_T1.FN('USERID').AsInteger;
try
fibdsT1.Post;
dec(t1_non_upd_rec);
except
fibdsT1.Cancel;
end;
end;
end;
// Обновляем “возраст”, обработанные записи выбраны больше не будут
t1_max_num:=fibdsAU_T1.FN('AU_NUM').AsInteger;
fibdsAU_T1.Next;
end;
finally
// восстанавливаем все обратно
fibdsT1.InsertSQL.Text := sS;
fibdsT1.UpdateSQL.Text := sU;
fibdsT1.DeleteSQL.Text := sD;
if fibdsT1.State=dsBrowse then
fibdsT1.Locate('INTF',iKey,[]);
fibdsT1.EnableControls;
Label2.Caption:='';
if t1_non_upd_rec<>0 then
Label2.Caption:= 'Невозможно обновить '+ IntToStr(t1_non_upd_rec)+' записей'
else
Label2.Caption:='Другими пользователями обновлено '+ IntToStr(updrec)+' записей';
t1_event_locked := False;
end;
end;
end;
Да, не упомянул, важно: насчет DeleteSQL: там конструкция No Action по умолчанию не проходит, придется подпатчить библиотеку FIBPlus, а так не хотелось. Еще раз, версия 4.4.2, модуль pFIBDataSet:
procedure TpFIBDataSet.InternalDeleteRecord(Qry: TFIBQuery; Buff: Pointer);//override;
begin
AutoStartUpdateTransaction;
ExecUpdateObjects(ukDelete,Buff,oeBeforeDefault);
//plotn
//if Qry.SQL.Text<>'' then begin
if (Qry.SQL.Text<>'') and (Qry.SQL[0]<>'No Action') then
begin
//\plotn
SetQueryParams(Qry, Buff);
Qry.ExecQuery;
end;
ExecUpdateObjects(ukDelete,Buff,oeAfterDefault);
with PRecordData(Buff)^ do
begin
rdUpdateStatus := usDeleted;
rdCachedUpdateStatus := cusUnmodified;
end;
WriteRecordCache(PRecordData(Buff)^.rdRecordNumber, Buff);
if not FCachedUpdates then
AutoCommitUpdateTransaction;
FHasUncommitedChanges:=Transaction.State=tsActive;
end;
begin
AutoStartUpdateTransaction;
ExecUpdateObjects(ukDelete,Buff,oeBeforeDefault);
//plotn
//if Qry.SQL.Text<>'' then begin
if (Qry.SQL.Text<>'') and (Qry.SQL[0]<>'No Action') then
begin
//\plotn
SetQueryParams(Qry, Buff);
Qry.ExecQuery;
end;
ExecUpdateObjects(ukDelete,Buff,oeAfterDefault);
with PRecordData(Buff)^ do
begin
rdUpdateStatus := usDeleted;
rdCachedUpdateStatus := cusUnmodified;
end;
WriteRecordCache(PRecordData(Buff)^.rdRecordNumber, Buff);
if not FCachedUpdates then
AutoCommitUpdateTransaction;
FHasUncommitedChanges:=Transaction.State=tsActive;
end;
И кстати, патч этот не самый лучший – легко увидеть работа с UpdateObject’ами будет идти и при No Action (они будут вызываться для “чужих” записей), просто я их пока не использую и в данном примере они были не нужны, можно сделать и красивее, но нет времени на дополнительное тестирование.
И все. Конечно, достаточно сыро, но работает прилично. Кому надо рабочий пример, пишите, вышлю: plotn@euro.ru. Все комментарии, дополнения также обязательно пишите туда. Может кто и сподобится написать соответствующий компонент для этого (сочетающий в себе и датасет, и eventer, и автоматизизирующий создание триггеров, и таблицы AU_…).