Jump to content

[Patch/Dev] /guildinfo command


Guest astriconX

Recommended Posts

[Patch/Dev] /guildinfo command

What bug does the patch fix? What features does the patch add?

- fix /guildinfo command: Without this patch create date is 0 0 0 and it shows the number of players in guild double.

without:

Guild: <guild name>

Guild created: 0 0 0, x player, x accounts

with patch (example):

Guild: <guild name>

Guild created: 31 12 2009, x player, y accounts

- Changes:

* Correctly load guild create date from database

* Implement number of total accounts in guild

For which repository revision was the patch created?

r[8383]

New version for r8402 with fixes see Post #3.

Who has been writing this patch? Please include either forum user names or email addresses.

Me.

Tested on/with:

- Windows 7 x64 (build as Win32)

- mySQL

ToDo

- SQL syntax ok for other DBMS (PGSQL) ?

- cleanup ?

- fix an issue with number of accounts:

Normally I update the number only while loading the guilds (startup), and if a member joins/leaves the guild, but for some reason I get unexpected results. For example we have 2 people. Character cA wih account aA and character cB with account aB.

cA is the leader of our guild and type /guildinfo - he gets 0 accounts.

He invites cB and use /guildinfo again - he gets 1.

Now cB leave the guild and cA type /guildinfo - he gets 2 accounts.

He invites cB a 2nd time and /guildinfo says 1.

cB leaves the 2nd time and /guildinfo say 2 accounts.

Then it becomes and endless game... 1 account, 2 accouns, 1... but not 2 while both characters are in the guild - no, everytime inversely.

But I placed the call of UpdateTotalAccounts() after adding/deleting the guild_member data in the character DB, so I really do not unterstand what is going on...

I used some more debug outputs as in the patch below, but I still don't know why the above happens.

If I use the query directly on the terminal/cmd/mySQL-client I get correct results, but ingame it won't work.

To get this working a bit better I placed UpdateTotalAccounts() in WorldSession::HandleGuildInfoOpcode() and it seems to output correct data, but it's "dirty" I think.

Anyone who exactly knows, what is the reason?

Because this fact I post it here in core mods, not in patch submission. Note that the date part works correct for me.

diff --git a/src/game/Guild.cpp b/src/game/Guild.cpp
index ccfe5b9..27d1435 100644
--- a/src/game/Guild.cpp
+++ b/src/game/Guild.cpp
@@ -136,6 +136,8 @@ bool Guild::AddMember(uint64 plGuid, uint32 plRank)
    CharacterDatabase.PExecute("INSERT INTO guild_member (guildid,guid,rank,pnote,offnote) VALUES
('%u', '%u', '%u','%s','%s')",
        Id, GUID_LOPART(plGuid), newmember.RankId, dbPnote.c_str(), dbOFFnote.c_str());

+    UpdateTotalAccounts();
+
    // If player not in game data in data field will be loaded from guild tables, no need to update it!!
    if(pl)
    {
@@ -172,6 +174,8 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)
    if(!LoadMembersFromDB(GuildId))
        return false;

+    UpdateTotalAccounts();
+
    QueryResult *result = CharacterDatabase.PQuery("SELECT MAX(TabId) FROM guild_bank_tab WHERE guildid='%u'", GuildId);
    if(result)
    {
@@ -186,8 +190,8 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)

    //                                        0        1     2           3            4            5           6
    result = CharacterDatabase.PQuery("SELECT guildid, name, leaderguid, EmblemStyle, EmblemColor, BorderStyle, BorderColor,"
-    //   7                8     9     10          11
-        "BackgroundColor, info, motd, createdate, BankMoney FROM guild WHERE guildid = '%u'", GuildId);
+    //   7                8     9     10                          11
+        "BackgroundColor, info, motd, UNIX_TIMESTAMP(createdate), BankMoney FROM guild WHERE guildid = '%u'", GuildId);

    if(!result)
        return false;
@@ -205,15 +209,15 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)
    BackgroundColor = fields[7].GetUInt32();
    GINFO = fields[8].GetCppString();
    MOTD = fields[9].GetCppString();
-    uint64 time = fields[10].GetUInt64();                   //datetime is uint64 type ... YYYYmmdd:hh:mm:ss
+    time_t time = time_t(fields[10].GetUInt64());      //datetime is uint64 type ... YYYY-mm-dd hh:mm:ss
    guildbank_money = fields[11].GetUInt64();
