[lxc-devel] [lxd/master] DB: Adds networks to project usage view

[tomponline on Github]

A non-text attachment was scrubbed...
Name: not available
Type: text/x-mailbox
Size: 361 bytes
Desc: not available
URL: <http://lists.linuxcontainers.org/pipermail/lxc-devel/attachments/20200903/eadce887/attachment.bin>
-------------- next part --------------
From 08d7f594064b70877433ede712919c8a6a2f9f21 Mon Sep 17 00:00:00 2001
From: Thomas Parrott <thomas.parrott at canonical.com>
Date: Thu, 3 Sep 2020 11:23:46 +0100
Subject: [PATCH] lxd/db/cluster: Adds networks to project usage view

Signed-off-by: Thomas Parrott <thomas.parrott at canonical.com>
---
 lxd/db/cluster/schema.go |  9 +++++++--
 lxd/db/cluster/update.go | 39 +++++++++++++++++++++++++++++++++++++++
 2 files changed, 46 insertions(+), 2 deletions(-)

diff --git a/lxd/db/cluster/schema.go b/lxd/db/cluster/schema.go
index de1981674d..5c71dc219c 100644
--- a/lxd/db/cluster/schema.go
+++ b/lxd/db/cluster/schema.go
@@ -461,7 +461,12 @@ CREATE VIEW projects_used_by_ref (name,
     printf('/1.0/profiles/%s?project=%s',
     profiles.name,
     projects.name)
-    FROM profiles JOIN projects ON project_id=projects.id;
+    FROM profiles JOIN projects ON project_id=projects.id UNION
+  SELECT projects.name,
+    printf('/1.0/networks/%s?project=%s',
+    networks.name,
+    projects.name)
+    FROM networks JOIN projects ON project_id=projects.id;
 CREATE TABLE storage_pools (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     name TEXT NOT NULL,
@@ -573,5 +578,5 @@ CREATE TABLE storage_volumes_snapshots_config (
     UNIQUE (storage_volume_snapshot_id, key)
 );
 
-INSERT INTO schema (version, updated_at) VALUES (36, strftime("%s"))
+INSERT INTO schema (version, updated_at) VALUES (37, strftime("%s"))
 `
diff --git a/lxd/db/cluster/update.go b/lxd/db/cluster/update.go
index 52b8c96997..d9ba84a2e0 100644
--- a/lxd/db/cluster/update.go
+++ b/lxd/db/cluster/update.go
@@ -73,6 +73,45 @@ var updates = map[int]schema.Update{
 	34: updateFromV33,
 	35: updateFromV34,
 	36: updateFromV35,
+	37: updateFromV36,
+}
+
+// Add networks to projects references.
+func updateFromV36(tx *sql.Tx) error {
+	stmts := `
+DROP VIEW projects_used_by_ref;
+CREATE VIEW projects_used_by_ref (name,
+    value) AS
+  SELECT projects.name,
+    printf('/1.0/instances/%s?project=%s',
+    "instances".name,
+    projects.name)
+    FROM "instances" JOIN projects ON project_id=projects.id UNION
+  SELECT projects.name,
+    printf('/1.0/images/%s?project=%s',
+    images.fingerprint,
+    projects.name)
+    FROM images JOIN projects ON project_id=projects.id UNION
+  SELECT projects.name,
+    printf('/1.0/storage-pools/%s/volumes/custom/%s?project=%s&target=%s',
+    storage_pools.name,
+    storage_volumes.name,
+    projects.name,
+    nodes.name)
+    FROM storage_volumes JOIN storage_pools ON storage_pool_id=storage_pools.id JOIN nodes ON node_id=nodes.id JOIN projects ON project_id=projects.id WHERE storage_volumes.type=2 UNION
+  SELECT projects.name,
+    printf('/1.0/profiles/%s?project=%s',
+    profiles.name,
+    projects.name)
+    FROM profiles JOIN projects ON project_id=projects.id UNION
+  SELECT projects.name,
+    printf('/1.0/networks/%s?project=%s',
+    networks.name,
+    projects.name)
+    FROM networks JOIN projects ON project_id=projects.id;
+`
+	_, err := tx.Exec(stmts)
+	return err
 }
 
 // This fixes node IDs of storage volumes on non-remote pools which were