вторник, 10 мая 2011 г.

Перенос данных с Firebird 1.5 на Firebird 2.5

Появилась необходимость перетащить базу с Firebird 1.5 на Firebird 2.5. После полуторки были еще FB 2.0 и FB 2.1, структура базы поменялась значительно. Почитал форумы - выяснилось, что наиболее надежный способ переноса базы - заново пересоздавать базу из скриптов на FB2.5.

С переносом метаданных все понятно - с помощью IBExpert извлекаем метаданные (команда Extract Metadata) из базы под FB 1.5 и создаем новенькую пустую базу на FB 2.5. А вот как перетаскивать сами данные?

Несмотря на то, что в IBExpert есть множество средств импорта/экспорта данных, готового инструмента для переноса данных между базами - нет. К счастью, IBExpert поддерживает мощный скриптовый язык IBEBlock. И на нем достаточно просто создать скрипт с требуемой функциональностью. Далее расскажу подробнее о получившемся скрипте и опишу итоговую процедуру переноса базы с FB1.5 на FB2.5.

Расширенный INSERT INTO в SQL Editor

Честно говоря, начал я не с IBEBlock, а с расширенного синтаксиса INSERT INTO, который поддерживает IBExpert (см. раздел Moving data between databases в документации IBExpert). SQL Editor поддерживает такой синтаксис:
INSERT INTO <database_alias>.<table_name>[(<columns_list>)]<select_statement>
С помощью этой команды можно перенести таблицу из одной базы в другую. Недолго думая, я написал скрипт вида:
INSERT INTO [db25].TABLE1 SELECT * FROM TABLE1;
INSERT INTO [db25].TABLE2 SELECT * FROM TABLE2;
...
INSERT INTO [db25].TABLE120 SELECT * FROM TABLE120;
и сразу воткнулся в три проблемы.
  • Расширенный вариант INSERT INTO <database_alias>.<table_name> работает только в SQL Editor и не работает в SQL Executor. На практике это означает, что за раз можно выполнить только одну команду INSERT INTO. А выполнить все команды оптом - нельзя. Крайне неудобно, если перенос данных между базами требуется провести не раз и не два.
  • Расширенный вариант INSERT INTO не учитывает вычисляемые read-only поля, созданные через computed by. Если в таблице есть вычисляемое поле, INSERT INTO выдает ошибку.
  • Многие таблицы связаны между собой зависимостями через foreign keys. Поэтому переноситься они должны в определенном порядке. Так, чтобы при загрузке очередной таблицы, все данные, на которые она ссылается, в базе уже были. Порядок загрузки таблиц нужно определить - либо экспериментальным путем, либо теоретическим, изучив структуру базы. В худшем случае зависимости могут оказаться циклическими и тут без танцев с бубном не обойтись - простого способа автоматизации переноса данных в этом случае нет, придется переносить данные частыми, создавать Update-скрипты и т.д. В моем случае таких проблем, к счастью, не возникло.
Таким образом, INSERT INTO годится для одноразового переноса данных между базами - например, чтобы определить правильный порядок переноса таблиц. А для автоматизации процесса переноса данных этот способ не подходит.

Скрипт на IBEBlock

А вот IBEBlock подходит для переноса данных идеально. В документации так и написано - With EXECUTE IBEBLOCK you will be able to: ... Move (copy) data from one database to another. Более того. В примерах использования IBEBlock есть функция Copy Table, предназначенная для копирования таблиц из базы в базу (она, кстати, прекрасно распознает вычисляемые поля и исключает их из INSERT запросов).

Я взял эту функцию за основу и, немного повозившись, получил скрипт с нужным функционалом:
EXECUTE IBEBLOCK
AS
BEGIN

FuncCopyTableData = 'execute ibeblock (
   SrcObjectName variant = '''' comment ''Table name to be copied'',
   DestObjectName variant = '''' comment ''Destination table name, leave empty if no changes need'')
