ORM in kbmMW #2

Preface

kbmMW’s ORM will in next release be extended with a couple of new generator oriented additions.

Please look for the REST Easy posts for more background information about how to use the ORM and the ORM reference for all the relevant attributes that can be used with the ORM.

The generators currently supported are SEQUENCE, DATETIME, GUID and SHORTGUID, where the SEQUENCE basically created auto incremented numbers either using the backend database’s native generator/sequencer features, or a pivot table.

New sequence types

In next version of kbmMW, it will be possible to define a sequenceType attribute, which can either be DEFAULT, PIVOT or SUBPIVOT.

Using the DEFAULT type (or not specifying sequenceType at all) means that the sequencer will work as it has always worked, either using the database’s features or falling back to a pivot table default named KBMMW_SEQUENCES.

However if you specify it to be of type PIVOT, then you will force it to use a pivot table for this particular generator, regardless of if the database is capable of doing sequences of its own. The default pivot table name will be KBMMW_COUNTERS but it can be changed to something else via the Metadata components CounterTableName. Multiple forced PIVOT sequencers will use the same pivot table.

The type can also be set to SUBPIVOT. Basically it is also using a pivot table for keeping track of the numbers, but in this case you will be able to define a scope, meaning that you can have multiple counters for the same fields, based on the value of other fields.

One example it can be used for, is keeping track of versions of a contract number. Let’s say you will want to have a contract identifier (actually a string in this case) xxx-xxx that you want to be able to provide to your clients. From time to time you will want the contract to be updated with new agreements on for example pricing or conditions. Legally this is a new contract, but since the contract number is already out with your clients, you do not want to change the contract number every time. Instead you want to make a version available.

This way your clients can refer to the contract by the contract identifier (xxx-xxx) and know they will get the latest contract, or a specific version of the contract, for example using this format (xxx-xxx/vvv) because the old version may not have been completely fulfilled yet and is still open.

An example of a contract class which can be versioned automatically:

unit uData;

interface

uses 
   kbmMWORM,
   kbmMWNullable;

type
   [kbmMW_Table('name:contract')]
   TContract = class
   private
      FID:kbmMWNullable<string>;
      FIdentifier:kbmMWNullable<string>;
      FVersion:kbmMWNullable<integer>;
      FPrice:kbmMWNullable<double>;
   public
      [kbmMW_Field('name:id, primary:true, generator:shortGuid',ftString,40)]
      property ID:kbmMWNullable<string> read FID write FID;

      [kbmMW_Field('name:identifier',ftWideString,50)]
      property Identifier:kbmMWNullable<string> read FIdentifier write FIdentifier;

      [kbmMW_Field('name:version, generator:sequence, sequenceType:subpivot, subPivotScope:[identifier]',ftInteger)]
      property Version:kbmMWNullable<integer> read FVersion write FVersion;

      [kbmMW_Field('name:price',ftFloat)]
      property Price:kbmMWNullable<double> read FPrice write FPrice;
   end;

...

initialization
   TkbmMWRTTI.EnableRTTI([TContract]);
   kbmMWRegisterKnownClasses([TContract]);

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

uses
  kbmMWORM,
  uData;
...
     ORM.CreateOrUpgradeTable([TContract]);
...

The moment you insert a new TContract into the database, it will automatically be getting a unique ID which is not required but recommended so you can always uniquely and simply identify any very specific record. It will however also be getting the value of 0 for the Version field, if no other contract has been put into the database with the same Identifier value. If there already existed a record with the same identifier value, the Version field would be 1, 2… etc.

It will essentially store the identifier along with the field name in a pivot table, which default is named KBMMW_COUNTERS, but which default can be changed using the CounterTableName property of the metadata component.

However since you will have many entries in the pivot table, one for each unique combination of the subPivotScope field values, then it can make sense to define a specific table to contain pivot values for exactly that field.

This can be specified by setting the subPivotName value to a database table name that match the use of the sequence.
Example:
subPivotName: ContractVersions

Now all version number pivot values for the TContract’s Version property is stored in the database table called ContractVersions.

The Version field of the TContract class would thus have its attribute defined as this:

[kbmMW_Field(‘name:version, generator:sequence, sequenceType:subpivot, subPivotScope:[identifier], subPivotName:ContractVersions’,ftInteger)]

Happy new year!