diff options
-rw-r--r-- | tsp/results-schema.sql | 30 | ||||
-rw-r--r-- | tsp/results-values-device.sql | 3 | ||||
-rw-r--r-- | tsp/results-values-result.sql | 5 |
3 files changed, 38 insertions, 0 deletions
diff --git a/tsp/results-schema.sql b/tsp/results-schema.sql new file mode 100644 index 0000000..3f1551d --- /dev/null +++ b/tsp/results-schema.sql @@ -0,0 +1,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 +; diff --git a/tsp/results-values-device.sql b/tsp/results-values-device.sql new file mode 100644 index 0000000..cfe3740 --- /dev/null +++ b/tsp/results-values-device.sql @@ -0,0 +1,3 @@ +INSERT OR IGNORE INTO device VALUES (NULL, "odroid"); +INSERT OR IGNORE INTO device VALUES (NULL, "minnow32"); +INSERT OR IGNORE INTO device VALUES (NULL, "minnow64"); diff --git a/tsp/results-values-result.sql b/tsp/results-values-result.sql new file mode 100644 index 0000000..d770a39 --- /dev/null +++ b/tsp/results-values-result.sql @@ -0,0 +1,5 @@ +INSERT OR IGNORE INTO result VALUES (NULL, "OK"); +INSERT OR IGNORE INTO result VALUES (NULL, "Unclear"); +INSERT OR IGNORE INTO result VALUES (NULL, "Fail"); +INSERT OR IGNORE INTO result VALUES (NULL, "Identical"); +INSERT OR IGNORE INTO result VALUES (NULL, "Snapshot"); |