[RESOLVED] [vbRichClient] SQLite Pragma and Foreign Key support.

[7edm]

Hi Olaf,

I hope you will see this.

I'm trying to switch On SQLite Foreign Key support in RC5 but without success :-(

While

Call con.Execute("PRAGMA user_version = 1")

seam to work and set assigned value

Call con.Execute("PRAGMA foreign_keys = 1")

doesn't

Have you stripped out the FK support in the RC SQLite library or what, and if so for what reason?

If this simply is a bug no one have come across yet, this is one more observation that may or may not have something to do with it.

When Using the "PRAGMA" statement to open a recordset (I cannot see any other way to read a DB setting value, although I may have missed it) like

Set rs = con.OpenRecordset("PRAGMA user_version")

rs(0).Updatable always equals False and same with rs.Updatable

while for e.g.

Set rs = con.OpenRecordset("SELECT value FROM config WHERE name = 'setting'")

behave as you would expect it depending on if optional ReadOnly arg is set or not.

And while you are on it, maybe you could update the sqlite version as well, as the current on used by RC5 isn't among the recommended ones by upstream ;-)

/Joakim

[Schmidt]

I'm trying to switch On SQLite Foreign Key support in RC5 but without success :-(

While

Call con.Execute("PRAGMA user_version = 1")

seam to work and set assigned value

Call con.Execute("PRAGMA foreign_keys = 1")

doesn't

Here it does - what you perhaps mean is, that setting the foreign_keys Pragma is not made persistent in the DB.
Some Pragmas will be persisted in the DB-File itself - and some are only considered valid "for the session" (as long as the Connection lives).

Have you stripped out the FK support in the RC SQLite library or what, and if so for what reason?

No, support for FKs is compiled in - but the foreign_keys pragma (as a session-pragma) is not explicitely enabled,
when I create/open a new connection-instance.
I refrained from enabling the pragma in the wrapper, to remain conform to the default-behaviour of SQLite
(which also clearly states, that for reasons of backward-compatibility the pragma is per default Off).

Here you can read more about pragmas in general, but also about the foreign_key-pragmas:
http://www.sqlite.org/pragma.html#pragma_foreign_keys

As for testing the FK-support - I just wrote a small example-demo, which is created with the same example-data as in:
http://www.sqlite.org/foreignkeys.html

And the code below produces just the right errors (Foreign-Key-Constraints) as the example on the linked site "predicts".

Option Explicit 'needs a reference to vbRichClient5 (downloadable on vbRichClient.com)

Private DBFolder As String, DBFile As String, Cnn As cConnection

Private Sub Form_Load()
  'create a writable DBFolder in the Users local AppData
  DBFolder = New_c.FSO.GetLocalAppDataPath & "\MySQLiteDBs\"
  New_c.FSO.EnsurePath DBFolder
  
  DBFile = DBFolder & "TestFKs.db3"
 
  Set Cnn = New_c.Connection(DBFile, IIf(New_c.FSO.FileExists(DBFile), DBOpenFromFile, DBCreateNewFileDB))
  
  Debug.Print "ReadOut of potentially persistet Pragmas:", Pragma("foreign_keys"), Pragma("user_version")
  
  Pragma("foreign_keys") = 1
  Pragma("user_version") = 1
  Debug.Print "After setting fresh Session-Pragmas:", Pragma("foreign_keys"), Pragma("user_version"); vbLf
 
 
  'example-tables according to: http://www.sqlite.org/foreignkeys.html
  Cnn.Execute "CREATE TABLE IF NOT EXISTS Artist(ArtistID INTEGER PRIMARY KEY, ArtistName Text)"
  Cnn.Execute "CREATE TABLE IF NOT EXISTS Track (TrackID INTEGER PRIMARY KEY, TrackName TEXT, TrackArtist INTEGER," & _
                                                "FOREIGN KEY(TrackArtist) REFERENCES Artist(ArtistID))"
 
  'also according to the site above, some example-data, starting with 2 entries in Table Artist
  Cnn.Execute "Insert Into Artist Values(1, 'Dean Martin')"
  Cnn.Execute "Insert Into Artist Values(2, 'Frank Sinatra')"
  
  'and some entries in Track
  Cnn.Execute "Insert Into Track Values(11, 'That''s Amore', 1)"
  Cnn.Execute "Insert Into Track Values(12, 'Christmas Blues', 1)"
  Cnn.Execute "Insert Into Track Values(13, 'My Way', 2)"
  
  DumpRs Cnn.OpenRecordset("Select * From Artist")
  DumpRs Cnn.OpenRecordset("Select * From Track")
 
  'This fails because the value inserted into the trackartist column (3)
  'does not correspond to row in the artist table.
  ExecWithErrReport "INSERT INTO track VALUES(14, 'Mr. Bojangles', 3)"
 
  'This succeeds because a NULL is inserted into trackartist. A
  'corresponding row in the artist table is not required in this case.
  ExecWithErrReport "INSERT INTO track VALUES(14, 'Mr. Bojangles', NULL)"
 
  'Trying to modify the trackartist field of the record after it has
  'been inserted does not work either, since the new value of trackartist (3)
  'Still does not correspond to any row in the artist table.
  ExecWithErrReport "UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'"
 
  'Insert the required row into the artist table. It is then possible to
  'update the inserted row to set trackartist to 3 (since a corresponding
  'row in the artist table now exists).
  ExecWithErrReport "INSERT INTO artist VALUES(3, 'Sammy Davis Jr.');"
  ExecWithErrReport "UPDATE track SET trackartist = 3 WHERE trackname = 'Mr. Bojangles'"
 
  'Now that "Sammy Davis Jr." (artistid = 3) has been added to the database,
  'it is possible to INSERT new tracks using this artist without violating
  'the foreign key constraint:
  ExecWithErrReport "INSERT INTO track VALUES(15, 'Boogie Woogie', 3)"
 
 
  DumpRs Cnn.OpenRecordset("Select * From Artist")
  DumpRs Cnn.OpenRecordset("Select * From Track")

  '************ cleanup the test-table-data for the next round *************
  
  'an attempt to delete all records from the artist-table will fail, because this would result in Zomby-IDs in tracks
  ExecWithErrReport "Delete From artist"
  
  'when done in the right order (delete everything from track, and then from artist) then it succeeds
  ExecWithErrReport "Delete From track"
  ExecWithErrReport "Delete From artist"
