Friendlist

[hoegarden31]

Hello, it's me again. This is a bug for experts. (the fix too)
So what's the bug ? Well as you all know, when you send a friend invitation, and the other player accept, there will be 2 rows created in the database. Now, there is the problem, the table list_friend as no primary keys. So what happens when you delete a friend from your friendlist ? Only 1 record is deleted. Then if you invite the same player again, there will be 2 records (rows) for him.
Pretty weird isn't it ? I discovered this when i wanted to make a limitation on the friendlist.
It count the number of friends the player already have, and if it's at the max, he can't add anymore friends (or receive invitations). But with those duplicated rows, he can have the max with only 25 real friends.

I'm not really good at sql, so i fixed this in 2 times. First we do like the code do, delete the row with ID = id of the player and friendid = id of the friend.
Then in the second time i delete the row with id = id of the friend and friendid = id of the player.
So that both rows are deleted.
I do it now like this : You can find those codes in CCharServer::pakMessengerManager in the community.cpp of the charserver

Line number On/Off | Expand/Contract | Select all

1. if(!DB->QExecute("DELETE FROM list_friend WHERE id=%i and idfriend=%i",thisclient->charid,id))
2.             {
3.  
4.                 Log(MSG_INFO,"user failed to delete friend slot %i",thisclient->charname,id);
5.                 return false;
6.             }
7.             if(!DB->QExecute("DELETE FROM list_friend WHERE id=%i and idfriend=%i",id,thisclient->charid))
8.             {
9.                 Log(MSG_INFO,"user failed to delete friend slot %i",id,thisclient->charname);
10.                 return false;
11.             }

If someone knows how to do it in one single query, i would be grateful.
I also made the field id and friendid primary key. To prevent duplication of the rows.

Ok because it's a little chaotic with all the little parts, here is the code, and in color you will see what i added.

Line number On/Off | Expand/Contract | Select all

