hotfix 4

Parse players differently to handle gaps in data. SQL query optimisation for stats.

7 files changed, 55 insertions, 50 deletions

diff --git a/Changelog b/Changelog
index 4ad9f5a..def69ce 100644
--- a/Changelog
+++ b/Changelog
@@ -1,5 +1,8 @@
 Changelog for MasterServer-Qt
 ---
+v0.27 hotfix 4 2025-03-06:
+  * Parse serverinfo differently to handle player gaps
+  * SQL query optimisation for stats
 
 v0.27 hotfix 3 2025-03-01:
   * Expand protocol with 3rd party enctype2 code
diff --git a/bin/MasterServer-Qt5hf3 b/bin/MasterServer-Qt5hf3
deleted file mode 100644
index 4fca512..0000000
--- a/bin/MasterServer-Qt5hf3
+++ /dev/null
diff --git a/bin/MasterServer-Qt5hf4 b/bin/MasterServer-Qt5hf4
new file mode 100644
index 0000000..7233442
--- /dev/null
+++ b/bin/MasterServer-Qt5hf4
diff --git a/src/Core/version.h b/src/Core/version.h
index 7cd3254..5308bfc 100644
--- a/src/Core/version.h
+++ b/src/Core/version.h
@@ -29,7 +29,7 @@
 #define BUILD_VERSION QString("0.27")
 
 // short version (in query) -- Qt v0.n
-#define SHORT_VER QString("Qt-" + BUILD_VERSION + "hf3")
+#define SHORT_VER QString("Qt-" + BUILD_VERSION + "hf4")
 
 // build time/date
 #define BUILD_TIME QStringLiteral("%1 %2").arg(__DATE__).arg(__TIME__)
diff --git a/src/Maintenance/updatestats.cpp b/src/Maintenance/updatestats.cpp
index a19e7d9..21ef213 100644
--- a/src/Maintenance/updatestats.cpp
+++ b/src/Maintenance/updatestats.cpp
@@ -18,36 +18,23 @@ int Maintenance::updateStats()
         // get next gamename
         QString gamename = gamenameQuery.value(0).toString();
 
-        // determine beacon and server counts
-        QString selectStats = "SELECT COUNT(CASE WHEN f_direct THEN 1 END) AS num_direct, "
-                                                                 "count(*) AS num_total "
-                              "FROM serverlist "
-                              "WHERE gamename = :gamename AND dt_updated > :timestamp";
+        // hotfix hf4
+        // update number of servers directly
+        QString numStatsAll = "UPDATE gameinfo "
+                              "SET num_total = ( "
+                                "SELECT COUNT(gamename) FROM serverlist "
+                                "WHERE gamename = :gamename AND dt_updated > :timestamp "
+                              "), "
+                              "num_direct = ( "
+                                "SELECT COUNT(gamename) FROM serverlist "
+                                "WHERE gamename = :gamename AND dt_updated > :timestamp AND f_direct"
+                              ") WHERE gamename = :gamename";
+
         QSqlQuery statQuery;
-        statQuery.prepare(selectStats);
+        statQuery.prepare(numStatsAll);
         statQuery.bindValue(":gamename", gamename);
         statQuery.bindValue(":timestamp", QDateTime::currentDateTime()
-                            .addSecs(-_coreObject->Settings.ListenServerSettings.serverttl_s).toSecsSinceEpoch());
-        if ( ! statQuery.exec() )
-            return reportQuery(statQuery);
-
-        // get values
-        int num_direct = -1;
-        int num_total  = -1;
-        if ( statQuery.next() )
-        {
-            num_direct = statQuery.value("num_direct").toInt();
-            num_total  = statQuery.value("num_total").toInt();
-        }
-
-        // write to db
-        QString updateStatQuery = "UPDATE gameinfo "
-                                  "SET num_direct = :num_direct, num_total = :num_total "
-                                  "WHERE gamename = :gamename ";
-        statQuery.prepare(updateStatQuery);
-        statQuery.bindValue(":num_direct", num_direct);
-        statQuery.bindValue(":num_total", num_total);
-        statQuery.bindValue(":gamename", gamename);
+                                              .addSecs(-_coreObject->Settings.ListenServerSettings.serverttl_s).toSecsSinceEpoch());
         if ( ! statQuery.exec() )
             return reportQuery(statQuery);
 
