Preface

In 5.15.xx kbmMW’s ORM got even better since it allow us to easily copy table structure and data from one table in one database to another database.

The table structure

ORM tables can be defined in a couple of ways, where a usual way is to define a Delphi class and provide attributes for it that makes the class a template for a table in the ORM, which in turn then can be used to automatically create a table in the datastore which the current ORM is connected to.

But what if the table in the database do not exist as a Delphi class? Well the ORM is fortunately able to create one for us, based on just about any existing table in any of the supported databases.

procedure TForm1.CreateDelphiClass(const ATableName:string; const AStrings:TStrings);
var
   t:TkbmMWORMTable;
begin
     t:=FORMSrc.SurfaceDynamicTable(ATableName,[mwsdtoPromoteFieldDefaultsAsGenerators]);
     if t<>nil then
        AStrings.Text:=FORMSrc.GenerateDelphiClass(t,[mwocgoNoSchemaNameInClassName]);
end;

The above code shows how to easily have the ORM, at runtime, generate the needed Delphi code that describes a table, and thus can be used for creating tables in all sorts of ORM connected databases.

In the above example, FORMSrc is a variable/field of type TkbmMWORM, which has been connected to a connection pool, that represents the database.

     FORMSrc:=TkbmMWORM.Create(cpSrc);

chSrc is the connection pool, defined at designtime, and in this case hooked up to a FireDAC database instance connected to an old Firebird 1.5 database (which happens to be our old newsgroup database).

All we need to do is to ask the ORM to surface the table based on the table name, and then produce the class for that table.

Surfacing a table means that the ORM gathers very detailed information about the table structure, indexes, generators, constraints etc. and provide us with a TkbmMWORMTable instance that we can use for accessing that table via the ORM.

In fact enough detail is collected to be able to reproduce the table (within the scope of the ORM) elsewhere, which we utilize by asking the ORM to generate the Delphi code for us for that particular surfaced table using the GenerateDelphiClass method.

The output of the calling the method with the table name TBLNEWS looks like this

interface

uses
  DB,
  Math,
  Classes,
  System.Generics.Collections,
  kbmMWGlobal,
  kbmMWObjectMarshal,
  kbmMWNullable,
  kbmMWORM,
  kbmMWRTTI;


type

[kbmMW_Table('name:TBLNEWS')]
TTBLNEWS = class
private
  FAUTOINC:integer;
  FGROUPNO:kbmMWNullable<integer>;
  FARTICLENO:kbmMWNullable<integer>;
  FPARENTNO:kbmMWNullable<string>;
  FTHREADNO:kbmMWNullable<string>;
  FCLIENTDATE:TkbmMWDateTime;
  FSERVERDATE:TkbmMWDateTime;
  FDATESTR:kbmMWNullable<string>;
  FNUMLINES:kbmMWNullable<integer>;
  FNUMBYTES:kbmMWNullable<integer>;
  FCONTENTTYPE:kbmMWNullable<string>;
  FCANCELLED:word;
  FMAILFROM:kbmMWNullable<string>;
  FFROMADDRESS:kbmMWNullable<string>;
  FREPLYTONAME:kbmMWNullable<string>;
  FREPLYTOADDRESS:kbmMWNullable<string>;
  FSUBJECT:kbmMWNullable<string>;
  FMESSAGEID:kbmMWNullable<string>;
  FNREFERENCES:kbmMWNullable<string>;
  FHEADER:kbmMWNullable<string>;
  FBODY:kbmMWNullable<string>;
  FLASTTHREADPOST:TkbmMWDateTime;
  FLASTTHREADPOSTNO:kbmMWNullable<integer>;
  FLASTTHREADPOSTFROMNAME:kbmMWNullable<string>;
  FTOTALTHREADPOSTS:kbmMWNullable<integer>;
  FTOTALREPLIES:kbmMWNullable<integer>;
  FAPPROVED:word;
  FIPADDR:kbmMWNullable<string>;
