mORMot Open Source

igors233

Member

Registered: 2012-09-10

Posts: 241

Working with legacy DB and proper model

Hi all,

I'm writing server/client app for which I'll use mORMot Rest implementation, one client will be done in Delphi and another in SMS.
Server part works with existing legacy DB that's used extensively from some other third party application, so I have to treat DB as read only.
I need just help on how to get it started, pointing me to the right direction would be enough.

I've created a simple model, here is an example:

  TSQLItem = class(TSQLRecord)
  private
    FItemCode: string;
    FItemDescription: string;
    FItemUnit: string;
  published
    property ItemCode: string read FItemCode write FItemCode;
    property ItemDescription: string read FItemDescription write FItemDescription;
    property ItemUnit: string read FItemUnit write FItemUnit;
  end;

Since query needed to populate this record isn't a simple SELECT I need to write custom SQL to get records from existing db, question is how to do it best at the client and cerver side? Should I manually create instances of TSQLItem (one for each returned record) and then add it to
TSQLRestServer instance? Second question is what is the appropriate TSQLRestServer descendant to use?

Thanks,
Igor

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Working with legacy DB and proper model

Is the primary key of your legacy DB an integer?
If yes, you "may" use the ORM, otherwise it is not easily possible.

But for legacy DB, I would define interface-based services, and DTOs.
I would by-pass the whole ORM, and use only interface-based services.
Perhaps not TSQLRecord, just TPersistent/TSynPersistent, or record/dynamic arrays as DTOs.

Then use our SynDB classes to access the DB.
It is able to create JSON directly from the DB.
So in some cases, you may even use RawJSON parameter in your interface-based services, and return the JSON content directly to the client.
Then clients in Delphi or SMS would be able to parse this JSON content.

See http://synopse.info/files/html/Synopse% … ml#TITL_66
and http://synopse.info/files/html/Synopse% … l#TITL_106

igors233

Member

Registered: 2012-09-10

Posts: 241

Re: Working with legacy DB and proper model

> Is the primary key of your legacy DB an integer?
> If yes, you "may" use the ORM, otherwise it is not easily possible.

I think that less than 5% of tables uses INTEGER, it's usually VARCHAR as ItemCode field in my example.

> But for legacy DB, I would define interface-based services, and DTOs.
> I would by-pass the whole ORM, and use only interface-based services.

Something similar to Samples 27 and 29?

> Perhaps not TSQLRecord, just TPersistent/TSynPersistent, or record/dynamic arrays as DTOs.

Thanks for the idea, I've already used/worked with json serialization for records in SynCommons, haven't used TSynPersistent earlier.

Should I return raw JSON to client and then use JSONToObject to load it's content into TSynPersistent records (with proper hierarchy, like parent/child table structure)?

> It is able to create JSON directly from the DB.

With the FetchAllAsJSON right?

> Then clients in Delphi or SMS would be able to parse this JSON content.

Do you know if I would be able to use TSynPersistent classes in SMS or record oriented approach is better solution, or best/esiest to do like in samples and uses TSQLRecord.

Thanks,
Igor

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Working with legacy DB and proper model

I think that less than 5% of tables uses INTEGER, it's usually VARCHAR as ItemCode field in my example.

So you won't be able to use directly the ORM.

Something similar to Samples 27 and 29?

Yes

Should I return raw JSON to client

It is a good idea, then use either JsonToObject() or TDocVariant/TJsonVariant to access the content.

TSynPersistent classes in SMS or record oriented approach is better solution

I did not make extensive tests with TSynPersistent, so I guess only record + dynamic arrays are handled by SMS now.
This is why RawJSON + manual marshalling may also be an option.

willo

Member

From: Cape Town, South Africa

Registered: 2014-11-15

Posts: 67

Website

Re: Working with legacy DB and proper model

I'm having a similar problem. Our legacy database also uses varchar for primary keys, so no ORM.
I've defined DTO's (inherited from TSynPersistent) and CQRS interfaces to operate on these DTO's.

So basically I have this:

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

  TLegacyStockQuery = class(TInterfacedObject, ILegacyStockQuery)
  public
    constructor Create( aDbConnection : TSQLDBConnectionProperties );
    function GetSupplier(const aID: TRevelightID; out Supplier: TLegacySupplier): TCQRSResult;
  end;

function TLegacyStockQuery.GetSupplier(const aID: TRevelightID; out Supplier: TLegacySupplier): TCQRSResult;
var
  Res : ISQLDBRows;
begin
  Result := cqrsNotFound;
  Res := fDbConnection.Execute( 'select SUPPLIER_ID, SUPPLIER_NAME from SUPPLIERS where SUPPLIER_ID=? ', [aID] );
  if Res.Step then begin
    Result := cqrsSuccess;
    Supplier.RevelightID := Res['SUPPLIER_ID'];
    Supplier.Name := Res['SUPPLIER_NAME'];
  end;
end;

The issue I'm having is this: How do I get hold of the fDbConnection (TSQLDBConnectionProperties). I'd prefer to specify it when I create the specific server/service, and not to access a global or singleton.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Working with legacy DB and proper model

If you define the service instance as sicShared, you can create it and supply the single instance to ServiceDefine().

But for other modes (like sicClient), I've just added the TSQLRestServer.OnServiceCreateInstance callback.
See http://synopse.info/fossil/info/5eace8e75f

It may help in your case, since you may write something like that:

procedure TMyClass.OnCreateInstance(
  Sender: TServiceFactoryServer; Instance: TInterfacedObject);
begin
  if Sender.ImplementationClass=TLegacyStockQuery then
    TLegacyStockQuery(Instance).fDbConnection := fDbConnection;
end;

Of course, using TInjectableObjectClass is recommended for interface IoC.
But this new callback may help in most simple cases like yours.

willo

Member

From: Cape Town, South Africa

Registered: 2014-11-15

Posts: 67

Website

Re: Working with legacy DB and proper model

Thanks Ab.

Just a question on reentrancy:
Would my method above survive two simultaneous requests in sicShared mode? Id not, what do I have to do to protect it?

On that note, if my implementation is a bit more complex, how do I insure that my transaction doesn't get nabbed by some other request? (Since a single fDbConnection is shared among several possible requests.)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Working with legacy DB and proper model

For transactions, you should use a BATCH with auto-transaction.
See http://synopse.info/files/html/Synopse% … ml#TITL_28

About sicShared thread safety, it is up to you to protect the multi-thread execution.
By default, code is not protected (locked), for best performance.
You have several built-in options at service level, to tune the multi-threading execution of the methods.
See http://synopse.info/files/html/Synopse% … ml#TITL_72