mORMot Open Source

Hi,

Please can you add support of sqlite function: sqlite3_interrupt
https://www.sqlite.org/c3ref/interrupt.html

Without this function it's not possible to abort long queries performed in threads via TSQLDBSQLite3ConnectionProperties.

Or maybe I missing something ?

Hi,

I trying to implement my own virtual table module, but while executing statement:

Execute('CREATE VIRTUAL TABLE test USING mylog(temptest.log);');

I got error: no suh module 'mylog'

In documentation I read:

7.3.2. Defining a Virtual Table module
Here is how the TSQLVirtualTableLog class type is defined, which will implement a Virtual Table module named "Log". Adding a new module is just made by overriding some Delphi methods

But where to define module name "Log" ??? (in my case - "mylog") ?
No info about it in documentation ! And I cant find "Log" module name in TSQLVirtualTableLog in mormot sources !

Thanks for help !

Hi,

I updated my FPC/Lazarus to FPC 3.3.1 (trunk), Lazarus 2.0.11 (fixes) and also download latest mORMot trunk and I have sigsegv in SynCommons.GetEumName.

Here is a output of TestSQL3:

[dev@localhost x86_64-linux]$ ./TestSQL3
An unhandled exception occurred at $000000000050DC37:
EAccessViolation: Access violation
  $000000000050DC37  GETENUMNAME,  line 21623 of SynCommons.pas
  $0000000000519CAF  TOTEXT,  line 27165 of SynCommons.pas
  $0000000000519E81  SETLINUXDISTRIB,  line 27455 of SynCommons.pas
  $000000000051A7BF  RETRIEVESYSTEMINFO,  line 27532 of SynCommons.pas
  $0000000000595716  SYNCOMMONS_$$_init$,  line 63242 of SynCommons.pas
  $000000000041E09C

Or I should report about it to FPC bugtracker ?

Hi !

I trying to move one of my projects to mORMot for better performance. This project is relatively complex, for now I try to move
only DB access to SynDBSQlite. I can't use ORM features now because it's requires total project rewrite. I hope starting from DB layer makes me more familiar with framework and I can use ORM features in another prjects.

I have several questions about proper SynDBSQlite usage in threads. mORMot documentation is great but not cover this moments:

1. I have global connection object somewhere in main unit:

GlobalConnection:=TSQLDBSQLite3ConnectionProperties.Create(MyDataPath+'common.db','','','');

How to use it right in threads ?

Method A:

procedure AccessDBInThread;
var
  ThreadConnection: TSQLDBSQLite3Connection;
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadConnection:=GlobalConnection.NewConnection; // call this method if the shared MainConnection is not enough (e.g. for
                                                                             // multi-thread access)
ThreadStatement:=ThreadConnection.NewStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free;
  ThreadConnection.Free;
end;
end;

Method B:

procedure AccessDBInThread;
var
  ThreadConnection: TSQLDBSQLite3Connection;
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadConnection:=GlobalConnection.ThreadSafeConnection; 
ThreadStatement:=ThreadConnection.NewStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free; 
  ThreadConnection.Free; // should be freed ?
end;
end;

Method C:

procedure AccessDBInThread;
var
  ThreadStatement: TSQLDBSQLite3Statement;
begin
ThreadStatement:=GlobalConnection.NewThreadSafeStatement;
try
  // Prepare, bind, execute statement
finally;
  ThreadStatment.Free;
end;
end;

Method D:

procedure AccessDBInThread;
begin
// Can I safe use GlobalConnection in threads this way ? :
with GlobalConnection.ExecuteInlined('SELECT * FROM Table',true) do
   begin
   Step;
   // process
   end;
end;

Usage scenario: AccessDBInThread procedure is api request handler, it's executes in existing non-mORMot based api server's thread.

What method (A,B,C) is proper, best and fastest  for threads ? Maybe all is wrong and I must use something different ?
Is statement cache will works in all A,B,C methods ?
Can I use connection's MainSQLite3DB.LastInsertRowID, LastChangeCount, TotalChangeCount properties if I obtain thread statements via GlobalConnection.NewThreadSafeStatement (Method C) ?
Can I use method D in threads or I should create TSQLDBSQLite3ConnectionProperties for each thread ?

2. How locking mechanism works ?

If I understand correctly, after I obtain thread-local statement and/or connection object they have exclusive DB access locked by
RTLCriticalSection in SynSQlite3.pas. But how exactly locking works (how long lock is active) ?

