Recent

Author Topic: SQLite3 & WAL Journal Mode  (Read 10642 times)

runs

  • New Member
  • *
  • Posts: 26
SQLite3 & WAL Journal Mode
« on: October 08, 2015, 10:56:40 am »
I made "SqliteConnection.ExecuteDirect("PRAGMA journal_mode=WAL"); " before any SQL SELECT, at the beginning of all, and I get the error:
"Error: cannot change into wal mode from within a transaction"

Accordingly this post:
http://sqlite.1065341.n5.nabble.com/setting-journal-mode-while-in-a-transaction-may-behave-strangely-td77334.html
this is a normal behaviour.

So, is there a way to enter into WAL mode with Lazarus standard db components? Has anyone able to? I try to put the above sentence into BeforeConection & AfterConnnection events but it does not work.
Projects: LibreStaff

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: SQLite3 & WAL Journal Mode
« Reply #1 on: October 09, 2015, 07:30:41 am »
Executing any SQL statement automatically starts transaction.

In FPC 3.0 you can play with TSQLTransaction.Options := stoUseImplicit;

Or you can use SqliteConnection.Handle and directly pass it to sqlite3_exec() api call.

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 & WAL Journal Mode
« Reply #2 on: October 09, 2015, 10:05:13 am »
There is another PRAGMA command that suffers from this, which need to be set before anything else. That's "PRAGMA foreign_keys = on". If you look at the source you'll see there is a provision created that if you set foreign_keys=1 in the params the DoInternalConnect of TSQLite3Connection does this for you directly after the connect before any transaction is started.

Code: Pascal  [Select][+][-]
  1. procedure TSQLite3Connection.DoInternalConnect;
  2. var
  3.   filename: ansistring;
  4. begin
  5.   Inherited;
  6.   if DatabaseName = '' then
  7.     DatabaseError(SErrNoDatabaseName,self);
  8.   InitializeSQLite;
  9.   filename := DatabaseName;
  10.   checkerror(sqlite3_open(PAnsiChar(filename),@fhandle));
  11.   if (Length(Password)>0) and assigned(sqlite3_key) then
  12.     checkerror(sqlite3_key(fhandle,PChar(Password),StrLen(PChar(Password))));
  13.   if Params.IndexOfName('foreign_keys') <> -1 then
  14.     execsql('PRAGMA foreign_keys =  '+Params.Values['foreign_keys']);
  15. end;

The AfterConnect-event is just a tad too late for this. But if you create your own TSQLite3Connection.DoInternalConnect and set your PRAGMA it should work correctly.

Set this above your TForm1 declaration:
Code: Pascal  [Select][+][-]
  1. type
  2.   TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)
  3.   protected
  4.     procedure DoInternalConnect; override;
  5.   end;
  6.  
  7. type
  8.   { TForm1 }
  9.   TForm1 = class(TForm)
  10.   //...

And this below the implementation:
Code: Pascal  [Select][+][-]
  1. implementation
  2.  
  3. procedure TSQLite3Connection.DoInternalConnect;
  4. begin
  5.   inherited;
  6.   execsql('PRAGMA journal_mode=WAL');
  7. end;

Now when the connection is set your PRAGMA will be set before anything else.

Maybe, in the furture, the maintainers of TSQLite3Connection could create a generic PRAGMA option (for params) to be set in the DoConnect (and not just for the foreign_keys).

runs

  • New Member
  • *
  • Posts: 26
Re: SQLite3 & WAL Journal Mode
« Reply #3 on: October 09, 2015, 04:36:04 pm »
Thanks. It works!  8)
Projects: LibreStaff

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: SQLite3 & WAL Journal Mode
« Reply #4 on: October 09, 2015, 05:57:00 pm »
A while ago I used cascading deletes with SQLite. It needs foreign_keys enabled, which is off by default. Going to the Object Inspector and putting 'foreign_keys=on' to the Params property works. Also in this case?

After that incident I added it to the wiki:
http://wiki.lazarus.freepascal.org/MasterDetail#Delete_all_Detail_records_if_Master_record_is_deleted
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 & WAL Journal Mode
« Reply #5 on: October 10, 2015, 12:24:01 am »
A while ago I used cascading deletes with SQLite. It needs foreign_keys enabled, which is off by default. Going to the Object Inspector and putting 'foreign_keys=on' to the Params property works. Also in this case?
As you can see in my post I showed the code of TSQLite3Connection.DoInternalConnect. The developers made a special provision for "PRAGMA foreign_keys=on". But they ONLY made it for "foreign_keys". runs (OP) needed to do "PRAGMA  journal_mode=WAL". Because the developers of TSQLite3Connection haven't taken any other PRAGMA settings (other then foreign_keys=on) into account you can't set the journal_mode with the connection-params.

