From e1344ae7017b28a54d7714895bd54c8431a20bc6 Mon Sep 17 00:00:00 2001
From: Wojtek Kosior <koszko@koszko.org>
Date: Thu, 18 Aug 2022 19:18:00 +0200
Subject: allow adding, removing and altering repositories

This commit also temporarily breaks package import from files :/
---
 src/hydrilla/proxy/tables.sql | 42 ++++++++++++++++++++++++++++++++----------
 1 file changed, 32 insertions(+), 10 deletions(-)

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

diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index a915f74..fc7c65c 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -67,25 +67,34 @@ CREATE TABLE repos(
         repo_id             INTEGER PRIMARY KEY,
 
         name                VARCHAR NOT NULL,
-        url                 VARCHAR NULL,
-	deleted             BOOLEAN NULL,
+        url                 VARCHAR NOT NULL,
+	deleted             BOOLEAN NOT 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),
+	-- The local semi-repo used for packages installed offline is always
+	-- marked as deleted. Semi-repo's name is chosen as an empty string so
+	-- as not to collide with other names (which are required to be
+	-- non-empty).
+	CHECK  ((repo_id = 1) = (name = '')),
+	CHECK  (repo_id != 1 OR deleted = TRUE),
+	-- All deleted repos shall have "url" set to an empty string. All other
+	-- repos shall have a valid http(s) URL.
+	CHECK  (deleted = (url = '')),
+	-- Only non-deleted repos are allowed to have an active iteration.
+	CHECK  (NOT deleted OR active_iteration_id IS NULL),
+	-- Only non-deleted repos are allowed to have last refresh timestamp.
+	CHECK  (NOT deleted 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);
+INSERT INTO repos(repo_id, name, url, deleted, next_iteration)
+VALUES(1, '', '', TRUE, 1);
 
 CREATE TABLE repo_iterations(
         repo_iteration_id INTEGER PRIMARY KEY,
@@ -133,10 +142,19 @@ CREATE TABLE mapping_statuses(
 	-- EXACT_VERSION, is to be updated only with versions from the same
 	-- REPOSITORY or is NOT_FROZEN at all.
 	frozen             CHAR(1) NULL,
+	active_version_id  INTEGER NULL,
 
 	CHECK (enabled IN ('E', 'D', 'N')),
 	CHECK ((frozen IS NULL) = (enabled != 'E')),
-	CHECK (frozen IS NULL OR frozen in ('E', 'R', 'N'))
+	CHECK (frozen IS NULL OR frozen in ('E', 'R', 'N')),
+	CHECK (enabled != 'E' OR active_version_id IS NOT NULL)
+	CHECK (enabled != 'D' OR active_version_id IS NULL)
+
+	FOREIGN KEY (item_id)
+                REFERENCES items (item_id)
+		ON DELETE CASCADE,
+	FOREIGN KEY (active_version_id, item_id)
+                REFERENCES item_versions (item_version_id, item_id)
 );
 
 CREATE TABLE item_versions(
@@ -148,6 +166,8 @@ CREATE TABLE item_versions(
 	definition          TEXT    NOT NULL,
 
         UNIQUE (item_id, version, repo_iteration_id),
+	-- Constraint below needed to allow foreign key from "mapping_statuses".
+	UNIQUE (item_version_id, item_id),
 
 	FOREIGN KEY (item_id)
                 REFERENCES items (item_id),
@@ -169,6 +189,8 @@ FROM
 GROUP BY
         r.repo_id, r.name, r.url, r.deleted, r.last_refreshed;
 
+-- Every time a repository gets refreshed, or a mapping gets enabled/disabled,
+-- all dependencies the "payloads" table and those that reference it are
 CREATE TABLE payloads(
         payload_id          INTEGER PRIMARY KEY,
 
@@ -202,7 +224,7 @@ SELECT
         COALESCE(
                 r.active_iteration_id != ri.repo_iteration_id,
                 TRUE
-        ) AND r.name != '<local>' AS is_orphan
+        ) AND r.repo_id != 1 AS is_orphan
 FROM
                   item_versions    AS iv
         LEFT JOIN payloads         AS p
-- 
cgit v1.2.3

