[ptxdist] [PATCH] sqlite: enable foreign keys by default

[ptxdist] [PATCH] sqlite: enable foreign keys by default

[Bruno Thomsen]

Support for foreign_keys pragma was already enabled at
compile-time, but it was not enabled by default on new
database connections. It's still possible to turn off
enforcement at runtime.
This makes it easier to work on databases that uses
this feature without causing them to end up in an
invalid state.

Signed-off-by: Bruno Thomsen <bruno.thomsen@gmail.com>
---
 rules/sqlite.make | 1 +
 1 file changed, 1 insertion(+)

diff --git a/rules/sqlite.make b/rules/sqlite.make
index b23c2d377..e0b26e3cb 100644
--- a/rules/sqlite.make
+++ b/rules/sqlite.make
@@ -42,6 +42,7 @@ SQLITE_CONF_ENV := \
 	$(CROSS_ENV) \
 	ac_cv_header_zlib_h=no \
 	CPPFLAGS=" \
+	-DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
 	-DSQLITE_ENABLE_COLUMN_METADATA=1 \
 	-DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
 	-DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
-- 
2.24.1


_______________________________________________
ptxdist mailing list
ptxdist@pengutronix.de

Re: [ptxdist] [PATCH] sqlite: enable foreign keys by default

[Michael Olbrich]

On Sat, Feb 15, 2020 at 01:52:05PM +0100, Bruno Thomsen wrote:
> Support for foreign_keys pragma was already enabled at
> compile-time, but it was not enabled by default on new
> database connections. It's still possible to turn off
> enforcement at runtime.
> This makes it easier to work on databases that uses
> this feature without causing them to end up in an
> invalid state.

Hmm, what's the impact of this? What are the performance implications? And
what's the default in other distros? I mean, if the default is generally
off, then I don't expect any existing software to disable this.

Michael

> Signed-off-by: Bruno Thomsen <bruno.thomsen@gmail.com>
> ---
>  rules/sqlite.make | 1 +
>  1 file changed, 1 insertion(+)
> 
> diff --git a/rules/sqlite.make b/rules/sqlite.make
> index b23c2d377..e0b26e3cb 100644
> --- a/rules/sqlite.make
> +++ b/rules/sqlite.make
> @@ -42,6 +42,7 @@ SQLITE_CONF_ENV := \
>  	$(CROSS_ENV) \
>  	ac_cv_header_zlib_h=no \
>  	CPPFLAGS=" \
> +	-DSQLITE_DEFAULT_FOREIGN_KEYS=1 \
>  	-DSQLITE_ENABLE_COLUMN_METADATA=1 \
>  	-DSQLITE_ENABLE_FTS3_PARENTHESIS=1 \
>  	-DSQLITE_ENABLE_UNLOCK_NOTIFY=1 \
> -- 
> 2.24.1
> 
> 
> _______________________________________________
> ptxdist mailing list
> ptxdist@pengutronix.de
> 

-- 
Pengutronix e.K.                           |                             |
Steuerwalder Str. 21                       | http://www.pengutronix.de/  |
31137 Hildesheim, Germany                  | Phone: +49-5121-206917-0    |
Amtsgericht Hildesheim, HRA 2686           | Fax:   +49-5121-206917-5555 |

_______________________________________________
ptxdist mailing list
ptxdist@pengutronix.de

Re: [ptxdist] [PATCH] sqlite: enable foreign keys by default

[Bruno Thomsen]

Hi Michael

Den fre. 21. feb. 2020 kl. 08.23 skrev Michael Olbrich
<m.olbrich@pengutronix.de>:
>
> On Sat, Feb 15, 2020 at 01:52:05PM +0100, Bruno Thomsen wrote:
> > Support for foreign_keys pragma was already enabled at
> > compile-time, but it was not enabled by default on new
> > database connections. It's still possible to turn off
> > enforcement at runtime.
> > This makes it easier to work on databases that uses
> > this feature without causing them to end up in an
> > invalid state.
>
> Hmm, what's the impact of this? What are the performance implications? And
> what's the default in other distros? I mean, if the default is generally
> off, then I don't expect any existing software to disable this.

Fedora 31 has =0, I don't know about other distros,
but you can test it fast with an in-memory database.

$ sqlite3
sqlite> pragma foreign_keys;
1
sqlite> .exit

You will only be affected by this change if your database schema contain
"FOREIGN KEY" SQL syntax. Features is already compiled into the binary
so there will not be any performance implications, and software does not
need to disable it to work.

