#1 2017-06-07 05:25:36

mariomoretti
Member
From: italy
Registered: 2012-01-29
Posts: 88

using sqlite or external database

I've started a project  where you can configure at startup if use a sqlite or an external database.
On server side , i'm using a tSQLRestServerDB to access the data , created using two distinct approach owing to the case :

         fModel := CreateModel(aRoot);
         fProps := nil;
         if aDNS > '' then  // ODBC
         begin
              fProps := TODBCConnectionProperties.Create(aDNS, '',aUser, aPwd);
              VirtualTableExternalRegisterAll(fModel, fProps);
         end
         else fProps := TSQLDBSQLite3ConnectionProperties.Create(aDBFileName,'','','');

         inherited create(fModel, aDBFileName);
         if aDNS > ''  then
         begin
              // optionally execute all external DB requests in a single thread
              AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
              AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
         end;
         CreateMissingTables(0);

it seems to work , in the sense that the tables are created, updated etc... but , when i started to use an external database i've found some issues :

using server.transactionbegin and commit or rollback doesn't work : on external database transactions are not started.
mainengineretrieve is not called : i have found in mormot.pas  :    /// overridden methods for direct sqlite3 database engine call: 
Obviously I need to override it using sqlite or external tables.

After days I've found a workaround to solve the first using fProp.Mainconnection.StartTransaction /commit/rollback for estrernal db and transactionbegin for sqlite.
But i don't know how to solve the second.

Is there anybody knowing a solution ?

tx very much

Offline

#2 2017-06-07 07:33:15

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

Re: using sqlite or external database

First of all, what I do currently is not to inherit from TSQLRestServerDB, but use a TSQLRest field and fill it with a TSQLRestServerDB instance - sometimes using a TSQLRestServer field.
To separate logic and storage. You may use for instance a remote DB later on.

Don't create an external SQLite3 database.
TSQLRestServerDB has already a Sqlite3 engine running, and its own database: no need to use SynDB here.

About transactions, the way to go is to use a TSQLRestBatch, not create manual transactions.
I will check why external transactions are not triggered, but they should.

Offline

#3 2017-06-07 14:09:56

mariomoretti
Member
From: italy
Registered: 2012-01-29
Posts: 88

Re: using sqlite or external database

TX AB,

AB wrote:

First of all, what I do currently is not to inherit from TSQLRestServerDB, but use a TSQLRest field and fill it with a TSQLRestServerDB instance - sometimes using a TSQLRestServer field.
To separate logic and storage. You may use for instance a remote DB later on.

Don't create an external SQLite3 database.
TSQLRestServerDB has already a Sqlite3 engine running, and its own database: no need to use SynDB here.

Sorry but I don't understand what you are telling me. I imagine how busy you are, but could you be so kind to show me an example ?

AB wrote:

About transactions, the way to go is to use a TSQLRestBatch, not create manual transactions.

I can't use a tSQLRestBatch because sometimes  , in the same transaction, i need to know the ID of an inserted record to fill a property of another class before inserting it (I've tried to define it as tSQLRecord, but in some circumstances i can't do it). If I have well undertstood the batchsend operates a commit, so, using batch,  it's impossible to grant that all data are persisted togheter.

Offline

Board footer

Powered by FluxBB