Restructured the database schema.

The most important change of this patch
is a restructring of the database schema
in order to better support current
and future use cases.

In particular, the 'result' table has been reduced
in size (which is important since this table is
the one that will grow on a regular basis), and
a 'bmcontext' table has been introduced as an extra
structural level that is expected to make certain
queries faster than they would have otherwise been
(due to reduced need for complex join expressions
etc.).

1 files changed, 42 insertions, 39 deletions

diff --git a/src/bmserver/main.cpp b/src/bmserver/main.cpp
index ed042f8..a5431b5 100644
--- a/src/bmserver/main.cpp
+++ b/src/bmserver/main.cpp
@@ -47,13 +47,6 @@ static bool initDatabase(const QString &dbfile, QString *error)
 
     // *** Create tables ***
 
-    // benchmark
-    ok = query.exec(
-        "CREATE TABLE benchmark(id INTEGER PRIMARY KEY AUTOINCREMENT"
-        ", testCase TEXT NOT NULL, testFunction TEXT NOT NULL, dataTag TEXT NOT NULL"
-        ", UNIQUE(testCase, testFunction, dataTag));");
-    Q_ASSERT(ok);
-
     // metric
     ok = query.exec(
         "CREATE TABLE metric(id INTEGER PRIMARY KEY AUTOINCREMENT"
@@ -82,6 +75,31 @@ static bool initDatabase(const QString &dbfile, QString *error)
         ", UNIQUE(gitRepo, gitBranch));");
     Q_ASSERT(ok);
 
+    // context
+    ok = query.exec(
+        "CREATE TABLE context(id INTEGER PRIMARY KEY AUTOINCREMENT"
+        ", metricId INTEGER REFERENCES metric(id)"
+        ", platformId INTEGER REFERENCES platform(id)"
+        ", hostId INTEGER REFERENCES host(id)"
+        ", branchId INTEGER REFERENCES branch(id)"
+        ", UNIQUE(metricId, platformId, hostId, branchId));");
+    Q_ASSERT(ok);
+
+    // benchmark
+    ok = query.exec(
+        "CREATE TABLE benchmark(id INTEGER PRIMARY KEY AUTOINCREMENT"
+        ", testCase TEXT NOT NULL, testFunction TEXT NOT NULL, dataTag TEXT NOT NULL"
+        ", UNIQUE(testCase, testFunction, dataTag));");
+    Q_ASSERT(ok);
+
+    // bmcontext
+    ok = query.exec(
+        "CREATE TABLE bmcontext(id INTEGER PRIMARY KEY AUTOINCREMENT"
+        ", contextId INTEGER REFERENCES context(id)"
+        ", benchmarkId INTEGER REFERENCES benchmark(id)"
+        ", UNIQUE(contextId, benchmarkId));");
+    Q_ASSERT(ok);
+
     // snapshot
     ok = query.exec(
         "CREATE TABLE snapshot(id INTEGER PRIMARY KEY AUTOINCREMENT"
@@ -92,67 +110,52 @@ static bool initDatabase(const QString &dbfile, QString *error)
     // result
     ok = query.exec(
         "CREATE TABLE result(id INTEGER PRIMARY KEY AUTOINCREMENT"
-        ", benchmarkId INTEGER REFERENCES benchmark(id)"
-        ", metricId INTEGER REFERENCES metric(id)"
-        ", platformId INTEGER REFERENCES platform(id)"
-        ", hostId INTEGER REFERENCES host(id)"
-        ", branchId INTEGER REFERENCES branch(id)"
+        ", bmcontextId INTEGER REFERENCES bmcontext(id)"
         ", snapshotId INTEGER REFERENCES snapshot(id)"
         ", value REAL NOT NULL"
-        ", UNIQUE(benchmarkId, metricId, platformId, hostId, branchId, snapshotId));");
-    Q_ASSERT(ok);
-
-    // context
-    ok = query.exec(
-        "CREATE TABLE context(id INTEGER PRIMARY KEY AUTOINCREMENT"
-        ", metricId INTEGER REFERENCES metric(id)"
-        ", platformId INTEGER REFERENCES platform(id)"
-        ", hostId INTEGER REFERENCES host(id)"
-        ", branchId INTEGER REFERENCES branch(id)"
-        ", UNIQUE(metricId, platformId, hostId, branchId));");
+        ", UNIQUE(bmcontextId, snapshotId));");
     Q_ASSERT(ok);
 
-
     // *** Create indexes ***
-    ok = query.exec("CREATE INDEX index_result_benchmarkId ON result(benchmarkId);");
-    Q_ASSERT(ok);
-
-    ok = query.exec("CREATE INDEX index_result_metricId ON result(metricId);");
+    ok = query.exec("CREATE INDEX index_metric_name ON metric(name);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_result_platformId ON result(platformId);");
+    ok = query.exec("CREATE INDEX index_platform_name ON platform(name);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_result_hostId ON result(hostId);");
+    ok = query.exec("CREATE INDEX index_host_name ON host(name);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_result_branchId ON result(branchId);");
+    ok = query.exec("CREATE INDEX index_branch_name ON branch(gitRepo, gitBranch);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_result_snapshotId ON result(snapshotId);");
+    ok = query.exec(
+        "CREATE INDEX index_context_all ON context(metricId, platformId, hostId, branchId);");
     Q_ASSERT(ok);
 
     ok = query.exec(
         "CREATE INDEX index_benchmark_name ON benchmark(testCase, testFunction, dataTag);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_metric_name ON metric(name);");
+    ok = query.exec("CREATE INDEX index_bmcontext_contextId ON bmcontext(contextId);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_platform_name ON platform(name);");
+    ok = query.exec("CREATE INDEX index_bmcontext_benchmarkId ON bmcontext(benchmarkId);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_host_name ON host(name);");
+    ok = query.exec("CREATE INDEX index_bmcontext_all ON bmcontext(contextId, benchmarkId);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_branch_name ON branch(gitRepo, gitBranch);");
+    ok = query.exec("CREATE INDEX index_snapshot_timestamp ON snapshot(timestamp);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_snapshot_timestamp ON snapshot(timestamp);");
+    ok = query.exec("CREATE INDEX index_result_snapshotId ON result(snapshotId);");
     Q_ASSERT(ok);
 
-    ok = query.exec(
-        "CREATE INDEX index_context_all ON context(metricId, platformId, hostId, branchId);");
+    ok = query.exec("CREATE INDEX index_result_bmcontextId ON result(bmcontextId);");
+    Q_ASSERT(ok);
+
+    ok = query.exec("CREATE INDEX index_result_all ON result(snapshotId, bmcontextId);");
     Q_ASSERT(ok);
 
     return true;