diff options
Diffstat (limited to 'src/hydrilla/proxy/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/tables.sql | 71 |
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, |