public
  [kbmMW_Field('name:AUTOINC, primary:true', ftInteger)]
  [kbmMW_NotNull]
  property AUTOINC:integer read FAUTOINC write FAUTOINC;

  [kbmMW_Field('name:GROUPNO', ftInteger)]
  property GROUPNO:kbmMWNullable<integer> read FGROUPNO write FGROUPNO;

  [kbmMW_Field('name:ARTICLENO', ftInteger)]
  property ARTICLENO:kbmMWNullable<integer> read FARTICLENO write FARTICLENO;

  [kbmMW_Field('name:PARENTNO', ftMemo)]
  property PARENTNO:kbmMWNullable<string> read FPARENTNO write FPARENTNO;

  [kbmMW_Field('name:THREADNO', ftMemo)]
  property THREADNO:kbmMWNullable<string> read FTHREADNO write FTHREADNO;

  [kbmMW_Field('name:CLIENTDATE', ftDate)]
  property CLIENTDATE:TkbmMWDateTime read FCLIENTDATE write FCLIENTDATE;

  [kbmMW_Field('name:SERVERDATE', ftDate)]
  property SERVERDATE:TkbmMWDateTime read FSERVERDATE write FSERVERDATE;

  [kbmMW_Field('name:DATESTR', ftString, 50)]
  property DATESTR:kbmMWNullable<string> read FDATESTR write FDATESTR;

  [kbmMW_Field('name:NUMLINES', ftInteger)]
  property NUMLINES:kbmMWNullable<integer> read FNUMLINES write FNUMLINES;

  [kbmMW_Field('name:NUMBYTES', ftInteger)]
  property NUMBYTES:kbmMWNullable<integer> read FNUMBYTES write FNUMBYTES;

  [kbmMW_Field('name:CONTENTTYPE', ftString, 50)]
  property CONTENTTYPE:kbmMWNullable<string> read FCONTENTTYPE write FCONTENTTYPE;

  [kbmMW_Field('name:CANCELLED', ftSmallInt)]
  [kbmMW_NotNull]
  property CANCELLED:word read FCANCELLED write FCANCELLED;

  [kbmMW_Field('name:MAILFROM', ftString, 100)]
  property MAILFROM:kbmMWNullable<string> read FMAILFROM write FMAILFROM;

  [kbmMW_Field('name:FROMADDRESS', ftString, 100)]
  property FROMADDRESS:kbmMWNullable<string> read FFROMADDRESS write FFROMADDRESS;

  [kbmMW_Field('name:REPLYTONAME', ftString, 100)]
  property REPLYTONAME:kbmMWNullable<string> read FREPLYTONAME write FREPLYTONAME;

  [kbmMW_Field('name:REPLYTOADDRESS', ftString, 100)]
  property REPLYTOADDRESS:kbmMWNullable<string> read FREPLYTOADDRESS write FREPLYTOADDRESS;

  [kbmMW_Field('name:SUBJECT', ftString, 180)]
  property SUBJECT:kbmMWNullable<string> read FSUBJECT write FSUBJECT;

  [kbmMW_Field('name:MESSAGEID', ftString, 180)]
  property MESSAGEID:kbmMWNullable<string> read FMESSAGEID write FMESSAGEID;

  [kbmMW_Field('name:NREFERENCES', ftMemo)]
  property NREFERENCES:kbmMWNullable<string> read FNREFERENCES write FNREFERENCES;

  [kbmMW_Field('name:HEADER', ftMemo)]
  property HEADER:kbmMWNullable<string> read FHEADER write FHEADER;

  [kbmMW_Field('name:BODY', ftMemo)]
  property BODY:kbmMWNullable<string> read FBODY write FBODY;

  [kbmMW_Field('name:LASTTHREADPOST', ftDate)]
  property LASTTHREADPOST:TkbmMWDateTime read FLASTTHREADPOST write FLASTTHREADPOST;

  [kbmMW_Field('name:LASTTHREADPOSTNO', ftInteger)]
  property LASTTHREADPOSTNO:kbmMWNullable<integer> read FLASTTHREADPOSTNO write FLASTTHREADPOSTNO;

  [kbmMW_Field('name:LASTTHREADPOSTFROMNAME', ftString, 180)]
  property LASTTHREADPOSTFROMNAME:kbmMWNullable<string> read FLASTTHREADPOSTFROMNAME write FLASTTHREADPOSTFROMNAME;

  [kbmMW_Field('name:TOTALTHREADPOSTS', ftInteger)]
  property TOTALTHREADPOSTS:kbmMWNullable<integer> read FTOTALTHREADPOSTS write FTOTALTHREADPOSTS;

  [kbmMW_Field('name:TOTALREPLIES', ftInteger)]
  property TOTALREPLIES:kbmMWNullable<integer> read FTOTALREPLIES write FTOTALREPLIES;

  [kbmMW_Field('name:APPROVED', ftSmallInt)]
  [kbmMW_NotNull]
  property APPROVED:word read FAPPROVED write FAPPROVED;

  [kbmMW_Field('name:IPADDR', ftString, 64)]
  property IPADDR:kbmMWNullable<string> read FIPADDR write FIPADDR;

