<< Automatic database structure comparison with recompilation of triggers and procedures | IBEBlock | IBEBLOCK and Test Data Generator >>

Data comparer using cursors

The following example illustrates the use of cursors to compare two tables in different databases.

 execute ibeblock (
   ProcessInserts boolean = TRUE,
    ProcessUpdates boolean = TRUE,
    ProcessDeletes boolean = TRUE)
 returns (
    InsertedRecs integer = 0 comment 创Records inserted创,
    UpdatedRecs integer = 0 comment 创Records updated创,
    DeletedRecs integer = 0 comment 创Records deleted创,
    TotalTime double precision = 0 comment 创Time spent (seconds)创)
 as
 begin
    RecNum = 50000; -- How many records will be inserted into our test table

If the databases already exist we will not try to create them. Of course, this this approach does not apply to remote databases.

    if (not ibec_fileexists(创c:\MasterDB.fdb创)) then
       create database 创localhost:c:\MasterDB.fdb创 user 创SYSDBA创 password 创masterkey创
       page_size 4096 sql_dialect 3

CLIENTLIB isn't mandatory if you're using the standard gds32.dll.

       clientlib 创C:\Program Files\Firebird\bin\fbclient.dll创;

    if (not ibec_fileexists(创c:\SubscriberDB.fdb创)) then
       create database 创localhost:c:\SubscriberDB.fdb创 user 创SYSDBA创 password 创masterkey创
       page_size 4096 sql_dialect 3
       clientlib 创C:\Program Files\Firebird\bin\fbclient.dll创;

Creating two named connections to our databases...

    create connection MasterDB dbname 创localhost:c:\MasterDB.fdb创
    password 创masterkey创 user 创SYSDBA创
    clientlib 创C:\Program Files\Firebird\bin\fbclient.dll创;

    create connection SubscriberDB dbname 创localhost:c:\SubscriberDB.fdb创
    password 创masterkey创 user 创SYSDBA创
    sql_dialect 3
    clientlib 创C:\Program Files\Firebird\bin\fbclient.dl创';

Now we shall create the IBE$$TEST_DATA table in each database and populate it with some data:

    CreateStmt =
       创create table IBE$$TEST_DATA (
         ID integer not null,
         ID2 varchar(20) not null,
         F_INTEGER integer,
         F_VARCHAR varchar(100),
         F_DATE date,
         F_TIME time,
         F_TIMESTAMP timestamp, 
         F_NUMERIC numeric(15,2),
         F_BOOL char(1) check (F_BOOL in (创T创, 创F创)),
         F_BLOB blob sub_type 1,
         F_SEASON varchar(15) check(F_SEASON in (创Spring创, 创Summer创, 创Autumn创, 创Winter创)))创;

IBE$$TEST_DATA will have a primary key consisting of two fields. This is just to demonstrate how to do this when a primary key consists of more than one field.

    AlterStmt =
       创alter table IBE$$TEST_DATA add constraint PK_IBE$$TEST_DATA primary key (ID, ID2)创;

First we're working with the MasterDB:

    use MasterDB;

If IBE$$TEST_DATA doesn't exist in the database we must create it:

    if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name = 创IBE$$TEST_DATA创)) then
    begin

Creating the table itself...

       execute statement :CreateStmt;

DDL statements must be committed explicitly:

       commit;

...and create a primary key:

       execute statement :AlterStmt;
       commit;

So, we've just created the table. Now we should populate it with data. We will generate some random data for each field, and use an autoincrement for the first primary key field value:

       i = 0;
       while (i < RecNum) do
       begin
          fid2    = ibec_randomstring(1,20,65,90);
          fint    = ibec_random2(1, 100000);
          fvarc   = ibec_randomstring(1,100,65,90);
          fdate   = ibec_random2(20000,40000);
          ftime   = ibec_random(0);
          ftimest = ibec_random2(20000,40000) + ibec_random(0);
          fnum    = ibec_random2(1,40000) + ibec_random(0);
          fbool   = ibec_randomval(创T创,创F创);
          fblob   = ibec_randomstring(500, 1000, 0, 255);
          fseason = ibec_randomval(创Spring'创, 创Summer创, 创Autumn创, 创Winter创);

          insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);
          i = i + 1;

We will display a progress message after each 500 records inserted. In the SQL Editor it will be displayed on the progress panel above the code editor.

          if (ibec_mod(i, 500) = 0) then
          begin
             ibec_progress(i || 创 records inserted...创);

Don't forget to commit!

             commit;
          end
       end

Once more COMMIT. Maybe there are some uncommited INSERTs...

       commit;
    end

Let's work with the second connection...

    use SubscriberDB;

If IBE$$TEST_DATA doesn't exist in the database we must create it:

    if (not exists(select rdb$relation_name from rdb$relations where rdb$relation_name =  IBE$$TEST_DATA)) then
    begin
    execute statement :CreateStmt;

