mORMot Open Source

Actually, I relied on mORMot itself to create all needed tables and fields.

But how can I make it "transparent"? I would like to use it with SQLite3 or MSSQL alternatively. Is there some way to overcome that? FYI, I use 'ORDER BY ID DESC' to get the most recent row.

I browsed through the commit hstory for hours before posting, but didn't found changes related to that.
I'd prefer not to update too frequently, sometimes regressions broke out

Thanks for your replies,

Oops, you've got me here!  Thanks for pointing me that I've said something "plainly wrong" but "the same thing in (my) question"
And yes, pool of threads can handle anything, including a single thread (which can be seen as a trivial case of a pool) can do anything...

You said also:

I presume you've meant the call in the method THttpApiServer.Execute.

Actually this can happen only in the case when ReceiveHttpRequest() was called in a synchronous manner (it is) and there is no request arrived in the specified queue.
See: https://docs.microsoft.com/en-us/window … ttprequest

I suppose you should check the way you generate the requests first.

Regards,

@danielkuettner,

I apologize (if it is possible at all), in my country we have also saying: "no teeth are examined on a gift horse". You're perfectly right - this single issue can be considered as a "hair in the soup".
We really have to admire people who provide their work for the benefit of others.
Perhaps I should change my attitude.

At the other hand, everybody in that forum can easily check that I've trying to work (benefit) with (from) the framework for the past 4 years. And I really struck an issues.

To put it another way:

@ab,

I want to make a strong contribution to the development as an exchange for the benefits I have received (will receive in the future). But there are things that hinder me, and I believe not only me, but also many other potential contributors. To note a few of them:

- The mammoth size of the main source files; They are beyond my comprehension;
- My unawareness of the development model; AFAIK there is only a development version, nor stable or LTS;
- My ignorance of the framework historical background, in every project there are things you cannot explain without such a context; IMHO there is lot of decisions taken by reasons which I do not fully understand;

I sincerely believe that if you give a little push against the above things, I can be of real benefit to the cause.

Best regards,

@ab,
Why you're talking again in 'patterns'? What about the 'common sense' pattern? How about the VMT^ part in my previous reply? Asserting that a nil reference is usable just isn't sane!

Regards,

@edwinsn:

Guard clauses has nothing to do with that. Or to be more specific - yes, it had to have a guard clause before using a nil reference, not after that.

The irony is that I had the intention to refer to the Marco's post in order to support my point. Have you noticed the title?
"When Self in Nil... You Know You are in Trouble"

And, of course, any sort of comments can be expected on any post, I would point to Jolyon Smith's comment, the 5-th after the one you cited.

Simply: does not work. I don't use TSQLRecord properties. I use my own ID generating scheme (forced ids) and the program behaves differently on different machines (MSWin).

Yes, it happens. But it shouldn't be used. It is a programming error. A big one.

Hmm, maybe... Depends on the (order of) data you're inserting. Wondering if creating an index does not lock too, doesn't it?

What's the difference with good ol' plain fopen()/fseek()/fread()/fwrite()/fclose() then?

Lately I wonder what apps you are dealing with, they must be very problem specific ... to dump a tons of information without provisions for fast retrieving ... DAQ systems?

Regards,

@edwinsn,

Thanks for sharing your experience!

Currently I have hard times debugging my application, and to be honest, my code also contributed to the issue. As I wrote above there is two things alarming in the log, the first is about "TransactionBegin timeout" and second is that spurious "cannot rollback" thing.

While the former is the one I'm complaining about, the latter was actually provoked by my code. The problem lies into TSQLDataBase.TransactionBegin() method, which detects the active transaction and instead of throwing an exception like "Can't nest transactions", it surprisingly rollbacks the transaction and begins a fresh one! IMHO this is extremely insidious, because it prevents me (developer) from detecting that something is wrong with my code (yes, it is) and throws away all changes made so far without any clue.

