mORMot Open Source

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

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

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.

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Re: using sqlite or external database

TX AB,

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 ?

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.