Current recommendation of SQLite3 class usage

greedy · 2018-10-26 19:20:58

Hello,
I've started with reading about mORMot and want to start using it in my (old) application. But adapting everything to ORM is quite a huge task, so I want to get rid of my old implementation of a SQLite3 library first in most places as it's buggy since I changed to unicode. Afterwards I'll decide where it makes sense to use ORM and adapt my code for it.
I've searched for some examples which use the SQLite3 from mORMot (SynSQLite3 + SynDBSQLite3) but haven't found anything which is helpful for me.

1.
I'd like to use ':param' in my query and then set the values via ParamByName/FieldByName and afaik only TQuery seems to support it? But I also read that mORMot TQuery is much faster than default Delphi one but I also read that this was changed (lately) and it's slow again now. What's correct? Are there any other ways for simpler use of querys? I only knew about bind/column as an alternative but the syntax is not really easy to read if you have many (5+) values.
Another thing I tested is to use a Variant; I create a TSQLDBStatement with NewThreadSafeStatement before. Seems readable too, what should I prefer?

2.
Using of TSQLDBSQLite3ConnectionProperties is still correct?
Afterwards I use MainSQLite3DB.Execute to create my table.

3.
As I have a multi-thread application, anything I need to take care about? Or is using TQuery or the Variant way with TSQLDBStatement (with NewThreadSafeStatement) enough?

4.
I've seen that your SQLite3 uses an internal lock, why does it use it? Are there any plans to change to the new sqlite _v2 functions which supports passing multi-thread config to it? Or don't use locks if sqlite is compiled in serialized/multi-thread mode? As in those modes, sqlite takes care of locks.

Best Regards,
greedy

ab · 2018-10-26 20:17:40

1. To my knownledge nothing has changed in mORMot TQuery, so it should be as fast as it was - or perhaps even faster than before.
Of course, SynDB or SynSQLite3 are faster than TQuery, since they don't use intermediate storage.
In both SynDB and SynSQLite3, parameters are expected to be positioned by order, using ? in the statement then bound by index.

2. For SynDB classes, yes it is correct.

3. NewThreadSafeStatement is enough. But for SQLite3 it returns the same instance on all threads, IIRC.

4. There is a lock in SynSQLite3 main DB class - it is on purpose for the internal caches of this class: one prepared statement cache, and one JSON results cache.
But in practice, it is not a problem, and the benefice of the caches is worth it.

greedy · 2018-10-26 20:52:07

1.
Are there any benchmarks for mORMot TQuery vs. direct access (SynDB)? But I don't think that it's really relevant for my application as it'll be in the range of ms I guess.
Is there no way to use ':paramname' in my query instead of '?' and then call bind()?
What about the method with a Variant? The call to the entries must be positioned by order, too?

4.
First time that I hear about the internal cache for prepared statements.
Is it better to use a separate TSQLDBSQLite3ConnectionProperties for each statement (SELECT / INSERT)? As I'll have two or three different SELECTs and one INSERT, only parameters will change.

ab · 2018-10-27 07:52:52

1. Use the doc and the source to find out.

4. Don't make premature optimization!
If you use SQLite3, you will have tremendous performances (a select is a few microseconds), since it will run in-process, with no network involved.
Opening several instances to a single SQLite3 database will slow down the process. So it is a wrong idea.

greedy · 2018-10-27 17:53:04

1.
I already did but haven't found any benchmarks which compares TQuery vs. other access.
As I haven't found any examples with using :params with other classes, I assume it's only supported by TQuery.
I think the examples in docs for Variant are not sufficient to answer my question...or not showing it means it does not support it in different order?

4.
Thanks, haven't thought about this but your absolutely right.

greedy · 2018-10-30 20:13:47

I've created a small application to test the multi-threading but I can't get it to work without errors when writing data.

I create a number of threads for read/write operations as following:

// global variable
C := TSQLDBSQLite3ConnectionProperties.Create('name.db', '', '', '');

// reading thread
Q1 := TQuery.Create(C.ThreadSafeConnection); // does also work flawless with .NewConnection
Q1.SQL.Text := 'SELECT * FROM USERS';
try
  Q1.Open;
  While not Q1.EOF do
  begin
    Inc(i); // counts the entries
    Q1.Next
  end;
  Q1.Close;
except on E: Exception do
  Writeln(E.Message);
end;