Is database locked only between ThreadStatement.Execute and ThreadStatement.Reset or ThreadStatement.Free ?

I ask because I need to know how to implement long batch operations like importing million records from file. I need to sleep batch thread each time after importing several thousands records to let other threads do their job on unlocked database.

But where to Sleep ? Where lock is released ?
Just after ThreadStatement.Reset ? Or just after ThreadStatement.ExecuteNoResult, etc ?

3. Locking and transactions

Is database access always serialized for all scenarios ?

For example can I run this query in Thread A:

ThreadAConnection.StartTransaction;
ThreadAStatement.ExecuteNoResult('ALTER TABLE MyTable...'); // this requires exsclusive lock because sqlite_master is changed
ThreadAConnection.Commit;

and this query in Thread B at same time:

ThreadBConnection.StartTransaction;
ThreadBStatement.Reset;
ThreadBStatement.Bind;
ThreadBStatement.ExecuteNoResult('INSERT INTO MyTable...'); // do 5000 batch inserts per transaction to same table !
ThreadBConnection.Commit;
// repeat until 1M records imported...

My connections will use LockingMode:=lmExclusive, so my app have exslusive access to database file. Concurency is only in my threads.

Should I need any additional errors handling or all access always serialized in SynSQlite3.pas and I no need to worry about concurency  errors handling like SQLITE_LOCKED, SQLITE_BUSY ?

If I manually start transactions in Thread A and Thread B is I need dedicated connection objects for each thread ?
Or I can get threads statements via GlobalConnection.NewThreadSafeStatement but start transactions via GlobalConnection.MainConnection.StartTransaction ?

Thanks for help and I hope my questions is not to stupid ) Of cource I looking mORMot source but they big and complex, so I ask to be sure.

Hi,

I use TZipWrite to make large zip archive contains about 36k of small files. Result zip file is about 1 gb size.

But while compressing TZipWrite eat about 6.5 GB of RAM !!!! It's to much for my app.

I Use TZipWrite this way:

procedure TSaveThread.SaveMessages;
var
  MsgSource:TMemoryStream;
  Zipper:TZipWrite;
begin
MsgSource:=TMemoryStream.Create;
Zipper:=TZipWrite.Create('SomeFile.zip']);
try
  LocalQuery.SQL.Add('SELECT MsgSource FROM MsgList'); // LocalQuery is ZEOS TZQuery
  LocalQuery.Open;
  while not LocalQuery.EOF do
    begin
    MsgSource.Clear;
    TMemoField(LocalQuery.Fields.Fields[0]).SaveToStream(MsgSource);
    MsgSource.Position:=0;
    Zipper.AddDeflated(GenerateFilePathAndName+'.eml',MsgSource.Memory,MsgSource.Size,9);
    LocalQuery.Next;
    end;
finally
  Zipper.Free;
  MsgSource.Free;
  end;
end;

How to reduce memory usage while creating large zip archive ? 500-700 mb is Ok for me, but not 6.5 gb !

My enviroment: CentOS 7 x64, Lazarus 1.8RC5/FPC 3.0.4RC1, target Linux64

Hi, Arnaud and all !

