REST easy with kbmMW #2 – Database

Building on the “REST easy with kbmMW #1” article, I will now show one way to interact with data from a database.

kbmMW supports multiple ways to access databases, ranging from more complex to use but with absolutely fastest performance, to very easy to use with very good performance.

The good thing is that you can pick and choose the way you want to use, depending on the scenario. In 99.99% of all cases, the very easy way most likely will be the preferred way. In those very few scenarios where you really need to get the highest reachable performance out of your hardware, you may want to go a less abstracted route, which you can do fully supported by kbmMW.

But as the easy way is the preferred way, this is what I will show now.

Our previous REST sample only displayed Hello world in a number of different ways.

It would be more fun to create a contact management REST server as it better resembles typical use cases for REST servers.

First thing is to make the server understand databases. We do that by opening the primary form (Unit7 in our previous blogpost), and adding one of each of these components:

• TkbmSQLiteConnectionPool
• TkbmMWSQLMetaData

Doing that, your form will look something similar to this.

Then set the Metadata property of the kbmMWSQLiteConnectionPool1 component to kbmMWSQLiteMetaData1.

And set the Database property of the kbmMWSQLiteConnectionPool1 component to the string ‘.\test.db3′.

Set mwsloAutoCreateDB in the SQLiteOptions property of the kbmMWSQLiteConnectionPool1.

And if the Active property of kbmMWSQLiteConnectionPool1 is true, set it to false.

What we have done, is prepared the server to work with a SQLite database. We do need to make sure that a reasonably new sqlite3.dll (if on Windows) is to be found in the same directory as the executable of this project. You can download SQLite from http://www.sqlite.org

We have also told kbmMW that we want kbmMW to auto create the referenced database, if it do not exist.

Save the project. It will make sure Delphi updates referenced units.

Right now we already have access to the kbmMW’s fast database access methods (the 0.01% use case mentioned above). But as I want to demonstrate the easy database access route, we should do add a little bit of code to the Unit7 main form.

Now we will need to put a single line of code into the form’s OnDestroy event, let us do that now. Double click the OnDestroy event of the form in the object inspector, and we will be brought to the source code. Add the following line:

     ORM.Free;

In the already existing OnCreate event of the form, we also need to add a line of code:

     ORM:=TkbmMWORM.Create(kbmMWSQLiteConnectionPool1);

In the form’s public interface section we need to add a new field:

    ORM:TkbmMWORM;

And finally in the uses clause in the interface section we must add the kbmMWORM unit.

The source of your main server form should now look similar to this. The code we have just updated is shown in bold.

unit Unit7;

interface

uses
 Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
 Vcl.Controls, Vcl.Forms, Vcl.Dialogs, kbmMWCustomTransport, kbmMWServer,
 kbmMWTCPIPIndyServerTransport, kbmMWRESTTransStream,
 kbmMWCustomConnectionPool, kbmMWCustomSQLMetaData, kbmMWSQLiteMetaData,
 kbmMWSQLite, kbmMWORM;

type
 TForm7 = class(TForm)
 kbmMWServer1: TkbmMWServer;
 kbmMWTCPIPIndyServerTransport1: TkbmMWTCPIPIndyServerTransport;
 kbmMWSQLiteConnectionPool1: TkbmMWSQLiteConnectionPool;
 kbmMWSQLiteMetaData1: TkbmMWSQLiteMetaData;
 procedure FormCreate(Sender: TObject);
 procedure FormDestroy(Sender: TObject);
 private
 { Private declarations }
 public
 { Public declarations }
 ORM:TkbmMWORM;
end;

var
 Form7: TForm7;

implementation

{$R *.dfm}

procedure TForm7.FormCreate(Sender: TObject);
begin
 ORM:=TkbmMWORM.Create(kbmMWSQLiteConnectionPool1);
 kbmMWServer1.AutoRegisterServices;
 kbmMWServer1.Active:=true;
end;

procedure TForm7.FormDestroy(Sender: TObject);
begin
 ORM.Free;
end;

end.

Next thing to think about is what are we going to store in the database? As it’s a contact management system, we might want to store contact name, address, zip code, city and a comment.

For some contacts we may not know the address, or city, or we may not have specified a comment. So our contact storage must be able to differentiate between data and no data for any of the stored values. Fortunately that is how most SQL type databases operate. They understand a state of NULL for each field defined in them. That state can be checked to see if the field contains a value or not. An empty string is considered a value.

So let’s make a Delphi object that can contain this information.

Create a new unit by clicking File – New – Unit in Delphi.

This gives us an empty source only unit. The intention with it, is that it will hold the definition of our contact object.

