REST easy with kbmMW #9 – Database 4 – Data augmentation and XML

This blog post will focus on one way of augmenting data returned from a database using the ORM, serving this as a wellformed XML result to REST client’s using as little code as possible.

kbmMW’s ORM is pretty good at fetching data from a database based on a class.

Sometimes we want to augment the class with additional data, before returning the data to a client.

This we can use the virtual table attribute for.

An example.

We have a class TPerson, which is used by the ORM to persist and retrieve persons from the person database table.

The person might refer to a company, via a companyId which is a GUID. This is all straight forward.

  TPerson = class
     [kbmMW_Field('name:id, primary:true, generator:shortGUID',ftString,40)]
     property ID:kbmMWNullable<string> read FID write FID;

     property Name:kbmMWNullable<string> read FName write FName;

     property CompanyID:kbmMWNullable<string> read FCompanyID write FCompanyID;

However lets say we want to provide an augmented REST interface to the person information, where we want to add additional fields, like company name.

  TAugmentedPerson = class
     property ID:kbmMWNullable<string> read FID write FID;

     property Name:kbmMWNullable<string> read FName write FName;

     property CompanyID:kbmMWNullable<string> read FCompanyID write FCompanyID;

     property CompanyName:kbmMWNullable<string> read FCompanyName write FCompanyName;

What you can see is that an additional class is defined, which purpose primarely is to marshal augmented TPerson data to REST clients.

We have told that the TAugmentedPerson (which by outset has no relation to TPerson class wise), is a virtual table, which means it does in fact not live in any databases, but it can still be used as the output for queries.

So lets put together an augmented query:

[kbmMW_Service('name:REST, flags:[listed]')]
TsvcRest = class(TkbmMWCustomHTTPSmartService)
   [kbmMW_Rest('method:get, path:persons')]
   function GetPersons:TObjectList<TAugmentedPerson>;
// Return augmented persons.
function TsvcRest.GetPersons:TObjectList<TAugmentedPerson>;
 Result:=dmMain.ORM.QueryList<TAugmentedPerson>('SELECT p.ID as ID, p.Name as Name, '+
           ' CompanyID, c.Name as CompanyName '+
           'FROM uData.TPerson p, uData.TCompany c '+
           'WHERE c.ID=p.Company');

This will make a query, augmenting the person data with a company name and returning it as a JSON object to the REST client.

This is all well and fine.

But lets say that the REST interface wants to return this list of TAugmentedPerson’s as XML?

This is now easily done in kbmMW by adding a responseMimeType to the kbmMW_Rest attribute of the GetPersons method.

[kbmMW_Service('name:REST, flags:[listed]')]
TsvcRest = class(TkbmMWCustomHTTPSmartService)
   [kbmMW_Rest('method:get, path:persons, responseMimeType:application/xml')]
   function GetPersons:TObjectList<TAugmentedPerson>;

This will result in simple XML document representing the resulting list of TAugmentedPerson’s. You can ask kbmMW to add XML declarations, namespaces and types if you want to by adding the properties declared:true, typed:true to the kbmMW_Rest method attribute.

But looking at the XML, it is still not perfect. It’s outer node is called TObjectList<person>, which is not a terribly nice tag name for an XML node. We are missing a way to redefine how kbmMW is to name the TObjectList<TAugmentedPerson> class.

Usually one would use the kbmMW_Root attribute in combination with our own class definition, to specify any name changes to root elements when marshalling data into or out from object instances, similarly to how TAugmentedPerson was defined.