-
    delete result;

-    uint64 dTime = time /1000000;
-    CreatedDay   = dTime%100;
-    CreatedMonth = (dTime/100)%100;
-    CreatedYear  = (dTime/10000)%10000;
+    tm *guild_creationtime = localtime(&time);
+    CreatedDay   = guild_creationtime->tm_mday;
+    CreatedMonth = guild_creationtime->tm_mon+1;
+    CreatedYear  = guild_creationtime->tm_year+1900;
+    // sLog.outDebug("Guild %u creation time loaded: day: %u, month: %u, year: %u", GuildId, CreatedDay, CreatedMonth, CreatedYear);

    // If the leader does not exist attempt to promote another member
    if(!objmgr.GetPlayerAccountIdByGUID(leaderGuid ))
@@ -225,7 +229,6 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)
            return false;
    }

-    sLog.outDebug("Guild %u Creation time Loaded day: %u, month: %u, year: %u", GuildId, CreatedDay, CreatedMonth, CreatedYear);
    m_bankloaded = false;
    m_eventlogloaded = false;
    m_onlinemembers = 0;
@@ -480,6 +483,8 @@ void Guild::DelMember(uint64 guid, bool isDisbanding)
    }

    CharacterDatabase.PExecute("DELETE FROM guild_member WHERE guid = '%u'", GUID_LOPART(guid));
+
+    UpdateTotalAccounts();
}

void Guild::ChangeRank(uint64 guid, uint32 newRank)
@@ -791,6 +796,20 @@ void Guild::UpdateLogoutTime(uint64 guid)
    }
}

+void Guild::UpdateTotalAccounts()
+{
+    QueryResult *result = CharacterDatabase.PQuery("SELECT COUNT(DISTINCT(characters.account)) "
+        "FROM guild_member LEFT JOIN characters ON characters.guid = guild_member.guid WHERE guildid = '%u'", GetId());
+
+    if(result)
+    {
+        TotalAccounts = (*result)[0].GetUInt32();
+        delete result;
+    }
+    else
+        sLog.outError("Updated total accounts for guild %u but no guild member has an account.", GetId());
+}
+
// *************************************************
// Guild Eventlog part
// *************************************************
diff --git a/src/game/Guild.h b/src/game/Guild.h
index 9794668..8b61e77 100644
--- a/src/game/Guild.h
+++ b/src/game/Guild.h
@@ -318,6 +318,8 @@ class Guild
        void SetEmblem(uint32 emblemStyle, uint32 emblemColor, uint32 borderStyle, uint32 borderColor, uint32 backgroundColor);

        uint32 GetMemberSize() const { return members.size(); }
+        uint32 GetTotalAccounts() const { return TotalAccounts; }
+        void UpdateTotalAccounts();

        bool LoadGuildFromDB(uint32 GuildId);
        bool LoadRanksFromDB(uint32 GuildId);
@@ -450,6 +452,8 @@ class Guild
        uint32 BorderColor;
        uint32 BackgroundColor;

+        uint32 TotalAccounts;
+
        RankList m_ranks;

        MemberList members;
diff --git a/src/game/GuildHandler.cpp b/src/game/GuildHandler.cpp
index b950a80..c8a34b9 100644
--- a/src/game/GuildHandler.cpp
+++ b/src/game/GuildHandler.cpp
@@ -237,13 +237,16 @@ void WorldSession::HandleGuildInfoOpcode(WorldPacket& /*recvPacket*/)
        return;
    }

+    // We need to update it here again, cause otherwise we get false data
+    guild->UpdateTotalAccounts();
+
    WorldPacket data(SMSG_GUILD_INFO, (5*4 + guild->GetName().size() + 1));
    data << guild->GetName();
    data << guild->GetCreatedDay();
    data << guild->GetCreatedMonth();
    data << guild->GetCreatedYear();
    data << guild->GetMemberSize();
-    data << guild->GetMemberSize();
+    data << guild->GetTotalAccounts();

    SendPacket(&data);
}

Link to comment
Share on other sites

Thank you for testing.

I found out that there is some problem with

CharacterDatabase.PExecute("INSERT/DELETE FROM guild_member ...", ...)

and