Don't forget to commit each DDL statement explicitly!

    commit;
    execute statement :AlterStmt;
    commit;

The idea is that we fetch the data from the first database and insert it into IBE$$TEST_TABLE in the second database:

    use MasterDB;

    i = 0;
    k = 0;

FOR ... SELECT will select data from the first database...

    for select * from IBE$$TEST_DATA
    into vals
    do
    begin

...and we will insert them into the second database:

       use SubscriberDB;
       k = k + 1; -- Just a counter...

Now we should modify some of the data. Otherwise we''ll have nothing to compare ;-)

       if (ibec_mod(k,100) <> 0) then 

Each hundredth record will be skipped...

       begin
          if (ibec_mod(i,10) = 0) then 

the 8th field of each tenth record will be changed to NULL...

             vals[7] = null;
          if (ibec_mod(i,30) = 0) then 

...and the 10th field of each 30th record will be modified...

             vals[9] = ibec_randomstring(500, 1000, 0, 255);

Finally insert a record:

          insert into SubscriberDB.IBE$$TEST_DATA values :vals;
          i = i + 1;

After each 500 inserted records we will display a progress message. We will also commit after every 500 INSERTs:

          if (ibec_mod(i, 500) = 0) then
          begin
             ibec_progress(i || 创records inserted...创);
             commit;
          end
       end
    end

Once again COMMIT...

    use SubscriberDB;
    commit;

Now we will insert some more data into the second database just to provide further discrepancies between the two tables...

    i = k + 1;
    while (i < (RecNum + 1000 + 1)) do
    begin
       fid2    = ibec_randomstring(1,20,65,90);
       fint    = ibec_random2(1, 100000);
       fvarc   = ibec_randomstring(1,100,65,90);
       fdate   = ibec_random2(20000,40000);
       ftime   = ibec_random(0);
       ftimest = ibec_random2(20000,40000) + ibec_random(0);
       fnum    = ibec_random2(1,40000) + ibec_random(0);
       fbool   = ibec_randomval(创T创,创F创);
       fblob   = ibec_randomstring(500, 1000, 0, 255);
       fseason = ibec_randomval(创Spring创, 创Summer创, 创Autumn创, 创Winter创);

       insert into IBE$$TEST_DATA values (:i, :fid2, :fint, :fvarc, :fdate, :ftime, :ftimest, :fnum, :fbool, :fblob, :fseason);

       if (ibec_mod(i, 500) = 0) then
       begin
          ibec_progress(i || 创records inserted...创);
          commit;
       end
       i = i + 1;
    end
    commit;
 end

So, let's begin to compare data. Our goal is make the second IBE$$TEST_DATA a full copy of the first IBE$$TEST_DATA.

First of all we should get the primary key of the reference table:

 use MasterDB;
    i = 0;
    for select i.rdb$field_name
    from rdb$relation_constraints rc, rdb$index_segments i, rdb$indices idx
    where (i.rdb$index_name = rc.rdb$index_name) and
          (idx.rdb$index_name = rc.rdb$index_name) and
          (rc.rdb$constraint_type = 创PRIMARY KEY创) and
          (rc.rdb$relation_name = 创IBE$$TEST_DATA创)
    order by i.rdb$field_position
    into fldname
    do
    begin
       PKFields[i] = fldname;
       i = i + 1;
    end

Now we need to get a list of remaining fields:

    SelStmt = 创select rdb$field_name
               from rdb$relation_fields
               where (rdb$relation_name = 创IBE$$TEST_DATA创)创;

Here we add a condition to exclude primary key fields from the SELECT result:

    i = 0;
    HighDim = ibec_high(PKFields);
    for i = 0 to HighDim do
       SelStmt = SelStmt || 创 and (rdb$field_name <> 创 || ibec_trim(PKFields[i]) || 创)创;

We need the natural order of the fields...

    SelStmt = SelStmt || 创 order by rdb$field_position创;

Finally execute the SELECT statement just created and get an array of all non-PK fields:

    i = 0;
    for execute statement :SelStmt
    into :s
    do
    begin

Trim spaces, we don't need them...

       NonPKFields[i] = ibec_trim(:s);
       i = i + 1;
    end

Let's compose necessary statements:

 SelStmt will be used to retrieve data  
 UpdStmt will be used to update the second table if two records differ:
    SelStmt = 创创;
    UpdStmt = 创update ibe$$test_data set 创;
    WhereClause = 创 where 创;

    HighDim = ibec_high(NonPKFields);
    for i = 0 to HighDim do
    begin
       SelStmt = SelStmt || NonPKFields[i];
       SelStmt = SelStmt || 创, 创;
       UpdStmt = UpdStmt || ibec_chr(13) || NonPKFields[i] || 创 = :创 || NonPKFields[i];
       if (i < HighDim) then
          UpdStmt = UpdStmt || 创, 创;
    end

