Digging for wORMs

I’ve promised to release v5 with a new very cool feature (in beta), which I’ll explain a bit about now: kbmMW ORM (object relational model).

Whats an ORM? It can be interpreted in multiple ways. In our way we interpret it as a way to persist, query and manipulate objects to and from a data storage of some sort.

We have focused more on the object persistence part of the ORM, than on the automatic relational management of master/detail scenarios. There are good and bad things about fully automatic relational cascade type storage and data manipulation. In some cases the bad things outweighs the good, and sometimes visa versa.

Show me the code!

Well.. its simple… create an object, tell the framework to persist the object and you have stored your data. It sounds too easy doesn’t it? It really _is_ easy.

This is an ordinary class, for a TPerson. This example contains some funny types, like kbmMWNullable, but thats just a convenience that makes some things even easier. However you could choose to use  plain simple regular native strings and integer and other native types.

type

 [kbmMW_Table('name:person, index:{name:i1,field:name,descending:false}, index:{name:i2,unique:true,fields:[{name:name,descending:true},{name:age}]')]
 TPerson = class
 public
  FID:kbmMWNullable;
  FName:kbmMWNullable;
  FAddress:kbmMWNullable;
  FAge:kbmMWNullable;

 published
  [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
  property ID:kbmMWNullable read FID write FID;

  [kbmMW_Field('name:name',ftWideString,50)]
  property FullName:kbmMWNullable read FName write FName;

  [kbmMW_Field('name:address',ftWideString,50)]
  property Address:kbmMWNullable read FAddress write FAddress;

  [kbmMW_Field('name:age',ftInteger)]
  property Age:kbmMWNullable read FAge write FAge;
end;

initialization

 kbmMWRegisterKnownClasses([TPerson,TObjectList]);

end.

What you may also notice are some custom attributes named kbmMW_Table and kbmMW_Field.

kbmMW_Table tells the kbmMW framework something about how objects of this type are to be stored in data storage. It includes the desired table name and optional additional indexes that would be nice to have of performance reasons.

kbmMW_Field tells the framework that the following property or field is to be persisted, and under what data storage field name it will be persisted. Further it describes how that data storage field should be, type, size, precision and more.

The ID property has more information attached to it. The framework is being told that this field should be the primary field (a primary field should always be declared), and that the fields value is automatically generated upon storage in the database. In this case the value will contain a so called short GUID, which is a shortened version of a regular GUID.

The field refers to a generator. kbmMW ORM supports a number of generators:

• GUID – Returns a GUID formatted as a regular GUID
  {123e4567-e89b-12d3-a456-426655440000}
• SHORTGUID – Returns a short GUID where braces and dashes are missing:
  123e4567e89b12d3a456426655440000
• SEQUENCE – Returns next unique number from a sequence. Provide name of sequencer in sequence property and optional sequencestart property (not supported by all databases!)
• DATETIME – Returns a date time value, formatted according to the dateFormat property

In addition the field type can be set to ftAutoinc, just like any regular TDataset based databases. In this case the database will generate a unique number, which the framework will pickup automatically and return into the persisted object.

So how do we get around to actually persist anything?

First we create an ORM instance, linking it to a connection pool. The connection pool is responsible for the actual connection to the data storage.

 orm:TkbmMWORM;
...
 orm:=TkbmMWORM.Create(kbmMWSQLiteConnectionPool1);

In this case we have chosen a kbmMWSQLite connection pool, but most databases and connection pools are supported. I will write a bit more about that later.

The connection pool and a metadata component matching it (kbmMW will automatically attempt to find the right one if none has been setup) was put on the form at design time.

One single thing you need to setup on the connection pool, is the database name, and if you want the system to automatically create the SQLite database if none exists, then also set its SQLiteOptions property to contain mwsloAutoCreateDB, and set its MetaData to point on the kbmMWSQLiteMetaData1 component.

The metadata component is actually not mandatory to place on the form, and is only shown for completeness. kbmMW will automatically generate a metadata component instance that match the used connection pool, if none has been provided.

As you may be able to see from the screendump (if you have been using kbmMW before), is that this form is part of a simple n-tier server ORM sample which is included with v5.

Lets look at the code again… we now want the ORM to automatically create all relevant tables in the data storage. Im talking about table’s here, because that’s what most people use for storing data, but it could also be other types of storage, which have other concepts for storage than tables. As long as a connection pool, with accompanying metadata exists for that storage, it will work fine with kbmMW ORM.

Having the table created is as simple as this

orm.CreateTable(TPerson);

CreateTable can take an array of classes, if multiple tables should be created in one go.

You can test if a table (or tables) already exists using

 if not orm.ExistsTable(TPerson) then
....

CreateTable will only create tables if they are missing, but using ExistsTable may make it easier for you, to know when you can prepopulate newly created table with whatever bootstrap information you like to put into them from start.

You can use DeleteTable(TPerson) to delete the table and its data, and you can use PurgeTable to leave the table, but remove its contents.

How do we put some data into the table?

We do that by instantiating instances of TPerson like this

 p:=TPerson.Create;
 p.FullName:='Hans Hansen';
 p.Address:='Hans Home Address 1';
 p.Age:=65;
 orm.Persist(p);

After Persist has run successfully, you will find that p.ID has a value, even though we didn’t put one originally. If an object do not have a value for the field that was marked as primary, then that object is understood as a new object.

Thus in the future (for as long as we keep the instance pointed to by p) alive, kbmMW ORM will know that future persisting of it means that the existing record in the table will be updated rather than a new added.

To add more records, simply create more instances and persist them.

Remember that you own the TPerson instances that you create, and can do with them what you want, which also means that you are responsible for freeing them again when you no longer need them.

Freeing the instance will not affect what has been persisted.

If you make changes in the object and want those changes persisted, simply call orm.Persist(p) on the object again.

If you want to delete an object from persistence, you do

orm.Delete(p);

You still have the p instance in memory, but it is now gone from storage.

If you have a collection or an array of TPeople you can Persist and Delete those the same way as already shown.

Persisting data is no fun, unless you can retrieve them again at some point.

kbmMW ORM supports a vast number of ways to search and retrieve data via the various Query methods.

Lets say you want to retrieve an object with a specific primary key

var
 p:TPerson;
...
 p:=orm.Query<TPerson>('123e4567e89b12d3a456426655440000');

Now p will either be nil or point to a matching instance of TPerson. You own this object instance and may free it when you want.

Or if you know there is only 0 or 1 record in the table, you can simply do

p:=orm.Query<TPerson>;

Even if there are more records, only the first one will be returned to you. It should obviously be used with common sense because it does request all records from the data storage, and they may come in any order.

If you want a complete list of all persons known by the data storage

var
 p:TObjectList<TPerson>;
...
 p:=orm.QueryList<TPerson>;

Now… this is all nice and easy.. but what if I want to search for all people with a specific age?

var
 p:TObjectList<TPerson>;
...
 p:=orm.QueryList<TPerson>(['age'],[65]);

Now only people of the age 65 is returned.

var
 p:TObjectList<TPerson>;
...
 p:=orm.QueryList<TPerson>(['age'],[65],mwoqoGT);

Now only people older than 65 is returned. If you only wanted one of them you could have used Query<TPerson>… instead of QueryList.

You can specify multiple fields and values in your search this way, but all are controlled by the given operator (default mwoqoEQ = equal).

If you just wanted a count

var
 i:integer;
...
 i:=orm.QueryCount<TPerson>(['age'],[65],mwoqoGT);

You can use various aggregates, like QueryCount, QuerySum, QueryAvg, QueryMin, QueryMax and QueryStdDev or the generic QueryAggregate method.

Or if you really want the data as a TDataset instead, you can do

var
 ds:TDataset;
...
 ds:=orm.QueryDataset<TPerson>(['age'],[65],mwoqoGT);

This will return a dataset to, which you can use as any other dataset. The dataset is however not directly connected with the data storage behind, so changes in the dataset needs to be either resolved using kbmMW’s regular resolving mechanism (which is nice, but not ORM), or by converting the dataset back to a set of objects you can persist.

The field name given is the name your property is known as. So if you would have searched on a name, you would have specified ‘FullName’ as the field name, rather than ‘name’ as it’s given in the data storage.

With kbmMW ORM you must generally always refer to the object’s property names. There is an exception to that, which is shown further down.

For example if you want to get all deleted persons from the dataset, to let the ORM actually delete those:

var
 p:TObjectList<TPerson>;
...
 p:=orm.ListFromDataset<TPerson>(ds,[usDeleted]);
 orm.Delete(p);
 o.Free;

If you want to know if data has changed (been inserted or modified) in an object, you can

if orm.HasChanged(p) then
...

This is here where kbmMWNullable and TkbmMWDateTime comes into play. Regular native types like string, integer etc. can’t hold the value Null, and they also can’t hold any information about if their value has been changed.

But if you wrap the types in kbmMWNullable, or use TkbmMWDateTime instead of TDateTime, you suddenly have those abilities which the framework can take advantage of.

Looking at the FullName property in our TPerson object, you can see that it was wrapped in kbmMWNullable. That means we at any time can request information about if its null, or modified like this

if p.FullName.IsNull then
...
if p.FullName.Modified then
...

And we can clear its modified flag anytime if we need to

p.FullName.Modified:=False;

And we can set its value to null

p.FullName.Clear;

By default, all properties are marked as non modified when they have been returned as a result of one of the Query methods.

This way, Persist and other UpdateIfChanged / InsertIfChanged methods, will only resolve data back to the data storage if any changes have been made to the data in the object. If you are using regular non wrapped properties, then kbmMW ORM can only assume that the data may have changed, and it will attempt to update all provided objects regardless.

Most of the time, you really want to make more complex searches when querying for data. We have chosen to build in a uniform SQL based query language into kbmMW ORM which is not only easy to understand for most, but is automatically translated on the fly, to the query language used by the data storage.

var
 o:TObjectList<TPerson>;
...
 o:=orm.QueryList<TPerson>('SELECT * FROM uData.TPerson WHERE age>65 ORDER BY FullName'));