Normally, my application works without writing a log. To get this findings I had to make special debug environment with 5 client computers and one server and to record a mouse/keyboard macros to simulate operator activity on each of the client. The macros are replayed at higher speed to stress the server. Even then, I must wait at least one hour to reveal the issue. Then I start digging in a hundred megabytes of log text (@ab: yes, the concurrency is a difficult thing, I agree).

And not surprisingly, the syncing code between the DB-s was involved one more time, the logical operation needs to update the target table and the modifications log table in one batch. That is the relational connection blamed so furiously by Mr Bouchez.

Best regards,

I see. Since the SQLite transaction overhead is added to every single statement (which is in its own implicit transaction), you use the side effect of the explicit transactions to skip low level writes in between records. Why then is all the fuss about the "12.3.3. Unit Of Work pattern" in your documentation?   

Correct. What's wrong about it?

Why should I change the model of my application just to walk around an incapability?

Anyway, why we must discuss software architectural matter, when those "batches" exists into the library and obviously they have a concurrency issue?

Kind regards,

1.18.4878

Lazarus 1.9.0
FPC 3.1.1 SVN r63034
i386-win32-win32

TSQLHttpServer kind: useHttpSocket
Database.AcquireWriteMode := amBackgroundThread; {if it makes a difference}

@ab

C'mon, it should be trivial to implement. Perhaps preserving the last Data returned from TSQLRestBatch.PrepareForSending()?

Regards,

Actually my code goes like this:

  Batch := TSQLRestBatch.Create(Client, Nil, MaxInt);
  ...
  for {several times} do begin
    Batch.Add(Barcode, True,True); 
    Batch.Add(Journal, True,True); 
  end;
  ...
  Client.BatchSend(Batch);