Here we compose a WHERE clause with primary key fields: WHERE (PK_FIELD1 = :PK_FIELD1) AND (PK_FIELD2 = :PK_FIELD2) AND ...

    HighDim = ibec_high(PKFields);
    for i = 0 to HighDim do
    begin
       SelStmt = SelStmt || ibec_trim(PKFields[i]);
       WhereClause = WhereClause || 创(创 || ibec_trim(PKFields[i]) || 创 = :创 || ibec_trim(PKFields[i]) || 创)创;
       if (i < HighDim) then
       begin
          SelStmt = SelStmt || 创, 创;
          WhereClause = WhereClause || 创 and 创;
       end
    end

    SelStmt = 创select 创 || SelStmt || 创 from IBE$$TEST_DATA order by 创;

    for i = 0 to HighDim do  
    begin
       SelStmt = SelStmt || ibec_trim(PKFields[i]);
       if (i < HighDim) then
          SelStmt = SelStmt || 创, 创;
    end

    PKFieldCount = ibec_high(PKFields)+1;
    PKFieldIndex = ibec_high(NonPKFields)+1;

    StartTime = ibec_gettickcount(); -- Note the time...

    MasterCR = ibec_cr_OpenCursor(MasterDB, SelStmt);
    SubscriberCR = ibec_cr_OpenCursor(SubscriberDB, SelStmt);

Compose the INSERT statement:

    InsFields = 创创; InsValues = 创创;
    FldCount = ibec_cr_FieldCount(SubscriberCR);
    for i = 0 to (FldCount-1) do
    begin
       FldName = ibec_Trim(ibec_cr_FieldName(SubscriberCR, i));
       InsFields = InsFields || FldName;
       InsValues = InsValues || '创:创 || FldName;
       if (i < (FldCount-1)) then
       begin
          InsFields = InsFields || 创, 创;
          InsValues = InsValues || 创, 创;
       end
    end
    InsStmt = 创insert into ibe$$test_data (创 || InsFields || 创) values (创 || InsValues || 创)创;

 ibec_UseConnection(SubscriberDB);

    while (not (ibec_cr_Eof(MasterCR) and ibec_cr_Eof(SubscriberCR))) do
    begin
       CompResult = 0;
       if (ibec_cr_Eof(MasterCR)) then
          CompResult = 1;
       else if (ibec_cr_Eof(SubscriberCR)) then
          CompResult = -1;
       else
       begin
          ibec_cr_Fetch(MasterCR, MasterPK, PKFieldIndex, PKFieldCount);
          ibec_cr_Fetch(SubscriberCR, SubscriberPK, PKFieldIndex, PKFieldCount);
          CompResult = ibec_CmpRecords2(MasterPK, SubscriberPK);
       end

       if (ProcessUpdates and (CompResult = 0)) then
       begin
          ibec_cr_Fetch(MasterCR, MasterVals, 0, PKFieldIndex);
          ibec_cr_Fetch(SubscriberCR, SubscriberVals, 0, PKFieldIndex);
          CompResult = ibec_CmpRecords(MasterVals, SubscriberVals);
          if (CompResult <> -1) then
          begin
             UpdatedRecs = UpdatedRecs + 1;
             ibec_progress(创Record must be updated...创);
             ibec_cr_Fetch(MasterCR, MasterVals, 0, null);
             execute statement :UpdStmt || WhereClause values :MasterVals;
          end

          ibec_cr_Next(MasterCR);
          ibec_cr_Next(SubscriberCR);
       end
       else if (ProcessInserts and (CompResult < 0)) then
       begin

Redundant master record found. Insert it into the subscriber:

          InsertedRecs = InsertedRecs + 1;
          ibec_progress(创Record must be inserted...创);
          ibec_cr_Fetch(MasterCR, MasterVals, 0, null);
          execute statement :InsStmt values :MasterVals;
          ibec_cr_Next(MasterCR);
       end 
       else if (ProcessDeletes and (CompResult > 0)) then
       begin

Redundant subscriber record found. Delete it.

          DeletedRecs = DeletedRecs + 1;
          ibec_progress(创Record must be deleted...创);
          ibec_cr_Fetch(SubscriberCR, SubscriberPK, PKFieldIndex, PKFieldCount);
          execute statement 创delete from ibe$$test_data 创 || WhereClause values :SubscriberPK;
         ibec_cr_Next(SubscriberCR);
       end;
    end

    ibec_cr_CloseCursor(MasterCR);
    ibec_cr_CloseCursor(SubscriberCR);

    commit;

Done. Close both connections:

    close connection MasterDB;
    close connection SubscriberDB;

Let's count the elapsed time...

    EndTime = ibec_gettickcount();
    TotalTime = (EndTime - StartTime) / 1000;
    suspend;
 end

See also:
Cursor functions

back to top of page
<< Automatic database structure comparison with recompilation of triggers and procedures | IBEBlock | IBEBLOCK and Test Data Generator >>