REST easy with kbmMW #17 – Database 6 – Existing databases

kbmMW already contains quite elaborate features to determine and interpret the metadata of tables in a database.

In next release this has been further strengthened, so it is possible to import existing database tables and automatically create the ORM classes matching the tables.

What that means is that you will be able to use kbmMW’s ORM intelligence to query the tables, persist data back into the tables and manipulate the tables metadata, for example adding, changing or deleting fields and indexes and needed to be able to upgrade a database table to match changed later versions of the ORM table class.

One of the design goals about the ORM has always been to abstract away all the dull work needed, when you realize that you need to change a database table to allow for additional or different fields. In kbmMW it is as simple as changing the ORM class, and use kbmMW’s ORM to CreateOrUpgrade the table. It has already been shown in one of the earlier REST easy posts.

Included in next upcoming release of kbmMW is the ability for the ORM itself to emit code for a Delphi class that match any table the ORM has gotten knowledge about. As the ORM already supports analyzing existing table structure to determine if the existing ORM class is different from the actual table structure, to be able to auto upgrade it, it is a next logical step, to be able to use the knowledge about table structure and metadata to generate completely new database table Delphi classes.

This has been wrapped into a simple tool, ImportDBSchema which I’ll show now.

The code for the tool is actually pretty simple, since the complex parts are contained in kbmMW’s internals.

Basically it use kbmMW with FireDAC to connect to any of the databases supported by both kbmMW’s ORM and FireDAC, which are SQLite, MSSQL, MySQL, Oracle, Interbase/Firebird and PostgreSQL and select one or more tables from the connected database, after which those tables are “surfaced” to kbmMW and code generated.

Surfacing a table means in kbmMW lingual, to establish enough metadata info from the table, to be able to register a TkbmMWORMTable with kbmMW’s ORM, essentially enabling querying etc. without actually having an ORM class available.

But since the TkbmMWORMTable contains all the metadata required for kbmMW’s ORM to operate, then it can also be used as the source for generating new ORM table classes. The ORM class obviously makes it very easy for you to access fields and records of a the table as regular objects with properties.

In fact, importing a table including generating the ORM class, boils down to calling these two ORM methods:

var
  tbl:TkbmMWORMTable;
begin
...
   tbl:=MyORM.SurfaceDynamicTable('actualdbtablename',true);
   MyORM.GenerateDelphiClass(tbl...);
...
end;

Lets try to import a table from the Ensembl genome MySQL database.

In this case we select a the database called aedes_aegypti_core_48_1b on the server ensembldb.ensembl.org, using a username anonymous.

Connection info can be found on this page: https://www.ensembl.org/info/data/mysql.html

Clicking Connect to database opens a FireDAC dialog where we can specify the connection to the database:

Clicking OK, a list of tables within that database (or schema) is presented, and we select a couple of them for import, and click ‘Import selected tables’.
Now we are prompted to type in the unit file name for the file that is to hold the generated classes.

Since we selected two tables, the unit will contain two classes. After clicking OK, the import will start. It may take a short while.

Lets look at the generated unit, which I called ensembl.pas

unit ensembl;

interface

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


type

[kbmMW_Table('name:aedes_aegypti_core_48_1b.dna')]
TAedes_aegypti_core_48_1b_dna = class
private
  FSeq_region_id:integer;
  FSequence:string;
public
  [kbmMW_Field('name:seq_region_id, primary:true', ftInteger)]
  [kbmMW_NotNull]
  property Seq_region_id:integer read FSeq_region_id write FSeq_region_id;

  [kbmMW_Field('name:sequence', ftWideMemo)]
  [kbmMW_NotNull]
  property Sequence:string read FSequence write FSequence;

end;

[kbmMW_Table('name:aedes_aegypti_core_48_1b.gene')]
TAedes_aegypti_core_48_1b_gene = class
private
  FGene_id:integer;
  FBiotype:string;
  FAnalysis_id:word;
  FSeq_region_id:integer;
  FSeq_region_start:integer;
  FSeq_region_end:integer;
  FSeq_region_strand:byte;
  FDisplay_xref_id:kbmMWNullable<integer>;
  FSource:string;
  FStatus:kbmMWNullable<string>;
  FDescription:kbmMWNullable<string>;
  FIs_current:byte;
