The following pull request was submitted through Github. It can be accessed and reviewed at: https://github.com/lxc/lxd/pull/4506
This e-mail was sent by the LXC bot, direct replies will not reach the author unless they happen to be subscribed to this list. === Description (from pull-request) === This adds support for the patch.local.sql and patch.global.sql one-off SQL patches as described in #4390.
From 97c9d706c2f70d6e418bde5c812c785d07785439 Mon Sep 17 00:00:00 2001 From: Free Ekanayaka <[email protected]> Date: Fri, 27 Apr 2018 09:00:29 +0000 Subject: [PATCH 1/3] Add a new Schema.File() method to load extra queries from a file Signed-off-by: Free Ekanayaka <[email protected]> --- lxd/db/schema/query.go | 28 ++++++++++++++++++++ lxd/db/schema/schema.go | 16 ++++++++++- lxd/db/schema/schema_test.go | 63 ++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 106 insertions(+), 1 deletion(-) diff --git a/lxd/db/schema/query.go b/lxd/db/schema/query.go index 6ec5ea6c4..6a421d6b3 100644 --- a/lxd/db/schema/query.go +++ b/lxd/db/schema/query.go @@ -3,8 +3,12 @@ package schema import ( "database/sql" "fmt" + "io/ioutil" + "os" "github.com/lxc/lxd/lxd/db/query" + "github.com/lxc/lxd/shared" + "github.com/pkg/errors" ) // Return whether the schema table is present in the database. @@ -68,3 +72,27 @@ INSERT INTO schema (version, updated_at) VALUES (?, strftime("%s")) _, err := tx.Exec(statement, new) return err } + +// Read the given file (if it exists) and executes all queries it contains. +func execFromFile(tx *sql.Tx, path string) error { + if !shared.PathExists(path) { + return nil + } + + bytes, err := ioutil.ReadFile(path) + if err != nil { + return errors.Wrap(err, "failed to read file") + } + + _, err = tx.Exec(string(bytes)) + if err != nil { + return err + } + + err = os.Remove(path) + if err != nil { + return errors.Wrap(err, "failed to remove file") + } + + return nil +} diff --git a/lxd/db/schema/schema.go b/lxd/db/schema/schema.go index 7015977c4..f8166e826 100644 --- a/lxd/db/schema/schema.go +++ b/lxd/db/schema/schema.go @@ -8,6 +8,7 @@ import ( "github.com/lxc/lxd/lxd/db/query" "github.com/lxc/lxd/shared" + "github.com/pkg/errors" ) // Schema captures the schema of a database in terms of a series of ordered @@ -17,6 +18,7 @@ type Schema struct { hook Hook // Optional hook to execute whenever a update gets applied fresh string // Optional SQL statement used to create schema from scratch check Check // Optional callback invoked before doing any update + path string // Optional path to a file containing extra queries to run } // Update applies a specific schema change to a database, and returns an error @@ -111,6 +113,13 @@ func (s *Schema) Fresh(statement string) { s.fresh = statement } +// File extra queries from a file. If the file is exists, all SQL queries in it +// will be executed transactionally at the very start of Ensure(), before +// anything else is done. +func (s *Schema) File(path string) { + s.path = path +} + // Ensure makes sure that the actual schema in the given database matches the // one defined by our updates. // @@ -127,7 +136,12 @@ func (s *Schema) Ensure(db *sql.DB) (int, error) { var current int aborted := false err := query.Transaction(db, func(tx *sql.Tx) error { - err := ensureSchemaTableExists(tx) + err := execFromFile(tx, s.path) + if err != nil { + return errors.Wrapf(err, "failed to execute queries from %s", s.path) + } + + err = ensureSchemaTableExists(tx) if err != nil { return err } diff --git a/lxd/db/schema/schema_test.go b/lxd/db/schema/schema_test.go index 4c225980e..b48425093 100644 --- a/lxd/db/schema/schema_test.go +++ b/lxd/db/schema/schema_test.go @@ -3,6 +3,7 @@ package schema_test import ( "database/sql" "fmt" + "os" "testing" "github.com/stretchr/testify/assert" @@ -10,6 +11,7 @@ import ( "github.com/lxc/lxd/lxd/db/query" "github.com/lxc/lxd/lxd/db/schema" + "github.com/lxc/lxd/shared" ) // Create a new Schema by specifying an explicit map from versions to Update @@ -334,6 +336,67 @@ func TestSchema_ExeciseUpdate(t *testing.T) { require.EqualError(t, err, "no such column: name") } +// A custom schema file path is given, but it does not exists. This is a no-op. +func TestSchema_File_NotExists(t *testing.T) { + schema, db := newSchemaAndDB(t) + schema.Add(updateCreateTable) + schema.File("/non/existing/file/path") + + _, err := schema.Ensure(db) + require.NoError(t, err) +} + +// A custom schema file path is given, but it contains non valid SQL. An error +// is returned an no change to the database is performed at all. +func TestSchema_File_Garbage(t *testing.T) { + schema, db := newSchemaAndDB(t) + schema.Add(updateCreateTable) + + path, err := shared.WriteTempFile("", "lxd-db-schema-", "SELECT FROM baz") + require.NoError(t, err) + defer os.Remove(path) + + schema.File(path) + + _, err = schema.Ensure(db) + + message := fmt.Sprintf("failed to execute queries from %s: near \"FROM\": syntax error", path) + require.EqualError(t, err, message) +} + +// A custom schema file path is given, it runs some queries that repair an +// otherwise broken update, before the update is run. +func TestSchema_File(t *testing.T) { + schema, db := newSchemaAndDB(t) + + // Add an update that would insert a value into a non-existing table. + schema.Add(updateInsertValue) + + path, err := shared.WriteTempFile("", "lxd-db-schema-", + `CREATE TABLE test (id INTEGER); +INSERT INTO test VALUES (2); +`) + + require.NoError(t, err) + defer os.Remove(path) + + schema.File(path) + + _, err = schema.Ensure(db) + require.NoError(t, err) + + // The file does not exist anymore. + assert.False(t, shared.PathExists(path)) + + // The table was created, and the extra row inserted as well. + tx, err := db.Begin() + require.NoError(t, err) + + ids, err := query.SelectIntegers(tx, "SELECT id FROM test ORDER BY id") + require.NoError(t, err) + assert.Equal(t, []int{1, 2}, ids) +} + // Return a new in-memory SQLite database. func newDB(t *testing.T) *sql.DB { db, err := sql.Open("sqlite3", ":memory:") From 467c795740e9a8578e64f7684aa5649dc5c35ceb Mon Sep 17 00:00:00 2001 From: Free Ekanayaka <[email protected]> Date: Fri, 27 Apr 2018 09:15:02 +0000 Subject: [PATCH 2/3] Add support for patch.local.sql and patch.global.sql Signed-off-by: Free Ekanayaka <[email protected]> --- lxd/api_cluster.go | 2 +- lxd/cluster/heartbeat_test.go | 2 +- lxd/cluster/membership_test.go | 8 ++++---- lxd/daemon.go | 3 ++- lxd/db/cluster/open.go | 4 +++- lxd/db/cluster/open_test.go | 6 +++--- lxd/db/db.go | 6 +++--- lxd/db/node/open.go | 1 + lxd/db/testing.go | 2 +- 9 files changed, 19 insertions(+), 15 deletions(-) diff --git a/lxd/api_cluster.go b/lxd/api_cluster.go index 0fd89ed3b..a8cc8f978 100644 --- a/lxd/api_cluster.go +++ b/lxd/api_cluster.go @@ -318,7 +318,7 @@ func clusterPutDisable(d *Daemon) Response { if err != nil { return SmartError(err) } - d.cluster, err = db.OpenCluster("db.bin", d.gateway.Dialer(), address) + d.cluster, err = db.OpenCluster("db.bin", d.gateway.Dialer(), address, "/unused/db/dir") if err != nil { return SmartError(err) } diff --git a/lxd/cluster/heartbeat_test.go b/lxd/cluster/heartbeat_test.go index bc4e7624f..b1def2ed1 100644 --- a/lxd/cluster/heartbeat_test.go +++ b/lxd/cluster/heartbeat_test.go @@ -249,7 +249,7 @@ func (f *heartbeatFixture) node() (*state.State, *cluster.Gateway, string) { var err error require.NoError(f.t, state.Cluster.Close()) - state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address) + state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address, "/unused/db/dir") require.NoError(f.t, err) f.gateways[len(f.gateways)] = gateway diff --git a/lxd/cluster/membership_test.go b/lxd/cluster/membership_test.go index 5d3523350..b1cbc7c86 100644 --- a/lxd/cluster/membership_test.go +++ b/lxd/cluster/membership_test.go @@ -256,7 +256,7 @@ func TestJoin(t *testing.T) { targetAddress := targetServer.Listener.Addr().String() var err error require.NoError(t, targetState.Cluster.Close()) - targetState.Cluster, err = db.OpenCluster("db.bin", targetGateway.Dialer(), targetAddress) + targetState.Cluster, err = db.OpenCluster("db.bin", targetGateway.Dialer(), targetAddress, "/unused/db/dir") require.NoError(t, err) targetF := &membershipFixtures{t: t, state: targetState} targetF.NetworkAddress(targetAddress) @@ -283,7 +283,7 @@ func TestJoin(t *testing.T) { address := server.Listener.Addr().String() require.NoError(t, state.Cluster.Close()) - state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address) + state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address, "/unused/db/dir") require.NoError(t, err) f := &membershipFixtures{t: t, state: state} @@ -368,7 +368,7 @@ func FLAKY_TestPromote(t *testing.T) { targetAddress := targetServer.Listener.Addr().String() var err error require.NoError(t, targetState.Cluster.Close()) - targetState.Cluster, err = db.OpenCluster("db.bin", targetGateway.Dialer(), targetAddress) + targetState.Cluster, err = db.OpenCluster("db.bin", targetGateway.Dialer(), targetAddress, "/unused/db/dir") require.NoError(t, err) targetF := &membershipFixtures{t: t, state: targetState} targetF.NetworkAddress(targetAddress) @@ -397,7 +397,7 @@ func FLAKY_TestPromote(t *testing.T) { mux.HandleFunc(path, handler) } - state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address) + state.Cluster, err = db.OpenCluster("db.bin", gateway.Dialer(), address, "/unused/db/dir") require.NoError(t, err) // Promote the node. diff --git a/lxd/daemon.go b/lxd/daemon.go index 2b76ab2ec..75691226c 100644 --- a/lxd/daemon.go +++ b/lxd/daemon.go @@ -463,7 +463,8 @@ func (d *Daemon) init() error { /* Open the cluster database */ for { - d.cluster, err = db.OpenCluster("db.bin", d.gateway.Dialer(), address) + dir := filepath.Join(d.os.VarDir, "database") + d.cluster, err = db.OpenCluster("db.bin", d.gateway.Dialer(), address, dir) if err == nil { break } diff --git a/lxd/db/cluster/open.go b/lxd/db/cluster/open.go index 498e5d923..b11347bd0 100644 --- a/lxd/db/cluster/open.go +++ b/lxd/db/cluster/open.go @@ -3,6 +3,7 @@ package cluster import ( "database/sql" "fmt" + "path/filepath" "sync/atomic" "github.com/CanonicalLtd/go-grpc-sql" @@ -47,7 +48,7 @@ func Open(name string, dialer grpcsql.Dialer) (*sql.DB, error) { // nodes have version greater than us and we need to be upgraded), or return // false and no error (if some nodes have a lower version, and we need to wait // till they get upgraded and restarted). -func EnsureSchema(db *sql.DB, address string) (bool, error) { +func EnsureSchema(db *sql.DB, address string, dir string) (bool, error) { someNodesAreBehind := false apiExtensions := version.APIExtensionsCount() @@ -86,6 +87,7 @@ func EnsureSchema(db *sql.DB, address string) (bool, error) { } schema := Schema() + schema.File(filepath.Join(dir, "patch.global.sql")) // Optional custom queries schema.Check(check) var initial int diff --git a/lxd/db/cluster/open_test.go b/lxd/db/cluster/open_test.go index 63ac1c4f7..2b74fa309 100644 --- a/lxd/db/cluster/open_test.go +++ b/lxd/db/cluster/open_test.go @@ -17,7 +17,7 @@ import ( func TestEnsureSchema_NoClustered(t *testing.T) { db := newDB(t) addNode(t, db, "0.0.0.0", 1, 1) - ready, err := cluster.EnsureSchema(db, "1.2.3.4:666") + ready, err := cluster.EnsureSchema(db, "1.2.3.4:666", "/unused/db/dir") assert.True(t, ready) assert.NoError(t, err) } @@ -83,7 +83,7 @@ func TestEnsureSchema_ClusterNotUpgradable(t *testing.T) { subtest.Run(t, c.title, func(t *testing.T) { db := newDB(t) c.setup(t, db) - ready, err := cluster.EnsureSchema(db, "1") + ready, err := cluster.EnsureSchema(db, "1", "/unused/db/dir") assert.Equal(t, c.ready, ready) if c.error == "" { assert.NoError(t, err) @@ -125,7 +125,7 @@ func TestEnsureSchema_UpdateNodeVersion(t *testing.T) { addNode(t, db, "1", schema-1, apiExtensions-1) // Ensure the schema. - ready, err := cluster.EnsureSchema(db, "1") + ready, err := cluster.EnsureSchema(db, "1", "/unused/db/dir") assert.NoError(t, err) assert.Equal(t, c.ready, ready) diff --git a/lxd/db/db.go b/lxd/db/db.go index 3c540c322..f2742babc 100644 --- a/lxd/db/db.go +++ b/lxd/db/db.go @@ -149,13 +149,13 @@ type Cluster struct { // - name: Basename of the database file holding the data. Typically "db.bin". // - dialer: Function used to connect to the dqlite backend via gRPC SQL. // - address: Network address of this node (or empty string). -// - api: Number of API extensions that this node supports. +// - dir: Base LXD database directory (e.g. /var/lib/lxd/database) // // The address and api parameters will be used to determine if the cluster // database matches our version, and possibly trigger a schema update. If the // schema update can't be performed right now, because some nodes are still // behind, an Upgrading error is returned. -func OpenCluster(name string, dialer grpcsql.Dialer, address string) (*Cluster, error) { +func OpenCluster(name string, dialer grpcsql.Dialer, address, dir string) (*Cluster, error) { db, err := cluster.Open(name, dialer) if err != nil { return nil, errors.Wrap(err, "failed to open database") @@ -181,7 +181,7 @@ func OpenCluster(name string, dialer grpcsql.Dialer, address string) (*Cluster, } } - nodesVersionsMatch, err := cluster.EnsureSchema(db, address) + nodesVersionsMatch, err := cluster.EnsureSchema(db, address, dir) if err != nil { return nil, errors.Wrap(err, "failed to ensure schema") } diff --git a/lxd/db/node/open.go b/lxd/db/node/open.go index 23048820f..05c556ae4 100644 --- a/lxd/db/node/open.go +++ b/lxd/db/node/open.go @@ -30,6 +30,7 @@ func EnsureSchema(db *sql.DB, dir string, hook schema.Hook) (int, error) { backupDone := false schema := Schema() + schema.File(filepath.Join(dir, "patch.local.sql")) // Optional custom queries schema.Hook(func(version int, tx *sql.Tx) error { if !backupDone { logger.Infof("Updating the LXD database schema. Backup made as \"local.db.bak\"") diff --git a/lxd/db/testing.go b/lxd/db/testing.go index beb025384..e2d77cb4d 100644 --- a/lxd/db/testing.go +++ b/lxd/db/testing.go @@ -56,7 +56,7 @@ func NewTestCluster(t *testing.T) (*Cluster, func()) { // Create an in-memory gRPC SQL server and dialer. server, dialer := newGrpcServer() - cluster, err := OpenCluster(":memory:", dialer, "1") + cluster, err := OpenCluster(":memory:", dialer, "1", "/unused/db/dir") require.NoError(t, err) cleanup := func() { From c12c708aa9c9e1c2065e80a884c563b63bfd3079 Mon Sep 17 00:00:00 2001 From: Free Ekanayaka <[email protected]> Date: Fri, 27 Apr 2018 09:37:38 +0000 Subject: [PATCH 3/3] Add integration tests Signed-off-by: Free Ekanayaka <[email protected]> --- test/suites/database_update.sh | 24 +++++++++++++++++++++++- 1 file changed, 23 insertions(+), 1 deletion(-) diff --git a/test/suites/database_update.sh b/test/suites/database_update.sh index 6259d5c34..d212c739b 100644 --- a/test/suites/database_update.sh +++ b/test/suites/database_update.sh @@ -3,6 +3,19 @@ test_database_update(){ mkdir -p "${LXD_MIGRATE_DIR}/database" MIGRATE_DB=${LXD_MIGRATE_DIR}/database/local.db + # Add some custom queries in patch.local.sql + cat << EOF > "${LXD_MIGRATE_DIR}/database/patch.local.sql" +INSERT INTO certificates(fingerprint, type, name, certificate) VALUES('abc', 0, 'cert1', 'blob1'); +CREATE TABLE test (n INT); +INSERT INTO test(n) VALUES(1); +EOF + + # Add some custom queries in patch.global.sql + cat << EOF > "${LXD_MIGRATE_DIR}/database/patch.global.sql" +CREATE TABLE test (n INT); +INSERT INTO test(n) VALUES(1); +EOF + # Create the version 1 schema as the database sqlite3 "${MIGRATE_DB}" > /dev/null < deps/schema1.sql @@ -10,9 +23,18 @@ test_database_update(){ spawn_lxd "${LXD_MIGRATE_DIR}" true # Assert there are enough tables. - expected_tables=4 + expected_tables=5 tables=$(sqlite3 "${MIGRATE_DB}" ".dump" | grep -c "CREATE TABLE") [ "${tables}" -eq "${expected_tables}" ] || { echo "FAIL: Wrong number of tables after database migration. Found: ${tables}, expected ${expected_tables}"; false; } + # Check that the custom queries were executed. + LXD_DIR="${LXD_MIGRATE_DIR}" lxd sql local "SELECT * FROM test" | grep -q "1" + LXD_DIR="${LXD_MIGRATE_DIR}" lxd sql global "SELECT * FROM certificates" | grep -q "cert1" + LXD_DIR="${LXD_MIGRATE_DIR}" lxd sql global "SELECT * FROM test" | grep -q "1" + + # The custom patch files were deleted. + ! [ -e "${LXD_MIGRATE_DIR}/database/patch.local.sql" ] + ! [ -e "${LXD_MIGRATE_DIR}/database/patch.global.sql" ] + kill_lxd "$LXD_MIGRATE_DIR" }
_______________________________________________ lxc-devel mailing list [email protected] http://lists.linuxcontainers.org/listinfo/lxc-devel
