Many of these new features will be utilized by next version of kbmMW, which supports extensive SQL rewriting for major databases. Hence you will be able to write most SQL in the standard kbmMemTable SQL way which is SQL 92 compatible, and kbmMW will optionally automatically rewrite it to match the actual database, making supporting different databases a breeze. More about that in another blogpost.

</TEASER>

Examples:

SELECT fld1, fld2,
CASE
WHEN fld2<100 THEN ‘LOW’
WHEN fld2>=100 AND fld2<200 THEN ‘MEDIUM’
ELSE ‘HIGH’
END AS FLD3 INTO table7
FROM Table1

SELECT fld1, fld2,
CASE fld2
WHEN 10 THEN 99999
WHEN 20 THEN 22222
ELSE -1
END
FROM Table1

I have in the former two “REST easy with kbmMW” articles shown, how to make a REST server with kbmMW, and how to use that REST server to easily return and store data from/to a database all in less than 30 lines of real code.

This blog post will center around how to ensure that communication with the server stays protected using SSL (Secure Socket Layer). In other words, how to make the REST server talk HTTPS rather than HTTP.

There are multiple ways to secure a kbmMW based application server with SSL, but I will focus on one simple way to do it using OpenSSL.

First we should create a certificate we can use. SSL certificates can be purchased from various places where they sell official certificates, or you can create one that is self signed. A self signed certificate is generally as secure as anything else, but it is not automatically trusted by other servers, which may flag your certificate as unsafe.

For inhouse use however, a self signed certificate is usually fine.

There are many places on the internet explaining the procedure of how to create SSL certificates using OpenSSL. You can click here for one of them.

After you have gotten access to either a bought or a homemade certificate, we can continue integrating it with the REST server.

Add a TIdServerIOHandlerSSLOpenSSL component to the main form (Unit7).

You will need to set it’s SSLOptions properties like this:

SSLOptions.Mode must be sslmServer

Of the supported SSLOptions.SSLVersions I will suggest enabling only sslvTLSv1_2

Leave the remaining properties as is at the moment.

Now double click the OnGetPassword event handler of the IdServerIOHandlerSSLOpenSSL1 component to write some code in an event handler.

The code going in the event should simply return the password you used when you created the private part of the certificate. It is required by OpenSSL to have access to this, to be able to use your private key.

Despite the above example, I would suggest you, not to hardcode the password inside your application, but rather read it from an external configuration file, of security reasons, in case your REST server executable got leaked elsewhere.

But for the current sample, with a homemade sample certificate, we can do with the hardcoded password.

Next we need to tell the kbmMW server side transport to let the OpenSSL code handle the main communication of our data. One part of that is to write an event handler for the OnConnect event of the kbmMWTCPIPIndyServerTransport1 component.

Make sure that the files YourCertificateFile.cer and YourPrivateKeyFile.key is available to the REST executable when it runs, but also make sure that they are not accessible for download for anyone else. It’s of high importance that those files (along with your private key password) is kept a secret for anybody else.

As you may notice, we change the port number for the first binding from 80 to 443, since we want to support the standard HTTPS port. You may also notice that it is possible to provide a Root certificate file. The Root certificate file typically contains a chain of public certificates that can be used by OpenSSL and browsers to verify that your own certificate is a valid and trusted certificate generated by the entity from which you have the root certificate.

