Connecting to legacy databases and publishing a RESTful interface to it

Most systems, especially in the DDD area, needs to integrate to a legacy system. In our case our we had to communicate to a Firebird 1.5 database.

The first step was to define our Data Transfer Objects:

type
  TLegacyID = type RAWUTF8;

  TLegacyAccount = class(TSynPersistent)
  private
    fLegacyID: TLegacyID;
    fDateModified: TDateTime;
    fUserCreated: RAWUTF8;
    fDateCreated: TDateTime;
    fName: RAWUTF8;
    fisActive: Boolean;
  public
    constructor Create; overload; override;
    constructor Create( aID : TLegacyID; aName : RAWUTF8; aIsActive : Boolean;
      aDateCreated, aDateModified : TDateTime; aUserCreated : RAWUTF8 ); overload;
  published
    property ID : TLegacyID read fRevelightID write fRevelightID;
    property Name : RAWUTF8 read fName write fName;
    property isActive : Boolean read fisActive write fisActive;
    property DateCreated : TDateTime read fDateCreated write fDateCreated;
    property DateModified : TDateTime read fDateModified write fDateModified;
    property UserCreated : RAWUTF8 read fUserCreated write fUserCreated;
  end;

  TLegacySupplier = class(TLegacyAccount)
  end;

Here we declare a unique type to identify our legacy IDs (strings). We also do a basic map of our data layout, with a customized constructor for ease of creation. One can add other methods later to handle copies and assignments.

The next step was to define our service:

type
  ILegacyStockQuery = interface(IInvokable)
    ['{2BDC9F78-B9C2-4621-A557-F87F02AC0581}']
    function GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
  end;

we’ll publish a service as LegacyStockQuery with a single method GetSupplier. This method will return a JSON encoded representation of our TLegacySupplier, ready to be consumed by a client.

To implement it:

type
  TLegacyStockQuery = class(TInterfacedObject, ILegacyStockQuery)
  private
    fDbConnection : TSQLDBConnectionProperties;
  public
    constructor Create( const aProps: TSQLDBConnectionProperties ); overload;
    function GetSupplier(const aID: TRevelightID; out Supplier: TLegacySupplier): TCQRSResult;

    property DbConnection : TSQLDBConnectionProperties read fDbConnection write fDbConnection;
  end;

We keep a copy of our database connection properties local to our instance to ensure thread safety.

{ TLegacyStockQuery }

constructor TLegacyStockQuery.Create(const aProps: TSQLDBConnectionProperties);
begin
  fDbConnection := aProps;
  inherited Create;
end;

function TLegacyStockQuery.GetSupplier(const aID: TLegacyID; out Supplier: TLegacySupplier): TCQRSResult;
var
  Res, Address : ISQLDBRows;
begin
  Result := cqrsNotFound;
  Res := fDbConnection.Execute( 'select * from SUPPLIERS where SUPPLIER_ID=? ', [aID] );
  if Res.Step then begin
    Result := cqrsSuccess;
    Supplier.ID := Res['SUPPLIER_ID'];
    Supplier.Name := Res['SUPPLIER_NAME'];
    Supplier.isActive := Res['ACTIVE_FLAG'] = 'Y';

    Supplier.DateCreated := Res['DATE_CREATED'];
    Supplier.DateModified := Res['DATE_MODIFIED'];
    Supplier.UserCreated := Res['USER_CREATED'];
  end;
end;

Execute the query against the legacy database and populate the DTO. Using the ISQLDBRows interface means less object maintenance and cleaner code.

To kick the whole thing off we have:

procedure StartServer( aDbURI : RawURF8 );
var
  aDbConnection : TSQLDBConnectionProperties;
  aStockServer  : TSQLRestServerFullMemory;
  aHTTPServer   : TSQLHttpServer;
begin
  aDbConnection := TSQLDBZEOSConnectionProperties.Create( aDbURI, '', '', '' );
  aStockServer  := TSQLRestServerFullMemory.Create([]);
  try
    aStockServer.ServiceDefine( TLegacyStockQuery.Create( aDbConnection ), [ILegacyStockQuery]);
    aHTTPServer := TSQLHttpServer.Create( DEFAULT_HTTP_PORT, [aStockServer] );
    try
      aHttpServer.AccessControlAllowOrigin := '*'; // allow cross-site AJAX queries
      writeln('Background server is running.'#10);
      writeln('Cross-Platform wrappers are available at ',
          DEFAULT_HTTP_PORT ,'/', DEFAULT_SERVER_ROOT );
      write('Press [Enter] to close the server.');
      readln;
    finally
      aHTTPServer.Free;
    end;
  finally
    aStockServer.Free;
  end;
end; 

It would be better to use dependency injection here, but we’ll get in to that later.

When invoking it, we used a Zeos DB URI, like this one:

zdbc:firebird-1.5://tst-db-svr-1/srv/db/EXAMPLE.FDB?username=SYSDBA;password=masterkey

Tips!

Remember to register your Object array types with

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(<ArrayType>),<ObjType>);

