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