If this change breaks your software it's because you had a bug or a bad
database access design. This feature is used when you have multiple
tables in a database that relates to each other and you what to ensure
consistency between tables.

Example:

dummy.sql
---------->8----------
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE device(
  address INTEGER PRIMARY KEY,
  description TEXT
);
INSERT INTO device VALUES(1001,"Lorem ipsum dolor sit amet,
consectetuer adipiscing elit");
INSERT INTO device VALUES(2002,"Typi non habent claritatem insitam");
CREATE TABLE event(
  address INTEGER,
  data BLOB NOT NULL,
  FOREIGN KEY(address) REFERENCES device(address) ON DELETE CASCADE
);
INSERT INTO event VALUES(1001,X'AABBCCDDEEFF');
INSERT INTO event VALUES(1001,X'112233445566');
INSERT INTO event VALUES(2002,X'112233445566778899');
INSERT INTO event VALUES(2002,X'112233445566101010');
COMMIT;
---------->8----------

Load database schema with dummy data.
$ sqlite3 device.sqlite < dummy.sql

Calling this command can result in 2 states in the database depending on
foreign_keys pragma.
$ sqlite3 device.sqlite "DELETE FROM device WHERE address=1001;"

foreign_keys=on result in cascade delete on event table:
---------->8----------
$ sqlite3 device.sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE device(
  address INTEGER PRIMARY KEY,
  description TEXT
);
INSERT INTO device VALUES(2002,'Typi non habent claritatem insitam');
CREATE TABLE event(
  address INTEGER,
  data BLOB NOT NULL,
  FOREIGN KEY(address) REFERENCES device(address) ON DELETE CASCADE
);
INSERT INTO event VALUES(2002,X'112233445566778899');
INSERT INTO event VALUES(2002,X'112233445566101010');
COMMIT;
---------->8----------

foreign_keys=off result in dangling rows in event table (inconsistency):
---------->8----------
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE device(
  address INTEGER PRIMARY KEY,
  description TEXT
);
INSERT INTO device VALUES(2002,"Typi non habent claritatem insitam");
CREATE TABLE event(
  address INTEGER,
  data BLOB NOT NULL,
  FOREIGN KEY(address) REFERENCES device(address) ON DELETE CASCADE
);
INSERT INTO event VALUES(1001,X'AABBCCDDEEFF');
INSERT INTO event VALUES(1001,X'112233445566');
INSERT INTO event VALUES(2002,X'112233445566778899');
INSERT INTO event VALUES(2002,X'112233445566101010');
COMMIT;
---------->8----------

The patch could be changed to a new menuconfig option, e.g.
1) Text field with extra compile defines
2) Specific config parameter
3) Generic config parameter (enable modern features)

What kind of change do you prefer? So I don't have to keep a forked edition :)

/Bruno

_______________________________________________
ptxdist mailing list
ptxdist@pengutronix.de

Re: [ptxdist] [PATCH] sqlite: enable foreign keys by default

[Uwe Kleine-König]

On Fri, Feb 21, 2020 at 12:20:33PM +0100, Bruno Thomsen wrote:
> Hi Michael
> 
> Den fre. 21. feb. 2020 kl. 08.23 skrev Michael Olbrich
> <m.olbrich@pengutronix.de>:
> >
> > On Sat, Feb 15, 2020 at 01:52:05PM +0100, Bruno Thomsen wrote:
> > > Support for foreign_keys pragma was already enabled at
> > > compile-time, but it was not enabled by default on new
> > > database connections. It's still possible to turn off
> > > enforcement at runtime.
> > > This makes it easier to work on databases that uses
> > > this feature without causing them to end up in an
> > > invalid state.
> >
> > Hmm, what's the impact of this? What are the performance implications? And
> > what's the default in other distros? I mean, if the default is generally
> > off, then I don't expect any existing software to disable this.
> 
> Fedora 31 has =0, I don't know about other distros,
> but you can test it fast with an in-memory database.
> 
> $ sqlite3
> sqlite> pragma foreign_keys;
> 1
> sqlite> .exit

FTR: on Debian the pragma command yields 0. (Tested with sqlite3
3.31.1-2 that is currently in testing/unstable)

Best regards
Uwe

-- 
Pengutronix e.K.                           | Uwe Kleine-König            |
Industrial Linux Solutions                 | https://www.pengutronix.de/ |

_______________________________________________
ptxdist mailing list
ptxdist@pengutronix.de