mORMot Open Source

? About which mode do we talk here? Did you set "hard_commit=true"? Else i can't confirm your findings? Subsequential transactions are NOT supported yet. There is no TA1, 2 or 3! Required savepoints e.g. FD-Docs are not implmented yet.

Note I'm NOT the initial autor of Zeos. Just want to support you guys as good as i can. The behavior is known since years. (Even if i know this is a show-stopper if you are not aware of that!)

Thinking about it .. might be a option for 7.3.. Not sure yet Loads of work is waiting for current 7.3 development..

@AB
are you talking about Daniel's issues? Hope i got it resolved inbetween...? Let's wait for reply.. I wouldn't start from the premisse the issue remains, Arnaud.

Michael

Agree.

Note you kan kill the sweeper_cache issue by having a second "UPDATE"-connection.

Adding 'hard_commit=True' like

Your_SQLDBZEOSConnectionProperties.ZeosURL.Properties.Add('hard_commit=true');

The TIL of FB is you choise too:

(YourSQLDBConnection as TSQLDBZEOSConnection).Database.SetTransactionIsolation(YourTIL{Level: TZTransactIsolationLevel});

As i wrote in \testing-7.2 SVN the patch is available to do this.
tiNone allows you to define you custom tdb's by adding them to the URL.Properties too. See FireBird/Interbase doc's how to do that.

Michael

@AB

Yes this is right! You are in a single transaction since Connect.
You anly can Commit/Rolback.

Yes i agree with the AutoCommit idea. BUT who controls this behavior?
If YOU set Zeos in AutoCommit mode -> Zeos does it by it's selves! Each update will be commited after execution.

What i'm trying to say: If AutoCommit is set for Zeos you don't need a explicit Commit call on your side.
In addition if you are controler about the commits also in AutoCommit mode -> Than use the suggestion of Daniel. There is no need Zeos and Your are doing the commit call, understand me?

Michael

@mpv

which info's do you need?

Yes this is right for Commit and Rollback. NOTHING would happen. So it's not perfekt but ok.

procedure TSQLDBZEOSConnection.StartTransaction;
begin
  inherited StartTransaction;
  fDatabase.SetAutoCommit(false);
end;

is correct to me. You're expecting a Transaction which YOU can commit or Rollback.

If i would know how everything works in AB's ORM (:  In the past he asked me if this code is correct.. If it's a pure retaining model than of course it is correct.

Aranud it might be a good idea to add a property

TSQLDBZEOSConnection = class(TSQLDBConnectionThreadSafe)
...
public
  ....
  TransactIsolationLevel: TZTransactIsolationLevel read f.... write SetTransactionIsolation;
 ....
end;
procdedure SetTransactionIsolation(Level: TZTransactIsolationLevel);
begin
  fDatabase.SetTransactionIsolation(tiReadCommitted);
 
end;

tiNone by default is still OK for me.

Agree the users should have free hand to choose the TIL.

Just study SetTransactionIsolation() in ech ZDbcXXXConnection.pas what the enums do represent.
For PostgreSQL f.e. tiNone is the simple 'BEGIN' call.

But ... I must admit i have to check how you can manipulate in FB?!

Edit: Now you can also customize your TIL's for FB. Patch done R3526 \testing-7.2 will be merged to trunk and \testing-7.3 in a periode of a week.

Arnaud,

i know we did loudly ask for this support... But i have to admit TDS is a dead and way from my point of view. Let me explain some advantage/disadvantage for your users:

Advantages:
1. one little library to access Sybase or MSSQL by using the "Tabular Data Stream" protocol: ntwdblib.dll/dblib.dll or a FreeTDS library.

Disadvantages:
1. MSSQL does not support a type difference between N(VAR)Char / (VAR)Char-Fields
2. All Strings which are retrieved need a encoding detection to indicate which encoding a column has -> what about native USASCII chars? -> Performance?
2. NTEXT fields are not selectable you need a cast to TEXT
3. NO Prepared Statement API. Zeos simply emulatates it.
4. MSSQL is doing no encoding detection: so the spezial syntax whould be required to indicate which param uses which encoding! NO WAY AROUND
5. The TDS protocol is sequential only. The more you have only one Connection handle. NOTHING else. SO if you guys are making a select, Zeos is forced cache ALL data. If this is done mORMot can get the JSON-Contents. This is done because next query kills a opened request...
6. All String columns are retrieved with ' '(Space)-filled strings. There is a mode "ANSI_PADDING=OFF/ON" which allows to enable/disable this behavior. Disabling means you will get a exception if you try to update a fixed-char field with a length shorter than Field.Length. Nice isn't it?

Should i go on? I don't think so.

Well it might be possible some more users do want to use it. So keep the ticked but note this is a very spezial case. From my point of view: No need to over hurry.

Michael

Well the report is marked as Resolved and fixed.
AFAIU the FPC bugtracker: Closing should be done by reporter. Else they keep the report a long time until a core member decides to close it.

So they are waiting for a confirmation or you can reopen same ticket if you disagree, Arnaud.

Just a hint. Maybe AOG can compile the trunk and test the fix? Or you kindly should close the report. This way all are happy.

@corchi72

note:
You can't load a 64Bit dll with a 32Bit compiletarget. My error-message told you: The file was found but we can't load this lib!
C:\Program Files\ is propably the 64Bit directory.

This is also NO problem. Just download the 32Bit client from http://www.enterprisedb.com/postgresql- … =Crossover
This is NO installer since you've allready installed the 64Bit server. Then use the lib's of the zip. Best practice: Copy them to C:\windows\SYSWOW64 which is made for the !!!32Bit!!! libs. In addition copy the libs of your current installation to C:\Windows\system32 which is made for the !!!64Bit!!! DLL's. Delete all other approaches you made... have fun!

An example please... So i can help from Zeos-side!

@AOG

there is propably an aligment issue with ARM-processors.
FPC has a buildin define to control that behavior:

  MyRecord = {$ifndef FPC_REQUIRES_PROPER_ALIGNMENT}packed{$endif} record...

Note it might be possible you need dummy pointer alignments if pointers are used in the record

Don't know anything about delphi issues in this case..

You can safely use both:

IZConnection.GetMetadata.ClearCache;

pesits since i know Zeos.

'NoTableInfoCache=true'

Is a String property which simply has no effect for older Revisions.

During the process only the TableInfoCache can grow the memory until all informations are collected. May i ask if FireBird behaves equal?

Looks like good start.

To be clear:
Am i right you're calling the Zeos-MetaInformations only once an CreateMissingTables?

If so than you can simply flush the cache by calling:

IZConnection.GetMetadata.ClearCache;

if CreateMissingTables is done. What do you think? You can execute it by default from my point of view..

I know that, but keeps temporary allocated memory much smaller on each GetUTF8String().

Note:
1. since your project works with threads and each thread has a new IZConnection the memory grows because of Cached metadata informations.
2. for PostgreSQL the is a second cache, called TableInfoCache.

supressing the MetaData-Caching:

IZConnection.SetUseMetadata(False);

Well i've commited one little patch R3436 \testing-7.2
suppressing TableInfoCache:

'NoTableInfoCache=True' 

to the PostgreSQL-ZURL.Properties. This suppresses caching table informations we added for the NativeResultSets.

All in one.. You wouldn't notice such memory consumtion if there is just one connection...

Of course, they are threaded as CLOB, everything else would be wrong. Btw. your could also use VARCHAR with no length.

Hundrets MB of mem... hard to say. NOTE: Zeos internals do cache a loads of of Informations, instead of calling the Server again. Espezially for the slow PostgreSQL-server. This migth be one of Zeos performance advantages against FireDac. But this consumes Memory, of course.
Digging in.. the TZPGResultSet doesn't have a spezial GetUTF8String override yet. Currently i'm working on another refactoring: DO NOT DETERMINE all Columns-Information for !Reopened! reseultsets twice, just reset positions and handles instead. Slightly i've to many locale WC's. But having a look to the miising GetUTF8String override will be next.

May i ask if this example also uses ColumnsToJSON proc?

Thanks Arnaud!

i was thinking about updating this link: http://blog.synopse.info/post/2013/11/0 … ing-MS-SQL
IFAIU is Oracle/FB not changed in the diagramm, or am i wrong?

Note: I'm one with red/green colors (eyes)issues. So reading/comparing the diagramms is ... hard/impossible for me, sorry. Is there a way you change the colors for the Access-layers?
Something like Blue = mMORot, Green = Zeos, Gray = x etc? That would help me very much.

I would like to use the link as reference, Arnaud.

Cheers, Michael

Oh ok, thanks for sharing such knowledges. So let's say my proposal would hit some mirco-benchmarks with FastMM4.

Some weeks ago i did some research about FPC + FastMM4. I found some mailing lists of a user and Florian.
What i found out was: They did implement !some! parts which makes GetMem/ReallocMem comparable with FastMM4. But all other features are ignored AFAIRC.

Just a note, since mORMot seems to make the final steps for FPC cross-compiling..

Cheers, Michael

Edit, except you're checking against len+refcount for the result of the unquoted string, of course.

Arnaud,

may i ask .. is this issue Daniel reports a Zeos issue only? The patch you made seems to say YES whereas i think there is a common issue.
As Daniel describes there is a generic problem to convert TDateTime value if the values are quoted.

So what is wrong? Either Daniels JSON with quotes, mORMots conversion or simply my batch implementation for mORMot? Is this case handled for all providers and your own access units too?

Just to be clear...

Doe's Zeos have an issue here?

Look to the FPC-Bugtracker ... they clicked snooze button
Except ab did a complex workaround. Maybe you push the FPC core (which mostly intents to make endless discussions) by writing objections for the severity state, which btw. i do NOT understand.

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

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..

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

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

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.

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

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.

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)"

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

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"

True. But only persits 3x in SetDataArray(). The String-length things are different from that. As you've pointed me in the past: PChar(aString) calls the RTL (since FPC2.7 too) so a check if Pointer(aString)=nil the Result := myEmptyStringPChar else Result := Pointer(aString). So it's not only the missing inline D7 function Length() which was reason for this kind of code...

Edit: simplification of LowLevel code is done R3304 \testing-7.2 (SVN)

@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.

Does the ID generation really happen this way?

exactly this is suppressed in our FB implementation, by a dev-member "AVZ"(which is a FB "guru"). Check ZDbcInterbaseUtils.pas -> PrepareStatement. I don't know whay this is done...

I'll check your suggestion. Personaly i was thinking about registering a "TProcedure() of objet" as a callback routine if a connection lost happens. Flags are various and need to be handled from caller level again. So we need either a common flag or a callback function. (i would prefere this because each ZDBC interface could be closed and nil the handles silently). 

might be a good idea!
But there must be done much more than trapping this error flags. What should really happen if a "Connection lost" happens? Best case would be reconnect with same handle which would catch such dead-lock situations too. Might be possible to implement(even if i don't think this is possible) need to check the API's. If this is not possible.. we need a shutdown or a admin who kills the unused connections than.

BTW. Ping is supported by IZConnection, Arnaud.

Possible if prepared non commited stmt-handle blocks the transaction of another connection. Thus i don't think this is the case. Our FB implmentation seems to be stable even if i changed a lot for better performance. The SQLite statement thing you did point me was another thing. Before the performance upgrades did happen, we had a complete different (strange) behavior for this plain. So after my changes we did return to a true alpha state. Thanks to synopse we did resolve these issues too.

Hi Daniel,

i don't know enough about mORMot to know how it works with transactions. Was there a broken connection before? Something to know why a deadlock occours?