CharacterDatabase.PQuery("SELECT COUNT(DISTINCT(characters.account)) FROM guild_member LEFT JOIN characters ON characters.guid = guild_member.guid WHERE guildid ...", ...)

I think the 2nd query takes old values and not the new with the INSERT / DELETE before.

The problem seems to be somewhere between them...

If I call the 2nd query a few times more (I do this with a for loop around 100 times on my enviroment) and output everytime the result of the 2nd query you will see that the number changes from old to new correct value.

Changes:

* Fix auto merge errors.

* Correctly load member count at startup

* Adding a few comments (@Guild::UpdateTotalAccounts(); they describe the problem noted above)

No real functionally changed.

New version for r[8402]:

diff --git a/src/game/Guild.cpp b/src/game/Guild.cpp
index dd78d9c..211ab8c 100644
--- a/src/game/Guild.cpp
+++ b/src/game/Guild.cpp
@@ -148,6 +148,8 @@ bool Guild::AddMember(uint64 plGuid, uint32 plRank)
    CharacterDatabase.PExecute("INSERT INTO guild_member (guildid,guid,rank,pnote,offnote) VALUES
('%u', '%u', '%u','%s','%s')",
        m_Id, GUID_LOPART(plGuid), newmember.RankId, dbPnote.c_str(), dbOFFnote.c_str());

+    UpdateTotalAccounts();
+
    // If player not in game data in data field will be loaded from guild tables, no need to update it!!
    if(pl)
    {
@@ -198,8 +200,8 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)

    //                                        0        1     2           3            4            5           6
    result = CharacterDatabase.PQuery("SELECT guildid, name, leaderguid, EmblemStyle, EmblemColor, BorderStyle, BorderColor,"
-    //   7                8     9     10          11
-        "BackgroundColor, info, motd, createdate, BankMoney FROM guild WHERE guildid = '%u'", GuildId);
+    //   7                8     9     10                          11
+        "BackgroundColor, info, motd, UNIX_TIMESTAMP(createdate), BankMoney FROM guild WHERE guildid = '%u'", GuildId);

    if(!result)
        return false;
@@ -210,22 +212,23 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)
    m_Name = fields[1].GetCppString();
    m_LeaderGuid  = MAKE_NEW_GUID(fields[2].GetUInt32(), 0, HIGHGUID_PLAYER);

-    m_EmblemStyle = fields[3].GetUInt32();
-    m_EmblemColor = fields[4].GetUInt32();
-    m_BorderStyle = fields[5].GetUInt32();
-    m_BorderColor = fields[6].GetUInt32();
+    m_EmblemStyle     = fields[3].GetUInt32();
+    m_EmblemColor     = fields[4].GetUInt32();
+    m_BorderStyle     = fields[5].GetUInt32();
+    m_BorderColor     = fields[6].GetUInt32();
    m_BackgroundColor = fields[7].GetUInt32();
-    GINFO = fields[8].GetCppString();
-    MOTD = fields[9].GetCppString();
-    uint64 time = fields[10].GetUInt64();                   //datetime is uint64 type ... YYYYmmdd:hh:mm:ss
-    m_GuildBankMoney = fields[11].GetUInt64();
+    GINFO             = fields[8].GetCppString();
+    MOTD              = fields[9].GetCppString();
+    time_t time       = time_t(fields[10].GetUInt64());      //datetime is uint64 type ... YYYY-mm-dd hh:mm:ss
+    m_GuildBankMoney  = fields[11].GetUInt64();

    delete result;

-    uint64 dTime = time /1000000;
-    m_CreatedDay   = dTime%100;
-    m_CreatedMonth = (dTime/100)%100;
-    m_CreatedYear  = (dTime/10000)%10000;
+    tm *guild_creationtime = localtime(&time);
+    m_CreatedDay   = guild_creationtime->tm_mday;
+    m_CreatedMonth = guild_creationtime->tm_mon+1;
+    m_CreatedYear  = guild_creationtime->tm_year+1900;
+    // sLog.outDebug("Guild %u creation time loaded: day: %u, month: %u, year: %u", GuildId, CreatedDay, CreatedMonth, CreatedYear);

    // If the leader does not exist attempt to promote another member
    if(!objmgr.GetPlayerAccountIdByGUID(m_LeaderGuid ))
@@ -237,7 +240,7 @@ bool Guild::LoadGuildFromDB(uint32 GuildId)
            return false;
    }