Notice that you select from uData.TPerson. That is a so called qualified class name, which means that there is no doubt about which exact TPerson you want to search on. You can have multiple TPerson classes defined in multiple units and that’s perfectly legal. While querying on them, just remember always to prefix the unit name.

Also notice the ORDER BY part. It refers to FullName which is the property in the TPerson class. The query parser also supports joins. But remember that only the fields that match the requested object type will be returned.

var
 o:TPersonAccount;
...
 o:=orm.Query<TPersonAccount>('SELECT p.id, name, value FROM uData.TPerson p, uData.TAccount a WHERE a.PID=p.ID');

To have all data returned, you will have to have define a TPersonAccount class somewhere which contains minimum the fields ID, Name and Value. Excess fields in the query will be silently ignored.

[kbmMW_VirtualTable]
 TPersonAccount = class
 private
   FID:string;
   FName:kbmMWNullable<string>;
   FValue:kbmMWNullable<double>;
 public
   [kbmMW_Field('name:id')]
   property ID:string read FID write FID;

  [kbmMW_Field('name:name')]
  property Name:kbmMWNullable<string> read FName write FName;

  [kbmMW_Field('name:value')]
  property Value:kbmMWNullable<double> read FValue write FValue;
 end;

Notice that this class has been given the attribute kbmMW_VirtualTable. A virtual table is a table that do not exist in the data storage, and as such if you try to CreateTable(TPersonAccount) an exception will be thrown telling you that its a virtual table that is only meant for transient uses.

