make it possible to list all repositories in the web UI

1 files changed, 14 insertions, 17 deletions

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,