pyrox.dev
1CREATE TABLE IF NOT EXISTS sha256 (
2 id integer PRIMARY KEY AUTOINCREMENT,
3 url text UNIQUE,
4 hash text NOT NULL,
5 created real DEFAULT (julianday('now'))
6);
7
8CREATE TABLE IF NOT EXISTS system (
9 id integer PRIMARY KEY AUTOINCREMENT,
10 name text NOT NULL,
11 version text NOT NULL,
12 asd text NOT NULL,
13 created real DEFAULT (julianday('now')),
14 UNIQUE(name, version)
15);
16
17CREATE TABLE IF NOT EXISTS dep (
18 system_id integer NOT NULL REFERENCES system(id),
19 dep_id integer NOT NULL REFERENCES system(id),
20 PRIMARY KEY (system_id, dep_id)
21);
22
23CREATE TABLE IF NOT EXISTS src (
24 sha256_id integer REFERENCES sha256(id),
25 system_id integer UNIQUE REFERENCES system(id)
26);
27
28DROP VIEW IF EXISTS system_view;
29CREATE VIEW IF NOT EXISTS system_view AS
30 SELECT
31 sys.name,
32 sys.version,
33 sys.asd,
34 sha.url,
35 sha.hash,
36 group_concat((SELECT name FROM system WHERE id = dep.dep_id)) as deps
37 FROM system sys
38 JOIN src ON src.system_id = sys.id
39 JOIN sha256 sha ON sha.id = src.sha256_id
40 LEFT JOIN dep ON dep.system_id = sys.id
41 GROUP BY sys.name;