TSQLDBSQLite3ConnectionProperties.mainconnection transaction

mariomoretti · 2016-08-14 05:54:25

Hi,

in my project one can decide to use sqlite or an external db via ODBC.

On server side, to uniform db access , I create a TSQLDBSQLite3ConnectionProperties or a TODBCConnectionProperties to access data and use theirs Mainconnection to start a transaction when needed.

        if Params.ExternalDB then
             fProdConn := TODBCConnectionProperties.Create(Params.DBName, '', Params.DBUser, Params.DBPwd)
        else
            fProdConn := TSQLDBSQLite3ConnectionProperties.Create(StringToUtf8(DataBaseName),'','','');

        fProdconn.MainConnection.Connect;
        fConn := fProdConn.MainConnection;

fConn is the connection used by the server to start a transaction.

The project initially used only sqlite as database, so there was no a connection, but the transactions where started via the TSQLRestServerDb.TransactionBegin, now it uses fConn.StartTransaction.

When installing , the program asks to define where host the tables, then creates and populates some of them with data , in a whole transaction : start a transaction, insert all rows and finally commit.

There are minus then one thousand rows to add and the inserting time was of few seconds using sqlite and TSQLRestServerDb.TransactionBegin.

Now , using fConn.StartTransaction , via ODBC take the same little time, but using sqlite , the inserting time results in more than two minutes and I've noted that it continuosly creates and deletes DatabaseName.journal on disk.

Probably there is something that i'm wronging , may someone help me ? Tx.

I'm using last revision of the framework.

ab · 2016-08-14 08:24:11

My guess is that the transaction is not really started on SQLite3, so it waits to flush every single insertion to disk, so it is much slower.
Check around the StartTransaction: I guess you are mixing/confusing to diverse Sqlite3 "connections", i.e. two TSQLite3DB instances.

mariomoretti · 2016-08-15 01:08:33

You are right,i'm using two diverse connection, because i'm confusing direct sql via the connection and ORM.

I need to use direct sql because the use of queries too complex for ORM.

I'll try to use only one of the two method in a transaction.

tx very much.

mariomoretti · 2016-08-16 11:03:38

Hi AB,

i'm having trouble using a connection to access database (else sqlite or external).

Certainly i'm doing something of wrong, but cannot resolve the issue, so i've reproduced this little example in which I show how i'm defininig a TSQLDBSQLite3ConnectionProperties, used to access data when queries are too complex and a TSQLRestServerDB for ORM

Till when I don't fire a select (i.e. till when i don't use directly the connection) , all works fine, but, after the usage of a dbConn.execute, accessing data with ORM results in a unpredictable error : in this example , trying to update a blob field (only a blob) via ORM results in this error : Error SQLITE_BUSY (5) [Step] using 3.13.0 - database is locked, extended errcode=5.
Using ODBC also i've similar problems.

Coud you tell me how i should correctly define this scenario ?
tx very much.

unit Unit1;

interface

uses
  {$ifdef MSWINDOWS}
  Windows,
  Messages,
  Graphics,
  {$endif}
  Classes, SysUtils, Forms, Controls, Dialogs, StdCtrls,
  SynCommons,
  SynDB,
  mORMot,
  mormotsqlite3,
  synDBsqlite3;

type

  { TForm1 }

  TForm1 = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
         DBConn : tSQLDBConnectionProperties;
         fConn : TSQLDBSQLite3Connection;
    { private declarations }
  public
    { public declarations }
    Database: TSQLRestServerDB;
    Model: TSQLModel;
  end;

  TSQLSampleRecord = class(TSQLRecord)
  private
    fData     : RawUtf8;
    fBlobData : RAwByteString;
  published
    property Data : Rawutf8 read fData write fData;
    property BlobData : RawByteString read fBlobData write fBlobData;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
var Model : tSQLModel;
    s : tSQLSampleRecord;
    rows : isqldbrows;
    V : Variant;
    data : RawByteString;
begin
  model := tSQLModel.Create([tSQLSampleRecord]);
  DataBase := tSQLRestServerDB.Create(model,'test.db3');
  DataBase.CreateMissingTables(0);
  s := tSQLSampleRecord.Create(Database, 1);
  s.Data := 'data';
  s.BlobData := 'blobdata';
  if s.ID = 0 then Database.Add(s, true);
  s.Free;
  dbConn := TSQLDBSQLite3ConnectionProperties.Create('test.db3','','','');
  dbconn.MainConnection.Connect;
end;




procedure TForm1.FormDestroy(Sender: TObject);
var Model : tSQLModel;
    s : tSQLSampleRecord;
    rows : isqldbrows;
    V : Variant;
    d : Rawutf8;
    blobd : rawbytestring;
begin
     // here the select (if not executed, updateblobfields works fine.
     rows := dbconn.execute('select data from ' + tSQLSampleRecord.SQLTableName, [], @V);
     if rows.Step then
        d := rows.ColumnUTF8(0);
     rows := nil;

     s := tSQLSampleRecord.Create(Database, 1);
     s.data := 'òaiosufvbnsliughjwrpt90rp35';
     s.blobdata := 'òaifgdffosufvbnsliughjwrpt90rp35';
     database.update(s);
     database.UpdateBlobFields(s);
     s.free;
     database.Free;
     model.free;
     dbconn.Free;
end;

end.

mpv · 2016-08-16 11:17:15

Current realization of SQLDBSQLite3Connection don;'t set a SQLite3 database to the WalMode, so it is impossible to access the database from the two TSQLDBSQLite3ConnectionProperties

