From 2579081df2a568192887d776a6965af323b7c4ee Mon Sep 17 00:00:00 2001
From: Wojtek Kosior <koszko@koszko.org>
Date: Thu, 18 Aug 2022 13:40:16 +0200
Subject: make it possible to list all repositories in the web UI

---
 src/hydrilla/proxy/tables.sql | 31 ++++++++++++++-----------------
 1 file changed, 14 insertions(+), 17 deletions(-)

(limited to 'src/hydrilla/proxy/tables.sql')

diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index 0417613..a915f74 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -155,6 +155,20 @@ CREATE TABLE item_versions(
                 REFERENCES repo_iterations (repo_iteration_id)
 );
 
+CREATE VIEW repo_display_infos
+AS
+SELECT
+	r.repo_id, r.name, r.url, r.deleted, r.last_refreshed,
+	COALESCE(SUM(i.type = 'R'), 0) AS resource_count,
+	COALESCE(SUM(i.type = 'M'), 0) AS mapping_count
+FROM
+		  repos           AS r
+	LEFT JOIN repo_iterations AS ir USING (repo_id)
+	LEFT JOIN item_versions   AS iv USING (repo_iteration_id)
+	LEFT JOIN items           AS i  USING (item_id)
+GROUP BY
+        r.repo_id, r.name, r.url, r.deleted, r.last_refreshed;
+
 CREATE TABLE payloads(
         payload_id          INTEGER PRIMARY KEY,
 
@@ -225,23 +239,6 @@ CREATE TABLE resolved_depended_resources(
 		ON DELETE CASCADE
 ) WITHOUT ROWID;
 
--- CREATE TABLE resolved_required_mappings(
---         requiring_item_id        INTEGER,
---         required_mapping_item_id INTEGER,
-
---         PRIMARY KEY (requiring_item_id, required_mapping_item_id),
-
---         CHECK (requiring_item_id != required_mapping_item_id),
-
---         FOREIGN KEY (requiring_item_id)
---                 REFERENCES items (item_id),
--- 	-- Note: the referenced mapping shall have installed=TRUE.
---         FOREIGN KEY (required_mapping_item_id)
---                 REFERENCES mappings (item_id),
---         FOREIGN KEY (required_mapping_item_id)
---                 REFERENCES items (item_id)
--- );
-
 CREATE TABLE files(
         file_id INTEGER PRIMARY KEY,
 
-- 
cgit v1.2.3