end;

implementation


initialization

  TkbmMWRTTI.EnableRTTI([TTBLNEWS, TObjectList<TTBLNEWS>]);
  kbmMWRegisterKnownClasses([TTBLNEWS, TObjectList<TTBLNEWS>]);

Obviously we can simply use

FORMDst.CreateOrUpgradeTable(TTBLNEWS);

to create the table structure in a different database, pointed to by the FORMDst TkbmMWORM instance. After that we can copy the data over.

However we can do it even simpler, without the need to compile an application containing our specific TTBLNEWS class.

     tSrc:=FORMSrc.SurfaceDynamicTable(ATableName,[mwsdtoPromoteFieldDefaultsAsGenerators]);
     if tSrc<>nil then
     begin
          // If source table surfaced, clone it to the destination ORM.
          tDst:=tSrc.Clone(FORMDst);
          FORMDst.CreateOrUpgradeTable(tDst);
     end;

Again we surface the original table, but this time we do not create a Delphi class. Instead we ask the ORM to clone the TkbmMWORMTable instance onto another ORM (FORMDst) connected to another database.

As the FORMDst ORM now knows about our table, we can directly use it to CreateOrUpgradeTable, and thus very easily have copied the table structure completely dynamically without linking in any Delphi classes describing the tables.

The data

Now all that is missing is copying the data.

This can fortunately also be done easily. Simply query the source ORM, make sure that the data is marked as to be inserted and persist the data in the destination ORM.

And in our case, try to move the data in batches, to avoid loading loads of records into memory. This is not important for smaller tables (few tens of thousands of records), but for a very large amount of records, using the new batch features in kbmMW v. 5.15.xx may be of good assistance.

Notice though that I have had limited success getting FireDAC to actually not fetching all data from the Firebird 1.5 database, so even if kbmMW may batch, any DB API may not always do it.

A final MoveTable method could look like this

procedure TForm1.MoveTable(const ATableName:string);
var
   tSrc,tDst:TkbmMWORMTable;
   ds:TkbmMWCustomPooledCursor;
   ls:IkbmMWORMQueryStage<TkbmMWORMQueryStageTable>;
begin
     // Check if destination table exists, then drop it.
     tDst:=FORMDst.SurfaceDynamicTable(ATableName,[mwsdtoPromoteFieldDefaultsAsGenerators]);
     if tDst<>nil then
        FORMDst.DeleteTable(tDst);

     // Now create table from source.
     tSrc:=FORMSrc.SurfaceDynamicTable(ATableName,[mwsdtoPromoteFieldDefaultsAsGenerators]);
     if tSrc<>nil then
     begin
          // If source table surfaced, clone it to the destination ORM.
          tDst:=tSrc.Clone(FORMDst);
          FORMDst.CreateOrUpgradeTable(tDst);

          // Copy data.
          ls:=FORMSrc.Using(tSrc).AsInserted.Batched.Query([],mwoqoEQ);
          repeat
                ds:=TkbmMWCustomPooledCursor(ls.AsDataset);
                if ds<>nil then
                   FORMDst.PersistDataset(tDst,ds);
          until ds=nil;
     end;
end;

In this case we first check to see if the destination database already contains a table with same name. If so, we delete it. You may not like to do that and could thus simply avoid the call to DeleteTable.

Next we establish information about the source table and clone it to the destination ORM after which we ask the ORM to ensure that a compatible table with that name and structure exists or will be made to exist on the database represented by the destination ORM.

Finally we use a new 5.15.xx feature allowing us to query records and have them returned ready for insert elsewhere, and in a batched mode in the following statement

ls:=FORMSrc.Using(tSrc).AsInserted.Batched.Query([],mwoqoEQ);

We use the fluid variant of the query syntax to ask for data from the source table, matching any key value, returned ready for insertion, and we only want a batch at a time. A batch is currently 100 records and as is not configurable.

When we execute the line

ds:=TkbmMWCustomPooledCursor(ls.AsDataset);

we will be given a batch, or if there are no more batches the value nil will be returned to us.

While ds has a value we persist the dataset using the destination ORM, and loop until we get the value nil at which point no more records can be copied.

Do not free the returned ds instance. That is entirely handled internally by the ORM query stage logic when it goes out of scope.

Prologue

The above demonstrates one new ORM feature in 5.15.xx.

Another new feature in v. 5.15.xx is that it is able to understand partial Firebird/Interbased intermediate language (BLR) and thus able to determine if default column values has been defined and which they are, as long as they have been defined to a constant.

 

 611 total views,  6 views today

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.