Opened 8 months ago
Closed 8 months ago
#3991 closed Feature Request (Completed)
_SQLite_ForeignKeys
Reported by: | Andreik | Owned by: | Jpm |
---|---|---|---|
Milestone: | 3.3.17.0 | Component: | Standard UDFs |
Version: | Severity: | None | |
Keywords: | sqlite | Cc: |
Description
Foreign keys are supported by default by pretty much all important relational database engines and it would be nice to have it by default in SQLite as well or at least a way to enable support when a connection it's made. For this I propose this function _SQLite_ForeignKeys() that enable/disable foreign keys support in SQLite or retrives current foreign_key setting if $bFKEnable is set to Default.
; #FUNCTION# ====================================================================================================================== ; Name: _SQLite_ForeignKeys ; Description: Enable or disable foreign key support in SQLite. ; Syntax: _SQLite_ForeignKeys([$hDB = Default[, $bFKEnable = Default]]) ; Parameters: $hDB - a database handle (by default it's used the handle of the last opened database). ; $bFKEnable - enable foreign key support if this is set to True or disable foreign key support if this is set to False. ; Return value: Success - Returns current foreign key setting. ; Failure - Returns a value that can be compared against $SQLITE_* constants. ; @error = 1 - Cannot retrieve SQLite library version. ; @error = 2 - Invalid format of SQLite library version. ; @error = 3 - Current SQLite library version does not support foreign keys. ; @error = 4 - Cannot retrieve current foreign keys setting (@extended contains the error code returned by _SQLite_QuerySingleRow). ; @error = 5 - Cannot set foreign keys setting (@extended contains the error code returned by _SQLite_Exec). ; Author: Andreik ; Remarks: This function can be used to retrieve the current foreign key support in SQLite if $bFKEnable is set to Default. ; Link: https://www.autoitscript.com/forum/topic/211365-sqlite-foreign-keys/#comment-1529191 ; Example: Yes ; =============================================================================================================================== Func _SQLite_ForeignKeys($hDB = Default, $bFKEnable = Default) If $hDB = Default Then $hDB = -1 ; Check if SQLite version it's at least 3.6.19 (minimum version that supports foreign key constraints) Local $sVersion = _SQLite_LibVersion() If @error Then Return SetError(1, @error, $SQLITE_ERROR) Local $aMinVersion = StringSplit('3.6.19', '.') Local $aLibVersion = StringSplit($sVersion, '.') If $aLibVersion[0] <> 3 Then Return SetError(2, 0, $SQLITE_ERROR) For $Index = 1 To $aLibVersion[0] If Int($aLibVersion[$Index]) > Int($aMinVersion[$Index]) Then ExitLoop If Int($aLibVersion[$Index]) < Int($aMinVersion[$Index]) Then Return SetError(3, 0, $SQLITE_ERROR) Next ; $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(4, @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(5, @error, $iExecRet) EndIf EndFunc ;==>_SQLite_ForeignKeys
Since this feature must be enable each time at runtime and it's persistent for connection, I propose a new parameter ($bFKEnable) in _SQLite_Open() that can be set to enable foreign keys when a database it's opened. By default this parameter won't modify the actual behavior of _SQLite_Open() unless the $bFKEnable is set to True. So the _SQLite_Open() in this variant should look like this:
Func _SQLite_Open($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
It preserves the code and behavior of the actual function, the only addition it's the last part of the function between # symbols.
Here I wrote two examples about how this is suppose to work with this parameter set and unset.
https://www.autoitscript.com/forum/topic/211365-sqlite-foreign-keys/#comment-1529191
Attachments (0)
Change History (3)
comment:1 Changed 8 months ago by Jpm
- Owner set to Jpm
- Status changed from new to assigned
comment:2 Changed 8 months ago by Andreik
In the documentation it should be also included this for clarity.
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
It should also be specified that SQLite does not make any checks on previously inserted data. So enabling foreign keys does not trigger any warnings or errors for data that has been previously inserted in the database that would normally raise a foreign keys constraint error. This can be seen in this example:
#include <SQLite.au3> _SQLite_Startup() ; By default foreign keys support is disabled $hDB = _SQLite_Open() _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) _SQLite_Exec($hDB, 'INSERT INTO Books(idAuthor, BookName) VALUES(' & $idAuthor & ', "The Lost World");') ; Here we insert some inconsistent data $idAuthor += 1 _SQLite_Exec($hDB, 'INSERT INTO Books(idAuthor, BookName) VALUES(' & $idAuthor & ', "The Final Problem");') ; Foreign keys support is enabled but don't throw any errors for data already in database ConsoleWrite('Foreign keys enabled: ' & _SQLite_ForeignKeys($hDB, True) & @CRLF) 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
comment:3 Changed 8 months ago by Jpm
- Milestone set to 3.3.17.0
- Resolution set to Completed
- Status changed from assigned to closed
Added by revision [13030] in version: 3.3.17.0
Guidelines for posting comments:
- You cannot re-open a ticket but you may still leave a comment if you have additional information to add.
- In-depth discussions should take place on the forum.
For more information see the full version of the ticket guidelines here.
Thanks I will commit the needed file with the doc