[proxy] make package version info page also display other versions; fix&improve some aspects of package loading and dependency recomputing

1 files changed, 30 insertions, 41 deletions

diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index 863c989..918c1f1 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -68,8 +68,8 @@ CREATE TABLE repos(
 
         name                VARCHAR NOT NULL,
         url                 VARCHAR NOT NULL,
-	deleted             BOOLEAN NOT NULL,
-	next_iteration      INTEGER NOT NULL,
+	deleted             BOOLEAN NOT NULL DEFAULT FALSE,
+	next_iteration      INTEGER NOT NULL DEFAULT 1,
 	active_iteration_id INTEGER NULL,
 	last_refreshed      INTEGER NULL,
 
@@ -93,8 +93,8 @@ CREATE TABLE repos(
 		ON DELETE SET NULL
 );
 
-INSERT INTO repos(repo_id, name, url, deleted, next_iteration)
-VALUES(1, '', '', TRUE, 1);
+INSERT INTO repos(repo_id, name, url, deleted)
+VALUES(1, '', '', TRUE);
 
 CREATE TABLE repo_iterations(
         repo_iteration_id INTEGER PRIMARY KEY,
@@ -139,30 +139,22 @@ CREATE TABLE mapping_statuses(
 
         -- "enabled" determines whether mapping's status is ENABLED,
 	-- DISABLED or NO_MARK.
-        enabled            CHAR(1) NOT NULL,
+        enabled            CHAR(1) NOT NULL DEFAULT 'N',
 	-- "frozen" determines whether an enabled mapping is to be kept in its
 	-- EXACT_VERSION, is to be updated only with versions from the same
 	-- REPOSITORY or is NOT_FROZEN at all.
 	frozen             CHAR(1) NULL,
-	-- The last 2 fields defined below shall be updated when dependency tree
-	-- is recomputed.
-	-- When "required" is TRUE, the mapping is assumed to either be enabled
-	-- or be (directly or indirectly) required by another mapping which is
-	-- enabled (i.e. has "enabled" set to 'E').
-	required           BOOLEAN NOT NULL,
 	-- Only one version of a mapping is allowed to be active at any time.
 	-- "active_version_id" indicates which version it is. Only a mapping
 	-- version referenced by "active_version_id" is allowed to have rows
 	-- in the "payloads" table reference it.
+	-- "active_version_id" shall be updated every time dependency tree is
+	-- recomputed.
 	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 (enabled != 'E' OR required),
-	CHECK (enabled != 'D' OR NOT required),
-	CHECK (not required OR active_version_id IS NOT NULL),
-	CHECK (enabled != 'D' OR active_version_id IS NULL),
 
 	FOREIGN KEY (item_id)
                 REFERENCES items (item_id)
@@ -189,11 +181,18 @@ CREATE TABLE item_versions(
 	repo_iteration_id   INTEGER  NOT NULL,
 	definition          BLOB     NOT NULL,
 	definition_sha256   CHAR(64) NOT NULL,
+	-- "active" determines whether a version of this mapping is active
+	-- because it is REQUIRED, has been AUTO activated or is NOT_ACTIVE.
+	-- "active" shall be updated every time dependency tree is recomputed.
+	-- It shall be set to NOT_ACTIVE if and only if given row does not
+	-- correspond to "active_version_id" of any row in "mapping_statuses".
+	active              CHAR(1)  NOT NULL DEFAULT 'N',
 
         UNIQUE (item_id, version, repo_iteration_id),
 	-- Constraint below needed to allow foreign key from "mapping_statuses".
 	UNIQUE (item_version_id, item_id),
 	CHECK (installed in ('I', 'N', 'F')),
+	CHECK (active in ('R', 'A', 'N')),
 
 	FOREIGN KEY (item_id)
                 REFERENCES items (item_id),
@@ -236,35 +235,25 @@ CREATE TABLE payloads(
 		ON DELETE CASCADE
 );
 
-CREATE VIEW mapping_display_infos
+CREATE VIEW item_versions_extra
 AS
 SELECT
-	i.item_id, i.identifier,
-        CASE WHEN
-                ms.enabled = 'N' AND
-		COALESCE(ms.active_version_id, -1) = iv.item_version_id
-        THEN
-                'A' -- AUTO_ENABLED mapping
-        ELSE
-                ms.enabled
-        END AS enabled,
-        iv.item_version_id, iv.definition, iv.installed,
-        r.name AS repo,
-        ri.iteration AS repo_iteration,
-	COALESCE(r.active_iteration_id, -1) != ri.repo_iteration_id AND
-        r.repo_id != 1 AS is_orphan,
-	r.repo_id = 1 AS is_local,
-	COALESCE(ms.active_version_id, -1) = iv.item_version_id AS is_active
+        iv.item_version_id,
+	iv.item_id,
+        iv.version,
+	iv.installed,
+	iv.repo_iteration_id,
+	iv.definition,
+	iv.active,
+	r.name AS repo,
+	ri.iteration AS repo_iteration,
+	COALESCE(r.active_iteration_id, -1) != ri.repo_iteration_id AND r.repo_id != 1
+	AS is_orphan,
+	r.repo_id = 1 AS is_local
 FROM
-                  items            AS i
-        JOIN      mapping_statuses AS ms
-                USING (item_id)
-	JOIN      item_versions    AS iv
-	        USING (item_id)
-        JOIN      repo_iterations  AS ri
-                USING (repo_iteration_id)
-        JOIN      repos            AS r
-                USING (repo_id);
+	          item_versions    AS iv
+        JOIN      repo_iterations  AS ri USING (repo_iteration_id)
+        JOIN      repos            AS r  USING (repo_id);
 
 CREATE TABLE resolved_depended_resources(
         payload_id       INTEGER,