diff --git a/src/UdpTasks/StatusChecker/playerinfoinsert.cpp b/src/UdpTasks/StatusChecker/playerinfoinsert.cpp
index ce21422..c98b8b4 100644
--- a/src/UdpTasks/StatusChecker/playerinfoinsert.cpp
+++ b/src/UdpTasks/StatusChecker/playerinfoinsert.cpp
@@ -31,29 +31,43 @@ bool StatusChecker::insertPlayerInfo(const QString                 &serverAddres
     if ( ! q.exec() )
         return reportQuery(q);
 
-    // iterate through serverInfo player data
-    int playerIndex = 0;
-    while ( serverInfo.contains( QStringLiteral("player_%1").arg(playerIndex) ) )
+    // hotfix 4: some player data may be missing intermittently (e.g player 1, 2, 4, 5 )
+
+    // regex to find player_%d
+    static QRegularExpression p_regex("player_(\\d+)");
+
+    // iterate all serverinfo fields for player_%d
+    QHashIterator<QString, QString> infoIterator(serverInfo);
+    while ( infoIterator.hasNext() )
     {
-        QString insertString = "INSERT INTO playerinfo "
-                               "(sid, name, team, frags, mesh, skin, face, ping, misc, dt_player) "
-                               "VALUES (:sid, :name, :team, :frags, :mesh, :skin, :face, :ping, :misc, :dt_player)";
-        q.prepare(insertString);
-        q.bindValue(":sid",   serverID);
-        q.bindValue(":name",  serverInfo.value(QStringLiteral("player_%1").arg(playerIndex), "Player"));
-        q.bindValue(":team",  serverInfo.value(QStringLiteral(  "team_%1").arg(playerIndex), "0"));
-        q.bindValue(":frags", serverInfo.value(QStringLiteral( "frags_%1").arg(playerIndex), "0"));
-        q.bindValue(":mesh",  serverInfo.value(QStringLiteral(  "mesh_%1").arg(playerIndex), "default"));
-        q.bindValue(":skin",  serverInfo.value(QStringLiteral(  "skin_%1").arg(playerIndex), "default"));
-        q.bindValue(":face",  serverInfo.value(QStringLiteral(  "face_%1").arg(playerIndex), "default"));
-        q.bindValue(":ping",  serverInfo.value(QStringLiteral(  "ping_%1").arg(playerIndex), "0"));
-        q.bindValue(":misc",  ""); // reserved for additional query info
-        q.bindValue(":dt_player", QDateTime::currentSecsSinceEpoch() );
-        if ( ! q.exec() )
-            return reportQuery(q);
-
-        // successfull insert, increase player index
-        playerIndex++;
+        infoIterator.next();
+
+        // match player id in player_%d
+        QRegularExpressionMatch p_match = p_regex.match( infoIterator.key() );
+        if ( p_match.hasMatch()  )
+        {
+            // extract the player_id from matched string
+            QString playerIndexString = p_match.captured(0);
+            int playerIndex = playerIndexString.remove("player_").toInt();
+
+            // and insert the whole player dataset into the db (no sanity check needed, to defaults)
+            QString insertString = "INSERT INTO playerinfo "
+                                   "(sid, name, team, frags, mesh, skin, face, ping, misc, dt_player) "
+                                   "VALUES (:sid, :name, :team, :frags, :mesh, :skin, :face, :ping, :misc, :dt_player)";
+            q.prepare(insertString);
+            q.bindValue(":sid",   serverID);
+            q.bindValue(":name",  serverInfo.value(QStringLiteral("player_%1").arg(playerIndex), "Player"));
+            q.bindValue(":team",  serverInfo.value(QStringLiteral(  "team_%1").arg(playerIndex), "0"));
+            q.bindValue(":frags", serverInfo.value(QStringLiteral( "frags_%1").arg(playerIndex), "0"));
+            q.bindValue(":mesh",  serverInfo.value(QStringLiteral(  "mesh_%1").arg(playerIndex), "default"));
+            q.bindValue(":skin",  serverInfo.value(QStringLiteral(  "skin_%1").arg(playerIndex), "default"));
+            q.bindValue(":face",  serverInfo.value(QStringLiteral(  "face_%1").arg(playerIndex), "default"));
+            q.bindValue(":ping",  serverInfo.value(QStringLiteral(  "ping_%1").arg(playerIndex), "0"));
+            q.bindValue(":misc",  ""); // reserved for additional query info
+            q.bindValue(":dt_player", QDateTime::currentSecsSinceEpoch() );
+            if ( ! q.exec() )
+                return reportQuery(q);
+        } // hasMatch
     }
 
     return true;
diff --git a/src/UdpTasks/StatusChecker/statuschecker.h b/src/UdpTasks/StatusChecker/statuschecker.h
index bf3c4c2..907c8ae 100644
--- a/src/UdpTasks/StatusChecker/statuschecker.h
+++ b/src/UdpTasks/StatusChecker/statuschecker.h
@@ -4,6 +4,7 @@
 #include <QTimer>
 #include <QUdpSocket>
 #include <QNetworkDatagram>
+#include <QRegularExpression>
 
 #include "Core/CoreObject/coreobject.h"
 #include "Database/Common/commonactions.h"