Is there some documentation on master detail relationships in mORMot?

vonH · 2016-09-19 23:02:54

Is there some documentation on master detail relationships in mORMot and some demos as well?

Last edited by vonH (2016-10-09 14:45:35)

ab · 2016-09-20 15:43:07

The idea with the framework is about "sharding" the data, and using "aggregates", as it is current with DDD (and NoSQL).
That is, you embed all your data into a single record, using complex structures like arrays or nested objects (in mORMot, this is done by using variants and T*ObjArray).

For one-to-many or many-to-many relationship, see http://synopse.info/files/html/Synopse% … ml#TITL_70
For the "aggregate" orientation, and how to store "entities", please see http://synopse.info/files/html/Synopse% … l#TITL_167

edwinsn · 2016-09-21 14:15:35

My most recent approach for handling detail items follows @ab's suggestion, except that **only use** types such as TObjectList, TStrings, etc (http://synopse.info/files/html/Synopse% … ml#TITL_26), that always stored as JSON arrays in the database.

Why so? Because you can take advantages of the JSON1 extension of SQLITE (https://www.sqlite.org/json1.html#jex), espciaally the json_each() function, to **directly access** the detail items in SQL, inlcuding joining regular table rows and json arrays, this is crucial, otherwise you'll lose the power of SQL statements.

In other words, do NOT use dynamic arrays to hold the detail items, because dynamic arrays will be saved as BLOB in the db.

This is my conclusion after hours studying and evaluating the JSON1 SQLite extension today

oz · 2016-09-21 20:12:10

Imho DDD is the only way to go for building complex applications. By Following the DDD priciples using mORMot's great SOA features you can seperate storage classes from your DTO objects. You can use T*ObjArrays in your DTO's and persist those arrays to regular RawUTF8 fields in your TSQLRecord* classes using custom field assignment functions from DTO to TSQLRecord and vice versa.

Last edited by oz (2016-09-21 20:13:08)

Junior/RO · 2016-09-22 02:26:31

I can think of complex structures using arrays. Can you give a example of complex structs using the Variant type?

ab · 2016-09-22 06:27:12

Use a TDocVariant type: it is very powerful.
See http://synopse.info/files/html/Synopse% … ml#TITL_80

vonH · 2016-09-23 07:00:41

How do you upgrade an object schema?

When you add more fields to your objects how is the database upgraded?

Do you have to create a table for the new schema, open all the old records and save them to the table for the new schema?

ab · 2016-09-23 07:20:42

Documentation wrote:

Another good impact is the naming consistency. For example, what about if you want to rename your table? Just change the class definition, and your IDE will do all refactoring for you, without any risk of missing a hidden SQL statement anywhere. Do you want to rename or delete a field? Change the class definition, and the Delphi compiler will let you know all places where this property was used in your code. Do you want to add a field to an existing database? Just add the property definition, and the framework will create the missing field in the database schema for you.
http://synopse.info/files/html/Synopse% … #TITLE_116

emk · 2016-09-23 22:12:01

If you want some classic approach this is what I want to test next week:

I'm using SOA - method based

1. Retrieve data as an JSON object (ex: "Order" with "OrderLine") into 2 TDataset-compatible(client side). Server-side, you could use for this ISQLDbRows.FetchAllToJSON for every table and combine the result by using TTextWriter.
2. On those 2 memory-TClientDataset you can have master-detail as you already know.
3. When you post changes back to mORMot server, on client side, scan every of those 2 TClientDataset for changed rows and you build an JSON array of changed records:
ex:

[{"table":"Order",  "op":"update", "version":1, "Data":{"ID":123, "Amount":110} },
 {"table":"OrderLine",  "op":"update", "version":1, "Data":{"ID":124, "Qty":11} },
 {"table":"OrderLine",  "op":"delete", "version":1, "Data":{"ID":125} }]

For generating on client side the JSON from TClientDtaset you can use TDocVariant or JsonDataObjects or any Json writer.
4. On server-side, for transforming that JSON into SQL phrases use TJSONObjectDecoder record (for Fields "table", "op", "version") and GetJSONObjectAsSQL function (for "Data"; GetJSONObjectAsSQL uses inside also TJSONObjectDecoder record):
ex:

update Order set Amount=110 where ID=123 and version=1;
update OrderLine set Qty=11 where ID=124 and version=1;
delete from OrderLine where ID=125 and version=1;

Notes:
a) "version" it's used for optimistic locking; you can use a timestamp instead; the basic idea is it prevents updating a row which is already modified by somebody-else since the time it was retrieved by me.
b) this approach is compatible with Elevate Web Builder with the difference that instead of "Data" and "version", they use 2 json-subobjects "newrow" and "oldrow".

Last edited by emk (2016-09-24 09:44:08)

mORMot Open Source

#1 2016-09-19 23:02:54

Is there some documentation on master detail relationships in mORMot?

#2 2016-09-20 15:43:07

Re: Is there some documentation on master detail relationships in mORMot?

#3 2016-09-21 14:15:35

Re: Is there some documentation on master detail relationships in mORMot?

#4 2016-09-21 20:12:10

Re: Is there some documentation on master detail relationships in mORMot?

#5 2016-09-22 02:26:31

Re: Is there some documentation on master detail relationships in mORMot?

#6 2016-09-22 06:27:12

Re: Is there some documentation on master detail relationships in mORMot?

#7 2016-09-23 07:00:41

Re: Is there some documentation on master detail relationships in mORMot?

#8 2016-09-23 07:20:42

Re: Is there some documentation on master detail relationships in mORMot?

#9 2016-09-23 22:12:01

Re: Is there some documentation on master detail relationships in mORMot?

Board footer