#1 2017-01-13 04:21:37

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

How to use Multi table transaction

How to use Multi table transaction In the Master/Detail Situation?
I found TransactionBegin/BatchStart both need a TSQLRecordClass parameter, Is that means it supports one table transaction only?
I plan to use servcies, implement transaction on server side.How can I do?

Offline

#2 2017-01-13 07:14:13

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: How to use Multi table transaction

Pass "nil" for the record class parameter to enable multi table transaction.

Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#3 2017-01-13 08:33:40

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

Re: How to use Multi table transaction

edwinsn wrote:

Pass "nil" for the record class parameter to enable multi table transaction.
Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

thanks. I'll try this.

Offline

#4 2017-01-13 12:43:17

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 105

Re: How to use Multi table transaction

edwinsn wrote:

Note, if you are using client-side trans, it's strongly recommended to use the batch-mode instead.

Why?

Offline

#5 2017-01-13 15:56:04

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: How to use Multi table transaction

RTFM

Offline

#6 2017-01-22 07:46:10

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

Re: How to use Multi table transaction

Sorry. I still have some problems. I am using server-side transaction. I tried two ways, both have problems.

aRestServer.TransactionBegin(nil, 0);
try
  aRestServer.Add(aMasterRecord, True);
  aDetailRecord.MaserId := aMasterRecord.AsTSQLRecord;
  aRestServer.Add(aDetailRecord, True);
  aRestServer.Commit(0);
except
  aRestServer.RollBack(0);
end;

In this way i can get and set the MasterId property.
But After aRestServer.Add(aMasterRecord, True),  data has been written into database already. Commit and RollBack won't do nothing.

aSQLRestBatch.Add(aMasterRecord, True);
aSQLRestBatch.Add(aDetailRecord, True);
aRestServer.BatchSend(aSQLRestBatch, Res);

In this way, transactions works fine. But I don't konw how to set aDetailRecord.MasterId property.

The real problem is that I want use transactions in the Master/Detail situation, and set Master/Detail relationship correctly.
Any help?

Offline

#7 2017-01-22 08:12:03

lele9
Member
Registered: 2011-10-28
Posts: 170

Re: How to use Multi table transaction

Hi,
Why don t use array of record (detail) as tsqlmasterrecord s property?

Offline

#8 2017-01-22 08:25:40

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: How to use Multi table transaction

@fubail@qq.com,
I think you at least have two options:
1 - As @lele9 said, re-design your data model, make the "detail table" as a property of the master record, in this case I prefer TObjectList as the type of the property, that way, the "detail records" are stored in JSON in the db, you can then take advantage of the SQLite's JSON feature. If you use another db system, I believe most modern db system has support for JSON. ref: (https://synopse.info/files/html/Synopse … ml#TITL_26)

2 - Control the generation of the ID value for your master records.


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#9 2017-01-22 08:33:24

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

Re: How to use Multi table transaction

lele9 wrote:

Hi,
Why don t use array of record (detail) as tsqlmasterrecord s property?

It's not a new project. It should works fine with old data.
And this way is not so SQL, is it?

Offline

#10 2017-01-22 09:02:41

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

Re: How to use Multi table transaction

@edwinsn
Thanks, I can try option 2.
I want to have an easier way. How do you solve this problem?

Offline

#11 2017-01-22 09:30:01

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: How to use Multi table transaction

@edwinsn
About data sharding and nesting object lists, we tend to use T*ObjArray, which are freed by the framework automatically, and also stored as JSON.

@fubail
It is in my todo list to add reference of IDs generated during the batch.
For instance, having TSQLREstBatch.Add returning a fake negative ID for the master, which will be recognized when the detailed record is inserted, and replaced by the actual real just-created ID.
But it is not implemented yet.

Offline

#12 2017-01-22 09:59:46

fubail@qq.com
Member
Registered: 2016-10-11
Posts: 9

Re: How to use Multi table transaction

@ab
Thanks. It's a good news.

Offline

#13 2017-01-22 10:45:52

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: How to use Multi table transaction

I'm currently using a timestamp-based UUID generated at client sides, so far so good: https://synopse.info/forum/viewtopic.php?id=2522

@ab,

That's a great proposal! And I'm looking forward to it.

Last edited by edwinsn (2017-01-22 10:46:29)


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#14 2017-01-22 11:42:02

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: How to use Multi table transaction

ab,
+1 !!

Offline

#15 2017-01-22 11:44:58

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: How to use Multi table transaction

For ID generation, you have the TSynUniqueIdentifierGenerator class which was designed for that.
In respect to your Generate64BitUUID, it doesn't use random, so is much safer, and has a process ID, so you can generate unique IDs among several processes/services.
And it is also faster.

In production, for cases with a batch insertion with linked IDs, we use TSynUniqueIdentifierGenerator.
And it also fits very well the need of gathering some information from several uncoupled nodes into a shared MongoDB bigdata storage, as we designed.
You can obfuscate the ID into some hexadecimal value: we use it for public HTTP URI generation, to safely return some resources (like photos or videos) by convention.

Offline

Board footer

Powered by FluxBB