Andreik Posted January 16 Share Posted January 16 (edited) Since foreign keys are supported by default by pretty much all important relational database engines it would be nice to have it by default in SQLite as well. SQLite introduced support for foreign key constraints since SQLite v.3.6.19 but you have to manually enable foreign keys support at runtime. I know it's just a single line of code but should we consider to add this by default when _SQLite_Open() it's called? It might be a parameter as such: Func _SQLite_Open($sDatabase_Filename = Default, $iAccessMode = Default, $iEncoding = Default, $bFKEnable = True) If Not $__g_hDll_SQLite Then Return SetError(3, $SQLITE_MISUSE, 0) If $sDatabase_Filename = Default Or Not IsString($sDatabase_Filename) Then $sDatabase_Filename = ":memory:" Local $tFilename = __SQLite_StringToUtf8Struct($sDatabase_Filename) If @error Then Return SetError(2, @error, 0) If $iAccessMode = Default Then $iAccessMode = BitOR($SQLITE_OPEN_READWRITE, $SQLITE_OPEN_CREATE) Local $bOldBase = FileExists($sDatabase_Filename) ; encoding cannot be changed if base already exists If $iEncoding = Default Then $iEncoding = $SQLITE_ENCODING_UTF8 EndIf Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_open_v2", "struct*", $tFilename, _ ; UTF-8 Database filename "ptr*", 0, _ ; OUT: SQLite db handle "int", $iAccessMode, _ ; database access mode "ptr", 0) If @error Then Return SetError(1, @error, 0) ; DllCall error If $avRval[0] <> $SQLITE_OK Then __SQLite_ReportError($avRval[2], "_SQLite_Open") _SQLite_Close($avRval[2]) Return SetError(-1, $avRval[0], 0) EndIf $__g_hDB_SQLite = $avRval[2] __SQLite_hAdd($__g_ahDBs_SQLite, $avRval[2]) If Not $bOldBase Then Local $aEncoding[3] = ["8", "16", "16be"] _SQLite_Exec($avRval[2], 'PRAGMA encoding="UTF-' & $aEncoding[$iEncoding] & '";') EndIf If $bFKEnable Then _SQLite_Exec($avRval[2], 'PRAGMA foreign_keys = ON;') ; <<< --- added just this line to original _SQLite_Open() Return SetExtended($avRval[0], $avRval[2]) EndFunc ;==>_SQLite_Open If we really want to stick with the previous behavior of _SQLite_Open() then $bFKEnable can be set as False by default. Or we can add to UDF a new function like below to enable/disable foreign keys, but for me it makes more sense to enable the support when connection starts and you can disable support with _SQLite_Exec(). Func _SQLite_FK_Enable($hDB = Default, $bFKEnable = True) If __SQLite_hChk($hDB, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) If _SQLite_Exec($hDB, 'PRAGMA foreign_keys = ' & ($bFKEnable ? 'ON' : 'OFF') & ';') == $SQLITE_OK Then Return SetError(0, 0, $bFKEnable) Else Return SetError(2, 0, Null) EndIf EndFunc ;==>_SQLite_FK_Enable Thoughts on this? Edited January 16 by Andreik argumentum 1 When the words fail... music speaks. Link to comment Share on other sites More sharing options...
argumentum Posted January 16 Share Posted January 16 In my experience, you'll have to add headers to the functions, give a clear example and write the help ( for the help file ) or something close to it, so that the implementation/addition/change is a smooth operation. Otherwise is just more work for them ( the team ). After you get that done, open a trac with the code and point to this discussion. After the many years here, naming convention and what not, should be an easy task four you. I trust you with my life code Follow the link to my code contribution ( and other things too ). FAQ - Please Read Before Posting. Link to comment Share on other sites More sharing options...
argumentum Posted January 16 Share Posted January 16 (edited) 6 hours ago, Andreik said: set as False by default. Or we can add to UDF a new function like below to enable/disable foreign keys Or do both. Add the default as false, as to not brake prior code and have the new function too. Tho I would set the default as Default ( in _SQLite_ForeignKey() ), and if is Default, return the current pragma (on/off) setting. Also error check for versions of SQLite not supporting the feature(s). Also, returning -1 instead of Null. Don't remember seeing any other function return null in case of error. To keep it as AutoIt ( users ) friendly as possible. Edit: _SQLite_ForeignKey($hDB = -1, $bFKEnable = Default) would be a better name, and instead of repeating the code in _SQLite_Open(), just add _SQLite_ForeignKey() in that line. Edited January 17 by argumentum brainstorming Andreik 1 Follow the link to my code contribution ( and other things too ). FAQ - Please Read Before Posting. Link to comment Share on other sites More sharing options...
Andreik Posted January 17 Author Share Posted January 17 (edited) Following your suggestions. Added two examples also: First example is what might be default behavior of _SQLite_Open() where foreign keys are disabled by default. Second example is how the new parameter $bFKEnable would work to enable foreign keys when the database is opened. _SQLite_ForeignKeys() with $bFKEnable = Default returns current foreign_keys setting or if $bFKEnable is provided as a boolean value will enable/disable foreign keys support. Success: returns current foreign_keys setting as boolean value (enabled/disabled) Failure: returns a value that can be compared against $SQLITE_* constants @error = 1 - Cannot retrieve current foreign_keys setting (@extended contains the error code returned by _SQLite_QuerySingleRow). @error = 2 - Cannot set foreign_keys setting (@extended contains the error code returned by _SQLite_Exec). expandcollapse popup#include <SQLite.au3> _SQLite_Startup() ;~ ################################################################# ;~ # Example #1 # ;~ ################################################################# ConsoleWrite('Running example #1' & @CRLF) $hDB = _SQLite_Open2() ; Current value ConsoleWrite('Current foreign_keys setting: ' & _SQLite_ForeignKeys() & @CRLF) ; Enable foreign keys _SQLite_ForeignKeys($hDB, True) ConsoleWrite('Current foreign_keys setting: ' & _SQLite_ForeignKeys() & @CRLF) ; Disable foreign keys _SQLite_ForeignKeys($hDB, False) ConsoleWrite('Current foreign_keys setting: ' & _SQLite_ForeignKeys() & @CRLF) _SQLite_Close($hDB) ;~ ################################################################# ;~ ################################################################# ;~ # Example #2 # ;~ ################################################################# ConsoleWrite('Running example #2' & @CRLF) $hDB = _SQLite_Open2(Default, Default, Default, True) ; Current value ConsoleWrite('Current foreign_keys setting: ' & _SQLite_ForeignKeys() & @CRLF) _SQLite_Close($hDB) ;~ ################################################################# _SQLite_Shutdown() Func _SQLite_ForeignKeys($hDB = Default, $bFKEnable = Default) If $hDB = Default Then $hDB = -1 ; $bFKEnable = Default - Return current PRAGMA foreign_keys setting If $bFKEnable = Default Then Local $aRow Local $iQueryRet = _SQLite_QuerySingleRow($hDB, 'PRAGMA foreign_keys;', $aRow) If @error Then Return SetError(1, @error, $iQueryRet) Return SetError(0, 0, $aRow[0] = 1 ? True : False) EndIf ; $bFKEnable = True - Enable foreign keys ; $bFKEnable = False - Disable foreign keys Local $iExecRet = _SQLite_Exec($hDB, 'PRAGMA foreign_keys = ' & ($bFKEnable ? 'ON' : 'OFF') & ';') If $iExecRet = $SQLITE_OK Then Return SetError(0, 0, $bFKEnable ? True : False) Else Return SetError(2, @error, $iExecRet) EndIf EndFunc ;==>_SQLite_ForeignKeys Func _SQLite_Open2($sDatabase_Filename = Default, $iAccessMode = Default, $iEncoding = Default, $bFKEnable = Default) If Not $__g_hDll_SQLite Then Return SetError(3, $SQLITE_MISUSE, 0) If $sDatabase_Filename = Default Or Not IsString($sDatabase_Filename) Then $sDatabase_Filename = ":memory:" Local $tFilename = __SQLite_StringToUtf8Struct($sDatabase_Filename) If @error Then Return SetError(2, @error, 0) If $iAccessMode = Default Then $iAccessMode = BitOR($SQLITE_OPEN_READWRITE, $SQLITE_OPEN_CREATE) Local $bOldBase = FileExists($sDatabase_Filename) ; encoding cannot be changed if base already exists If $iEncoding = Default Then $iEncoding = $SQLITE_ENCODING_UTF8 EndIf Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_open_v2", "struct*", $tFilename, _ ; UTF-8 Database filename "ptr*", 0, _ ; OUT: SQLite db handle "int", $iAccessMode, _ ; database access mode "ptr", 0) If @error Then Return SetError(1, @error, 0) ; DllCall error If $avRval[0] <> $SQLITE_OK Then __SQLite_ReportError($avRval[2], "_SQLite_Open") _SQLite_Close($avRval[2]) Return SetError(-1, $avRval[0], 0) EndIf $__g_hDB_SQLite = $avRval[2] __SQLite_hAdd($__g_ahDBs_SQLite, $avRval[2]) If Not $bOldBase Then Local $aEncoding[3] = ["8", "16", "16be"] _SQLite_Exec($avRval[2], 'PRAGMA encoding="UTF-' & $aEncoding[$iEncoding] & '";') EndIf ; Foreign keys support ; ##################################### If $bFKEnable = Default Then $bFKEnable = False If $bFKEnable Then _SQLite_ForeignKeys($avRval[2], $bFKEnable) If @error Then Local $iError = @error _SQLite_Close($avRval[2]) Return SetError(4, $iError, 0) EndIf EndIf ; ##################################### Return SetExtended($avRval[0], $avRval[2]) EndFunc ;==>_SQLite_Open One more example with foreign keys enabled. expandcollapse popup#include <SQLite.au3> _SQLite_Startup() $hDB = _SQLite_Open2(Default, Default, Default, True) _SQLite_Exec($hDB, 'CREATE TABLE Authors(idAuthor INTEGER, AuthorName VARCHAR(64), PRIMARY KEY("idAuthor" AUTOINCREMENT));') _SQLite_Exec($hDB, 'CREATE TABLE Books(idBook INTEGER, idAuthor INTEGER , BookName VARCHAR(64), PRIMARY KEY("idBook" AUTOINCREMENT), FOREIGN KEY("idAuthor") REFERENCES Authors("idAuthor"));') _SQLite_Exec($hDB, 'INSERT INTO Authors(AuthorName) VALUES("Arthur Conan Doyle");') $idAuthor = _SQLite_LastInsertRowID($hDB) ; This should work _SQLite_Exec($hDB, 'INSERT INTO Books(idAuthor, BookName) VALUES(' & $idAuthor & ', "The Lost World");') ; This should fail with message: FOREIGN KEY constraint failed $idAuthor += 1 _SQLite_Exec($hDB, 'INSERT INTO Books(idAuthor, BookName) VALUES(' & $idAuthor & ', "The Final Problem");') Local $aResult, $iRows, $iCols _SQLite_GetTable2D($hDB, 'SELECT * FROM Books;', $aResult, $iRows, $iCols) _SQLite_Display2DResult($aResult) _SQLite_Close($hDB) _SQLite_Shutdown() Func _SQLite_ForeignKeys($hDB = Default, $bFKEnable = Default) If $hDB = Default Then $hDB = -1 ; $bFKEnable = Default - Return current PRAGMA foreign_keys setting If $bFKEnable = Default Then Local $aRow Local $iQueryRet = _SQLite_QuerySingleRow($hDB, 'PRAGMA foreign_keys;', $aRow) If @error Then Return SetError(1, @error, $iQueryRet) Return SetError(0, 0, $aRow[0] = 1 ? True : False) EndIf ; $bFKEnable = True - Enable foreign keys ; $bFKEnable = False - Disable foreign keys Local $iExecRet = _SQLite_Exec($hDB, 'PRAGMA foreign_keys = ' & ($bFKEnable ? 'ON' : 'OFF') & ';') If $iExecRet = $SQLITE_OK Then Return SetError(0, 0, $bFKEnable ? True : False) Else Return SetError(2, @error, $iExecRet) EndIf EndFunc ;==>_SQLite_ForeignKeys Func _SQLite_Open2($sDatabase_Filename = Default, $iAccessMode = Default, $iEncoding = Default, $bFKEnable = Default) If Not $__g_hDll_SQLite Then Return SetError(3, $SQLITE_MISUSE, 0) If $sDatabase_Filename = Default Or Not IsString($sDatabase_Filename) Then $sDatabase_Filename = ":memory:" Local $tFilename = __SQLite_StringToUtf8Struct($sDatabase_Filename) If @error Then Return SetError(2, @error, 0) If $iAccessMode = Default Then $iAccessMode = BitOR($SQLITE_OPEN_READWRITE, $SQLITE_OPEN_CREATE) Local $bOldBase = FileExists($sDatabase_Filename) ; encoding cannot be changed if base already exists If $iEncoding = Default Then $iEncoding = $SQLITE_ENCODING_UTF8 EndIf Local $avRval = DllCall($__g_hDll_SQLite, "int:cdecl", "sqlite3_open_v2", "struct*", $tFilename, _ ; UTF-8 Database filename "ptr*", 0, _ ; OUT: SQLite db handle "int", $iAccessMode, _ ; database access mode "ptr", 0) If @error Then Return SetError(1, @error, 0) ; DllCall error If $avRval[0] <> $SQLITE_OK Then __SQLite_ReportError($avRval[2], "_SQLite_Open") _SQLite_Close($avRval[2]) Return SetError(-1, $avRval[0], 0) EndIf $__g_hDB_SQLite = $avRval[2] __SQLite_hAdd($__g_ahDBs_SQLite, $avRval[2]) If Not $bOldBase Then Local $aEncoding[3] = ["8", "16", "16be"] _SQLite_Exec($avRval[2], 'PRAGMA encoding="UTF-' & $aEncoding[$iEncoding] & '";') EndIf ; Foreign keys support ; ##################################### If $bFKEnable = Default Then $bFKEnable = False If $bFKEnable Then _SQLite_ForeignKeys($avRval[2], $bFKEnable) If @error Then Local $iError = @error _SQLite_Close($avRval[2]) Return SetError(4, $iError, 0) EndIf EndIf ; ##################################### Return SetExtended($avRval[0], $avRval[2]) EndFunc ;==>_SQLite_Open Edited January 17 by Andreik Removed checks for library version as jpm suggested. argumentum and ioa747 2 When the words fail... music speaks. Link to comment Share on other sites More sharing options...
jpm Posted January 17 Share Posted January 17 Thanks @Andreik I will add that to the SQLite.au3 I Will not check for library version as the Version 3.6.19 was introduced before the first SQLite.au3 Andreik 1 Link to comment Share on other sites More sharing options...
Andreik Posted January 17 Author Share Posted January 17 11 minutes ago, jpm said: Thanks @Andreik I will add that to the SQLite.au3 I Will not check for library version as the Version 3.6.19 was introduced before the first SQLite.au3 Thanks. Less code it's even better. When the words fail... music speaks. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now