as
begin
   SrcDBPassword = ''masterkey'';
   SrcDBUserName = ''SYSDBA'';
   DestDBUserName = ''SYSDBA'';
   DestDBPassword = ''masterkey'';
   SrcDBCharset = ''WIN1251'';
   DestDBCharset = ''WIN1251'';
   SrcDBClientLib = ''C:\Program Files (x86)\Firebird\Firebird_1_5\bin\fbclient.dll'';
   DestDBClientLib = ''C:\Program Files (x86)\Firebird\Firebird_2_5\bin\fbclient.dll'';
   SrcDBConnStr = ''127.0.0.1:z:\db\db15.fdb'';
   DestDBConnStr = ''127.0.0.1/3051:z:\db\db25.fdb'';

   Time1 = ibec_GetTickCount(); 

   CRLF = ibec_CRLF();
   BS = ibec_Chr(8);
   Success = BS + '' Successfull.'';
   Failed = BS + '' FAILED!'';

   SrcTableName = SrcObjectName;
   DestTableName = DestObjectName; 

   SrcDBParams = ''DBName='' + SrcDBConnStr + '';'' +
                 ''User='' + SrcDBUserName + '';'' +
                 ''Password='' + SrcDBPassword + '';'' +
                 ''Names='' + SrcDBCharset + '';'' +
                 ''ClientLib='' + SrcDBClientLib;

   DestDBParams = ''DBName='' + DestDBConnStr + '';'' +
                  ''User='' + DestDBUserName + '';'' +
                  ''Password='' + DestDBPassword + '';'' +
                  ''Names='' + DestDBCharset + '';'' +
                  ''ClientLib='' + DestDBClientLib;

   try
     try
       ibec_Progress(''Connecting to '' + SrcDBConnStr + ''...'');
       SrcDB = ibec_CreateConnection(__ctFirebird, SrcDBParams);
       ibec_Progress(Success);
       SrcDBSQLDialect = ibec_GetConnectionProp(SrcDB, ''DBSQLDialect'');
     except
       ibec_Progress(Failed);
       raise;
       Exit;
     end;

     try
       ibec_Progress(''Connecting to '' + DestDBConnStr + ''...'');
       DestDB = ibec_CreateConnection(__ctFirebird, DestDBParams);
       ibec_Progress(Success);
       DestDBSQLDialect = ibec_GetConnectionProp(DestDB, ''DBSQLDialect'');
     except
       ibec_Progress(Failed);
       raise;
       Exit;
     end; 

     ibec_UseConnection(SrcDB); 

     select rdb$relation_name, rdb$system_flag, rdb$external_file, rdb$description
            from rdb$relations
            where (rdb$relation_name = :SrcTableName) and (rdb$view_blr is null)
            into :SrcTableData;

     if (SrcTableData[''RDB$RELATION_NAME''] is null) then
       exception cant_find_table ''There is no such table ('' + :SrcTableName + '') in the source database.'';
     IsSys = SrcTableData[''RDB$SYSTEM_FLAG''] = 1;
     if (IsSys) then
       exception cant_copy_system_table ''Cannot copy a system table.'';


     if ((DestTableName is null) or (DestTableName = ''''))  then
       DestTableName = SrcTableName; 

     DestTableNameFmt = ibec_IIF(DestDBSQLDialect = 3, ibec_QuotedStr(:DestTableName, ''"''), ibec_AnsiUpperCase(:DestTableName));
     SrcTableNameFmt = ibec_IIF(SrcDBSQLDialect = 3, ibec_QuotedStr(:SrcTableName, ''"''), ibec_AnsiUpperCase(:SrcTableName)); 

     select rdb$field_name
            from rdb$relation_fields
            where (rdb$relation_name = ''RDB$FIELDS'') and
                  (rdb$field_name = ''RDB$FIELD_PRECISION'')
            into :bPrecision;
     bPrecision = ibec_IIF(:bPrecision is NULL, FALSE, TRUE);

     SelStmt = ''select rf.rdb$field_name as fld_name,'' +
                      ''rf.rdb$field_source as fld_domain,'' +
                      ''rf.rdb$null_flag as fld_null_flag,'' +
                      ''rf.rdb$default_source as fld_default,'' +
                      ''rf.rdb$description as fld_description,'' +
                      ''f.rdb$field_type as dom_type,'' +
                      ''f.rdb$field_length as dom_length,'' +
                      ''f.rdb$field_sub_type as dom_subtype,'' +
                      ''f.rdb$field_scale as dom_scale,'' +
                      ''f.rdb$null_flag as dom_null_flag,'' +
                      ''f.rdb$character_length as dom_charlen,'' +
                      ''f.rdb$segment_length as dom_seglen,'' +
                      ''f.rdb$system_flag as dom_system_flag,'' +
                      ''f.rdb$computed_source as dom_computedby,'' +
                      ''f.rdb$default_source as dom_default,'' +
                      ''f.rdb$dimensions as dom_dims,'' +
                      ''f.rdb$description as dom_description,'' +
                      ''ch.rdb$character_set_name as dom_charset,'' +
                      ''ch.rdb$bytes_per_character as charset_bytes,'' +
                      ''dco.rdb$collation_name as dom_collation,'' +
                      ''fco.rdb$collation_name as fld_collation'';
     if (bPrecision) then
       SelStmt = SelStmt + '', f.rdb$field_precision as dom_precision''; 

     SelStmt = SelStmt + CRLF +
               ''from rdb$relation_fields rf '' + CRLF +
               ''left join rdb$fields f on rf.rdb$field_source = f.rdb$field_name'' + CRLF +
               ''left join rdb$character_sets ch on f.rdb$character_set_id = ch.rdb$character_set_id'' + CRLF +
               ''left join rdb$collations dco on ((f.rdb$collation_id = dco.rdb$collation_id) and (f.rdb$character_set_id =  dco.rdb$character_set_id))'' + CRLF +
               ''left join rdb$collations fco on ((rf.rdb$collation_id = fco.rdb$collation_id) and (f.rdb$character_set_id =  fco.rdb$character_set_id))'' + CRLF +
               ''where rf.rdb$relation_name = '' + ibec_QuotedStr(:SrcTableName, '''''''') + CRLF +
               ''order by rf.rdb$field_position'';


     ibec_Progress(''Collecting fields info...'');
     i = 0;
     iUserDomainCount = 0;
     for execute statement SelStmt into :FldData
     do
     begin
       s = ibec_Trim(FldData[''FLD_DOMAIN'']);
       aDomains[i] = ibec_IIF(ibec_Copy(s, 1, 4) = ''RDB$'', null, s);
       if (aDomains[i] is not null) then
         iUserDomainCount = iUserDomainCount + 1; 

       aFields[i] = ibec_Trim(FldData[''FLD_NAME'']); 

       sType = ibec_IBTypeToStr(FldData[''DOM_TYPE''],
                                FldData[''DOM_SUBTYPE''],
                                FldData[''DOM_LENGTH''],
                                FldData[''DOM_SCALE''],
                                FldData[''DOM_SEGLEN''],
                                FldData[''DOM_CHARLEN''],
                                FldData[''DOM_PRECISION''],
                                DestDBSQLDialect);
       aTypes[i] = sType;

       aFieldsNotNull[i] = ibec_IIF(FldData[''FLD_NULL_FLAG''] = 1, '' NOT NULL'', '''');
       aFieldsDefault[i] = ibec_IIF(FldData[''FLD_DEFAULT''] is null, '''', '' '' + ibec_Trim(FldData[''FLD_DEFAULT'']));
       aFieldsComment[i] = FldData[''FLD_DESCRIPTION''];
       aFieldsCharset[i] = ibec_IIF(FldData[''DOM_CHARSET''] is null, '''', ibec_Trim(FldData[''DOM_CHARSET'']));
       aFieldsCollate[i] = ibec_IIF(FldData[''FLD_COLLATION''] is null, '''', ibec_Trim(FldData[''FLD_COLLATION'']));

       aDomainsComputedBy[i] = FldData[''DOM_COMPUTEDBY''];
       i = i + 1;
     end
     ibec_UseConnection(DestDB);

     -------------------------------------------------------------
     -- TRANSFER TABLE DATA --------------------------------------
     -------------------------------------------------------------
   sFields = '''';
   sValues = '''';
   foreach (aFields as FldName key FldKey) do begin
     if (aDomainsComputedBy[FldKey] is null) then
     begin
       if (sFields <> '''') then
       begin
         sFields .= '', '';
         sValues .= '', '';
       end;
       FldNameFmt = ibec_IIF(DestDBSQLDialect = 3, ibec_QuotedStr(:FldName, ''"''), ibec_AnsiUpperCase(:FldName));
       sFields .= FldNameFmt;
       sValues .= '':'' + FldNameFmt;
     end;
   end;

   SelectStmt = ''SELECT '' + sFields + '' FROM '' + SrcTableNameFmt;
   InsertStmt = ''INSERT INTO '' + DestTableNameFmt + '' ('' + sFields + '') VALUES ('' + sValues + '')'';

   ibec_UseConnection(SrcDB);
   i = 0;
   ibec_Progress(''Copying table data...'');
   for execute statement :SelectStmt into :Data
   do
   begin
     ibec_UseConnection(DestDB);
     execute statement :InsertStmt values :Data;
     i = i + 1;
     if (ibec_mod(i, 500) = 0) then
     begin
       commit;
       ibec_Progress(''    '' + ibec_cast(i, __typeString) + '' records copied...'');
     end;
   end;
   ibec_Progress(''Totally '' + ibec_cast(i, __typeString) + '' records copied.'');
   ibec_UseConnection(DestDB);
   commit;

  finally
     if (SrcDB is not null) then begin
       ibec_Progress(''Closing connection to '' + SrcDBConnStr + ''...'');
       ibec_CloseConnection(SrcDB);
     end;
     if (DestDB is not null) then
     begin
       ibec_Progress(''Closing connection to '' + DestDBConnStr + ''...'');
       ibec_CloseConnection(DestDB);
     end;
     Time2 = ibec_GetTickCount();
     sTime = ibec_div((Time2 - Time1), 1000) || ''.'' ||ibec_mod((Time2 - Time1), 1000);
     ibec_Progress(''Finished.'');
     ibec_Progress(''Total time spent: '' || sTime || '' seconds'');
     ibec_Progress(''That''''s all, folks!'');
   end;
end;';

EXECUTE IBEBLOCK FuncCopyTableData ('TABLE1', 'TABLE1');
EXECUTE IBEBLOCK FuncCopyTableData ('TABLE2', 'TABLE2');
...
EXECUTE IBEBLOCK FuncCopyTableData ('TABLE120', 'TABLE120');
end;
Команды EXECUTE IBEBLOCK в конце скрипта расположены в том порядке, в котором нужно переносить таблицы из базы в базу, чтобы не нарушались связи между данными.

P.s. Обращаю внимание на ошибку в примере Copy Table на сайте IBExpert: вместо
ibec_QuotedStr(:SrcTableName, ) нужно писать ibec_QuotedStr(:SrcTableName, ''''), иначе скрипт не работает.

Итоговая процедура переноса базы с Firebird 1.5 на Firebird 2.5

Итоговый процесс перевода базы выглядел следующим образом. Подготовка:
  • С помощью IBExpert\Tools\Extract Metadata извлечь под FB1.5 метаданные в виде скрипта.
  • Выполнить этот скрипт под FB2.5 - убедиться, что все триггеры, процедуры и т.д. создаются без проблем.
  • Определить правильный порядок переноса таблиц из базы в базу. Для этого, например, можно вручную перенести все таблицы (одну за одной) из базы в базу с помощью INSERT INTO в SQL Edtitor.
Собственно перенос базы:
  • Деактивировать триггеры в исходной базе. Для этого открыть исходную базу в IBExpert, в DBExplorer щелкнуть правой кнопкой мыши по Triggers и дать команду Deactivate triggers.
  • Извлечь метаданные (с отключенными триггерами) под FB1.5 в виде скрипта.
  • С помощью полученного скрипта создать чистую пустую базу под FB2.5.
  • Создать скрипт переноса данных на IBEBlock - по аналогии со скриптом, приведенным выше. Важно соблюсти правильный порядок следования таблиц в конце скрипта - при загрузке данных не должны нарушаться связи между данными.
  • Выполнить скрипт, сделать Commit.
  • Активировать триггеры в итоговой базе. Для этого открыть результирующую базу в IBExpert, в DBExplorer щелкнуть правой кнопкой мыши по Triggers и дать команду Activate triggers.

Итоги

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

Комментариев нет:

Отправить комментарий