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:

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

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.

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.

Author: kimbomadsen

4 thoughts on “REST easy with kbmMW #17 – Database 6 – Existing databases

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

      1. 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:
        ———————-
        var
        tbl:TkbmMWORMTable;
        fld: TkbmMWORMField;
        begin
        tbl.TkbmMWORMTable.create;
        tbl.fields.add( TkbmMWORMField.Create(xxx, xxx ,…) );
        tbl.fields.add( TkbmMWORMField.Create(xxx, xxx ,…) );
        tbl.indexes.add (TkbmMWORMIndex.Create(xxx, …) );
        end
        —————————

        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:
        i.e.
        MyORM.GenerateSourceCode(tbl…, TkbmMWSourceCodeDelphiClass ); // (TkbmMWSourceCodeDelphiClass can be a class or object instance)
        or
        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)

        Regards

        1. Hi,

          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.

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.