[SOLVED] SQLite Pragma's don't take
lazarus / 16-Databases

----------------------------------------------------------------------
Zvoni · 2023-10-05 13:16

Stumbled across this with a current project.
Found this thread and rvk's "hack": https://forum.lazarus.freepascal.o
rg/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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Type 
TSQLite3Connection = class(sqlite3conn.TSQLite3Connection)  protected 
  procedure DoInternalConnect; override;  end;          
  Implementation procedure TSQLite3Connection.DoInternalConnect;var 
i:Integer;begin  inherited DoInternalConnect;  If Params.Count>0 Then 
  For i:=0 To Params.Count-1 Do      Begin        If
Params.Names[i]<>'foreign_keys' Then  //Already done in original
DoInternalConnect          execsql('PRAGMA '+Params[i]);      end;end;
Considering above: Shouldn't we provide a patch for this, along the
lines like
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSQLite3Connection.DoInternalConnect;var  filename: ansistring;  pvfs:
PChar;  i:Integer;begin  Inherited;  if DatabaseName = '' then   
DatabaseError(SErrNoDatabaseName,self);  InitializeSQLite;  filename
:= DatabaseName;  if FVFS <> '' then    pvfs := PAnsiChar(FVFS)  else 
  pvfs := Nil;  checkerror(sqlite3_open_v2(PAnsiChar(filename),@fhandl
e,GetSQLiteOpenFlags,pvfs));  if (Length(Password)>0) and
assigned(sqlite3_key) then    checkerror(sqlite3_key(fhandle,PChar(Pas
sword),StrLen(PChar(Password))));         //Change starts here  If
Params.Count>0 Then     For i:=0 To Params.Count-1 Do       
 execsql('PRAGMA '+Params[i]);                 //if
Params.IndexOfName('foreign_keys') <> -1 then      //  execsql('PRAGMA
foreign_keys =  '+Params.Values['foreign_keys']);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 · 2023-10-09 13:00

Quote from: Zvoni on October 05, 2023, 03:16:41 pmin 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:
QuoteThis 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:
QuoteNo 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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSQLite3Connection.DoInternalConnect;const  PRAGMAS:array[0..1] of
string=('foreign_keys','journal_mode');//...  checkerror(sqlite3_open_
v2(PAnsiChar(filename),@fhandle,GetSQLiteOpenFlags,pvfs));  if
(Length(Password)>0) and assigned(sqlite3_key) then    checkerror(sqli
te3_key(fhandle,PAnsiChar(Password),StrLen(PAnsiChar(Password)))); 
for i:=Low(PRAGMAS) to High(PRAGMAS) do begin   
j:=Params.IndexOfName(PRAGMAS[i]);    if j <> -1 then     
execsql('PRAGMA '+Params[j]);  end;

----------------------------------------------------------------------
Zvoni · 2023-10-09 13:15

Quote from: rvk on October 09, 2023, 03:00:31 pmBTW. 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 · 2023-11-03 10:13

Coming back to this.
I finally solved it this way
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
interface uses  Classes, SysUtils,sqlite3conn; Type   { TSQLiteHelper
}   TSQLiteHelper=class helper for TSQLite3Connection  Public   
Procedure ExecPragma(const APragma:String);  end; implementation {
TSQLiteHelper } procedure TSQLiteHelper.ExecPragma(const APragma:
String);begin  execsql(APragma);end;