feat(Core/DBLayer): replace `char const*` to `std::string_view` (#10211)

* feat(Core/DBLayer): replace `char const*` to `std::string_view`

* CString

* 1

* chore(Core/Misc): code cleanup

* cl

* db fix

* fmt style sql

* to fmt

* py

* del old

* 1

* 2

* 3

* 1

* 1

1 files changed, 82 insertions, 58 deletions

diff --git a/src/server/database/Database/MySQLConnection.cpp b/src/server/database/Database/MySQLConnection.cpp
index e594e22434..632df7a456 100644
--- a/src/server/database/Database/MySQLConnection.cpp
+++ b/src/server/database/Database/MySQLConnection.cpp
@@ -23,46 +23,46 @@
 #include "MySQLWorkaround.h"
 #include "PreparedStatement.h"
 #include "QueryResult.h"
+#include "StringConvert.h"
 #include "Timer.h"
 #include "Tokenize.h"
 #include "Transaction.h"
 #include "Util.h"
-#include "StringConvert.h"
 #include <errmsg.h>
 #include <mysqld_error.h>
 
-MySQLConnectionInfo::MySQLConnectionInfo(std::string const& infoString)
+MySQLConnectionInfo::MySQLConnectionInfo(std::string_view infoString)
 {
     std::vector<std::string_view> tokens = Acore::Tokenize(infoString, ';', true);
 
     if (tokens.size() != 5 && tokens.size() != 6)
         return;
 
-    host.assign(tokens[0]);
-    port_or_socket.assign(tokens[1]);
-    user.assign(tokens[2]);
-    password.assign(tokens[3]);
-    database.assign(tokens[4]);
+    host.assign(tokens.at(0));
+    port_or_socket.assign(tokens.at(1));
+    user.assign(tokens.at(2));
+    password.assign(tokens.at(3));
+    database.assign(tokens.at(4));
 
     if (tokens.size() == 6)
-        ssl.assign(tokens[5]);
+        ssl.assign(tokens.at(5));
 }
 
 MySQLConnection::MySQLConnection(MySQLConnectionInfo& connInfo) :
-m_reconnecting(false),
-m_prepareError(false),
-m_queue(nullptr),
-m_Mysql(nullptr),
-m_connectionInfo(connInfo),
-m_connectionFlags(CONNECTION_SYNCH) { }
+    m_reconnecting(false),
+    m_prepareError(false),
+    m_queue(nullptr),
+    m_Mysql(nullptr),
+    m_connectionInfo(connInfo),
+    m_connectionFlags(CONNECTION_SYNCH) { }
 
 MySQLConnection::MySQLConnection(ProducerConsumerQueue<SQLOperation*>* queue, MySQLConnectionInfo& connInfo) :
-m_reconnecting(false),
-m_prepareError(false),
-m_queue(queue),
-m_Mysql(nullptr),
-m_connectionInfo(connInfo),
-m_connectionFlags(CONNECTION_ASYNC)
+    m_reconnecting(false),
+    m_prepareError(false),
+    m_queue(queue),
+    m_Mysql(nullptr),
+    m_connectionInfo(connInfo),
+    m_connectionFlags(CONNECTION_ASYNC)
 {
     m_worker = std::make_unique<DatabaseWorker>(m_queue, this);
 }
@@ -76,7 +76,6 @@ void MySQLConnection::Close()
 {
     // Stop the worker thread before the statements are cleared
     m_worker.reset();
-
     m_stmts.clear();
 
     if (m_Mysql)
@@ -99,7 +98,8 @@ uint32 MySQLConnection::Open()
     char const* unix_socket;
 
     mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8");
-    #ifdef _WIN32
+
+#ifdef _WIN32
     if (m_connectionInfo.host == ".")                                           // named pipe use option (Windows)
     {
         unsigned int opt = MYSQL_PROTOCOL_PIPE;
@@ -112,7 +112,7 @@ uint32 MySQLConnection::Open()
         port = *Acore::StringTo<uint32>(m_connectionInfo.port_or_socket);
         unix_socket = 0;
     }
-    #else
+#else
     if (m_connectionInfo.host == ".")                                           // socket use option (Unix/Linux)
     {
         unsigned int opt = MYSQL_PROTOCOL_SOCKET;
@@ -126,7 +126,7 @@ uint32 MySQLConnection::Open()
         port = *Acore::StringTo<uint32>(m_connectionInfo.port_or_socket);
         unix_socket = nullptr;
     }
-    #endif
+#endif
 
     if (m_connectionInfo.ssl != "")
     {
@@ -136,6 +136,7 @@ uint32 MySQLConnection::Open()
         {
             opt_use_ssl = SSL_MODE_REQUIRED;
         }
+
         mysql_options(mysqlInit, MYSQL_OPT_SSL_MODE, (char const*)&opt_use_ssl);
 #else
         MySQLBool opt_use_ssl = MySQLBool(0);
@@ -143,6 +144,7 @@ uint32 MySQLConnection::Open()
         {
             opt_use_ssl = MySQLBool(1);
         }
+
         mysql_options(mysqlInit, MYSQL_OPT_SSL_ENFORCE, (char const*)&opt_use_ssl);
 #endif
     }
@@ -156,9 +158,6 @@ uint32 MySQLConnection::Open()
         {
             LOG_INFO("sql.sql", "MySQL client library: {}", mysql_get_client_info());
             LOG_INFO("sql.sql", "MySQL server ver: {} ", mysql_get_server_info(m_Mysql));
-            // MySQL version above 5.1 IS required in both client and server and there is no known issue with different versions above 5.1
-            // if (mysql_get_server_version(m_Mysql) != mysql_get_client_version())
-            //     LOG_INFO("sql.sql", "[WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements.");
         }
 
         LOG_INFO("sql.sql", "Connected to MySQL database at {}", m_connectionInfo.host);
@@ -166,7 +165,7 @@ uint32 MySQLConnection::Open()
 
         // set connection properties to UTF8 to properly handle locales for different
         // server configs - core sends data in UTF8, so MySQL must expect UTF8 too
-        mysql_set_character_set(m_Mysql, "utf8");
+        mysql_set_character_set(m_Mysql, "utf8mb4");
         return 0;
     }
     else
@@ -184,7 +183,7 @@ bool MySQLConnection::PrepareStatements()
     return !m_prepareError;
 }
 