The basic TContact class could look like this

 TContact = class
 private
  FID:kbmMWNullable<string>;
  FName:kbmMWNullable<string>;
  FAddress:kbmMWNullable<string>;
  FZipCode:kbmMWNullable<integer>;
  FCity:kbmMWNullable<string>;
  FComments:kbmMWNullable<string>;
 public
  property ID:kbmMWNullable<string> read FID write FID;
  property Name:kbmMWNullable<string> read FName write FName;
  property Address:kbmMWNullable<string> read FAddress write FAddress;
  property ZipCode:kbmMWNullable<integer> read FZipCode write FZipCode;
  property City:kbmMWNullable<string> read FCity write FCity;
  property Comments:kbmMWNullable<string> read FComments write FComments;
 end;

You will notice that there is an additional field named FID (with its property description). The idea is that we want to be able to identify a specific contact uniquely later on, without having to use the name or other information type fields (since the contents of those could change by the end user).

There are multiple ways to define a unique identifier. Some may have heard about auto incrementing fields. Some may have heard about sequencers or generators (which is sort of the same as auto incrementing fields, just in a different packing), and then some may have heard about using GUID‘s (Globally Unique IDentifiers) which often are represented as 38 character strings.

I personally prefer using GUID‘s for unique identifiers, because it allows me to import the database in another system, and even to integrate data across different database systems, without risking accidental renumbering of auto incrementing identifiers.

Performance wise GUID‘s are slightly slower and takes up additional space in a database, but that is usually only an issue to consider if you know you will have tens of millions of records in a table. If that’s not the case I recommend using GUID’s. I will show in a moment how.

But first I will talk about the other weird thing in the object… the kbmMWNullable generic type.

It allows for a field variable to contain both the value (for ‘name’, a string), and an indication about if the variable is null or not null.  In addition it also keeps track of if the value in the field has been modified or not, which can be very practical to know about  when the object is to be used to magically make the contents of the object be part of the database.

In fact, every time you have data in an object you would like to put into a database, wrap its fields with kbmMWNullable. The only exception is if your field is of type TkbmMWDateTime (which is a kbmMW TDateTime like type that also understands timezones and more), since it by itself is null and modification aware.

Back to the GUID. We want the ID property to be used as the primary identification for our contact. So let’s tell kbmMW that by adding an attribute to it.

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

We have told kbmMW to handle this property as a field named ‘id’ in a data storage (like our database) and that should be stored as the datatype ftString with the size of max 40 characters. In addition the field is to be considered the primary point of access to data in the database for the TContact type information.

We have also told kbmMW that we want the actual contents of this field to be automatically generated using the shortGUID generator which produces string GUID’s without dashes and curly braces.

Now let us create similar attributes for the remaining properties, so kbmMW knows what to do about them in relation to a database.

And finally we might also want to tell kbmMW that this TContact object is to be stored in a container (usually a table) with a specific name in the database.

As in blog post #1 we also need to register the class to kbmMW. The registration can be done any time, as long as it is before first time the object is to be used by kbmMW. Typically registration is put into the initialization section of the unit. And a couple of units, DB, kbmMWNullable, kbmMWRTTI and kbmMWORM must be added to the uses clause. Now we have defined the skeleton around what a contact is, and we have a place to put the information about a single contact. But what about multiple contacts? That’s easy.. we consider multiple contacts as being a list of contacts. In Delphi code terms using generics: TObjectList

This object list also needs to be registered with kbmMW and the Delphi generics list unit (System.Generics.Collections) must be added. The resulting unit will look like this:

unit Unit9;

interface

uses
 DB,
 System.Generics.Collections,
 kbmMWRTTI,
 kbmMWORM,
 kbmMWNullable;

type

[kbmMW_Table('name:contact')]
TContact = class
private
 FID:kbmMWNullable<string>;
 FName:kbmMWNullable<string>;
 FAddress:kbmMWNullable<string>;
 FZipCode:kbmMWNullable<integer>;
 FCity:kbmMWNullable<string>;
 FComments:kbmMWNullable<string>;
public
 [kbmMW_Field('primary:true, generator:shortGuid',ftString,40)]
 property ID:kbmMWNullable<string> read FID write FID;

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

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

 [kbmMW_Field('name:zipCode',ftInteger)]
 property ZipCode:kbmMWNullable<integer> read FZipCode write FZipCode;

 [kbmMW_Field('name:city',ftString,50)]
 property City:kbmMWNullable<string> read FCity write FCity;

 [kbmMW_Field('name:comments',ftMemo)]
 property Comments:kbmMWNullable<string> read FComments write FComments;
end;

implementation

initialization
 kbmMWRegisterKnownClasses([TContact,TObjectList<TContact>]);
end.

So we now have the understanding of a single contact and a list of contacts. That should cover our data type needs for now.

Save all.

Next we need to establish a place in the SQLite database where this data can be stored. This is fortunately very easy to do. Open up your main form, and add this new data unit (Unit9) to the uses clause of the main form’s implementation section like this:

...
var
 Form7: TForm7;

implementation

uses Unit9;
...

And a good place to tell kbmMW where to store TContact type data, could be in the form’s OnCreate event.

