SQLite Foreign Keys

Andreik · January 16

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 · 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

argumentum · January 16

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 · January 17

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).

#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.

#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.

jpm · 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 · 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.

Sign In

SQLite Foreign Keys

Recommended Posts

Andreik

Link to comment

Share on other sites

argumentum

Link to comment

Share on other sites

argumentum

Link to comment

Share on other sites

Andreik

Link to comment

Share on other sites

jpm

Link to comment

Share on other sites

Andreik

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Similar Content

Bulldozer

A "WorkDay" function using SQLite

Sqlite 32bit not working

Map Flooder

[Solved] How to combine two SQLite Queries into one returned result

Browse

AutoIt Resources

Release

Beta