Also notice that in this case we have defined ID as a regular native string instead of wrapping it with kbmMWNullable. As we will always have a value in the ID property, we can safely choose not to use kbmMWNullable, but its perfectly fine to wrap even this one with kbmMWNullable to follow a simple rule of thumb and not having to judge for each property type.

The kbmMW ORM SQL syntax is fairly complete, and sticking to it allow you to easily move your application between the different database types supported by kbmMW, in particular SQLite, Oracle, MSSQL, PostgreSQL, MySQL and Interbase/Firebird. Also many other databases are supported. If a database specific metadata component (which takes care of the rewriting of SQL) do not exist, then kbmMW ORM will attempt to fall back to a generic SQL metadata component, which will work in many cases.

However if there is a special query you want to use, which kbmMW ORM SQL do not directly support, you can send it raw, provided your orm instance will allow it.

To allow for custom native query statements, make sure that
orm.QueryMode:=mwoqmNative or mwoqmMixed.

It is default mwoqmMW.

Setting it to native, prevents kbmMW ORM to rewrite anything, and all query statements are considered native.

Setting it to mixed, will make kbmMW ORM assume that the query statements are kbmMW ORM SQL, but allow for you to put a # in front of a statement to send a native statement in a query.

var
 o:TObjectList<TPerson>;