-bool MySQLConnection::Execute(char const* sql)
+bool MySQLConnection::Execute(std::string_view sql)
 {
     if (!m_Mysql)
         return false;
@@ -192,7 +191,7 @@ bool MySQLConnection::Execute(char const* sql)
     {
         uint32 _s = getMSTime();
 
-        if (mysql_query(m_Mysql, sql))
+        if (mysql_query(m_Mysql, std::string(sql).c_str()))
         {
             uint32 lErrno = mysql_errno(m_Mysql);
 
@@ -219,7 +218,7 @@ bool MySQLConnection::Execute(PreparedStatementBase* stmt)
     uint32 index = stmt->GetIndex();
 
     MySQLPreparedStatement* m_mStmt = GetPreparedStatement(index);
-    ASSERT(m_mStmt);            // Can only be null if preparation failed, server side error or bad query
+    ASSERT(m_mStmt); // Can only be null if preparation failed, server side error or bad query
 
     m_mStmt->BindParameters(stmt);
 
@@ -291,8 +290,7 @@ bool MySQLConnection::_Query(PreparedStatementBase* stmt, MySQLPreparedStatement
     if (mysql_stmt_execute(msql_STMT))
     {
         uint32 lErrno = mysql_errno(m_Mysql);
-        LOG_ERROR("sql.sql", "SQL(p): {}\n [ERROR]: [{}] {}",
-            m_mStmt->getQueryString(), lErrno, mysql_stmt_error(msql_STMT));
+        LOG_ERROR("sql.sql", "SQL(p): {}\n [ERROR]: [{}] {}", m_mStmt->getQueryString(), lErrno, mysql_stmt_error(msql_STMT));
 
         if (_HandleMySQLErrno(lErrno))  // If it returns true, an error was handled successfully (i.e. reconnection)
             return _Query(stmt, mysqlStmt, pResult, pRowCount, pFieldCount);      // Try again
@@ -312,9 +310,9 @@ bool MySQLConnection::_Query(PreparedStatementBase* stmt, MySQLPreparedStatement
     return true;
 }
 
-ResultSet* MySQLConnection::Query(char const* sql)
+ResultSet* MySQLConnection::Query(std::string_view sql)
 {
-    if (!sql)
+    if (sql.empty())
         return nullptr;
 
     MySQLResult* result = nullptr;
@@ -328,7 +326,7 @@ ResultSet* MySQLConnection::Query(char const* sql)
     return new ResultSet(result, fields, rowCount, fieldCount);
 }
 
-bool MySQLConnection::_Query(const char* sql, MySQLResult** pResult, MySQLField** pFields, uint64* pRowCount, uint32* pFieldCount)
+bool MySQLConnection::_Query(std::string_view sql, MySQLResult** pResult, MySQLField** pFields, uint64* pRowCount, uint32* pFieldCount)
 {
     if (!m_Mysql)
         return false;
@@ -336,13 +334,13 @@ bool MySQLConnection::_Query(const char* sql, MySQLResult** pResult, MySQLField*
     {
         uint32 _s = getMSTime();
 
-        if (mysql_query(m_Mysql, sql))
+        if (mysql_query(m_Mysql, std::string(sql).c_str()))
         {
             uint32 lErrno = mysql_errno(m_Mysql);
             LOG_INFO("sql.sql", "SQL: {}", sql);
             LOG_ERROR("sql.sql", "[{}] {}", lErrno, mysql_error(m_Mysql));
 
-            if (_HandleMySQLErrno(lErrno))      // If it returns true, an error was handled successfully (i.e. reconnection)
+            if (_HandleMySQLErrno(lErrno)) // If it returns true, an error was handled successfully (i.e. reconnection)
                 return _Query(sql, pResult, pFields, pRowCount, pFieldCount);    // We try again
 
             return false;
@@ -355,7 +353,7 @@ bool MySQLConnection::_Query(const char* sql, MySQLResult** pResult, MySQLField*
         *pFieldCount = mysql_field_count(m_Mysql);
     }
 
-    if (!*pResult )
+    if (!*pResult)
         return false;
 
     if (!*pRowCount)
@@ -392,18 +390,29 @@ int MySQLConnection::ExecuteTransaction(std::shared_ptr<TransactionBase> transac
 
     BeginTransaction();
 
-    for (auto itr = queries.begin(); itr != queries.end(); ++itr)
+    for (auto const& data : queries)
     {
-        SQLElementData const& data = *itr;
-        switch (itr->type)
+        switch (data.type)
         {
             case SQL_ELEMENT_PREPARED:
             {
-                PreparedStatementBase* stmt = data.element.stmt;
+                PreparedStatementBase* stmt = nullptr;
+
+                try
+                {
+                    stmt = std::get<PreparedStatementBase*>(data.element);
+                }
+                catch (const std::bad_variant_access& ex)
+                {
+                    LOG_FATAL("sql.sql", "> PreparedStatementBase not found in SQLElementData. {}", ex.what());
+                    ABORT();
+                }
+
                 ASSERT(stmt);
+
                 if (!Execute(stmt))
                 {
-                    LOG_WARN("sql.sql", "Transaction aborted. {} queries not executed.", (uint32)queries.size());
+                    LOG_WARN("sql.sql", "Transaction aborted. {} queries not executed.", queries.size());
                     int errorCode = GetLastError();
                     RollbackTransaction();
                     return errorCode;
@@ -412,12 +421,24 @@ int MySQLConnection::ExecuteTransaction(std::shared_ptr<TransactionBase> transac
             break;
             case SQL_ELEMENT_RAW:
             {
-                char const* sql = data.element.query;
-                ASSERT(sql);
+                std::string sql{};
+
+                try
+                {
+                    sql = std::get<std::string>(data.element);
+                }
+                catch (const std::bad_variant_access& ex)
+                {
+                    LOG_FATAL("sql.sql", "> std::string not found in SQLElementData. {}", ex.what());
+                    ABORT();
+                }
+
+                ASSERT(!sql.empty());
+
                 if (!Execute(sql))
                 {
-                    LOG_WARN("sql.sql", "Transaction aborted. {} queries not executed.", (uint32)queries.size());
-                    int errorCode = GetLastError();
+                    LOG_WARN("sql.sql", "Transaction aborted. {} queries not executed.", queries.size());
+                    uint32 errorCode = GetLastError();
                     RollbackTransaction();
                     return errorCode;
                 }
@@ -469,7 +490,9 @@ MySQLPreparedStatement* MySQLConnection::GetPreparedStatement(uint32 index)
 {
     ASSERT(index < m_stmts.size(), "Tried to access invalid prepared statement index {} (max index {}) on database `{}`, connection type: {}",
         index, m_stmts.size(), m_connectionInfo.database, (m_connectionFlags & CONNECTION_ASYNC) ? "asynchronous" : "synchronous");
+
     MySQLPreparedStatement* ret = m_stmts[index].get();
+
     if (!ret)
         LOG_ERROR("sql.sql", "Could not fetch prepared statement {} on database `{}`, connection type: {}.",
             index, m_connectionInfo.database, (m_connectionFlags & CONNECTION_ASYNC) ? "asynchronous" : "synchronous");
@@ -477,7 +500,7 @@ MySQLPreparedStatement* MySQLConnection::GetPreparedStatement(uint32 index)
     return ret;
 }
 
-void MySQLConnection::PrepareStatement(uint32 index, std::string const& sql, ConnectionFlags flags)
+void MySQLConnection::PrepareStatement(uint32 index, std::string_view sql, ConnectionFlags flags)
 {
     // Check if specified query should be prepared on this connection
     // i.e. don't prepare async statements on synchronous connections
@@ -497,7 +520,7 @@ void MySQLConnection::PrepareStatement(uint32 index, std::string const& sql, Con
     }
     else
     {
-        if (mysql_stmt_prepare(stmt, sql.c_str(), static_cast<unsigned long>(sql.size())))
+        if (mysql_stmt_prepare(stmt, std::string(sql).c_str(), static_cast<unsigned long>(sql.size())))
         {
             LOG_ERROR("sql.sql", "In mysql_stmt_prepare() id: {}, sql: \"{}\"", index, sql);
             LOG_ERROR("sql.sql", "{}", mysql_stmt_error(stmt));
@@ -523,6 +546,7 @@ PreparedResultSet* MySQLConnection::Query(PreparedStatementBase* stmt)
     {
         mysql_next_result(m_Mysql);
     }
+
     return new PreparedResultSet(mysqlStmt->GetSTMT(), result, rowCount, fieldCount);
 }
 
@@ -556,7 +580,7 @@ bool MySQLConnection::_HandleMySQLErrno(uint32 errNo, uint8 attempts /*= 5*/)
                 if (!this->PrepareStatements())
                 {
                     LOG_FATAL("sql.sql", "Could not re-prepare statements!");
-                    std::this_thread::sleep_for(std::chrono::seconds(10));
+                    std::this_thread::sleep_for(10s);
                     std::abort();
                 }
 
@@ -572,24 +596,24 @@ bool MySQLConnection::_HandleMySQLErrno(uint32 errNo, uint8 attempts /*= 5*/)
             {
                 // Shut down the server when the mysql server isn't
                 // reachable for some time
-                LOG_FATAL("sql.sql", "Failed to reconnect to the MySQL server, "
-                             "terminating the server to prevent data corruption!");
+                LOG_FATAL("sql.sql", "Failed to reconnect to the MySQL server, terminating the server to prevent data corruption!");
 
                 // We could also initiate a shutdown through using std::raise(SIGTERM)
-                std::this_thread::sleep_for(std::chrono::seconds(10));
+                std::this_thread::sleep_for(10s);
                 std::abort();
             }
             else
             {
                 // It's possible this attempted reconnect throws 2006 at us.
                 // To prevent crazy recursive calls, sleep here.
-                std::this_thread::sleep_for(std::chrono::seconds(3)); // Sleep 3 seconds
+                std::this_thread::sleep_for(3s); // Sleep 3 seconds
                 return _HandleMySQLErrno(lErrno, attempts); // Call self (recursive)
             }
         }
 
         case ER_LOCK_DEADLOCK:
-            return false;    // Implemented in TransactionTask::Execute and DatabaseWorkerPool<T>::DirectCommitTransaction
+            return false; // Implemented in TransactionTask::Execute and DatabaseWorkerPool<T>::DirectCommitTransaction
+
         // Query related errors - skip query
         case ER_WRONG_VALUE_COUNT:
         case ER_DUP_ENTRY:
@@ -599,12 +623,12 @@ bool MySQLConnection::_HandleMySQLErrno(uint32 errNo, uint8 attempts /*= 5*/)
         case ER_BAD_FIELD_ERROR:
         case ER_NO_SUCH_TABLE:
             LOG_ERROR("sql.sql", "Your database structure is not up to date. Please make sure you've executed all queries in the sql/updates folders.");
-            std::this_thread::sleep_for(std::chrono::seconds(10));
+            std::this_thread::sleep_for(10s);
             std::abort();
             return false;
         case ER_PARSE_ERROR:
             LOG_ERROR("sql.sql", "Error while parsing SQL. Core fix required.");
-            std::this_thread::sleep_for(std::chrono::seconds(10));
+            std::this_thread::sleep_for(10s);
             std::abort();
             return false;
         default: