aboutsummaryrefslogtreecommitdiff
path: root/src/hydrilla/proxy/tables.sql
blob: 2a6cac65eac858415828878c16509e5528d8f771 (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
-- 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,
	-- "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  ((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,

        UNIQUE (item_id, version, repo_iteration_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,
	-- 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 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_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),
	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;