From mboxrd@z Thu Jan 1 00:00:00 1970 Return-path: Received: from mail-ed1-x544.google.com ([2a00:1450:4864:20::544]) by metis.ext.pengutronix.de with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1j56MU-0002Ae-OE for ptxdist@pengutronix.de; Fri, 21 Feb 2020 12:20:51 +0100 Received: by mail-ed1-x544.google.com with SMTP id j17so1906815edp.3 for ; Fri, 21 Feb 2020 03:20:50 -0800 (PST) MIME-Version: 1.0 References: <20200215125205.31606-1-bruno.thomsen@gmail.com> <20200221072308.GB4597@pengutronix.de> In-Reply-To: <20200221072308.GB4597@pengutronix.de> From: Bruno Thomsen Date: Fri, 21 Feb 2020 12:20:33 +0100 Message-ID: Subject: Re: [ptxdist] [PATCH] sqlite: enable foreign keys by default List-Id: PTXdist Development Mailing List List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Reply-To: ptxdist@pengutronix.de Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Errors-To: ptxdist-bounces@pengutronix.de Sender: "ptxdist" To: ptxdist@pengutronix.de, Bruno Thomsen , Bruno Thomsen Hi Michael Den fre. 21. feb. 2020 kl. 08.23 skrev 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. 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