ORM in kbmMW #1

Preface

The kbmMW ORM (Object Relational Mapping) database access features, has been explained in several REST Easy blog posts. However now I think its time that the ORM get its own line of blog posts, starting with this one.

To sum up, the kbmMW ORM is an alternative database access method, where you work with Delphi based objects and classes rather than directly with tables/fields in a database. The ORM makes it very easy to define data storage’s for objects and to retrieve, save and delete the objects from the storage, in a way that, after a very short time of use, makes you think how you could have lived with accessing databases any other way previously 🙂

Please look for the REST Easy posts for more background information about how to use the ORM.

This blogpost will focus on two things that the ORM will support in the upcoming release of kbmMW:

  • A new fluent syntax
  • The ability to link the result of ORM queries directly with LINQ to make additional local manipulation with the search results
  • The ability to use both positional and named parameter values

The new fluent syntax

A new fluent syntax has been added, which makes it a bit easier for us to add feature variations to the ORM, without having to provide specialised methods for all the combinations of those features. Further it is following the way we already are using fluent syntax in the Scheduler and LINQ libraries.

Let us have a look at the new features.

For the completeness of the blogpost I first show the creation of data that we can search on. First the class of data that we want to operate on:

Next the creation of the database structure to hold the TPerson data:

Next the insertion of data into the database:

Now we have some simple data we can operate on fluently.

First I’ll show the fluent syntax returning all data matching the TPerson class from the database:

o will contain 2 TPerson objects populated with the previously inserted data, Hans and Kim. AsList will always assume you will want a TObjectList<yourtype> back. If not items are found, you will receive an empty TObjectList<yourtype>.

If you only wanted the first object returned, you could use AsItem:

Notice if no items are found, p will be nil.

If you want to return an item, but as a different class, for example as a descendant of TPerson or a completely different class which may have some overlapping member field/property names, you can use:

Remember that TMyPerson must be a class known to kbmMW. So you need to have it registered beforehand the same way as TPerson was.

Similarly you can ask for the returned value to be a list of your TMyPerson this way:

You can also ask to get the data return in object notation format, to make it easy to convert to for example JSON, XML, YAML, CSV etc.

Resulting in the variable s holding this JSON value:

If you need the data as TDataset, you can do:

Easy combination of ORM data and LINQ

If you want to further manipulate the data by using LINQ:

AsLinq makes the result directly LINQ aware, which means you can use all the features of kbmMW’s LINQ library to manipulate the resulting data.

If you want to access all data matching the class, you actually do not even need to use the Query statement:

This is equivalent to the previous LINQ example.

Query modes

As you may have read in the REST easy posts about the ORM, you can choose between two syntaxes for querying the datastorage, the native one used by the datastorage engine (typically one of many SQL dialects) or kbmMW’s own SQL syntax, which is then automatically converted to the appropriate query syntax for the given datastorage engine. The syntax choice is called the query mode.

Default fluent ORM assumes you are using the ORM instance global query mode, set on the ORM object itself, however you can select the query mode in a couple different ways:

Notice here that we are referring directly to the table name in the datastorage, rather than to a class name. In fact this SQL statement is run as is towards the datastorage without any manipulation from kbmMW’s ORM.

An alternative way:

And similarly you can force to use kbmMW’s query mode:

Parameters

kbmMW’s fluent ORM supports both named and positional parameters, in contrast to kbmMW’s non fluent ORM syntax, which only supports positional parameters.

This example shows how to use parameters using the positional method:

You simply put a ? where you want a parameter to be provided. Notice that parameters are not like string replacement macros. You can only provide parameters in places where you would refer to a constant value, not keywords or field names.

For each ?, you will need to provide a .Param or a .Params statement. It is fully legal to have multiple .Param statements, but you would normally only have one .Params statement if you prefer to use that syntax instead.

The .Param statement also supports named parameters:

Again the .Param can occur multiple times if needed.

Further you can also provide a TParams instance in similar way that you would do with “old fashioned” TDataset type database queries:

The .Params statement takes an additional optional boolean argument which is used to indicate if the ORM should own the TParams instance, and thus automatically free it when it is no longer needed, or if the developer is responsible for that. True means that the ORM will handle the lifetime of the TParams instance, while False (which is default) means that the developer is responsible for freeing the TParams instance.

Other fluent features

kbmMW’s ORM always provided easy access to request aggregation of data as a result, and the fluent syntax also supports that:

You can specify a specific transaction which is used for the fluent ORM operations, if you do not want the ORM to auto create a transaction on each request:

This way you can group operations together in transactions, the way you prefer.

—-

What’s the story behind the featured image?

In Danish, ORM is in itself a word, which means worm, hence the picture is of a sand worm on a beach.

Kim/C4D

 2,233 total views,  69 views today

Author: kimbomadsen

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.