...
 o:=orm.QueryList(TPerson,'#SELECT * FROM Person');

Notice that the table name given, must match the table name in the data storage.

But what about n-tier scenarios?

I started out with telling about that this article was based on an n-tier ORM sample, so it makes sense to show the remaining parts that enables a client to query for people, update changes to them and delete them.

kbmMW v5 was from outset designed to be the easiest to use n-tier product ever. With it came the new smart service and smart clients, and what else makes sense than to use those along with the ORM. This makes it possible to create both a server and a client, requiring only a few lines of business code almost totally eliminating boiler plate code (ie. code that does nothing for your business logic, but is required to glue things together).

Thus we will create a smart service with 3 methods, StorePersons, DeletePersons and GetPersons. As we have put the orm instance on the main form (Form1) we refer to that from here.

[kbmMW_Service('name:ORMDEMO')]
 TkbmMWCustomService2 = class(TkbmMWCustomSmartService)
 public
  // This lists stores changes (but not removed items) in the list of persons.
  [kbmMW_Method]
  procedure StorePersons(const APersons:TObjectList<TPerson>);

  // This lists deletes persons in the list.
  [kbmMW_Method]
  procedure DeletePersons(const APersons:TObjectList<TPerson>);

  // This method returns a list of persons.
  [kbmMW_Method]
  function GetPersons:TObjectList<TPerson>;
 end;

implementation

uses
 Unit1;

{$R *.dfm}

procedure TkbmMWCustomService2.StorePersons(const APersons:TObjectList<TPerson>);
begin
 // The APersons list is owned by this function, and must be manually freed.
 Form1.orm.Persist(APersons);
 APersons.Free;
end;

procedure TkbmMWCustomService2.DeletePersons(const APersons:TObjectList<TPerson>);
begin
 // The APersons list is owned by this function, and must be manually freed.
 Form1.orm.Delete(APersons);
 APersons.Free;
end;

function TkbmMWCustomService2.GetPersons:TObjectList<TPerson>;
begin
 // Return all persons (people).
 Result:=Form1.orm.QueryList<TPerson>;
end;

initialization
 TkbmMWRTTI.EnableRTTI(TkbmMWCustomService2);

This is really really simple isn’t it?  Only business logic code and nothing else.

In the FormCreate event handler of the main form Form1, where we have also put the orm creation, we add

 // Register all services automatically.
 // Services will only be autoregistered if they have a kbmMW_Service attribute.
 server.AutoRegisterServices;

In the event handler of the Listen button on Form1 we write

 server.Active:=not server.Active;

That’s all!

Now to the client.

The client has a connect button, a button requesting a list of TPerson and a button to store changes made to TPerson data.

In addition it contains a string grid and a mtPersons kbmMemTable which are live bound together along with the navigator.

Embarcadero’s live binding is in some ways nice, but it is not terribly flexible, so it will only bind to native object properties and fields. In our case we have kbmMWNullable wrapped properties, which are simple to use in code, but which are not understood by livebinding.

This can be circumvented in a number of ways. One is to add a number of alternative properties to the TPerson object, which exposes the same data, but as native types.

This could look like this

