mORMot Open Source

VojkoCendak

Member

From: Celje Slovenia

Registered: 2012-09-02

Posts: 88

SQLite Journal and smOff

Hi, (Delphi2009 Ent).

We have several questions:
We'd like to use SQLite for several reasons, especially because it is simple and no installation and unlimited size...
Main concern is write lock on the db serverside.

1. help using SQLite3 db scenario:
  FDB := TSQLRestServerDB.Create(FDBModel,MyModulePath+'\data\'+MyExeName+'.db3',False);
  FDB.CreateMissingTables(0);
  FDBServer := TSQLite3HttpServer.Create('8081',[FDB]);
<--- can I apply some write lock on table ?

regarding client taht appends records in server I use this one:
<-- can I use something directly?
            ldbclient := TSQLite3HttpClient.Create(aHost,'8081',CreateTrendModel);
            try
//              ldbclient.Server.DB.Synchronous := smOff;
              ldbclient.TransactionBegin(TSQLTrendRecord);
              ldbclient.BatchStart(TSQLTrendRecord);
              lrec := TSQLTrendRecord.Create;
              try
                while lvals.Count>0 do
                begin
                  try
                    lrec.TagName := lvals[0].TagName;
                    ldbclient.BatchAdd(lrec,True);
                  finally
                    lvals.Delete(0);
                  end;
                end;
              finally
                lrec.Free
              end;
              ldbclient.BatchSend(ida);
              ldbclient.Commit();
             Finally
                ldbclient .Free
             end;

2. Where can we apply or find smOff (for Synchronous) in this scenario?

3. Deleting data with Query: How?

    lsql := 'Delete From TrendRecord Where TimeStampDbl <= '+FloatToStr(dtDo);
    FDB.EngineExecuteAll(lsql);
But we find that it doesn't always delete data?

thank you for your patience and help,
Vojko

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: SQLite Journal and smOff

1. There is not file-level LOCK available implemented yet.
Our SQlite3 engine is compiled with some options which may tend to avoid use of concurrent access to the DB file.
I do not know if it is possible at SQLite3 level, perhaps with a #pragma.

2. DB.Synchronous := smOff shall be set on the Server side, in your custom TSQLRestServer custom sub-class.

3. You have to such Delete() method with a WHERE clause for the ORM methods, currently. Perhaps we should add it.
The "ORM-way" should be to retrieve all matching IDs in an array of integer, then call BatchDelete() over the IDs. It won't be much slower, and will ensure that callbacks expected on deletion (e.g. for data cascade coherency) are played.

By now, you should also implement a Service - defined e.g. as DeleteDeprecatedTrendRecord(dtDo: TDateTime), then run the SQL statement by hand, in your TSQLRestServer instance.
This is IMHO better than calling plain SQL from client, for security reasons. By default, you are not allowed to launch such statements from clients.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,238

Website

Re: SQLite Journal and smOff

I've added a new TSQLRest.Delete() overloaded method with a WHERE clause parameter.
See http://synopse.info/fossil/info/1cb51e996f

It could be used for your exact needed purpose.

In addition to a direct SQL statement defined as a service, it will also trigger all ORM-related methods to process the expected actions:
- refresh internal caching;
- process internal notifications;
- launch coherency checking after deletion.

You should better use this new TSQLRest.Delete() than a manual SQL statement.