From c100476b0a34f5098efc96bf2487f09b66b4a6c4 Mon Sep 17 00:00:00 2001
From: Wojtek Kosior <koszko@koszko.org>
Date: Mon, 22 Aug 2022 16:02:10 +0200
Subject: re-enable the functionality to load packages from ZIP file

---
 src/hydrilla/proxy/tables.sql | 18 +++++++++++++++++-
 1 file changed, 17 insertions(+), 1 deletion(-)

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

diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql
index 0c604fc..3b3506d 100644
--- a/src/hydrilla/proxy/tables.sql
+++ b/src/hydrilla/proxy/tables.sql
@@ -167,9 +167,12 @@ CREATE TABLE mapping_statuses(
 	FOREIGN KEY (item_id)
                 REFERENCES items (item_id)
 		ON DELETE CASCADE,
+        -- We'd like to set "active_version_id" to NULL when referenced entry is
+	-- deleted, but we cannot do it with ON DELETE clause because the
+	-- foreign key is composite. For now - this will be done by the
+	-- application.
 	FOREIGN KEY (active_version_id, item_id)
                 REFERENCES item_versions (item_version_id, item_id)
-		ON DELETE SET NULL
 );
 
 CREATE TABLE item_versions(
@@ -177,12 +180,19 @@ CREATE TABLE item_versions(
 
 	item_id             INTEGER NOT NULL,
         version             VARCHAR NOT NULL,
+	-- "installed" determines whether item is INSTALLED, is NOT_INSTALLED or
+	-- it FAILED_TO_INSTALL when last tried. If "required" in a row of
+	-- "mapping_statuses is set to TRUE, the mapping version and all
+	-- resource versions corresponding to it are supposed to have
+	-- "installed" set to 'I'.
+	installed           CHAR(1) NOT NULL,
 	repo_iteration_id   INTEGER NOT NULL,
 	definition          BLOB    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),
+	CHECK (installed in ('I', 'N', 'F')),
 
 	FOREIGN KEY (item_id)
                 REFERENCES items (item_id),
@@ -281,7 +291,10 @@ CREATE TABLE resolved_depended_resources(
 CREATE TABLE files(
         file_id INTEGER PRIMARY KEY,
 
+	-- File's hash as hexadecimal string.
         sha256  CHAR(64) NOT NULL,
+	-- The value of "data" - if not NULL - shall be a bytes sequence that
+	-- corresponds the hash stored in "sha256".
 	data    BLOB     NULL,
 
 	UNIQUE (sha256)
@@ -290,6 +303,9 @@ CREATE TABLE files(
 CREATE TABLE file_uses(
         file_use_id     INTEGER PRIMARY KEY,
 
+	-- If item version referenced by "item_version_id" has "installed" set
+	-- to 'I', the file referenced by "file_id" is supposed to have "data"
+	-- set to a valid, non-NULL value.
 	item_version_id INTEGER NOT NULL,
         file_id         INTEGER NOT NULL,
         name            VARCHAR NOT NULL,
-- 
cgit v1.2.3

