mORMot Open Source

Marmot76it

Member

Registered: 2012-07-03

Posts: 4

Serializing to JSON and storing on a text field: is it a bad idea?

Hi everyone,
I'm doing some experiments with the framework and I would like to get an opinion about an idea I've came up with.
I'm going to code a project. The DB side is not very demanding, to be honest, but it's a good way to start. Mainly I've got two tables with a 1-to-many relationship between them, let's call them "users" and "logs".
Now, while "users" is a very basic kind of table, "logs" can contain a lot of different kind of things. I mean every single record is still a log, but every single record can store differnt kind of things.
Particularly, the stored things can change in time, so new type of logs will eventually be created over time, and myabe older ones will be dropped, who knows.
I've thought about 2 different approaches.
The first one is perhaps more "mORMottian", i.e.

1) define a class TSQLUser(TSQLRecord)
2) define a class TSQLLog(TSQLRecord)
3) define a TSQLUser property in the TSQLLog class (for the 1-to-many relationship)
4) define some kind of "LogType" property in TSQLLog class (to identify wich kind of log is stored in the TSQLLog property and eventually querying for specific kind of logs)
5) define various TSQLLog derived classes, like TSQLTemperatureLog(TSQLLog), TSQLSpeedLog(TSQLLog) and so on, to store the specific kind of logs (so every kind of log will have its table)

I think that this solution should be correct, but I also thought about a solution more similar to what I would have done some week ago, while facing the same situation, that is:

1) define a class TSQLUser(TSQLRecord)
2) define a class TSQLLog(TSQLRecord)
3) define a TSQLUser property in the TSQLLog class (for the 1-to-many relationship)
4) define a textual LogData property in the TSQLLog class and use it to store a serialized (JSON, YAML, or whatever) representation of the actual log

What's your opinion about that? Well, this is more a general DB oriented question, maybe you will be not very interested in it.
So, here is a more framework oriented question.
I see that JSON serialization is widely used by mORMot, and while thinking about the 2nd solution I though that I would have used the GetJSONValues on a TSQLRecord derived class to get a RawUTF8 representation of my data (I've tryed it out and it worked).
Is there any complemetary function? I mean, something to throw a RawUTF8 JSON representation to an onject and having it deserilized into its properties?

Thanks, every kind of suggestion will be appreciated!

Last edited by Marmot76it (2012-07-05 14:16:17)

lele9

Member

Registered: 2011-10-28

Posts: 170

Re: Serializing to JSON and storing on a text field: is it a bad idea?

hi Marmot76it,
i think either solution its ok. what is the best solution depend how you would like to embrace this framework.
if you are interested just at DB part you can use the secondo solution, but if you would really think "mormot"tian you should prefer first solution.
Emanuele.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,609

Website

Re: Serializing to JSON and storing on a text field: is it a bad idea?

In fact, mORMot has a specific kind of field which allows to point to several kind of tables.

This is the TRecordReference type.

  /// a reference to another record in any table in the database Model
  // - stored as an 32 bits unsigned integer (i.e. a pointer=TObject)
  // - type cast any value of TRecordReference with the RecordRef object below
  // for easy access to its content
  // - use TSQLRest.Retrieve(Reference) to get a record value
  // - don't change associated TSQLModel tables order, since TRecordReference
  // depends on it to store the Table type in its highest bits
  TRecordReference = type PtrUInt;

You have even some dedicated methods - like TSQLRest.Retrieve(Reference) - to retrieve a particular class instance according to the value of this field, or TSQLRecord.RecordReference(Model: TSQLModel): TRecordReference to create a record reference from a class instance.

The framework will create an index for this property, so you are able to handle very fast search on it.

So for your purpose, defining a TRecordReference field will join your TWO options into a better one - perfectly "mORMotable":
- Create as many dedicated class as you need (even using inheritance, if you have e.g. some common fields like the timestamp or a comment);
- Have another class containing the logs, with a RecordReference field pointing to any of the first log items classes.

Such a layout allows using indexes, minimize code base, and work at the log level.
It is IMHO much better than serializing JSON into a text field, which should be only done if nothing else is possible.

Up to now, there is no "auto-JOIN" feature, like with the regular TSQLRecord properties.
But you can easily create you own request, on need.

For further idea, search the documentation for TRecordReference and its RecordRef wrapper object.
And see the "Main Demo" code: the "Audit Trail" of this demo program uses exactly this pattern.

Note that such a feature is IMHO unique in the world of existing ORMs / OPFs - correct me if I'm wrong.

Marmot76it

Member

Registered: 2012-07-03

Posts: 4

Re: Serializing to JSON and storing on a text field: is it a bad idea?

Interesting, but maybe I'm loosing something here:
TRecordReference stores both the record ID (to identify the row) and the record type (to identify the table), is that right?
The record type is bound to the used model.
Well, in my case I wouldn't use such a solution because my model will expand and change over time, staying with the example above I would say that for example my model, at a certaing time, will include new log types, and maybe will drop some older ones.
But this is not the point.
The record IDs used by mORMot are Integers, am I right? I mean, there's something strange, Delphi Integers are 32 bit signed, while SQLite INTEGER PRIMARY KEY are 64 bit, so I'm not getting how things work.
So, is TRecordReference a 64 bit value of sort, something like 32 bit for table identification and 32 for row?
In any case how does this relate with the 64 bit INTEGER PRIMARY KEY?
There's something else that leaves me with some doubts, and it's about the first approach I described in the post: if I define a type called TSQLMyInterestingType(TSQLRecord), and then several:

TSQLMyInterestingAndBeautifuType(TSQLMyInterestingType)
TSQLMyInterestingAndFunnyType(TSQLMyInterestingType)
TSQLMyInterestingAndNiceType(TSQLMyInterestingType)
etc.

can I define another type, let's call it TSQLVeryImportantType(TSQLRecord), with a TSQLMyInterestingType property allowing me to "point" to any of the objects above? I mean: being children classes it should make sense, because it respect the general inheritance mechanism, but I don't know if it can work correctly inside the framework (well, as I wrote above, in this particular case I would also store the record type on another property, for querying purposes).

Forgive me for the elementary questions!

Last edited by Marmot76it (2012-07-06 07:09:17)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,609

Website

Re: Serializing to JSON and storing on a text field: is it a bad idea?

From the mORMot point of view, inheritance does not matter: each TSQLRecord class is of its own.
Inheritance is just for you to easily share code and properties, where it does make sense.

TRecordReference is linked to the Model.
You can safely add new TSQLRecord types to the Model, without any issue, or even upgrade the class type (i.e. add some columns).
But you can not:
- Delete an existing class from the model (I suspect you won't do that, since it does not make sense and would break code integrity);
- Change the TSQLRecord delcaration order in the Model (but I think this is not an issue, since this order is used only internally by the ORM).
So I suppose it will work for you.

About 32 bit indexes, it is not linked to SQLite3 (in its engine, every INTEGER is an Int64), but it is linked to the fact that IDs are sometimes typecasted to pointers (TObject/TSQLRecord) in published properties. So it should match the native pointer size, i.e. 32 bit.
The limitation of TRecordReference is 64 tables, and 24 bit for IDs (i.e. 16777216 items - which should be enough).