6 files changed, 217 insertions, 66 deletions

diff --git a/src/server/game/DataStores/DB2Stores.cpp b/src/server/game/DataStores/DB2Stores.cpp
index 708bb0cccd1..d6560e6679a 100644
--- a/src/server/game/DataStores/DB2Stores.cpp
+++ b/src/server/game/DataStores/DB2Stores.cpp
@@ -74,24 +74,27 @@ inline void LoadDB2(uint32& availableDb2Locales, DB2StoreProblemList& errlist, D
     std::string db2_filename = db2_path + filename;
     if (storage.Load(db2_filename.c_str(), uint32(sWorld->GetDefaultDbcLocale())))
     {
-        storage.LoadSQLData();
+        storage.LoadFromDB();
 
         for (uint32 i = 0; i < TOTAL_LOCALES; ++i)
         {
-            if (!(availableDb2Locales & (1 << i)))
-                continue;
-
             if (uint32(sWorld->GetDefaultDbcLocale()) == i)
                 continue;
 
-            std::string localizedName(db2_path);
-            localizedName.append(localeNames[i]);
-            localizedName.push_back('/');
-            localizedName.append(filename);
+            if (availableDb2Locales & (1 << i))
+            {
+                std::string localizedName(db2_path);
+                localizedName.append(localeNames[i]);
+                localizedName.push_back('/');
+                localizedName.append(filename);
+
+                if (!storage.LoadStringsFrom(localizedName.c_str(), i))
+                    availableDb2Locales &= ~(1 << i);             // mark as not available for speedup next checks
+            }
 
-            if (!storage.LoadStringsFrom(localizedName.c_str(), i))
-                availableDb2Locales &= ~(1<<i);             // mark as not available for speedup next checks
+            storage.LoadStringsFromDB(i);
         }
+
     }
     else
     {
diff --git a/src/server/shared/DataStores/DB2StorageLoader.cpp b/src/server/shared/DataStores/DB2StorageLoader.cpp
index 928ee5e8e91..b83525cc0dd 100644
--- a/src/server/shared/DataStores/DB2StorageLoader.cpp
+++ b/src/server/shared/DataStores/DB2StorageLoader.cpp
@@ -419,20 +419,17 @@ char* DB2DatabaseLoader::Load(const char* format, int32 preparedStatement, uint3
     if (!result)
         return nullptr;
 
-    // we store flat holders pool as single memory block
-    size_t stringFields = DB2FileLoader::GetFormatStringFieldCount(format);
-
-    size_t expectedFields = strlen(format) + 1 /*VerifiedBuild*/;
-    if (stringFields)
-        expectedFields += 1 /*ID*/ + stringFields * (TOTAL_LOCALES - 1) + 1 /*VerifiedBuild in locale table*/;
-
-    if (expectedFields != result->GetFieldCount())
+    uint32 const fieldCount = strlen(format);
+    if (fieldCount + 1 /*VerifiedBuild*/ != result->GetFieldCount())
         return nullptr;
 
-    //get struct size and index pos
+    // get struct size and index pos
     int32 indexField;
     uint32 recordSize = DB2FileLoader::GetFormatRecordSize(format, &indexField);
 
+    // we store flat holders pool as single memory block
+    size_t stringFields = DB2FileLoader::GetFormatStringFieldCount(format);
+
     // each string field at load have array of string for each locale
     size_t stringHolderSize = sizeof(char*) * TOTAL_LOCALES;
     size_t stringHoldersRecordPoolSize = stringFields * stringHolderSize;
@@ -445,65 +442,82 @@ char* DB2DatabaseLoader::Load(const char* format, int32 preparedStatement, uint3
         // DB2 strings expected to have at least empty string
         for (size_t i = 0; i < stringHoldersPoolSize / sizeof(char*); ++i)
             ((char const**)stringHolders)[i] = nullStr;
-
     }
     else
         stringHolders = nullptr;
 
-    std::unordered_map<uint32, char*> tempIndexTable;
-    tempIndexTable.reserve(result->GetRowCount());
-    char* dataTable = new char[result->GetRowCount() * recordSize];
-    uint32 offset = 0;
+    // Resize index table
+    // database query *MUST* contain ORDER BY `index_field` DESC clause
+    uint32 indexTableSize;
+    if (indexField >= 0)
+    {
+        indexTableSize = (*result)[indexField].GetUInt32() + 1;
+        if (indexTableSize < records)
+            indexTableSize = records;
+    }
+    else
+        indexTableSize = records + result->GetRowCount();
 
-    uint32 const fieldCount = strlen(format);
-    uint32 const localeFieldsOffset = fieldCount + 2 /*VerifiedBuild in main table, ID in locale table*/;
-    uint32 oldIndexSize = records;
+    if (indexTableSize > records)
+    {
+        char** tmpIdxTable = new char*[indexTableSize];
+        memset(tmpIdxTable, 0, indexTableSize * sizeof(char*));
+        memcpy(tmpIdxTable, indexTable, records * sizeof(char*));
+        delete[] indexTable;
+        indexTable = tmpIdxTable;
+    }
+
+    char* tempDataTable = new char[result->GetRowCount() * recordSize];
+    uint32* newIndexes = new uint32[result->GetRowCount()];
     uint32 rec = 0;
+    uint32 newRecords = 0;
+
     do
     {
         Field* fields = result->Fetch();
+        uint32 offset = 0;
         uint32 stringFieldNumInRecord = 0;
 
+        uint32 indexValue;
         if (indexField >= 0)
+            indexValue = fields[indexField].GetUInt32();
+        else
+            indexValue = records + rec;
+
+        // Attempt to overwrite existing data
+        char* dataValue = indexTable[indexValue];
+        if (!dataValue)
         {
-            uint32 indexValue = fields[indexField].GetUInt32();
-            tempIndexTable[indexValue] = &dataTable[offset];
-            if (records <= indexValue)
-                records = indexValue + 1;
+            newIndexes[newRecords] = indexValue;
+            dataValue = &tempDataTable[newRecords++ * recordSize];
         }
-        else
-            tempIndexTable[records++] = &dataTable[offset];
 
         for (uint32 f = 0; f < fieldCount; f++)
         {
             switch (format[f])
             {
                 case FT_FLOAT:
-                    *((float*)(&dataTable[offset])) = fields[f].GetFloat();
+                    *((float*)(&dataValue[offset])) = fields[f].GetFloat();
                     offset += 4;
                     break;
                 case FT_IND:
                 case FT_INT:
-                    *((int32*)(&dataTable[offset])) = fields[f].GetInt32();
+                    *((int32*)(&dataValue[offset])) = fields[f].GetInt32();
                     offset += 4;
                     break;
                 case FT_BYTE:
-                    *((int8*)(&dataTable[offset])) = fields[f].GetInt8();
+                    *((int8*)(&dataValue[offset])) = fields[f].GetInt8();
                     offset += 1;
                     break;
                 case FT_STRING:
                 {
-                    LocalizedString** slot = (LocalizedString**)(&dataTable[offset]);
+                    LocalizedString** slot = (LocalizedString**)(&dataValue[offset]);
                     *slot = (LocalizedString*)(&stringHolders[stringHoldersRecordPoolSize * rec + stringHolderSize * stringFieldNumInRecord]);
 
                     // Value in database in main table field must be for enUS locale
                     if (char* str = AddLocaleString(*slot, LOCALE_enUS, fields[f].GetString()))
                         stringPool.push_back(str);
 
-                    for (uint32 locale = LOCALE_koKR; locale < TOTAL_LOCALES; ++locale)
-                        if (char* str = AddLocaleString(*slot, locale, fields[localeFieldsOffset + (locale - 1) + stringFields * stringFieldNumInRecord].GetString()))
-                            stringPool.push_back(str);
-
                     ++stringFieldNumInRecord;
                     offset += sizeof(char*);
                     break;
@@ -511,30 +525,100 @@ char* DB2DatabaseLoader::Load(const char* format, int32 preparedStatement, uint3
             }
         }
 
+        ASSERT(offset == recordSize);
         ++rec;
     } while (result->NextRow());
 
-    // Reallocate index if needed
-    if (records > oldIndexSize)
-    {
-        char** tmpIdxTable = new char*[records];
-        memset(tmpIdxTable, 0, records * sizeof(char*));
-        memcpy(tmpIdxTable, indexTable, oldIndexSize * sizeof(char*));
-        delete[] indexTable;
-        indexTable = tmpIdxTable;
-    }
+    if (!newRecords)
+        return nullptr;
+
+    // Compact new data table to only contain new records not previously loaded from file
+    char* dataTable = new char[newRecords * recordSize];
+    memcpy(dataTable, tempDataTable, newRecords * recordSize);
+
+    // insert new records to index table
+    for (uint32 i = 0; i < newRecords; ++i)
+        indexTable[newIndexes[i]] = &dataTable[i * recordSize];
 
-    // Merge new data into index
-    for (auto itr = tempIndexTable.begin(); itr != tempIndexTable.end(); ++itr)
-        indexTable[itr->first] = itr->second;
+    delete[] tempDataTable;
+    delete[] newIndexes;
+
+    records = indexTableSize;
 
     return dataTable;
 }
 
+void DB2DatabaseLoader::LoadStrings(const char* format, int32 preparedStatement, uint32 locale, char**& indexTable, std::list<char*>& stringPool)
+{
+    PreparedQueryResult result = HotfixDatabase.Query(HotfixDatabase.GetPreparedStatement(preparedStatement));
+    if (!result)
+        return;
+
+    size_t stringFields = DB2FileLoader::GetFormatStringFieldCount(format);
+    if (result->GetFieldCount() != stringFields + 1 /*ID*/)
+        return;
+
+    uint32 const fieldCount = strlen(format);
+    int32 indexField;
+    uint32 recordSize = DB2FileLoader::GetFormatRecordSize(format, &indexField);
+    ASSERT(indexField >= 0, "DB2Storage must be indexed to load localized strings");
+
+    do
+    {
+        Field* fields = result->Fetch();
+        uint32 offset = 0;
+        uint32 stringFieldNumInRecord = 0;
+        uint32 indexValue = fields[0].GetUInt32();
+
+        // Attempt to overwrite existing data
+        char* dataValue = indexTable[indexValue];
+        for (uint32 x = 0; x < fieldCount; x++)
+        {
+            switch (format[x])
+            {
+                case FT_FLOAT:
+                case FT_IND:
+                case FT_INT:
+                    offset += 4;
+                    break;
+                case FT_BYTE:
+                    offset += 1;
+                    break;
+                case FT_STRING:
+                {
+                    // fill only not filled entries
+                    LocalizedString* db2str = *(LocalizedString**)(&dataValue[offset]);
+                    if (db2str->Str[locale] == nullStr)
+                        if (char* str = AddLocaleString(db2str, locale, fields[1 + stringFieldNumInRecord].GetString()))
+                            stringPool.push_back(str);
+
+                    ++stringFieldNumInRecord;
+                    offset += sizeof(char*);
+                    break;
+                }
+            }
+        }
+
+        ASSERT(offset == recordSize);
+    } while (result->NextRow());
+
+    return;
+}
+
 char* DB2DatabaseLoader::AddLocaleString(LocalizedString* holder, uint32 locale, std::string const& value)
 {
     if (!value.empty())
     {
+        std::size_t existingLength = strlen(holder->Str[locale]);
+        if (existingLength >= value.length())
+        {
+            // Reuse existing storage if there is enough space
+            char* str = const_cast<char*>(holder->Str[locale]);
+            memcpy(str, value.c_str(), value.length());
+            str[value.length()] = '\0';
+            return nullptr;
+        }
+
         char* str = new char[value.length() + 1];
         memcpy(str, value.c_str(), value.length());
         str[value.length()] = '\0';
diff --git a/src/server/shared/DataStores/DB2StorageLoader.h b/src/server/shared/DataStores/DB2StorageLoader.h
index 0bd8c8a9403..1282dad3a9c 100644
--- a/src/server/shared/DataStores/DB2StorageLoader.h
+++ b/src/server/shared/DataStores/DB2StorageLoader.h
@@ -109,8 +109,8 @@ private:
 class DB2DatabaseLoader
 {
 public:
-
     char* Load(const char* format, int32 preparedStatement, uint32& records, char**& indexTable, char*& stringHolders, std::list<char*>& stringPool);
+    void LoadStrings(const char* format, int32 preparedStatement, uint32 locale, char**& indexTable, std::list<char*>& stringPool);
     static char* AddLocaleString(LocalizedString* holder, uint32 locale, std::string const& value);
 };
 
diff --git a/src/server/shared/DataStores/DB2Store.h b/src/server/shared/DataStores/DB2Store.h
index 9204d68754b..c4ca929065b 100644
--- a/src/server/shared/DataStores/DB2Store.h
+++ b/src/server/shared/DataStores/DB2Store.h
@@ -142,7 +142,8 @@ public:
             m_stringPoolList.push_back(stringHolders);
 
             // load strings from db2 data
-            m_stringPoolList.push_back(db2.AutoProduceStrings(fmt, (char*)m_dataTable, locale));
+            if (char* stringBlock = db2.AutoProduceStrings(fmt, (char*)m_dataTable, locale))
+                m_stringPoolList.push_back(stringBlock);
         }
 
         // error in db2 file at loading if NULL
@@ -162,24 +163,33 @@ public:
 
         // load strings from another locale db2 data
         if (DB2FileLoader::GetFormatStringFieldCount(fmt))
-            m_stringPoolList.push_back(db2.AutoProduceStrings(fmt, (char*)m_dataTable, locale));
-
+            if (char* stringBlock = db2.AutoProduceStrings(fmt, (char*)m_dataTable, locale))
+                m_stringPoolList.push_back(stringBlock);
         return true;
     }
 
-    void LoadSQLData()
+    void LoadFromDB()
     {
         if (_hotfixStatement == -1)
             return;
 
-        DB2DatabaseLoader db2;
         char* extraStringHolders = nullptr;
-        if (char* dataTable = db2.Load(fmt, _hotfixStatement, nCount, indexTable.asChar, extraStringHolders, m_stringPoolList))
-        {
+        if (char* dataTable = DB2DatabaseLoader().Load(fmt, _hotfixStatement, nCount, indexTable.asChar, extraStringHolders, m_stringPoolList))
             m_dataTableEx = reinterpret_cast<T*>(dataTable);
-            if (extraStringHolders)
-                m_stringPoolList.push_back(extraStringHolders);
-        }
+
+        if (extraStringHolders)
+            m_stringPoolList.push_back(extraStringHolders);
+    }
+
+    void LoadStringsFromDB(uint32 locale)
+    {
+        if (_hotfixStatement == -1)
+            return;
+
+        if (!DB2FileLoader::GetFormatStringFieldCount(fmt))
+            return;
+
+        DB2DatabaseLoader().LoadStrings(fmt, _hotfixStatement + locale, locale, indexTable.asChar, m_stringPoolList);
     }
 
     void Clear()
diff --git a/src/server/shared/Database/Implementation/HotfixDatabase.cpp b/src/server/shared/Database/Implementation/HotfixDatabase.cpp
index a41c037e848..55f2c3e53fb 100644
--- a/src/server/shared/Database/Implementation/HotfixDatabase.cpp
+++ b/src/server/shared/Database/Implementation/HotfixDatabase.cpp
@@ -16,12 +16,52 @@
  */
 
 #include "HotfixDatabase.h"
+#include "Util.h"
+
+/*
+    Hotfix database statements are constructed in a special way
+    Each db2 storage that contains localized string data
+    must declare a prepared statement for each locale in the same order as
+    locales are defined (enforced during compilation)
+
+    '@' character is replaced with locale index for PrepareStatement call
+*/
+
+// Force locale statments to appear exactly in locale declaration order, right after normal data fetch statement
+#define PREPARE_LOCALE_STMT(stmtBase, loc, sql, con) \
+    static_assert(stmtBase + loc == stmtBase##_##loc, "Invalid prepared statement index for " ## STRINGIZE(stmtBase##_##loc)); \
+    PrepareLocaleStatement(stmtBase##_##loc, loc, sql, con);
+
+#define PREPARE_LOCALE_STMTS(stmtBase, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_koKR, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_frFR, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_deDE, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_zhCN, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_zhTW, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_esES, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_esMX, sql, con) \
+    PREPARE_LOCALE_STMT(stmtBase, LOCALE_ruRU, sql, con)
 
 void HotfixDatabaseConnection::DoPrepareStatements()
 {
     if (!m_reconnecting)
         m_stmts.resize(MAX_HOTFIXDATABASE_STATEMENTS);
 
-    PrepareStatement(HOTFIX_SEL_BROADCAST_TEXT, "SELECT * FROM broadcast_text b LEFT JOIN locales_broadcast_text lb ON b.ID = lb.ID", CONNECTION_SYNCH);
-    PrepareStatement(HOTFIX_SEL_TAXI_PATH_NODE, "SELECT * FROM taxi_path_node", CONNECTION_SYNCH);
+    // BroadcastText.db2
+    PrepareStatement(HOTFIX_SEL_BROADCAST_TEXT, "SELECT * FROM broadcast_text ORDER BY ID DESC", CONNECTION_SYNCH);
+    PREPARE_LOCALE_STMTS(HOTFIX_SEL_BROADCAST_TEXT, "SELECT ID, MaleText_loc@, FemaleText_loc@ FROM locales_broadcast_text", CONNECTION_SYNCH);
+
+    // TaxiPathNode.db2
+    PrepareStatement(HOTFIX_SEL_TAXI_PATH_NODE, "SELECT * FROM taxi_path_node ORDER BY ID DESC", CONNECTION_SYNCH);
+}
+
+void HotfixDatabaseConnection::PrepareLocaleStatement(uint32 index, uint32 localeIndex, const char* sql, ConnectionFlags flags)
+{
+    Tokenizer tokens(sql, '@');
+    std::ostringstream stmt;
+    stmt << tokens[0];
+    for (std::size_t i = 1; i < tokens.size(); ++i)
+        stmt << localeIndex << tokens[i];
+
+    PrepareStatement(index, stmt.str().c_str(), flags);
 }
diff --git a/src/server/shared/Database/Implementation/HotfixDatabase.h b/src/server/shared/Database/Implementation/HotfixDatabase.h
index 4e434a1dad6..37234277838 100644
--- a/src/server/shared/Database/Implementation/HotfixDatabase.h
+++ b/src/server/shared/Database/Implementation/HotfixDatabase.h
@@ -30,6 +30,9 @@ class HotfixDatabaseConnection : public MySQLConnection
 
         //- Loads database type specific prepared statements
         void DoPrepareStatements() override;
+
+    private:
+        void PrepareLocaleStatement(uint32 index, uint32 localeIndex, const char* sql, ConnectionFlags flags);
 };
 
 typedef DatabaseWorkerPool<HotfixDatabaseConnection> HotfixDatabaseWorkerPool;
@@ -40,9 +43,20 @@ enum HotfixDatabaseStatements
         {DB}_{SEL/INS/UPD/DEL/REP}_{Summary of data changed}
         When updating more than one field, consider looking at the calling function
         name for a suiting suffix.
+
+        DB2 locale loading statements must have the name of base statement with locale enum value name suffix
     */
 
     HOTFIX_SEL_BROADCAST_TEXT,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_koKR,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_frFR,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_deDE,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_zhCN,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_zhTW,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_esES,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_esMX,
+    HOTFIX_SEL_BROADCAST_TEXT_LOCALE_ruRU,
+
     HOTFIX_SEL_TAXI_PATH_NODE,
     MAX_HOTFIXDATABASE_STATEMENTS
 };