From 2579081df2a568192887d776a6965af323b7c4ee Mon Sep 17 00:00:00 2001 From: Wojtek Kosior 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