aboutsummaryrefslogtreecommitdiff
path: root/src/hydrilla/proxy/state_impl
diff options
context:
space:
mode:
authorWojtek Kosior <koszko@koszko.org>2022-10-07 12:37:04 +0200
committerWojtek Kosior <koszko@koszko.org>2022-10-07 12:37:04 +0200
commita072f5d48406d7e4fe69c5d9d02d64384d42660d (patch)
treee0126fff4e77c9350d6b2cb3ff43850c28780ad2 /src/hydrilla/proxy/state_impl
parentc5073d9e7d68449bb9aba2791b8a9b3f4d707362 (diff)
downloadhaketilo-hydrilla-a072f5d48406d7e4fe69c5d9d02d64384d42660d.tar.gz
haketilo-hydrilla-a072f5d48406d7e4fe69c5d9d02d64384d42660d.zip
[proxy] move tables.sql to insite state implementation directory
Diffstat (limited to 'src/hydrilla/proxy/state_impl')
-rw-r--r--src/hydrilla/proxy/state_impl/concrete_state.py2
-rw-r--r--src/hydrilla/proxy/state_impl/tables.sql331
2 files changed, 332 insertions, 1 deletions
diff --git a/src/hydrilla/proxy/state_impl/concrete_state.py b/src/hydrilla/proxy/state_impl/concrete_state.py
index d8706e8..f189aa2 100644
--- a/src/hydrilla/proxy/state_impl/concrete_state.py
+++ b/src/hydrilla/proxy/state_impl/concrete_state.py
@@ -76,7 +76,7 @@ def _prepare_database(connection: sqlite3.Connection) -> None:
(db_initialized,), = cursor.fetchall()
if not db_initialized:
- cursor.executescript((here.parent / 'tables.sql').read_text())
+ cursor.executescript((here / 'tables.sql').read_text())
else:
cursor.execute(
'''
diff --git a/src/hydrilla/proxy/state_impl/tables.sql b/src/hydrilla/proxy/state_impl/tables.sql
new file mode 100644
index 0000000..6ceea25
--- /dev/null
+++ b/src/hydrilla/proxy/state_impl/tables.sql
@@ -0,0 +1,331 @@
+-- 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);
+
+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;