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:
...
var
person:TPerson;
begin
person:=TPerson.Create;
person.FullName:='Hans Hansen';
person.Address:='Hans Home Address 1';
person.Age:=65;
orm.Persist(person);
person.Free;
person:=TPerson.Create;
person.FullName:='Kim Kimsen';
person.Address:='Kims Home Address 1';
person.Age:=43;
orm.Persist(person);
person.Free;
end;
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:
...
var
o:TObjectList<TPerson>;
begin
// Simple fluent returning a list of matching objects.
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsList;
o.Free;
end;
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:
...
var
p:TPerson;
begin
// Simple fluent returning first match as an object.
p:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsItem;
p.Free;
end;
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:
...
var
p:TMyPerson;
begin
// Simple fluent returning first match as an object of a different type.
p:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsGeneric.&Object<TMyPerson>;
p.Free;
end;
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:
...
var
o:TObjectList<TMyPerson>;
begin
// Simple fluent returning first match as a list of objects of a different type.
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsGeneric.List<TMyPerson>;
o.Free;
end;
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.
co:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsON;
json:=TkbmMWJSONStreamer.Create;
s:=json.SaveToUTF16String(co);
json.Free;
co.Free;
Resulting in the variable s holding this JSON value:
[{"ID":"3EA45A890C3847EA9FB3FE67EFD8F007","FullName":"Hans Hansen","Address":"Hans Home Address 1","Age":65},{"ID":"0BE359E70ED44D5984D525BFA7A517C4","FullName":"Kim Kimsen","Address":"Kims Home Address 1","Age":43}]
If you need the data as TDataset, you can do:
var
ds:TDataset;
begin
ds:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsDataset;
ds.Free;
end;
Easy combination of ORM data and LINQ
If you want to further manipulate the data by using LINQ:
// Doing LINQ on fluent result
n:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').AsLinq.Sum('Age');
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:
// Doing LINQ on fluent result
n:=orm.Using<TPerson>.AsLinq.Sum('Age');
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:
// Doing LINQ on fluent result using native query mode.
n:=orm.Using<TPerson>.Query('SELECT * FROM person').NativeMode.AsLinq.Sum('Age');
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:
// Doing LINQ on fluent result using native query mode.
n:=orm.Using<TPerson>.NativeQuery('SELECT * FROM person').AsLinq.Sum('Age');
And similarly you can force to use kbmMW’s query mode:
// Doing LINQ on fluent result using kbmMW query mode.
n:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson').MWMode.AsLinq.Sum('Age');
// Doing LINQ on fluent result using kbmMW query mode.
n:=orm.Using<TPerson>.MWQuery('SELECT * FROM uData.TPerson').AsLinq.Sum('Age');
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:
// Fluent with positional param
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson WHERE FullName LIKE ?').Param('%Kim%').AsList;
o.Free;
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.
// Fluent with multiple positional param
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson WHERE FullName LIKE ? AND Age>?').Param('%Kim%').Param(40).AsList;
o.Free;
// Fluent with multiple positional param
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson WHERE FullName LIKE ? AND Age>?').Params(['%Kim%',40}).AsList;
o.Free;
The .Param statement also supports named parameters:
// Fluent with named param
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson WHERE FullName LIKE :name').Param('name','%Kim%').AsList;
o.Free;
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:
// Fluent with TParams
params:=TParams.Create;
param:=params.CreateParam(ftString,'name',ptInput);
param.AsString:='%Kim%';
o:=orm.Using<TPerson>.Query('SELECT * FROM uData.TPerson WHERE FullName LIKE :name').Params(params,true).AsList;
o.Free;
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:
// Very simple fluent aggregation without selection limitation.
n:=orm.Using<TPerson>.Sum('Age');
n:=orm.Using<TPerson>.StdDev('Age');
n:=orm.Using<TPerson>.Avg('Age');
n:=orm.Using<TPerson>.Min('Age');
n:=orm.Using<TPerson>.Max('Age');
n:=orm.Using<TPerson>.Count;
n:=orm.Using<TPerson>.Aggregate(mwoqaSum,'Age');
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:
var
tx:TkbmMWTransaction;
begin
tx:=TkbmMWTransaction.Create;
try
tx.StartTransaction;
try
orm.Using<TPerson>.UsingTransaction(tx).Sum('Age');
orm.Using<TPerson>.UsingTransaction(tx).Min('Age');
tx.Commit;
except
tx.Rollback;
end;
finally
ts.Free;
end;
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.