e.g.

TJSONSerializer.RegisterObjArrayForJSON(TypeInfo(TLegacySupplierObjArray),TLegacySupplier);

ORM – Modeling relationships

Being able to populate data in a simple data store is great, but that is usually not good enough for any real world application. Most real world models would have some relationship between classes.

Let’s take our Note example. If you have no idea what example I’m talking about, maybe start here first.

Let’s say we needed to associate our notes to a specific case. The first step would be to modify the NoteORM unit to look like this:

unit NoteORM;
interface

uses Classes, SynCommons, mORMot;

type
  TNoteCase = class(TSQLRecord)
  private
    FDescription: RawUTF8;
  published
    property Description: RawUTF8 read FDescription write FDescription;
  end;

  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
    FTitle: RawUTF8;
    FNoteCase: TNoteCase;
  published
    property NoteCase: TNoteCase read FNoteCase write FNoteCase;
    property Body: RawUTF8 read FBody write FBody;
    property Title: RawUTF8 index 80 read FTitle write FTitle;
  end;

implementation

end.

Here we’ve added the TNoteCase class, and added the NoteCase property to TNote. NoteCase is of type TNoteCase.

Next, we add the new TNoteCase class to our model:

RestServer := TSQLRestServerDB.CreateWithOwnModel( [NoteORM.TNote, NoteORM.TNoteCase], 'Test.DB' );

And now we can generate some data:

procedure TestIt(rest:TSQLRest);
var
  aCase : NoteORM.TNoteCase;
  aNote : NoteORM.TNote;
begin
  aCase := NoteORM.TNoteCase.Create;
  try
    aCase.Description := 'Case 1';
    rest.Add( aCase, true );
    aNote := NoteORM.TNote.Create;
    try
      aNote.Title := 'Note 1';
      aNote.Body  := 'Note 1 body. Lots of other stuff too.';
      aNote.NoteCase := aCase.AsTSQLRecord;
      rest.Add( aNote, true );

      aNote.Title := 'Note 2';
      aNote.Body  := 'Note 2 body. Lots of other stuff too. Some more things here.';
      rest.Add( aNote, true );
    finally
      aNote.Free;
    end;
  finally
    aCase.Free;
  end;
end;

The most important bit to notice is:

      aNote.NoteCase := aCase.AsTSQLRecord;

This is because mORMot stores the referential key, and not the actual instance. That means that under normal circumstances you can not access aNote.NoteCase.Description, as this will generate an Access Violation.

Once the records have been committed to the storage engine, however, there are other ways of dealing with these classes.

One way is by using the CreateJoined constructor:

procedure TestIt(rest:TSQLRest);
var
  aNote : NoteORM.TNote;
begin
  aNote := NoteORM.TNote.CreateJoined( rest, 4);
  try
    writeln( 'Case: ', aNote.NoteCase.Description );
    writeln( 'Title: ', aNote.Title );
    writeln( 'Body: ', aNote.Body );
  finally
    aNote.Free;
  end;
end;

This will auto load and populate and manage all the contained instances. This method can be slow, especially if the class graph is big.

The other option is to use lazy loading, where classes are created and managed as and when they’re required:

procedure TestIt(rest:TSQLRest);
var
  aCase : NoteORM.TNoteCase;
  aNote : NoteORM.TNote;
begin
  aNote := NoteORM.TNote.Create( rest, 4);
  try
    aCase := NoteORM.TNoteCase.Create( rest, aNote.NoteCase );
    try
      writeln( 'Case: ', aCase.Description );
      writeln( 'Title: ', aNote.Title );
      writeln( 'Body: ', aNote.Body );
    finally
      aCase.Free;
    end;
  finally
    aNote.Free;
  end;
end;

Investigating the ORM – retrieving data

In a previous post we illustrated how to use the ORM to populate a simple data store. Now, we’ll look how to retrieve the data.

If you have the ID of the record / instance you’re after, you can simply call:

  Note := TNote.Create(Client,ID);

for mORMot to fetch and populate Note.

Being an implementation of the Active Record Pattern, basic operations, such as Update, Retrieve and Delete are part of the basic implementation. TSQLRecord also provides list retrieval methods and lazy loading to all it’s descendants.

For example:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.CreateAndFillPrepare( rest, 'Body LIKE ?',['Sample 2%']);
  try
    while Note.FillOne do
    begin
      writeln( 'Note ID: ', Note.ID );
      writeln( 'Note Title: ', Note.Title );
      writeln( 'Note Body: ', Note.Body );
    end;
  finally
    Note.Free;
  end;
end;

will produce:

Note ID: 3
Note Title: This is the title!
Note Body: Sample 2. This is a sample note.

