Перенесення програм MIDAS з однієї СУБД на іншу

Олександр Капустін

Введення

У даній статті розглядаються проблеми, пов'язані з міграцією програми MIDAS з однієї СУБД на іншу. Розглянемо це на прикладі перенесення програми, описаного в статті Романа Ігнатьєва "MIDAS: практика застосування ". Додаток написано під Interbase 5.6 і використовує компоненти IBX на сервері додатків для доступу до СУБД. Перепишемо його таким чином, щоб додаток зміг працювати під керуванням MSSQL Server 7.0 і MSSQL Server 2000 (за допомогою невеликих переробок скрипта можна домогтися роботи програми під Sybase ASE 12.0). Слід також зауважити, що переробці піддадуться тільки скрипт СУБД і сервер додатків. Клієнтська частина залишається недоторканою, тому що при використанні багатоланкової архітектури вона абсолютно ізольована від деталей реалізації серверної частини.

Деякі зауваження до змісту статті.

Передбачається, що читачеві вже знайомі (хоча б у початковій стадії) синтаксис SQL (в додатку або до Interbase, або до MSSQL, а також загальні принципи роботи з БД з Delphi (у статті використовуються IBX & ADO, але це не єдино можливе рішення).

Процес перенесення відбувається вже після того, як додаток злагоджено і стабільно працює (тому що паралельно розробляється для кількох СУБД - Трохи інший випадок, і його ми розглядати не будемо).

Хотілося б сказати кілька слів про те, навіщо це взагалі потрібно, що ми набуваємо, і що втрачаємо (трохи теорії).

купують в основному зниження вартості програмного продукту, бо якщо у клієнта вже встановлена хоча б одна з підтримуваних вами СУБД, немає необхідності витрачати великі гроші на закупівлю СУБД, нового сервера й настроювання всього цього. Поліпшуються можливості інтеграції з існуючими системами.

Але при цьому ми перестаємо використовувати на 100% можливості будь-якої окремої СУБД. Слід розрізняти два випадки. Перший випадок, коли підтримується сумісність зі старими версіями цієї ж СУБД (наприклад, підтримується лінійка MSSQL 6.5 - MSSQL2000). У цьому випадку ми обмежені можливостями найслабкішої версії, і не можемо використовувати нововведення. Другий випадок, набагато більш важкий і разом з тим цікавий для розгляду, коли планується сумісність між різними СУБД, наприклад між MSSQL і Interbase. Повинен відразу зауважити, що цей випадок зустрічається набагато рідше, але якщо ви при проектуванні програми не будете враховувати цю можливість, то перехід викличе набагато більше складнощів.

Слід зазначити, що при перенесенні дворівневого програми проблем виникне набагато більше, тому що більша частина бізнес-логіки знаходиться на сервері, і якщо синтаксис СУБД сильно відрізняється, можливості перенесення сильно обмежуються. У випадку ж трирівневого програми більшість завдань, пов'язаних з логікою, вирішує сервер додатків (хочеться помітити, що це справедливо тільки для правильно спроектованого додатки).

Модифікація структури БД

На жаль, перенесення структури БД "один-в-один" між різними СУБД практично неможливий. Тут наведено опис деяких проблем, з якими доведеться зіткнутися при перенесення, а також можливі шляхи їх вирішення. Найкраще, якби ці речі були враховано спочатку при проектуванні вихідного програми, тому що в цьому випадку обсяг робіт при переносі програми скорочується.

Додавання записів до таблиці

Для абстрагування методу створення унікальних ідентифікаторів для кожного запису можна винести його в збережену процедуру, яка буде повертати ID нового запису. Це дозволяє легко додавати записи в підлеглу таблицю, не роблячи ніяких додаткових маніпуляцій. У Надалі ви можете покласти на цю процедуру, наприклад, генерацію ідентифікаторів в заданому діапазоні, або забезпечити наскрізну нумерацію. Для зберігання ідентифікаторів простіше всього мати окрему таблицю приблизно наступного виду:        

create table Seeds (   

TableName varchar (30), - ім'я таблиці   

ID int, - ID останньої   вставленою записи в дану таблицю   

LowOffset int - нижня   межа діапазону   

)   

go     

При додаванні користувача таблиць необхідно не забувати вставляти в цю таблицю відповідні записи. Нижче наведений приклад SQL-запиту, що робить це:        

insert   into Seeds (TableName, ID, LowOffset, HiOffset)   

values ( 'MyCoolTable',   0, 0, 1000000)   

go     

Текст процедури в простому випадку буде виглядати так:        

create procedure CLIENT_ID   

@ TableName varchar (30),   

@ ID int output   

as   

update Seeds   

set ID = ID + 1,   

@ ID = ID + LowOffset   

where TableName = @ TableName   

go     

проведення процедури оновлення (update) таблиці накладається блокування зміни, яка не дозволить іншому клієнту виконати цю ж процедуру одночасно з першим. Крім перерахованого вище такий підхід спрощує життя при необхідності реплікації даних між філіями. Тоді в кожній філії настроюється свій діапазон, і первинні ключі гарантовано не будуть перетинатися.

Контроль цілісності даних

При проектуванні бази необхідно врахувати наступні Обмеження:

Каскадне зміна по foreign key з'явилося тільки в MSSQL2000. Так що якщо поставити собі за мету зберегти сумісність з попередніми версіями (а також з Sybase), каскадні зміни необхідно проводити при допомоги збережених процедур (чому не використовувати тригери, сказано нижче).

тригери, відпрацьовують не після перевірки всіх обмежень цілісності, а замість дії, на яку їх викликали, також з'явилися тільки в MSSQL2000. У більш ранніх версіях вони просто не змогли б відпрацювати каскадне зміна при наявності foreign key. Також при написанні тригерів слід врахувати особливості реалізації для кожної СУБД. Так, наприклад, в Interbase тригер відпрацьовує на кожен запис, а в MSSQL - на зміну, вставку або видалення запису. Як варіант, можна відмовитися від підтримки цілісності, заснованої на foreign key, і реалізувати її повністю на тригерах.

Перенесення скрипта

Тут наведені основні труднощі, з якими можна зіткнутися при перенесенні скрипта Interbase на MSSQL (повинен ще раз повторитися, що стаття не претендує на повний і детальний розбір відмінностей між цими СУБД, та такий аналіз і не може бути повністю коректним).

Відповідність вбудованих типів

Основні відмінності, які слід враховувати при перенесенні скрипта:        

IB         

MSSQL         

Коментар             

char         

char         

-в MSSQL - не більше 8000, в   IB - не більше 32767 char             

varchar         

varchar         

-в MSSQL - не більше 8000, в   IB - не більше 32767 char             

blob         

text, image                      

date         

datetime, smalldatetime         

(останній обрізає час   до хвилин)                                                     

money, smallmoney         

- в IB немає аналогів                      

bit         

- в IB немає аналогів     

У MSSQL немає типів, що представляють лише дату або тільки час, що є в IB6.

Домени

У IB для створення доменів використовується наступна конструкція:        

create   domain DCount numeric (15,4) default 1 not null;     

Для MSSQL це буде виглядати наступним чином:        

create   default ONE as 1   

go   

exec   sp_addtype 'DCount', 'numeric (15,4)', 'NOT NULL'   

go   

exec   sp_bindefault 'ONE', 'DCount'   

go     

Таблиці

переносяться без проблем, варто лише звернути увагу на зауваження з контролю цілісності даних (до речі, зворотне перетворення буде ускладнений, якщо ви будете використовувати специфічні для MSSQL типи (особливо для MSSQL2000 )).

Збережені процедури

Перенесення збережених процедур - це найбільш трудомісткий процес, тому що доведеться переписувати всі цілком. Але в правильно спроектованому триланкового додатку роль ХП повинна бути зведена до мінімуму. Основні труднощі виникають при перекладі ХП, повертають результуючий набір. Частина з них (що не містять складної бізнес-логіки) може бути переведена в розряд уявлень (view). Для інших можна або створювати тимчасові таблиці на рівні з'єднання з СУБД, або створювати постійні таблиці та розмежовувати дані в них за ідентифікатором підключення (SPID) (але тоді не забувайте їх чистити:)). Якщо ж ви вирішите обмежитися тільки MSSQL2000, то можете використовувати тип "таблиця" для повернення набору значень з процедури. Розглянемо кілька прикладів перекладу ХП. Процедура звіту про взаєморозрахунках між клієнтами:        

create procedure REP_INOUT (FROM_DATE date, TO_DATE date)   

returns (FROM_ID integer,   FROM_NAME varchar (180), TO_ID integer,   

TO_NAME varchar (180), FULL_SUM   numeric (15,4))   

as   

begin   

for select FROM_ID, TO_ID,   sum (DOC_SUM)   

from DOC_TITLE   

where DOC_DATE> =   : FROM_DATE and DOC_DATE <=: TO_DATE   

group by FROM_ID, TO_ID   

into: FROM_ID,: TO_ID,   : FULL_SUM   

do begin   

FROM_NAME = NULL;   

TO_NAME = NULL;      

select NAME   

from client   

where CLIENT_ID =: FROM_ID   

into: FROM_NAME;      

select NAME   

from client   

where CLIENT_ID =: TO_ID   

into: TO_NAME;      

if (FULL_SUM is NULL) then   

FULL_SUM = 0;   

suspend;   

end   

end   

^     

Перетвориться в процедуру такого вигляду:        

create procedure rep_inout @ from_date smalldatetime, @ to_date   smalldatetime   

as   

select dt.from_id, dt.to_id,   isnull (sum (dt.doc_sum), 0) as full_sum,   

c.name as from_name, c1.name   as to_name   

from doc_title dt,   

client c,   

client c1   

where dt.doc_date> =   @ from_date   

and dt.doc_date <=   @ to_date   

and c.client_id = dt.from_id   

and c1.client_id = dt.to_id      

group by dt.from_id, c.name,   dt.to_id, c1.name   

go     

Наступний приклад. Процедура виводить список документів і повні імена клієнта:        

create procedure LIST_DOC (FROM_DATE date, TO_DATE date)   

returns (DOC_ID integer,   DOC_NUM varchar (40), DOC_DATE date,   

FROM_ID integer, TO_ID   integer, FROM_NAME varchar (224),   

TO_NAME varchar (224), DOC_SUM   numeric (15,4))   

as   

begin   

for select DOC_ID, DOC_NUM,   DOC_DATE, FROM_ID, TO_ID, DOC_SUM   

from DOC_TITLE   

where DOC_DATE> =   : FROM_DATE and DOC_DATE <=: TO_DATE   

into: DOC_ID,: DOC_NUM,   : DOC_DATE,: FROM_ID,: TO_ID,: DOC_SUM   

do begin   

FROM_NAME = NULL;   

TO_NAME = NULL;      

execute procedure   CLIENT_FULL_NAME (: FROM_ID)   

returning_values: FROM_NAME;      

execute procedure   CLIENT_FULL_NAME (: TO_ID)   

returning_values: TO_NAME;      

suspend;   

end   

end   

^     

На прикладі перекладу даної процедури покажемо один з варіантів того, як можна звести до мінімуму кількість блокувань на часто використовуваної таблиці.

Створимо для початку допоміжну таблицю наступного виду:        

create table pDoc_List   

(   

SPID int, - ідентифікатор підключення   

doc_id int,   

doc_num varchar (40),   

doc_date smalldatetime,   

from_id int,   

to_id int,   

doc_sum DSum,   

from_name varchar (224),   

to_name varchar (224)   

)   

go     

У цієї тимчасової таблиці ми будемо зберігати дані, відповідають нашим критеріям пошуку. Для того, щоб можна було відрізнити, якому клієнтові призначені дані, вводиться стовпець SPID, в якому ми будемо зберігати унікальний ідентифікатор підключення до БД (@ @ spid).

Після того, як дані скопійовані в цю таблицю, ми можемо спокійно, нікому не заважаючи, обробляти їх так, як нам заманеться. А клієнтові (точніше, сервера додатків) залишається тільки їх вибрати з даної таблиці.        

ПРИМІТКА   

Треба зазначити, що даний алгоритм   застосовується лише в тих випадках, де некритично, що між перечитуванням   даних клієнтом вони можуть змінитися     

Ось код процедури, що заповнює цю таблицю:        

create proc list_doc @ from_date datetime, @ to_date datetime   

as   

declare @ from_name varchar (224)   

declare @ to_name varchar (224)   

declare @ from_id int   

declare @ to_id int   

declare @ doc_id int   

declare @ doc_num varchar (40)   

declare @ doc_date datetime   

declare @ doc_sum dsum      

delete from pDoc_List   

where SPID = @ @ spid - очищаємо   тимчасову таблицю від попередніх даних      

- вставляємо потрібні записи   

insert into   pDoc_List (SPID,   

doc_id,   

doc_num,   

doc_date,   

from_id,   

to_id,   

doc_sum,   

from_name,   

to_name   

)   

select @ @ spid,   

doc_id,   

doc_num,   

doc_date,   

from_id,   

to_id,   

doc_sum,   

'',   

''   

from doc_title   

where doc_date> =   @ from_date and doc_date <= @ to_date   

  

- створюємо найбільш швидкий курсор для обробки записів   

declare list_docs insensitive cursor for   

select doc_id, from_id,   to_id   

from pDoc_List   

where SPID = @ @ spid   

for read only      

open list_docs   

fetch next from list_docs   

into @ doc_id, @ from_id, @ to_id      

while @ @ fetch_status = 0   

begin   

select @ from_name ='',   @ to_name =''   

exec client_full_name   @ from_id, @ from_name output   

exec client_full_name @ to_id,   @ to_name output      

- заповнюємо поля, яких немає в основній таблиці   

update pDoc_List   

set from_name = @ from_name,   

to_name = @ to_name   

where SPID = @ @ spid   

and doc_id = @ doc_id   

  

fetch next from list_docs   

into @ doc_id, @ from_id,   @ to_id   

end      

close list_docs   

deallocate list_docs   

go     

SQL-запит, що виконується на сервері додатків для передачі даних клієнтові:        

exec   list_doc @ from_date =: from_date, @ to_date =: to_date   

select   * From pDoc_List where SPID = @ @ spid             

ПРИМІТКА   

При написанні ХП слід звернути   особливу увагу на одночасну роботу декількох користувачів. Необхідно   мінімізувати вплив "важких" (звітних) процедур на роботу   клієнтів (один з варіантів було показано вище).       

Тригери

Тут нічого складного немає, необхідно тільки пам'ятати, що тригери в MSSQL запускаються тільки після дії (є ще замість (instead of), але це тільки в MSSQL2000).

Приклад: запобігання видалення клієнта, якщо існують документи з його участю (щоб такий тригер не конфліктував з обмеженням посилальної цілісності, в MSSQL необхідно прибрати foreign key з таблиці doc_title на client)        

create trigger CLIENT_BEFORE_DELETE for CLIENT   

before delete   

as   

begin   

if (exists (select * from   DOC_TITLE   

where FROM_ID =   OLD.CLIENT_ID))   

then   

exception EX_CLIENT_IN_DOC;      

if (exists (select * from   DOC_TITLE   

where TO_ID =   OLD.CLIENT_ID))   

then   

exception EX_CLIENT_IN_DOC;   

end   

^     

Перетвориться в        

create trigger CLIENT_AFTER_DELETE on CLIENT   

for delete   

as   

if (exists (select d.CLIENT_ID   from   

DOC_TITLE dt, deleted d   

where dt.FROM_ID =   d.CLIENT_ID))   

begin   

- щоб повідомлення було видно на клієнті   

raiserror ( 'Існує запис у документі',   16, 1)   

- необхідно ручками відкотити транзакцію   

rollback transaction   

end      

if (exists (select d.CLIENT_ID   from   

DOC_TITLE dt, deleted d   

where dt.TO_ID =   d.CLIENT_ID))   

begin   

raiserror ( 'Існує запис у документі',   16, 1)   

rollback transaction   

end   

go     

Модифікація сервера додатків.

Тут основна частина переробки пов'язана з переходом від IBX (InterBase Express) до ADO (ActiveX Data Object). Основні речі, на які слід звернути увагу:

Реалізація транзакцій на клієнта - в IBX це окремий компонент, в ADO така функціональність надається методами TADOConnection. Ще невелика рекомендація - акуратно підходите до вибору рівня ізоляції транзакцій (чим менше рівень ізоляції, тим швидше буде працювати додаток).

Щоб клієнт працював без переробки з різними джерелами даних, необхідно, щоб типи даних полів збігалися. Наприклад, в IBX для numeric (15, 4) за замовчуванням підставляється TFloatField, а в ADO -- TBCDField. Це єдина відмінність, яку мені зустрілося при перенесенні (але це не означає, що їх взагалі немає). Проблема зважилася ручної установкою даного типу поля в TCurrencyField.

Переклад sql-виразів з синтаксису IB в MSSQL.

Відмінності, пов'язані з відмінностями структури БД. Наприклад, якщо буде реалізовано каскадне видалення за допомогою ХП, то доведеться реалізовувати цю логіку всередині сервера, щоб залишити клієнта недоторканим.

Як приклад наведемо переклад однієї з процедур сервера додатків:        

// Опис того, що робить   дана процедура, читайте у статті Ігнатьєва.      

// Код, що працює з IBX   

function TrdmDoc.ApplyChanges: WideString;   

begin   

lock;   

try   

FLastUpdateErrors: ='';   

if FState = osInactive then   

raise Exception.Create ( 'Документ не був створений або відкритий');   

with cdsTitle do   

begin   

Edit;   

  FieldByName ( 'DOC_SUM'). AsCurrency: = CalcSum;   

Post;   

end;   

ibtDoc.StartTransaction;   //ibtDoc - компонент транзакції   

if FState = osInsert then   

begin   

if cdsTitle.ChangeCount>   0 then   

cdsTitle.ApplyUpdates (-1);   

if cdsBody.ChangeCount> 0   then   

cdsBody.ApplyUpdates (-1);   

end;   

if FState = osUpdate then   

begin   

if cdsBody.ChangeCount> 0   then   

cdsBody.ApplyUpdates (-1);   

if cdsTitle.ChangeCount>   0 then   

cdsTitle.ApplyUpdates (-1);   

end;   

Result: = FLastUpdateErrors;   

if Result =''then   

ibtDoc.Commit   

else   

begin   

ibtDoc.Rollback;   

end;   

finally   

ibtDoc.Active: = False;   //DefaultAction = Rollback   

unlock;   

end;   

end;      

// Код, що працює з ADO   

function TrdmDoc.ApplyChanges: WideString;

  

begin   

lock;   

try   

FLastUpdateErrors: ='';   

if FState = osInactive then   

raise Exception.Create ( 'Документ не був створений або відкритий');   

with cdsTitle do   

begin   

Edit;   

  FieldByName ( 'DOC_SUM'). AsCurrency: = CalcSum;   

Post;   

end;   

adcDocs.BeginTrans;// явні транзакції задаються на рівні   з'єднання   

if FState = osInsert then// а не окремим   компонентом   

begin   

if cdsTitle.ChangeCount>   0 then   

cdsTitle.ApplyUpdates (-1);   

if cdsBody.ChangeCount> 0   then   

cdsBody.ApplyUpdates (-1);   

end;   

if FState = osUpdate then   

begin   

if cdsBody.ChangeCount> 0   then   

cdsBody.ApplyUpdates (-1);   

if cdsTitle.ChangeCount>   0 then   

cdsTitle.ApplyUpdates (-1);   

end;   

Result: = FLastUpdateErrors;   

if Result =''then   

adcDocs.CommitTrans   

else   

begin   

adcDocs.RollbackTrans;   

end;   

finally   

unlock;   

end;   

end;     

Висновок

Це всього лише приклад. У реальних додатках варто більш ретельно продумувати перенесення додатків. Наприклад, переписувати краще не весь сервер додатків, а тільки залежний від джерела даних код, виносячи його в окремі модулі.

Дана стаття не претендує на повноту висвітлення даного питання, а також і автор при викладі підходів для вирішення проблем не претендує на роль "істини в останній інстанції". Тут був викладений лише мінімум відомостей, необхідний для вирішення поставленого завдання, а також деякі міркування, які можуть допомогти при вирішенні подібних проблем.

Всі питання, зауваження, виправлення, доповнення направляйте на [email protected]

Хочу висловити вдячність Ігнатьєву Роману, Павлу Шмакова за поради, критику і наполегливість.

Список літератури

Для підготовки даної роботи були використані матеріали з сайту http://www.rsdn.ru/