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:

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:

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:

Or a selection:

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.

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:

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:

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

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:

And let us query it:

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:

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

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 🙂

Author: kimbomadsen

3 thoughts on “REST easy with kbmMW #13 – Database 5

  1. WOW Hi Kim Great News and congrats, after some weeks of silence it worth the wait,

    Hope next release will come soon.
    Just a little request: Is there a way you can add an extra parameter to the primary Field definition,
    so we can define a Custom Generator Class or Anonimous Procedure, wich you can assign/pass the current ORM Object content and the ORM context, in this case the TAccount

    Ex:



    [kbmMW_Field(‘name:id, primary:true, generator:custom’,ftString,40, TkbmMWCustomGIDGenerator )]
    [kbmMW_NotNull]
    property ID:kbmMWNullable read FID write FID;

    or

    [kbmMW_Field(‘name:id, primary:true, generator:custom’,ftString,40,
    procedure (AORMRecordObject: TObject; AORMContext: TkbmMWORM; AResultType: TFieldType; AResultValue: Variant )
    begin

    Use a custom procedure or Stored procedure to genearte a new UID
    For example in Mysql there is no Generators, but here is way to create a stored procedure to generate
    concurrent Thread-safe UIDs based o a sequence name or curent record values (Consecutive numbers base on the Year of the date of creation of the account, and generate something like ‘2018000099’). Or if you have Comming MariaDB 10.3.x, there will be Sequence generators

    end;)
    ]
    [kbmMW_NotNull]
    property ID:kbmMWNullable read FID write FID;

    1. Hi,
      I most probably wont be able to put that feature into the release right now, since Im attempting to actually getting it released sooner rather than later. However kbmMW already supports falling back to a non native sequencer/generator when a database do not support it. Then kbmMW will automatically generate a pivot table, which, under transactional control, is hosting all sequencers. It is also threadsafe.

      1. Thank for th replya KIM. No problem, I know there are things first, the main usage for this proposal is to generate sequences based on
        some record field values or otehr kind of input, like a unique ID pero Year, generated by concateaning the year and the sequence:
        2018000001, 2018000002, 2017000001.. (at that time in 2017 when the record was created), the year value can come from a field value of the current record or get it by calling YearOF(Date()) at delphi side before posting. So we manage many sequences pero table base on a Field or combination of fields.

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.