From d54a95e0f9c689f2bbaaea90a3a16a855a408823 Mon Sep 17 00:00:00 2001
From: Wojtek Kosior <koszko@koszko.org>
Date: Wed, 17 Aug 2022 13:50:34 +0200
Subject: allow loading packages from zip files through web UI and listing
 installed mappings

---
 src/hydrilla/proxy/tables.sql | 55 +++++++++++++++++++++++++++++++++++++++----
 1 file changed, 50 insertions(+), 5 deletions(-)

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

diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index 2a6cac6..0417613 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -45,7 +45,8 @@ INSERT INTO general(
         default_allow_scripts,
 	repo_refresh_seconds,
 	mapping_use_mode
-) VALUES(
+)
+VALUES(
         1,
         '3.0b1',
         FALSE,
@@ -66,19 +67,26 @@ CREATE TABLE repos(
         repo_id             INTEGER PRIMARY KEY,
 
         name                VARCHAR NOT NULL,
-        url                 VARCHAR NOT NULL,
-	deleted             BOOLEAN NOT NULL,
+        url                 VARCHAR NULL,
+	deleted             BOOLEAN NULL,
 	next_iteration      INTEGER NOT NULL,
 	active_iteration_id INTEGER NULL,
 	last_refreshed      INTEGER NULL,
 
         UNIQUE (name),
+	CHECK  ((repo_id = 1) = (name = '<local>')),
+	CHECK  ((repo_id = 1) = (url IS NULL)),
+	CHECK  ((repo_id = 1) = (deleted IS NULL)),
+	CHECK  (repo_id != 1 OR last_refreshed IS NULL),
 
 	FOREIGN KEY (active_iteration_id)
 		REFERENCES repo_iterations(repo_iteration_id)
 		ON DELETE SET NULL
 );
 
+INSERT INTO repos(repo_id, name, next_iteration)
+VALUES(1, '<local>', 1);
+
 CREATE TABLE repo_iterations(
         repo_iteration_id INTEGER PRIMARY KEY,
 
@@ -126,8 +134,9 @@ CREATE TABLE mapping_statuses(
 	-- REPOSITORY or is NOT_FROZEN at all.
 	frozen             CHAR(1) NULL,
 
-	CHECK  ((frozen IS NULL) = (enabled != 'E')),
-	CHECK  (frozen IS NULL OR frozen in ('E', 'R', 'N'))
+	CHECK (enabled IN ('E', 'D', 'N')),
+	CHECK ((frozen IS NULL) = (enabled != 'E')),
+	CHECK (frozen IS NULL OR frozen in ('E', 'R', 'N'))
 );
 
 CREATE TABLE item_versions(
@@ -163,6 +172,42 @@ CREATE TABLE payloads(
 		ON DELETE CASCADE
 );
 
+CREATE VIEW mapping_display_infos
+AS
+SELECT
+        CASE WHEN
+                ms.enabled = 'N' AND COUNT(p.payload_id) > 0
+        THEN
+                'A' -- AUTO_ENABLED mapping
+        ELSE
+                ms.enabled
+        END AS enabled,
+        iv.item_version_id, iv.definition,
+        r.name AS repo,
+        ri.iteration AS repo_iteration,
+        COALESCE(
+                r.active_iteration_id != ri.repo_iteration_id,
+                TRUE
+        ) AND r.name != '<local>' AS is_orphan
+FROM
+                  item_versions    AS iv
+        LEFT JOIN payloads         AS p
+                ON iv.item_version_id = p.mapping_item_id
+        JOIN      items            AS i
+                USING (item_id)
+        JOIN      mapping_statuses AS ms
+                USING (item_id)
+        JOIN      repo_iterations  AS ri
+                USING (repo_iteration_id)
+        JOIN      repos            AS r
+                USING (repo_id)
+WHERE
+        i.type = 'M'
+GROUP BY
+        ms.enabled,
+        iv.item_version_id, iv.definition,
+        r.name, ri.iteration;
+
 CREATE TABLE resolved_depended_resources(
         payload_id       INTEGER,
         resource_item_id INTEGER,
-- 
cgit v1.2.3