7 files changed, 214 insertions, 0 deletions
diff --git a/Database/Common/commonactions.h b/Database/Common/commonactions.h
new file mode 100644
index 0000000..3bce0b6
--- /dev/null
+++ b/Database/Common/commonactions.h
@@ -0,0 +1,31 @@
+#ifndef COMMONACTIONS_H
+#define COMMONACTIONS_H
+
+#include <QDateTime>
+#include "Database/databaseinterface.h"
+#include "Logger/logprimitive.h"
+
+// insert, update or delete a server from the list
+bool insertServer(const QString         &serverAddress,
+                  const unsigned short  &serverPort,
+                  const QString         &gamename,
+                  const bool            &directBeacon);
+
+bool updateServer(const QString    &serverAddress,
+                  const unsigned short  &serverPort,
+                  const QString         &gamename,
+                  const bool            &directBeacon,
+                  const bool            &authenticated);
+
+bool existServer(const QString         &serverAddress,
+                 const unsigned short  &serverPort);
+
+QSqlQuery selectServerList(const QString &gamename,
+                           const int     &serverAge_s,
+                           const bool    &withSyncData);
+
+QStringList getGamenames(const int &serverAge_s);
+
+QHash<QString, int> getNumGames(const int &serverAge_s);
+
+#endif // COMMONACTIONS_H
diff --git a/Database/Common/existserver.cpp b/Database/Common/existserver.cpp
new file mode 100644
index 0000000..9609948
--- /dev/null
+++ b/Database/Common/existserver.cpp
@@ -0,0 +1,21 @@
+#include "commonactions.h"
+
+bool existServer(const QString         &serverAddress,
+                 const unsigned short  &serverPort)
+{
+    // find existing entry
+    QSqlQuery q;
+    QString selectString = "SELECT id FROM serverlist "
+                           "WHERE ip = :ip AND queryport = :queryport ";
+
+    // bind values and execute
+    q.prepare(selectString);
+    q.bindValue(":ip", serverAddress);
+    q.bindValue(":queryport", serverPort);
+
+    if ( ! q.exec() )
+        return reportQuery(q);
+
+    // was a row/server found?
+    return q.next();
+}
diff --git a/Database/Common/getgamenames.cpp b/Database/Common/getgamenames.cpp
new file mode 100644
index 0000000..8093f7c
--- /dev/null
+++ b/Database/Common/getgamenames.cpp
@@ -0,0 +1,28 @@
+#include "commonactions.h"
+
+QStringList getGamenames(const int &serverAge_s)
+{
+    // init output
+    QStringList gamenameList;
+
+    // retrieve active gamenames from database
+    QSqlQuery q;
+    QString selectString = "SELECT DISTINCT gamename FROM serverlist "
+                           "WHERE dt_updated > :timestamp "
+                           "ORDER BY gamename ASC";
+
+    // bind and execute
+    q.prepare(selectString);
+    q.bindValue(":timestamp", QDateTime::currentDateTime().addSecs(-serverAge_s ).toSecsSinceEpoch());
+
+    if ( ! q.exec() )
+        reportQuery(q);
+
+    // parse to stringlist
+    while ( q.next() )
+    {
+         gamenameList.append( q.value(0).toString() );
+    }
+
+    return gamenameList;
+}
diff --git a/Database/Common/getnumgames.cpp b/Database/Common/getnumgames.cpp
new file mode 100644
index 0000000..1425427
--- /dev/null
+++ b/Database/Common/getnumgames.cpp
@@ -0,0 +1,30 @@
+#include "commonactions.h"
+
+QHash<QString, int> getNumGames(const int &serverAge_s)
+{
+    QSqlQuery q;
+    QHash<QString, int> numGames;
+
+    // number of games
+    q.prepare("SELECT count(gamename) FROM gameinfo");
+    if ( ! q.exec() )
+        reportQuery(q);
+    if (q.next())
+        numGames["numTotal"] = q.value(0).toInt();
+
+    // number of active games
+    QString selectString = "SELECT count(gamename) FROM gameinfo "
+                           "WHERE num_direct > 0 "
+                           "OR    num_total  > 0 ";
+    q.prepare(selectString);
+    q.bindValue(":timestamp", QDateTime::currentDateTime().addSecs(-serverAge_s ).toSecsSinceEpoch());
+    if ( ! q.exec() )
+        reportQuery(q);
+    if (q.next())
+        numGames["numActive"] = q.value(0).toInt();
+
+
+    // TODO: get more relevant stats
+
+    return numGames;
+}
diff --git a/Database/Common/insertserver.cpp b/Database/Common/insertserver.cpp
new file mode 100644
index 0000000..05b2eaa
--- /dev/null
+++ b/Database/Common/insertserver.cpp
@@ -0,0 +1,26 @@
+#include "commonactions.h"
+
+// insert a server into the list
+bool insertServer(const QString         &serverAddress,
+                  const unsigned short  &serverPort,
+                  const QString         &gamename,
+                  const bool            &directBeacon)
+{
+    // insert query string
+    QSqlQuery q;
+    QString insertString = "INSERT INTO serverlist (ip, queryport, gamename, f_direct) "
+                           "VALUES (:ip, :queryport, :gamename, :directbeacon)";
+
+    // bind values and execute
+    q.prepare(insertString);
+    q.bindValue(":ip", serverAddress);
+    q.bindValue(":queryport", serverPort);
+    q.bindValue(":gamename", gamename);
+    q.bindValue(":directbeacon", ( directBeacon ? 1 : 0 ) ); // bool to int
+
+    if ( ! q.exec() )
+        return reportQuery(q);
+
+    // was a row inserted?
+    return (q.numRowsAffected() > 0);
+}
diff --git a/Database/Common/selectserverlist.cpp b/Database/Common/selectserverlist.cpp
new file mode 100644
index 0000000..c4797ed
--- /dev/null
+++ b/Database/Common/selectserverlist.cpp
@@ -0,0 +1,31 @@
+#include "commonactions.h"
+
+QSqlQuery selectServerList(const QString &gamename,
+                           const int     &serverAge_s,
+                           const bool    &withSyncData)
+{
+    // retrieve servers from database
+    QSqlQuery q;
+    QString selectString = "SELECT ip, queryport FROM serverlist "
+                           "WHERE gamename = :gamename ";
+
+    if ( withSyncData )
+    {
+        // relies on sync data to be accurate (if checker is not enabled)
+        selectString += "AND (dt_updated > :timestamp OR dt_sync > :timestamp)";
+    }
+    else
+    {
+        // sync data may not be (sufficiently) accurate, only use data that we verified directly
+        selectString += "AND dt_updated > :timestamp";
+    }
+
+    // bind values and execute
+    q.prepare(selectString);
+    q.bindValue(":gamename", gamename);
+    q.bindValue(":timestamp", QDateTime::currentDateTime().addSecs(-serverAge_s ).toSecsSinceEpoch());
+    if ( ! q.exec() )
+        reportQuery(q);
+
+    return q;
+}
diff --git a/Database/Common/updateserver.cpp b/Database/Common/updateserver.cpp
new file mode 100644
index 0000000..24fa26f
--- /dev/null
+++ b/Database/Common/updateserver.cpp
@@ -0,0 +1,47 @@
+#include "commonactions.h"
+
+bool updateServer(const QString         &serverAddress,
+                  const unsigned short  &serverPort,
+                  const QString         &gamename,
+                  const bool            &directBeacon,
+                  const bool            &authenticated
+                  )
+{
+    // update existing entry, but do not insert.
+    QSqlQuery q;
+    QString updateString;
+
+    // update with available values
+    updateString = "UPDATE serverlist SET ";
+
+    /*
+     * Note that direct/auth set to 'false' will NOT override a previous value in the database
+     */
+
+    // is this a direct beacon?
+    if (directBeacon)
+    {
+        updateString += "f_direct = 1, ";
+        updateString += "dt_beacon  = :timestamp, ";
+    }
+
+    // did the server authenticate?
+    if (authenticated) updateString += "f_auth   = 1, ";
+
+    updateString +=  "gamename = :gamename, "
+                     "dt_updated = :timestamp "
+                     "WHERE ip = :ip AND queryport = :queryport";
+
+    // bind values and execute
+    q.prepare(updateString);
+    q.bindValue(":ip", serverAddress);
+    q.bindValue(":queryport", serverPort);
+    q.bindValue(":gamename", gamename);
+    q.bindValue(":timestamp", QDateTime::currentSecsSinceEpoch() );
+
+    if ( ! q.exec() )
+        return reportQuery(q);
+
+    // was a row updated?
+    return (q.numRowsAffected() > 0);
+}