User Defined Field

john_habil · 2014-04-25 17:58:14

Hi,

First of all, sorry for my English. It's not my native language, since I'm from Brazil.

Now, my question..

We have a ERP software currently developed using Delphi XE + Datasnap.

The mORMot framework has become our first choice to rewrite this ERP, leaving the slow Datasnap in the past..

But some features are incredible difficult to re-think in this new world of ORM..

The hardest one is User Defined Fields. Each one of our customers could create new fields in any table, at any time.
I can't imagine how to implement this in the mORMot framework.. It's a really important feature in our software.

Any help will be welcome

Thanks, John

ab · 2014-04-26 07:57:16

Thanks for your interest!
And no problem with your English: I'm a little Frenchy myself, so no native either!

Most of those questions are in part answered in the SAD 1.18 pdf - but it is difficult to put all pieces together, and find definitive (clear) answers to such questions.
So it does make sense to ask it in this forum!

First of all, I suppose you may have to find out more about SOA and server-side logic, with rich clients.
It sounds like if your ERP rewrite sounds like classic client-side logic.
Today, SOA is a very attractive design, especially if you want to easily add new means to connect (e.g. AJAX or mobile clients, in addition to Desktop clients).
I mean, mORMot will give its full power if your client do not use the Client-Server ORM directly, but let it stay on the server side (with the possibility to host the DB in a separate server, then using the client-server ORM), and access the logic from clients via services.

So I see several implementation patterns:

1. Use a SOA design.
Adding User defined fields could therefore be very easy, if you use SOA and interface-based services.
With the proper DTOs containing arrays of values, it will be easy to consume any non fixed data structure from client side.
It will use JSON for transmission, so will be perfectly AJAX friendly.

2. Use the client-server ORM, but with a variant published property storing a TDocVariant instance.
This sounds much like a NoSQL ODM (Object Document Mapping), with a non fixed schema layout, than a standard SQL ORM - pretty close to MongoDB.
Take a look at http://blog.synopse.info/post/2014/04/1 … principles and read the "A4 - From SQL to ORM" slides.
(BTW, other slides are worth reading, to find out most of the mORMot concepts, far away from the DataSnap limited architecture)
It will use JSON for storage (the variant published property will create a TEXT column in the DB storing the document as JSON) and transmission, so will be perfectly AJAX friendly.

3. Use the client-server ORM, but with a dynamic array of variant (TVariantDynArray) published property.
Then you can store the user defined fields layout somewhere in your business logic, and will be able to access the data with the dynamic array index (e.g. [0] for the first user defined column, [1] for the 2nd, and so on...).
Thanks to our optimized binary serialization, the variants will be stored as binary BLOB in the database.
This sounds much like a NoSQL ODM (Object Document Mapping), with fixed schema layout, than a standard SQL ORM - pretty close to column-based NoSQL engines.
It will use BLOB for storage, and Base64 binary for transmission - so you would need to use only Delphi clients.
Perhaps this could be a good solution on the server side, in conjunction with proposal 1 (SOA design).

4. More complex low-level mapping with the database.
That is, you use the client-server ORM, but creating some "virtual" published fields on the fly.
Advantage is that the ORM will be able to add real columns in the database (ALTER TABLE ... ADD COLUMN ...).
You should override the class procedure TSQLMyRecord.InternalRegisterCustomProperties() of the record, then create the fields to the TSQLRecordProperties instance on the fly.
You may probably have to inherit from the TSQLModel class, to add the ability to store a centralized dictionnary of user-defined fields for all tables.
This is feasible, from the code point of view, but not so direct yet.

john_habil · 2014-04-28 11:59:39

Thanks Arnaud,

One of the best things about mORMot is this forum.. A strong community is very important for any open source project.

I believe that we can go with suggestion 3, but probably 4 is the best for our needs, just will give a lot more work.

And again, many thanks, I appreciate you give such a fast and great response.

John.

john_habil · 2014-05-14 14:36:09

Hi..

So, this week I'm finally get the time to try your suggestions..

I get the 04 - HTTP Client-Server sample and just add this code:

    class procedure TSQLSampleRecord.InternalRegisterCustomProperties( Props : TSQLRecordProperties );
    var
        P : TSQLPropInfo;
    begin
      inherited;
      P := TSQLPropInfo.Create( 'TestInteger', sftInteger, [ ], $80000000, Props.Fields.Count );
      Props.Fields.Add( Self, P );
    end;

No error.. just fine, but when I click in Client/unit1.pas/AddButton1 I just get an "Abstract Error"
in the mORMot.pas at the "procedure TSQLRecord.GetJSONValues(W: TJSONSerializer)" in this line: Fields.List GetJSONValues(Self,W)
I think it calls the wrong TSQLPropInfo GetJSONValues.. but I don't know why.

What I miss?

Thanks for any hints..

John.

ab · 2014-05-15 15:42:25

You are creating an abstract TSQLPropInfo instance here!
You should not use this TSQLPropInfo class directly, but one of its children.

You can try TSQLPropInfoCustom, TSQLPropInfoRecordRTTI or TSQLPropInfoRecordFixedSize.
But those classes are not fully tested yet, since we did not need to use it on production here...
So you have been warned that their may be some surprises here...

mORMot Open Source

#1 2014-04-25 17:58:14

User Defined Field

#2 2014-04-26 07:57:16

Re: User Defined Field

#3 2014-04-28 11:59:39

Re: User Defined Field

#4 2014-05-14 14:36:09

Re: User Defined Field

#5 2014-05-15 15:42:25

Re: User Defined Field

Board footer