Which effectively calls InternalBatchStart()/InternalBatchStop() pair for every single row in the batch (Didn't know then about 2-nd level batching). So the  ESQLite3Exception fires exactly in call to InternalExecute() at TSQLRestServerDB.InternalBatchStop, line 1933. InternalExecute() catches it and (previously) it went unnoticed by InternalBatchStop(). Now it re-raises it, though under a different name, and this is an improvement.

It is getting better

Now I'm concerned about the semantics of the boRollbackOnError batch option. I have tried my code and now it correctly rollbacks the whole batch, but I didn't include boRollbackOnError into the batch options!

That happened because the call of InternalBatchStop() at line 43738 raises EORMBatchException way before checking the option at line 43790 and no matter the option was included or not - gets caught at line 43810 and everything roll-backed.

May be an additional try ... catch will be needed with a quick logic to mute the EORMBatchException in case the boRollbackOnError was not included.

PS: Noticed that PerformAutomaticCommit at line 43714 also calls InternalBatchStop with possibly the same effect.

PPS: About the Results[] array and multiple POSTs in a same table:

Browsing through TSQLRestServerDB methods, InternalBatchStart/InternalBatchStop pair and enclosed MainEngineAdd I saw that all INSERTs are merged in one single INSERT in form:

  INSERT INTO table (col1,col2,...) 
  VALUES
    (v1, v2,...),
    (v1, v2,...),
    ...

Taking in mind the MainEngineAdd() will return the ID from the Batch JSON (which was generated or supplied, it doesn't matter), the Results[count] will be assigned to the returned value and OK := true. With ESQLite3Exception raised at the time of  InternalBatchStop, the whole statement will fail, i.e. no rows will be inserted from the time of the last InternalBatchStart but the Results[n..n+m] will hold some 'good looking' IDs, which in fact are not IDs of successfully inserted rows. Thus, the caller can not rely on contents of the Results[].

Regards,

The exception got caught in except clause in mORMotSQLite3.pas, line 1212. Then TSQLRestServer.EngineBatchSend() continues execution normally and even processes the following "POST@..." records before making commit. The except clause in the file mORMot.pas, line 43800 has no chance, since there is no re-raise for the exception.

Here is (almost) the same stack dump with SynopseCommit.inc='1.18.4314':

#0 fpc_raiseexception at :0
#1 SQLITE3_CHECK(78104964, 19, 0xd85e84 'Step') at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5345
#2 TSQLREQUEST__STEP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SynSQLite3.pas:5264
#3 TSQLRESTSERVERDB__INTERNALEXECUTE(0x63b1084 'INSERT INTO Barcodes (RowID,BarcodeNumber,Barcode,Range,PriceID,UserID,Active,DateUpload) VALUES (:(3628054892):,:(1000114):,:(''333''):,:(''123''):,:(615024892):,:(580044892):,:(1):,:(135432434243):);', true, 0x0, 0x0, 0x0, 0x0, 0x0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1190
#4 TSQLRESTSERVERDB__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMotSQLite3.pas:1933
#5 TSQLSERVERMIST__INTERNALBATCHSTOP(<error reading variable>) at C:\AI\_\MIST\zoo\Model\DataModel.pas:2107
#6 TSQLRESTSERVER__ENGINEBATCHSEND(<incomplete type>, 0x634b604 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', 0x634bb40, 0, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:43734
#7 TSQLRESTSERVER__BATCH(0x62600a8, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:42499
#8 TSQLRESTSERVERURICONTEXT__EXECUTESOABYMETHOD(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40513
#9 TSQLRESTSERVERURICONTEXT__EXECUTEORMWRITE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40981
#10 TSQLRESTSERVERURICONTEXT__EXECUTECOMMAND(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:40397
#11 TSQLRESTSERVER__URI({URL = 0x4948ab4 'client/Batch', METHOD = 0x495225c 'PUT', INHEAD = 0xd548c0 'RemoteIP: 127.0.0.1', INBODY = 0x634b604 '["automaticTransactionPerRow",2147483647,"POST@Barcodes', OUTHEAD = 0x0, OUTBODY = 0x0, OUTSTATUS = 400, OUTINTERNALSTATE = 73, RESTACCESSRIGHTS = 0xbe10a0, LOWLEVELCONNECTIONID = 400, LOWLEVELFLAGS = [LLFSECURED]}, <error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:42088
#12 TSQLRESTSERVERNAMEDPIPERESPONSE__INTERNALEXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:44341
#13 TSQLRESTTHREAD__EXECUTE(<error reading variable>) at C:\AI\IDE\fpcupdeluxe\ccr\mORMotFPC\SQLite3\mORMot.pas:37236
#14 CLASSES$_$TSTRINGLIST_$__$$_SORT at :0
#15 ?? at :0

The reason I didn't specify a table in TSQLRestBatch.Create(Client, Nil, MaxInt), is the batch actually contains 'POST@...' in several tables. I'have simplified the example to post it in the forum.

Regards,

I beg you pardon!
Can't even compile it on intel x64-bit Lindows fpc.
Define "even work", please!

@ab
No, that is not the issue, sorry ... It's just full of tricks which just don't work on FPC It can't work reliably on the same OS/Arch (Win/x86). It is up to my regret, too

Dear ab,

Perhaps I'm adding a unnecessary fuzz here, but ... FPC wiki says: String is the same as AnsiString (with {H+}) and AnsiString is actually AnsiString(CP_ACP). In my case CP_ACP<>CP_UTF8 as probably in your case. One of my projects is already almost broken because of the new FPC way of dealing with strings. In that project I'm using mORMot and since it requires FPC 3.0+, my forms got weird messages with half readable letters and the other half cryptic. 

Anyway, Isn't it better for RawUTF8 to be AnsiString(CP_NONE) to bypass the internal conversions (and despite it's name)?

Found workable solution (requires TSQLRestClient):

with Rest.ListFmt([TSQLTable], 'COUNT(*)', 'Status=%', [requiredStatus]), []) do 
begin
  Result := GetAsInteger(1, 0);
  Free;
end;

Anything simpler?

It is okay now. Thanks again!