1. // Messenger actions (add/remove/invite)
2. bool CCharServer::pakMessengerManager ( CCharClient* thisclient, CPacket* P )
3. {
4.     BYTE action = GETBYTE((*P),0);
5.     switch (action)
6.     {
7.         case 0x01://wanna be my friend?
8.         {
9.             char* nick = new (nothrow) char[P->Size-7];
10.             if(nick==NULL)
11.             {
12.                 Log(MSG_ERROR, "Error allocing memory: pakMessengerManager 1" );
13.                 return false;
14.             }
15.             int row;
16.             MYSQL_RES *result = NULL;
17.             result = DB->QStore("SELECT * FROM list_friend WHERE id=%i",thisclient->charid);
18.              row = mysql_num_rows(result);
19.              Log(MSG_INFO,"%s has %i friend(s)",thisclient->charname,row);
20.             memcpy( nick, &P->Buffer[1], P->Size-7 );
21.             Log(MSG_INFO,"%s Trying to invite %s",nick,thisclient->charname);
22.  
23.             CCharClient* otherclient = (CCharClient*) GetClientByName (nick);
24.             if(otherclient!=NULL)
25.             {//Send friend invitation  (check this one)
26.                  if(row==100){
27.                      BEGINPACKET( pak, 0x0784 );
28.                      ADDSTRING( pak, "Server" );
29.                      ADDBYTE( pak, 0 );
30.                      ADDSTRING( pak, "Your friendlist is full. Delete friends before adding new ones." );
31.                      ADDBYTE( pak, 0 );
32.                      thisclient->SendPacket(&pak);
33.                      DB->QFree( );
34.                      return false;
35.                  }
36.                 int row2;
37.                 MYSQL_RES *result2 = NULL;
38.                 result2 = DB->QStore("SELECT * FROM list_friend WHERE id=%i",otherclient->charid);
39.                 row2 = mysql_num_rows(result2);
40.                 Log(MSG_INFO,"%s has %i friend(s)",otherclient->charname,row2);
41.                 if(row2==100)
42.                 {
43.                      BEGINPACKET( pak, 0x0784 );
44.                      ADDSTRING( pak, "Server" );
45.                       ADDBYTE( pak, 0 );
46.                       ADDSTRING( pak, "Your friendlist is full. Delete friends before adding new ones." );
47.                      ADDBYTE( pak, 0 );
48.                     otherclient->SendPacket(&pak);
49.  
50.                     RESETPACKET( pak, 0x0784 );
51.                     ADDSTRING( pak, "Server" );
52.                     ADDBYTE( pak, 0 );
53.                     ADDSTRING( pak, "The list of your friend is full. Invitation canceled" );
54.                     ADDBYTE( pak, 0 );
55.                     thisclient->SendPacket(&pak);
56.                     DB->QFree( );
57.                     return false;
58.                 }
59.  
60.                 BEGINPACKET( pak, 0x7e1 );
61.                 ADDBYTE    ( pak, 0x01 );
62.                 ADDWORD    ( pak, 0x0000 );
63.                 ADDSTRING  ( pak, thisclient->charname );
64.                 ADDBYTE    ( pak, 0x00 );
65.                 otherclient->SendPacket(&pak);
66.                 Log(MSG_INFO,"%s exists, invite sent to %s",nick,otherclient->charname);
67.             }
68.             else
69.             {//This charname doesnt exist
70.                BEGINPACKET( pak, 0x7e1 );
71.                ADDBYTE    ( pak, 0x04 );
72.                ADDSTRING  ( pak, nick );
73.                ADDBYTE    ( pak, 0x00 );
74.                thisclient->SendPacket(&pak);
75.                Log(MSG_INFO,"invite: %s doesn't exist",nick);
76.             }
77.             delete []nick;
78.         }
79.         break;
80.         case 0x02://yes i want
81.         {
82.             char* nick = new (nothrow) char[P->Size-9];
83.             if(nick==NULL)
84.             {
85.                 Log(MSG_ERROR, "Error allocing memory: pakMessengerManager 2" );
86.                 return false;
87.             }
88.             memcpy( nick, &P->Buffer[3], P->Size-9 );
89.  
90.             if(!CheckEscapeMySQL(nick,-1,true))
91.             {
92.                 Log(MSG_WARNING,"A nick (friendlist) contains incorrect caracter (see warnings above)");
93.                 return false;
94.             }
95.  
96.             CCharClient* otherclient = (CCharClient*) GetClientByName (nick);
97.             if(otherclient!=NULL)
98.             {
99.                     BEGINPACKET( pak, 0x7e1 );
100.                     ADDBYTE    ( pak, 0x02 );
101.                     /*ADDWORD    ( pak, thisclient->charid );
102.                     ADDBYTE    ( pak, 0x00 );
103.                     ADDWORD    ( pak, 0x0000 );*/
104.                     ADDDWORD    ( pak, thisclient->charid );
105.                     ADDBYTE    ( pak, 0x00 );
106.                     ADDSTRING  ( pak, thisclient->charname );
107.                     ADDBYTE    ( pak, 0x00);
108.                     otherclient->SendPacket(&pak);
109.  
110.                     //Add friend to my friend list(sql)
111.                     if(!DB->QExecute("INSERT INTO list_friend (id,idfriend,namefriend) VALUES (%i,%i,'%s')",otherclient->charid,thisclient->charid,thisclient->charname))
112.                     {
113.                         Log(MSG_WARNING,"error addind %s to %s friend list",otherclient->charname,thisclient->charname);
114.                         return false;
115.                     }
116.  
117.                     CFriendList * newfriend1 = new (nothrow) CFriendList;
118.                     if(newfriend1==NULL)
119.                         return false;
120.  
121.                     newfriend1->id = otherclient->charid; //friendid
122.                     strcpy(newfriend1->name, otherclient->charname); //friend name
123.  
124.                     thisclient->FriendList.push_back( newfriend1 );
125.                     RESETPACKET( pak, 0x7e1 );
126.                     ADDBYTE    ( pak, 0x02 );
127.                     /*ADDWORD    ( pak, otherclient->charid );
128.                     ADDBYTE    ( pak, 0x00 );
129.                     ADDWORD    ( pak, 0x0000 );*/
130.                     ADDDWORD    ( pak, otherclient->charid );
131.                     ADDBYTE    ( pak, 0x00 );
132.                     ADDSTRING  ( pak, otherclient->charname );
133.                     ADDBYTE    ( pak, 0x00);
134.                     thisclient->SendPacket(&pak);
135.  
136.                     //Add me to his friend list (sql)
137.                     if(!DB->QExecute("INSERT INTO list_friend (id,idfriend,namefriend) VALUES (%i,%i,'%s')",thisclient->charid,otherclient->charid,otherclient->charname))
138.                         return false;
139.                     CFriendList * newfriend2 = new (nothrow) CFriendList;
140.                     if(newfriend2==NULL)
141.                         return false;
142.                     newfriend2->id = thisclient->charid; //friendid
143.                     strcpy(newfriend2->name, thisclient->charname); //friend name
144.                     otherclient->FriendList.push_back( newfriend2 );
145.                     Log(MSG_INFO,"accept %s ok",nick);
146.             }
147.             else//not found
148.             {
149.                BEGINPACKET( pak, 0x7e1 );
150.                ADDBYTE    ( pak, 0x04 );
151.                ADDSTRING  ( pak, nick );
152.                ADDBYTE    ( pak, 0x00 );
153.                thisclient->SendPacket(&pak);
154.                Log(MSG_INFO,"accept: %s doesn't exist",nick);
155.             }
156.             delete []nick;
157.         }
158.         break;
159.         case 0x03://no, i dont want
160.         {
161.             char* nick = new (nothrow) char[P->Size-9];
162.             if(nick==NULL)
163.             {
164.                 Log(MSG_ERROR, "Error allocing memory: pakMessengerManager 3" );
165.                 return false;
166.             }
167.             memcpy( nick, &P->Buffer[3], P->Size-9 );
168.             CCharClient* otherclient = (CCharClient*) GetClientByName (nick);
169.             if(otherclient!=NULL)
170.             {
171.                 BEGINPACKET( pak, 0x7e1 );
172.                 ADDBYTE    ( pak, 0x03 );
173.                 ADDSTRING  ( pak, thisclient->charname );
174.                 ADDBYTE    ( pak, 0x00);
175.                 otherclient->SendPacket(&pak);
176.                 Log(MSG_INFO,"refuse: %s ok",nick);
177.             }
178.             else
179.             {
180.                BEGINPACKET( pak, 0x7e1 );
181.                ADDBYTE    ( pak, 0x04 );
182.                ADDWORD    ( pak, 0x0000 );
183.                ADDSTRING  ( pak, nick );
184.                ADDBYTE    ( pak, 0x00 );
185.                thisclient->SendPacket(&pak);
186.                Log(MSG_INFO,"refuse: %s doesn't exist",nick);
187.             }
188.         }
189.         break;
190.         case 0x05://delete user.
191.         {
192.             //WORD id = GETWORD ((*P),1);
193.             DWORD id = GETDWORD ((*P),1);
194.             if(!DB->QExecute("DELETE FROM list_friend WHERE id=%i and idfriend=%i",thisclient->charid,id))
195.             {
196.  
197.                 Log(MSG_INFO,"user failed to delete friend slot %i",thisclient->charname,id);
198.                 return false;
199.             }
200.              if(!DB->QExecute("DELETE FROM list_friend WHERE id=%i and idfriend=%i",id,thisclient->charid))
201.              {
202.                  Log(MSG_INFO,"user failed to delete friend slot %i",id,thisclient->charname);
203.                  return false;
204.              }
205.  
206.             Log(MSG_INFO,"user %s deletes friend slot %i",thisclient->charname,id);
207.  
208.             CCharClient* otherclient = (CCharClient*) GetClientByID(id);
209.             if(otherclient!=NULL)
210.             {
211.                     ChangeMessengerStatus ( thisclient, otherclient, 0x08);
212.             }
213.         }
214.         break;
215.         case 0xfa://messenger logout
216. [...]

I still need to do it the other way. Like a player send an invitation to an other player2 but the list of player2 is full, and so can't accept. But i didn't figure how to do it now.
EDIT: Code is not operational for now. So don't try to add this fix.
still a little problem. Charserver stops working right after deleting both rows.
EDIT 2: I putted the final code. But i still have some problems. It's not really working like it should. Some players can't send invitations. Did i something wrong somewhere ? Need little help :p

[WiseGuy]

If someone knows how to do it in one single query, i would be grateful.

try this:

Line number On/Off | Expand/Contract | Select all

1.            if(!DB->QExecute("DELETE FROM list_friend WHERE (id=%i and idfriend=%i) OR (id=%i and idfriend=%i)",thisclient->charid,id, id,thisclient->charid))

[rl2171]

If you get this one working, please post the code and what needs to be replaced and I will add to the SVN too.

[hoegarden31]

yes i will post it later. There are still little problems when you are at the max, and then try to invite a new friend. there is 50% chance that it crash the char-server, or do a weird thing (like the player is still in-game, and can do what you want, but if someone try to invite him, it says that the player is offline)

[WiseGuy]

If you get this one working, please post the code and what needs to be replaced and I will add to the SVN too.

FIND:

Line number On/Off | Expand/Contract | Select all

1.        case 0x05://delete user.

REPLACE:

Line number On/Off | Expand/Contract | Select all

1.            if(!DB->QExecute("DELETE FROM list_friend WHERE id=%i and idfriend=%i",thisclient->charid,id))
2.             {
3.                 Log(MSG_INFO,"user failed to delete friend slot %i",thisclient->charname,id);
4.                 return false;
5.             }
6.  

BY:

Line number On/Off | Expand/Contract | Select all

1.            if(!DB->QExecute("DELETE FROM list_friend WHERE (id=%i and idfriend=%i) OR (id=%i and idfriend=%i)",thisclient->charid,id, id,thisclient->charid))
2.             {
3.                 Log(MSG_INFO,"user %s failed to delete friend slot %i",thisclient->charname,id);
4.                 return false;
5.             }
6.  

This code just reply to:

hoegarden31 wrote
If someone knows how to do it in one single query, i would be grateful.

And yeah it work for me, delete 'idfriend' from this 'id' and vice versa.

[rl2171]

Thank you for the update, will add to the SVN asap.

[hoegarden31]

Here is my final code. I got trouble with when the player who you try to invite has the max too. So i check it at the beginning too.

Line number On/Off | Expand/Contract | Select all

1. // Messenger actions (add/remove/invite)
2. bool CCharServer::pakMessengerManager ( CCharClient* thisclient, CPacket* P )
3. {
4.     BYTE action = GETBYTE((*P),0);
5.     switch (action)
6.     {
7.         case 0x01://wanna be my friend?
8.         {
9.             char* nick = new (nothrow) char[P->Size-7];
10.             if(nick==NULL)
11.             {
12.                 Log(MSG_ERROR, "Error allocing memory: pakMessengerManager 1" );
13.                 return false;
14.             }
15.             memcpy( nick, &P->Buffer[1], P->Size-7 );
16.             Log(MSG_INFO,"%s Trying to invite %s",nick,thisclient->charname);
17.  
18.             CCharClient* otherclient = (CCharClient*) GetClientByName (nick);
19.             if(otherclient!=NULL)
20.             {//Send friend invitation  (check this one)
21.                 if(thisclient->FriendList.size() > 30){
22.                      BEGINPACKET( pak, 0x0784 );
23.                      ADDSTRING( pak, "Server" );
24.                      ADDBYTE( pak, 0 );
25.                      ADDSTRING( pak, "Your friendlist is full. Delete friends before adding new ones." );
26.                      ADDBYTE( pak, 0 );
27.                      thisclient->SendPacket(&pak);
28.                      return false;
29.                 }
30.                 if(otherclient->FriendList.size() > 30)
31.                 {
32.                     BEGINPACKET( pak, 0x0784 );
33.                     ADDSTRING( pak, "Server" );
34.                      ADDBYTE( pak, 0 );
35.                     ADDSTRING( pak, "Your friendlist is full. Delete friends before adding new ones." );
36.                     ADDBYTE( pak, 0 );
37.                      otherclient->SendPacket(&pak);
38.  
39.                     RESETPACKET( pak, 0x0784 );
40.                     ADDSTRING( pak, "Server" );
41.                      ADDBYTE( pak, 0 );
42.                      ADDSTRING( pak, "The list of your friend is full. Invitation canceled" );
43.                      ADDBYTE( pak, 0 );
44.                      thisclient->SendPacket(&pak);
45.                     return false;
46.                 }
47.                 BEGINPACKET( pak, 0x7e1 );
48.                 ADDBYTE    ( pak, 0x01 );
49.                 ADDWORD    ( pak, 0x0000 );
50.                 ADDSTRING  ( pak, thisclient->charname );
51.                 ADDBYTE    ( pak, 0x00 );
52.                 otherclient->SendPacket(&pak);
53.                 Log(MSG_INFO,"%s exists, invite sent to %s",nick,otherclient->charname);
54.             }
55.             else
56.             {//This charname doesnt exist
57.                BEGINPACKET( pak, 0x7e1 );
58.                ADDBYTE    ( pak, 0x04 );
59.                ADDSTRING  ( pak, nick );
60.                ADDBYTE    ( pak, 0x00 );
61.                thisclient->SendPacket(&pak);
62.                Log(MSG_INFO,"invite: %s doesn't exist",nick);
63.             }
64.             delete []nick;
65.         }
66.         break;
67.         case 0x02://yes i want
68.         {
69.             char* nick = new (nothrow) char[P->Size-9];
70.             if(nick==NULL)
71.             {
72.                 Log(MSG_ERROR, "Error allocing memory: pakMessengerManager 2" );
73.                 return false;
74.             }
75.             memcpy( nick, &P->Buffer[3], P->Size-9 );
76.  
77.             if(!CheckEscapeMySQL(nick,-1,true))
78.             {
79.                 Log(MSG_WARNING,"A nick (friendlist) contains incorrect caracter (see warnings above)");
80.                 return false;
81.             }
82.  
83.             CCharClient* otherclient = (CCharClient*) GetClientByName (nick);
84.             if(otherclient!=NULL)
85.             {
86.                     BEGINPACKET( pak, 0x7e1 );
87.                     ADDBYTE    ( pak, 0x02 );
88.                     /*ADDWORD    ( pak, thisclient->charid );
89.                     ADDBYTE    ( pak, 0x00 );
90.                     ADDWORD    ( pak, 0x0000 );*/
91.                     ADDDWORD    ( pak, thisclient->charid );
92.                     ADDBYTE    ( pak, 0x00 );
93.                     ADDSTRING  ( pak, thisclient->charname );
94.                     ADDBYTE    ( pak, 0x00);
95.                     otherclient->SendPacket(&pak);
96.  
97.                     //Add friend to my friend list(sql)
98.                     if(!DB->QExecute("INSERT INTO list_friend (id,idfriend,namefriend) VALUES (%i,%i,'%s')",otherclient->charid,thisclient->charid,thisclient->charname))
99.                     {
100.                         Log(MSG_WARNING,"error addind %s to %s friend list",otherclient->charname,thisclient->charname);
101.                         return false;
102.                     }
103.  
104.                     CFriendList * newfriend1 = new (nothrow) CFriendList;
105.                     if(newfriend1==NULL)
106.                         return false;
107.  
108.                     newfriend1->id = otherclient->charid; //friendid
109.                     strcpy(newfriend1->name, otherclient->charname); //friend name
110.  
111.                     thisclient->FriendList.push_back( newfriend1 );
112.                     RESETPACKET( pak, 0x7e1 );
113.                     ADDBYTE    ( pak, 0x02 );
114.                     /*ADDWORD    ( pak, otherclient->charid );
115.                     ADDBYTE    ( pak, 0x00 );
116.                     ADDWORD    ( pak, 0x0000 );*/
117.                     ADDDWORD    ( pak, otherclient->charid );
118.                     ADDBYTE    ( pak, 0x00 );
119.                     ADDSTRING  ( pak, otherclient->charname );
120.                     ADDBYTE    ( pak, 0x00);
121.                     thisclient->SendPacket(&pak);
122.  
123.                     //Add me to his friend list (sql)
124.                     if(!DB->QExecute("INSERT INTO list_friend (id,idfriend,namefriend) VALUES (%i,%i,'%s')",thisclient->charid,otherclient->charid,otherclient->charname))
125.                         return false;
126.                     CFriendList * newfriend2 = new (nothrow) CFriendList;
127.                     if(newfriend2==NULL)
128.                         return false;
129.                     newfriend2->id = thisclient->charid; //friendid
130.                     strcpy(newfriend2->name, thisclient->charname); //friend name
131.                     otherclient->FriendList.push_back( newfriend2 );
132.                     Log(MSG_INFO,"accept %s ok",nick);
133.             }
134.             else//not found
135.             {
136.                BEGINPACKET( pak, 0x7e1 );
137.                ADDBYTE    ( pak, 0x04 );
138.                ADDSTRING  ( pak, nick );
139.                ADDBYTE    ( pak, 0x00 );
140.                thisclient->SendPacket(&pak);
141.                Log(MSG_INFO,"accept: %s doesn't exist",nick);
142.             }
143.             delete []nick;
144.         }
145.         break;
146.         case 0x03://no, i dont want

Maybe there are some DB->QFree() who are totally unnecessary, but i put them where i could to make sure the script didn't stuck. Maybe in the future, it's possible to add a constant in the config file who define the max amount of friends.
For the delete user from the list, go see the code couple of posts above

[WiseGuy]

You do not need to read from the database to find the number of friends you owned.
Just used something like this:

Line number On/Off | Expand/Contract | Select all

1. if thisclient->FriendList.size() > 30 {
2.     //your code here
3.     return false;
4. }

more faster and simpler.

[hoegarden31]

wait a second .... you tell me that just now ?
Happy new year Thanks for the help, i will change all this now.

[WiseGuy]

better late than not

Happy new year