Add files via upload

12 files changed, 462 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);
+}
diff --git a/Database/closedatabase.cpp b/Database/closedatabase.cpp
new file mode 100644
index 0000000..6f3ff89
--- /dev/null
+++ b/Database/closedatabase.cpp
@@ -0,0 +1,7 @@
+#include "databaseinterface.h"
+
+void closeDatabase()
+{
+    QSqlDatabase dbi;
+    dbi.close();
+}
diff --git a/Database/createtables.cpp b/Database/createtables.cpp
new file mode 100644
index 0000000..b437163
--- /dev/null
+++ b/Database/createtables.cpp
@@ -0,0 +1,129 @@
+#include "databaseinterface.h"
+
+bool createTables()
+{
+    { // create serverlist
+        QSqlQuery q;
+        QString createServerlist = QStringLiteral(
+            "CREATE TABLE serverlist("
+            "id            INTEGER PRIMARY KEY AUTOINCREMENT,"
+            "gamename      TEXT    NOT NULL DEFAULT '',"
+            "ip            TEXT    NOT NULL ,"
+            "queryport     INTEGER NOT NULL DEFAULT 0,"
+            "t_protocol    INTEGER NOT NULL DEFAULT 0,"
+            "f_blacklist   INTEGER NOT NULL DEFAULT 0,"
+            "f_auth        INTEGER NOT NULL DEFAULT 0,"
+            "f_direct      INTEGER NOT NULL DEFAULT 0,"
+            "dt_added      BIGINT  DEFAULT (CAST(strftime('%s','now') AS BIGINT)),"
+            "dt_beacon     BIGINT  DEFAULT (CAST(strftime('%s','now') AS BIGINT)),"
+            "dt_sync       BIGINT  DEFAULT (CAST(strftime('%s','now') AS BIGINT)),"
+            "dt_updated    BIGINT  DEFAULT (CAST(strftime('%s','now') AS BIGINT))"
+            ")");
+
+        // bind values and execute (not all db-interfaces support prepare(create table), exec directly)
+        if ( ! q.exec(createServerlist) )
+            return reportQuery(q);
+    }
+
+    { // create serverinfo
+        QSqlQuery q;
+        QString createServerinfo = QStringLiteral(
+            "CREATE TABLE serverinfo("
+                "sid                    INTEGER NOT NULL DEFAULT 0,"
+                "hostport               INTEGER NOT NULL DEFAULT 0,"
+                "hostname               TEXT,"
+                "gamever                TEXT,"
+                "minnetver              TEXT,"
+                "country                TEXT,"
+                "location               TEXT,"
+                "listenserver           TEXT,"
+                "adminname              TEXT,"
+                "adminemail             TEXT,"
+                "password               TEXT,"
+                "gametype               TEXT,"
+                "gamestyle              TEXT,"
+                "changelevels           TEXT,"
+                "maptitle               TEXT,"
+                "mapname                TEXT,"
+                "numplayers             INTEGER DEFAULT 0,"
+                "maxplayers             INTEGER DEFAULT 0,"
+                "minplayers             INTEGER DEFAULT 0,"
+                "botskill               TEXT,"
+                "balanceteams           TEXT,"
+                "playersbalanceteams    TEXT,"
+                "friendlyfire           TEXT,"
+                "maxteams               TEXT,"
+                "timelimit              TEXT,"
+                "goalteamscore          TEXT,"
+                "fraglimit              TEXT,"
+                "mutators               TEXT DEFAULT 'None',"
+                "misc                   TEXT,"
+                "dt_serverinfo          BIGINT DEFAULT (CAST(strftime('%s','now') AS BIGINT))"
+            ")");
+
+        // bind values and execute
+        if ( ! q.exec(createServerinfo) )
+            return reportQuery(q);
+    }
+
+    { // create playerinfo
+        QSqlQuery q;
+        QString createPlayerlist = QStringLiteral(
+            "CREATE TABLE playerinfo("
+                "sid        INTEGER NOT NULL DEFAULT 0,"
+                "name       TEXT DEFAULT 'Player',"
+                "team       TEXT,"
+                "frags      INTEGER DEFAULT 0,"
+                "mesh       TEXT,"
+                "skin       TEXT,"
+                "face       TEXT,"
+                "ping       INTEGER DEFAULT 0,"
+                "misc       TEXT,"
+                "dt_player  BIGINT DEFAULT (CAST(strftime('%s','now') AS BIGINT))"
+            ")");
+
+        // bind values and execute
+        if ( ! q.exec(createPlayerlist) )
+            return reportQuery(q);
+    }
+
+    { // create gameinfo
+        QSqlQuery q;
+        QString createGamelist = QStringLiteral(
+            "CREATE TABLE gameinfo("
+                "gamename   TEXT    NOT NULL DEFAULT '',"
+                "label      TEXT    NOT NULL DEFAULT '',"
+                "num_total  INTEGER NOT NULL DEFAULT 0,"
+                "num_direct INTEGER NOT NULL DEFAULT 0"
+            ")");
+
+        // bind values and execute
+        if ( ! q.exec(createGamelist) )
+            return reportQuery(q);
+    }
+
+    { // create version info
+        QSqlQuery q;
+        QString createVersion = QStringLiteral(
+            "CREATE TABLE versioninfo("
+                "type    TEXT    NOT NULL DEFAULT '',"
+                "version TEXT    NOT NULL DEFAULT ''"
+            ")");
+
+        // bind values and execute
+        if ( ! q.exec(createVersion) )
+            return reportQuery(q);
+
+        // insert current version directly into the db during creation
+        q.prepare("INSERT INTO versioninfo (type, version) VALUES (\"database\", :currentver)");
+        q.bindValue(":currentver", BUILD_VERSION);
+
+        if ( ! q.exec() )
+            reportQuery(q);
+    }
+
+    // TODO: optional: write an sql file to /data/tables.sql for easy access on generated tables.
+
+    // no errors
+    return true;
+}
diff --git a/Database/databaseinterface.h b/Database/databaseinterface.h
new file mode 100644
index 0000000..f80c93a
--- /dev/null
+++ b/Database/databaseinterface.h
@@ -0,0 +1,24 @@
+#ifndef DATABASEINTERFACE_H
+#define DATABASEINTERFACE_H
+
+#include <QSqlDatabase>
+#include <QSqlQuery>
+#include <QSqlError>
+#include <QDataStream>
+#include "Core/version.h"
+#include "Logger/logprimitive.h"
+
+// database file path (following README structure)
+const QString _sqlitePath = "../data/masterserver.db";
+
+// init and close
+bool initDatabase(const QString applicationPath);
+void closeDatabase();
+
+// report execution errors (terminal/display only)
+bool reportQuery(const QSqlQuery &q); // always returns false!
+
+// generate tables in first run
+bool createTables();
+
+#endif // DATABASEINTERFACE_H
diff --git a/Database/initdatabase.cpp b/Database/initdatabase.cpp
new file mode 100644
index 0000000..06cd37c
--- /dev/null
+++ b/Database/initdatabase.cpp
@@ -0,0 +1,77 @@
+#include "databaseinterface.h"
+
+bool initDatabase(const QString applicationPath)
+{
+    // open SQLite database
+    QSqlDatabase dbi = QSqlDatabase::addDatabase( "QSQLITE" );
+    dbi.setDatabaseName(applicationPath + "/" + _sqlitePath);
+
+    // open database
+    if ( ! dbi.open() )
+    {
+        QSqlError sqlError = dbi.lastError();
+        logPrimitive() << "Error opening database: " << sqlError.text() << endl;
+        return false;
+    }
+
+    // speed up SQLite with keeping journals in memory and asynchronous writing
+    dbi.exec("PRAGMA synchronous = OFF");
+    dbi.exec("PRAGMA journal_mode = MEMORY");
+
+    { // check if the database was generated with this version of the software
+
+        // serverlist exists (and thus the other tables exist) but not version
+        if ( dbi.tables().contains("serverlist") and ! dbi.tables().contains("versioninfo"))
+        {
+            // outdated because the "version" table does not even exist yet (pre-0.14)
+            logPrimitive() << "The database tables are outdated (pre-0.14). Please remove the current database." << endl;
+            return false;
+        }
+
+        // does the version table exist, and does it contain the correct version?
+        if ( dbi.tables().contains("versioninfo") )
+        {
+            float dbVersion = 0.0;
+            QSqlQuery versionQuery;
+            versionQuery.prepare("SELECT version FROM versioninfo WHERE type = \"database\"");
+
+            // failed query? (unlikely)
+            if ( ! versionQuery.exec() )
+                return reportQuery(versionQuery);
+
+            if (versionQuery.next())
+                dbVersion = versionQuery.value("version").toFloat();
+
+            // version check is intentionally hardcoded since the last change in database structure (currently v0.15)
+            if ( dbVersion < DATABASE_VERSION )
+            {
+                logPrimitive() << "The database tables are outdated (" << QString::number(dbVersion) << "). Please remove the current database." << endl;
+                return false;
+            }
+
+            // tables up to date
+            return true;
+        }
+    }
+
+    // specifying AUTO_CREATE_DB=y in the environment allows bypassing of the interactive prompt for non-interactive environments
+    if (qgetenv("AUTO_CREATE_DB") != "y")
+    {
+        // relevant tables do not exist. ask to generate.
+        logPrimitive() << "The database tables do not exist. Do you want to generate these? [y/N]" << endl;
+
+        // if no, do not proceed.
+        if ( ! QTextStream(stdin).readLine().startsWith("y") )
+        {
+            // do not generate. failed to load database.
+            return false;
+        }
+    }
+    else
+    {
+        logPrimitive() << "The database tables do not exist and will now be created." << endl;
+    }
+
+    // create tables and proceed
+    return createTables();
+}
diff --git a/Database/reportquery.cpp b/Database/reportquery.cpp
new file mode 100644
index 0000000..2a5d7f7
--- /dev/null
+++ b/Database/reportquery.cpp
@@ -0,0 +1,11 @@
+#include "databaseinterface.h"
+
+bool reportQuery(const QSqlQuery &q)
+{
+    logPrimitive() << "Database query error." << endl << "" << endl
+                   << "Technical info: " << q.lastQuery() << endl << "" << endl
+                   << "Reported error: " <<  q.lastError().text() << endl;
+
+    // always return false, so we can report error and return false in one line.
+    return false;
+}