Please can anybody give me more details about using Sqlite via SynDBSqlite3 in multithreaded server (sorry, I can't find needed info in great mORMot documentation):

1. My sqlite3.dll compiled with SQLITE_THREADSAFE=2 option (multithreaded). Should I create TSQLDataBase and TSQLDBSQLite3ConnectionProperties in each thread ? Or I must create it only one time and share between my threads ?

2. Does SynDBSqlite/SynSqlite3 opens sqlite database in WAL journal mode by default (where writers not block readers) ? Or I should switch to WAL mode manually for best concurency and performance ?

3. Should I worry about serializing access to database and use CriticalSection (or MREW for WAL mode) ?

4. Does SynDBSqlite/SynSqlite3 handle SQLITE_BUSY and/or SQLITE_LOCKED errors ? For example, I have several read threads and one writer thread who want to alter some table and add additional field. During ALTER TABLE sqlite_master table is modified and readers may get busy/locked error because of it. How to avoid it ? Use CriticalSection or MREW myself to serialize access, handle busy/locked errors then try again or restart transaction, or SynDB framework do this job automatically ?

5. I see this comment in SynSqlite3.pas:

//  TSQLDataBase is simple wrapper for direct SQLite3 database manipulation
// - embed the SQLite3 database calls into a common object
// - thread-safe call of all SQLite3 queries (SQLITE_THREADSAFE 0 in sqlite.c)

And also I see CriticalSection usage in this class.

Is it's means I can't use Sqlite with SQLITE_THREADSAFE=2 + WAL option and can't read database from several threads (and write from single thread) in same time for best server performance ?

Or (maybe) SQLITE_THREADSAFE=2 + WAL is not best options for multithreaded server ?

My app server needs this operations on database:

- 50% is fast reads (simple selects with LIMIT/OFFSET for pagination)
- 30% is fast insert/update/delete (single record)
- 18-19% is batch insert/update/delete (100k-500k records, 5000 records per transaction)
- 1-2%  is operations like ALTER TABLE requires exclusive lock on entire database.

I think using WAL mode and MREW lock for reads and writes and some global lock like critical section for exclusive operations like ALTER table. But I dont know is this needed with framework ? Or maybe it's not needed at all and simple CriticalSection to serialize everything gives best results ? At this time I not use ORM features, only SynDBSqlite/SynSqlite3.

Many thanks for help !

Hello,

I found mORMot few years ago and want to use it very much but... My project written in C++Builder :-( I and other peoples on this forum try to compile it for BCB with no success because of heavy Object usage wich is not supported in C++.
But I try to do it with every RAD Studio release. Few days ago I make another attempt with latest mORMot build and C++Builder Berlin and almost make it works ! At least I can generate .hpp and .o files, rewrite 01 - In Memory ORM sample in C++ and compile/link/run it in BCB ! And it's paritally works (successfuly save to TSQLRestStorageInMemory but AV on retreive, possible due to constructors problem, see bellow).

This changes in mORMot needed for base BCB compatibility:

SynBidirSock.pas: change TWebSocketProcessSettings from Object to Record
SynZipFiles.pas: change TSynCompressionAlgos, TBlobData, TMemoryMap from Object to Record.
mORMot.pas: change TClassInstance, TSQLRibbonTabParameters, TSynMonitorUsageID, TServicesPublishedInterfaces, TServicesPublishedInterfaces from Object to Record.

This changes NOT produce any bugs (TestSQL3 works same as unmodified version) but allow to compile mORMot files with "Generate C++ .objs, headers, namespaces, export" option. Of course generated .hpp files not compiles becase SynWinSock.pas and mORMotService.pas contains lot of Constants wich conflict with winsock.h, winbase.h, etc, but it's can be easy fiexed by comment out or add lots of {$EXTERNALSYM} to mORMot sources. There is also some issues with IInvokable and _di_IInvokable but it's can be easy fixed to.

The biggest problem in BCB is TSQLRecord constructors: in C++ constructors can't have different names like in Pascal. For example this TSQLRecord constructors:

constructor CreateFrom(const JSONRecord: RawUTF8); overload;
constructor CreateAndFillPrepare(const aJSON: RawUTF8); overload;

Will be translated to C++ as:

__fastcall TSQLRecord(const Syncommons::RawUTF8 JSONRecord);
__fastcall TSQLRecord(const Syncommons::RawUTF8 aJSON);

This is not valid in C++ because overloaded constructors can't have same parameters and their types !

Same problem with SynZipFiles.TZipCompressor:

constructor Create(outStream: TStream; CompressionLevel: Integer; Algorithm: integer=0);
constructor CreateAsBlobData(outStream: TStream; CompressionLevel: Integer; Algorithm: integer=0);

Accoridng to this arcticle: http://community.embarcadero.com/blogs/ … lder-22631

"So, apart from what the JVCL pages describe, you should also avoid using constructors with the same signature but differing in name. It is best if you simply do not use any other constructor name than Create. If you need more than one, be sure to use the overload directive."

But it's not possible to just rename all TSQLRecord constructors to .Create because it's break entire framwork and documentation :-(

Maybe Arnaud or others can suggest some workaround ? This issues probably not fatal and if fixed mORMot can be also used in C++Builder !

Hi,

mORMot is very nice framework ! I want to use it in my project very much but I can't because I have to
many legacy C++Builder code. So, I need to use it from BCB :-(

Current SynCommon.pas version cannot be compiled for BCB because it's use Object type. This type cannot be translated while generating C++ .hpp headers.

I was try to modify your code but found to many problems with TDynArray and THashedDynArray.

Can you update code and use Classes instead of Objects if I pay/donate for it ?

How much I must pay ?

You can contact me by email - support[at]bspdev.com

Thank you !