aboutsummaryrefslogtreecommitdiff
path: root/src/Database/Common
diff options
context:
space:
mode:
Diffstat (limited to 'src/Database/Common')
-rw-r--r--src/Database/Common/commonactions.h31
-rw-r--r--src/Database/Common/existserver.cpp21
-rw-r--r--src/Database/Common/getgamenames.cpp28
-rw-r--r--src/Database/Common/getnumgames.cpp30
-rw-r--r--src/Database/Common/insertserver.cpp26
-rw-r--r--src/Database/Common/selectserverlist.cpp31
-rw-r--r--src/Database/Common/updateserver.cpp47
7 files changed, 214 insertions, 0 deletions
diff --git a/src/Database/Common/commonactions.h b/src/Database/Common/commonactions.h
new file mode 100644
index 0000000..3bce0b6
--- /dev/null
+++ b/src/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/src/Database/Common/existserver.cpp b/src/Database/Common/existserver.cpp
new file mode 100644
index 0000000..9609948
--- /dev/null
+++ b/src/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/src/Database/Common/getgamenames.cpp b/src/Database/Common/getgamenames.cpp
new file mode 100644
index 0000000..8093f7c
--- /dev/null
+++ b/src/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/src/Database/Common/getnumgames.cpp b/src/Database/Common/getnumgames.cpp
new file mode 100644
index 0000000..1425427
--- /dev/null
+++ b/src/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/src/Database/Common/insertserver.cpp b/src/Database/Common/insertserver.cpp
new file mode 100644
index 0000000..05b2eaa
--- /dev/null
+++ b/src/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/src/Database/Common/selectserverlist.cpp b/src/Database/Common/selectserverlist.cpp
new file mode 100644
index 0000000..c4797ed
--- /dev/null
+++ b/src/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/src/Database/Common/updateserver.cpp b/src/Database/Common/updateserver.cpp
new file mode 100644
index 0000000..24fa26f
--- /dev/null
+++ b/src/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);
+}
generated by cgit v1.2.3 (git 2.25.1) at 2026-03-13 18:22:01 +0000