public
  [kbmMW_Field('name:gene_id, primary:true', ftInteger)]
  [kbmMW_NotNull]
  property Gene_id:integer read FGene_id write FGene_id;

  [kbmMW_Field('name:biotype', ftString, 40)]
  [kbmMW_NotNull]
  property Biotype:string read FBiotype write FBiotype;

  [kbmMW_Field('name:analysis_id', ftSmallInt)]
  [kbmMW_NotNull]
  property Analysis_id:word read FAnalysis_id write FAnalysis_id;

  [kbmMW_Field('name:seq_region_id', ftInteger)]
  [kbmMW_NotNull]
  property Seq_region_id:integer read FSeq_region_id write FSeq_region_id;

  [kbmMW_Field('name:seq_region_start', ftInteger)]
  [kbmMW_NotNull]
  property Seq_region_start:integer read FSeq_region_start write FSeq_region_start;

  [kbmMW_Field('name:seq_region_end', ftInteger)]
  [kbmMW_NotNull]
  property Seq_region_end:integer read FSeq_region_end write FSeq_region_end;

  [kbmMW_Field('name:seq_region_strand', ftShortInt)]
  [kbmMW_NotNull]
  property Seq_region_strand:byte read FSeq_region_strand write FSeq_region_strand;

  [kbmMW_Field('name:display_xref_id', ftInteger)]
  property Display_xref_id:kbmMWNullable<integer> read FDisplay_xref_id write FDisplay_xref_id;

  [kbmMW_Field('name:source', ftString, 20)]
  [kbmMW_NotNull]
  property Source:string read FSource write FSource;

  [kbmMW_Field('name:status', ftWideString, 19)]
  property Status:kbmMWNullable<string> read FStatus write FStatus;

  [kbmMW_Field('name:description', ftWideMemo)]
  property Description:kbmMWNullable<string> read FDescription write FDescription;

  [kbmMW_Field('name:is_current', ftShortInt)]
  [kbmMW_NotNull]
  property Is_current:byte read FIs_current write FIs_current;

end;


implementation


initialization

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

end.

As you may notice, each class name includes both the schema/database name and the table name. If you only wanted the table name as part of the class name, check the checkbox “Dont include schema names in class name” before importing.

In some fairly rare situations, the database table may have been defined with some data types unknown to kbmMW. In this case, the import will still happen, but the fields in question will be marked with an UNKNOWN datatype.

kbmMW also attempts to determine additional index and sequencer information and produce relevant attributes matching that. However this is down prioritized in such way that if deducing those types of information fails, it will be ignored so the basic ORM table class continues to be produced.

Now by adding this unit to your application, and setting up a MySQL kbmMW connection pool to point at the same (or similar) server we used for the import, we can now query data from those two tables easily using the ORM.

var
  lst:TObjectList<TAedes_aegypti_core_48_1b_dna>;
begin
  lst:=MyORM.QueryList<TAedes_aegypti_core_48_1b_dna>([2000],mwoqoGT);
...
end;

In the above example, we will be given a list of TAedes_aegypti_core_48_1b_dna objects for primary key larger than 2000.

If any change is made to any of the objects in the list, the changes can easily be resolved back to the database by

MyORM.Persist(lst);

Using the ORM, it is also very easy to create our own database with the correct structure for those two tables. The database does not even have to be a MySQL based database. It could be any of the ones supported by kbmMW’s ORM.

MyOtherORM.CreateOrUpgradeTable([TAedes_aegypti_core_48_1b_dna,TAedes_aegypti_core_48_1b_gene])

That concludes this blogentry about how you easily can reuse existing databases in a new modern object oriented way.

If you like what I have shown please spread the word about kbmMW by sharing our blogposts and articles.