ab · 2016-08-16 11:46:51

But I still don't understand why you are using a TSQLDBSQLite3ConnectionProperties...
You have access to the underlying DB in Database...

And you can just write:

  dbConn := TSQLDBSQLite3ConnectionProperties.Create(Database.DB);

mpv · 2016-08-16 12:23:02

@AB - Because this is not the first request for this functionality, maybe I should share my ThreadSafe implementation of SQLite3 connection? (TSQLite3ThreadSafeConnection = class(TSQLDBConnectionThreadSafe).....)

mpv · 2016-08-16 12:29:07

ab wrote:

But I still don't understand why you are using a TSQLDBSQLite3ConnectionProperties...
You have access to the underlying DB in Database...
And you can just write:
  dbConn := TSQLDBSQLite3ConnectionProperties.Create(Database.DB);

And do something like this?

Database.DB.BusyTimeout := 10000; // other thread will wait up to 10 sec if other is write something
Database.DB.WalMode := true; // in this case database is operable from other connections (SQLite3Studio, for example)

ab · 2016-08-16 13:38:57

AFAIR WalMode has nothing to do with sharing a SQLite3 connection.
It is just one way of writing the transaction journal.
Our Sqlite3.c code is built with the idea that only a single Sqlite3 connection will happen.
See e.g. #define SQLITE_OMIT_SHARED_CACHE 1

So the safest, and also the best performance, will be achieved by sharing a single Database.DB instance.

mariomoretti · 2016-08-17 04:46:06

Ok,

sorry for delay.

i'd like very much to use a single Database.Db instance !

A little resume.

I started project using only sqlite3 and ORM.

When I switched to ODBC , I reailized that some queries were too complex for ORM , so I introduced the TSQLDBConnectionProperties (also for Sqlite, to have a unique way to access data).

This introduced transaction and locking problems owing to the way I created the connection.

Now , thank to you, i've understood how to do it for sqlite3.

Using an external db , via ODBC , i've attempted various ways, but I cannot resolve how to register the virtual tables before creating the ODBC connection.

Could you , please , tell me how to create the Database without creating , before, the ODBC connection on wich register the virtual tables ? I cannot solve how to do it (i always obtain exception because external engine is unknown).

tx very much

mariomoretti · 2016-09-21 05:38:34

Hi,

i solved using transaction only with tsqlrestserverdb.transactionbegin : the complex query are needed only in select statements.

tx.

mariomoretti · 2017-05-28 23:13:12

I'm sorry to return to this issue , but i'm accidentally seen that the problem persists.
This is how i define the connection to a postgres db via odbc on server side:

       fProdConn := tODBCConnectionProperties.create(DNS, '',User, Pwd);
       .....  // add all table to model.
       fModel := CreateModel(HTTPRoot);    
       VirtualTableExternalRegisterAll(fModel, fProdConn);                 
       fServer := tSQLRestServerDB.create(fModel, databasename);  // databse name is the sqlite3 db.

I'm updating db only on server side via the tSQLRestServerDB (clients uses only services via interfaces).
In this example i'm changing the row ID in same tables reding from Jobs the tableindex, oldID and newID.
Every change is immediately visible in db, and even if i change commit with rollback, the db doesn't revert to old state.

fServer.TransactionBegin(tSQLCD_Queue, 1);
try 
   Jobs := tSQLCD_Queue.CreateAndFillPrepare(fServer,'status=1 order by id', []);
   try
      while Jobs.FillOne do
      begin
           aSQLRecordClass := fServer.Model.Tables[Jobs.TIndex];

           aSQLRecord := aSQLRecordClass.create(fServer, Jobs.OrigID);
           try
              // erase original record
              fServer.Delete(aSQLRecordClass, Jobs.OrigID);

              // Insert with new ID
              aSQLRecord.IDValue := Jobs.NewID;
              fServer.Add(aSQLRecord, true, true, False);
            finally
                   aSQLRecord.Free;
            end;
	end;
   finally
	jobs.free;
   end;
finally
	fServer.commit(1, true);
	// fServer.rollback(1);
end;

Please say me where i'm mistaking.
Tx very much.

mariomoretti · 2017-05-29 05:17:45

I've tried to put breackpoints in syndbodbc.starttransaction , commit and rollback : they are never called by fserver.transactionbegin or commit.
I searched for examples without success : please help me.
tx

mariomoretti · 2017-05-29 05:55:07

This is the trace of a fServer.TransactionBegin :

MormotSqlite3.TSQLRestServerDB.TransactionBegin

SynSqLite 3.tSQLDataBase.TransactionBegin
SynSqLite 3.tSQLDatabase.EXECUTE('BEGIN TRANSACTION')
SynSqLite 3.TSQLRequest.Execute
SynSqLite 3.tSQLRequest.Prepare

Sqlite3.prepare_v2....

mORMot Open Source

#1 2016-08-14 05:54:25

TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#2 2016-08-14 08:24:11

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#3 2016-08-15 01:08:33

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#4 2016-08-16 11:03:38

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#5 2016-08-16 11:17:15

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#6 2016-08-16 11:46:51

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#7 2016-08-16 12:23:02

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#8 2016-08-16 12:29:07

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#9 2016-08-16 13:38:57

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#10 2016-08-17 04:46:06

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#11 2016-09-21 05:38:34

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#12 2017-05-28 23:13:12

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#13 2017-05-29 05:17:45

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

#14 2017-05-29 05:55:07

Re: TSQLDBSQLite3ConnectionProperties.mainconnection transaction

Board footer