tsp/results-schema.sql


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

CREATE TABLE IF NOT EXISTS device (
    did INTEGER PRIMARY KEY,
    dname TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS result (
    rid INTEGER PRIMARY KEY,
    rname TEXT NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS test (
    tid INTEGER PRIMARY KEY AUTOINCREMENT,
    tnumber TEXT,
    tdate TEXT,
    trid INTEGER REFERENCES result (rid),
    tdid INTEGER REFERENCES device (did),
    UNIQUE (tnumber, tdid) ON CONFLICT REPLACE
);

CREATE VIEW IF NOT EXISTS currentstatus AS
    SELECT
        test.tnumber AS "SR",
        test.tdate AS "Date",
        device.dname AS "Device",
        result.rname AS "Status"
    FROM test
    INNER JOIN device ON test.tdid = device.did
    INNER JOIN result ON result.rid = test.trid
    ORDER BY SR DESC
;