path: root/src/hydrilla/proxy/tables.sql

-- 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 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;