Moved timestamp from snapshot to result.

This patch moves the timestamp attribute from
the snapshot table to the result table. This
is done for two purposes:

1) Retrieving a pure result history (not including the
SHA-1 values) becomes quicker, since a join is avoided
(simply fetch all (timestamp, value) pairs matching a
given BM context).

2) It constitutes the first of two steps towards supporting
multiple result recordings per (SHA-1, BM context)
combination (the other step will be to remove the current
uniqueness constraint on that combination!).
The (future) use case is to improve statistical confidence
by e.g. taking the median value of the set of values for
a certain (SHA-1, BM context) combination as the representative
value for the combination.

4 files changed, 24 insertions, 23 deletions

diff --git a/doc/er-diagram.dia b/doc/er-diagram.dia
index a860b22..22bd8f9 100644
--- a/doc/er-diagram.dia
+++ b/doc/er-diagram.dia
diff --git a/src/bm/bmrequest.cpp b/src/bm/bmrequest.cpp
index 063dd19..da685fd 100644
--- a/src/bm/bmrequest.cpp
+++ b/src/bm/bmrequest.cpp
@@ -340,15 +340,12 @@ bool BMRequest::getBranchId(
 }
 
 bool BMRequest::getSnapshotId(
-    QString *reply, int *snapshotId, const QString &sha1, const QString &timestamp,
-    bool autoInsert) const
+    QString *reply, int *snapshotId, const QString &sha1, bool autoInsert) const
 {
     const QStringList colNames = QStringList() << "sha1";
     const QStringList colValues = QStringList() << sha1;
     return getId(
-        reply, snapshotId, "snapshot",
-        colNames, colValues,
-        colNames + QStringList("timestamp"), colValues + QStringList(timestamp), autoInsert);
+        reply, snapshotId, "snapshot", colNames, colValues, colNames, colValues, autoInsert);
 }
 
 bool BMRequest::getContextId(
@@ -566,7 +563,8 @@ bool BMRequest::getOrInsertBMContextId(
 }
 
 bool BMRequest::insertOrReplaceResult(
-    QString *reply, const int bmcontextId, const int snapshotId, const qreal value) const
+    QString *reply, const int bmcontextId, const int snapshotId, const int timestamp,
+    const qreal value) const
 {
     QSqlQuery *query = createQuery();
 
@@ -593,9 +591,9 @@ bool BMRequest::insertOrReplaceResult(
         if (!query->exec(
                 QString(
                     "UPDATE result"
-                    "   SET value=%1"
-                    " WHERE id=%2;")
-                .arg(value).arg(id))) {
+                    "   SET timestamp=%1, value=%2"
+                    " WHERE id=%3;")
+                .arg(timestamp).arg(value).arg(id))) {
             *reply = errorReply(*query, name(), "failed to update result (exec() failed)");
             deleteQuery(query, Rollback);
             return false;
@@ -611,9 +609,9 @@ bool BMRequest::insertOrReplaceResult(
 
         if (!query->exec(
                 QString(
-                    "INSERT INTO result (bmcontextId, snapshotId, value) "
-                    "VALUES (%1, %2, %3);")
-                .arg(bmcontextId).arg(snapshotId).arg(value))) {
+                    "INSERT INTO result (bmcontextId, snapshotId, timestamp, value) "
+                    "VALUES (%1, %2, %3, %4);")
+                .arg(bmcontextId).arg(snapshotId).arg(timestamp).arg(value))) {
             *reply = errorReply(*query, name(), "failed to insert result (exec() failed)");
             deleteQuery(query, Rollback);
             return false;
@@ -1214,7 +1212,9 @@ QByteArray BMRequest_PutResults::toReplyBuffer()
     host = contextElem.attributeNode("host").value();
     gitRepo = contextElem.attributeNode("gitRepo").value();
     gitBranch = contextElem.attributeNode("gitBranch").value();
-    timestamp = contextElem.attributeNode("timestamp").value();
+    bool ok;
+    const int timestamp_ = contextElem.attributeNode("timestamp").value().toInt(&ok);
+    Q_ASSERT(ok);
     sha1 = contextElem.attributeNode("sha1").value();
     testCase = contextElem.attributeNode("testCase").value();
 
@@ -1233,7 +1233,7 @@ QByteArray BMRequest_PutResults::toReplyBuffer()
         return xmlConvert(reply);
 
     int snapshotId;
-    if (!getSnapshotId(&reply, &snapshotId, sha1, timestamp, true))
+    if (!getSnapshotId(&reply, &snapshotId, sha1, true))
         return xmlConvert(reply);
 
     QMap<QString, int> contextIds; // ### replace with QHash?
@@ -1284,8 +1284,8 @@ QByteArray BMRequest_PutResults::toReplyBuffer()
             if (!getOrInsertBMContextId(&reply, &bmcontextId, contextId, benchmarkId))
                 return xmlConvert(reply);
 
-            // Insert value ...
-            if (!insertOrReplaceResult(&reply, bmcontextId, snapshotId, value))
+            // Insert timestamp and value ...
+            if (!insertOrReplaceResult(&reply, bmcontextId, snapshotId, timestamp_, value))
                 return xmlConvert(reply);
         }
     }
diff --git a/src/bm/bmrequest.h b/src/bm/bmrequest.h
index aa5074a..4ce2605 100644
--- a/src/bm/bmrequest.h
+++ b/src/bm/bmrequest.h
@@ -85,8 +85,7 @@ protected:
         QString *reply, int *branchId, const QString &gitRepo, const QString &gitBranch,
         bool autoInsert = false) const;
     bool getSnapshotId(
-        QString *reply, int *snapshotId, const QString &sha1, const QString &timestamp,
-        bool autoInsert = false) const;
+        QString *reply, int *snapshotId, const QString &sha1, bool autoInsert = false) const;
     bool getContextId(
         QString *reply, int *id, const QString &metric, const QString &platform,
         const QString &host, const QString &gitRepo, const QString &gitBranch) const;
@@ -101,7 +100,8 @@ protected:
     bool getOrInsertBMContextId(
         QString *reply, int *id, const int contextId, const int benchmarkId) const;
     bool insertOrReplaceResult(
-        QString *reply, const int bmcontextId, const int snapshotId, const qreal value) const;
+        QString *reply, const int bmcontextId, const int snapshotId, const int timestamp,
+        const qreal value) const;
     bool getBenchmark(
         QString *reply, int benchmarkId, QString *testCase, QString *testFunction,
         QString *dataTag) const;
diff --git a/src/bmserver/main.cpp b/src/bmserver/main.cpp
index 8dc6a20..7da8be0 100644
--- a/src/bmserver/main.cpp
+++ b/src/bmserver/main.cpp
@@ -103,7 +103,7 @@ static bool initDatabase(const QString &dbfile, QString *error)
     // snapshot
     ok = query.exec(
         "CREATE TABLE snapshot(id INTEGER PRIMARY KEY AUTOINCREMENT"
-        ", sha1 TEXT NOT NULL, timestamp INTEGER NOT NULL"
+        ", sha1 TEXT NOT NULL"
         ", UNIQUE(sha1));");
     Q_ASSERT(ok);
 
@@ -112,6 +112,7 @@ static bool initDatabase(const QString &dbfile, QString *error)
         "CREATE TABLE result(id INTEGER PRIMARY KEY AUTOINCREMENT"
         ", bmcontextId INTEGER REFERENCES bmcontext(id)"
         ", snapshotId INTEGER REFERENCES snapshot(id)"
+        ", timestamp INTEGER NOT NULL"
         ", value REAL NOT NULL"
         ", UNIQUE(bmcontextId, snapshotId));");
     Q_ASSERT(ok);
@@ -166,15 +167,15 @@ static bool initDatabase(const QString &dbfile, QString *error)
     ok = query.exec("CREATE INDEX index_bmcontext_all ON bmcontext(contextId, benchmarkId);");
     Q_ASSERT(ok);
 
-    ok = query.exec("CREATE INDEX index_snapshot_timestamp ON snapshot(timestamp);");
-    Q_ASSERT(ok);
-
     ok = query.exec("CREATE INDEX index_result_snapshotId ON result(snapshotId);");
     Q_ASSERT(ok);
 
     ok = query.exec("CREATE INDEX index_result_bmcontextId ON result(bmcontextId);");
     Q_ASSERT(ok);
 
+    ok = query.exec("CREATE INDEX index_result_timestamp ON result(timestamp);");
+    Q_ASSERT(ok);
+
     ok = query.exec("CREATE INDEX index_result_all ON result(snapshotId, bmcontextId);");
     Q_ASSERT(ok);