// writing thread
Q1 := TQuery.Create(C.ThreadSafeConnection); // even errors with .NewConnection
Q1.SQL.Text := 'INSERT OR IGNORE INTO USERS (U_NAME) VALUES (:randomname)';
Q1.ParamByName('randomname').AsString := 'username' + IntTostr(RandomRange(0, 4096));
try
  Q1.ExecSQL;
except on E: Exception do
  Writeln(E.Message);
end;

The errors I get are:

Error SQLITE_BUSY (5) [Step] using 3.25.2 - database is locked, extended_errcode=5
Error SQLITE_BUSY (5) [Step] using 3.25.2 - another row available, extended_errcode=100
TSQLDBSQLite3Statement.Step(SeekFirst=true) not implemented

- When WALMode enabled: (less errors but still sometimes some)

ESQLite3Exception "Error SQLITE_MISUSE (21) [bind_text] using 3.25.2 - another row available, extended_errcode=100" when binding value for parameter "randomname" in "INSERT OR IGNORE INTO USERS (U_NAME) VALUES (:randomname)"

- With C.MainSQLite3DB.Synchronous := smOff; and C.MainSQLite3DB.LockingMode := lmExclusive; (rarely errors)

ESQLite3Exception "Error SQLITE_MISUSE (21) [bind_text] using 3.25.2 - another row available, extended_errcode=100" when binding value for parameter "randomname" in "INSERT OR IGNORE INTO USERS (U_NAME) VALUES (:randomname)"

- With disabled writing threads, concurrent reading works fine

Last edited by greedy (2018-10-31 14:04:20)

mpv · 2018-10-31 09:20:31

In mORMot SQLite 3 is used in single-thread way.
Long time ago for my internal needs I wrote a Thread-safe implementation of SQLite3 access ubDBSQLite3ThreadSafe.pas
The key feature is to set a WalMode to true and BusyTimeout to something long (I set to 10 second).
I use it for regression testing and for a small productions. Not sure it can be used in hi-concurrently services

greedy · 2018-11-02 14:24:15

Might be useful to add sqlite3_threadsafe function to SynSQLite3, for checking if the used (user) library is compiled in single-thread mode because this will omit the own critical mutexing logic of SQLite. In this case, no multi-thread or serialized use case is possible!

sqlite3_open_v2() could also be called natively with

SQLITE_OPEN_NOMUTEX -> database connection to be in the multi-thread mode 
SQLITE_OPEN_FULLMUTEX -> connection to be in serialized mode

if your lib supports it.

--

At the moment, I'm not able to get it working with multiple reader and single writer (special locking used which enables this), if one writes I get an exception as above (library supports multi-threading/serializing).
I tested this with adding a third argument to

fDB := TSQLDatabase.Create(UTF8ToString(Properties.ServerName),Properties.PassWord);

but it does not work either - anything I missed?

@mpv,
any reason why you didn't tried to get it merged into mORMot?

mpv · 2018-11-02 22:34:58

In mORMot SQLIte3 is statically linked. sqlite3.o is compiled with SQLITE_THREADSAFE 1 (see `SQLite3/sqlite3.c` file for details), so we sure it's thread safe.

I'm use class from prev. post in "multiple reader + single writer" mode in productions about 4 year without problems for Gigabytes DB (you can use it in your app by removing all Tokenizer related code).
But I use it directly, without TQuery

var
  Query: ISQLDBStatement;
  conn: TSQLDBConnection;

  conn := prop.ThreadSafeConnection();
  Query := conn.NewStatementPrepared('insert into tbl (fld) values( ?)', false);
  
  conn.StartTransaction()
  Query.bindTextU(0, FormatUTF8('user%', [i]));
  Query.ExecutePrepared(); // for reader use NewStatementPrepared('..;, true); result := Query.ExecutePrepared().FetchAllAsJSON() 
  conn.Commit();

mORMot Open Source

#1 2018-10-26 19:20:58

Current recommendation of SQLite3 class usage

#2 2018-10-26 20:17:40

Re: Current recommendation of SQLite3 class usage

#3 2018-10-26 20:52:07

Re: Current recommendation of SQLite3 class usage

#4 2018-10-27 07:52:52

Re: Current recommendation of SQLite3 class usage

#5 2018-10-27 17:53:04

Re: Current recommendation of SQLite3 class usage

#6 2018-10-30 20:13:47

Re: Current recommendation of SQLite3 class usage

#7 2018-10-31 09:20:31

Re: Current recommendation of SQLite3 class usage

#8 2018-11-02 14:24:15

Re: Current recommendation of SQLite3 class usage

#9 2018-11-02 22:34:58

Re: Current recommendation of SQLite3 class usage

Board footer