diff options
Diffstat (limited to 'src/hydrilla/proxy/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/tables.sql | 42 |
1 files changed, 32 insertions, 10 deletions
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 |