[SOLVED] SQLite Pragma's don't take

[Zvoni]

Stumbled across this with a current project.
Found this thread and rvk's "hack": https://forum.lazarus.freepascal.org/index.php/topic,29913.msg189824.html#msg189824
in my case i needed recursive triggers

Based on rvk's approach i solved it like this:

Code: Pascal  [Select][+][-]

1. Type
2.   TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)
3.   protected
4.     procedure DoInternalConnect; override;
5.   end;            
6.  
7. Implementation
8.  
9. procedure TSQLite3Connection.DoInternalConnect;
10. var
11.   i:Integer;
12. begin
13.   inherited DoInternalConnect;
14.   If Params.Count>0 Then
15.     For i:=0 To Params.Count-1 Do
16.       Begin
17.         If Params.Names[i]<>'foreign_keys' Then  //Already done in original DoInternalConnect
18.           execsql('PRAGMA '+Params[i]);
19.       end;
20. end;

Considering above: Shouldn't we provide a patch for this, along the lines like

Code: Pascal  [Select][+][-]

1. procedure TSQLite3Connection.DoInternalConnect;
2. var
3.   filename: ansistring;
4.   pvfs: PChar;
5.   i:Integer;
6. begin
7.   Inherited;
8.   if DatabaseName = '' then
9.     DatabaseError(SErrNoDatabaseName,self);
10.   InitializeSQLite;
11.   filename := DatabaseName;
12.   if FVFS <> '' then
13.     pvfs := PAnsiChar(FVFS)
14.   else
15.     pvfs := Nil;
16.   checkerror(sqlite3_open_v2(PAnsiChar(filename),@fhandle,GetSQLiteOpenFlags,pvfs));
17.   if (Length(Password)>0) and assigned(sqlite3_key) then
18.     checkerror(sqlite3_key(fhandle,PChar(Password),StrLen(PChar(Password))));  
19.       //Change starts here
20.   If Params.Count>0 Then
21.      For i:=0 To Params.Count-1 Do
22.          execsql('PRAGMA '+Params[i]);          
23.       //if Params.IndexOfName('foreign_keys') <> -1 then
24.       //  execsql('PRAGMA foreign_keys =  '+Params.Values['foreign_keys']);
25. end;

Thoughts?
Any "Params" for SQLite-Connection this would break?
I don't think so, since the way i read it, any and all "Params" except foreign_keys are ignored anyway

[rvk]

in my case i needed recursive triggers
...
Thoughts?
Any "Params" for SQLite-Connection this would break?
I don't think so, since the way i read it, any and all "Params" except foreign_keys are ignored anyway

When I look at the documentation of the PRAGMA in sqlite3 I see this:

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

This is specifically about foreign_keys.

So are you sure all other PRAGMA's are ignored when executed inside a transaction?

There is a list of all PRAGMA's here: https://www.sqlite.org/pragma.html

Also:

No reads or writes occur except within a transaction. Any command that accesses the database (basically, any SQL command, except a few PRAGMA statements) will automatically start a transaction if one is not already in effect. Automatically started transactions are committed when the last SQL statement finishes.

https://www.sqlite.org/lang_transaction.html

So we would need to make sure that if a transaction is started by a PRAGMA statement (which?) FPC can handle the already started transaction (and not choke on that fact).

BTW. The PRAGMA's are already expanded to include journal_mode besides foreign_keys.

So expanding it to ALL PRAGMA's it will require testing then all too.

Code: Pascal  [Select][+][-]

1. procedure TSQLite3Connection.DoInternalConnect;
2. const
3.   PRAGMAS:array[0..1] of string=('foreign_keys','journal_mode');
4. //...
5.   checkerror(sqlite3_open_v2(PAnsiChar(filename),@fhandle,GetSQLiteOpenFlags,pvfs));
6.   if (Length(Password)>0) and assigned(sqlite3_key) then
7.     checkerror(sqlite3_key(fhandle,PAnsiChar(Password),StrLen(PAnsiChar(Password))));
8.   for i:=Low(PRAGMAS) to High(PRAGMAS) do begin
9.     j:=Params.IndexOfName(PRAGMAS[i]);
10.     if j <> -1 then
11.       execsql('PRAGMA '+Params[j]);
12.   end;

[Zvoni]

BTW. The PRAGMA's are already expanded to include journal_mode besides foreign_keys.

Hmm... on FPC3.2.2 the Param for journal_mode didn't take, and it choked if i fired of an Connection.ExecSQL as the first statement after successful connection.
But i'll take your word for it.
recursive_triggers definitely cannot be fired per Connection.ExecSQL (at least in FPC3.2.2)

But i agree: any modification should be done carefully.
I was just coming from the angle, that any PRAGMA's could be set as Params that way, irrespective of any (pending) transactions, and it did work with my modification of your hack

[Zvoni]

Coming back to this.
I finally solved it this way

Code: Pascal  [Select][+][-]

1. interface
2.  
3. uses
4.   Classes, SysUtils,sqlite3conn;
5.  
6. Type
7.  
8.   { TSQLiteHelper }
9.  
10.   TSQLiteHelper=class helper for TSQLite3Connection
11.   Public
12.     Procedure ExecPragma(const APragma:String);
13.   end;
14.  
15. implementation
16.  
17. { TSQLiteHelper }
18.  
19. procedure TSQLiteHelper.ExecPragma(const APragma: String);
20. begin
21.   execsql(APragma);
22. end;