The following pull request was submitted through Github. It can be accessed and reviewed at: https://github.com/lxc/lxd/pull/4521
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) ===
From da330de0fd89a4f7d5d62d1873e4fd6eee103fe0 Mon Sep 17 00:00:00 2001 From: Free Ekanayaka <[email protected]> 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 <[email protected]> --- 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 <[email protected]> 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 <[email protected]> --- 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 <[email protected]> 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 <[email protected]> --- 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
_______________________________________________ lxc-devel mailing list [email protected] http://lists.linuxcontainers.org/listinfo/lxc-devel
