You are not logged in.
Pages: 1
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
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
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
Ticket created: http://synopse.info/fossil/tktview/139a … 906975a149
Offline
I've changed the implementation.
See http://synopse.info/fossil/info/bd06477233
Your feedback is welcome!
Offline
Yes! My first little test was successful.
Thanks a lot Arnaud!
Offline
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
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
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
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
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
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
Yes, please.
And I hope EgonHugeist will help us, here!
About the ID generated issue, please check http://synopse.info/fossil/info/b3c560ec2e
Offline
@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.
A little "error":
There must be a call of "select max(id) from table" twice.
Does the ID generation really happen this way?
Offline
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
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
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.
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
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
I didn't want to bother here. Simply thought Arnaud should think about it..
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"
Last edited by EgonHugeist (2014-09-13 08:15:08)
Offline
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
@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
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
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
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...
And i did learn a loads from you too.
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 ) 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
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
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
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.
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
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
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..
Numbers are talking. smile
Of course, as i said i understand you point of view.
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.
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.
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%) . Excuse me, i really didn't know enough about.
Michael
Last edited by EgonHugeist (2014-09-14 17:10:47)
Offline
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
First, I want to state, that for me this discussion is not so important, but:
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.
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
... 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
Offline
Pages: 1