summaryrefslogtreecommitdiff
path: root/src/hydrilla/proxy/tables.sql
blob: 2483c96edc4b237aa1a1ae181d3e50ba716bd146 (about) (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
-- 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 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;