Provide API support for REALSQLlite Foreign key Constraint supp

Databases
1

Since the SQLite database plugin is being worked on for 2013R1 anyway, I thought it a good idea to post this here…

<https://xojo.com/issue/25077>

I assume the SQLite plugin is compiled with the ability to support Foreign key Constraints. Even so as of now SQLite defaults to not using them. You have to execute a PRAGMA with each connection to enable them. Why you would not want them on all the time I don’t know.

In any case I certainly don’t want old code to break, but it makes sense to have this taken care of for us so we don’t forget to turn it on or have to subclass the DB class just for this.

What I propose is an enum property called ForeignKeySupport on the SQLite database class with the following values:

Default ’ Whatever that version of SQLite defaults to
On
Off

If the property was set to ‘On’ or ‘Off’ (BUT NOT to ‘Default’), on a successful connection, the connect method would then execute the PRAGMA with that value.

An optional additional enhancement to that for more flexibility could be to add an optional parameter (ForeignKeySupport) to the connect method.

Here Default could mean default to the setting of the Database ForeignKeySupport property

2

I don’t know how enabling FOREIGN KEYS could break old code.
An old code, not declaring FOREIGN KEYS, would just work ok in a new SQLite compiled to handle it as default.
SQLite team advised that in a near future they will turn it ON by default.
They already provide a MACRO to do so right now: SQLITE_DEFAULT_FOREIGN_KEYS=1
I vote just turning it ON by default, and nothing more will be necessary. Goodbye Extra Pragmas.

3

Someone defined foreign keys, never enabled them and did not ensure compliance in their code… Yes a likely a bug on their part but still it could make something that at least looked to be working to ‘break’.

That said I would be fine to always have it on.

  • Karen
4

I’m fine on having it ON too, as I never write timebombs in my code, like explicit defining things that I must to pray for never being enabled in a point in the future. :slight_smile: