[lxc-devel] [lxd/master] db: actually enable foreign keys per connection

[tych0 on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 1088 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20170203/fdd9e14a/attachment.bin>
-------------- next part --------------
From 2d4cc59e4ba13d91abdf663d2f52459733cf323f Mon Sep 17 00:00:00 2001
From: Tycho Andersen <tycho.andersen at canonical.com>
Date: Fri, 3 Feb 2017 11:29:19 +0100
Subject: [PATCH] db: actually enable foreign keys per connection

According to: https://www.sqlite.org/pragma.html#pragma_foreign_keys (and
the comments in the code deleted by this patch); the foreign keys pragma
needs to be set per connection to a sqlite database. The problem here is
that the golang sql driver hides the fact that there might be more than one
connection, and they're pooled, so only the first connection would have
foreign keys enabled. This means that e.g. the constraints aren't enforced
on other connections that were automatically created by the sql driver.

This patch installs our own sql driver and uses that, which uses the
sqlite3 hook interface to always enable foreign keys on every connection
that's created, so we don't have this problem any more.

Signed-off-by: Tycho Andersen <tycho.andersen at canonical.com>
---
 lxd/db.go | 14 ++++++++++----
 1 file changed, 10 insertions(+), 4 deletions(-)

diff --git a/lxd/db.go b/lxd/db.go
index 969db52..aba920e 100644
--- a/lxd/db.go
+++ b/lxd/db.go
@@ -181,6 +181,15 @@ CREATE TABLE IF NOT EXISTS schema (
     UNIQUE (version)
 );`
 
+func enableForeignKeys(conn *sqlite3.SQLiteConn) error {
+	_, err := conn.Exec("PRAGMA foreign_keys=ON;", nil)
+	return err
+}
+
+func init() {
+	sql.Register("sqlite3_with_fk", &sqlite3.SQLiteDriver{ConnectHook: enableForeignKeys})
+}
+
 // Create the initial (current) schema for a given SQLite DB connection.
 func createDb(db *sql.DB) (err error) {
 	latestVersion := dbGetSchema(db)
@@ -240,7 +249,7 @@ func initializeDbObject(d *Daemon, path string) (err error) {
 	openPath = fmt.Sprintf("%s?_busy_timeout=%d&_txlock=exclusive", path, timeout*1000)
 
 	// Open the database. If the file doesn't exist it is created.
-	d.db, err = sql.Open("sqlite3", openPath)
+	d.db, err = sql.Open("sqlite3_with_fk", openPath)
 	if err != nil {
 		return err
 	}
@@ -251,9 +260,6 @@ func initializeDbObject(d *Daemon, path string) (err error) {
 		return fmt.Errorf("Error creating database: %s", err)
 	}
 
-	// Run PRAGMA statements now since they are *per-connection*.
-	d.db.Exec("PRAGMA foreign_keys=ON;") // This allows us to use ON DELETE CASCADE
-
 	// Apply any update
 	err = dbUpdatesApplyAll(d)
 	if err != nil {