REST easy with kbmMW #13 – Database 5

The ORM in kbmMW continues to evolve. The upcoming release contains some new nice features that makes it easy to fetch adjacent data from other joined tables, but all defined by Delphi model style classes.

To illustrate the new features, lets think that we have a small bank, and we want to keep track of people and their accounts in any database of our choosing.

We represent a person by the TPerson Delphi class, and an account by the TAccount Delphi class. People are represented as TObjectList<TPerson> and multiple accounts are represented as TObjectList<TAccount>.

The TPerson class could be defined 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>;
   public
      [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;
   end;

As you notice there are attributes like kbmMW_Table and kbmMW_Field here. Those attributes governs how the Delphi class will be mapped towards a datastorage. As a side note its possible to add other attributes like kbmMW_Root, kbmMW_Child, kbmMW_Element and kbmMW_Attribute attributes, which governs how contents of the class is streamed/unstreamed when converting it to and from JSON, XML , YAML, BSON, MessagePack and CSV (also new in this release… a separate blog post may talk about that) which all are formats supported natively by kbmMW.

The above uses of the kbmMW_Table and kbmMW_Field attributes has been covered in several previous blog posts.

For that reason I will only go into details with whats new.

And the TAccount class could be defined like this:

   [kbmMW_Table('name:account')]
   TAccount = class
   private
      FID:kbmMWNullable<string>;
      FPersonID:string;
      FName:kbmMWNullable<string>;
      FValue:double;
   public
      [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
      [kbmMW_NotNull]
      property ID:kbmMWNullable<string> read FID write FID;

      [kbmMW_Field('name:personid',ftString,40)]
      [kbmMW_NotNull]
      [kbmMW_Null('')]
      property PID:string read FPersonID write FPersonID;

      [kbmMW_Field('name:name, default:"Unknown"',ftString,30)]
      [kbmMW_NotNull]
      property Name:kbmMWNullable<string> read FName write FName;

      [kbmMW_Field]
      [kbmMW_Null(Math.NaN)]
      property Value:double read FValue write FValue;
   end;

This one is also pretty standard kbmMW ORM definition stuff. Only minor thing (which is actually not new) is the use of a default value for the Name field.
The reason for that, is that this TAccount class has evolved over time. In the previous versions, there were no Name property.
Many account records may already have been added to the datastorage. Since I now added the Name property, and declared it should not accept Null values, I will have to tell kbmMW what to do with the already existing records in the database. Using the UpgradeTable or CreateOrUpgradeTable methods, kbmMW will automatically figure out how to change the datastorage to now also accept and store the new Name property. However the old records needs updating to conform with the Not null constraint defined, and for that purpose, the default value is used.

The value property is also (for sample purpose) declared differently, in the sense that it will interpret a value of Math.NaN as a Null value, instead of using the kbmMWNullable generics construction used by the other properties.

We can query for all people in the datastorage or a selection of them like this:

procedure TForm6.btnQueryList2Click(Sender: TObject);
var
   o:TObjectList<TPerson>;
begin
     o:=orm.QueryList<TPerson>;
     // Here we have a complete list of all people in the datastorage.
     o.Free;
end;

Or a selection:

procedure TForm6.btnQueryOne2Click(Sender: TObject);
var
   o:TPerson;
begin
     o:=orm.Query<TPerson>(['FullName'],['%IM%'],mwoqoLike);
     if o=nil then
        raise Exception.Create('Not found');
     // We found at least one person containing IM in the FullName property.
     // Only the first one found is returned since we have not asked for
     // a list.
     o.Free;
end;

But what if we would like to return a person along with the persons accounts?

It can be done in a number of ways. Now I will show how to do it in a way, where we can keep the original classes as is, and make a new class which contains both the person information and a list of accounts.

   [kbmMW_VirtualTable(TPerson)]
   TPersonWithAccounts = class(TPerson)
   private
      FAccounts:TObjectList<TAccount>;
   public
      destructor Destroy; override;

      [kbmMW_VirtualField('name:accounts, source:uData.TAccount, key:ID, sourceKey:PID')]
      property Accounts:TObjectList<TAccount> read FAccounts write FAccounts;
   end;
...

destructor TPersonWithAccounts.Destroy;
begin
     FAccounts.Free;
     inherited;
end;

What I have done is defined a virtual table. A virtual table can be used for any type of ORM operations except being base for datastorage definition. Hence calling CreateTable(TPersonWithAccounts) will raise an exception, since we have declared that TPersonWithAccounts as a virtual table. It is only used for representing its based table (TPerson) in a different way. Basically we augment the TPerson class with additional information. You can read more about augmented data structures in one of the previous blog posts.

Whats even more interesting is that there is an Accounts property which can contain a list of TAccount, and that property has been declared as being a virtual field by using the kbmMW_VirtualField attribute. Similar to a virtual table a virtual field can’t exist in a datastorage. It is only for internal use by the developer. Since we already defined the table as virtual, we could have chosen to just use the kbmMW_Field attribute instead of the kbmMW_VirtualField attribute, since the field would anyway never materialize itself into a field in a datastorage.

However its good practice to do as I have shown.

The new bits here is that the kbmMW_Field and kbmMW_VirtualField attributes now also understands a source, key, sourceKey and optionally a value setting. I’ll talk about the value setting later.

The source setting refers to the fully scoped TAccount class. Fully scoped means that you need to tell which unit it was defined in. kbmMW requires use of fully scoped names because there could be another TAccount class defined in another unit and kbmMW needs to know exactly which one you want it to use.

The key setting refers to which field (or array of fields) should be used in TPerson to use as a key when finding matching TAccount instances in the datastorage.

The sourceKey setting obviously refers to which fields in the the TAccount class that the key settings should be matched up against. The number of fields must be equal between key and sourceKey.

A number of fields can be defined like this: key:[field1,field2], sourceKey:[afield1,afield2]

Now if we query using the TPersonWithAccounts class like this:

procedure TForm6.Button4Click(Sender: TObject);
var
   o:TObjectList<TPersonWithAccounts>;
begin
     o:=orm.QueryList<TPersonWithAccounts>('SELECT * FROM uData.TPersonWithAccounts WHERE FullName LIKE ?',['%MS%']);
     // The above shows another way to query using kbmMW's built in SQL support.
     // We will now have recieved a list of TPersonWithAccounts which match the query,
     // all populated from the datastorage represented by TPerson, and 
     // magically the Account property of each of the TPersonWithAccounts instances will contain
     // the accounts matching that specific person.
     o.Free;
end;

We will have received not only the person information, but also the matching accounts.

Let us look at a reverse scenario. We have a TAccount but we would also like to get the matching person that holds the account.

Again that can be done in multiple ways, but a nice clean one is like this:

   [kbmMW_VirtualTable(TAccount)]
   TAccountWithPerson = class(TAccount)
   private
      FPerson:TPerson;
   public
      destructor Destroy; override;

      [kbmMW_VirtualField('name:person, source:uData.TPerson, key:PID, sourceKey:ID')]
      property Person:TPerson read FPerson write FPerson;
   end;

...

destructor TAccountWithPerson.Destroy;
begin
     FPerson.Free;
     inherited;
end;

Now querying for an account will automatically also return an object matching the person holding the account.

procedure TForm6.Button3Click(Sender: TObject);
var
   o:TAccountWithPerson;
begin
     o:=orm.Query<TAccountWithPerson>('SELECT * FROM uData.TAccountWithPerson WHERE Value>?',[9000]);
     o.Free;
end;

In this case we get a list of all accounts with matching TPerson instance for accounts having a value of more than 9000.

If there are multiple accounts held by the same person, that match this, each of the returned instances will have each their own instance of the TPerson.

But what if we actually do not need a complete TPerson instance, but only the name of the person?

Let us make a nice clean class for that:

   [kbmMW_VirtualTable(TAccount)]
   TAccountWithPersonName = class(TAccount)
   private
      FFullName:kbmMWNullable<string>;
   public
      [kbmMW_VirtualField('name:fullName, source:uData.TPerson, key:PID, sourceKey:ID, value:uData.TPerson.FullName')]
      property FullName:kbmMWNullable<string> read FFullName write FFullName;
   end;

And let us query it:

procedure TForm6.Button5Click(Sender: TObject);
var
   o:TAccountWithPersonName;
begin
     o:=orm.Query<TAccountWithPersonName>('SELECT * FROM uData.TAccountWithPersonName WHERE Value>?',[9000]);
     o.Free;
end;

Now the first record found matching in the TAccount datastorage is returned in the TAccountWithPersonName, along with the person’s name.

In fact it is even possible to do complex things in the value setting, by writing any expression that kbmMW handles, like:

 [kbmMW_VirtualField('name:fullName, source:uData.TPerson, key:PID, sourceKey:ID, value:"Mr. "||uData.TPerson.FullName')]

Which will set the field value to “Mr. Hans Hansen” in case the persons full name is “Hans Hansen”, or

 [kbmMW_VirtualField('name:fullName, source:uData.TPerson, key:PID, sourceKey:ID, value:"uData.TPerson.FullName||\" Age:\"||uData.TPerson.Age"')]

Which will concatenate the persons full name with the persons age and enter that in the FullName property of TAccountWithPersonName.

In fact all kbmMW’s SQL column expression features can be used here, including things like string, math, date etc. manipulation functions.

How does kbmMW do this? It’s ORM is clever enough to understand the query statement you may have provided, and rewrite it to match the datastorage database engine, including adding needed manipulations and joins if the database engine supports it. If it does not, kbmMW will instead attempt to emulate the features needed.

Oh… whats about that gorilla image?

Well… it could indicate that the hairy person sitting in the shadow, coding away on kbmMW… namely me…. got an idea…. or you may have a better explanation 🙂