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>(,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
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.
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.
6 thoughts on “REST easy with kbmMW #17 – Database 6 – Existing databases”
Can there be a way to output code using kbmMWORMTable instead of defining a delphi class?
I was thinking for example, to use that code generated in a script, so instead of recompiling an exe or dll, just sending the script and running that in a pascal-language scripter ie. TMS Scripter, DWS, PaxCompiler, etc
I suppose it is possible but it would probably require some additional code to serialize and deserialize the class for communication with the kbmmw server
Well Maybe I didn’t explain well, Instead of streaming a class, I can stream a script text, then the upgrade can be done at server side. so instead of output a class definition, maybe it can also output something like:
tbl.fields.add( TkbmMWORMField.Create(xxx, xxx ,…) );
tbl.fields.add( TkbmMWORMField.Create(xxx, xxx ,…) );
tbl.indexes.add (TkbmMWORMIndex.Create(xxx, …) );
Or at least if you can declare a delegate class for the source generator (or overridable methods like ProcessTableName, processFields, etc), so we can generate out own custom output and delegate it:
MyORM.GenerateSourceCode(tbl…, TkbmMWSourceCodeDelphiClass ); // (TkbmMWSourceCodeDelphiClass can be a class or object instance)
MyORM.GenerateSourceCode(tbl…, TkbmMWSourceCustom); (can be a class that generates custom Delphi code like the first example, or any other custom scripting language that we can run at server side)
Im a bit unsure about why you would do that. You can simply use SQL statements from your script. It should be an easier way to define a table than dedicated script code?
Perhaps Im completely overlooking something, so please explain your use case so I can understand.
Hi Kim, well it is more for updating a database/table, rather than creating a new one.
yes for a fresh new database a SQL script is better.
I was thinking a way to make this process ‘scriptable’, so intead of embedding hte table definitions in an exe, store it via a script (using kbmMMWORMTable sentences intead of class definitions), and that can be executed by the host when starting, if there are changes they will be aplied. My doubt comes if I later can consume that kbmMWORMTable to query stuff (this can be done via scripting, using PaxCompiler or DWScript, which are able to define classes or connect Delphi classes with scripts), or do I still the My Table Class definition (class with rtti attributes) to query stuff?.
Hope I have explained it well, or maybe Im complicating things and can be done easier. Your opinion is always important to me.
kbmMW’s ORM encompass two types of SQL… its own generic SQL and translated SQL. The generic SQL is IMO what you would use as the definition in the “script”. Send the generic SQL to the kbmMW server, and let the ORM rewrite the statements to translated SQL and execute those. Then surface the table (a kbmMW ORM method), and you will have access to it via a number of ORM methods taking a TkbmMWORMTable as argument instead of a class instance. Next minor release will add additional Query methods that can operate directly on TkbmMWORMTable.
I dont think its possible to directly define classes in PaxCompiler or DWScript and use those for direct ORM use. You should define the class in Delphi code, and then refer to the class from PaxCompiler/DWScript. The reason is that neither of those (afaik) has Delphi compatible extended RTTI support which kbmMW ORM require.