However since we do not define TObjectList<TAugmentedPerson> anywhere (it is implicitely being defined as the result from our ORM.QueryList call, we have no place to specify our settings/attributes for that particular class.

Next version of kbmMW provides a new kbmMW_Alias attribute which handles this issue.

Basically what it does is to declare any class as an attribute wise alias to any other class/classes, like this

TAugmentedPersonList = class(TObjectList<TAugmentedPerson>);

The kbmMW_Alias can have zero or one argument. If an argument is given, it can be a class reference, or an array of class references. If no argument is given, kbmMW automaticallyh defines TAugmentedPersonList to be an alias to TObjectList<TAugmentedPerson> due to the class inheritance.

As we never define TObjectList<TAugmentedPerson> anywhere, we can not refer to it as a class reference, why we use kbmMW’s way to implicitely determine the class by not providing any arguments for the kbmMW_Alias attribute.

In reality we will usually never instantiate any TAugmentedPersonList instances. It is only being used as a “placeholder” for defining attributes (on the class level) on types we don’t directly declare ourselves, like the TObjectList<TAughmentedPerson>.

Now the xml will look pretty, with the outer node named <persons> containing a number of inner nodes named <person> which each of them includes the companyName in addition to other TPerson related data.

As a side note, the  [kbmMW_VirtualTable] attribute can now also take an argument, namely the actual database class for which this class is a virtual class for.

It would be possible to define  [kbmMW_VirtualTable(TPerson)]

It informs kbmMW about that any queries made for TAugmentedPerson (which is not really a table found in the database), where the ORM can not deduce from any kbmMW SQL query statement, where to pickup data from, then it should use TPerson as the goto data table.

So this is now legal:


It will return first found record in the person table, which matches the person named Kim and return that as a TAugmentedPerson instance.

Only fields matching will be filled. Hence in this case the CompanyName value is null since we did not provide any value for it via the query.

But we are getting an object instance which allows us to add our own value for CompanyName, thus in practice augmenting the TPerson look alike object with additional information.


REST easy with kbmMW #8 – Database 3

In previous articles we have seen how easy it is to use kbmMW’s ORM to maintain database structures and access and manipulate data.

Next release of kbmMW continues to extend on the ORM with additional features designed to make typical chores easy.

Most often, you want records to disappear from a table the moment you call ORM’s delete methods.

But sometimes, for example when you are referencing the record from other tables, it would be nice to retain the record silently, but keep it out of the result set of most queries.

Some databases supports this the hard way by using referential integrity, which means you will simply not be allowed to delete the record if some other table reference it.

That leaves it entirely up to the developer to manually manage when to show the record and when not to.

kbmMW’s ORM takes a slightly different approach to the problem. What if you define a field in the class that will flag if the record is to be considered deleted or not, and tell kbmMW that it should take that field into account when operating that particular class?

Next version will allow you to do just that. So instead of actually deleting the record, kbmMW will automatically flag it as deleted, AND automatically keep the record out of the queries result sets, regardless if you use higher level methods to operate the data, or lowerlevel kbmMW SQL statements.

The following is a sample class holding images and other stuff. Notice the kbmMW_Table attribute. It now also contains some new settings:


[kbmMW_Table('name:image, defaultDeleteMethod:mark, deleteMarkProperty:Deleted, deleteMarkValue:true')]
TImage = class // this unit is called uData
   procedure SetBlob(AValue:TMemoryStream); virtual;
   constructor Create; virtual;
   destructor Destroy; override;

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

   property PID:string read FPersonID write FPersonID;

   property Description:kbmMWNullable read FDescription write FDescription;

   property Blob:TMemoryStream read FBlob write SetBlob;

   property Deleted:boolean read FDeleted write FDeleted;

Default value for defaultDeleteMethod is delete or default which means the same… just delete the record (the usual way).

However when setting it to mark, we tell kbmMW that we want kbmMW to mark the deletion of records by setting the value of a specific field to a specific value. Basically it can be most (non blob) types of field, and the value can also be chosen at will. In our case, we indicates that we want the Deleted boolean property to be the one that indicates if a record is deleted or not.

The moment we use ORM.Delete(…) on an TImage instance, the corresponding record will be marked as deleted, and if we search for TImage instances in the database using ORM, the deleted ones will not be returned to us.

So from the perspective of the developer, the delete seems to operate exactly the same as before, with the big exception, that the records are in fact still available in the table. The ORM just hides the deleted ones from us.

In other situations, you might actually want to delete the record the old fashioned way, but you would like also to have a copy of the deleted record in a backup or audit table.

This can also easily be done in kbmMW by setting defaultDeleteMethod to move.

[kbmMW_Table('name:image, defaultDeleteMethod:move, deleteMoveToTable:uData.TBackupImage')]
TImage = class

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

Notice that we also provide a deleteMoveToTable setting, which points to the fully qualified name of the class that should act as the backup/audit table.

Each time we use kbmMW to delete a record, it will first be inserted into the database table named backupImage, and only then be deleted from the image table. All this will run in a single transaction, and if things goes wrong, a rollback will be forced to ensure the consistency between the backup table and the actual table is not violated during the insert/delete operation.

In the above example we have redefined the id field’s attributes, to ensure that there is no generators defined on it. The reason is that using kbmMW’s SQL statements to delete a record will automatically be rewritten to select into and delete. If any generator fields were defined, those would not be populated in such scenario, why kbmMW would raise an exception if generator fields were found. However if you do not use kbmMW’s SQL statements to delete records, its perfectly legal to have additional generator fields in the backup table. Those will then be populated automatically the usual way.

If you really really want to delete a record on a table where the class are using one of these alternative delete methods, you can add an option to make kbmMW just delete the record.



Assuming o is an instance of uData.TImage, and o is actually to be found in the table, then the record corresponding to o will be deleted from the table the old fashioned way.

The same option can be given to the Query methods, to allow showing a record that otherwise would not be returned, because it was marked as deleted.

If the move method was used, then you will have to query using the TBackupImage class to get to the deleted (but backed up) records.

ANN: kbmMemTable v. 7.77.10 Standard and Professional Edition released!

We are happy to announce the latest and greatest release of our memory table.

Whats new in 7.77.10 September 16 2017

  • Added support for SQL DDL statements:
    and some variations (ON instead of FOR, TABLE keyword
    optional in INDEX statement).
  • Added support for CASE WHEN THEN ELSE END in both forms.
  • Added support for NOT IN, NOT BETWEEN, NOT LIKE
  • Fixed CREATE TABLE issues.
  • Added support for SELECT INTO
  • Added support for SQL multistatements. Statements separated
    by ; (semicolon)
  • Added support for EXISTS TABLE and EXISTS INDEX
  • Added support for DEFAULT value in CREATE TABLE
  • Added support for UNIQUE constraint in CREATE TABLE
  • Improved SQL field datatype parsing.
  • Added support for OUT parameters in SQL custom functions.
  • Fixed SQLReplace (Replace) incorrect argument index.
  • Added SQLSplit (Split) custom SQL function to split strings.
  • Added SQLRegExp (RegExp) custom SQL function for pattern
    matching and splitting
  • Added SQLDataType (DataType) custom SQL function for splitting
    SQL datatype declaration.
  • Added Options:TkbmSQLOptions property to TkbmMemSQL.
    soOrderByNullFirst – Default Null orders last in comparison
    soOldFieldNamingSyntax – Revert to old field naming syntax
    soOldLikeSyntax – Revert to old wildcard style like syntax
    else use true SQL style format using % and ?.
  • Added multiple overloaded ExecSQL functions to TkbmMemSQL to
    allow easy one line calls. If source table names are not provided
    they will be named T1..Tn.
  • Changed to support multiple SQL parsing errors before erroring out.

Professional Edition is released with source and additional performance enhancement features to holders of an active kbmMW Pro/Ent Service and Update subscription (SAU).

A free CodeGear Edition can be found bundled with kbmMW CodeGear Edition
for specific Delphi versions.

kbmMemTable supports the following development environments:

  • RAD Studio Delphi/C++ 10.2 Tokyo
  • RAD Studio Delphi/C++ 10.1 Berlin
  • RAD Studio Delphi/C++ 10 Seattle
  • RAD Studio Delphi/C++ XE8
  • RAD Studio Delphi/C++ XE7
  • RAD Studio Delphi/C++ XE6
  • RAD Studio Delphi/C++ XE5
  • RAD Studio Delphi/C++ XE4
  • RAD Studio Delphi/C++ XE3
  • RAD Studio Delphi/C++ XE2
  • Lazarus 1.2.4 with FPC 2.6.4

And can be used on Android, IOS, Linux, OSX and Windows 32/64 bit platforms.

kbmMemTable is the premier high performance, high functionality in memory dataset for Delphi and C++Builder with kbmMemTable Professional topping the scales as being the worlds fastest!

If you have an up to date Service and Update (SAU) subscription, then you can immediately visit to download the latest kbmMemTable release.

If not, please visit our shop at and extend your SAU with another 12 months.