From a072f5d48406d7e4fe69c5d9d02d64384d42660d Mon Sep 17 00:00:00 2001 From: Wojtek Kosior Date: Fri, 7 Oct 2022 12:37:04 +0200 Subject: [proxy] move tables.sql to insite state implementation directory --- src/hydrilla/proxy/state_impl/concrete_state.py | 2 +- src/hydrilla/proxy/state_impl/tables.sql | 331 ++++++++++++++++++++++++ src/hydrilla/proxy/tables.sql | 331 ------------------------ 3 files changed, 332 insertions(+), 332 deletions(-) create mode 100644 src/hydrilla/proxy/state_impl/tables.sql delete mode 100644 src/hydrilla/proxy/tables.sql 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 . +-- +-- +-- 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; diff --git a/src/hydrilla/proxy/tables.sql b/src/hydrilla/proxy/tables.sql deleted file mode 100644 index 6ceea25..0000000 --- a/src/hydrilla/proxy/tables.sql +++ /dev/null @@ -1,331 +0,0 @@ --- 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 . --- --- --- 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; -- cgit v1.2.3