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(&currentRating, 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