Investigating the ORM – persisting data

The ORM (Object Relational Mapper) that ships with mORMot implements the Active Record pattern. All objects that you want to persist must inherit from TSQLRecord, which handles a lot of the marshaling and facilitates querying and verification.

Let’s look at a simple sample:
Let’s say you have an application server that stores notes.

The very first thing we’ll do is create a unit to define our ORM classes. Let’s call this one NoteORM.pas.

unit NoteORM.pas;
interface

uses Classes, SynCommons, mORMot;

type
  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
  end;

implementation

end.

That’s pretty simple! Great, how do we use it?

To illustrate the usage, here’s a basic test application framework:

program TestNotes;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  {$I SynDprUses.inc}
  SysUtils,
  SynSQLite3Static,
  mORMot,
  mORMotSQLite3,
  NoteORM in 'NoteORM.pas';


procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 1. This is a sample note.';
  rest.Add( Note, true ); 
end;

var RestServer: TSQLRestServerDB;
    RestClient: TSQLRestClientURI;
begin
  RestServer := TSQLRestServerDB.CreateWithOwnModel( 
                    [NoteORM.TNote], 'Test.DB' );
  RestClient := TSQLRestClientURIDll.Create(
                    TSQLModel.Create(RestServer.Model),
                    @URIRequest);
  try
    // initialize URIRequest() with the aStatic database
    RestServer.ExportServer;
    USEFASTMM4ALLOC := true;
    RestServer.CreateMissingTables;
    RestClient.Model.Owner := RestClient;
    TestIt(RestClient);
  finally
    RestClient.Free;
    RestServer.Free;
  end;
end.

Running the application, a new SQLite database will be created, called Test.DB. In it there will be a table called Note, with a two fields: ID and Body. A single row will exist:

ID          Body
----------  --------------------------------
1           Sample 1. This is a sample note.

Let’s extend our Note class to add a title. We’d like the title to be restricted to 80 characters.

We simply modify NoteORM.pas to look like this:

  TNote = class(TSQLRecord)
  private
    FBody: RawUTF8;
    FTitle: RawUTF8;
  published
    property Body: RawUTF8 read FBody write FBody;
    property Title: RawUTF8 index 80 read FTitle write FTitle;
  end;

Notice the “index 80” part: This tells the ORM that we would like to restrict the width of the field in the database to 80 characters.

Running the application now, will yield the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.

Great! mORMot automatically added the Title column, but we forgot to add test data. Let’s do that now:

procedure TestIt(rest:TSQLRest);
var
  Note : NoteORM.TNote;
begin
  Note := TNote.Create;
  Note.Body := 'Sample 2. This is a sample note.';
  Note.Title := 'This is the title!';
  rest.Add( Note, true );
end;

Run it and it produces the following in our database:

ID          Body                              Title
----------  --------------------------------  ----------
1           Sample 1. This is a sample note.
2           Sample 1. This is a sample note.
3           Sample 2. This is a sample note.  This is the title!

That was quite easy! Next, we see how we can retrieve data using the ORM.

Some mORMot basics

mORMot’s architecture is based on a modular multi-tier system.

Puzzles

Everything you do in mORMot will follow this pattern. The server component can be embedded in the same physical application, or it can be a proxy, passing the requests and queries along to another instance, which will work with the same pattern again.

Getting started

As mORMot is actively being developed, the best way of getting hold of the library is to grab the latest, bleeding edge, code. We do that by:

  1. Getting and installing git
  2. Grab a copy of the repository
  3. Configuring Delphi to use the library

Step 1: Get git!

This is easy enough. Visit http://msysgit.github.io/ and download and install the appropriate package.

Step 2: Get the code!

Almost as easy! Open a command prompt, change to the drive and directory where you want the library to live and type:

git clone https://github.com/synopse/mORMot.git

I suggest something like

E:
git clone https://github.com/synopse/mORMot.git mORMot

This will create and download a copy of the repository to E:\mORMot\
Next we need to grab a copy of the SQLite 3 static .obj files. This will allow our mORMot based application to embed the SQLite engine in our application.

Grab http://synopse.info/files/sqlite3obj.7z and extract to yourE:\mORMot\ folder. If you plan to develop for 64-bit targets grab http://synopse.info/files/SQLite3-64.7z and place sqlite3-64.dll in your project’s build directory or in your Windows directory.

Step 3: Configure the environment!

Add the following to your Delphi Library Path:

  • E:\mORMot;
  • E:\mORMot\SQLite3;
  • E:\mORMot\SynDBDataset;
  • E:\mORMot\SQLite3\DDD;
  • E:\mORMot\SQLite3\DDD\dom;
  • E:\mORMot\SQLite3\DDD\infra;

Step 4: Test the environment!

Open E:\mORMot\SQLite3\TestSQL3.dpr and run it. Make sure all the tests pass.

You’re done!