-    sLog.outDebug("Guild %u Creation time Loaded day: %u, month: %u, year: %u", GuildId, m_CreatedDay, m_CreatedMonth, m_CreatedYear);
+    UpdateTotalAccounts();

    return true;
}
@@ -488,6 +491,8 @@ void Guild::DelMember(uint64 guid, bool isDisbanding)
    }

    CharacterDatabase.PExecute("DELETE FROM guild_member WHERE guid = '%u'", GUID_LOPART(guid));
+
+    UpdateTotalAccounts();
}

void Guild::ChangeRank(uint64 guid, uint32 newRank)
@@ -799,6 +804,48 @@ void Guild::UpdateLogoutTime(uint64 guid)
    }
}

+void Guild::UpdateTotalAccounts()
+{
+    /*
+    FIX ME: after insert / delete data from guild_member we call this function instantly, but get false data 
+    Problem: the query below selects "old" data from DB which create unexpected results
+        If we call this function in Guild::AddMember() / Guild::DelMember() within a for loop with about 100 iterations (depends on hardware)
+        and output everytime the actual m_TotalAccounts we see that after a few iterations it jumps to correct value.
+
+    possible solutions:
+
+    (1) Implement Guild::Update and use something like this:
+    Guild::AddMember() / Guild::DelMember()
+    {
+        [...]
+        m_UpdateTotalAccounts = true;
+    }
+
+    Guild::Update()
+    {
+        if (m_UpdateTotalAccounts)
+            UpdateTotalAccounts();
+    }
+
+    (2) Fix the problem with COUNT/DISTINCT in this case
+
+    (3) Try to rebuild the query below within the core
+
+    (4) (Not good?!) Only update this if we really need the actual count of accounts in guild and hope we get correct results...
+    */
+
+    QueryResult *result = CharacterDatabase.PQuery("SELECT COUNT(DISTINCT(characters.account)) "
+        "FROM guild_member LEFT JOIN characters ON characters.guid = guild_member.guid WHERE guildid = '%u'", GetId());
+
+    if (result)
+    {
+        m_TotalAccounts = (*result)[0].GetUInt32();
+        delete result;
+    }
+    else
+        sLog.outError("Update total accounts for guild %u but no guild member has an account.", GetId());
+}
+
// *************************************************
// Guild Eventlog part
// *************************************************
diff --git a/src/game/Guild.h b/src/game/Guild.h
index 060f9f0..a0eeb3d 100644
--- a/src/game/Guild.h
+++ b/src/game/Guild.h
@@ -315,6 +315,8 @@ class Guild
        void SetEmblem(uint32 emblemStyle, uint32 emblemColor, uint32 borderStyle, uint32 borderColor, uint32 backgroundColor);

        uint32 GetMemberSize() const { return members.size(); }
+        uint32 GetTotalAccounts() const { return m_TotalAccounts; }
+        void UpdateTotalAccounts();

        bool LoadGuildFromDB(uint32 GuildId);
        bool LoadRanksFromDB(uint32 GuildId);
@@ -445,6 +447,8 @@ class Guild
        uint32 m_BorderColor;
        uint32 m_BackgroundColor;

+        uint32 m_TotalAccounts;
+
        RankList m_Ranks;

        MemberList members;
diff --git a/src/game/GuildHandler.cpp b/src/game/GuildHandler.cpp
index 794ee4c..7d24e19 100644
--- a/src/game/GuildHandler.cpp
+++ b/src/game/GuildHandler.cpp
@@ -237,13 +237,16 @@ void WorldSession::HandleGuildInfoOpcode(WorldPacket& /*recvPacket*/)
        return;
    }

+    // We need to update it here again, cause otherwise we get false data; see Guild::UpdateTotalAccounts()
+    guild->UpdateTotalAccounts();
+
    WorldPacket data(SMSG_GUILD_INFO, (5*4 + guild->GetName().size() + 1));
    data << guild->GetName();
    data << guild->GetCreatedDay();
    data << guild->GetCreatedMonth();
    data << guild->GetCreatedYear();
    data << guild->GetMemberSize();
-    data << guild->GetMemberSize();
+    data << guild->GetTotalAccounts();

    SendPacket(&data);
}

Link to comment
Share on other sites

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue. Privacy Policy Terms of Use