Hence my example of the small "hack" to override DoInternalConnect and do it there (because you already saw you can't do it with ExecuteDirect and OnAfterConnect is too late).

Until someone changes TSQLite3Connection and makes all PRAGMA-settings possible via connection.params the only way to set these PRAGMAs is via this "hack" (which works fine).


sky_khan

  • Guest
Re: SQLite3 & WAL Journal Mode
« Reply #6 on: October 10, 2015, 01:15:15 am »
a possible workaround:

SqliteConnection.ExecuteDirect("COMMIT;PRAGMA journal_mode=WAL;BEGIN")

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 & WAL Journal Mode
« Reply #7 on: October 10, 2015, 02:20:26 am »
a possible workaround:
SqliteConnection.ExecuteDirect("COMMIT;PRAGMA journal_mode=WAL;BEGIN")
That was a nice idea.
But, unfortunately, ExecuteDirect doesn't execute scripts (as far as I know). It executes one statement and here are 3 statements used.

I tested it like this:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.Button1Click(Sender: TObject);
  2. begin
  3.   SQLite3Connection1.Connected:=true;
  4.   SQLite3Connection1.ExecuteDirect('COMMIT;PRAGMA journal_mode=WAL;BEGIN');
  5.   with TSQLQuery.Create(nil) do
  6.   begin
  7.     Database := SQLite3Connection1;
  8.     SQL.Text := 'PRAGMA journal_mode;';
  9.     Open;
  10.     ShowMessage('PRAGMA journal_mode = ' + fields[0].AsString);
  11.     Free;
  12.   end;
  13. end;
It still showed
Code: [Select]
PRAGMA journal_mode = delete(and I got an error exiting the test-project)

So, we're still stuck with my little "hack" :)

sky_khan

  • Guest
Re: SQLite3 & WAL Journal Mode
« Reply #8 on: October 10, 2015, 09:41:48 am »
Well then, lets do it properly this time  :-[

Code: Pascal  [Select][+][-]
  1. uses
  2.    sqlite3dyn;
  3.  
  4. procedure TForm1.ExecSQL3(Conn: TSQLite3Connection; const aSQL: string);
  5. var
  6.   SL3Handle:Pointer;
  7.   err_msg : PChar;
  8.   msg : string;
  9. begin
  10.   SL3Handle:=Conn.Handle;
  11.   if sqlite3_exec(SL3Handle,@aSQL[1],nil,nil,@err_msg) <> SQLITE_OK then
  12.   begin
  13.     if err_msg<>nil then
  14.     begin
  15.       msg:=err_msg;
  16.       sqlite3_free(err_msg);
  17.     end;
  18.     raise Exception.Create('Query failed: '+ msg);
  19.   end;
  20. end;
  21.  
  22. procedure TForm1.Button1Click(Sender: TObject);
  23. begin
  24.   SQLite3Connection1.Connected:=true;
  25.   ExecSQL3(SQLite3Connection1,'PRAGMA journal_mode=WAL');
  26.   with TSQLQuery.Create(nil) do
  27.   begin
  28.     Database := SQLite3Connection1;
  29.     SQL.Text := 'PRAGMA journal_mode;';
  30.     Open;
  31.     ShowMessage('PRAGMA journal_mode = ' + fields[0].AsString);
  32.     Free;
  33.   end;
  34. end;
  35.  

« Last Edit: October 10, 2015, 09:49:48 am by sky_khan »

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: SQLite3 & WAL Journal Mode
« Reply #9 on: October 10, 2015, 09:58:29 am »
Well then, lets do it properly this time  :-[
Yep, that works much better  ;)

Although one small remark. I wouldn't make it a function of TForm1. It's not needed and works just fine as a standalone function. That way you could make this a global function in your program (in a utils-unit for example):
Code: Pascal  [Select][+][-]
  1. procedure ExecSQL3(Conn: TSQLite3Connection; const aSQL: string);
  2. var
  3. //...

 

TinyPortal © 2005-2018