End Sub

'just a few helper-routines...
Private Property Get Pragma(Name As String)
  Pragma = Cnn.OpenRecordset("PRAGMA " & Name)(0)
End Property
Private Property Let Pragma(Name As String, Value)
  Cnn.Execute "PRAGMA " & Name & "=" & Value
End Property

Private Sub DumpRs(Rs As cRecordset) 'a Dump for the current Rs-Content
Dim Fld As cField
  For Each Fld In Rs.Fields: Debug.Print Fld.Name,: Next 'a "HeaderLine" from the FieldNames
  Debug.Print vbCrLf; String(64, "-")

  Do Until Rs.EOF 'and the Rs-Record-Loop for the Field-Value-Dump
    For Each Fld In Rs.Fields: Debug.Print Fld.Value,: Next 'Field-Values of the current Record
    Debug.Print
    Rs.MoveNext
  Loop
  Debug.Print
End Sub

Private Sub ExecWithErrReport(SQL As String)
  On Error Resume Next
    Cnn.Execute SQL
  If Err Then Debug.Print Err.Description; vbLf; SQL; vbLf: Err.Clear
End Sub

When Using the "PRAGMA" statement to open a recordset (I cannot see any other way to read a DB setting value, although I may have missed it) like

Set rs = con.OpenRecordset("PRAGMA user_version")

rs(0).Updatable always equals False and same with rs.Updatable

while for e.g.

Set rs = con.OpenRecordset("SELECT value FROM config WHERE name = 'setting'")

behave as you would expect it depending on if optional ReadOnly arg is set or not.

The DB-Read-Direction is ensured always (and exclusively) over recordset-retrieval (the Pragma being no exception).
The Write-Direction can happen over Rs.UpdateBatch, CommandObjects and Cnn.Executes - the Property-Defs below show examples for Read and Write of Pragmas.

Property Get Pragma(Cnn As cConnection, Name As String)
  Pragma = Cnn.OpenRecordset("PRAGMA " & Name)(0)
End Property
Property Let Pragma(Cnn As cConnection, Name As String, Value)
  Cnn.Execute "PRAGMA " & Name & "=" & Value
End Property

As for handing out a Recordset being "automatically detected as ReadOnly" ...
that happens, when I do not find a Table, I could later on write changes "back into" (e.g. when UpdateBatch would be called).

e.g. you can use a Cnn as a simple Formula-Evaluator, using the fast SQL-Interpreter of SQLite (which is based on Lemon), without involving a table-name:

Function Evaluate(Cnn As cConnection, Expression As String)
  Evaluate = Cnn.OpenRecordset("Select " & Expression)(0)
End Function

And in the (temporary) Recordset (consisting of only one record and one field with the appropriate Expression-Result) - Rs.Updateable would be false too -
since there's not a target "to update back-into".

And while you are on it, maybe you could update the sqlite version as well, as the current on used by RC5 isn't among the recommended ones by upstream ;-)

I'm not updating with as high a frequency anymore as I did some years ago, and in this special stage (or Step) of the version-history I want to wait a bit
longer - until the dust around the new Query-Optimizier has settled a bit (there was already a version 3.8.1 - let's wait a few more weeks, until the next
official version comes out - then I will recompile the engine-dll).

Olaf

[7edm]

Here it does - what you perhaps mean is, that setting the foreign_keys Pragma is not made persistent in the DB.
Some Pragmas will be persisted in the DB-File itself - and some are only considered valid "for the session" (as long as the Connection lives).

No, support for FKs is compiled in - but the foreign_keys pragma (as a session-pragma) is not explicitely enabled,
when I create/open a new connection-instance.
I refrained from enabling the pragma in the wrapper, to remain conform to the default-behaviour of SQLite
(which also clearly states, that for reasons of backward-compatibility the pragma is per default Off).

Ok I see, I have to set it in code each time I open the db, fair enough. I just didn't get that when reading sqlite.org and thought that as the 'user_version' change was persistence the 'foreign_keys' was too. I read it as it was Off by default by the pragma changed it, while if you wanted it to be On by default you had to set it at compile time. The sqlite.org docs imho is a bit unclear about its persistence but ok now I know.

I'm not updating with as high a frequency anymore as I did some years ago, and in this special stage (or Step) of the version-history I want to wait a bit
longer - until the dust around the new Query-Optimizier has settled a bit (there was already a version 3.8.1 - let's wait a few more weeks, until the next
official version comes out - then I will recompile the engine-dll).
Olaf

Ok fair enough, and really appreciate all the hard work you put into vbRichCLient. Although you may like to consider that as a user of the library including it in a distributed app, it can feel a bit "awkward" to not adhere the recommendation given by sqlite.org, I mean they clearly have to mean it when putting effort into single out releases that is "OK" not to update at this point in contrast to others. Just making a point;-)

Anyway thanks for your extensive reply with example code.

/Joakim