Self signed certificates usually do not need any root certificate files.

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.

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.

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 TkbmMWDataTime (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.

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<TContact>

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:

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.

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.

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:

If you have never logged in on our download portal before, you will need to register yourself first. We do not verify the information you provide for name etc, but please, as a courtesy to us, type in your real name and accessible email address. as a minimum.

When you have logged in, you will have direct access to all the free products we currently offer, and in all the versions we have made available.

kbmMW CodeGear Edition do not include source code, so you will have to choose a version matching the development environment you have. CodeGear Edition only supports Delphi.

Further kbmMW CodeGear Edition is updated less regular than our paid versions of kbmMemTable and kbmMW. Latest version released of CodeGear Edition will only match latest version of Delphi. Thus the older Delphi version you will want to download for, the older version of kbmMW CodeGear Edition you will need to download.

What do you get with kbmMW CodeGear Edition?

A complete application server framework

Standard TCP/IP based communication channel

Remote dataset support with resolving features

Server side support for FireDAC, DBExpress, IBExpress and SQLite

Advanced logging framework

Native XML support

Native JSON support

ISAPI communication support

Support for a subset of compression libraries

Support for a subset of hashing methods

Advanced DateTime with Timezone support

Server and client side dataset caching

Advanced actor, role, resource, condition based authorization manager

kbmMemTable with without source, but with indexing, filtering, ordering, SQL and more support

What do you not get with kbmMW CodeGear Edition?

Source

Latest version

No limitations in server and client side caching

No limitations in number of concurrent server service instances

35+ more database adapters

YAML, BSON, MessagePack support

Loadbalancing and failover

Messaging

High quality encryption functionality including AES and many other algorithms

High quality hashing functionality including SHA256/512 and many other algorithms

The kbmMW Scheduler schedules jobs. The jobs may be one time or recurrent jobs.

Some jobs may not be critical related to the exactness of their executing time on the mSec (like generating a report, doing a backup, importing some data and other such housekeeping and maintenance), while others may require as accurate as possible execution, for example sampling data, screen grabbing or stuff like that.

The scheduler can do both.

When defining a recurrent job, you typically also tells it with which interval it should run. The scheduler contains multiple fluent expressions to define the interval, like EveryMinute, EveryHour, EveryDay, EverySecond, EveryMSecond etc. which all takes floating point values.

Hence you can easily schedule (and activate) a job to run every 1.5 hour, by writing this:

Scheduler.Schedule(somejob).EveryHour(1.5).Activate;

kbmMW default assumes that jobs scheduled with an interval less than 2 seconds should be considered “precise” jobs, while others should be considered “relaxed” jobs.

A “Relaxed” job is a job, that is being assigned a thread from a thread pool, when it’s about to execute. If there are no threads available, the job is put on hold until a thread is free.

Thus Relaxed jobs often share one or a few threads to run, and thus are very gentle to the system resources. But the downside is that they may not execute completely on time. Their execution may be delayed a few mSecs to even minutes or hours, depending on the available number of threads in the relaxed thread pool, and the load of the scheduled relaxed jobs. Typically any delays will be minimal, but there is no guarantee.

A “Precise” job is a job that from time of defining the job, will have its own thread assigned to it, and thus will run independently of all other jobs.

As mentioned kbmMW automatically decides, upon scheduling time, which mode the job is to take, relaxed or precise. But you can override this manually by specifically stating either Relaxed or Precise as part of the fluent expression. E.g.

// Schedule a job in its own thread, running every 1.5 hour.
Scheduler.Schedule(somejob).EveryHour(1.5).Precise.Activate;
// Schedule a job to be run every 100 msecs, from a shared thread from the relaxed threadpool.
Scheduler.Schedule(somejob).EveryMSecond(100).Relaxed.Activate;

In a previous Scheduler Tidbit blog post, I mentioned the new CronTab scheduling style. Since UNIX cron only has an interval resolution of 1 minute, scheduling such a job using kbmMW’s scheduler also will predefine the use of a one minute interval, and thus all cron jobs will default be scheduled as relaxed jobs.

However you can choose a different interval resolution by using any of the Every…. fluent expressions before the Cron expression. Eg.

// This will schedule a job to be run every minute from 5:00 to 5:59 every day.
// The job will default be considered a relaxed scheduled job.
Scheduler.Schedule(somejob).Cron('* 5 * * *').Activate;

// This will schedule a job to be run every second from 5:00 to 5:59 every day.
// The job will default be considered a precise scheduled job.
Scheduler.Schedule(somejob).EverySecond(1).Cron('* 5 * * *').Activate;

If you state the Every… expression after the Cron expression, it will add to the default 1 minute cron schedule. Eg.

// This will schedule a job to be run every 1.5 minute from 5:00 to 5:59 every day.
Scheduler.Schedule(somejob).Cron('* 5 * * *').EverySecond(30).Activate;