[proxy] move tables.sql to insite state implementation directory

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;