#1 2014-09-10 17:03:27

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Batch+POST+Zeos+Firebird

I wanted to POST a batch like this:

{"Splitt":["POST",{"ID":"4617","KONTOAUSZUG_ID":"1","INFO":"Test"}]}
or
{"Splitt":["POST",{"KONTOAUSZUG_ID":"1","INFO":"Test"}]}
or
{"Splitt":["POST",{"KONTOAUSZUG_ID":"1"}]}

In server-log I see this:

20140910 17520960  % call  	TSQLPrcashRestServerDB(030BBDB0) Batch
...
20140910 17520960  % SQL   		TSQLDBZEOSStatement(016FD940) select max(SPLITT_ID) from Splitt
20140910 17520960  % EXC   	EAssertionFailed ("Assertion fehlgeschlagen (c:\\delphi\\common\\mormot\\trunk\\SQLite3\\mORMot.pas, Zeile 19352)") at 0016D9B8 SynCommons.TRawUTF8List (4752)  stack trace API 0012B0D4 System.Classes.{System.Generics.Collections}TThreadList<System.IInterface>.Destroy 00009664 System.Exp 
20140910 17520960  % EXC   	EAssertionFailed ("Assertion fehlgeschlagen (c:\\delphi\\common\\mormot\\trunk\\SQLite3\\mORMot.pas, Zeile 19352)") at 0016D9B8 SynCommons.TRawUTF8List (4752)  stack trace API 0012B0D4 System.Classes.{System.Generics.Collections}TThreadList<System.IInterface>.Destroy 00009664 System.Exp 
(yes 2x-->it's not a copy/past error from me)
20140910 17520960  % srvr  	POST root/Splitt/Batch ERROR=400 (Exception EAssertionFailed "Assertion fehlgeschlagen (c:\delphi\common\mormot\trunk\SQLite3\mORMot.pas, Zeile 19352)" did break TSQLSplitt BATCH process)

The ID-Field has a generator and a trigger so it were not necessary the mORMot sets an ID.
However, I can't get it to work.

The SQL-Insert is possible. But mORMot seems to create an false statement in EncodeAsSQLPrepared.
P is '' in 19352. And tmp look also not so nice for me, but I don't understand the code not so really.

Perhaps AB could help a little bit?

Offline

#2 2014-09-10 19:02:40

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

Re: Batch+POST+Zeos+Firebird

Are you creating the BATCH by hand?

We may have to change TJSONObjectDecoder.EncodeAsSQLPrepared() to use a TTextWriter instead of the current manual pre-allocation of the tmp buffer, which sounds buggy in your case.

Could you please create a ticket?

Offline

#3 2014-09-10 19:24:27

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

ab wrote:

Are you creating the BATCH by hand?

Yes, I admit it!

I've done a request

http://domain/root/Splitt/Batch

and post in body the Json (like the mORMot client do -> I've first tried with CrossPlatform client and here was the same result).

I'll create the ticket tomorrow.

Last edited by danielkuettner (2014-09-10 20:17:46)

Offline

#4 2014-09-11 06:10:18

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

Offline

#5 2014-09-11 06:55:39

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

Re: Batch+POST+Zeos+Firebird

I've changed the implementation.
See http://synopse.info/fossil/info/bd06477233

Your feedback is welcome!

Offline

#6 2014-09-11 07:13:05

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

Yes! My first little test was successful.

Thanks a lot Arnaud!

Offline

#7 2014-09-11 07:47:14

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

A little "error":

There must be a call of "select max(id) from table" twice.

When I post an insert of 1 row, the ID of table increase by 2.

1. ID is 4617
2. Batch/Insert of 1 Row
3. ID is 4619
4. Batch/Insert of 1 Row
5. ID is 4621

and so on.

Offline

#8 2014-09-11 09:28:24

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

Re: Batch+POST+Zeos+Firebird

I have never seen that.

ID are tested in the regression tests.
What is your db backend?
Have you code to reproduce it?

Offline

#9 2014-09-11 10:23:46

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

I use Firebird 2.5 server (not embedded) under Win2012.

The batch is doing with javascript:

      var aJson = '{"Splitt":["POST",{"KONTOAUSZUG_ID":1,"INFO":"Test"}]}';

      $.ajax({
      url: url + 'Splitt/Batch',
	  type: 'POST',
	  data: aJson,
	  success: function (data, textStatus, xhr) {
	    //f();
	  },
	  error: function (xhr, textStatus, errorThrown) {
	    var s = 'Error in Operation';
	    alert(s + textStatus + errorThrown);
	  }
       });

MAX-ID in table splitt before first batch: 4616
MAX-ID in table splitt after first batch: 4619
MAX-ID in table split before second batch: 4621

ID will created by mORMot (Generator/Trigger is active but won't be active because ID is not NULL).

Last edited by danielkuettner (2014-09-11 10:24:02)

Offline

#10 2014-09-11 12:23:13

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

I've founded the following in mORMotDB.pas

line 900:

inc(fEngineLockedLastID)

and in line 1058:

if fBatchMethod=mPost then
  fEngineLockedLastID := fBatchAddedID+fBatchCount;

These are two inch's.

I've tested and suggest this:

if (fBatchMethod=mPost) and (fBatchCount > 1) then
  fEngineLockedLastID := fBatchAddedID+fBatchCount;

Last edited by danielkuettner (2014-09-11 12:43:13)

Offline

#11 2014-09-11 13:42:43

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

Re: Batch+POST+Zeos+Firebird

AFAIK line 900 (EngineLockedNextID) is used when inserting one item - TSQLRest.Add.
And line 1058 (InternalBatchStop) is used when inserting items in batch - TSQLRest.BatchAdd.

But EngineLockedNextID is also called in InternalBatchStart:

      if Method=mPOST then
        fBatchAddedID := EngineLockedNextID else
        fBatchAddedID := 0;

Sounds like an issue.

Could you try:

  finally
    if (fBatchMethod=mPost) and (fBatchCount>1) then // this is right
      fEngineLockedLastID := fBatchAddedID+fBatchCount-1; // note the -1 here, since EngineLockedNextID did already a +1!
    SetLength(fBatchValues,0);

Offline

#12 2014-09-11 15:28:29

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

Yes, you are right. Your -1 works.

But as I tested the code under 64bit (sorry),  there came a type-error on line 1029:

Query.ExecutePrepared;

with exact the same code (json) as with 32bit that works.

But I can debug tomorrow.

Offline

#13 2014-09-11 16:45:20

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

Re: Batch+POST+Zeos+Firebird

Weird.

Please try with SQLite3 (e.g. as external dll, via SynDBSQLite3.pas unit), instead of SynDBZeos + FireBird.
To see where is the problem.

Offline

#14 2014-09-12 07:24:00

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

I've found the issue in ZDbcInterbase6Statement.pas line 202:

procedure TZInterbase6PreparedStatement.BindInParameters;
begin
  if (ArrayCount > 0) then --> under 32bit is 4; under 64bit is 0

The Exception raises in ZDbcInterbaseUtils.pas line 1030:

      stLongWord, stLong, stULong:
        ParamSqlData.UpdateLong(I,
          ClientVarManager.GetAsInteger(InParamValues[i]));

Should I create a new topic?

Offline

#15 2014-09-12 08:00:01

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

Re: Batch+POST+Zeos+Firebird

Yes, please.
And I hope EgonHugeist will help us, here!

About the ID generated issue, please check http://synopse.info/fossil/info/b3c560ec2e

Offline

#16 2014-09-12 19:37:35

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

@daniel

the Exception should be fixed now for 64Bit too. Please test it.

@ab

Hope we got it, since my first idea was the bottlenek.

daniel wrote:

A little "error":

There must be a call of "select max(id) from table" twice.

Does the ID generation really happen this way?

Offline

#17 2014-09-12 20:30:41

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

Re: Batch+POST+Zeos+Firebird

EgonHugeist wrote:

Does the ID generation really happen this way?

It did, but it should have been fixed now.

Offline

#18 2014-09-12 21:12:04

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

ab wrote:

It did, but it should have been fixed now.

What i can see is you fixed a little double constraint viaolation. What i mean is do you really generate the ID's this way? Or is it just a expression of Daniel?

If so -> Threads/Transactions/Multiuser-insertiations --> new constraint issues.. Thought using Max(ID) could called be 100 threads+connection @same time(or differnt but as long the transaction aren't commited the results are always the same(tiReadCommited f.e.)) so all of them are trying to insert a row with same starting to X ID's. My suggestion would be stop doing that. Could a user flag a field to let mORMot know such ID's are auto-generated?

Possible scenario:
Daniel nicely did add a BeforeInsert-Trigger which calls a sequence/genarator "if id_fiel = null then select gen_id() into id_field" (OSLT). However AFAICS does the trigger never do his job and the generator never increases it's value (which is fixable). Now another app attaches and inserts Daniels "Kontoauszüge" and left the ID field as NULL. Bang the trigger cames to shove and we'll have next constraint violation.

Should i go on with such bad scenarios? IMHO should the be a flag to exclude mORMotish generated values and use RDBM solutions instead. A generator/sequence (supported by Oracle, PostgreSQL, FB/Interbase, DB2) is a nice multiuser-save idea to prevent such cases. Each provider has it's own solutions. SQLite has its LastRowID, MySQL too, So a common way is hard to implement. But call a max(ID) usually is safe for one user and one transaction only.

Last edited by EgonHugeist (2014-09-12 21:14:30)

Offline

#19 2014-09-12 22:20:22

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

Re: Batch+POST+Zeos+Firebird

So it is centralized on the mORMot ORM level, protected with all multi thread safety as possible, for best speed. Max ID is run only once. Please see our code before doing wrong guess.

We may implement sequences later (as we do for Sqlite internal main engine) but it was not mandatory yet.
We tried to abstract most of the SQL plumbing and would never certainly let our ORM create triggers or such kind of code. We have no time nor passion to recreate a hibernate complex system.
We persist objects in a db, which may not be a rdbms.
Remember we support nosql back ends also, which needed this client side ID generation.

For complex SQL solutions, the easiest is to write the SQL and publish the data with our SOA features.
This is why our classes are able to generate JSON directly, BTW.

Offline

#20 2014-09-13 05:50:13

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

ab wrote:

So it is centralized on the mORMot ORM level, protected with all multi thread safety as possible, for best speed. Max ID is run only once. Please see our code before doing wrong guess.

I'm not trying to teach you. It might be a wrong guess. Thought i'll point you to such "possible issues" in your current state.

ab wrote:

We have no time nor passion to recreate a hibernate complex system.

Of course! And it's not really required to do that. Example of Daniel again: He did implement a trigger how's doing the ID job. So my idea was to let the user decide if mORMot generates the values for primary keys or the DB is doing that. Than just work with a "RETURNING" clause for example. So you can update your JSON contents after insertations is done. AGAIN just some personal ideas. It's up to you to decide the best practice for your framework and i'm starting from the premisse you know what your're doing.

Last edited by EgonHugeist (2014-09-13 05:50:56)

Offline

#21 2014-09-13 07:19:38

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

I'm sorry for the discussion between ab and EgonHugeist.

The creating of ID shouldn't be a problem. The select max(id) sounds simple but is ingenious. If the code ist thread-safe and every client must through this line of code, all is good.
My old trigger does it the same way. And I've the trigger created many years ago. Next what I'll do is to delete them. The trigger is fine for Firebird, but what when I decide to change my DB to SQLite?

Therefore I need a "trigger" in mORMot and this is "select max(id)" (at the moment).

Thanks for your support!

Offline

#22 2014-09-13 07:37:15

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

I didn't want to bother here. Simply thought Arnaud should think about it..

daniel wrote:

The trigger is fine for Firebird, but what when I decide to change my DB to SQLite?

MySQL: supports a mysql_insert_id call.
SQLite: supports a sqlite_last_insert_rowid call which Arnaud allready uses.
Postgres supports some more: Sequences or SerialFields -> use a returning clause
FB/Interbase, Oracle, DB2 as suggested before use a returning clause
MSSQL 'SELECT @@IDENTITY' which could be a ordinal or a guid...

All in one if there is no API-call like MySQL or SQLite have this mostly means performance decrease... So i do understand Arnaud.

Edit: And as Arnaud already mentioned: check the code -> which i really didn't yet. So my objections might be a bit "paranoid" wink

Last edited by EgonHugeist (2014-09-13 08:15:08)

Offline

#23 2014-09-13 08:22:19

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

Ok, now we have a special method for all kinds of DB's which all are different.
But if all clients are connected over mORMot with select max(id) we can use all DB's in one way.
The advantage is the independence. I'dont think (it's only my guess) that 'SELECT @@IDENTITY' is better than 'select max(id)'. 'SELECT @@IDENTITY' is a client-server feature that isn't needed more in a multi tier system (like mORMot).

If I'm wrong, than let me know.

Offline

#24 2014-09-13 08:39:10

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

@Daniel

Inital issue for my proposals: ALL ID's need to be "fetched" BEFORE or AFTER the insertion is done. This really means a performance loss for BATCH inserts as well. So i understand AB.

some links:
http://stackoverflow.com/questions/2944 … nserted-id
http://stackoverflow.com/questions/9477 … -statement
http://www.firebirdfaq.org/faq243/
...

All of them do point to a session "save" idea how to do that.. Let's stop that discussion. I think Arnaud know his code better then me..

Last edited by EgonHugeist (2014-09-13 09:25:52)

Offline

#25 2014-09-13 08:49:38

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

I think the discussion was sometimes to hard.

I really learn much from that discussions. I read statements from very good developers and therefore I think such discussions should be allowed.

Thanks!

Last edited by danielkuettner (2014-09-13 08:55:56)

Offline

#26 2014-09-13 09:06:11

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

Re: Batch+POST+Zeos+Firebird

Sorry if my words sounded hard.

I am so glad and thanks full for all the good job Michael is doing with Zeos!
It outperforms even FireDac on most platforms!
And was tuned to work with our little mORMot.

Offline

#27 2014-09-13 09:59:42

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

Thank you Arnaud. Writing fastcode is a permantal learning process for me too... So such "hard words" are welcome even if a first smile is missing... wink
And i did learn a loads from you too.

ab wrote:

And was tuned to work with our little mORMot.

Yes i did a loads to reach something like "perfect fit" for mORMot (which is an incredible not little framework IMHO). But i'm sure other ZDBC users will also benefit from latest refactoring. And we can also catch some more users to test current implementations!
Have one more idea which could be usefull for all ZDBC users: something like RowToText(ARowWriter: ITextWriter); OSLT. However feature adding did stop on 7.2 we'll go beta which means bugfixing only is allowed than. AND 7.3 goes into production.

Apart from this.. Arnaud finally i miss a little comment accordingly my objections i wrote before. Do i overlook something or do you plan some changes like (my ideas)

dependent to server create a sequence/genarator e.g. CreateMissingTables, and execute a "select gen_id()" to generate your id's instead.
EDIT: for FireBird you could also make a EXECUTE BLOCK stmt which fetches all ID's you need once. Something like this should be possible for PGSQL and Oracle too.
That would be multi-session save.
for MSSQL(in most areas a show-stopper ), MySQL no idea about best practice in batch mode...
Performance is pretty nice but not everything (like low level implementations smile ) I know this is hard to judge...

Just a final "Arnaud" stmt..

Second Edit:
Very simple (FB again): select gen_id(GENERATOR_NAME, YourBatchCount);
So you have last ID you can use ID := LastId-YourBatchCount+CurrentIteration. Multi-User/Session save too... And shouldn't take more round-trips like calling "select Max(ID)"

Last edited by EgonHugeist (2014-09-13 10:49:06)

Offline

#28 2014-09-13 16:01:36

moctes
Member
From: Mexico
Registered: 2013-05-11
Posts: 129

Re: Batch+POST+Zeos+Firebird

I have to agree with Michael about generating ID's at the database level and if the database does not have an internal mechanism we can always fallback to the "select Max(ID)", but of course Arnaud has the last word on this; I think it will always be "safer" to let the database handle that job also I want to be very careful here, that doesn't mean I'm against AB approach it is just to let you know how I think about this if I may :-)

Offline

#29 2014-09-13 19:45:46

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

Did some research for my sequence suggestion and to resolve a "on demand" session-safe ID generation

FireBird (simplest one):
http://www.firebirdfaq.org/faq243/

instead of calling "select Max(ID)" you could use in
1. Batch mode:

use "select gen_id(GENERATOR_NAME, YourBatchCount)-YourBatchCount;" 

2. Single insertiation:

use "select gen_id(GENERATOR_NAME, 1);"

PostgreSQL/Oracle are more complicated since you can't increment the sequence by hand.

great Oracle description: http://www.dbanotes.com/database-develo … sequences/
PostgreSQL: Just a !untested! idea with a SP:

CREATE OR REPLACE FUNCTION NextId(seqname varchar, iters bigint)
RETURNS BIGINT AS
BEGIN
  select NextVal(seqname) as result;
  if iters > 1 then
    SetVal(seqname, result+iters);
END;
LANGUAGE 'PLPGSQL';

Don't know if this works for Oracle too.. Just an idea.
Edit: it would see http://www.gokhanatil.com/2011/01/how-t … ating.html
and for MSSQL too, see http://msdn.microsoft.com/de-de/library/ff878091.aspx

Now in all 3 cases you've got the first ID and you're sure no other session can get a id in range of returned ID + Itaration;
These Values can be used safely. This approach would be session safe, you can insert them by hand in your JSON contents, you won't run into a constraint issues and finally you won't have a leaking generator incrementing issue.

Oh and i found a quite nice syntax for MYSQL:

 INSERT INTO foo VALUES () ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID (id +1);

But this wouldn't resolve the issue on you side. IMHO there is only the LAST_INSERT_ID call (SQL/API) which can be used session safe.


Disadvantages of my ideas: What about user permissions(altering a sequence)??

Cheers, Michael

Last edited by EgonHugeist (2014-09-13 22:06:47)

Offline

#30 2014-09-14 12:06:48

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

Re: Batch+POST+Zeos+Firebird

Yes, I already did some similar research and findings:
- There is no unified way to create sequences;
- Some DB do not even handle such generators (e.g. NoSQL MongoDB);
- In some cases, you need a dedicated request either to compute the new ID, or retrieve the just inserted ID - so for single insertions, it may divide speed the throughput by two;
- For ZDBC, AFAIR there is the ZSequence object, but some providers do not have one "LastInsertedID" safe value - so we would need to support this sequence at ORM level;
- It would require a lot of time to debug, tune and report, for each supported DB, at ORM level.

This was in short the reasons why I just implemented a simple and safe ID generator at ORM level.
Once again, please understand that "select max(id)" is just called once, then the ORM maintains its own thread-safe ID.

Remember the findings of Roberto Schneiders about SQL performance of the current pattern of mORMot, and TMS Aurelius.
See http://synopse.info/forum/viewtopic.php?pid=6363#p6363
AFAIK Aurelius use such SQL-defined generators for inserting data, as you propose to use.
Numbers are talking. smile

For external databases with existing code still accessing and inserting rows to the DB outside of the ORM, we may use sequences.
But since it would need to work with existing code, we may not even be able to let the existing code use the same sequences...
It does not mean anything to implement our own complex - but safe - "ID" feature in SQL, if the legacy code, outside mORMot, is not able to handle it on its side...

So my initial proposal was to simply by-pass the ORM for insertion in such "shared" tables, and rely on manual SQL written as a service, sharing the same ID generator logic than any legacy code.
It would certainly cost less to write some manual SQL statements in a mORMot service, to fit the existing sequence generation behavior on the legacy code, than changing the legacy code to fit mORMot's way of using sequences...
What do you think?
I just want to be pragmatic.

And once legacy code does not need any more to insert its data with its own SQL, and switched to n-Tier design, we may use the existing ORM code.

Offline

#31 2014-09-14 15:29:56

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: Batch+POST+Zeos+Firebird

First of all i still doesn't know about you ORM, Arnaud.

Did some research for "select MAX(ID)" but i didn't found enough to judge if it is session safe or not.

If i understand you correctly

ab wrote:

Once again, please understand that "select max(id)" is just called once, then the ORM maintains its own thread-safe ID.

then do you execute the determination once and all threads and sessions to get the value from the RestServer, right?

TSQLRestStorageExternal.EngineLockedNextID: Integer;

seems to execute this determinations. If now all clients get the Next ID from server, all ID generation is in sync with all threads/sessions and you are able to ensure NO other additional client can produce a constraint violation.. Everything is fine and forget about my warning.
But IMHO can nobody ensure this will never happen. Ok you can simply add a comment to the documentations and say this is not a mORMotish problem but this is eval, i think.
Intital issue i see are manual written queries and or non mORMotish clients..

ab wrote:

Numbers are talking. smile

Of course, as i said i understand you point of view.

ab wrote:

So my initial proposal was to simply by-pass the ORM for insertion in such "shared" tables, and rely on manual SQL written as a service, sharing the same ID generator logic than any legacy code.
It would certainly cost less to write some manual SQL statements in a mORMot service, to fit the existing sequence generation behavior on the legacy code, than changing the legacy code to fit mORMot's way of using sequences...

Uff a simple code snippet? No idea how you wanna do that right now.

ab wrote:

What do you think?

Create a abstract class with !current! behavior like the IZSequence. Allways accessable and default. Initial idea: Let the User decide if ID generation should happen current way or the "safe" way -> performance and than it's no longer an issue you've to think about, Arnaud.

Then i would introduce:

IDGenerator := array[low(TSQLDBDefinition)..High(TSQLDBDefinition)] of TAbstractIDGen = (....);

If this is done just introduce the classes in code. So it's not a huge refactoring at beginning.

Next would be to create RDBM speciffic classes. I did read the comments of Roberto Schneiders and the linked thred. Where i'm 100% sure about:

"select gen_id(Generator_NAME, xxx)" is LOADS faster than "select MAX(ID) from xxx"

And i wouldn't be surepriced if this would hit the goals for other providers too.
Also could this simply do the Genarator/sequence creation in its own logic e.g. CreateMissingTables.

ab wrote:

And once legacy code does not need any more to insert its data with its own SQL, and switched to n-Tier design, we may use the existing ORM code.

as i said i don't know enough about your framework. It was just a hypothetical guess. But my experiences are: you may start from the premisse such session constraint violations happens. And you should take care how to resolve this.
Another little guess: What will current mORMot do IF such a bad scenario happens. AFAICS there is no callback which executes a reset of the ID and resets all clients, or is there a emergency callback available?

Arnaud, i wrote with Daniel. I must admit my english is a bit .... Sometimes i've problems to understand you.

All in one and to be clear:
You fetch the ID once.
All clients get the next id from the RestServer, right?

Well my guess might be right but not insight your ORM. Only if (worst case) "someone" external  does some inserts and you get no notification about. So i'm more quiet now (some thing like 75%) smile. Excuse me, i really didn't know enough about.

Michael

Last edited by EgonHugeist (2014-09-14 17:10:47)

Offline

#32 2014-09-15 07:01:38

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

Re: Batch+POST+Zeos+Firebird

This is what I stated: as soon as one external process does insert its own items, bypassing the ORM, there would be a conflict.

Since there is no simple way to ensure that this external legacy process and the ORM may share the same generator, we can not ensure that the ORM would be able to work on this table.
So in this case, what we recommend is to create a dedicated mORMot service, with INSERT SQL statements, compatible with the external legacy process.

In all cases, it is only for projects including legacy code, which already INSERT data to an existing DB.
Designing a new mORMot project with a new external process inserting rows to the new DB (e.g. stored procedures) would be a non sense.
If you use mORMot ORM, you have NO reason not to use its ORM for inserts, especially its BATCH process.

Offline

#33 2014-09-15 09:45:59

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

First, I want to state, that for me this discussion is not so important, but:

AB wrote:

This is what I stated: as soon as one external process does insert its own items, bypassing the ORM, there would be a conflict.

Yes it is, but it is not a must.

AB wrote:

Since there is no simple way to ensure that this external legacy process and the ORM may share the same generator, we can not ensure that the ORM would be able to work on this table.

There is a simple way: When mORMot would use the same generator like any other client (like suggestion of EgonHugeist). This is clearly not necessary for mORMot, but
if coexistence of mMORot and older client-software is a goal, then it is.

Of course you have right, you cannot ensure that other legacy process makes a "select max(id) instead of using a generator. But if legacy software do it right and mORMot do it also right, then it works.

Therefore mORMot has to know, wich generator it should use. There have to be an option for that.

Offline

#34 2014-09-15 10:48:58

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

Re: Batch+POST+Zeos+Firebird

... remember that:
- Some DB engine do NOT have generators!
- Generator use for simple INSERT or BATCH process would need to be specific, depending on the generator type: so we would need TWO generator logics.

Could you create a ticket for having at least an option for external ID generation?
(linking to this post)
We may define one callback event, which would run it instead of our TSQLRestStorageExternal.EngineLockedNextID default current implementation.
Then user may be able to write some simple SQL, depending on the DB backend and the external code implementation, to return the ID.
Then, in a second time, we may implement some generic implementation of this callback, at SynDB level, for all supported DB.
This would create something similar to the TOnBatchInsert event as defined in SynDB, but for ID generation.

Offline

#35 2014-09-15 10:58:48

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 357

Re: Batch+POST+Zeos+Firebird

Offline

Board footer

Powered by FluxBB