[lxc-devel] [lxd/master] Add --schema option to lxd sql dump and update database docs
freeekanayaka on Github
lxc-bot at linuxcontainers.org
Wed May 2 14:21:02 UTC 2018
A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 301 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20180502/dbd5b7fa/attachment.bin>
-------------- next part --------------
From da330de0fd89a4f7d5d62d1873e4fd6eee103fe0 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Wed, 2 May 2018 13:42:24 +0000
Subject: [PATCH 1/3] Add --schema flag to lxd sql to dump only the schema.
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
lxd/api_internal.go | 8 +++++++-
lxd/db/query/dump.go | 19 +++++++++++++------
lxd/db/query/dump_test.go | 2 +-
lxd/main_sql.go | 8 ++++++++
test/suites/sql.sh | 14 ++++++++++++++
5 files changed, 43 insertions(+), 8 deletions(-)
diff --git a/lxd/api_internal.go b/lxd/api_internal.go
index 62a3b6edc..b1741eccb 100644
--- a/lxd/api_internal.go
+++ b/lxd/api_internal.go
@@ -128,6 +128,12 @@ func internalSQLGet(d *Daemon, r *http.Request) Response {
return BadRequest(fmt.Errorf("Invalid database"))
}
+ schemaFormValue := r.FormValue("schema")
+ schemaOnly, err := strconv.Atoi(schemaFormValue)
+ if err != nil {
+ schemaOnly = 0
+ }
+
var schema string
var db *sql.DB
if database == "global" {
@@ -143,7 +149,7 @@ func internalSQLGet(d *Daemon, r *http.Request) Response {
return SmartError(errors.Wrap(err, "failed to start transaction"))
}
defer tx.Rollback()
- dump, err := query.Dump(tx, schema)
+ dump, err := query.Dump(tx, schema, schemaOnly == 1)
if err != nil {
return SmartError(errors.Wrapf(err, "failed dump database %s", database))
}
diff --git a/lxd/db/query/dump.go b/lxd/db/query/dump.go
index 5cfc34cab..33ce6105f 100644
--- a/lxd/db/query/dump.go
+++ b/lxd/db/query/dump.go
@@ -13,7 +13,7 @@ import (
// Dump returns a SQL text dump of all rows across all tables, similar to
// sqlite3's dump feature
-func Dump(tx *sql.Tx, schema string) (string, error) {
+func Dump(tx *sql.Tx, schema string, schemaOnly bool) (string, error) {
schemas := dumpParseSchema(schema)
// Begin
@@ -34,6 +34,11 @@ BEGIN TRANSACTION;
}
sort.Strings(tables)
for _, table := range tables {
+ if schemaOnly {
+ // Dump only the schema.
+ dump += schemas[table] + "\n"
+ continue
+ }
tableDump, err := dumpTable(tx, table, schemas[table])
if err != nil {
return "", errors.Wrapf(err, "failed to dump table %s", table)
@@ -41,12 +46,14 @@ BEGIN TRANSACTION;
dump += tableDump
}
- // Sequences
- tableDump, err = dumpTable(tx, "sqlite_sequence", "DELETE FROM sqlite_sequence;")
- if err != nil {
- return "", errors.Wrapf(err, "failed to dump table sqlite_sequence")
+ // Sequences (unless the schemaOnly flag is true)
+ if !schemaOnly {
+ tableDump, err = dumpTable(tx, "sqlite_sequence", "DELETE FROM sqlite_sequence;")
+ if err != nil {
+ return "", errors.Wrapf(err, "failed to dump table sqlite_sequence")
+ }
+ dump += tableDump
}
- dump += tableDump
// Commit
dump += "COMMIT;\n"
diff --git a/lxd/db/query/dump_test.go b/lxd/db/query/dump_test.go
index b08ac42ec..3f6c4a676 100644
--- a/lxd/db/query/dump_test.go
+++ b/lxd/db/query/dump_test.go
@@ -12,7 +12,7 @@ import (
func TestDump(t *testing.T) {
tx := newTxForDump(t, "local")
- dump, err := query.Dump(tx, schemas["local"])
+ dump, err := query.Dump(tx, schemas["local"], false /* schemaOnly */)
require.NoError(t, err)
assert.Equal(t, `PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
diff --git a/lxd/main_sql.go b/lxd/main_sql.go
index 52a7ac870..bbb1fa476 100644
--- a/lxd/main_sql.go
+++ b/lxd/main_sql.go
@@ -16,6 +16,8 @@ import (
type cmdSql struct {
global *cmdGlobal
+
+ flagSchema bool
}
func (c *cmdSql) Command() *cobra.Command {
@@ -51,6 +53,9 @@ func (c *cmdSql) Command() *cobra.Command {
cmd.RunE = c.Run
cmd.Hidden = true
+ flags := cmd.Flags()
+ flags.BoolVar(&c.flagSchema, "schema", false, `Dump only the SQL schema (if <query> is set to "dump")`)
+
return cmd
}
@@ -91,6 +96,9 @@ func (c *cmdSql) Run(cmd *cobra.Command, args []string) error {
if query == "dump" {
url := fmt.Sprintf("/internal/sql?database=%s", database)
+ if c.flagSchema {
+ url += "&schema=1"
+ }
response, _, err := d.RawQuery("GET", url, nil, "")
if err != nil {
return errors.Wrap(err, "failed to request dump")
diff --git a/test/suites/sql.sh b/test/suites/sql.sh
index 5fda0c7b7..4a0a0cce1 100644
--- a/test/suites/sql.sh
+++ b/test/suites/sql.sh
@@ -27,9 +27,23 @@ test_sql() {
sqlite3 "${SQLITE_DUMP}" "SELECT * FROM patches" | grep -q invalid_profile_names
rm -f "${SQLITE_DUMP}"
+ # Local database schema dump
+ SQLITE_DUMP="${TEST_DIR}/dump.db"
+ lxd sql local dump --schema | sqlite3 "${SQLITE_DUMP}"
+ sqlite3 "${SQLITE_DUMP}" "SELECT * FROM schema" | grep -q 1
+ [ "$(sqlite3 ${SQLITE_DUMP} 'SELECT * FROM patches' | wc -l)" = "0" ]
+ rm -f "${SQLITE_DUMP}"
+
# Global database dump
SQLITE_DUMP="${TEST_DIR}/dump.db"
lxd sql global dump | sqlite3 "${SQLITE_DUMP}"
sqlite3 "${SQLITE_DUMP}" "SELECT * FROM profiles" | grep -q "Default LXD profile"
rm -f "${SQLITE_DUMP}"
+
+ # Global database schema dump
+ SQLITE_DUMP="${TEST_DIR}/dump.db"
+ lxd sql global dump --schema | sqlite3 "${SQLITE_DUMP}"
+ sqlite3 "${SQLITE_DUMP}" "SELECT * FROM schema" | grep -q 1
+ [ "$(sqlite3 ${SQLITE_DUMP} 'SELECT * FROM profiles' | wc -l)" = "0" ]
+ rm -f "${SQLITE_DUMP}"
}
From d64afc3a0215587708659c2b526a5e6c03b81452 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Wed, 2 May 2018 14:11:37 +0000
Subject: [PATCH 2/3] Update database.md with information about lxd sql and
patch.*.sql
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
doc/database.md | 367 ++++----------------------------------------------------
1 file changed, 24 insertions(+), 343 deletions(-)
diff --git a/doc/database.md b/doc/database.md
index 4018eb35f..45466df5e 100644
--- a/doc/database.md
+++ b/doc/database.md
@@ -40,346 +40,27 @@ Backups of the global database directory and of the local database file are made
before upgrades, and are tagged with the ``.bak`` suffix. You can use those if
you need to revert the state as it was before the upgrade.
-# Design
-The design of the database is made to be as close as possible to
-the [RESTful API](rest-api.md).
-
-The main table and field names are exact match for the REST API.
-
-However this database isn't an exact match of the API, mostly because
-any runtime or external piece of information will not be stored in the
-database (as this would require constent polling and wouldn't gain us
-anything).
-
-We make no guarantee of stability for the database schema. This is a
-purely internal database which only LXD should ever use. Updating LXD
-may cause a schema update and data being shuffled. In those cases, LXD
-will make a copy of the old database as ".old" to allow for a revert.
-
-
-# Tables
-The list of tables is:
-
- * certificates
- * config
- * containers
- * containers\_config
- * containers\_devices
- * containers\_devices\_config
- * containers\_profiles
- * images
- * images\_aliases
- * images\_properties
- * images\_source
- * networks
- * networks\_config
- * patches
- * profiles
- * profiles\_config
- * profiles\_devices
- * profiles\_devices\_config
- * schema
-
-You'll notice that compared to the REST API, there are a few differences:
-
- 1. The extra "\*\_config" tables which are there for key/value config storage.
- 2. The extra "images\_properties" table which is there for key/value property storage.
- 3. The extra "schema" table whish is used for database schema version tracking.
- 4. The extra "patches" table used for data migration and other non-schema changes on upgrades.
- 5. There is no "snapshots" table. That's because snapshots are a copy
- of a container at a given point in time, including its configuration and
- on-disk state. So having snapshots in a separate table would only be needless duplication.
-
-# Notes on sqlite3
-sqlite3 only supports 5 storage classes: NULL, INTEGER, REAL, TEXT and BLOB
-There are then a set of aliases for each of those storage classes which is what we use below.
-
-# Schema
-## certificates
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-fingerprint | VARCHAR(255) | - | NOT NULL | HEX encoded certificate fingerprint
-type | INTEGER | - | NOT NULL | Certificate type (0 = client)
-name | VARCHAR(255) | - | NOT NULL | Certificate name (defaults to CN)
-certificate | TEXT | - | NOT NULL | PEM encoded certificate
-
-Index: UNIQUE ON id AND fingerprint
-
-
-## config (server configuration)
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND key
-
-
-## containers
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | Container name
-architecture | INTEGER | - | NOT NULL | Container architecture
-type | INTEGER | 0 | NOT NULL | Container type (0 = container, 1 = container snapshot)
-ephemeral | INTEGER | 0 | NOT NULL | Whether the container is ephemeral (0 = persistent, 1 = ephemeral)
-stateful | INTEGER | 0 | NOT NULL | Whether the snapshot contains state (snapshot only)
-creation\_date | DATETIME | - | | Container creation date
-last\_use\_date | DATETIME | - | | Last container action
-
-Index: UNIQUE ON id AND name
-
-
-## containers\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-container\_id | INTEGER | - | NOT NULL | containers.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND container\_id + key
-
-Foreign keys: container\_id REFERENCES containers(id)
-
-
-## containers\_devices
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-container\_id | INTEGER | - | NOT NULL | containers.id FK
-name | VARCHAR(255) | - | NOT NULL | Container name
-type | INTEGER | 0 | NOT NULL | Device type (see configuration.md)
-
-Index: UNIQUE ON id AND container\_id + name
-
-Foreign keys: container\_id REFERENCES containers(id)
-
-
-## containers\_devices\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-container\_device\_id | INTEGER | - | NOT NULL | containers\_devices.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND container\_device\_id + key
-
-Foreign keys: container\_device\_id REFERENCES containers\_devices(id)
-
-
-## containers\_profiles
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-container\_id | INTEGER | - | NOT NULL | containers.id FK
-profile\_id | INTEGER | - | NOT NULL | profiles.id FK
-apply\_order | INTEGER | 0 | NOT NULL | Profile ordering
-
-Index: UNIQUE ON id AND container\_id + profile\_id
-
-Foreign keys: container\_id REFERENCES containers(id) and profile\_id REFERENCES profiles(id)
-
-
-## images
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-cached | INTEGER | 0 | NOT NULL | Whether this is a cached image
-fingerprint | VARCHAR(255) | - | NOT NULL | Tarball fingerprint
-filename | VARCHAR(255) | - | NOT NULL | Tarball filename
-size | INTEGER | - | NOT NULL | Tarball size
-public | INTEGER | 0 | NOT NULL | Whether the image is public or not
-auto\_update | INTEGER | 0 | NOT NULL | Whether to update from the source of this image
-architecture | INTEGER | - | NOT NULL | Image architecture
-creation\_date | DATETIME | - | | Image creation date (user supplied, 0 = unknown)
-expiry\_date | DATETIME | - | | Image expiry (user supplied, 0 = never)
-upload\_date | DATETIME | - | NOT NULL | Image entry creation date
-last\_use\_date | DATETIME | - | | Last time the image was used to spawn a container
-
-Index: UNIQUE ON id AND fingerprint
-
-
-## images\_aliases
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | Alias name
-image\_id | INTEGER | - | NOT NULL | images.id FK
-description | VARCHAR(255) | - | | Description of the alias
-
-Index: UNIQUE ON id AND name
-
-Foreign keys: image\_id REFERENCES images(id)
-
-
-## images\_properties
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-image\_id | INTEGER | - | NOT NULL | images.id FK
-type | INTEGER | 0 | NOT NULL | Property type (0 = string, 1 = text)
-key | VARCHAR(255) | - | NOT NULL | Property name
-value | TEXT | - | | Property value (NULL for unset)
-
-Index: UNIQUE ON id
-
-Foreign keys: image\_id REFERENCES images(id)
-
-## images\_source
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-image\_id | INTEGER | - | NOT NULL | images.id FK
-server | TEXT | - | NOT NULL | Server URL
-protocol | INTEGER | 0 | NOT NULL | Protocol to access the remote (0 = lxd, 1 = direct, 2 = simplestreams)
-certificate | TEXT | - | | PEM encoded certificate of the server
-alias | VARCHAR(255) | - | NOT NULL | What remote alias to use as the source
-
-Index: UNIQUE ON id
-
-Foreign keys: image\_id REFERENCES images(id)
-
-## networks
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | Profile name
-
-Index: UNIQUE on id AND name
-
-## networks\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-network\_id | INTEGER | - | NOT NULL | networks.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND network\_id + key
-
-Foreign keys: network\_id REFERENCES networks(id)
-
-## patches
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | Patch name
-applied\_at | DATETIME | - | NOT NULL | When the patch was applied
-
-Index: UNIQUE ON id AND name
-
-## profiles
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | Profile name
-description | TEXT | - | | Description of the profile
-
-Index: UNIQUE on id AND name
-
-
-## profiles\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-profile\_id | INTEGER | - | NOT NULL | profiles.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | VARCHAR(255) | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND profile\_id + key
-
-Foreign keys: profile\_id REFERENCES profiles(id)
-
-
-## profiles\_devices
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-profile\_id | INTEGER | - | NOT NULL | profiles.id FK
-name | VARCHAR(255) | - | NOT NULL | Container name
-type | INTEGER | 0 | NOT NULL | Device type (see configuration.md)
-
-Index: UNIQUE ON id AND profile\_id + name
-
-Foreign keys: profile\_id REFERENCES profiles(id)
-
-
-## profiles\_devices\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-profile\_device\_id | INTEGER | - | NOT NULL | profiles\_devices.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-Index: UNIQUE ON id AND profile\_device\_id + key
-
-Foreign keys: profile\_device\_id REFERENCES profiles\_devices(id)
-
-
-## schema
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-version | INTEGER | - | NOT NULL | Schema version
-updated\_at | DATETIME | - | NOT NULL | When the schema update was done
-
-Index: UNIQUE ON id AND version
-
-## storage\_pools
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-name | VARCHAR(255) | - | NOT NULL | storage pool name
-driver | VARCHAR(255) | - | NOT NULL | storage pool driver
-
-## storage\_pools\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-storage\_pool\_id | INTEGER | - | NOT NULL | storage\_pools.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
-
-## storage\_volumes
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-storage\_pool\_id | INTEGER | - | NOT NULL | storage\_pools.id FK
-name | VARCHAR(255) | - | NOT NULL | storage volume name
-type | INTEGER | - | NOT NULL | storage volume type
-
-## storage\_volumes\_config
-
-Column | Type | Default | Constraint | Description
-:----- | :--- | :------ | :--------- | :----------
-id | INTEGER | SERIAL | NOT NULL | SERIAL
-storage\_volume\_id | INTEGER | - | NOT NULL | storage\_volumes.id FK
-key | VARCHAR(255) | - | NOT NULL | Configuration key
-value | TEXT | - | | Configuration value (NULL for unset)
+# Running custom queries from the console
+If you need to perform SQL queries (e.g. ``SELECT``, ``INSERT``, ``UPDATE``)
+against the local or global database, you can use the ``lxd sql`` command (run
+``lxd sql --help`` for details).
+
+You should only need to do that in order to recover from broken updates or bugs.
+Please consult the LXD team first (creating a [GitHub
+issue](https://github.com/lxc/lxd/issues/new) or
+[forum](https://discuss.linuxcontainers.org/) post).
+
+# Running custom queries at LXD daemon startup
+In case the LXD daemon fails to start after an upgrade because of SQL data
+migration bugs or similar problems, it's possible to recover the situation by
+creating ``.sql`` files containing queries that repair the broken update.
+
+To perform repairs against the local database, write a
+``./database/patch.local.sql`` file containing the relevant queries, and
+similarly a ``./database/patch.global.sql`` for global database repairs.
+
+Those files will be loaded very early in the daemon startup sequence and deleted
+if the queries were successful (if they fail, no state will change as they are
+run in a SQL transaction).
+
+As above, please consult the LXD team first.
From 7757908cddfadaff2eebc2855d6634f5a02909c7 Mon Sep 17 00:00:00 2001
From: Free Ekanayaka <free.ekanayaka at canonical.com>
Date: Wed, 2 May 2018 14:17:35 +0000
Subject: [PATCH 3/3] Document how to dump the content or schema of databases
Signed-off-by: Free Ekanayaka <free.ekanayaka at canonical.com>
---
doc/database.md | 6 ++++++
1 file changed, 6 insertions(+)
diff --git a/doc/database.md b/doc/database.md
index 45466df5e..fd0ecc3bb 100644
--- a/doc/database.md
+++ b/doc/database.md
@@ -40,6 +40,12 @@ Backups of the global database directory and of the local database file are made
before upgrades, and are tagged with the ``.bak`` suffix. You can use those if
you need to revert the state as it was before the upgrade.
+# Dumping the database content or schema
+If you want to get a SQL text dump of the content or the schema of the databases,
+use the ``lxd sql <local|global> dump [--schema]`` command, which produces the
+equivalent output of the ``.dump`` or ``.schema`` directives of the sqlite3
+command line tool.
+
# Running custom queries from the console
If you need to perform SQL queries (e.g. ``SELECT``, ``INSERT``, ``UPDATE``)
against the local or global database, you can use the ``lxd sql`` command (run
More information about the lxc-devel
mailing list