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

1 files changed, 0 insertions, 331 deletions

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