procedure TForm7.FormCreate(Sender: TObject);
begin
 ORM:=TkbmMWORM.Create(kbmMWSQLiteConnectionPool1);
 ORM.CreateTable(TContact);
 kbmMWServer1.AutoRegisterServices;
 kbmMWServer1.Active:=true;
end;

kbmMW’s easy to use ORM (Object Relational Manager), automatically detects if the database already contains TContact type data or not. If it does not, kbmMW prepares the database to hold such information.

If we would run the application now, a file called test.db3 is created and using a SQLite database exploration tool like kbmMWSQLiteMan (which can be downloaded for free after registering on our portal at https://portal.components4developers.com), you can see that the database has been prepared with an empty table.

Now we can start to write some REST functionality to access and manipulate the contacts data.

Open Unit8 (the kbmMW smart service).

We will add functions to return all contacts known to the system, to add a contact only providing name, to add a contact providing name, address, zipcode and city, and to delete a contact by it’s id.

The service will then look like this:

 [kbmMW_Service('name:MyREST, flags:[listed]')]
 [kbmMW_Rest('path:/MyREST')]
 TkbmMWCustomSmartService8 = class(TkbmMWCustomSmartService)
 public
   [kbmMW_Rest('method:get, path:helloworld, anonymousResult:true')]
   [kbmMW_Method]
   function HelloWorld:TMyResult;

   [kbmMW_Rest('method:get, path:contacts, anonymousResult:true')]
   function GetContacts:TObjectList<TContact>;

   [kbmMW_Rest('method:put, path:addcontact')]
   function AddContact(
     [kbmMW_Rest('value:"{$name}"')] const AName:string;
     [kbmMW_Rest('value:"{$address}"')] const AAddress:string;
     [kbmMW_Rest('value:"{$zipcode}"')] const AZipCode:string;
     [kbmMW_Rest('value:"{$city}"')] const ACity:string):string; overload;

   [kbmMW_Rest('method:get, path:"addcontact/{name}"')]
   function AddContact([kbmMW_Rest('value:"{name}"')] const AName:string):string; overload;

   [kbmMW_Rest('method:delete, path:"contact/{id}"')]
   function DeleteContact([kbmMW_Rest('value:"{id}"')] const AID:string):boolean;
 end;

You can see a number of new functions.

• GetContacts method which will return a TObjectList of TContact. We have told kbmMW that we want it to be called when people use this URL to perform a HTTP GET request: http://localhost/MyREST/contacts
  The result will be a JSON array of contact information.
• AddContact method which will take a name, address, zipcode and city and return a string (the id of the newly created contact). It will be activated the moment someone makes an HTTP PUT request on http://localhost/MyREST/addcontact
  The caller should include HTML form fields named name, address, zipcode and city. kbmMW will automatically attempt to pair the provided formfields with the arguments for the AddContacts method according to the mapping shown.
• AddContact method which will take a name only, and return a string (the id of the newly created contact). It will be called when someone makes an HTTP GET request on http://localhost/MyREST/addcontact/somename
  The somename part should be the name of the contact to add.
• DeleteContact method which will take an id, and return a boolean true/false for if the operation succeeded. It will be called when someone makes an HTTP DELETE request on http://localhost/MyREST/contact/someid
  The someid part should be the id of the contact to delete.

The implementation of these functions is very simple. Make sure that Unit7 (the main form unit) is added to the uses clause of the interface section of Unit8 (the kbmMW smart service module), and add the following code:

function TkbmMWCustomSmartService8.GetContacts:TObjectList<TContact>;
begin
 Result:=Form7.ORM.QueryList<TContact>;
end;

function TkbmMWCustomSmartService8.AddContact(const AName:string;
 const AAddress:string;
 const AZipCode:string;
 const ACity:string):string;
var
 c:TContact;
begin
 c:=TContact.Create;
 try
   c.Name:=AName;
   c.Address:=AAddress;
   c.ZipCode:=AZipCode;
   c.City:=ACity;
   Form7.ORM.Persist(c);
   Result:=c.ID;
 finally
   c.Free;
 end;
end;

function TkbmMWCustomSmartService8.AddContact(const AName:string):string;
var
 c:TContact;
begin
 c:=TContact.Create;
 try
   c.Name:=AName;
   Form7.ORM.Persist(c);
   Result:=c.ID;
 finally
   c.Free;
 end; 
end;

function TkbmMWCustomSmartService8.DeleteContact(const AID:string):boolean;
begin
 Form7.ORM.Delete([AID]);
end;

That is really all that is needed to enable returning a list of contacts, adding new contacts and deleting a contact. kbmMW’s ORM makes all the work for us automatically.

Basically a REST enabled contact manager in only 30 lines of actual code and a definition of a TContact class.

This obviously can be expanded on in many ways, but I will leave that up to the reader.

Feel free to share, retweet, repost, comment and like this post! 🙂

REST easy with kbmMW #1

REST easy with kbmMW #3 – SSL

REST easy with kbmMW #4 – Access management

REST easy with kbmMW #5 – Logging