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
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
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.