[kbmMW_Table('name:person, index:{name:i1,field:name,descending:false}, index:{name:i2,unique:true,fields:[{name:name,descending:true},{name:age}]')]
 TPerson = class
 private
  FID:kbmMWNullable<string>;
  FName:kbmMWNullable<string>;
  FAddress:kbmMWNullable<string>;
  FAge:kbmMWNullable<integer>;

  function GetID:string;
  procedure SetID(AValue:string);
  function GetName:string;
  procedure SetName(AValue:string);
  function GetAddress:string;
  procedure SetAddress(AValue:string);
  function GetAge:integer;
  procedure SetAge(AValue:integer);
 published
  [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
  property ID:kbmMWNullable<string> read FID write FID;

  [kbmMW_Field('name:name',ftWideString,50)]
  property FullName:kbmMWNullable<string> read FName write FName;

  [kbmMW_Field('name:address',ftWideString,50)]
  property Address:kbmMWNullable<string> read FAddress write FAddress;

  [kbmMW_Field('name:age',ftInteger)]
  property Age:kbmMWNullable<integer> read FAge write FAge;

  // One way of livebinding to kbmMWNullable or kbmMWDateTime fields is
  // to add an alternative access to them.
  // LiveBinding only supports basic data types.
  // Remember to kbmMW_Ignore the properties to tell
  // kbmMW not to attempt to stream them.
  // As kbmMWNullable and TkbmMWDateTime values retain
  // information about nullability and modification,
  // it may be preferred to use these types over
  // regular string, int, TDateTime etc. types.
  // If the number of properties of such types is large,
  // writing many alternative setters and getters may become
  // tedious. Instead you can (as this sample shows in unit1.pas), convert
  // the objects to a dataset (a proxy) which is easy to databind with.

  [kbmMW_Ignore]
  property LBID:string read GetID write SetID;

  [kbmMW_Ignore]
  property LBName:string read GetName write SetName;

  [kbmMW_Ignore]
  property LBAddress:string read GetAddress write SetAddress;

  [kbmMW_Ignore]
  property LBAge:integer read GetAge write SetAge;
 end;

Notice that the extra properties are not wrappe with kbmMWNullable. They have also been marked with the attribute kbmMW_Ignore, to tell the kbmMW framework that they are not to be marshalled (serialized).

Their only purpose is to allow for easy object live binding and is basically boiler plate code that we want to avoid.

There is another way that require less typing, and converting the objects to a dataset and back again when needed.

kbmMW ORM provides easy support for that.

In this case, we have already put a mtPerson kbmMemTable on the form at designtime, to make it easy to do designtime live binding.

All we need is to populate it with some data. This happens in the Get persons buttons eventhandler.

But first we need to prepare the smart client.

This can happen in the Connect buttons event handler.

// A form field
c:IkbmMWSmartClient;
...
// Connect button's event handler
c:=TkbmMWSmartRemoteClientFactory.GetClient(Transport,'ORMDEMO');

Then let us make a few methods to get and send data.

function TForm1.GetPersons:TObjectList<TPerson>;
begin
 // Request a list of persons.
 Result:=Use.AsObject<TObjectList<TPerson>>(c.Service.GetPersons);
end;

procedure TForm1.StorePersons(APersons:TObjectList<TPerson>);
begin
 // Store a list of persons.
 c.Service.StorePersons(Use.Arg(APersons));
end;

procedure TForm1.DeletePersons(APersons:TObjectList<TPerson>);
begin
 // Store a list of persons.
 c.Service.DeletePersons(Use.Arg(APersons));
end;

And in the Get Persons buttons event handler put

orm.ToDataset<TPerson>(mtPersons,GetPersons,true);

Now the grid is populated with data, and you can work the data in any way you want. As the data is available in a kbmMemTable, sorting, searching , filtering and other things are very easy to do.

At some point, the data may have been modified, and it should be sent to the server for updating.

In the Store Persons buttons event handler put

var
 persons:TObjectList<TPerson>;
begin
 persons:=orm.ListFromDataset<TPerson>(mtPersons,[usDeleted]);
 if persons.Count>0 then
   DeletePersons(persons)
 else
   persons.Free;
 persons:=orm.ListFromDataset<TPerson>(mtPersons);
 StorePersons(persons);
end;

This first sends a list of deleted TPerson’s to the server’s DeletePersons method (if any were deleted), and then a list of remaining data. In fact that list could also be limited to only inserted or modified data by adding the argument [usModified,usInserted] to the ListFromDataset method call. But right now we just ship all what is visible.

What happens on the server, is that it will know which fields was changed and only update the records that are relevant . Remember this require that all fields in the TPerson object are either wrapped with kbmMWNullable or are of type TkbmMWDateTime.

The combination of smart services, smart clients, advanced object marshalling, and the new ORM really makes it extremely easy to build multi tier applications in a true RAD way!

Happy wORMing!