diff options
Diffstat (limited to 'src/hydrilla/proxy/tables.sql')
| -rw-r--r-- | src/hydrilla/proxy/tables.sql | 235 |
1 files changed, 235 insertions, 0 deletions
diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql new file mode 100644 index 0000000..53539a7 --- /dev/null +++ b/src/hydrilla/proxy/tables.sql @@ -0,0 +1,235 @@ +-- 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 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, + 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, + repo_refresh_seconds, + mapping_use_mode +) VALUES( + 1, + '3.0b1', + 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, + next_iteration INTEGER NOT NULL, + active_iteration_id INTEGER NULL, + last_refreshed INTEGER NULL, + + UNIQUE (name), + + FOREIGN KEY (active_iteration_id) + REFERENCES repo_iterations(repo_iteration_id) + ON DELETE SET NULL +); + +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'). + item_id INTEGER PRIMARY KEY, + + -- "enabled" determines whether mapping's status is ENABLED, + -- DISABLED or NO_MARK. + enabled CHAR(1) NOT NULL, + enabled_version_id INTEGER NULL, + -- "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, + + CHECK (NOT (enabled = 'D' AND enabled_version_id IS NOT NULL)), + CHECK (NOT (enabled = 'E' AND enabled_version_id IS NULL)), + + CHECK ((frozen IS NULL) = (enabled != 'E')), + CHECK (frozen IS NULL OR frozen in ('E', 'R', 'N')) +); + +CREATE TABLE item_versions( + item_version_id INTEGER PRIMARY KEY, + + item_id INTEGER NOT NULL, + version VARCHAR NOT NULL, + repo_iteration_id INTEGER NOT NULL, + definition TEXT 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 (item_id, version, repo_iteration_id), + -- Allow foreign key from "mapping_statuses". + UNIQUE (item_version_id, item_id), + + FOREIGN KEY (item_id) + REFERENCES items (item_id), + FOREIGN KEY (repo_iteration_id) + REFERENCES repo_iterations (repo_iteration_id) +); + +CREATE TABLE payloads( + payload_id INTEGER PRIMARY KEY, + + mapping_item_id INTEGER NOT NULL, + pattern VARCHAR NOT NULL, + + UNIQUE (mapping_item_id, pattern), + + FOREIGN KEY (mapping_item_id) + REFERENCES item_versions (versioned_item_id) + ON DELETE CASCADE +); + +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), + FOREIGN KEY (resource_item_id) + REFERENCES item_versions (item_version_id) +) WITHOUT ROWID; + +-- CREATE TABLE resolved_required_mappings( +-- requiring_item_id INTEGER, +-- required_mapping_item_id INTEGER, + +-- PRIMARY KEY (requiring_item_id, required_mapping_item_id), + +-- CHECK (requiring_item_id != required_mapping_item_id), + +-- FOREIGN KEY (requiring_item_id) +-- REFERENCES items (item_id), +-- -- Note: the referenced mapping shall have installed=TRUE. +-- FOREIGN KEY (required_mapping_item_id) +-- REFERENCES mappings (item_id), +-- FOREIGN KEY (required_mapping_item_id) +-- REFERENCES items (item_id) +-- ); + +CREATE TABLE files( + file_id INTEGER PRIMARY KEY, + + sha256 CHAR(64) NOT NULL, + data BLOB NULL, + + UNIQUE (sha256) +); + +CREATE TABLE file_uses( + file_use_id INTEGER PRIMARY KEY, + + 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), + + FOREIGN KEY (item_version_id) + REFERENCES item_versions(item_version_id) + ON DELETE CASCADE, + FOREIGN KEY (file_id) + REFERENCES files(file_id) +); + +COMMIT TRANSACTION; |