1 #include "Lobby2Message_PGSQL.h"
2
3 using namespace RakNet;
4
GetUserRowFromHandle(RakNet::RakString & userName,PostgreSQLInterface * pgsql)5 unsigned int RakNet::GetUserRowFromHandle(RakNet::RakString& userName, PostgreSQLInterface *pgsql)
6 {
7 PGresult *result = pgsql->QueryVariadic("SELECT userId_pk,handle from lobby2.users WHERE handleLower=lower(%s)", userName.C_String());
8 if (result)
9 {
10 unsigned int primaryKey;
11 int numRowsReturned = PQntuples(result);
12 if (numRowsReturned>0)
13 {
14 PostgreSQLInterface::PQGetValueFromBinary(&primaryKey, result, 0, "userId_pk");
15 PostgreSQLInterface::PQGetValueFromBinary(&userName, result, 0, "handle");
16 PQclear(result);
17 return primaryKey;
18 }
19 PQclear(result);
20 return 0;
21 }
22 return 0;
23 }
GetClanIdFromHandle(RakNet::RakString clanName,PostgreSQLInterface * pgsql)24 unsigned int RakNet::GetClanIdFromHandle(RakNet::RakString clanName, PostgreSQLInterface *pgsql)
25 {
26 PGresult *result = pgsql->QueryVariadic("SELECT clanId_pk from lobby2.clans WHERE clanHandleLower=lower(%s)", clanName.C_String());
27 if (result)
28 {
29 unsigned int primaryKey;
30 int numRowsReturned = PQntuples(result);
31 if (numRowsReturned>0)
32 {
33 PostgreSQLInterface::PQGetValueFromBinary(&primaryKey, result, 0, "clanId_pk");
34 PQclear(result);
35 return primaryKey;
36 }
37 PQclear(result);
38 return 0;
39 }
40 return 0;
41 }
IsClanLeader(RakNet::RakString clanName,unsigned int userId,PostgreSQLInterface * pgsql)42 bool RakNet::IsClanLeader(RakNet::RakString clanName, unsigned int userId, PostgreSQLInterface *pgsql)
43 {
44 unsigned int clanId = GetClanIdFromHandle(clanName, pgsql);
45 if (clanId==0)
46 return false;
47 return IsClanLeader(clanId, userId, pgsql);
48 }
GetClanLeaderId(unsigned int clanId,PostgreSQLInterface * pgsql)49 unsigned int RakNet::GetClanLeaderId(unsigned int clanId, PostgreSQLInterface *pgsql)
50 {
51 PGresult *result = pgsql->QueryVariadic("SELECT leaderUserId_fk FROM lobby2.clans WHERE clanId_pk=%i", clanId);
52 if (result==0)
53 return 0;
54 int numRowsReturned = PQntuples(result);
55 if (numRowsReturned==0)
56 {
57 PQclear(result);
58 return 0;
59 }
60 int leaderId;
61 PostgreSQLInterface::PQGetValueFromBinary(&leaderId, result, 0, "leaderUserId_fk");
62 PQclear(result);
63 return leaderId;
64 }
IsClanLeader(unsigned int clanId,unsigned int userId,PostgreSQLInterface * pgsql)65 bool RakNet::IsClanLeader(unsigned int clanId, unsigned int userId, PostgreSQLInterface *pgsql)
66 {
67 return userId!=0 && GetClanLeaderId(clanId, pgsql)==userId;
68 }
GetClanMemberState(unsigned int clanId,unsigned int userId,bool * isSubleader,PostgreSQLInterface * pgsql)69 RakNet::ClanMemberState RakNet::GetClanMemberState(unsigned int clanId, unsigned int userId, bool *isSubleader, PostgreSQLInterface *pgsql)
70 {
71 PGresult *result = pgsql->QueryVariadic("SELECT memberState_fk FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", userId, clanId);
72 if (result==0)
73 return CMD_UNDEFINED;
74 int numRowsReturned = PQntuples(result);
75 if (numRowsReturned==0)
76 {
77 PQclear(result);
78 return CMD_UNDEFINED;
79 }
80 int memberState;
81 PostgreSQLInterface::PQGetValueFromBinary(&memberState, result, 0, "memberState_fk");
82 PostgreSQLInterface::PQGetValueFromBinary(isSubleader, result, 0, "isSubleader");
83 PQclear(result);
84 return (ClanMemberState) memberState;
85 }
GetClanMembers(unsigned int clanId,DataStructures::List<ClanMemberDescriptor> & clanMembers,PostgreSQLInterface * pgsql)86 void RakNet::GetClanMembers(unsigned int clanId, DataStructures::List<ClanMemberDescriptor> &clanMembers, PostgreSQLInterface *pgsql)
87 {
88 ClanMemberDescriptor cmd;
89
90 PGresult *result = pgsql->QueryVariadic(
91 "SELECT M.userId_fk, M.isSubleader, M.memberState_fk, M.banReason, U.handle "
92 "FROM lobby2.clanMembers AS M, lobby2.users AS U WHERE M.clanId_fk=%i AND U.userId_pk=M.userId_fk", clanId);
93 if (result==0)
94 return;
95 int numRowsReturned = PQntuples(result);
96 int idx;
97 for (idx=0; idx < numRowsReturned; idx++)
98 {
99 PostgreSQLInterface::PQGetValueFromBinary(&cmd.userId, result, idx, "userId_fk");
100 PostgreSQLInterface::PQGetValueFromBinary(&cmd.isSubleader, result, idx, "isSubleader");
101 int cms;
102 PostgreSQLInterface::PQGetValueFromBinary(&cms, result, idx, "memberState_fk");
103 cmd.memberState=(ClanMemberState)cms;
104 PostgreSQLInterface::PQGetValueFromBinary(&cmd.banReason, result, idx, "banReason");
105 PostgreSQLInterface::PQGetValueFromBinary(&cmd.name, result, idx, "handle");
106 clanMembers.Insert(cmd, __FILE__, __LINE__ );
107 }
108 PQclear(result);
109 }
IsTitleInUse(RakNet::RakString titleName,PostgreSQLInterface * pgsql)110 bool RakNet::IsTitleInUse(RakNet::RakString titleName, PostgreSQLInterface *pgsql)
111 {
112 PGresult *result = pgsql->QueryVariadic("SELECT titleName_pk FROM lobby2.titles where titleName_pk=%s", titleName.C_String());
113 if (result==0)
114 return false;
115 int numRowsReturned = PQntuples(result);
116 PQclear(result);
117 if (numRowsReturned==0)
118 return false;
119 return true;
120 }
StringContainsProfanity(RakNet::RakString string,PostgreSQLInterface * pgsql)121 bool RakNet::StringContainsProfanity(RakNet::RakString string, PostgreSQLInterface *pgsql)
122 {
123 RakNet::RakString strLower1 = " " + string;
124 RakNet::RakString strLower2 = string + " ";
125 RakNet::RakString strLower3 = " " + string + " ";
126 RakNet::RakString strLower4 = string;
127 strLower1.ToLower();
128 strLower2.ToLower();
129 strLower3.ToLower();
130 strLower4.ToLower();
131 PGresult *result = pgsql->QueryVariadic("SELECT wordLower FROM lobby2.profanity WHERE "
132 "wordLower LIKE %s OR wordLower LIKE %s OR wordLower LIKE %s OR wordLower LIKE %s"
133 , strLower1.C_String(), strLower2.C_String(), strLower3.C_String(), strLower4.C_String());
134 if (result==0)
135 return false;
136 int numRowsReturned = PQntuples(result);
137 PQclear(result);
138 if (numRowsReturned==0)
139 return false;
140 return true;
141 }
IsValidCountry(RakNet::RakString string,bool * countryHasStates,PostgreSQLInterface * pgsql)142 bool RakNet::IsValidCountry(RakNet::RakString string, bool *countryHasStates, PostgreSQLInterface *pgsql)
143 {
144 PGresult *result = pgsql->QueryVariadic("SELECT country_name,country_has_states FROM lobby2.country where lower(country_name)=lower(%s)", string.C_String());
145 if (result==0)
146 return false;
147 int numRowsReturned = PQntuples(result);
148 if (countryHasStates && numRowsReturned>0)
149 PostgreSQLInterface::PQGetValueFromBinary(countryHasStates, result, 0, "country_has_states");
150 PQclear(result);
151 if (numRowsReturned==0)
152 return false;
153 return true;
154 }
IsValidState(RakNet::RakString string,PostgreSQLInterface * pgsql)155 bool RakNet::IsValidState(RakNet::RakString string, PostgreSQLInterface *pgsql)
156 {
157 PGresult *result = pgsql->QueryVariadic("SELECT state_name FROM lobby2.state WHERE lower(state_name)=lower(%s)", string.C_String());
158 if (result==0)
159 return false;
160 if (result==0)
161 return false;
162 int numRowsReturned = PQntuples(result);
163 PQclear(result);
164 if (numRowsReturned==0)
165 return false;
166 return true;
167 }
IsValidRace(RakNet::RakString string,PostgreSQLInterface * pgsql)168 bool RakNet::IsValidRace(RakNet::RakString string, PostgreSQLInterface *pgsql)
169 {
170 PGresult *result = pgsql->QueryVariadic("SELECT race_text FROM lobby2.race WHERE lower(race_text)=lower(%s)", string.C_String());
171 if (result==0)
172 return false;
173 if (result==0)
174 return false;
175 int numRowsReturned = PQntuples(result);
176 PQclear(result);
177 if (numRowsReturned==0)
178 return false;
179 return true;
180 }
GetFriendIDs(unsigned int callerUserId,bool excludeIfIgnored,PostgreSQLInterface * pgsql,DataStructures::List<unsigned int> & output)181 void RakNet::GetFriendIDs(unsigned int callerUserId, bool excludeIfIgnored, PostgreSQLInterface *pgsql, DataStructures::List<unsigned int> &output)
182 {
183 PGresult *result = pgsql->QueryVariadic("SELECT userTwo_fk from lobby2.friends WHERE userOne_fk=%i AND "
184 "actionId_fk=(SELECT actionId_pk from lobby2.friendActions WHERE description='isFriends');", callerUserId);
185 if (result==0)
186 return;
187 int numRowsReturned = PQntuples(result);
188 int idx;
189 unsigned int id;
190 for (idx=0; idx < numRowsReturned; idx++)
191 {
192 PostgreSQLInterface::PQGetValueFromBinary(&id, result, idx, "userTwo_fk");
193 if (excludeIfIgnored==false || IsIgnoredByTarget(callerUserId, id, pgsql)==false)
194 output.Insert(id, __FILE__, __LINE__ );
195 }
196 PQclear(result);
197 }
GetClanMateIDs(unsigned int callerUserId,bool excludeIfIgnored,PostgreSQLInterface * pgsql,DataStructures::List<unsigned int> & output)198 void RakNet::GetClanMateIDs(unsigned int callerUserId, bool excludeIfIgnored, PostgreSQLInterface *pgsql, DataStructures::List<unsigned int> &output)
199 {
200 PGresult *result = pgsql->QueryVariadic(
201 "select userId_fk from lobby2.clanMembers where clanId_fk="
202 "(select clanId_fk from lobby2.clanMembers where userId_fk=%i)"
203 "AND userId_fk !=%i;"
204 , callerUserId, callerUserId);
205 if (result==0)
206 return;
207 int numRowsReturned = PQntuples(result);
208 int idx;
209 unsigned int id;
210 for (idx=0; idx < numRowsReturned; idx++)
211 {
212 PostgreSQLInterface::PQGetValueFromBinary(&id, result, idx, "userId_fk");
213 if (excludeIfIgnored==false || IsIgnoredByTarget(callerUserId, id, pgsql)==false)
214 output.Insert(id, __FILE__, __LINE__ );
215 }
216 PQclear(result);
217 }
218
IsIgnoredByTarget(unsigned int callerUserId,unsigned int targetUserId,PostgreSQLInterface * pgsql)219 bool RakNet::IsIgnoredByTarget(unsigned int callerUserId, unsigned int targetUserId, PostgreSQLInterface *pgsql)
220 {
221 PGresult *result = pgsql->QueryVariadic(
222 "select userMe_fk from lobby2.ignore where userMe_fk=%i AND userOther_fk=%i"
223 , callerUserId, targetUserId);
224 if (result==0)
225 return false;
226 int numRowsReturned = PQntuples(result);
227 PQclear(result);
228 return numRowsReturned>0;
229 }
230
OutputFriendsNotification(RakNet::Notification_Friends_StatusChange::Status notificationType,Lobby2ServerCommand * command,PostgreSQLInterface * pgsql)231 void RakNet::OutputFriendsNotification(RakNet::Notification_Friends_StatusChange::Status notificationType, Lobby2ServerCommand *command, PostgreSQLInterface *pgsql)
232 {
233 // Tell all friends about this new login
234 DataStructures::List<unsigned int> output;
235 GetFriendIDs(command->callerUserId, true, pgsql, output);
236
237 unsigned int idx;
238 for (idx=0; idx < output.Size(); idx++)
239 {
240 Notification_Friends_StatusChange *notification = (Notification_Friends_StatusChange *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_StatusChange);
241 RakAssert(command->callingUserName.IsEmpty()==false);
242 notification->otherHandle=command->callingUserName;
243 notification->op=notificationType;
244 notification->resultCode=L2RC_SUCCESS;
245 command->server->AddOutputFromThread(notification, output[idx], "");
246 }
247 }
248
GetFriendInfosByStatus(unsigned int callerUserId,RakNet::RakString status,PostgreSQLInterface * pgsql,DataStructures::List<FriendInfo> & output,bool callerIsUserOne)249 void RakNet::GetFriendInfosByStatus(unsigned int callerUserId, RakNet::RakString status, PostgreSQLInterface *pgsql, DataStructures::List<FriendInfo> &output, bool callerIsUserOne)
250 {
251 RakNet::RakString query;
252 /*
253 if (callerIsUserOne)
254 {
255 query = "SELECT handle from lobby2.users WHERE userId_pk ="
256 "(SELECT userTwo_fk from lobby2.friends WHERE userOne_fk=%i AND actionId_fk ="
257 "(SELECT actionId_pk FROM lobby2.friendActions where description='";
258 }
259 else
260 {
261 query = "SELECT handle from lobby2.users WHERE userId_pk ="
262 "(SELECT userOne_fk from lobby2.friends WHERE userTwo_fk=%i AND actionId_fk ="
263 "(SELECT actionId_pk FROM lobby2.friendActions where description='";
264 }
265
266 query+=status;
267 query+="'));";
268 */
269
270 if (callerIsUserOne)
271 {
272 query = "SELECT handle from lobby2.friends,lobby2.users WHERE userId_pk=userTwo_fk AND userOne_fk=%i";
273 }
274 else
275 {
276 query = "SELECT handle from lobby2.friends,lobby2.users WHERE userId_pk=userOne_fk AND userTwo_fk=%i";
277 }
278 query+=" AND actionId_fk=(SELECT actionId_pk FROM lobby2.friendActions where description='";
279 query+=status;
280 query+="');";
281
282 PGresult *result = pgsql->QueryVariadic( query.C_String(), callerUserId );
283 RakAssert(result);
284
285 int numRowsReturned = PQntuples(result);
286 int i;
287 for (i=0; i < numRowsReturned; i++)
288 {
289 FriendInfo fi;
290 PostgreSQLInterface::PQGetValueFromBinary(&fi.usernameAndStatus.handle, result, i, "handle");
291 fi.usernameAndStatus.isOnline=false;
292 output.Insert(fi, __FILE__, __LINE__ );
293 }
294
295 PQclear(result);
296 }
297
SendEmail(DataStructures::List<RakNet::RakString> & recipientNames,unsigned int senderUserId,RakNet::RakString senderUserName,Lobby2Server * server,RakNet::RakString subject,RakNet::RakString body,RakNetSmartPtr<BinaryDataBlock> binaryData,int status,RakNet::RakString triggerString,PostgreSQLInterface * pgsql)298 void RakNet::SendEmail(DataStructures::List<RakNet::RakString> &recipientNames, unsigned int senderUserId, RakNet::RakString senderUserName, Lobby2Server *server, RakNet::RakString subject, RakNet::RakString body, RakNetSmartPtr<BinaryDataBlock>binaryData, int status, RakNet::RakString triggerString, PostgreSQLInterface *pgsql)
299 {
300 DataStructures::List<unsigned int> targetUserIds;
301 unsigned int targetUserId;
302 for (unsigned int i=0; i < recipientNames.Size(); i++)
303 {
304 targetUserId = GetUserRowFromHandle(recipientNames[i], pgsql);
305 if (targetUserId!=0)
306 targetUserIds.Insert(targetUserId, __FILE__, __LINE__ );
307 }
308 SendEmail(targetUserIds, senderUserId, senderUserName, server, subject, body, binaryData, status, triggerString, pgsql);
309 }
SendEmail(unsigned int targetUserId,unsigned int senderUserId,RakNet::RakString senderUserName,Lobby2Server * server,RakNet::RakString subject,RakNet::RakString body,RakNetSmartPtr<BinaryDataBlock> binaryData,int status,RakNet::RakString triggerString,PostgreSQLInterface * pgsql)310 void RakNet::SendEmail(unsigned int targetUserId, unsigned int senderUserId, RakNet::RakString senderUserName, Lobby2Server *server, RakNet::RakString subject, RakNet::RakString body, RakNetSmartPtr<BinaryDataBlock>binaryData, int status, RakNet::RakString triggerString, PostgreSQLInterface *pgsql)
311 {
312 DataStructures::List<unsigned int> targetUserIds;
313 targetUserIds.Insert(targetUserId, __FILE__, __LINE__ );
314 SendEmail(targetUserIds, senderUserId, senderUserName, server, subject, body, binaryData, status, triggerString, pgsql);
315 }
SendEmail(DataStructures::List<unsigned int> & targetUserIds,unsigned int senderUserId,RakNet::RakString senderUserName,Lobby2Server * server,RakNet::RakString subject,RakNet::RakString body,RakNetSmartPtr<BinaryDataBlock> binaryData,int status,RakNet::RakString triggerString,PostgreSQLInterface * pgsql)316 void RakNet::SendEmail(DataStructures::List<unsigned int> &targetUserIds, unsigned int senderUserId, RakNet::RakString senderUserName, Lobby2Server *server, RakNet::RakString subject, RakNet::RakString body, RakNetSmartPtr<BinaryDataBlock>binaryData, int status, RakNet::RakString triggerString, PostgreSQLInterface *pgsql)
317 {
318 if (targetUserIds.Size()==0)
319 return;
320
321 PGresult *result=0;
322 if (binaryData.IsNull()==false)
323 {
324 result = pgsql->QueryVariadic(
325 "INSERT INTO lobby2.emails (subject, body, binaryData, triggerId_fk) VALUES "
326 "(%s, %s, %a, (SELECT triggerId_pk FROM lobby2.emailTriggers WHERE description=%s) ) RETURNING emailId_pk;"
327 ,subject.C_String(), body.C_String(), binaryData->binaryData, binaryData->binaryDataLength, triggerString.C_String()
328 );
329 }
330 else
331 {
332 result = pgsql->QueryVariadic(
333 "INSERT INTO lobby2.emails (subject, body, triggerId_fk) VALUES "
334 "(%s, %s, %a, (SELECT triggerId_pk FROM lobby2.emailTriggers WHERE description=%s) ) RETURNING emailId_pk;"
335 ,subject.C_String(), body.C_String(), triggerString.C_String()
336 );
337 }
338
339 RakAssert(result);
340 unsigned int emailId_pk;
341 PostgreSQLInterface::PQGetValueFromBinary(&emailId_pk, result, 0, "emailId_pk");
342 PQclear(result);
343
344 unsigned int i;
345 for (i=0; i < targetUserIds.Size(); i++)
346 {
347 // Once in my inbox
348 result = pgsql->QueryVariadic(
349 "INSERT INTO lobby2.emailTargets (emailId_fk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail) VALUES "
350 "(%i, %i, %i, %i, %b, %b);", emailId_pk, senderUserId, targetUserIds[i], status, true, true);
351 RakAssert(result);
352 PQclear(result);
353
354 // Once in the destination inbox
355 result = pgsql->QueryVariadic(
356 "INSERT INTO lobby2.emailTargets (emailId_fk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail) VALUES "
357 "(%i, %i, %i, %i, %b, %b) RETURNING emailTarget_pk;", emailId_pk, targetUserIds[i], senderUserId, status, false, false);
358 RakAssert(result);
359 unsigned int emailTarget_pk;
360 PostgreSQLInterface::PQGetValueFromBinary(&emailTarget_pk, result, 0, "emailTarget_pk");
361 PQclear(result);
362
363 // Notify recipient that they got an email
364 Notification_Emails_Received *notification = (Notification_Emails_Received *) server->GetMessageFactory()->Alloc(L2MID_Notification_Emails_Received);
365 notification->sender=senderUserName;
366 notification->subject=subject;
367 notification->emailId=emailTarget_pk;
368 notification->resultCode=L2RC_SUCCESS;
369 server->AddOutputFromThread(notification, targetUserIds[i], "");
370 }
371 }
GetActiveClanCount(unsigned int userId,PostgreSQLInterface * pgsql)372 int RakNet::GetActiveClanCount(unsigned int userId, PostgreSQLInterface *pgsql)
373 {
374 PGresult *result = pgsql->QueryVariadic("SELECT clanMemberId_pk FROM lobby2.clanMembers WHERE userId_fk=%i AND memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active')", userId);
375 int numRowsReturned = PQntuples(result);
376 PQclear(result);
377 return numRowsReturned;
378 }
CreateAccountParametersFailed(CreateAccountParameters & createAccountParameters,RakNet::Lobby2ResultCode & resultCode,Lobby2ServerCommand * command,PostgreSQLInterface * pgsql)379 bool RakNet::CreateAccountParametersFailed( CreateAccountParameters &createAccountParameters, RakNet::Lobby2ResultCode &resultCode, Lobby2ServerCommand *command, PostgreSQLInterface *pgsql)
380 {
381 bool hasStates=true;
382
383 if (createAccountParameters.homeCountry.IsEmpty()==false)
384 {
385 if (IsValidCountry(createAccountParameters.homeCountry, &hasStates, pgsql)==false)
386 {
387 resultCode=L2RC_Client_RegisterAccount_INVALID_COUNTRY;
388 return true;
389 }
390 }
391 if (hasStates==true)
392 {
393 if (createAccountParameters.homeState.IsEmpty()==false && IsValidState(createAccountParameters.homeState, pgsql)==false)
394 {
395 resultCode=L2RC_Client_RegisterAccount_INVALID_STATE;
396 return true;
397 }
398 }
399 else
400 createAccountParameters.homeState.Clear();
401
402 if (createAccountParameters.billingCountry.IsEmpty()==false)
403 {
404 if (IsValidCountry(createAccountParameters.billingCountry, &hasStates, pgsql)==false)
405 {
406 resultCode=L2RC_Client_RegisterAccount_INVALID_COUNTRY;
407 return true;
408 }
409 }
410 if (hasStates==true)
411 {
412 if (createAccountParameters.billingState.IsEmpty()==false && IsValidState(createAccountParameters.billingState, pgsql)==false)
413 {
414 resultCode=L2RC_Client_RegisterAccount_INVALID_STATE;
415 return true;
416 }
417 }
418 else
419 createAccountParameters.billingState.Clear();
420
421 if (createAccountParameters.race.IsEmpty()==false &&
422 IsValidRace(createAccountParameters.race, pgsql)==false)
423 {
424 resultCode=L2RC_Client_RegisterAccount_INVALID_RACE;
425 return true;
426 }
427 unsigned int requiredAgeYears = command->server->GetConfigurationProperties()->accountRegistrationRequiredAgeYears;
428 if (createAccountParameters.ageInDays < requiredAgeYears*365 )
429 {
430 resultCode=L2RC_Client_RegisterAccount_REQUIRED_AGE_NOT_MET;
431 return true;
432 }
433
434
435 return false;
436
437 }
UpdateAccountFromMissingCreationParameters(CreateAccountParameters & createAccountParameters,unsigned int userPrimaryKey,Lobby2ServerCommand * command,PostgreSQLInterface * pgsql)438 void RakNet::UpdateAccountFromMissingCreationParameters(CreateAccountParameters &createAccountParameters, unsigned int userPrimaryKey, Lobby2ServerCommand *command, PostgreSQLInterface *pgsql)
439 {
440 (void)command;
441
442 PGresult *result=0;
443 unsigned int key;
444
445 if (createAccountParameters.homeState.IsEmpty()==false)
446 {
447 result = pgsql->QueryVariadic("SELECT state_id FROM lobby2.state where lower(state_name)=lower(%s)", createAccountParameters.homeState.C_String());
448 if (result)
449 {
450 if (PQntuples(result))
451 {
452 PostgreSQLInterface::PQGetValueFromBinary(&key, result, 0, "state_id");
453 PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET homeStateId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
454 }
455 PQclear(result);
456 }
457 }
458 if (createAccountParameters.homeCountry.IsEmpty()==false)
459 {
460 result = pgsql->QueryVariadic("SELECT country_id FROM lobby2.country where lower(country_name)=lower(%s)", createAccountParameters.homeCountry.C_String());
461 if (result)
462 {
463 if (PQntuples(result))
464 {
465 PostgreSQLInterface::PQGetValueFromBinary(&key, result, 0, "country_id");
466 PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET homeCountryId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
467 }
468 PQclear(result);
469 }
470 }
471 if (createAccountParameters.billingState.IsEmpty()==false)
472 {
473 result = pgsql->QueryVariadic("SELECT state_id FROM lobby2.state where lower(state_name)=lower(%s)", createAccountParameters.billingState.C_String());
474 if (result)
475 {
476 if (PQntuples(result))
477 {
478 PostgreSQLInterface::PQGetValueFromBinary(&key, result, 0, "state_id");
479 PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET billingStateId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
480 }
481 PQclear(result);
482 }
483 }
484 if (createAccountParameters.billingCountry.IsEmpty()==false)
485 {
486 result = pgsql->QueryVariadic("SELECT country_id FROM lobby2.country where lower(country_name)=lower(%s)", createAccountParameters.billingCountry.C_String());
487 if (result)
488 {
489 if (PQntuples(result))
490 {
491 PostgreSQLInterface::PQGetValueFromBinary(&key, result, 0, "country_id");
492 PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET billingCountryId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
493 }
494 PQclear(result);
495 }
496 }
497 if (createAccountParameters.race.IsEmpty()==false)
498 {
499 result = pgsql->QueryVariadic("SELECT race_id FROM lobby2.race where lower(race_text)=lower(%s)", createAccountParameters.race.C_String());
500 if (result)
501 {
502 if (PQntuples(result))
503 {
504 PostgreSQLInterface::PQGetValueFromBinary(&key, result, 0, "race_id");
505 PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET raceId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
506 }
507 PQclear(result);
508 }
509 }
510
511 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)512 bool RakNet::System_CreateDatabase_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
513 {
514 (void)command;
515
516 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
517 bool success;
518 PGresult *result=0;
519 pgsql->ExecuteBlockingCommand("DROP SCHEMA lobby2 CASCADE;", &result, false);
520 PQclear(result);
521 pgsql->ExecuteBlockingCommand("DROP LANGUAGE plpgsql;", &result, false);
522 PQclear(result);
523 FILE *fp = fopen("Lobby2Schema.txt", "rb");
524 RakAssert(fp && "Can't find Lobby2Schema.txt");
525 fseek( fp, 0, SEEK_END );
526 unsigned int fileSize = ftell( fp );
527 fseek( fp, 0, SEEK_SET );
528 char *cmd = (char*) rakMalloc_Ex(fileSize+1, __FILE__, __LINE__);
529 fread(cmd, 1, fileSize, fp);
530 fclose(fp);
531 cmd[fileSize]=0;
532 success = pgsql->ExecuteBlockingCommand(cmd, &result, false);
533 PQclear(result);
534 if (success)
535 {
536 resultCode=L2RC_SUCCESS;
537 }
538 else
539 {
540 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
541 printf(cmd);
542 printf(pgsql->GetLastError());
543 }
544 rakFree_Ex(cmd, __FILE__, __LINE__ );
545 return true;
546 }
547
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)548 bool RakNet::System_DestroyDatabase_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
549 {
550 (void)command;
551
552 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
553 bool success;
554 PGresult *result=0;
555 success=pgsql->ExecuteBlockingCommand("DROP SCHEMA lobby2 CASCADE;", &result, false);
556 PQclear(result);
557 if (success)
558 resultCode=L2RC_SUCCESS;
559 else
560 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
561 return true;
562 }
563
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)564 bool RakNet::System_CreateTitle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
565 {
566 (void)command;
567
568 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
569 PGresult *result = pgsql->QueryVariadic("INSERT INTO lobby2.titles (titleName_pk, titleSecretKey, requiredAge, binaryData) VALUES (%s,%s,%i,%a)",
570 titleName.C_String(),
571 titleSecretKey.C_String(),
572 requiredAge,
573 binaryData->binaryData,
574 binaryData->binaryDataLength);
575 if (result!=0)
576 {
577 PQclear(result);
578 resultCode=L2RC_SUCCESS;
579 }
580 else
581 {
582 resultCode=L2RC_System_CreateTitle_TITLE_ALREADY_IN_USE;
583 }
584 return true;
585 }
586
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)587 bool RakNet::System_DestroyTitle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
588 {
589 (void)command;
590
591 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
592 PGresult *result=0;
593 result = pgsql->QueryVariadic("DELETE FROM lobby2.titles WHERE titlename_pk=%s", titleName.C_String());
594 if (result!=0)
595 {
596 PQclear(result);
597 resultCode=L2RC_SUCCESS;
598 }
599 else
600 {
601 resultCode=L2RC_System_DestroyTitle_TITLE_NOT_IN_USE;
602 }
603 return true;
604 }
605
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)606 bool RakNet::System_GetTitleRequiredAge_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
607 {
608 (void)command;
609
610 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
611 PGresult *result = pgsql->QueryVariadic("SELECT requiredAge FROM lobby2.titles where titlename_pk=%s", titleName.C_String());
612 if (result!=0)
613 {
614 PostgreSQLInterface::PQGetValueFromBinary(&requiredAge, result, 0, "requiredAge");
615 PQclear(result);
616 resultCode=L2RC_SUCCESS;
617 }
618 else
619 {
620 resultCode=L2RC_System_GetTitleRequiredAge_TITLE_NOT_IN_USE;
621 }
622 return true;
623 }
624
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)625 bool RakNet::System_GetTitleBinaryData_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
626 {
627 (void)command;
628
629 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
630 PGresult *result = pgsql->QueryVariadic("SELECT binaryData FROM lobby2.titles where titlename_pk=%s", titleName.C_String());
631 if (result!=0)
632 {
633 PostgreSQLInterface::PQGetValueFromBinary(&binaryData->binaryData, &binaryData->binaryDataLength, result, 0, "binaryData");
634 PQclear(result);
635 resultCode=L2RC_SUCCESS;
636 }
637 else
638 {
639 resultCode=L2RC_System_GetTitleBinaryData_TITLE_NOT_IN_USE;
640 }
641 return true;
642 }
643
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)644 bool RakNet::System_RegisterProfanity_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
645 {
646 (void)command;
647
648 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
649 // pgsql->PrepareVariadic("INSERT INTO lobby2.profanity (word) VALUES (%s)");
650 // for (unsigned int i=0; i < profanityWords.Size(); i++)
651 // pgsql->PrepareVariadicArgs(0, profanityWords[i].C_String());
652 // PGresult *result = pgsql->ExecutePreparedStatement();
653 PGresult *result=0;
654 resultCode=L2RC_SUCCESS;
655 for (unsigned int i=0; i < profanityWords.Size(); i++)
656 {
657 result = pgsql->QueryVariadic("INSERT INTO lobby2.profanity (word) VALUES (%s)", profanityWords[i].C_String());
658 if (result==0)
659 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
660 PQclear(result);
661 if (resultCode==L2RC_DATABASE_CONSTRAINT_FAILURE)
662 return true;
663 }
664 return true;
665 }
666
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)667 bool RakNet::System_BanUser_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
668 {
669 (void)command;
670
671 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
672 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
673 if (userRow==0)
674 {
675 resultCode=L2RC_UNKNOWN_USER;
676 return true;
677 }
678 PGresult *result = pgsql->QueryVariadic("INSERT INTO lobby2.bannedUsers (userId_fk, description, timeout) VALUES (%i, %s, now () + %i * interval '1 hours')", userRow, banReason.C_String(), durationHours);
679 if (result!=0)
680 {
681 PQclear(result);
682 result = pgsql->QueryVariadic("INSERT INTO lobby2.userHistory (userId_fk, description, triggerId_fk) "
683 "VALUES (%i, %s, (SELECT triggerId_pk FROM lobby2.userHistoryTriggers WHERE description='Banned'))", userRow, banReason.C_String());
684 RakAssert(result);
685 PQclear(result);
686
687 resultCode=L2RC_SUCCESS;
688 }
689 else
690 {
691 resultCode=L2RC_System_BanUser_ALREADY_BANNED;
692 }
693
694 return true;
695 }
696
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)697 bool RakNet::System_UnbanUser_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
698 {
699 (void)command;
700
701 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
702 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
703 if (userRow==0)
704 {
705 resultCode=L2RC_UNKNOWN_USER;
706 return true;
707 }
708 PGresult *result = pgsql->QueryVariadic("DELETE FROM lobby2.bannedUsers WHERE userId_fk=%i", userRow);
709 if (result!=0)
710 {
711 PQclear(result);
712 result = pgsql->QueryVariadic("INSERT INTO lobby2.userHistory (userId_fk, description, triggerId_fk) "
713 "VALUES (%i, %s, (SELECT triggerId_pk FROM lobby2.userHistoryTriggers WHERE description='Unbanned'))", userRow, reason.C_String());
714 RakAssert(result);
715 PQclear(result);
716 resultCode=L2RC_SUCCESS;
717 }
718 else
719 {
720 resultCode=L2RC_System_BanUser_ALREADY_BANNED;
721 }
722 return true;
723 }
724
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)725 bool RakNet::CDKey_Add_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
726 {
727 (void)command;
728
729 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
730 PGresult *result=0;
731 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
732 {
733 resultCode=L2RC_CDKey_Add_TITLE_NOT_IN_USE;
734 return true;
735 }
736 unsigned int i;
737 for (i=0; i < cdKeys.Size(); i++)
738 {
739 result = pgsql->QueryVariadic("INSERT INTO lobby2.cdkeys (cdKey, usable, stolen, titleName_fk) VALUES (%s, true, false, %s);", cdKeys[i].C_String(), titleName.C_String());
740 RakAssert(result);
741 PQclear(result);
742 }
743 resultCode=L2RC_SUCCESS;
744 return true;
745 }
746
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)747 bool RakNet::CDKey_GetStatus_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
748 {
749 (void)command;
750
751 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
752 PGresult *result=0;
753 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
754 {
755 resultCode=L2RC_CDKey_GetStatus_TITLE_NOT_IN_USE;
756 return true;
757 }
758
759 result = pgsql->QueryVariadic(
760 "SELECT lobby2.cdkeys.usable, lobby2.cdkeys.stolen, lobby2.cdkeys.activationDate, lobby2.users.handle "
761 "FROM lobby2.cdkeys LEFT OUTER JOIN lobby2.users ON lobby2.users.userId_pk=lobby2.cdkeys.userId_fk "
762 "WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s;"
763 , cdKey.C_String(), titleName.C_String());
764
765 int numRowsReturned = PQntuples(result);
766 if (numRowsReturned==0)
767 {
768 PQclear(result);
769 resultCode=L2RC_CDKey_GetStatus_UNKNOWN_CD_KEY;
770 return true;
771 }
772
773 PostgreSQLInterface::PQGetValueFromBinary(&usable, result, 0, "lobby2.cdkeys.usable");
774 PostgreSQLInterface::PQGetValueFromBinary(&wasStolen, result, 0, "lobby2.cdkeys.stolen");
775 PostgreSQLInterface::PQGetValueFromBinary(&usedBy, result, 0, "lobby2.users.handle");
776 PostgreSQLInterface::PQGetValueFromBinary(&activationDate, result, 0, "lobby2.cdkeys.activationDate");
777
778 PQclear(result);
779 resultCode=L2RC_SUCCESS;
780 return true;
781 }
782
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)783 bool RakNet::CDKey_Use_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
784 {
785 (void)command;
786
787 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
788 PGresult *result=0;
789 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
790 {
791 resultCode=L2RC_CDKey_Use_TITLE_NOT_IN_USE;
792 return true;
793 }
794
795 result = pgsql->QueryVariadic("SELECT lobby2.cdkeys.usable, lobby2.cdkeys.stolen, lobby2.cdkeys.userId_fk "
796 "FROM lobby2.cdkeys, lobby2.users WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s",
797 cdKey.C_String(), titleName.C_String());
798 int numRowsReturned = PQntuples(result);
799 if (numRowsReturned==0)
800 {
801 PQclear(result);
802 resultCode=L2RC_CDKey_Use_UNKNOWN_CD_KEY;
803 return true;
804 }
805 bool usable, wasStolen, alreadyUsed;
806 unsigned int userId;
807 PostgreSQLInterface::PQGetValueFromBinary(&usable, result, 0, "lobby2.cdkeys.usable");
808 PostgreSQLInterface::PQGetValueFromBinary(&wasStolen, result, 0, "lobby2.cdkeys.stolen");
809 alreadyUsed=PostgreSQLInterface::PQGetValueFromBinary(&userId, result, 0, "lobby2.cdkeys.userId_fk");
810 PQclear(result);
811
812 if (wasStolen)
813 resultCode=L2RC_CDKey_Use_CD_KEY_STOLEN;
814 else if (alreadyUsed)
815 resultCode=L2RC_CDKey_Use_CD_KEY_ALREADY_USED;
816 else if (usable==false)
817 resultCode=L2RC_CDKey_Use_NOT_USABLE;
818 else
819 {
820 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
821 if (userRow==0)
822 {
823 resultCode=L2RC_UNKNOWN_USER;
824 return true;
825 }
826 result = pgsql->QueryVariadic("UPDATE lobby2.cdKeys SET activationDate=now(),"
827 "userId_fk=%i WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s", userRow, cdKey.C_String(), titleName.C_String());
828 if (result==0)
829 {
830 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
831 return true;
832 }
833 PQclear(result);
834 resultCode=L2RC_SUCCESS;
835 }
836 return true;
837 }
838
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)839 bool RakNet::CDKey_FlagStolen_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
840 {
841 (void)command;
842
843 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
844 PGresult *result=0;
845 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
846 {
847 resultCode=L2RC_CDKey_FlagStolen_TITLE_NOT_IN_USE;
848 return true;
849 }
850
851 result = pgsql->QueryVariadic("SELECT lobby2.cdkeys.cdKey, lobby2.cdkeys.userId_fk FROM lobby2.cdkeys WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s",
852 cdKey.C_String(), titleName.C_String());
853 if (result==0)
854 {
855 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
856 return true;
857 }
858 int numRowsReturned = PQntuples(result);
859 if (numRowsReturned==0)
860 {
861 PQclear(result);
862 resultCode=L2RC_CDKey_Use_UNKNOWN_CD_KEY;
863 return true;
864 }
865 unsigned int userId_fk;
866 if (PostgreSQLInterface::PQGetValueFromBinary(&userId_fk, result, 0, "userId_fk"))
867 {
868 PQclear(result);
869 result = pgsql->QueryVariadic("SELECT handle from lobby2.users WHERE userId_pk=%i", userId_fk);
870 PostgreSQLInterface::PQGetValueFromBinary(&userUsingThisKey, result, 0, "handle");
871 PQclear(result);
872
873 }
874 else
875 PQclear(result);
876
877 result = pgsql->QueryVariadic("UPDATE lobby2.cdKeys SET stolen=%b WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s", wasStolen, cdKey.C_String(), titleName.C_String());
878 if (result==0)
879 {
880 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
881 return true;
882 }
883 PQclear(result);
884
885 resultCode=L2RC_SUCCESS;
886 return true;
887 }
888
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)889 bool RakNet::Client_Login_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
890 {
891 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
892 PGresult *result=0;
893 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
894 if (userRow==0)
895 {
896 resultCode=L2RC_Client_Login_HANDLE_NOT_IN_USE_OR_BAD_SECRET_KEY;
897 return true;
898 }
899 result = pgsql->QueryVariadic("SELECT password FROM lobby2.users WHERE userId_pk=%i", userRow);
900 RakNet::RakString password;
901 PostgreSQLInterface::PQGetValueFromBinary(&password, result, 0, "password");
902 PQclear(result);
903 if (password!=userPassword)
904 {
905 resultCode=L2RC_Client_Login_HANDLE_NOT_IN_USE_OR_BAD_SECRET_KEY;
906 return true;
907 }
908 if (command->server->GetConfigurationProperties()->requiresEmailAddressValidationToLogin)
909 {
910 result = pgsql->QueryVariadic("SELECT emailAddressValidated FROM lobby2.users WHERE userId_pk=%i", userRow);
911 bool emailAddressValidated;
912 PostgreSQLInterface::PQGetValueFromBinary(&emailAddressValidated, result, 0, "emailAddressValidated");
913 PQclear(result);
914 if (emailAddressValidated==false)
915 {
916 resultCode=L2RC_Client_Login_EMAIL_ADDRESS_NOT_VALIDATED;
917 return true;
918 }
919 }
920 if (command->server->GetConfigurationProperties()->requiresTitleToLogin)
921 {
922 RakNet::RakString titleDBSecretKey;
923 result = pgsql->QueryVariadic("SELECT titleSecretKey FROM lobby2.titles where titleName_pk=%s", titleName.C_String());
924 int numRowsReturned = PQntuples(result);
925 if (numRowsReturned==0)
926 {
927 resultCode=L2RC_Client_Login_BAD_TITLE_OR_TITLE_SECRET_KEY;
928 PQclear(result);
929 return true;
930 }
931 PostgreSQLInterface::PQGetValueFromBinary(&titleDBSecretKey, result, 0, "titleSecretKey");
932 PQclear(result);
933 // title can have no secret key, in which case you just have to specify a valid title
934 if (titleDBSecretKey.IsEmpty()==false && titleDBSecretKey!=titleSecretKey)
935 {
936 resultCode=L2RC_Client_Login_BAD_TITLE_OR_TITLE_SECRET_KEY;
937 return true;
938 }
939 }
940
941 // Does this user have any stolen CD keys?
942 result = pgsql->QueryVariadic("SELECT stolen FROM lobby2.cdkeys WHERE userId_fk=%i AND stolen=TRUE", userRow);
943 int numRowsReturned = PQntuples(result);
944 PQclear(result);
945 if (numRowsReturned!=0)
946 {
947 resultCode=L2RC_Client_Login_CDKEY_STOLEN;
948 return true;
949 }
950
951 result = pgsql->QueryVariadic("SELECT description, timeout, creationDate from lobby2.bannedUsers WHERE userId_fk=%i AND now() < timeout", userRow);
952 numRowsReturned = PQntuples(result);
953 if (numRowsReturned!=0)
954 {
955 PostgreSQLInterface::PQGetValueFromBinary(&bannedReason, result, 0, "description");
956 PostgreSQLInterface::PQGetValueFromBinary(&bannedExpiration, result, 0, "timeout");
957 PostgreSQLInterface::PQGetValueFromBinary(&whenBanned, result, 0, "creationDate");
958 PQclear(result);
959 resultCode=L2RC_Client_Login_BANNED;
960 return true;
961 }
962 PQclear(result);
963
964 result = pgsql->QueryVariadic("INSERT INTO lobby2.userHistory (userId_fk, triggerId_fk) "
965 "VALUES "
966 "(%i,"
967 "(SELECT triggerId_pk FROM lobby2.userHistoryTriggers WHERE description='Login'))", userRow);
968 PQclear(result);
969
970 command->callingUserName=userName;
971 command->callerUserId=userRow;
972
973 // Let the user do this if they want. Not all users may want ignore lists
974 /*
975 // Trigger GetIgnoreList for this user, so they download the client ignore list used for rooms when they logon
976 Client_GetIgnoreList *ignoreListRequest = (Client_GetIgnoreList *) command->server->GetMessageFactory()->Alloc(L2MID_Client_GetIgnoreList);
977 ignoreListRequest->ServerDBImpl(command, databaseInterface);
978 command->server->AddOutputFromThread(ignoreListRequest, userRow, userName);
979 */
980
981 OutputFriendsNotification(Notification_Friends_StatusChange::FRIEND_LOGGED_IN, command, pgsql);
982
983 // Have the server record in memory this new login
984 command->server->OnLogin(command, true);
985
986 resultCode=L2RC_SUCCESS;
987 return true;
988 }
989
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)990 bool RakNet::Client_Logoff_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
991 {
992 if (command->callerUserId==0)
993 return false;
994
995 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
996 PGresult *result=0;
997 result = pgsql->QueryVariadic("INSERT INTO lobby2.userHistory (userId_fk, triggerId_fk) "
998 "VALUES "
999 "(%i,"
1000 "(SELECT triggerId_pk FROM lobby2.userHistoryTriggers WHERE description='Logoff'));", command->callerUserId);
1001 PQclear(result);
1002
1003 // Notification is done below
1004 command->server->OnLogoff(command, true);
1005
1006 resultCode=L2RC_SUCCESS;
1007 return true;
1008 }
1009
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1010 bool RakNet::Client_RegisterAccount_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1011 {
1012 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1013 PGresult *result=0;
1014
1015 if (StringContainsProfanity(userName, pgsql))
1016 {
1017 resultCode=L2RC_PROFANITY_FILTER_CHECK_FAILED;
1018 return true;
1019 }
1020
1021 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1022 if (userRow!=0)
1023 {
1024 resultCode=L2RC_Client_RegisterAccount_HANDLE_ALREADY_IN_USE;
1025 return true;
1026 }
1027
1028 if (CreateAccountParametersFailed(createAccountParameters, resultCode, command, pgsql))
1029 return true;
1030
1031
1032 if (command->server->GetConfigurationProperties()->accountRegistrationRequiresCDKey)
1033 {
1034 if (cdKey.IsEmpty())
1035 {
1036 resultCode=L2RC_Client_RegisterAccount_REQUIRES_CD_KEY;
1037 return true;
1038 }
1039
1040 if (titleName.IsEmpty())
1041 {
1042 resultCode=L2RC_Client_RegisterAccount_REQUIRES_CD_KEY;
1043 return true;
1044 }
1045 result = pgsql->QueryVariadic("SELECT usable, stolen "
1046 "FROM lobby2.cdkeys WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s",
1047 cdKey.C_String(), titleName.C_String());
1048
1049 int numRowsReturned = PQntuples(result);
1050 if (numRowsReturned==0)
1051 {
1052 PQclear(result);
1053 resultCode=L2RC_Client_RegisterAccount_CD_KEY_NOT_USABLE;
1054 return true;
1055 }
1056
1057 bool usable;
1058 bool wasStolen;
1059 RakNet::RakString usedBy;
1060 PostgreSQLInterface::PQGetValueFromBinary(&usable, result, 0, "usable");
1061 PostgreSQLInterface::PQGetValueFromBinary(&wasStolen, result, 0, "stolen");
1062 PQclear(result);
1063 if (usable==false)
1064 {
1065 PQclear(result);
1066 resultCode=L2RC_Client_RegisterAccount_CD_KEY_NOT_USABLE;
1067 return true;
1068 }
1069 if (wasStolen)
1070 {
1071 PQclear(result);
1072 resultCode=L2RC_Client_RegisterAccount_CD_KEY_STOLEN;
1073 return true;
1074 }
1075 if (usedBy.IsEmpty()==false)
1076 {
1077 PQclear(result);
1078 resultCode=L2RC_Client_RegisterAccount_CD_KEY_ALREADY_USED;
1079 return true;
1080 }
1081 }
1082
1083 result = pgsql->QueryVariadic(
1084 "INSERT INTO lobby2.users ("
1085 "handle, firstname, middlename, lastname,"
1086 "sex_male, homeaddress1, homeaddress2, homecity, "
1087 "homezipcode, billingaddress1, billingaddress2, billingcity,"
1088 "billingzipcode, emailaddress, password, passwordrecoveryquestion,"
1089 "passwordrecoveryanswer, caption1, caption2, dateOfBirth, binaryData) "
1090 "VALUES ("
1091 "%s, %s, %s, %s,"
1092 "%b, %s, %s, %s,"
1093 "%s, %s, %s, %s,"
1094 "%s, %s, %s, %s,"
1095 "%s, %s, %s, (select now() - %i * interval '1 day'), %a) RETURNING userId_pk",
1096 userName.C_String(), createAccountParameters.firstName.C_String(), createAccountParameters.middleName.C_String(), createAccountParameters.lastName.C_String(),
1097 createAccountParameters.sex_male, createAccountParameters.homeAddress1.C_String(), createAccountParameters.homeAddress2.C_String(), createAccountParameters.homeCity.C_String(),
1098 createAccountParameters.homeZipCode.C_String(), createAccountParameters.billingAddress1.C_String(), createAccountParameters.billingAddress2.C_String(), createAccountParameters.billingCity.C_String(),
1099 createAccountParameters.billingZipCode.C_String(), createAccountParameters.emailAddress.C_String(), createAccountParameters.password.C_String(), createAccountParameters.passwordRecoveryQuestion.C_String(),
1100 createAccountParameters.passwordRecoveryAnswer.C_String(), createAccountParameters.caption1.C_String(),createAccountParameters.caption2.C_String(),
1101 createAccountParameters.ageInDays, createAccountParameters.binaryData->binaryData, createAccountParameters.binaryData->binaryDataLength);
1102 if (result==0)
1103 {
1104 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1105 return true;
1106 }
1107
1108 unsigned int userPrimaryKey;
1109 PostgreSQLInterface::PQGetValueFromBinary(&userPrimaryKey, result, 0, "userId_pk");
1110 PQclear(result);
1111
1112 // Assign the cd key, already validated earlier
1113 if (command->server->GetConfigurationProperties()->accountRegistrationRequiresCDKey)
1114 {
1115 PQclear(pgsql->QueryVariadic("UPDATE lobby2.cdKeys SET activationDate=now(),"
1116 "userId_fk=%i WHERE lobby2.cdkeys.cdKey=%s AND lobby2.cdkeys.titleName_fk=%s", userPrimaryKey, cdKey.C_String(), titleName.C_String()));
1117 }
1118
1119 UpdateAccountFromMissingCreationParameters(createAccountParameters, userPrimaryKey, command, pgsql);
1120
1121 resultCode=L2RC_SUCCESS;
1122 return true;
1123 }
1124
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1125 bool RakNet::System_SetEmailAddressValidated_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1126 {
1127 (void)command;
1128
1129 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1130 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1131 if (userRow==0)
1132 {
1133 resultCode=L2RC_UNKNOWN_USER;
1134 return true;
1135 }
1136 PGresult *result = pgsql->QueryVariadic("UPDATE lobby2.users SET emailAddressValidated=%b WHERE userId_pk=%i", validated, userRow);
1137 PQclear(result);
1138 if (result!=0)
1139 resultCode=L2RC_SUCCESS;
1140 else
1141 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1142 return true;
1143 }
1144
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1145 bool RakNet::Client_ValidateHandle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1146 {
1147 (void)command;
1148
1149 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1150 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1151 if (userRow!=0)
1152 {
1153 resultCode=L2RC_Client_ValidateHandle_HANDLE_ALREADY_IN_USE;
1154 return true;
1155 }
1156 if (StringContainsProfanity(userName, pgsql))
1157 {
1158 resultCode=L2RC_PROFANITY_FILTER_CHECK_FAILED;
1159 return true;
1160 }
1161 resultCode=L2RC_SUCCESS;
1162 return true;
1163 }
1164
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1165 bool RakNet::System_DeleteAccount_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1166 {
1167 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1168 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1169 if (userRow==0)
1170 {
1171 resultCode=L2RC_UNKNOWN_USER;
1172 return true;
1173 }
1174
1175 PGresult *result = pgsql->QueryVariadic(
1176 "SELECT password from lobby2.users WHERE userId_pk = %i", userRow);
1177 if (result==0)
1178 {
1179 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1180 return true;
1181 }
1182 int numRowsReturned = PQntuples(result);
1183 if (numRowsReturned==0)
1184 {
1185 PQclear(result);
1186 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1187 return true;
1188 }
1189 RakNet::RakString passwordFromDB;
1190 PostgreSQLInterface::PQGetValueFromBinary(&passwordFromDB, result, 0, "password");
1191 PQclear(result);
1192 if (passwordFromDB!=password)
1193 {
1194 PQclear(result);
1195 resultCode=L2RC_System_DeleteAccount_INVALID_PASSWORD;
1196 return true;
1197 }
1198
1199 // Notify friends of account deletion
1200 command->callingUserName=userName;
1201 command->callerUserId=userRow;
1202 OutputFriendsNotification(Notification_Friends_StatusChange::FRIEND_ACCOUNT_WAS_DELETED, command, pgsql);
1203
1204 // Trigger logoff as well
1205 Client_Logoff *logoffRequest = (Client_Logoff *) command->server->GetMessageFactory()->Alloc(L2MID_Client_Logoff);
1206 logoffRequest->ServerDBImpl( command, databaseInterface );
1207 command->server->AddOutputFromThread(logoffRequest, userRow, userName);
1208
1209 // Delete the account
1210 result = pgsql->QueryVariadic("DELETE FROM lobby2.users WHERE userId_pk=%i", userRow);
1211 PQclear(result);
1212 if (result!=0)
1213 resultCode=L2RC_SUCCESS;
1214 else
1215 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1216 return true;
1217 }
1218
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1219 bool RakNet::System_PruneAccounts_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1220 {
1221 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1222 PGresult *result = pgsql->QueryVariadic(
1223 "SELECT handle from lobby2.users WHERE userId_pk ="
1224 "(SELECT userId_fk FROM lobby2.userHistory WHERE creationDate < now() - %i * interval '1 day' ORDER by creationDate DESC LIMIT 1 AND triggerid_fk="
1225 "(SELECT triggerId_pk from lobby2.userHistoryTriggers where description='Login')"
1226 ") ;", deleteAccountsNotLoggedInDays);
1227
1228 if (result==0)
1229 {
1230 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1231 return true;
1232 }
1233
1234 int numRowsReturned = PQntuples(result);
1235 if (numRowsReturned==0)
1236 {
1237 PQclear(result);
1238 resultCode=L2RC_SUCCESS;
1239 return true;
1240 }
1241
1242 // Delete all accounts where the user has not logged in deleteAccountsNotLoggedInDays
1243 System_DeleteAccount *deleteAccount = (System_DeleteAccount *) command->server->GetMessageFactory()->Alloc(L2MID_System_DeleteAccount);
1244
1245 RakNet::RakString userName;
1246 for (int i=0; i < numRowsReturned; i++)
1247 {
1248 PostgreSQLInterface::PQGetValueFromBinary(&userName, result, i, "handle");
1249 deleteAccount->userName=userName;
1250 deleteAccount->ServerDBImpl( command, databaseInterface );
1251 }
1252 command->server->GetMessageFactory()->Dealloc(deleteAccount);
1253 PQclear(result);
1254
1255 return true;
1256 }
1257
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1258 bool RakNet::Client_GetEmailAddress_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1259 {
1260 (void)command;
1261
1262 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1263 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1264 if (userRow==0)
1265 {
1266 resultCode=L2RC_UNKNOWN_USER;
1267 return true;
1268 }
1269
1270 PGresult *result = pgsql->QueryVariadic(
1271 "SELECT emailaddress, emailAddressValidated from lobby2.users WHERE userId_pk = %i", userRow);
1272
1273 if (result==0)
1274 {
1275 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1276 return true;
1277 }
1278
1279 PostgreSQLInterface::PQGetValueFromBinary(&emailAddress, result, 0, "emailAddress");
1280 PostgreSQLInterface::PQGetValueFromBinary(&emailAddressValidated, result, 0, "emailAddressValidated");
1281 PQclear(result);
1282
1283 resultCode=L2RC_SUCCESS;
1284 return true;
1285 }
1286
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1287 bool RakNet::Client_GetPasswordRecoveryQuestionByHandle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1288 {
1289 (void)command;
1290
1291 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1292 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1293 if (userRow==0)
1294 {
1295 resultCode=L2RC_UNKNOWN_USER;
1296 return true;
1297 }
1298
1299 PGresult *result = pgsql->QueryVariadic(
1300 "SELECT passwordRecoveryQuestion,emailAddress from lobby2.users WHERE userId_pk = %i", userRow);
1301
1302 if (result==0)
1303 {
1304 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1305 return true;
1306 }
1307
1308
1309 int numRowsReturned = PQntuples(result);
1310 if (numRowsReturned==0)
1311 {
1312 PQclear(result);
1313 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1314 return true;
1315 }
1316
1317 PostgreSQLInterface::PQGetValueFromBinary(&emailAddress, result, 0, "emailAddress");
1318 PostgreSQLInterface::PQGetValueFromBinary(&passwordRecoveryQuestion, result, 0, "passwordRecoveryQuestion");
1319 PQclear(result);
1320
1321 resultCode=L2RC_SUCCESS;
1322 return true;
1323 }
1324
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1325 bool RakNet::Client_GetPasswordByPasswordRecoveryAnswer_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1326 {
1327 (void)command;
1328
1329 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1330 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1331 if (userRow==0)
1332 {
1333 resultCode=L2RC_UNKNOWN_USER;
1334 return true;
1335 }
1336
1337 PGresult *result = pgsql->QueryVariadic(
1338 "SELECT password from lobby2.users WHERE lower(passwordrecoveryanswer) = lower(%s) AND userId_pk = %i", passwordRecoveryAnswer.C_String(), userRow);
1339
1340 if (result==0)
1341 {
1342 resultCode=L2RC_Client_GetPasswordByPasswordRecoveryAnswer_BAD_ANSWER;
1343 return true;
1344 }
1345
1346 int numRowsReturned = PQntuples(result);
1347 if (numRowsReturned==0)
1348 {
1349 PQclear(result);
1350 resultCode=L2RC_Client_GetPasswordByPasswordRecoveryAnswer_BAD_ANSWER;
1351 return true;
1352 }
1353
1354 PostgreSQLInterface::PQGetValueFromBinary(&password, result, 0, "password");
1355 PQclear(result);
1356
1357 resultCode=L2RC_SUCCESS;
1358 return true;
1359 }
1360
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1361 bool RakNet::Client_ChangeHandle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1362 {
1363 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1364 unsigned int userRow = GetUserRowFromHandle(userName, pgsql);
1365 if (userRow==0)
1366 {
1367 resultCode=L2RC_UNKNOWN_USER;
1368 return true;
1369 }
1370
1371 unsigned int userRow2 = GetUserRowFromHandle(newHandle, pgsql);
1372 if (userRow2!=0)
1373 {
1374 resultCode=L2RC_Client_ChangeHandle_NEW_HANDLE_ALREADY_IN_USE;
1375 return true;
1376 }
1377
1378 if (StringContainsProfanity(newHandle, pgsql))
1379 {
1380 resultCode=L2RC_PROFANITY_FILTER_CHECK_FAILED;
1381 return true;
1382 }
1383
1384 if (requiresPasswordToChangeHandle)
1385 {
1386 PGresult *result = pgsql->QueryVariadic(
1387 "SELECT password from lobby2.users WHERE userId_pk = %i", userRow);
1388 if (result==0)
1389 {
1390 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1391 return true;
1392 }
1393 int numRowsReturned = PQntuples(result);
1394 if (numRowsReturned==0)
1395 {
1396 PQclear(result);
1397 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1398 return true;
1399 }
1400 RakNet::RakString passwordFromDB;
1401 PostgreSQLInterface::PQGetValueFromBinary(&passwordFromDB, result, 0, "password");
1402 PQclear(result);
1403 if (passwordFromDB!=password)
1404 {
1405 PQclear(result);
1406 resultCode=L2RC_Client_ChangeHandle_INVALID_PASSWORD;
1407 return true;
1408 }
1409 }
1410
1411 PGresult *result = pgsql->QueryVariadic(
1412 "UPDATE lobby2.users SET handle=%s WHERE userId_pk=%i", newHandle.C_String(), userRow);
1413 PQclear(result);
1414
1415 // Tell all friends and clanMembers
1416 DataStructures::List<unsigned int> output;
1417 GetFriendIDs(command->callerUserId, false, pgsql, output);
1418 GetClanMateIDs(command->callerUserId, false, pgsql, output);
1419
1420 unsigned int i;
1421 for (i=0; i < output.Size(); i++)
1422 {
1423 Notification_User_ChangedHandle *notification = (Notification_User_ChangedHandle *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_User_ChangedHandle);
1424 notification->oldHandle=userName;
1425 notification->newHandle=newHandle;
1426 notification->resultCode=L2RC_SUCCESS;
1427 command->server->AddOutputFromThread(notification, output[i], "");
1428 }
1429
1430 command->callingUserName=newHandle;
1431 command->server->OnChangeHandle(command, true);
1432
1433 resultCode=L2RC_SUCCESS;
1434 return true;
1435 }
1436
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1437 bool RakNet::Client_UpdateAccount_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1438 {
1439 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1440 PGresult *result=0;
1441 if (CreateAccountParametersFailed(createAccountParameters, resultCode, command, pgsql))
1442 return true;
1443
1444 // PQclear( pgsql->QueryVariadic("UPDATE lobby2.users SET homeCountryId_fk=%i WHERE userId_pk=%i", key, userPrimaryKey ));
1445
1446 result = pgsql->QueryVariadic(
1447 "UPDATE lobby2.users SET "
1448 "firstname=%s, middlename=%s, lastname=%s,"
1449 "sex_male=%b, homeaddress1=%s, homeaddress2=%s, homecity=%s, "
1450 "homezipcode=%s, billingaddress1=%s, billingaddress2=%s, billingcity=%s,"
1451 "billingzipcode=%s, emailaddress=%s, password=%s, passwordrecoveryquestion=%s,"
1452 "passwordrecoveryanswer=%s, caption1=%s, caption2=%s, dateOfBirth=(select now() - %i * interval '1 day'), binaryData=%a "
1453 "WHERE userId_pk = %i",
1454 createAccountParameters.firstName.C_String(), createAccountParameters.middleName.C_String(), createAccountParameters.lastName.C_String(),
1455 createAccountParameters.sex_male, createAccountParameters.homeAddress1.C_String(), createAccountParameters.homeAddress2.C_String(), createAccountParameters.homeCity.C_String(),
1456 createAccountParameters.homeZipCode.C_String(), createAccountParameters.billingAddress1.C_String(), createAccountParameters.billingAddress2.C_String(), createAccountParameters.billingCity.C_String(),
1457 createAccountParameters.billingZipCode.C_String(), createAccountParameters.emailAddress.C_String(), createAccountParameters.password.C_String(), createAccountParameters.passwordRecoveryQuestion.C_String(),
1458 createAccountParameters.passwordRecoveryAnswer.C_String(), createAccountParameters.caption1.C_String(),createAccountParameters.caption2.C_String(),
1459 createAccountParameters.ageInDays, createAccountParameters.binaryData->binaryData, createAccountParameters.binaryData->binaryDataLength,
1460 command->callerUserId);
1461 if (result==0)
1462 {
1463 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1464 return true;
1465 }
1466
1467 UpdateAccountFromMissingCreationParameters(createAccountParameters, command->callerUserId, command, pgsql);
1468
1469 resultCode=L2RC_SUCCESS;
1470 return true;
1471 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1472 bool RakNet::Client_GetAccountDetails_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1473 {
1474 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1475 PGresult *result=0;
1476
1477 result = pgsql->QueryVariadic(
1478 "SELECT firstname, middlename, lastname, (SELECT race_text FROM lobby2.race WHERE "
1479 "race_id=(SELECT raceid_fk FROM lobby2.users WHERE userId_pk = %i )) as race, sex_male, homeaddress1, "
1480 "homeaddress2, homecity,(SELECT state_name FROM lobby2.state WHERE state_id=(SELECT homestateid_fk FROM lobby2.users WHERE userId_pk = %i)) as homeState,(SELECT country_name FROM lobby2.country "
1481 "WHERE country_id=(SELECT homeCountryid_fk FROM lobby2.users WHERE userId_pk = %i)) as homeCountry,homezipcode, billingaddress1, billingaddress2, billingcity,"
1482 "(SELECT state_name FROM lobby2.state WHERE state_id=(SELECT billingstateid_fk FROM lobby2.users WHERE userId_pk = %i)) as billingState,(SELECT country_name FROM "
1483 "lobby2.country WHERE country_id=(SELECT billingCountryid_fk FROM lobby2.users WHERE userId_pk = %i)) as billingCountry,billingzipcode, emailaddress, password, passwordrecoveryquestion,"
1484 "passwordrecoveryanswer, caption1, caption2, (SELECT (EXTRACT(EPOCH FROM now()) - extract(epoch from dateofbirth))/(24*60*60) AS days_old "
1485 "FROM lobby2.users WHERE userId_pk = %i) as ageInDays, binaryData FROM lobby2.users WHERE userId_pk = %i"
1486 ,command->callerUserId, command->callerUserId,command->callerUserId, command->callerUserId,command->callerUserId
1487 ,command->callerUserId, command->callerUserId);
1488
1489
1490
1491 if (result==0)
1492 {
1493 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1494 return true;
1495 }
1496
1497 int numRowsReturned = PQntuples(result);
1498 if (numRowsReturned==0)
1499 {
1500 PQclear(result);
1501 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1502 return true;
1503 }
1504
1505 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.firstName, result, 0, "firstname");
1506 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.middleName, result, 0, "middleName");
1507 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.lastName, result, 0, "lastName");
1508 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.race, result, 0, "race");
1509 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.sex_male, result, 0, "sex_male");
1510 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeAddress1, result, 0, "homeAddress1");
1511 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeAddress2, result, 0, "homeAddress2");
1512 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeCity, result, 0, "homeCity");
1513 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeState, result, 0, "homeState");
1514 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeCountry, result, 0, "homeCountry");
1515 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.homeZipCode, result, 0, "homeZipCode");
1516 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingAddress1, result, 0, "billingAddress1");
1517 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingAddress2, result, 0, "billingAddress2");
1518 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingCity, result, 0, "billingCity");
1519 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingState, result, 0, "billingState");
1520 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingCountry, result, 0, "billingCountry");
1521 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.billingZipCode, result, 0, "billingZipCode");
1522 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.emailAddress, result, 0, "emailAddress");
1523 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.password, result, 0, "password");
1524 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.passwordRecoveryQuestion, result, 0, "passwordRecoveryQuestion");
1525 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.passwordRecoveryAnswer, result, 0, "passwordRecoveryAnswer");
1526 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.caption1, result, 0, "caption1");
1527 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.caption2, result, 0, "caption2");
1528 double d;
1529 PostgreSQLInterface::PQGetValueFromBinary(&d, result, 0, "ageInDays");
1530 createAccountParameters.ageInDays=(unsigned int) d;
1531 // PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.ageInDays, result, 0, "ageInDays");
1532 PostgreSQLInterface::PQGetValueFromBinary(&createAccountParameters.binaryData->binaryData, &createAccountParameters.binaryData->binaryDataLength, result, 0, "binaryData");
1533 PQclear(result);
1534
1535 resultCode=L2RC_SUCCESS;
1536 return true;
1537 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1538 bool RakNet::Client_StartIgnore_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1539 {
1540 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1541 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
1542 if (targetUserId==0)
1543 {
1544 resultCode=L2RC_Client_StartIgnore_UNKNOWN_TARGET_HANDLE;
1545 return true;
1546 }
1547
1548 if (targetUserId==command->callerUserId)
1549 {
1550 resultCode=L2RC_Client_StartIgnore_CANNOT_PERFORM_ON_SELF;
1551 return true;
1552 }
1553
1554 PGresult *result = pgsql->QueryVariadic(
1555 "INSERT INTO lobby2.ignore (userMe_fk, userOther_fk) VALUES (%i, %i)", command->callerUserId, targetUserId);
1556
1557 if (result==0)
1558 {
1559 resultCode=L2RC_Client_StartIgnore_ALREADY_IGNORED;
1560 return true;
1561 }
1562 PQclear(result);
1563
1564 Notification_Client_IgnoreStatus *notification = (Notification_Client_IgnoreStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Client_IgnoreStatus);
1565 notification->otherHandle=command->callingUserName;
1566 notification->nowIgnored=true;
1567 notification->resultCode=L2RC_SUCCESS;
1568 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
1569
1570 resultCode=L2RC_SUCCESS;
1571 return true;
1572 }
1573
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1574 bool RakNet::Client_GetIgnoreList_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1575 {
1576 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1577 PGresult *result = pgsql->QueryVariadic("SELECT handle FROM lobby2.users WHERE userId_pk="
1578 "(SELECT userOther_fk FROM lobby2.ignore WHERE userMe_fk=%i);", command->callerUserId);
1579
1580 if (result==0)
1581 {
1582 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1583 return true;
1584 }
1585
1586 RakNet::RakString handle;
1587 ignoredHandles.Clear(false, __FILE__, __LINE__);
1588 int numRowsReturned = PQntuples(result);
1589 int i;
1590 for (i=0; i < numRowsReturned; i++)
1591 {
1592 PostgreSQLInterface::PQGetValueFromBinary(&handle, result, i, "handle");
1593 ignoredHandles.Insert(handle, __FILE__, __LINE__ );
1594 }
1595 PQclear(result);
1596
1597 resultCode=L2RC_SUCCESS;
1598 return true;
1599 }
Write(Lobby2ServerCommand * command,void * databaseInterface)1600 bool RakNet::Client_PerTitleIntegerStorage_PGSQL::Write( Lobby2ServerCommand *command, void *databaseInterface )
1601 {
1602 PGresult *result=0;
1603 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1604 result = pgsql->QueryVariadic(
1605 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g);",
1606 titleName.C_String(), slotIndex, command->callerUserId, inputValue);
1607 if (result==0)
1608 {
1609 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1610 return true;
1611 }
1612 /*
1613 switch (addConditionForOperation)
1614 {
1615 case PTISC_EQUAL:
1616 result = pgsql->QueryVariadic(
1617 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) WHERE value=%g RETURNING value;",
1618 titleName.C_String(), slotIndex, command->callerUserId, inputValue, conditionValue);
1619 break;
1620 case PTISC_NOT_EQUAL:
1621 result = pgsql->QueryVariadic(
1622 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) RETURNING value;",
1623 titleName.C_String(), slotIndex, command->callerUserId, inputValue);
1624 break;
1625 case PTISC_GREATER_THAN:
1626 result = pgsql->QueryVariadic(
1627 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) WHERE value<%g RETURNING value;",
1628 titleName.C_String(), slotIndex, command->callerUserId, inputValue, conditionValue);
1629 break;
1630 case PTISC_GREATER_OR_EQUAL:
1631 result = pgsql->QueryVariadic(
1632 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) WHERE value<=%g RETURNING value;",
1633 titleName.C_String(), slotIndex, command->callerUserId, inputValue, conditionValue);
1634 break;
1635 case PTISC_LESS_THAN:
1636 result = pgsql->QueryVariadic(
1637 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) WHERE value>%g RETURNING value;",
1638 titleName.C_String(), slotIndex, command->callerUserId, inputValue, conditionValue);
1639 break;
1640 case PTISC_LESS_OR_EQUAL:
1641 result = pgsql->QueryVariadic(
1642 "INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,userId_fk,value) VALUES (%s,%i,%i,%g) WHERE value>=%g RETURNING value;",
1643 titleName.C_String(), slotIndex, command->callerUserId, inputValue, conditionValue);
1644 break;
1645 }
1646
1647 int numRowsReturned = PQntuples(result);
1648 if (numRowsReturned==0)
1649 {
1650 PQclear(result);
1651 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1652 return true;
1653 }
1654 PostgreSQLInterface::PQGetValueFromBinary(&outputValue, result, 0, "value");
1655 */
1656
1657
1658 PQclear(result);
1659
1660 resultCode=L2RC_SUCCESS;
1661 return true;
1662 }
Read(Lobby2ServerCommand * command,void * databaseInterface)1663 bool RakNet::Client_PerTitleIntegerStorage_PGSQL::Read( Lobby2ServerCommand *command, void *databaseInterface )
1664 {
1665 PGresult *result=0;
1666 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1667
1668 result = pgsql->QueryVariadic("SELECT value FROM lobby2.perTitlePerUserIntegerStorage WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i", titleName.C_String(), slotIndex, command->callerUserId);
1669 int numRowsReturned = PQntuples(result);
1670 if (numRowsReturned==0)
1671 {
1672 PQclear(result);
1673 resultCode=L2RC_Client_PerTitleIntegerStorage_ROW_EMPTY;
1674 return true;
1675 }
1676
1677 PostgreSQLInterface::PQGetValueFromBinary(&outputValue, result, 0, "value");
1678 PQclear(result);
1679
1680 resultCode=L2RC_SUCCESS;
1681 return true;
1682 }
Delete(Lobby2ServerCommand * command,void * databaseInterface)1683 bool RakNet::Client_PerTitleIntegerStorage_PGSQL::Delete( Lobby2ServerCommand *command, void *databaseInterface )
1684 {
1685 PGresult *result=0;
1686 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1687 result = pgsql->QueryVariadic("DELETE FROM lobby2.perTitlePerUserIntegerStorage WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i", titleName.C_String(), slotIndex, command->callerUserId);
1688 PQclear(result);
1689
1690 resultCode=L2RC_SUCCESS;
1691 return true;
1692 }
Add(Lobby2ServerCommand * command,void * databaseInterface)1693 bool RakNet::Client_PerTitleIntegerStorage_PGSQL::Add( Lobby2ServerCommand *command, void *databaseInterface )
1694 {
1695 // In MySQL I think you can do this:
1696 // INSERT INTO lobby2.perTitlePerUserIntegerStorage (titleName_fk,slotIndex,value) VALUES ('tn',1,2) ON DUPLICATE KEY UPDATE titleName_fk='tn2', slotIndex=2, value=3;
1697 // But not in PostgreSQL
1698
1699 Read(command, databaseInterface);
1700 if (resultCode==L2RC_Client_PerTitleIntegerStorage_ROW_EMPTY)
1701 return Write(command, databaseInterface);
1702
1703 outputValue+=inputValue;
1704
1705 PGresult *result=0;
1706 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1707 switch (addConditionForOperation)
1708 {
1709 case PTISC_EQUAL:
1710 result = pgsql->QueryVariadic(
1711 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value=%g RETURNING value;",
1712 titleName.C_String(), slotIndex, command->callerUserId, outputValue, conditionValue);
1713 break;
1714 case PTISC_NOT_EQUAL:
1715 result = pgsql->QueryVariadic(
1716 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value!=%g RETURNING value;",
1717 titleName.C_String(), slotIndex, command->callerUserId, outputValue);
1718 break;
1719 case PTISC_GREATER_THAN:
1720 result = pgsql->QueryVariadic(
1721 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value<%g RETURNING value;",
1722 titleName.C_String(), slotIndex, command->callerUserId, outputValue, conditionValue);
1723 break;
1724 case PTISC_GREATER_OR_EQUAL:
1725 result = pgsql->QueryVariadic(
1726 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value<=%g RETURNING value;",
1727 titleName.C_String(), slotIndex, command->callerUserId, outputValue, conditionValue);
1728 break;
1729 case PTISC_LESS_THAN:
1730 result = pgsql->QueryVariadic(
1731 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value>%g RETURNING value;",
1732 titleName.C_String(), slotIndex, command->callerUserId, outputValue, conditionValue);
1733 break;
1734 case PTISC_LESS_OR_EQUAL:
1735 result = pgsql->QueryVariadic(
1736 "UPDATE lobby2.perTitlePerUserIntegerStorage SET value=%g WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i AND value>=%g RETURNING value;",
1737 titleName.C_String(), slotIndex, command->callerUserId, outputValue, conditionValue);
1738 break;
1739 }
1740
1741 int numRowsReturned = PQntuples(result);
1742 if (numRowsReturned==0)
1743 {
1744 PQclear(result);
1745 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1746 return true;
1747 }
1748 PostgreSQLInterface::PQGetValueFromBinary(&outputValue, result, 0, "value");
1749 PQclear(result);
1750
1751 resultCode=L2RC_SUCCESS;
1752 return true;
1753
1754 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1755 bool RakNet::Client_PerTitleIntegerStorage_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1756 {
1757 (void)command;
1758
1759 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1760 // Verify title name
1761 if (IsTitleInUse(titleName, pgsql)==false)
1762 {
1763 resultCode=L2RC_Client_PerTitleIntegerStorage_TITLE_NOT_IN_USE;
1764 return true;
1765 }
1766
1767 switch (operationToPerform)
1768 {
1769 case PTISO_WRITE:
1770 return Write(command,databaseInterface);
1771 break;
1772 case PTISO_READ:
1773 return Read(command,databaseInterface);
1774 break;
1775 case PTISO_DELETE:
1776 return Delete(command,databaseInterface);
1777 break;
1778 case PTISO_ADD:
1779 return Add(command,databaseInterface);
1780 break;
1781 }
1782
1783 return true;
1784 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1785 bool RakNet::Client_PerTitleBinaryStorage_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1786 {
1787 (void)command;
1788
1789 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1790 PGresult *result=0;
1791 // Verify title name
1792 if (IsTitleInUse(titleName, pgsql)==false)
1793 {
1794 resultCode=L2RC_Client_PerTitleBinaryStorage_TITLE_NOT_IN_USE;
1795 return true;
1796 }
1797
1798 int numRowsReturned;
1799 switch (operationToPerform)
1800 {
1801 case PTISO_WRITE:
1802 result = pgsql->QueryVariadic(
1803 "INSERT INTO lobby2.perTitlePerUserBinaryStorage (titleName_fk,slotIndex,userId_fk,binaryData) VALUES (%s,%i,%i,%a);",
1804 titleName.C_String(), slotIndex, command->callerUserId, binaryData->binaryData,
1805 binaryData->binaryDataLength);
1806
1807 if (result==0)
1808 {
1809 resultCode=L2RC_GENERAL_ERROR;
1810 return true;
1811 }
1812
1813 break;
1814 case PTISO_READ:
1815 result = pgsql->QueryVariadic(
1816 "SELECT binaryData FROM lobby2.perTitlePerUserBinaryStorage WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i;",
1817 titleName.C_String(), slotIndex, command->callerUserId);
1818
1819 if (result==0)
1820 {
1821 resultCode=L2RC_GENERAL_ERROR;
1822 return true;
1823 }
1824
1825 numRowsReturned = PQntuples(result);
1826 if (numRowsReturned==0)
1827 {
1828 resultCode=L2RC_Client_PerTitleBinaryStorage_ROW_EMPTY;
1829 return true;
1830 }
1831
1832 PostgreSQLInterface::PQGetValueFromBinary(&binaryData->binaryData, &binaryData->binaryDataLength, result, 0, "binaryData");
1833
1834 break;
1835 case PTISO_DELETE:
1836 result = pgsql->QueryVariadic("DELETE FROM lobby2.perTitlePerUserBinaryStorage WHERE titleName_fk=%s AND slotIndex=%i AND userId_fk=%i;",
1837 titleName.C_String(), slotIndex, command->callerUserId);
1838 break;
1839 }
1840
1841 PQclear(result);
1842
1843 resultCode=L2RC_SUCCESS;
1844 return true;
1845 }
ServerPreDBMemoryImpl(Lobby2Server * server,RakString userHandle)1846 bool RakNet::Client_SetPresence_PGSQL::ServerPreDBMemoryImpl( Lobby2Server *server, RakString userHandle )
1847 {
1848 server->SetPresence( presence, userHandle );
1849 resultCode=L2RC_SUCCESS;
1850 return true;
1851 }
ServerPreDBMemoryImpl(Lobby2Server * server,RakString userHandle)1852 bool RakNet::Client_GetPresence_PGSQL::ServerPreDBMemoryImpl( Lobby2Server *server, RakString userHandle )
1853 {
1854 server->GetPresence( presence, userHandle );
1855 resultCode=L2RC_SUCCESS;
1856 return true;
1857 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1858 bool RakNet::Client_StopIgnore_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1859 {
1860 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1861 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
1862 if (targetUserId==0)
1863 {
1864 resultCode=L2RC_Client_StopIgnore_UNKNOWN_TARGET_HANDLE;
1865 return true;
1866 }
1867
1868 if (targetUserId==command->callerUserId)
1869 {
1870 resultCode=L2RC_Client_StopIgnore_CANNOT_PERFORM_ON_SELF;
1871 return true;
1872 }
1873
1874 PGresult *result = pgsql->QueryVariadic(
1875 "DELETE FROM lobby2.ignore WHERE userMe_fk=%i AND userOther_fk=%i", command->callerUserId, targetUserId);
1876 PQclear(result);
1877
1878 Notification_Client_IgnoreStatus *notification = (Notification_Client_IgnoreStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Client_IgnoreStatus);
1879 notification->otherHandle=command->callingUserName;
1880 notification->nowIgnored=false;
1881 notification->resultCode=L2RC_SUCCESS;
1882 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
1883
1884 resultCode=L2RC_SUCCESS;
1885 return true;
1886 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1887 bool RakNet::Friends_SendInvite_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1888 {
1889 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1890 PGresult *result=0;
1891
1892 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
1893 if (targetUserId==0)
1894 {
1895 resultCode=L2RC_Friends_SendInvite_UNKNOWN_TARGET_HANDLE;
1896 return true;
1897 }
1898
1899 if (targetUserId==command->callerUserId)
1900 {
1901 resultCode=L2RC_Friends_SendInvite_CANNOT_PERFORM_ON_SELF;
1902 return true;
1903 }
1904
1905 // Don't do if already in friends table (already friends, or already has an invite)
1906 result = pgsql->QueryVariadic(
1907 "SELECT description FROM lobby2.friendActions WHERE actionId_pk="
1908 "(SELECT actionId_fk from lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i);"
1909 , command->callerUserId, targetUserId);
1910 if (result==0)
1911 {
1912 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1913 return true;
1914 }
1915
1916 int numRowsReturned = PQntuples(result);
1917 if (numRowsReturned!=0)
1918 {
1919 RakNet::RakString description;
1920 PostgreSQLInterface::PQGetValueFromBinary(&description, result, 0, "description");
1921 if (description=="sentInvite")
1922 resultCode=L2RC_Friends_SendInvite_ALREADY_SENT_INVITE;
1923 else if (description=="isFriends")
1924 resultCode=L2RC_Friends_SendInvite_ALREADY_FRIENDS;
1925 else
1926 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1927 PQclear(result);
1928 return true;
1929 }
1930 PQclear(result);
1931
1932 // Add friend invite
1933 result = pgsql->QueryVariadic(
1934 "INSERT INTO lobby2.friends (userOne_fk, userTwo_fk, actionId_fk) VALUES "
1935 "(%i, %i, (SELECT actionId_pk FROM lobby2.friendActions WHERE description='sentInvite'));"
1936 , command->callerUserId, targetUserId);
1937 if (result==0)
1938 {
1939 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1940 return true;
1941 }
1942 PQclear(result);
1943
1944 // Notify by email
1945 SendEmail(targetUserId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Friends_SendInvite", pgsql);
1946
1947 // Tell the other system the invitation was sent
1948 Notification_Friends_StatusChange *notification = (Notification_Friends_StatusChange *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_StatusChange);
1949 RakAssert(command->callingUserName.IsEmpty()==false);
1950 notification->otherHandle=command->callingUserName;
1951 notification->subject=subject;
1952 notification->body=body;
1953 notification->op=Notification_Friends_StatusChange::GOT_INVITATION_TO_BE_FRIENDS;
1954 notification->resultCode=L2RC_SUCCESS;
1955 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
1956
1957 resultCode=L2RC_SUCCESS;
1958 return true;
1959 }
1960
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)1961 bool RakNet::Friends_AcceptInvite_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
1962 {
1963 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
1964 PGresult *result=0;
1965
1966 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
1967 if (targetUserId==0)
1968 {
1969 resultCode=L2RC_Friends_AcceptInvite_UNKNOWN_TARGET_HANDLE;
1970 return true;
1971 }
1972
1973 if (targetUserId==command->callerUserId)
1974 {
1975 resultCode=L2RC_Friends_AcceptInvite_CANNOT_PERFORM_ON_SELF;
1976 return true;
1977 }
1978
1979 // Make sure we have an invite from the other user
1980 result = pgsql->QueryVariadic(
1981 "SELECT description FROM lobby2.friendActions WHERE actionId_pk="
1982 "(SELECT actionId_fk from lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i);"
1983 , targetUserId, command->callerUserId );
1984
1985 if (result==0)
1986 {
1987 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
1988 return true;
1989 }
1990
1991 int numRowsReturned = PQntuples(result);
1992 if (numRowsReturned!=0)
1993 {
1994 RakNet::RakString description;
1995 PostgreSQLInterface::PQGetValueFromBinary(&description, result, 0, "description");
1996 PQclear(result);
1997 if (description!=RakNet::RakString("sentInvite"))
1998 {
1999 resultCode=L2RC_Friends_AcceptInvite_NO_INVITE;
2000 return true;
2001 }
2002 }
2003 else
2004 {
2005 PQclear(result);
2006 resultCode=L2RC_Friends_AcceptInvite_NO_INVITE;
2007 return true;
2008 }
2009
2010 // Change from invited to friends, insert twice
2011 result = pgsql->QueryVariadic(
2012 "UPDATE lobby2.friends SET actionId_fk=(SELECT actionId_pk from lobby2.friendActions WHERE description='isFriends') WHERE userOne_fk=%i AND userTwo_fk=%i;"
2013 , targetUserId, command->callerUserId );
2014 RakAssert(result);
2015 PQclear(result);
2016
2017 // Delete any existing invites, etc. if there are any
2018 result = pgsql->QueryVariadic(
2019 "DELETE FROM lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i;"
2020 , command->callerUserId, targetUserId );
2021 PQclear(result);
2022
2023 // Insert as a friend
2024 result = pgsql->QueryVariadic(
2025 "INSERT INTO lobby2.friends (userOne_fk, userTwo_fk, actionId_fk) VALUES (%i, %i, (SELECT actionId_pk from lobby2.friendActions WHERE description='isFriends'));"
2026 ,command->callerUserId, targetUserId);
2027 RakAssert(result);
2028 PQclear(result);
2029
2030 SendEmail(targetUserId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Friends_AcceptInvite", (PostgreSQLInterface *) databaseInterface);
2031
2032 // Tell the other system the invitation was accepted
2033 Notification_Friends_StatusChange *notification = (Notification_Friends_StatusChange *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_StatusChange);
2034 RakAssert(command->callingUserName.IsEmpty()==false);
2035 notification->otherHandle=command->callingUserName;
2036 notification->subject=subject;
2037 notification->body=body;
2038 notification->op=Notification_Friends_StatusChange::THEY_ACCEPTED_OUR_INVITATION_TO_BE_FRIENDS;
2039 notification->resultCode=L2RC_SUCCESS;
2040 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
2041
2042 resultCode=L2RC_SUCCESS;
2043 return true;
2044 }
2045
ServerPostDBMemoryImpl(Lobby2Server * server,RakString userHandle)2046 void RakNet::Friends_AcceptInvite_PGSQL::ServerPostDBMemoryImpl( Lobby2Server *server, RakString userHandle )
2047 {
2048 (void)userHandle;
2049 server->GetPresence(presence,targetHandle);
2050 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2051 bool RakNet::Friends_RejectInvite_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2052 {
2053 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2054 PGresult *result=0;
2055
2056 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2057 if (targetUserId==0)
2058 {
2059 resultCode=L2RC_Friends_RejectInvite_UNKNOWN_TARGET_HANDLE;
2060 return true;
2061 }
2062
2063 if (targetUserId==command->callerUserId)
2064 {
2065 resultCode=L2RC_Friends_RejectInvite_CANNOT_PERFORM_ON_SELF;
2066 return true;
2067 }
2068
2069 // Make sure we have an invite from the other user
2070 result = pgsql->QueryVariadic(
2071 "SELECT description FROM lobby2.friendActions WHERE actionId_pk="
2072 "(SELECT actionId_fk from lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i);"
2073 , targetUserId, command->callerUserId );
2074
2075 if (result==0)
2076 {
2077 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2078 return true;
2079 }
2080
2081 int numRowsReturned = PQntuples(result);
2082 if (numRowsReturned!=0)
2083 {
2084 RakNet::RakString description;
2085 PostgreSQLInterface::PQGetValueFromBinary(&description, result, 0, "description");
2086 PQclear(result);
2087 if (description!=RakNet::RakString("sentInvite"))
2088 {
2089 resultCode=L2RC_Friends_RejectInvite_NO_INVITE;
2090 return true;
2091 }
2092 }
2093 else
2094 {
2095 PQclear(result);
2096 resultCode=L2RC_Friends_RejectInvite_NO_INVITE;
2097 return true;
2098 }
2099
2100 // Delete friend invite (both ways)
2101 result = pgsql->QueryVariadic(
2102 "DELETE FROM lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i;"
2103 , targetUserId, command->callerUserId );
2104 PQclear(result);
2105 result = pgsql->QueryVariadic(
2106 "DELETE FROM lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i;"
2107 , command->callerUserId, targetUserId );
2108 PQclear(result);
2109
2110 SendEmail(targetUserId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Friends_RejectInvite", (PostgreSQLInterface *) databaseInterface);
2111
2112 // Tell the other system the invitation was rejected
2113 Notification_Friends_StatusChange *notification = (Notification_Friends_StatusChange *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_StatusChange);
2114 RakAssert(command->callingUserName.IsEmpty()==false);
2115 notification->otherHandle=command->callingUserName;
2116 notification->subject=subject;
2117 notification->body=body;
2118 notification->op=Notification_Friends_StatusChange::THEY_REJECTED_OUR_INVITATION_TO_BE_FRIENDS;
2119 notification->resultCode=L2RC_SUCCESS;
2120 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
2121
2122 resultCode=L2RC_SUCCESS;
2123 return true;
2124 }
2125
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2126 bool RakNet::Friends_GetInvites_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2127 {
2128 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2129 GetFriendInfosByStatus(command->callerUserId, "sentInvite", pgsql, invitesSent, true);
2130 GetFriendInfosByStatus(command->callerUserId, "sentInvite", pgsql, invitesReceived, false);
2131 resultCode=L2RC_SUCCESS;
2132 return true;
2133 }
ServerPostDBMemoryImpl(Lobby2Server * server,RakString userHandle)2134 void RakNet::Friends_GetInvites_PGSQL::ServerPostDBMemoryImpl( Lobby2Server *server, RakString userHandle )
2135 {
2136 (void)userHandle;
2137
2138 for (unsigned int i=0; i < invitesSent.Size(); i++)
2139 server->GetUserOnlineStatus(invitesSent[i].usernameAndStatus);
2140 for (unsigned int i=0; i < invitesReceived.Size(); i++)
2141 server->GetUserOnlineStatus(invitesReceived[i].usernameAndStatus);
2142 }
2143
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2144 bool RakNet::Friends_GetFriends_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2145 {
2146 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2147 GetFriendInfosByStatus(command->callerUserId, "isFriends", pgsql, myFriends, true);
2148 resultCode=L2RC_SUCCESS;
2149 return true;
2150 }
ServerPostDBMemoryImpl(Lobby2Server * server,RakString userHandle)2151 void RakNet::Friends_GetFriends_PGSQL::ServerPostDBMemoryImpl( Lobby2Server *server, RakString userHandle )
2152 {
2153 (void)userHandle;
2154
2155 for (unsigned int i=0; i < myFriends.Size(); i++)
2156 server->GetUserOnlineStatus(myFriends[i].usernameAndStatus);
2157 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2158 bool RakNet::Friends_Remove_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2159 {
2160 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2161 PGresult *result=0;
2162 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2163 if (targetUserId==0)
2164 {
2165 resultCode=L2RC_Friends_Remove_UNKNOWN_TARGET_HANDLE;
2166 return true;
2167 }
2168
2169 if (targetUserId==command->callerUserId)
2170 {
2171 resultCode=L2RC_Friends_Remove_CANNOT_PERFORM_ON_SELF;
2172 return true;
2173 }
2174
2175 result = pgsql->QueryVariadic(
2176 "SELECT userOne_fk FROM lobby2.friends WHERE userOne_fk=%i AND userTwo_fk=%i;"
2177 , command->callerUserId, targetUserId );
2178 if (result==0)
2179 {
2180 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2181 return true;
2182 }
2183 int numRowsReturned = PQntuples(result);
2184 PQclear(result);
2185 if (numRowsReturned==0)
2186 {
2187 resultCode=L2RC_Friends_Remove_NOT_FRIENDS;
2188 return true;
2189 }
2190
2191 // Bidirectional delete
2192 result = pgsql->QueryVariadic("DELETE FROM lobby2.friends WHERE (userOne_fk=%i AND userTwo_fk=%i) OR (userOne_fk=%i AND userTwo_fk=%i)",
2193 command->callerUserId, targetUserId, targetUserId, command->callerUserId);
2194 RakAssert(result);
2195 PQclear(result);
2196
2197 SendEmail(targetUserId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Friends_Remove", (PostgreSQLInterface *) databaseInterface);
2198
2199 Notification_Friends_StatusChange *notification = (Notification_Friends_StatusChange *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_StatusChange);
2200 RakAssert(command->callingUserName.IsEmpty()==false);
2201 notification->otherHandle=command->callingUserName;
2202 notification->subject=subject;
2203 notification->body=body;
2204 notification->op=Notification_Friends_StatusChange::YOU_WERE_REMOVED_AS_A_FRIEND;
2205 notification->resultCode=L2RC_SUCCESS;
2206 command->server->AddOutputFromThread(notification, targetUserId, targetHandle);
2207
2208 resultCode=L2RC_SUCCESS;
2209 return true;
2210 }
2211
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2212 bool RakNet::BookmarkedUsers_Add_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2213 {
2214 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2215 PGresult *result=0;
2216 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2217 if (targetUserId==0)
2218 {
2219 resultCode=L2RC_BookmarkedUsers_Add_UNKNOWN_TARGET_HANDLE;
2220 return true;
2221 }
2222
2223 if (targetUserId==command->callerUserId)
2224 {
2225 resultCode=L2RC_BookmarkedUsers_Add_CANNOT_PERFORM_ON_SELF;
2226 return true;
2227 }
2228
2229 result = pgsql->QueryVariadic(
2230 "INSERT INTO lobby2.bookmarkedUsers (userMe_fk, userOther_fk, type, description) VALUES (%i, %i, %i, %s)",
2231 command->callerUserId, targetUserId, type, description.C_String() );
2232 if (result==0)
2233 {
2234 resultCode=L2RC_BookmarkedUsers_Add_ALREADY_BOOKMARKED;
2235 return true;
2236 }
2237 PQclear(result);
2238
2239 resultCode=L2RC_SUCCESS;
2240 return true;
2241 }
2242
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2243 bool RakNet::BookmarkedUsers_Remove_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2244 {
2245 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2246 PGresult *result=0;
2247 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2248 if (targetUserId==0)
2249 {
2250 resultCode=L2RC_BookmarkedUsers_Remove_UNKNOWN_TARGET_HANDLE;
2251 return true;
2252 }
2253
2254 if (targetUserId==command->callerUserId)
2255 {
2256 resultCode=L2RC_BookmarkedUsers_Remove_CANNOT_PERFORM_ON_SELF;
2257 return true;
2258 }
2259
2260 result = pgsql->QueryVariadic(
2261 "DELETE FROM lobby2.bookmarkedUsers WHERE userOther_fk=%i AND type=%i",
2262 targetUserId, type);
2263 if (result==0)
2264 {
2265 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2266 return true;
2267 }
2268 PQclear(result);
2269
2270 resultCode=L2RC_SUCCESS;
2271 return true;
2272 }
2273
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2274 bool RakNet::BookmarkedUsers_Get_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2275 {
2276 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2277 PGresult *result=0;
2278 result = pgsql->QueryVariadic(
2279 "SELECT (SELECT handle FROM lobby2.users where userId_pk=userOther_fk) as handle, *"
2280 "FROM (SELECT userOther_fk, type, description, creationDate from lobby2.bookmarkedUsers WHERE userMe_fk=%i) as bm ORDER BY type, creationDate ASC;",
2281 command->callerUserId);
2282
2283 if (result==0)
2284 {
2285 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2286 return true;
2287 }
2288 int numRowsReturned = PQntuples(result);
2289 int i;
2290 BookmarkedUser bm;
2291 for (i=0; i < numRowsReturned; i++)
2292 {
2293 PostgreSQLInterface::PQGetValueFromBinary(&bm.targetHandle, result, i, "handle");
2294 PostgreSQLInterface::PQGetValueFromBinary(&bm.type, result, i, "type");
2295 PostgreSQLInterface::PQGetValueFromBinary(&bm.description, result, i, "description");
2296 PostgreSQLInterface::PQGetValueFromBinary(&bm.dateWhenAdded, result, i, "creationDate");
2297 bookmarkedUsers.Insert(bm, __FILE__, __LINE__ );
2298 }
2299
2300 PQclear(result);
2301 resultCode=L2RC_SUCCESS;
2302 return true;
2303 }
2304
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2305 bool RakNet::Emails_Send_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2306 {
2307 SendEmail(recipients, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, status, "Emails_Send", (PostgreSQLInterface *) databaseInterface);
2308 resultCode=L2RC_SUCCESS;
2309 return true;
2310 }
2311
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2312 bool RakNet::Emails_Get_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2313 {
2314 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2315 PGresult *result;
2316
2317 if (unreadEmailsOnly==true)
2318 {
2319 if (emailIdsOnly)
2320 {
2321 result = pgsql->QueryVariadic(
2322 "SELECT tbl2.emailid_fk from lobby2.users, ("
2323 "SELECT tbl1.*, lobby2.emails.creationDate FROM"
2324 "(SELECT emailId_fk, userMe_fk, userOther_fk, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails"
2325 "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE AND tbl1.wasRead=FALSE"
2326 ") as tbl2"
2327 "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
2328 , command->callerUserId);
2329 }
2330 else
2331 {
2332 result = pgsql->QueryVariadic(
2333 "SELECT handle, tbl2.* from lobby2.users, ("
2334 "SELECT tbl1.*, lobby2.emails.subject, lobby2.emails.body, lobby2.emails.binaryData, lobby2.emails.creationDate FROM"
2335 "(SELECT emailId_fk, emailTarget_pk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
2336 "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE AND tbl1.wasRead=FALSE"
2337 ") as tbl2 "
2338 "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
2339 , command->callerUserId);
2340 }
2341 }
2342 else
2343 {
2344 if (emailIdsOnly)
2345 {
2346 result = pgsql->QueryVariadic(
2347 "SELECT tbl2.emailid_fk from lobby2.users, ("
2348 "SELECT tbl1.*, lobby2.emails.creationDate FROM"
2349 "(SELECT emailId_fk, userMe_fk, userOther_fk, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails"
2350 "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE"
2351 ") as tbl2"
2352 "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
2353 , command->callerUserId);
2354 }
2355 else
2356 {
2357 result = pgsql->QueryVariadic(
2358 "SELECT handle, tbl2.* from lobby2.users, ("
2359 "SELECT tbl1.*, lobby2.emails.subject, lobby2.emails.body, lobby2.emails.binaryData, lobby2.emails.creationDate FROM"
2360 "(SELECT emailId_fk, emailTarget_pk, userMe_fk, userOther_fk, status, wasRead, ISentThisEmail, isDeleted FROM lobby2.emailTargets) as tbl1, lobby2.emails "
2361 "WHERE tbl1.emailId_fk=lobby2.emails.emailId_pk AND tbl1.userMe_fk=%i AND tbl1.isDeleted=FALSE"
2362 ") as tbl2 "
2363 "WHERE userId_pk=tbl2.userother_fk ORDER BY creationDate ASC;"
2364 , command->callerUserId);
2365 }
2366 }
2367
2368
2369 if (result==0)
2370 {
2371 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2372 return true;
2373 }
2374 int numRowsReturned = PQntuples(result);
2375 int i;
2376 EmailResult emailResult;
2377 for (i=0; i < numRowsReturned; i++)
2378 {
2379 RakNet::RakString otherHandle;
2380 RakNet::RakString myHandle = command->callingUserName;
2381 // 11/4/2010 - I think this was a copy/paste error
2382 // PostgreSQLInterface::PQGetValueFromBinary(&emailResult.emailID, result, i, "emailTarget_pk");
2383 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.emailID, result, i, "emailId_fk");
2384 bool getThisEmail;
2385 if (emailsToRetrieve.Size()>0)
2386 {
2387 getThisEmail=false;
2388 for (unsigned int i=0; i < emailsToRetrieve.Size(); i++)
2389 {
2390 if (emailsToRetrieve[i]==emailResult.emailID)
2391 {
2392 getThisEmail=true;
2393 break;
2394 }
2395 }
2396 }
2397 else
2398 getThisEmail=true;
2399 if (getThisEmail)
2400 {
2401 if (emailIdsOnly==false)
2402 {
2403 PostgreSQLInterface::PQGetValueFromBinary(&otherHandle, result, i, "handle");
2404 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.status, result, i, "status");
2405 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.wasReadByMe, result, i, "wasRead");
2406 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.wasSendByMe, result, i, "ISentThisEmail");
2407 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.subject, result, i, "subject");
2408 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.body, result, i, "body");
2409 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.binaryData->binaryData, &emailResult.binaryData->binaryDataLength, result, i, "binaryData");
2410 PostgreSQLInterface::PQGetValueFromBinary(&emailResult.creationDate, result, i, "creationDate");
2411 if (emailResult.wasSendByMe)
2412 {
2413 emailResult.sender=myHandle;
2414 emailResult.recipient=otherHandle;
2415 }
2416 else
2417 {
2418 emailResult.sender=otherHandle;
2419 emailResult.recipient=myHandle;
2420 }
2421 }
2422 emailResults.Insert(emailResult, __FILE__, __LINE__ );
2423 }
2424
2425 }
2426
2427 resultCode=L2RC_SUCCESS;
2428 PQclear(result);
2429 return true;
2430 }
2431
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2432 bool RakNet::Emails_Delete_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2433 {
2434 (void)command;
2435
2436 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2437 PGresult *result = pgsql->QueryVariadic("SELECT isDeleted FROM lobby2.emailTargets WHERE emailTarget_pk = %i", emailId);
2438 if (result==0)
2439 {
2440 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2441 return true;
2442 }
2443 int numRowsReturned = PQntuples(result);
2444 if (numRowsReturned==0)
2445 {
2446 PQclear(result);
2447 resultCode=L2RC_Emails_Delete_UNKNOWN_EMAIL_ID;
2448 return true;
2449 }
2450 bool isDeleted;
2451 PostgreSQLInterface::PQGetValueFromBinary(&isDeleted, result, 0, "isDeleted");
2452 PQclear(result);
2453 if (isDeleted)
2454 {
2455 resultCode=L2RC_Emails_Delete_ALREADY_DELETED;
2456 return true;
2457 }
2458 // Don't actually delete, just flag as deleted. This is so the admin can investigate reports of abuse.
2459 result = pgsql->QueryVariadic("UPDATE lobby2.emailTargets SET isDeleted=TRUE WHERE emailTarget_pk = %i", emailId);
2460 if (result==0)
2461 {
2462 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2463 return true;
2464 }
2465
2466 PQclear(result);
2467 resultCode=L2RC_SUCCESS;
2468 return true;
2469 }
2470
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2471 bool RakNet::Emails_SetStatus_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2472 {
2473 (void)command;
2474
2475 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2476 PGresult *result = pgsql->QueryVariadic("SELECT isDeleted FROM lobby2.emailTargets WHERE emailTarget_pk = %i", emailId);
2477 if (result==0)
2478 {
2479 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2480 return true;
2481 }
2482 int numRowsReturned = PQntuples(result);
2483 if (numRowsReturned==0)
2484 {
2485 PQclear(result);
2486 resultCode=L2RC_Emails_SetStatus_UNKNOWN_EMAIL_ID;
2487 return true;
2488 }
2489 bool isDeleted;
2490 PostgreSQLInterface::PQGetValueFromBinary(&isDeleted, result, 0, "isDeleted");
2491 PQclear(result);
2492 if (isDeleted)
2493 {
2494 resultCode=L2RC_Emails_SetStatus_WAS_DELETED;
2495 return true;
2496 }
2497 if (updateStatusFlag)
2498 {
2499 result = pgsql->QueryVariadic("UPDATE lobby2.emailTargets SET status=%i WHERE emailTarget_pk = %i", newStatusFlag, emailId);
2500 if (result==0)
2501 {
2502 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2503 return true;
2504 }
2505 }
2506 PQclear(result);
2507 if (updateMarkedRead)
2508 {
2509 result = pgsql->QueryVariadic("UPDATE lobby2.emailTargets SET wasRead=%b WHERE emailTarget_pk = %i", isNowMarkedRead, emailId);
2510 if (result==0)
2511 {
2512 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2513 return true;
2514 }
2515 }
2516 PQclear(result);
2517
2518
2519 resultCode=L2RC_SUCCESS;
2520 return true;
2521 }
2522
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2523 bool RakNet::Ranking_SubmitMatch_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2524 {
2525 (void)command;
2526
2527 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2528 PGresult *result=0;
2529 // Verify title name
2530 if (IsTitleInUse(titleName, pgsql)==false)
2531 {
2532 resultCode=L2RC_Ranking_SubmitMatch_TITLE_NOT_IN_USE;
2533 return true;
2534 }
2535
2536 // Insert
2537 result = pgsql->QueryVariadic("INSERT INTO lobby2.matches (gameTypeName, titleName_fk, matchNote, binaryData) VALUES "
2538 "(%s, %s, %s, %a) RETURNING matchId_pk;",
2539 gameType.C_String(), titleName.C_String(), submittedMatch.matchNote.C_String(), submittedMatch.binaryData->binaryData, submittedMatch.binaryData->binaryDataLength );
2540
2541 int numRowsReturned = PQntuples(result);
2542 if (numRowsReturned==0)
2543 {
2544 PQclear(result);
2545 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2546 return true;
2547 }
2548 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.matchID, result, 0, "matchId_pk");
2549 PQclear(result);
2550
2551 // For each match participant, add to lobby2.matchParticipants
2552 unsigned int i;
2553 for (i=0; i < submittedMatch.matchParticipants.Size(); i++)
2554 {
2555 result = pgsql->QueryVariadic("INSERT INTO lobby2.matchParticipants (matchId_fk, userId_fk, score) VALUES "
2556 "(%i, (SELECT userId_pk FROM lobby2.users WHERE handleLower=lower(%s)), %f);",
2557 submittedMatch.matchID, submittedMatch.matchParticipants[i].handle.C_String(), submittedMatch.matchParticipants[i].score);
2558 // May fail if a user is deleted at the same time this is running
2559 if (result)
2560 PQclear(result);
2561 }
2562
2563 result = pgsql->QueryVariadic("SELECT EXTRACT(EPOCH FROM now()) as whenSubmittedDate;");
2564 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.whenSubmittedDate, result, 0, "whenSubmittedDate");
2565 PQclear(result);
2566
2567 resultCode=L2RC_SUCCESS;
2568 return true;
2569 }
2570
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2571 bool RakNet::Ranking_GetMatches_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2572 {
2573 (void)command;
2574
2575 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2576 PGresult *result1, *result2;
2577
2578 // Verify title name
2579 if (IsTitleInUse(titleName, pgsql)==false)
2580 {
2581 resultCode=L2RC_Ranking_SubmitMatch_TITLE_NOT_IN_USE;
2582 return true;
2583 }
2584
2585 result1 = pgsql->QueryVariadic("SELECT matchId_pk, matchNote, binaryData, EXTRACT(EPOCH FROM creationDate) as creationDate from lobby2.matches WHERE gameTypeName=%s AND titleName_fk=%s;", gameType.C_String(), titleName.C_String());
2586 if (result1==0)
2587 {
2588 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2589 return true;
2590 }
2591 int numMatchesReturned = PQntuples(result1);
2592 int i;
2593 SubmittedMatch submittedMatch;
2594 MatchParticipant matchParticipant;
2595 for (i=0; i < numMatchesReturned; i++)
2596 {
2597 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.matchID, result1, i, "matchId_pk");
2598 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.matchNote, result1, i, "matchNote");
2599 // PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.binaryData->binaryData, &submittedMatch.binaryData->binaryDataLength, result1, i, "binaryData");
2600 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.whenSubmittedDate, result1, i, "creationDate");
2601 PostgreSQLInterface::PQGetValueFromBinary(&submittedMatch.matchID, result1, i, "matchId_pk");
2602
2603 result2=pgsql->QueryVariadic("SELECT (SELECT handle FROM lobby2.users where userId_pk=userId_fk) as handle, score from lobby2.matchParticipants where matchId_fk=%i;", submittedMatch.matchID);
2604 int numParticipants = PQntuples(result2);
2605 for (int j=0; j < numParticipants; j++)
2606 {
2607 PostgreSQLInterface::PQGetValueFromBinary(&matchParticipant.handle, result2, j, "handle");
2608 PostgreSQLInterface::PQGetValueFromBinary(&matchParticipant.score, result2, j, "score");
2609 submittedMatch.matchParticipants.Insert(matchParticipant, __FILE__, __LINE__ );
2610 }
2611
2612 PQclear(result2);
2613
2614 submittedMatches.Insert(submittedMatch, __FILE__, __LINE__ );
2615 }
2616 PQclear(result1);
2617
2618 resultCode=L2RC_SUCCESS;
2619 return true;
2620 }
2621
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2622 bool RakNet::Ranking_GetMatchBinaryData_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2623 {
2624 (void)command;
2625
2626 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2627 PGresult *result=0;
2628 result = pgsql->QueryVariadic("SELECT binaryData from lobby2.matches WHERE matchId_pk=%i", matchID);
2629 if (result==0)
2630 {
2631 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2632 return true;
2633 }
2634 int numRowsReturned = PQntuples(result);
2635 if (numRowsReturned==0)
2636 {
2637 PQclear(result);
2638 resultCode=L2RC_Ranking_GetMatchBinaryData_INVALID_MATCH_ID;
2639 return false;
2640 }
2641
2642 PostgreSQLInterface::PQGetValueFromBinary(&binaryData->binaryData, &binaryData->binaryDataLength, result, 0, "binaryData");
2643 PQclear(result);
2644 resultCode=L2RC_SUCCESS;
2645 return true;
2646 }
2647
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2648 bool RakNet::Ranking_GetTotalScore_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2649 {
2650 (void)command;
2651
2652 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2653 PGresult *result=0;
2654
2655 // Verify title name
2656 if (IsTitleInUse(titleName, pgsql)==false)
2657 {
2658 resultCode=L2RC_Ranking_GetTotalScore_TITLE_NOT_IN_USE;
2659 return true;
2660 }
2661
2662 unsigned int userRow = GetUserRowFromHandle(targetHandle, pgsql);
2663 if (userRow==0)
2664 {
2665 resultCode=L2RC_UNKNOWN_USER;
2666 return true;
2667 }
2668
2669 result = pgsql->QueryVariadic("SELECT COUNT(score) as count, sum(score) as sum from lobby2.matchParticipants WHERE userId_fk=%i AND matchId_fk IN"
2670 "(SELECT matchId_pk from lobby2.matches WHERE gameTypeName=%s AND titleName_fk=%s);", userRow, gameType.C_String(), titleName.C_String());
2671
2672 if (result==0)
2673 {
2674 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2675 return true;
2676 }
2677
2678 int64_t count;
2679 PostgreSQLInterface::PQGetValueFromBinary(&count, result, 0, "count");
2680 numScoresSubmitted = (unsigned int) count;
2681 if (numScoresSubmitted>0)
2682 PostgreSQLInterface::PQGetValueFromBinary(&scoreSum, result, 0, "sum");
2683 else
2684 scoreSum=0.0f;
2685 PQclear(result);
2686 resultCode=L2RC_SUCCESS;
2687 return true;
2688 }
2689
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2690 bool RakNet::Ranking_WipeScoresForPlayer_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2691 {
2692 (void)command;
2693
2694 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2695 PGresult *result=0;
2696
2697 // Verify title name
2698 if (IsTitleInUse(titleName, pgsql)==false)
2699 {
2700 resultCode=L2RC_Ranking_WipeScoresForPlayer_TITLE_NOT_IN_USE;
2701 return true;
2702 }
2703
2704 unsigned int userRow = GetUserRowFromHandle(targetHandle, pgsql);
2705 if (userRow==0)
2706 {
2707 resultCode=L2RC_UNKNOWN_USER;
2708 return true;
2709 }
2710
2711 result = pgsql->QueryVariadic("DELETE FROM lobby2.matchParticipants WHERE userId_fk=%i AND matchId_fk IN"
2712 "(SELECT matchId_pk from lobby2.matches WHERE gameTypeName=%s AND titleName_fk=%s);", userRow, gameType.C_String(), titleName.C_String());
2713
2714 if (result==0)
2715 {
2716 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2717 return true;
2718 }
2719
2720 resultCode=L2RC_SUCCESS;
2721 return true;
2722 }
2723
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2724 bool RakNet::Ranking_WipeMatches_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2725 {
2726 (void)command;
2727
2728 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2729 PGresult *result=0;
2730
2731 // Verify title name
2732 if (IsTitleInUse(titleName, pgsql)==false)
2733 {
2734 resultCode=L2RC_Ranking_WipeMatches_TITLE_NOT_IN_USE;
2735 return true;
2736 }
2737
2738 result = pgsql->QueryVariadic("DELETE FROM lobby2.matches WHERE gameTypeName=%s AND titleName_fk=%s;", gameType.C_String(), titleName.C_String());
2739
2740 if (result==0)
2741 {
2742 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2743 return true;
2744 }
2745
2746 resultCode=L2RC_SUCCESS;
2747 return true;
2748 }
2749
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2750 bool RakNet::Ranking_PruneMatches_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2751 {
2752 (void)command;
2753
2754 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2755 PGresult *result=0;
2756 result = pgsql->QueryVariadic("DELETE FROM lobby2.matches WHERE creationDate < (select now() - %i * interval '1 day')", pruneTimeDays);
2757
2758 if (result==0)
2759 {
2760 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2761 return true;
2762 }
2763
2764 resultCode=L2RC_SUCCESS;
2765 return true;
2766 }
2767
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2768 bool RakNet::Ranking_UpdateRating_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2769 {
2770 (void)command;
2771
2772 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2773 PGresult *result=0;
2774
2775 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
2776 {
2777 resultCode=L2RC_Ranking_UpdateRating_TITLE_NOT_IN_USE;
2778 return true;
2779 }
2780
2781 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2782 if (targetUserId==0)
2783 {
2784 resultCode=L2RC_Ranking_UpdateRating_UNKNOWN_TARGET_HANDLE;
2785 return true;
2786 }
2787
2788 result = pgsql->QueryVariadic("INSERT INTO lobby2.ratings (userId_fk, gameTypeName, titleName_fk, userRating) VALUES (%i, %s, %s, %f)", targetUserId, gameType.C_String(), titleName.C_String(), targetRating);
2789
2790 if (result==0)
2791 {
2792 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2793 return true;
2794 }
2795
2796 resultCode=L2RC_SUCCESS;
2797 return true;
2798 }
2799
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2800 bool RakNet::Ranking_WipeRatings_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2801 {
2802 (void)command;
2803
2804 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2805 PGresult *result=0;
2806
2807 // Verify title name
2808 if (IsTitleInUse(titleName, pgsql)==false)
2809 {
2810 resultCode=L2RC_Ranking_WipeRatings_TITLE_NOT_IN_USE;
2811 return true;
2812 }
2813
2814 result = pgsql->QueryVariadic("DELETE FROM lobby2.ratings WHERE gameTypeName=%s AND titleName_fk=%s;", gameType.C_String(), titleName.C_String());
2815
2816 if (result==0)
2817 {
2818 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2819 return true;
2820 }
2821
2822 resultCode=L2RC_SUCCESS;
2823 return true;
2824 }
2825
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2826 bool RakNet::Ranking_GetRating_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2827 {
2828 (void)command;
2829
2830 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2831 PGresult *result=0;
2832
2833 if (RakNet::IsTitleInUse(titleName, pgsql)==false)
2834 {
2835 resultCode=L2RC_Ranking_GetRating_TITLE_NOT_IN_USE;
2836 return true;
2837 }
2838
2839 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
2840 if (targetUserId==0)
2841 {
2842 resultCode=L2RC_Ranking_GetRating_UNKNOWN_TARGET_HANDLE;
2843 return true;
2844 }
2845
2846 result = pgsql->QueryVariadic("SELECT userRating, creationDate FROM lobby2.ratings WHERE gameTypeName=%s AND titleName_fk=%s and userId_fk=%i ORDER by creationDate LIMIT 1;", gameType.C_String(), titleName.C_String(), targetUserId);
2847 if (result==0)
2848 {
2849 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2850 return true;
2851 }
2852
2853
2854 int numRowsReturned = PQntuples(result);
2855 if (numRowsReturned==0)
2856 {
2857 PQclear(result);
2858 resultCode=L2RC_Ranking_GetRating_NO_RATING;
2859 return true;
2860 }
2861
2862 PostgreSQLInterface::PQGetValueFromBinary(¤tRating, result, 0, "userRating");
2863 PQclear(result);
2864
2865 resultCode=L2RC_SUCCESS;
2866 return true;
2867 }
2868
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2869 bool RakNet::Clans_Create_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2870 {
2871 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2872 PGresult *result=0;
2873
2874 if (StringContainsProfanity(clanHandle, pgsql))
2875 {
2876 resultCode=L2RC_PROFANITY_FILTER_CHECK_FAILED;
2877 return true;
2878 }
2879
2880 if (GetClanIdFromHandle(clanHandle, pgsql))
2881 {
2882 resultCode=L2RC_Clans_Create_CLAN_HANDLE_IN_USE;
2883 return true;
2884 }
2885
2886 result = pgsql->QueryVariadic(
2887 "INSERT INTO lobby2.clans (leaderUserId_fk, clanHandle, requiresInvitationsToJoin, description, binaryData) VALUES "
2888 "(%i, %s, %b, %s, %a) RETURNING clanId_pk;", command->callerUserId, clanHandle.C_String(), requiresInvitationsToJoin, description.C_String(), binaryData->binaryData, binaryData->binaryDataLength );
2889
2890 if (result==0)
2891 {
2892 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2893 return true;
2894 }
2895 unsigned int clanId_pk;
2896 PostgreSQLInterface::PQGetValueFromBinary(&clanId_pk, result, 0, "clanId_pk");
2897 PQclear(result);
2898
2899 if (failIfAlreadyInClan)
2900 {
2901 // Checking after rather than before in case this user creates a clan in another thread at the same time
2902 result = pgsql->QueryVariadic("SELECT COUNT(*) as count from lobby2.clans WHERE leaderUserId_fk=%i", command->callerUserId);
2903 long long count;
2904 PostgreSQLInterface::PQGetValueFromBinary(&count, result, 0, "count");
2905 PQclear(result);
2906 if (count>1)
2907 {
2908 result = pgsql->QueryVariadic("DELETE FROM lobby2.clans WHERE (clanId_pk=%i);", clanId_pk);
2909 PQclear(result);
2910 resultCode=L2RC_Clans_Create_ALREADY_IN_A_CLAN;
2911 return true;
2912 }
2913 }
2914
2915 // Add yourself as a clan member
2916 result = pgsql->QueryVariadic(
2917 "INSERT INTO lobby2.clanMembers (userId_fk, clanId_fk, isSubleader, memberState_fk) VALUES "
2918 "(%i, %i, false, (SELECT stateId_Pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active'));", command->callerUserId, clanId_pk );
2919
2920 if (result==0)
2921 {
2922 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2923 return true;
2924 }
2925 PQclear(result);
2926
2927 // Tell all friends about this new clan
2928 DataStructures::List<unsigned int> output;
2929 GetFriendIDs(command->callerUserId, true, pgsql, output);
2930
2931 unsigned int idx;
2932 for (idx=0; idx < output.Size(); idx++)
2933 {
2934 Notification_Friends_CreatedClan *notification = (Notification_Friends_CreatedClan *)command->server->GetMessageFactory()->Alloc(L2MID_Notification_Friends_CreatedClan);
2935 RakAssert(command->callingUserName.IsEmpty()==false);
2936 notification->otherHandle=command->callingUserName;
2937 notification->clanName=clanHandle;
2938 notification->resultCode=L2RC_SUCCESS;
2939 command->server->AddOutputFromThread(notification, output[idx], "");
2940 }
2941
2942 resultCode=L2RC_SUCCESS;
2943 return true;
2944 }
2945
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2946 bool RakNet::Clans_SetProperties_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2947 {
2948 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2949 PGresult *result=0;
2950
2951 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
2952 if (clanId==0)
2953 {
2954 resultCode=L2RC_Clans_SetProperties_UNKNOWN_CLAN;
2955 return true;
2956 }
2957 if (IsClanLeader(clanId, command->callerUserId, pgsql)==false)
2958 {
2959 resultCode=L2RC_Clans_SetProperties_MUST_BE_LEADER;
2960 return true;
2961 }
2962
2963 result = pgsql->QueryVariadic("UPDATE lobby2.clans SET description=%s, binaryData=%a WHERE clanId_pk=%i", description.C_String(), binaryData->binaryData, binaryData->binaryDataLength, clanId);
2964
2965 if (result==0)
2966 {
2967 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2968 return true;
2969 }
2970 PQclear(result);
2971
2972 resultCode=L2RC_SUCCESS;
2973 return true;
2974 }
2975
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)2976 bool RakNet::Clans_GetProperties_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
2977 {
2978 (void)command;
2979
2980 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
2981 PGresult *result=0;
2982
2983 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
2984 if (clanId==0)
2985 {
2986 resultCode=L2RC_Clans_GetProperties_UNKNOWN_CLAN;
2987 return true;
2988 }
2989
2990 result = pgsql->QueryVariadic("SELECT description, binaryData FROM lobby2.clans WHERE clanId_pk=%i", clanId);
2991
2992 if (result==0)
2993 {
2994 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
2995 return true;
2996 }
2997 PostgreSQLInterface::PQGetValueFromBinary(&description, result, 0, "description");
2998 PostgreSQLInterface::PQGetValueFromBinary(&binaryData->binaryData, &binaryData->binaryDataLength, result, 0, "binaryData");
2999 PQclear(result);
3000
3001 resultCode=L2RC_SUCCESS;
3002 return true;
3003 }
3004
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3005 bool RakNet::Clans_SetMyMemberProperties_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3006 {
3007 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3008 PGresult *result=0;
3009
3010 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3011 if (clanId==0)
3012 {
3013 resultCode=L2RC_Clans_SetMyMemberProperties_UNKNOWN_CLAN;
3014 return true;
3015 }
3016
3017 bool isSubleader;
3018 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3019 if (clanMemberState!=CMD_ACTIVE)
3020 {
3021 resultCode=L2RC_Clans_SetMyMemberProperties_NOT_IN_CLAN;
3022 return true;
3023 }
3024
3025 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET description=%s, binaryData=%a WHERE userId_fk=%i AND clanId_fk=%i", description.C_String(), binaryData->binaryData, binaryData->binaryDataLength, command->callerUserId, clanId);
3026
3027 if (result==0)
3028 {
3029 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3030 return true;
3031 }
3032 PQclear(result);
3033
3034 resultCode=L2RC_SUCCESS;
3035 return true;
3036 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3037 bool RakNet::Clans_Get_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3038 {
3039 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3040
3041 // Gets all clans that I am in, as active
3042 PGresult *result = pgsql->QueryVariadic(
3043 "SELECT U.handle, C.clanHandle, C.description, C.binaryData, C.clanId_fk, C.leaderUserId_fk "
3044 "FROM lobby2.users AS U, (SELECT clanHandle, description, binaryData, leaderUserId_fk, myClans.* FROM lobby2.clans, "
3045 "(SELECT clanId_fk FROM lobby2.clanMembers WHERE userId_fk=%i "
3046 "AND memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active')) AS myClans "
3047 "WHERE clanId_pk=myClans.clanId_fk) AS C WHERE U.userId_pk=C.leaderUserId_fk;", command->callerUserId);
3048
3049 if (result==0)
3050 {
3051 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3052 return true;
3053 }
3054
3055 int numRowsReturned = PQntuples(result);
3056 if (numRowsReturned==0)
3057 {
3058 PQclear(result);
3059 resultCode=L2RC_SUCCESS;
3060 return true;
3061 }
3062
3063 ClanInfo ci;
3064 for (int i=0; i < numRowsReturned; i++)
3065 {
3066 unsigned int clanId;
3067 int leaderId;
3068 PostgreSQLInterface::PQGetValueFromBinary(&ci.clanName, result, i, "clanHandle");
3069 PostgreSQLInterface::PQGetValueFromBinary(&ci.description, result, i, "description");
3070 PostgreSQLInterface::PQGetValueFromBinary(&ci.clanLeader, result, i, "handle");
3071 PostgreSQLInterface::PQGetValueFromBinary(&ci.binaryData->binaryData, &ci.binaryData->binaryDataLength, result, i, "binaryData");
3072 PostgreSQLInterface::PQGetValueFromBinary(&clanId, result, i, "clanId_fk");
3073 PostgreSQLInterface::PQGetValueFromBinary(&leaderId, result, i, "leaderUserId_fk");
3074
3075 // Get the names of all other active members in this clan
3076 PGresult *result2 = pgsql->QueryVariadic(
3077 "SELECT U.handle FROM lobby2.clanMembers AS M, lobby2.users AS U "
3078 "WHERE M.clanId_fk=%i AND M.userId_fk!=%i AND U.userId_pk=M.userId_fk "
3079 "AND M.memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active');",
3080 clanId, leaderId);
3081
3082 int numRowsReturned2 = PQntuples(result2);
3083 RakNet::RakString memberHandle;
3084 for (int j=0; j < numRowsReturned2; j++)
3085 {
3086 PostgreSQLInterface::PQGetValueFromBinary(&memberHandle, result2, j, "handle");
3087 ci.clanMembersOtherThanLeader.Insert(memberHandle, __FILE__, __LINE__ );
3088 }
3089 PQclear(result2);
3090
3091 clans.Insert(ci, __FILE__, __LINE__ );
3092 }
3093
3094 PQclear(result);
3095
3096
3097 resultCode=L2RC_SUCCESS;
3098 return true;
3099
3100 };
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3101 bool RakNet::Clans_GrantLeader_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3102 {
3103 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3104 PGresult *result=0;
3105
3106 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3107 if (clanId==0)
3108 {
3109 resultCode=L2RC_Clans_GrantLeader_UNKNOWN_CLAN;
3110 return true;
3111 }
3112
3113 if (IsClanLeader(clanId, command->callerUserId, pgsql)==false)
3114 {
3115 resultCode=L2RC_Clans_GrantLeader_MUST_BE_LEADER;
3116 return true;
3117 }
3118
3119 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
3120 if (targetUserId==0)
3121 {
3122 resultCode=L2RC_Clans_GrantLeader_UNKNOWN_TARGET_HANDLE;
3123 return true;
3124 }
3125
3126 if (targetUserId==command->callerUserId)
3127 {
3128 resultCode=L2RC_Clans_GrantLeader_CANNOT_PERFORM_ON_SELF;
3129 return true;
3130 }
3131
3132 bool isSubleader;
3133 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetUserId, &isSubleader, pgsql);
3134 if (clanMemberState!=CMD_ACTIVE)
3135 {
3136 resultCode=L2RC_Clans_GrantLeader_TARGET_NOT_IN_CLAN;
3137 return true;
3138 }
3139
3140 result = pgsql->QueryVariadic("UPDATE lobby2.clans SET leaderUserId_fk=%i WHERE clanId_pk=%i;",targetUserId, clanId);
3141
3142 if (result==0)
3143 {
3144 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3145 return true;
3146 }
3147 PQclear(result);
3148 resultCode=L2RC_SUCCESS;
3149
3150 Notification_Clans_GrantLeader *notification;
3151 // Tell all clan members of the new leader
3152 DataStructures::List<ClanMemberDescriptor> clanMembers;
3153 GetClanMembers(clanId, clanMembers, pgsql);
3154
3155 // Tell all clan members, except the command originator, about the new clan leader
3156 for (unsigned int i=0; i < clanMembers.Size(); i++)
3157 {
3158 if (clanMembers[i].memberState!=CMD_ACTIVE)
3159 continue;
3160 if (clanMembers[i].userId==command->callerUserId)
3161 continue;
3162 notification = (Notification_Clans_GrantLeader *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_GrantLeader);
3163 notification->clanHandle=clanHandle;
3164 notification->oldLeader=command->callingUserName;
3165 notification->newLeader=targetHandle;
3166 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3167 }
3168
3169 return true;
3170 }
3171
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3172 bool RakNet::Clans_SetSubleaderStatus_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3173 {
3174 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3175 PGresult *result=0;
3176
3177 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3178 if (clanId==0)
3179 {
3180 resultCode=L2RC_Clans_SetSubleaderStatus_UNKNOWN_CLAN;
3181 return true;
3182 }
3183
3184 if (IsClanLeader(clanId, command->callerUserId, pgsql)==false)
3185 {
3186 resultCode=L2RC_Clans_SetProperties_MUST_BE_LEADER;
3187 return true;
3188 }
3189
3190 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
3191 if (targetUserId==0)
3192 {
3193 resultCode=L2RC_Clans_SetSubleaderStatus_UNKNOWN_TARGET_HANDLE;
3194 return true;
3195 }
3196
3197 if (targetUserId==command->callerUserId)
3198 {
3199 resultCode=L2RC_Clans_SetSubleaderStatus_CANNOT_PERFORM_ON_SELF;
3200 return true;
3201 }
3202
3203 bool isSubleader;
3204 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetUserId, &isSubleader, pgsql);
3205 if (clanMemberState!=CMD_ACTIVE)
3206 {
3207 resultCode=L2RC_Clans_SetSubleaderStatus_TARGET_NOT_IN_CLAN;
3208 return true;
3209 }
3210 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET isSubleader=%b WHERE clanId_fk=%i AND userId_fk=%i;",setToSubleader,clanId,targetUserId);
3211
3212 if (result==0)
3213 {
3214 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3215 return true;
3216 }
3217 PQclear(result);
3218
3219
3220 Notification_Clans_SetSubleaderStatus *notification;
3221 // Tell all clan members of the new leader
3222 DataStructures::List<ClanMemberDescriptor> clanMembers;
3223 GetClanMembers(clanId, clanMembers, pgsql);
3224
3225 // Tell all clan members, except the command originator, about the new clan leader
3226 for (unsigned int i=0; i < clanMembers.Size(); i++)
3227 {
3228 if (clanMembers[i].memberState!=CMD_ACTIVE)
3229 continue;
3230 if (clanMembers[i].userId==command->callerUserId)
3231 continue;
3232 notification = (Notification_Clans_SetSubleaderStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_SetSubleaderStatus);
3233 notification->clanHandle=clanHandle;
3234 notification->leaderHandle=command->callingUserName;
3235 notification->targetHandle=targetHandle;
3236 notification->setToSubleader=setToSubleader;
3237 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3238 }
3239
3240 resultCode=L2RC_SUCCESS;
3241 return true;
3242 }
3243
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3244 bool RakNet::Clans_SetMemberRank_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3245 {
3246 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3247 PGresult *result=0;
3248
3249 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3250 if (clanId==0)
3251 {
3252 resultCode=L2RC_Clans_SetMemberRank_UNKNOWN_CLAN;
3253 return true;
3254 }
3255
3256 if (IsClanLeader(clanId, command->callerUserId, pgsql)==false)
3257 {
3258 resultCode=L2RC_Clans_SetMemberRank_MUST_BE_LEADER;
3259 return true;
3260 }
3261
3262 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
3263 if (targetUserId==0)
3264 {
3265 resultCode=L2RC_Clans_SetMemberRank_UNKNOWN_TARGET_HANDLE;
3266 return true;
3267 }
3268
3269 if (targetUserId==command->callerUserId)
3270 {
3271 resultCode=L2RC_Clans_SetMemberRank_CANNOT_PERFORM_ON_SELF;
3272 return true;
3273 }
3274
3275 bool isSubleader;
3276 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetUserId, &isSubleader, pgsql);
3277 if (clanMemberState!=CMD_ACTIVE)
3278 {
3279 resultCode=L2RC_Clans_SetMemberRank_TARGET_NOT_IN_CLAN;
3280 return true;
3281 }
3282 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET rank=%i WHERE clanId_fk=%i AND userId_fk=%i;",newRank,clanId,targetUserId);
3283
3284 if (result==0)
3285 {
3286 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3287 return true;
3288 }
3289 PQclear(result);
3290
3291
3292 Notification_Clans_SetMemberRank *notification;
3293 // Tell all clan members of the new leader
3294 DataStructures::List<ClanMemberDescriptor> clanMembers;
3295 GetClanMembers(clanId, clanMembers, pgsql);
3296
3297 // Tell all clan members, except the command originator, about the new clan leader
3298 for (unsigned int i=0; i < clanMembers.Size(); i++)
3299 {
3300 if (clanMembers[i].memberState!=CMD_ACTIVE)
3301 continue;
3302 if (clanMembers[i].userId==command->callerUserId)
3303 continue;
3304 notification = (Notification_Clans_SetMemberRank *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_SetMemberRank);
3305 notification->clanHandle=clanHandle;
3306 notification->leaderHandle=command->callingUserName;
3307 notification->targetHandle=targetHandle;
3308 notification->newRank=newRank;
3309 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3310 }
3311
3312 resultCode=L2RC_SUCCESS;
3313 return true;
3314 }
3315
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3316 bool RakNet::Clans_GetMemberProperties_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3317 {
3318 (void)command;
3319
3320 PGresult *result=0;
3321 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3322 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3323 if (clanId==0)
3324 {
3325 resultCode=L2RC_Clans_GetMemberProperties_UNKNOWN_CLAN;
3326 return true;
3327 }
3328 unsigned int targetUserId = GetUserRowFromHandle(targetHandle, pgsql);
3329 if (targetUserId==0)
3330 {
3331 resultCode=L2RC_Clans_GetMemberProperties_UNKNOWN_TARGET_HANDLE;
3332 return true;
3333 }
3334 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetUserId, &isSubleader, pgsql);
3335 if (clanMemberState==CMD_UNDEFINED)
3336 {
3337 resultCode=L2RC_Clans_GetMemberProperties_TARGET_NOT_IN_CLAN;
3338 return true;
3339 }
3340 result = pgsql->QueryVariadic("SELECT description, binaryData, isSubleader, rank, memberState_fk, banReason FROM lobby2.clanMembers where userId_fk=%i AND clanId_fk=%i",
3341 targetUserId, clanId);
3342
3343 if (result==0)
3344 {
3345 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3346 return true;
3347 }
3348
3349 PostgreSQLInterface::PQGetValueFromBinary(&description, result, 0, "description");
3350 PostgreSQLInterface::PQGetValueFromBinary(&binaryData->binaryData, &binaryData->binaryDataLength, result, 0, "binaryData");
3351 PostgreSQLInterface::PQGetValueFromBinary(&isSubleader, result, 0, "isSubleader");
3352 PostgreSQLInterface::PQGetValueFromBinary(&rank, result, 0, "rank");
3353 int cms;
3354 PostgreSQLInterface::PQGetValueFromBinary(&cms, result, 0, "memberState_fk");
3355 clanMemberState=(ClanMemberState)cms;
3356 PostgreSQLInterface::PQGetValueFromBinary(&banReason, result, 0, "banReason");
3357 PQclear(result);
3358
3359 resultCode=L2RC_SUCCESS;
3360 return true;
3361 }
3362
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3363 bool RakNet::Clans_ChangeHandle_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3364 {
3365 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3366 PGresult *result=0;
3367
3368 unsigned int clanId = GetClanIdFromHandle(oldClanHandle, pgsql);
3369 if (clanId==0)
3370 {
3371 resultCode=L2RC_Clans_ChangeHandle_UNKNOWN_CLAN;
3372 return true;
3373 }
3374
3375 if (IsClanLeader(clanId, command->callerUserId, pgsql)==false)
3376 {
3377 resultCode=L2RC_Clans_ChangeHandle_MUST_BE_LEADER;
3378 return true;
3379 }
3380
3381 result = pgsql->QueryVariadic("UPDATE lobby2.clans SET clanHandle=%s WHERE clanId_pk=%i;", newClanHandle.C_String(), clanId);
3382
3383 if (result==0)
3384 {
3385 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3386 return true;
3387 }
3388 PQclear(result);
3389
3390 Notification_Clans_ChangeHandle *notification;
3391 // Tell all clan members of the new leader
3392 DataStructures::List<ClanMemberDescriptor> clanMembers;
3393 GetClanMembers(clanId, clanMembers, pgsql);
3394
3395 // Tell all clan members, except the command originator, about the new clan name
3396 for (unsigned int i=0; i < clanMembers.Size(); i++)
3397 {
3398 if (clanMembers[i].memberState!=CMD_ACTIVE)
3399 continue;
3400 if (clanMembers[i].userId==command->callerUserId)
3401 continue;
3402 notification = (Notification_Clans_ChangeHandle *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_ChangeHandle);
3403 notification->oldClanHandle=oldClanHandle;
3404 notification->leaderHandle=command->callingUserName;
3405 notification->newClanHandle=newClanHandle;
3406 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3407 }
3408
3409 resultCode=L2RC_SUCCESS;
3410 return true;
3411 }
3412
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3413 bool RakNet::Clans_Leave_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3414 {
3415 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3416 PGresult *result=0;
3417
3418 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3419 if (clanId==0)
3420 {
3421 resultCode=L2RC_Clans_Leave_UNKNOWN_CLAN;
3422 return true;
3423 }
3424
3425 bool isSubleader;
3426 ClanMemberState state = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3427 if (state!=CMD_ACTIVE)
3428 {
3429 resultCode=L2RC_Clans_Leave_NOT_IN_CLAN;
3430 return true;
3431 }
3432
3433 bool isClanLeader = IsClanLeader(clanId, command->callerUserId, pgsql);
3434
3435 // Remove from the clanMembers table
3436 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", command->callerUserId, clanId);
3437
3438 if (result==0)
3439 {
3440 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3441 return true;
3442 }
3443
3444 PQclear(result);
3445
3446 // Tell all clan members of the new leader
3447 DataStructures::List<ClanMemberDescriptor> clanMembers;
3448 GetClanMembers(clanId, clanMembers, pgsql);
3449
3450 if (subject.IsEmpty()==false || body.IsEmpty()==false)
3451 {
3452 // Send this email to the members
3453 DataStructures::List<unsigned int> targetUserIds;
3454 for (unsigned int i=0; i < clanMembers.Size(); i++)
3455 {
3456 if (clanMembers[i].memberState==CMD_ACTIVE)
3457 targetUserIds.Insert(clanMembers[i].userId, __FILE__, __LINE__ );
3458 }
3459 SendEmail(targetUserIds, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_Leave", pgsql);
3460 }
3461
3462 unsigned int validUserCount=0;
3463 for (unsigned int i=0; i < clanMembers.Size(); i++)
3464 {
3465 if (clanMembers[i].memberState!=CMD_ACTIVE)
3466 continue;
3467 validUserCount++;
3468 }
3469
3470 wasDissolved = false;
3471
3472 if (isClanLeader)
3473 {
3474 if (dissolveIfClanLeader || validUserCount==0)
3475 {
3476 wasDissolved = true;
3477
3478 // Send notification to clan members that the clan was destroyed, then destroy the clan
3479 Notification_Clans_Destroyed *notification;
3480 for (unsigned int i=0; i < clanMembers.Size(); i++)
3481 {
3482 if (clanMembers[i].memberState!=CMD_ACTIVE)
3483 continue;
3484 notification = (Notification_Clans_Destroyed *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_Destroyed);
3485 notification->clanHandle=clanHandle;
3486 notification->oldClanLeader=command->callingUserName;
3487 notification->resultCode=L2RC_SUCCESS;
3488 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3489 }
3490
3491 // Tell the former leader the clan was destroyed too
3492 notification = (Notification_Clans_Destroyed *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_Destroyed);
3493 notification->clanHandle=clanHandle;
3494 notification->oldClanLeader=command->callingUserName;
3495 notification->resultCode=L2RC_SUCCESS;
3496 command->server->AddOutputFromThread(notification, command->callerUserId, command->callingUserName);
3497
3498 // Destroy the clan
3499 result = pgsql->QueryVariadic("DELETE FROM lobby2.clans WHERE clanId_pk=%i", clanId);
3500 PQclear(result);
3501
3502 resultCode=L2RC_SUCCESS;
3503 return true;
3504 }
3505 else
3506 {
3507 // Choose the oldest subleader to lead, or if no subleaders, the oldest member
3508 result = pgsql->QueryVariadic("SELECT handle, userId_pk FROM lobby2.users WHERE userId_pk=(SELECT userId_fk FROM lobby2.clanMembers WHERE clanId_fk=%i ORDER BY isSubleader, creationDate DESC LIMIT 1)",
3509 clanId);
3510
3511 int numRowsReturned = PQntuples(result);
3512 if (numRowsReturned==0)
3513 {
3514 // Destroy the clan if no possible leader (due to asynch)
3515 wasDissolved = true;
3516 PQclear(result);
3517 result = pgsql->QueryVariadic("DELETE FROM lobby2.clans WHERE clanId_pk=%i", clanId);
3518 PQclear(result);
3519 resultCode=L2RC_SUCCESS;
3520 return true;
3521 }
3522
3523 unsigned int newLeaderId;
3524 PostgreSQLInterface::PQGetValueFromBinary(&newClanLeader, result, 0, "handle");
3525 PostgreSQLInterface::PQGetValueFromBinary(&newLeaderId, result, 0, "userId_pk");
3526
3527 PQclear(result);
3528
3529 // Promote this member to the leader
3530 result = pgsql->QueryVariadic("UPDATE lobby2.clans SET leaderUserId_fk=%i WHERE clanId_pk = %i", newLeaderId, clanId);
3531 PQclear(result);
3532
3533 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET isSubleader=FALSE WHERE userId_fk = %i AND clanId_fk = %i", newLeaderId, clanId);
3534 PQclear(result);
3535
3536 // Notify users of new leader
3537 Notification_Clans_GrantLeader *notification;
3538 for (unsigned int i=0; i < clanMembers.Size(); i++)
3539 {
3540 if (clanMembers[i].memberState!=CMD_ACTIVE)
3541 continue;
3542 if (clanMembers[i].userId==command->callerUserId)
3543 continue;
3544 notification = (Notification_Clans_GrantLeader *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_GrantLeader);
3545 notification->clanHandle=clanHandle;
3546 notification->newLeader=newClanLeader;
3547 notification->oldLeader=command->callingUserName;
3548 notification->resultCode=L2RC_SUCCESS;
3549 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3550 }
3551 }
3552 }
3553
3554
3555 // Send notification to clan members that the member has left the clan
3556 Notification_Clans_Leave *notification;
3557 for (unsigned int i=0; i < clanMembers.Size(); i++)
3558 {
3559 if (clanMembers[i].memberState!=CMD_ACTIVE)
3560 continue;
3561 if (clanMembers[i].userId==command->callerUserId)
3562 continue;
3563 notification = (Notification_Clans_Leave *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_Leave);
3564 notification->clanHandle=clanHandle;
3565 notification->targetHandle=command->callingUserName;
3566 notification->resultCode=L2RC_SUCCESS;
3567 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
3568 }
3569
3570 resultCode=L2RC_SUCCESS;
3571 return true;
3572 }
3573
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3574 bool RakNet::Clans_SendJoinInvitation_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3575 {
3576 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3577 PGresult *result=0;
3578
3579 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3580 if (clanId==0)
3581 {
3582 resultCode=L2RC_Clans_SendJoinInvitation_UNKNOWN_CLAN;
3583 return true;
3584 }
3585
3586 bool isSubleader;
3587 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3588 if (clanMemberState!=CMD_ACTIVE)
3589 {
3590 resultCode=L2RC_Clans_SendJoinInvitation_NOT_IN_CLAN;
3591 return true;
3592 }
3593
3594 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
3595
3596 if (isSubleader==false && clanLeaderId!=command->callerUserId)
3597 {
3598 resultCode=L2RC_Clans_SendJoinInvitation_MUST_BE_LEADER_OR_SUBLEADER;
3599 return true;
3600 }
3601
3602 // Does target already have an entry?
3603 unsigned int targetId = RakNet::GetUserRowFromHandle(targetHandle, pgsql);
3604 if (targetId==0)
3605 {
3606 resultCode=L2RC_Clans_SendJoinInvitation_UNKNOWN_TARGET_HANDLE;
3607 return true;
3608 }
3609
3610 if (targetId==command->callerUserId)
3611 {
3612 resultCode=L2RC_Clans_SendJoinInvitation_CANNOT_PERFORM_ON_SELF;
3613 return true;
3614 }
3615
3616 bool isTargetSubleader;
3617 RakNet::ClanMemberState targetClanMemberState = GetClanMemberState(clanId, targetId, &isTargetSubleader, pgsql);
3618 if (targetClanMemberState==CMD_ACTIVE)
3619 {
3620 // active member
3621 resultCode=L2RC_Clans_SendJoinInvitation_TARGET_ALREADY_IN_CLAN;
3622 return true;
3623 }
3624
3625 if (targetClanMemberState==CMD_BANNED)
3626 {
3627 // banned
3628 resultCode=L2RC_Clans_SendJoinInvitation_TARGET_IS_BANNED;
3629 return true;
3630 }
3631
3632 if (targetClanMemberState==CMD_JOIN_INVITED)
3633 {
3634 // already invited
3635 resultCode=L2RC_Clans_SendJoinInvitation_REQUEST_ALREADY_PENDING;
3636 return true;
3637 }
3638
3639 if (targetClanMemberState==CMD_JOIN_REQUESTED)
3640 {
3641 resultCode=L2RC_Clans_SendJoinInvitation_TARGET_ALREADY_REQUESTED;
3642 // already requested
3643 return true;
3644 }
3645
3646 // Add row to lobby2.clanMembers
3647 result = pgsql->QueryVariadic(
3648 "INSERT INTO lobby2.clanMembers (userId_fk, clanId_fk, isSubleader, memberState_fk) VALUES "
3649 "(%i, %i, false, (SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinInvited') );"
3650 ,targetId, clanId);
3651 PQclear(result);
3652
3653 // Send email to targetman
3654 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_SendJoinInvitation", pgsql);
3655
3656 // Send notification to target, leader, subleaders about this invite
3657 Notification_Clans_PendingJoinStatus *notification;
3658 notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
3659 notification->clanHandle=clanHandle;
3660 notification->targetHandle=targetHandle;
3661 notification->sourceHandle=command->callingUserName;
3662 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_INVITATION;
3663 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_SENT;
3664 command->server->AddOutputFromThread(notification, targetId, targetHandle); // target
3665
3666 DataStructures::List<ClanMemberDescriptor> clanMembers;
3667 GetClanMembers(clanId, clanMembers, pgsql);
3668 for (unsigned int i=0; i < clanMembers.Size(); i++)
3669 {
3670 if (clanMembers[i].memberState!=CMD_ACTIVE)
3671 continue;
3672 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
3673 continue;
3674
3675 notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
3676 notification->clanHandle=clanHandle;
3677 notification->targetHandle=targetHandle;
3678 notification->sourceHandle=command->callingUserName;
3679 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_INVITATION;
3680 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_SENT;
3681 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
3682
3683 }
3684
3685 resultCode=L2RC_SUCCESS;
3686 return true;
3687 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3688 bool RakNet::Clans_WithdrawJoinInvitation_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3689 {
3690 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3691 PGresult *result=0;
3692
3693 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3694 if (clanId==0)
3695 {
3696 resultCode=L2RC_Clans_WithdrawJoinInvitation_UNKNOWN_CLAN;
3697 return true;
3698 }
3699
3700 // Does target already have an entry?
3701 unsigned int targetId = RakNet::GetUserRowFromHandle(targetHandle, pgsql);
3702 if (targetId==0)
3703 {
3704 resultCode=L2RC_Clans_WithdrawJoinInvitation_UNKNOWN_TARGET_HANDLE;
3705 return true;
3706 }
3707
3708 if (targetId==command->callerUserId)
3709 {
3710 resultCode=L2RC_Clans_WithdrawJoinInvitation_CANNOT_PERFORM_ON_SELF;
3711 return true;
3712 }
3713
3714 bool isSubleader;
3715 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetId, &isSubleader, pgsql);
3716 if (clanMemberState!=CMD_JOIN_INVITED)
3717 {
3718 resultCode=L2RC_Clans_WithdrawJoinInvitation_NO_SUCH_INVITATION_EXISTS;
3719 return true;
3720 }
3721
3722 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
3723 if (isSubleader==false && clanLeaderId!=command->callerUserId)
3724 {
3725 resultCode=L2RC_Clans_WithdrawJoinInvitation_MUST_BE_LEADER_OR_SUBLEADER;
3726 return true;
3727 }
3728
3729 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", targetId, clanId);
3730 PQclear(result);
3731
3732 // Send email to target
3733 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_WithdrawJoinInvitation", pgsql);
3734
3735 // Send notification to target, leader, subleaders
3736 Notification_Clans_PendingJoinStatus *notification;
3737 notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
3738 notification->clanHandle=clanHandle;
3739 notification->targetHandle=targetHandle;
3740 notification->sourceHandle=command->callingUserName;
3741 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_INVITATION;
3742 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_WITHDRAWN;
3743 command->server->AddOutputFromThread(notification, targetId, targetHandle); // target
3744
3745
3746 DataStructures::List<ClanMemberDescriptor> clanMembers;
3747 GetClanMembers(clanId, clanMembers, pgsql);
3748 for (unsigned int i=0; i < clanMembers.Size(); i++)
3749 {
3750 if (clanMembers[i].memberState!=CMD_ACTIVE)
3751 continue;
3752 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
3753 continue;
3754 if (command->callerUserId==clanMembers[i].userId)
3755 continue;
3756
3757 notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
3758 notification->clanHandle=clanHandle;
3759 notification->targetHandle=targetHandle;
3760 notification->sourceHandle=command->callingUserName;
3761 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_INVITATION;
3762 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_WITHDRAWN;
3763 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
3764
3765 }
3766
3767 resultCode=L2RC_SUCCESS;
3768 return true;
3769 }
3770
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3771 bool RakNet::Clans_AcceptJoinInvitation_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3772 {
3773 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3774 PGresult *result=0;
3775
3776 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3777 if (clanId==0)
3778 {
3779 resultCode=L2RC_Clans_AcceptJoinInvitation_UNKNOWN_CLAN;
3780 return true;
3781 }
3782
3783 bool isSubleader;
3784 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3785 if (clanMemberState!=CMD_JOIN_INVITED)
3786 {
3787 resultCode=L2RC_Clans_AcceptJoinInvitation_NO_SUCH_INVITATION_EXISTS;
3788 return true;
3789 }
3790
3791 // Change status from invited to clan member
3792 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active') WHERE userId_fk=%i AND clanId_fk=%i", command->callerUserId, clanId);
3793 PQclear(result);
3794
3795
3796 // Do AFTER the update in case another thread also added to a clan
3797 if (failIfAlreadyInClan)
3798 {
3799 int count = GetActiveClanCount(command->callerUserId, pgsql);
3800 if (count>1)
3801 {
3802 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i;", command->callerUserId, clanId);
3803 PQclear(result);
3804 resultCode=L2RC_Clans_AcceptJoinInvitation_ALREADY_IN_DIFFERENT_CLAN;
3805 return true;
3806 }
3807 }
3808
3809 // Notify all members about this new member
3810 DataStructures::List<ClanMemberDescriptor> clanMembers;
3811 GetClanMembers(clanId, clanMembers, pgsql);
3812 for (unsigned int i=0; i < clanMembers.Size(); i++)
3813 {
3814 if (clanMembers[i].memberState!=CMD_ACTIVE)
3815 continue;
3816 if (clanMembers[i].userId==command->callerUserId )
3817 continue;
3818
3819 Notification_Clans_NewClanMember *notification = (Notification_Clans_NewClanMember *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_NewClanMember);
3820 notification->clanHandle=clanHandle;
3821 notification->targetHandle=command->callingUserName;
3822 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
3823
3824 }
3825
3826 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
3827
3828 // Send email to leader
3829 SendEmail(clanLeaderId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_AcceptJoinInvitation", pgsql);
3830
3831 resultCode=L2RC_SUCCESS;
3832 return true;
3833 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3834 bool RakNet::Clans_RejectJoinInvitation_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3835 {
3836 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3837 PGresult *result=0;
3838
3839 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3840 if (clanId==0)
3841 {
3842 resultCode=L2RC_Clans_RejectJoinInvitation_UNKNOWN_CLAN;
3843 return true;
3844 }
3845
3846 bool isSubleader;
3847 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3848 if (clanMemberState!=CMD_JOIN_INVITED)
3849 {
3850 resultCode=L2RC_Clans_RejectJoinInvitation_NO_SUCH_INVITATION_EXISTS;
3851 return true;
3852 }
3853
3854 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", command->callerUserId, clanId);
3855 PQclear(result);
3856
3857 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
3858
3859 // Send email to leader
3860 SendEmail(clanLeaderId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_RejectJoinInvitation", pgsql);
3861
3862 // Subleader and leader notification
3863 DataStructures::List<ClanMemberDescriptor> clanMembers;
3864 GetClanMembers(clanId, clanMembers, pgsql);
3865 for (unsigned int i=0; i < clanMembers.Size(); i++)
3866 {
3867 if (clanMembers[i].memberState!=CMD_ACTIVE)
3868 continue;
3869 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
3870 continue;
3871
3872 Notification_Clans_PendingJoinStatus *notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
3873 notification->clanHandle=clanHandle;
3874 notification->sourceHandle=command->callingUserName;
3875 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_INVITATION;
3876 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_WITHDRAWN;
3877 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
3878
3879 }
3880
3881 resultCode=L2RC_SUCCESS;
3882 return true;
3883 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3884 bool RakNet::Clans_DownloadInvitationList_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3885 {
3886 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3887 PGresult *result = pgsql->QueryVariadic(
3888 " SELECT clanHandle FROM lobby2.clans INNER JOIN "
3889 " (SELECT clanId_fk FROM lobby2.clanMembers WHERE userId_fk=%i AND memberState_fk = "
3890 " (SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinInvited') ) as tbl1 "
3891 " ON tbl1.clanId_fk = lobby2.clans.clanId_pk;", command->callerUserId);
3892
3893 if (result==0)
3894 {
3895 PQclear(result);
3896 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3897 return true;
3898 }
3899 int numRowsReturned = PQntuples(result);
3900 int i;
3901 for (i=0; i < numRowsReturned; i++)
3902 {
3903 OpenInvite oi;
3904 PostgreSQLInterface::PQGetValueFromBinary(&oi.clanHandle, result, i, "clanHandle");
3905 invitationsSentToMe.Insert(oi, __FILE__, __LINE__ );
3906 }
3907
3908 PQclear(result);
3909
3910 // Gets all users in clans that I am in, as active or leader, where an invitation has been sent
3911 result = pgsql->QueryVariadic(
3912 "SELECT creationDate, handle, clanHandle FROM "
3913 "( "
3914 "SELECT userId_fk, creationDate, myClans.clanId_fk FROM lobby2.clanMembers INNER JOIN (SELECT clanId_fk FROM lobby2.clanMembers WHERE userId_fk=%i) as myClans ON "
3915 "myClans.clanId_fk=lobby2.clanMembers.clanId_fk AND memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinInvited') "
3916 ") as t1 "
3917 "INNER JOIN lobby2.users as t2 on (t1.userId_fk=t2.userId_pk) "
3918 "INNER JOIN lobby2.clans as t3 on (t1.clanId_fk=t3.clanId_pk) ", command->callerUserId);
3919
3920
3921 if (result==0)
3922 {
3923 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
3924 return true;
3925 }
3926
3927 numRowsReturned = PQntuples(result);
3928 if (numRowsReturned==0)
3929 {
3930 PQclear(result);
3931 resultCode=L2RC_SUCCESS;
3932 return true;
3933 }
3934
3935 for (i=0; i < PQntuples(result); i++)
3936 {
3937 ClanJoinInvite cjr;
3938 PostgreSQLInterface::PQGetValueFromBinary(&cjr.sourceClan, result, i, "clanHandle");
3939 PostgreSQLInterface::PQGetValueFromBinary(&cjr.dateSent, result, i, "creationDate");
3940 PostgreSQLInterface::PQGetValueFromBinary(&cjr.joinRequestTarget, result, i, "joinRequestTarget");
3941 usersThatHaveAnInvitationFromClansThatIAmAMemberOf.Insert(cjr, __FILE__, __LINE__ );
3942 }
3943 PQclear(result);
3944
3945 resultCode=L2RC_SUCCESS;
3946 return true;
3947 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)3948 bool RakNet::Clans_SendJoinRequest_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
3949 {
3950 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
3951 PGresult *result=0;
3952
3953 clanJoined=false;
3954
3955 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
3956 if (clanId==0)
3957 {
3958 resultCode=L2RC_Clans_SendJoinRequest_UNKNOWN_CLAN;
3959 return true;
3960 }
3961
3962 bool isSubleader;
3963 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
3964 if (clanMemberState==CMD_ACTIVE)
3965 {
3966 resultCode=L2RC_Clans_SendJoinRequest_ALREADY_IN_CLAN;
3967 return true;
3968 }
3969 if (clanMemberState==CMD_BANNED)
3970 {
3971 resultCode=L2RC_Clans_SendJoinRequest_BANNED;
3972 return true;
3973 }
3974 if (clanMemberState==CMD_JOIN_REQUESTED)
3975 {
3976 resultCode=L2RC_Clans_SendJoinRequest_REQUEST_ALREADY_PENDING;
3977 return true;
3978 }
3979 if (clanMemberState==CMD_JOIN_INVITED)
3980 {
3981 resultCode=L2RC_Clans_SendJoinRequest_ALREADY_INVITED;
3982 return true;
3983 }
3984
3985 result = pgsql->QueryVariadic("SELECT requiresInvitationsToJoin FROM lobby2.clans WHERE clanId_pk=%i",clanId);
3986 bool requiresInvitationsToJoin;
3987 PostgreSQLInterface::PQGetValueFromBinary(&requiresInvitationsToJoin, result, 0, "requiresInvitationsToJoin");
3988 PQclear(result);
3989
3990 if (requiresInvitationsToJoin==false)
3991 {
3992 result = pgsql->QueryVariadic(
3993 "INSERT INTO lobby2.clanMembers (userId_fk, clanId_fk, isSubleader, memberState_fk) VALUES "
3994 "(%i, %i, false, (SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active') );"
3995 ,command->callerUserId, clanId);
3996 PQclear(result);
3997
3998 // Send notification all members about the new member
3999 // Notify all members about this new member
4000 DataStructures::List<ClanMemberDescriptor> clanMembers;
4001 GetClanMembers(clanId, clanMembers, pgsql);
4002 for (unsigned int i=0; i < clanMembers.Size(); i++)
4003 {
4004 if (clanMembers[i].memberState!=CMD_ACTIVE)
4005 continue;
4006 if (clanMembers[i].userId==command->callerUserId)
4007 continue;
4008
4009 Notification_Clans_NewClanMember *notification = (Notification_Clans_NewClanMember *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_NewClanMember);
4010 notification->clanHandle=clanHandle;
4011 notification->targetHandle=command->callingUserName;
4012 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
4013 }
4014
4015 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4016
4017 // Send email to leader
4018 SendEmail(clanLeaderId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_SendJoinRequest", pgsql);
4019
4020 clanJoined=true;
4021 }
4022 else
4023 {
4024 // Add row to lobby2.clanMembers
4025 result = pgsql->QueryVariadic(
4026 "INSERT INTO lobby2.clanMembers (userId_fk, clanId_fk, isSubleader, memberState_fk) VALUES "
4027 "(%i, %i, false, (SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinRequested') );"
4028 ,command->callerUserId, clanId);
4029 PQclear(result);
4030
4031 // Send notification to leader, subleaders about this invite
4032 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4033 DataStructures::List<ClanMemberDescriptor> clanMembers;
4034 GetClanMembers(clanId, clanMembers, pgsql);
4035 for (unsigned int i=0; i < clanMembers.Size(); i++)
4036 {
4037 if (clanMembers[i].memberState!=CMD_ACTIVE)
4038 continue;
4039 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
4040 continue;
4041
4042 Notification_Clans_PendingJoinStatus *notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
4043 notification->clanHandle=clanHandle;
4044 notification->targetHandle=clanMembers[i].name;
4045 notification->sourceHandle=command->callingUserName;
4046 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_REQUEST;
4047 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_SENT;
4048 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
4049 }
4050 }
4051
4052 resultCode=L2RC_SUCCESS;
4053 return true;
4054 }
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4055 bool RakNet::Clans_WithdrawJoinRequest_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4056 {
4057 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4058 PGresult *result=0;
4059
4060 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4061 if (clanId==0)
4062 {
4063 resultCode=L2RC_Clans_WithdrawJoinRequest_UNKNOWN_CLAN;
4064 return true;
4065 }
4066
4067 bool isSubleader;
4068 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
4069 if (clanMemberState==CMD_ACTIVE)
4070 {
4071 resultCode=L2RC_Clans_WithdrawJoinRequest_ALREADY_IN_CLAN;
4072 return true;
4073 }
4074
4075 if (clanMemberState!=CMD_JOIN_REQUESTED)
4076 {
4077 resultCode=L2RC_Clans_WithdrawJoinRequest_NO_SUCH_INVITATION_EXISTS;
4078 return true;
4079 }
4080
4081 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", command->callerUserId, clanId);
4082
4083 if (result==0)
4084 {
4085 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4086 return true;
4087 }
4088
4089 PQclear(result);
4090
4091 // Send email to leader
4092 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4093 SendEmail(clanLeaderId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_WithdrawJoinRequest", pgsql);
4094
4095 // Send notification to leader, subleaders
4096 DataStructures::List<ClanMemberDescriptor> clanMembers;
4097 GetClanMembers(clanId, clanMembers, pgsql);
4098 for (unsigned int i=0; i < clanMembers.Size(); i++)
4099 {
4100 if (clanMembers[i].memberState!=CMD_ACTIVE)
4101 continue;
4102 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
4103 continue;
4104
4105 Notification_Clans_PendingJoinStatus *notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
4106 notification->clanHandle=clanHandle;
4107 notification->targetHandle=clanMembers[i].name;
4108 notification->sourceHandle=command->callingUserName;
4109 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_REQUEST;
4110 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_WITHDRAWN;
4111 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
4112 }
4113
4114 resultCode=L2RC_SUCCESS;
4115 return true;
4116 }
4117
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4118 bool RakNet::Clans_AcceptJoinRequest_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4119 {
4120 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4121 PGresult *result=0;
4122
4123 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4124 if (clanId==0)
4125 {
4126 resultCode=L2RC_Clans_AcceptJoinRequest_UNKNOWN_CLAN;
4127 return true;
4128 }
4129
4130 bool isSubleader;
4131 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql);
4132 if (clanMemberState!=CMD_ACTIVE)
4133 {
4134 resultCode=L2RC_Clans_AcceptJoinRequest_NOT_IN_CLAN;
4135 return true;
4136 }
4137
4138 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4139 if (isSubleader==false && clanLeaderId!=command->callerUserId)
4140 {
4141 resultCode=L2RC_Clans_AcceptJoinRequest_MUST_BE_LEADER_OR_SUBLEADER;
4142 return true;
4143 }
4144
4145 // Does target already have an entry?
4146 unsigned int targetId = RakNet::GetUserRowFromHandle(requestingUserHandle, pgsql);
4147 if (targetId==0)
4148 {
4149 resultCode=L2RC_Clans_AcceptJoinRequest_UNKNOWN_TARGET_HANDLE;
4150 return true;
4151 }
4152
4153 if (targetId==command->callerUserId)
4154 {
4155 resultCode=L2RC_Clans_AcceptJoinRequest_CANNOT_PERFORM_ON_SELF;
4156 return true;
4157 }
4158
4159 bool isTargetSubleader;
4160 RakNet::ClanMemberState targetClanMemberState = GetClanMemberState(clanId, targetId, &isTargetSubleader, pgsql);
4161 if (targetClanMemberState==CMD_ACTIVE)
4162 {
4163 // active member
4164 resultCode=L2RC_Clans_AcceptJoinRequest_TARGET_ALREADY_IN_CLAN;
4165 return true;
4166 }
4167
4168 if (targetClanMemberState==CMD_BANNED)
4169 {
4170 resultCode=L2RC_Clans_AcceptJoinRequest_TARGET_IS_BANNED;
4171 return true;
4172 }
4173
4174 if (targetClanMemberState!=CMD_JOIN_REQUESTED)
4175 {
4176 resultCode=L2RC_Clans_AcceptJoinRequest_REQUEST_NOT_PENDING;
4177 return true;
4178 }
4179
4180 // Change status to clan member
4181 result = pgsql->QueryVariadic("UPDATE lobby2.clanMembers SET memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active') WHERE userId_fk=%i AND clanId_fk=%i", targetId, clanId);
4182 PQclear(result);
4183
4184 // Do AFTER the update in case another thread also added to a clan
4185 if (failIfAlreadyInClan)
4186 {
4187 int count = GetActiveClanCount(targetId, pgsql);
4188 if (count>1)
4189 {
4190 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i;", targetId, clanId);
4191 PQclear(result);
4192 resultCode=L2RC_Clans_AcceptJoinRequest_TARGET_ALREADY_IN_DIFFERENT_CLAN;
4193 return true;
4194 }
4195 }
4196
4197 // Notify all members about this new member
4198 DataStructures::List<ClanMemberDescriptor> clanMembers;
4199 GetClanMembers(clanId, clanMembers, pgsql);
4200 for (unsigned int i=0; i < clanMembers.Size(); i++)
4201 {
4202 if (clanMembers[i].memberState!=CMD_ACTIVE)
4203 continue;
4204 if (clanMembers[i].userId==command->callerUserId )
4205 continue;
4206
4207 Notification_Clans_NewClanMember *notification = (Notification_Clans_NewClanMember *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_NewClanMember);
4208 notification->clanHandle=clanHandle;
4209 notification->targetHandle=requestingUserHandle;
4210 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name); // subleader
4211 }
4212
4213 // Send email to member
4214 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_AcceptJoinRequest", pgsql);
4215
4216 resultCode=L2RC_SUCCESS;
4217 return true;
4218 }
4219
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4220 bool RakNet::Clans_RejectJoinRequest_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4221 {
4222 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4223 PGresult *result=0;
4224
4225 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4226 if (clanId==0)
4227 {
4228 resultCode=L2RC_Clans_RejectJoinRequest_UNKNOWN_CLAN;
4229 return true;
4230 }
4231
4232 bool isSubleader;
4233 if (GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql)!=CMD_ACTIVE)
4234 {
4235 resultCode=L2RC_Clans_RejectJoinRequest_NOT_IN_CLAN;
4236 return true;
4237 }
4238
4239 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4240
4241 if (isSubleader==false && clanLeaderId!=command->callerUserId)
4242 {
4243 resultCode=L2RC_Clans_RejectJoinRequest_MUST_BE_LEADER_OR_SUBLEADER;
4244 return true;
4245 }
4246
4247 unsigned int targetId = RakNet::GetUserRowFromHandle(requestingUserHandle, pgsql);
4248 if (targetId==0)
4249 {
4250 resultCode=L2RC_Clans_RejectJoinRequest_REQUESTING_USER_HANDLE_UNKNOWN;
4251 return true;
4252 }
4253
4254 if (GetClanMemberState(clanId, targetId, &isSubleader, pgsql)!=CMD_JOIN_REQUESTED)
4255 {
4256 resultCode=L2RC_Clans_RejectJoinRequest_NO_SUCH_INVITATION_EXISTS;
4257 return true;
4258 }
4259
4260 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", targetId, clanId);
4261 PQclear(result);
4262
4263 // Subleader and leader notification
4264 DataStructures::List<ClanMemberDescriptor> clanMembers;
4265 GetClanMembers(clanId, clanMembers, pgsql);
4266 // requestingUserHandle will be notified too, insert a fake record
4267 ClanMemberDescriptor cmd;
4268 cmd.memberState=CMD_ACTIVE;
4269 cmd.isSubleader=true;
4270 cmd.userId=targetId;
4271 clanMembers.Insert(cmd, __FILE__, __LINE__ );
4272
4273 for (unsigned int i=0; i < clanMembers.Size(); i++)
4274 {
4275 if (clanMembers[i].memberState!=CMD_ACTIVE)
4276 continue;
4277 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
4278 continue;
4279
4280 Notification_Clans_PendingJoinStatus *notification = (Notification_Clans_PendingJoinStatus *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_PendingJoinStatus);
4281 notification->clanHandle=clanHandle;
4282 notification->sourceHandle=command->callingUserName;
4283 notification->targetHandle=requestingUserHandle;
4284 notification->clanMemberHandle=command->callingUserName;
4285 notification->majorOp=Notification_Clans_PendingJoinStatus::JOIN_CLAN_REQUEST;
4286 notification->minorOp=Notification_Clans_PendingJoinStatus::JOIN_REJECTED;
4287 notification->resultCode=L2RC_SUCCESS;
4288 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
4289 }
4290
4291 // Send email to member
4292 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_RejectJoinRequest", pgsql);
4293
4294 resultCode=L2RC_SUCCESS;
4295 return true;
4296 };
4297
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4298 bool RakNet::Clans_DownloadRequestList_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4299 {
4300 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4301 PGresult *result=0;
4302 int i;
4303
4304 // Get all clanMembers that are in state requested of all clans where I am the leader or subleader
4305 result = pgsql->QueryVariadic(
4306 "SELECT C.clanHandle, creationDate, U.handle FROM lobby2.clanMembers INNER JOIN "
4307 "(SELECT clanId_pk as clanId FROM lobby2.clans WHERE leaderUserId_fk=%i "
4308 "UNION ALL "
4309 "SELECT clanId_fk as clanId FROM lobby2.clanMembers WHERE isSubleader=TRUE AND userId_fk=%i) AS clansWhereIAmLeaderOrSubleader "
4310 "ON clansWhereIAmLeaderOrSubleader.clanId=lobby2.clanMembers.clanId_fk "
4311 "INNER JOIN lobby2.users U ON U.userId_pk = userId_fk "
4312 "INNER JOIN lobby2.clans C ON C.clanId_pk = clanId "
4313 "WHERE lobby2.clanMembers.memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinRequested');",
4314 command->callerUserId, command->callerUserId);
4315 if (result==0)
4316 {
4317 PQclear(result);
4318 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4319 return true;
4320 }
4321 for (i=0; i < PQntuples(result); i++)
4322 {
4323 ClanJoinRequest cjr;
4324 PostgreSQLInterface::PQGetValueFromBinary(&cjr.targetClan, result, i, "clanHandle");
4325 PostgreSQLInterface::PQGetValueFromBinary(&cjr.dateSent, result, i, "creationDate");
4326 PostgreSQLInterface::PQGetValueFromBinary(&cjr.joinRequestSender, result, i, "handle");
4327 joinRequestsToMyClan.Insert(cjr, __FILE__, __LINE__ );
4328 }
4329 PQclear(result);
4330
4331 // Get all clanMembers where I am in state requested
4332 result = pgsql->QueryVariadic(
4333 "SELECT C.clanHandle, M.creationDate FROM lobby2.clanMembers AS M, lobby2.clans AS C WHERE "
4334 "M.userId_fk=%i AND C.clanId_pk=M.clanId_fk AND M.memberState_fk = "
4335 "(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_JoinRequested');",
4336 command->callerUserId);
4337 if (result==0)
4338 {
4339 PQclear(result);
4340 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4341 return true;
4342 }
4343 for (i=0; i < PQntuples(result); i++)
4344 {
4345 ClanJoinRequest cjr;
4346 PostgreSQLInterface::PQGetValueFromBinary(&cjr.targetClan, result, i, "clanHandle");
4347 PostgreSQLInterface::PQGetValueFromBinary(&cjr.dateSent, result, i, "creationDate");
4348 joinRequestsFromMe.Insert(cjr, __FILE__, __LINE__ );
4349 }
4350 PQclear(result);
4351
4352 resultCode=L2RC_SUCCESS;
4353 return true;
4354 }
4355
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4356 bool RakNet::Clans_KickAndBlacklistUser_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4357 {
4358 (void)command;
4359 (void)databaseInterface;
4360
4361 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4362 PGresult *result=0;
4363
4364 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4365 if (clanId==0)
4366 {
4367 resultCode=L2RC_Clans_KickAndBlacklistUser_UNKNOWN_CLAN;
4368 return true;
4369 }
4370
4371 bool isSubleader;
4372 if (GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql)!=CMD_ACTIVE)
4373 {
4374 resultCode=L2RC_Clans_KickAndBlacklistUser_NOT_IN_CLAN;
4375 return true;
4376 }
4377
4378 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4379 if (isSubleader==false && clanLeaderId!=command->callerUserId)
4380 {
4381 resultCode=L2RC_Clans_KickAndBlacklistUser_MUST_BE_LEADER_OR_SUBLEADER;
4382 return true;
4383 }
4384
4385 unsigned int targetId = RakNet::GetUserRowFromHandle(targetHandle, pgsql);
4386 if (targetId==0)
4387 {
4388 resultCode=L2RC_Clans_KickAndBlacklistUser_UNKNOWN_TARGET_HANDLE;
4389 return true;
4390 }
4391
4392 if (targetId==command->callerUserId)
4393 {
4394 resultCode=L2RC_Clans_KickAndBlacklistUser_CANNOT_PERFORM_ON_SELF;
4395 return true;
4396 }
4397
4398 if (targetId==clanLeaderId)
4399 {
4400 resultCode=L2RC_Clans_KickAndBlacklistUser_CANNOT_PERFORM_ON_LEADER;
4401 return true;
4402 }
4403
4404 RakNet::ClanMemberState clanMemberState = GetClanMemberState(clanId, targetId, &isSubleader, pgsql);
4405 if (clanMemberState==CMD_BANNED)
4406 {
4407 resultCode=L2RC_Clans_KickAndBlacklistUser_ALREADY_BLACKLISTED;
4408 return true;
4409 }
4410
4411 if (blacklist)
4412 {
4413 if (clanMemberState!=CMD_UNDEFINED)
4414 {
4415 // Change status to banned
4416 result = pgsql->QueryVariadic(
4417 "UPDATE lobby2.clanMembers "
4418 "SET banReason=%s, memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Banned') "
4419 "WHERE userId_fk=%i AND clanId_fk=%i;", reason.C_String(), targetId, clanId);
4420 }
4421 else
4422 {
4423 // Add row as banned
4424 result = pgsql->QueryVariadic(
4425 "INSERT INTO lobby2.clanMembers (userId_fk, clanId_fk, isSubleader, memberState_fk, banReason) VALUES "
4426 "(%i, %i, false, (SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Banned'), %s );"
4427 , targetId, clanId, reason.C_String());
4428 }
4429 PQclear(result);
4430 }
4431 else if (kick)
4432 {
4433 // Remove from the clanMembers table
4434 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", targetId, clanId);
4435 PQclear(result);
4436 }
4437
4438 // Subleader and leader notification
4439 DataStructures::List<ClanMemberDescriptor> clanMembers;
4440 GetClanMembers(clanId, clanMembers, pgsql);
4441
4442 for (unsigned int i=0; i < clanMembers.Size(); i++)
4443 {
4444 if (clanMembers[i].memberState!=CMD_ACTIVE)
4445 continue;
4446 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
4447 continue;
4448 if (clanMembers[i].userId==targetId)
4449 continue;
4450
4451 Notification_Clans_KickAndBlacklistUser* notification = (Notification_Clans_KickAndBlacklistUser*) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_KickAndBlacklistUser);
4452 notification->clanHandle=clanHandle;
4453 notification->targetHandle=targetHandle;
4454 notification->blacklistingUserHandle=command->callingUserName;
4455 notification->targetHandleWasKicked=(clanMemberState!=CMD_UNDEFINED);
4456 notification->reason=reason;
4457 notification->resultCode=L2RC_SUCCESS;
4458 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
4459 }
4460
4461 // If user was in the clan, send Notification_Clans_Leave to them.
4462 if (clanMemberState!=CMD_UNDEFINED)
4463 {
4464 Notification_Clans_Leave *notification = (Notification_Clans_Leave *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_Leave);
4465 notification->clanHandle=clanHandle;
4466 notification->targetHandle=targetHandle;
4467 notification->resultCode=L2RC_SUCCESS;
4468 command->server->AddOutputFromThread(notification, targetId, targetHandle);
4469 }
4470
4471 // Send email to member
4472 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_KickAndBlacklistUser", pgsql);
4473
4474 resultCode=L2RC_SUCCESS;
4475 return true;
4476 }
4477
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4478 bool RakNet::Clans_UnblacklistUser_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4479 {
4480 (void)command;
4481
4482 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4483 PGresult *result=0;
4484
4485 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4486 if (clanId==0)
4487 {
4488 resultCode=L2RC_Clans_UnblacklistUser_UNKNOWN_CLAN;
4489 return true;
4490 }
4491
4492 bool isSubleader;
4493 if (GetClanMemberState(clanId, command->callerUserId, &isSubleader, pgsql)!=CMD_ACTIVE)
4494 {
4495 resultCode=L2RC_Clans_UnblacklistUser_NOT_IN_CLAN;
4496 return true;
4497 }
4498
4499 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4500
4501 if (isSubleader==false && clanLeaderId!=command->callerUserId)
4502 {
4503 resultCode=L2RC_Clans_UnblacklistUser_MUST_BE_LEADER_OR_SUBLEADER;
4504 return true;
4505 }
4506
4507 unsigned int targetId = RakNet::GetUserRowFromHandle(targetHandle, pgsql);
4508 if (targetId==0)
4509 {
4510 resultCode=L2RC_Clans_UnblacklistUser_UNKNOWN_TARGET_HANDLE;
4511 return true;
4512 }
4513
4514 if (GetClanMemberState(clanId, targetId, &isSubleader, pgsql)!=CMD_BANNED)
4515 {
4516 resultCode=L2RC_Clans_UnblacklistUser_NOT_BLACKLISTED;
4517 return true;
4518 }
4519
4520 // Remove from the clanMembers table
4521 result = pgsql->QueryVariadic("DELETE FROM lobby2.clanMembers WHERE userId_fk=%i AND clanId_fk=%i", targetId, clanId);
4522 PQclear(result);
4523
4524 // Subleader and leader notification
4525 DataStructures::List<ClanMemberDescriptor> clanMembers;
4526 GetClanMembers(clanId, clanMembers, pgsql);
4527
4528 for (unsigned int i=0; i < clanMembers.Size(); i++)
4529 {
4530 if (clanMembers[i].memberState!=CMD_ACTIVE)
4531 continue;
4532 if (clanMembers[i].isSubleader==false && clanMembers[i].userId!=clanLeaderId)
4533 continue;
4534
4535 Notification_Clans_UnblacklistUser *notification = (Notification_Clans_UnblacklistUser *) command->server->GetMessageFactory()->Alloc(L2MID_Notification_Clans_UnblacklistUser);
4536 notification->clanHandle=clanHandle;
4537 notification->targetHandle=targetHandle;
4538 notification->unblacklistingUserHandle=command->callingUserName;
4539 notification->resultCode=L2RC_SUCCESS;
4540 command->server->AddOutputFromThread(notification, clanMembers[i].userId, clanMembers[i].name);
4541 }
4542
4543 // Send email to member
4544 SendEmail(targetId, command->callerUserId, command->callingUserName, command->server, subject, body, binaryData, emailStatus, "Clans_UnblacklistUser", pgsql);
4545
4546 resultCode=L2RC_SUCCESS;
4547 return true;
4548 }
4549
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4550 bool RakNet::Clans_GetBlacklist_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4551 {
4552 (void)command;
4553
4554 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4555 PGresult *result=0;
4556
4557 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4558 if (clanId==0)
4559 {
4560 resultCode=L2RC_Clans_GetBlacklist_UNKNOWN_CLAN;
4561 return true;
4562 }
4563
4564 // Get the names of all banned members in this clan
4565 result = pgsql->QueryVariadic(
4566 "SELECT U.handle FROM lobby2.clanMembers AS M, lobby2.users AS U "
4567 "WHERE M.clanId_fk=%i AND M.userId_fk=U.userId_pk "
4568 "AND M.memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Banned');",
4569 clanId );
4570 if (result==0)
4571 {
4572 PQclear(result);
4573 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4574 return true;
4575 }
4576
4577 RakNet::RakString memberName;
4578 for (int i=0; i < PQntuples(result); i++)
4579 {
4580 PostgreSQLInterface::PQGetValueFromBinary(&memberName, result, i, "handle");
4581 blacklistedUsers.Insert(memberName, __FILE__, __LINE__ );
4582 }
4583 PQclear(result);
4584
4585 resultCode=L2RC_SUCCESS;
4586 return true;
4587 }
4588
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4589 bool RakNet::Clans_GetMembers_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4590 {
4591 (void)command;
4592
4593 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4594 PGresult *result=0;
4595
4596 unsigned int clanId = GetClanIdFromHandle(clanHandle, pgsql);
4597 if (clanId==0)
4598 {
4599 resultCode=L2RC_Clans_GetMembers_UNKNOWN_CLAN;
4600 return true;
4601 }
4602
4603 unsigned int clanLeaderId = GetClanLeaderId(clanId, pgsql);
4604
4605 result = pgsql->QueryVariadic("SELECT handle from lobby2.users WHERE userId_pk=%i", clanLeaderId);
4606 if (result==0)
4607 {
4608 PQclear(result);
4609 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4610 return true;
4611 }
4612 if (PQntuples(result)!=0)
4613 {
4614 PostgreSQLInterface::PQGetValueFromBinary(&clanLeader, result, 0, "handle");
4615 }
4616 PQclear(result);
4617
4618 // Get the names of all other active members in this clan
4619 result = pgsql->QueryVariadic(
4620 "SELECT U.handle FROM lobby2.clanMembers AS M, lobby2.users AS U "
4621 "WHERE M.clanId_fk=%i AND M.userId_fk!=%i AND M.userId_fk=U.userId_pk "
4622 "AND M.memberState_fk=(SELECT stateId_pk FROM lobby2.clanMemberStates WHERE description='ClanMember_Active');",
4623 clanId, clanLeaderId);
4624 if (result==0)
4625 {
4626 PQclear(result);
4627 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4628 return true;
4629 }
4630
4631 RakNet::RakString memberName;
4632 for (int i=0; i < PQntuples(result); i++)
4633 {
4634 PostgreSQLInterface::PQGetValueFromBinary(&memberName, result, i, "handle");
4635 clanMembersOtherThanLeader.Insert(memberName, __FILE__, __LINE__ );
4636 }
4637 PQclear(result);
4638
4639 resultCode=L2RC_SUCCESS;
4640 return true;
4641 }
4642
ServerDBImpl(Lobby2ServerCommand * command,void * databaseInterface)4643 bool RakNet::Clans_GetList_PGSQL::ServerDBImpl( Lobby2ServerCommand *command, void *databaseInterface )
4644 {
4645 (void)command;
4646
4647 PostgreSQLInterface *pgsql = (PostgreSQLInterface *)databaseInterface;
4648
4649 PGresult *result = pgsql->QueryVariadic("SELECT clanhandle from lobby2.clans");
4650 if (result==0)
4651 {
4652 resultCode=L2RC_DATABASE_CONSTRAINT_FAILURE;
4653 return true;
4654 }
4655
4656 RakNet::RakString clanName;
4657 for (int i=0; i < PQntuples(result); i++)
4658 {
4659 PostgreSQLInterface::PQGetValueFromBinary(&clanName, result, i, "clanhandle");
4660 clanNames.Insert(clanName, __FILE__, __LINE__ );
4661 }
4662 PQclear(result);
4663
4664 resultCode=L2RC_SUCCESS;
4665 return true;
4666 }
4667
4668
4669
4670
4671