diff options
Diffstat (limited to 'src/hydrilla/proxy/state_impl/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/state_impl/tables.sql | 334 |
1 files changed, 334 insertions, 0 deletions
diff --git a/src/hydrilla/proxy/state_impl/tables.sql b/src/hydrilla/proxy/state_impl/tables.sql new file mode 100644 index 0000000..504d023 --- /dev/null +++ b/src/hydrilla/proxy/state_impl/tables.sql @@ -0,0 +1,334 @@ +-- SPDX-License-Identifier: GPL-3.0-or-later + +-- SQLite tables definitions for Haketilo proxy. +-- +-- This file is part of Hydrilla&Haketilo. +-- +-- Copyright (C) 2022 Wojtek Kosior +-- +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program. If not, see <https://www.gnu.org/licenses/>. +-- +-- +-- I, Wojtek Kosior, thereby promise not to sue for violation of this +-- file's license. Although I request that you do not make use of this +-- code in a proprietary program, I am not going to enforce this in +-- court. + +BEGIN TRANSACTION; + +CREATE TABLE general( + haketilo_version VARCHAR NOT NULL, + default_allow_scripts BOOLEAN NOT NULL, + advanced_user BOOLEAN NOT NULL, + repo_refresh_seconds INTEGER NOT NULL, + -- "mapping_use_mode" determines whether current mode is AUTO, + -- WHEN_ENABLED or QUESTION. + mapping_use_mode CHAR(1) NOT NULL, + + CHECK (rowid = 1), + CHECK (mapping_use_mode IN ('A', 'W', 'Q')), + CHECK (haketilo_version = '3.0b1') +); + +INSERT INTO general( + rowid, + haketilo_version, + default_allow_scripts, + advanced_user, + repo_refresh_seconds, + mapping_use_mode +) +VALUES( + 1, + '3.0b1', + FALSE, + FALSE, + 24 * 60 * 60, + 'Q' +); + +CREATE TABLE rules( + rule_id INTEGER PRIMARY KEY, + + pattern VARCHAR NOT NULL, + allow_scripts BOOLEAN NOT NULL, + + UNIQUE (pattern) +); + +CREATE TABLE repos( + repo_id INTEGER PRIMARY KEY, + + name VARCHAR NOT NULL, + url VARCHAR NOT NULL, + deleted BOOLEAN NOT NULL DEFAULT FALSE, + next_iteration INTEGER NOT NULL DEFAULT 1, + active_iteration_id INTEGER NULL, + last_refreshed INTEGER NULL, + + UNIQUE (name), + -- 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, url, deleted) +VALUES(1, '', '', TRUE); + +INSERT INTO repos(name, url) +VALUES('Hydrilla official', 'https://hydrilla.koszko.org/api_v2/'); + +CREATE TABLE repo_iterations( + repo_iteration_id INTEGER PRIMARY KEY, + + repo_id INTEGER NOT NULL, + iteration INTEGER NOT NULL, + + UNIQUE (repo_id, iteration), + + FOREIGN KEY (repo_id) + REFERENCES repos (repo_id) +); + +CREATE VIEW orphan_iterations +AS +SELECT + ri.repo_iteration_id, + ri.repo_id, + ri.iteration +FROM + repo_iterations AS ri + JOIN repos AS r USING (repo_id) +WHERE + COALESCE(r.active_iteration_id != ri.repo_iteration_id, TRUE); + +CREATE TABLE items( + item_id INTEGER PRIMARY KEY, + + -- "type" determines whether it's resource or mapping. + type CHAR(1) NOT NULL, + identifier VARCHAR NOT NULL, + + UNIQUE (type, identifier), + CHECK (type IN ('R', 'M')) +); + +CREATE TABLE mapping_statuses( + -- The item with this id shall be a mapping ("type" = 'M'). For each + -- mapping row in "items" there must be an accompanying row in this + -- table. + item_id INTEGER PRIMARY KEY, + + -- "enabled" determines whether mapping's status is ENABLED, + -- DISABLED or NO_MARK. + 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, + -- 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')), + + 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) +); + +CREATE TABLE item_versions( + item_version_id INTEGER PRIMARY KEY, + + 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, + 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), + FOREIGN KEY (repo_iteration_id) + REFERENCES repo_iterations (repo_iteration_id) +); + +CREATE VIEW repo_display_infos +AS +SELECT + r.repo_id, r.name, r.url, r.deleted, r.last_refreshed, + COALESCE(SUM(i.type = 'R'), 0) AS resource_count, + COALESCE(SUM(i.type = 'M'), 0) AS mapping_count +FROM + repos AS r + LEFT JOIN repo_iterations AS ir USING (repo_id) + LEFT JOIN item_versions AS iv USING (repo_iteration_id) + LEFT JOIN items AS i USING (item_id) +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, +-- the dependency tree is recomputed. In the process the "payloads" table gets +-- cleare and repopulated together with the "resolved_depended_resources" that +-- depends on it. +CREATE TABLE payloads( + payload_id INTEGER PRIMARY KEY, + + mapping_item_id INTEGER NOT NULL, + pattern VARCHAR NOT NULL, + -- What privileges should be granted on pages where this + -- resource/mapping is used. + eval_allowed BOOLEAN NOT NULL, + cors_bypass_allowed BOOLEAN NOT NULL, + + UNIQUE (mapping_item_id, pattern), + + FOREIGN KEY (mapping_item_id) + REFERENCES item_versions (item_version_id) + ON DELETE CASCADE +); + +CREATE VIEW item_versions_extra +AS +SELECT + iv.item_version_id, + iv.item_id, + iv.version, + iv.installed, + iv.repo_iteration_id, + iv.definition, + iv.active, + r.repo_id, r.name AS repo, + ri.repo_iteration_id, 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 + 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, + resource_item_id INTEGER, + + -- "idx" determines the ordering of resources. + idx INTEGER, + + PRIMARY KEY (payload_id, resource_item_id), + + FOREIGN KEY (payload_id) + REFERENCES payloads (payload_id) + ON DELETE CASCADE, + FOREIGN KEY (resource_item_id) + REFERENCES item_versions (item_version_id) + ON DELETE CASCADE +) WITHOUT ROWID; + +CREATE TABLE resolved_required_mappings( + requiring_mapping_id INTEGER, + required_mapping_id INTEGER, + + PRIMARY KEY (requiring_mapping_id, required_mapping_id), + + FOREIGN KEY (requiring_mapping_id) + REFERENCES item_versions (item_version_id) + ON DELETE CASCADE, + FOREIGN KEY (required_mapping_id) + REFERENCES item_versions (item_version_id) + ON DELETE CASCADE +) WITHOUT ROWID; + +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) +); + +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, + -- "type" determines whether it's license file or web resource. + type CHAR(1) NOT NULL, + mime_type VARCHAR NOT NULL, + -- "idx" determines the ordering of item's files of given type. + idx INTEGER NOT NULL, + + CHECK (type IN ('L', 'W')), + UNIQUE(item_version_id, type, idx), + UNIQUE(item_version_id, type, name), + + FOREIGN KEY (item_version_id) + REFERENCES item_versions(item_version_id) + ON DELETE CASCADE, + FOREIGN KEY (file_id) + REFERENCES files(file_id) +); + +COMMIT TRANSACTION; |