[games/chessament] src: Improve performance of deleting rating list

Git commit a2527ccfe0c54fdd56e9132fc19a1979df98fa42 by Manuel Alcaraz Zambrano.
Committed on 02/08/2026 at 21:25.
Pushed by manuelal into branch 'master'.

Improve performance of deleting rating list

M  +4    -4    src/ratinglistmodel.cpp
M  +32   -3    src/tournament/ratinglists/ratinglist.cpp
M  +6    -0    src/tournament/ratinglists/ratinglist.h

https://invent.kde.org/games/chessament/-/commit/a2527ccfe0c54fdd56e9132fc19a1979df98fa42

diff --git a/src/ratinglistmodel.cpp b/src/ratinglistmodel.cpp
index 1367ab4..7e31510 100644
--- a/src/ratinglistmodel.cpp
+++ b/src/ratinglistmodel.cpp
@@ -93,13 +93,13 @@ QCoro::Task<> RatingListModel::remove(int row)
     const auto list = m_lists.at(row).get();
     const auto id = list->id();
 
-    beginRemoveRows({}, row, row);
-    m_lists.erase(m_lists.begin() + row);
-    endRemoveRows();
-
     co_await QtConcurrent::run([id]() {
         RatingList::remove(id);
     });
+
+    beginRemoveRows({}, row, row);
+    m_lists.erase(m_lists.begin() + row);
+    endRemoveRows();
 }
 
 bool RatingListModel::isValidUrl(const QString &location)
diff --git a/src/tournament/ratinglists/ratinglist.cpp b/src/tournament/ratinglists/ratinglist.cpp
index c2627cb..82945d5 100644
--- a/src/tournament/ratinglists/ratinglist.cpp
+++ b/src/tournament/ratinglists/ratinglist.cpp
@@ -65,9 +65,9 @@ std::expected<QSqlDatabase, QString> RatingList::getDb(const QString &connection
     }
 
     QSqlQuery query(db);
-    query.prepare(ENABLE_FOREIGN_KEYS_QUERY);
+    query.prepare(ENABLE_WAL_QUERY);
     if (!query.exec()) {
-        qWarning() << "enable foreign keys" << query.lastError().text();
+        qWarning() << "enable wal" << query.lastError().text();
         return std::unexpected(query.lastError().text());
     }
 
@@ -95,6 +95,14 @@ std::expected<QSqlDatabase, QString> RatingList::getDb(const QString &connection
         return std::unexpected(query.lastError().text());
     }
 
+    query = QSqlQuery(db);
+    query.prepare(RATING_LIST_PLAYERS_LIST_INDEX);
+
+    if (!query.exec()) {
+        qWarning() << "Error creating ratings database idx_player_list" << query.lastError().text();
+        return std::unexpected(query.lastError().text());
+    }
+
     query = QSqlQuery(db);
     query.prepare(RATING_LIST_PLAYERS_NATIONAL_ID_INDEX);
 
@@ -343,18 +351,38 @@ std::expected<uint, QString> RatingList::readPlayers(QTextStream *stream, std::u
 
 void RatingList::remove(int id)
 {
+    qDebug() << "Starting to remove rating list" << id;
     {
         auto db = RatingList::getDb(RATING_LISTS_DB_CONNECTION_NAME_WRITER);
         if (!db) {
             return;
         }
 
+        if (const auto ok = db->transaction(); !ok) {
+            qWarning() << "Error starting transaction" << db->lastError();
+            return;
+        }
+
         QSqlQuery query(*db);
+        query.prepare(DELETE_RATING_LIST_PLAYERS_QUERY);
+        query.bindValue(":list"_L1, id);
+
+        if (!query.exec()) {
+            qWarning() << "Error deleting players from rating list" << query.lastError().text();
+            return;
+        }
+
+        query = QSqlQuery(*db);
         query.prepare(DELETE_RATING_LIST_QUERY);
         query.bindValue(":id"_L1, id);
 
         if (!query.exec()) {
-            qWarning() << "error deleting rating list" << query.lastError().text();
+            qWarning() << "Error deleting rating list" << query.lastError().text();
+            return;
+        }
+
+        if (!db->commit()) {
+            qWarning() << "Error commiting transaction" << db->lastError();
             return;
         }
 
@@ -362,6 +390,7 @@ void RatingList::remove(int id)
     }
 
     QSqlDatabase::removeDatabase(RATING_LISTS_DB_CONNECTION_NAME_WRITER);
+    qDebug() << "Finished removing rating list" << id;
 }
 
 std::expected<QList<RatingListPlayer>, QString> RatingList::searchPlayers(const QString &text)
diff --git a/src/tournament/ratinglists/ratinglist.h b/src/tournament/ratinglists/ratinglist.h
index 1fef506..1fc9600 100644
--- a/src/tournament/ratinglists/ratinglist.h
+++ b/src/tournament/ratinglists/ratinglist.h
@@ -19,6 +19,8 @@ class RatingListReader;
 
 using namespace Qt::StringLiterals;
 
+static const QString ENABLE_WAL_QUERY = u"PRAGMA journal_mode = WAL;"_s;
+
 constexpr auto RATING_LISTS_TABLE_SCHEMA =
     "CREATE TABLE IF NOT EXISTS ratinglists("
     "id INTEGER PRIMARY KEY,"
@@ -59,10 +61,14 @@ const QString RATING_LIST_PLAYERS_ID_INDEX = u"CREATE INDEX IF NOT EXISTS idx_pl
 
 const QString RATING_LIST_PLAYERS_NATIONAL_ID_INDEX = u"CREATE INDEX IF NOT EXISTS idx_national_id ON players(nationalId);"_s;
 
+const QString RATING_LIST_PLAYERS_LIST_INDEX = u"CREATE INDEX IF NOT EXISTS idx_player_list ON players(list);"_s;
+
 constexpr auto ADD_RATING_LIST_PLAYER_QUERY =
     "INSERT INTO players(list, name, playerId, federation, gender, title, birthday, standard, rapid, blitz, nationalId, nationalRating, extra) "
     "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, jsonb(?));"_L1;
 
+constexpr auto DELETE_RATING_LIST_PLAYERS_QUERY = "DELETE FROM players WHERE list = :list;"_L1;
+
 static const auto SEARCH_PLAYERS_QUERY =
     u"SELECT playerId, name, federation, gender, title, birthday, standard, rapid, blitz, nationalId, nationalRating, json(extra) as extra FROM players WHERE name LIKE :search LIMIT 20;"_s;