summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--tsp/results-schema.sql30
-rw-r--r--tsp/results-values-device.sql3
-rw-r--r--tsp/results-values-result.sql5
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");