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.
procedure TSQLite3Connection.DoInternalConnect;
var
filename: ansistring;
begin
Inherited;
if DatabaseName = '' then
DatabaseError(SErrNoDatabaseName,self);
InitializeSQLite;
filename := DatabaseName;
checkerror(sqlite3_open(PAnsiChar(filename),@fhandle));
if (Length(Password)>0) and assigned(sqlite3_key) then
checkerror(sqlite3_key(fhandle,PChar(Password),StrLen(PChar(Password))));
if Params.IndexOfName('foreign_keys') <> -1 then
execsql('PRAGMA foreign_keys = '+Params.Values['foreign_keys']);
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:
type
TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)
protected
procedure DoInternalConnect; override;
end;
type
{ TForm1 }
TForm1 = class(TForm)
//...
And this
below the implementation:
implementation
procedure TSQLite3Connection.DoInternalConnect;
begin
inherited;
execsql('PRAGMA journal_mode=WAL');
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).