public static void DeleteRoom(SqlDatabaseClient MySqlClient, uint RoomId)
{
MySqlClient.SetParameter("id", RoomId);
MySqlClient.ExecuteNonQuery("DELETE FROM rooms WHERE id = @id LIMIT 1");
MySqlClient.SetParameter("id", RoomId);
MySqlClient.ExecuteNonQuery("DELETE FROM navigator_frontpage WHERE room_id = @id");
RoomInfoLoader.RemoveFromCache(RoomId);
Navigator.ReloadOfficialItems(MySqlClient);
}
public static void CreateFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2, bool Confirmed)
{
for (int i = 0; i < (Confirmed ? 2 : 1); i++)
{
MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
MySqlClient.SetParameter("accepted", (int)(Confirmed ? 1 : 0));
MySqlClient.ExecuteNonQuery("INSERT INTO messenger_friendships (user_1_id,user_2_id,confirmed) VALUES (@user1,@user2,@accepted)");
}
}
public static bool DestroyFriendship(SqlDatabaseClient MySqlClient, uint UserId1, uint UserId2)
{
int aff = 0;
for (int i = 0; i < 2; i++)
{
MySqlClient.SetParameter("user1", (i == 1 ? UserId1 : UserId2));
MySqlClient.SetParameter("user2", (i == 1 ? UserId2 : UserId1));
aff += MySqlClient.ExecuteNonQuery("DELETE FROM messenger_friendships WHERE user_1_id = @user1 AND user_2_id = @user2 LIMIT 1");
}
return (aff > 0);
}
public static void BanUser(SqlDatabaseClient MySqlClient, uint UserId, string MessageText, uint ModeratorId, double Length)
{
MySqlClient.SetParameter("userid", UserId);
MySqlClient.SetParameter("reason", MessageText);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.SetParameter("timestampex", UnixTimestamp.GetCurrent() + Length);
MySqlClient.SetParameter("moderator", ModeratorId);
MySqlClient.ExecuteNonQuery("INSERT INTO bans (user_id,reason_text,timestamp_created,timestamp_expire,moderator_id) VALUES (@userid,@reason,@timestamp,@timestampex,@moderator)");
lock (mSyncRoot)
{
mCharacterBlacklist.Add(UserId);
}
}
public void AddOrUpdateData(SqlDatabaseClient MySqlClient, uint QuestId, int Progress, bool ActiveQuest)
{
if (ActiveQuest)
{
if (mCurrentQuest > 0)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("questid", mCurrentQuest);
MySqlClient.ExecuteNonQuery("UPDATE user_quests SET is_current = '0' WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
}
mCurrentQuest = QuestId;
}
else if (mCurrentQuest == QuestId)
{
mCurrentQuest = 0;
}
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("questid", QuestId);
MySqlClient.SetParameter("progress", Progress);
MySqlClient.SetParameter("iscurrent", ActiveQuest ? "1" : "0");
lock (mSyncRoot)
{
if (mInner.ContainsKey(QuestId))
{
mInner[QuestId] = Progress;
MySqlClient.ExecuteNonQuery("UPDATE user_quests SET progress = @progress, is_current = @iscurrent WHERE user_id = @userid AND quest_id = @questid LIMIT 1");
}
else
{
mInner.Add(QuestId, Progress);
MySqlClient.ExecuteNonQuery("INSERT INTO user_quests (user_id,quest_id,progress,is_current) VALUES (@userid,@questid,@progress,@iscurrent)");
}
}
}
public void AddOrUpdateData(SqlDatabaseClient MySqlClient, string Group, int Level, int Progress)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("group", Group);
MySqlClient.SetParameter("level", Level);
MySqlClient.SetParameter("progress", Progress);
lock (mSyncRoot)
{
if (mInner.ContainsKey(Group))
{
mInner[Group].Level = Level;
mInner[Group].Progress = Progress;
MySqlClient.ExecuteNonQuery("UPDATE user_achievements SET level = @level, progress = @progress WHERE user_id = @userid AND group_id = @group LIMIT 1");
}
else
{
mInner.Add(Group, new UserAchievement(Group, Level, Progress));
MySqlClient.ExecuteNonQuery("INSERT INTO user_achievements (user_id,group_id,level,progress) VALUES (@userid,@group,@level,@progress)");
}
}
}
public void SetWardrobeSlot(SqlDatabaseClient MySqlClient, int SlotId, string Figure, CharacterGender Gender)
{
lock (mWardrobe)
{
WardrobeItem Item = new WardrobeItem(Figure, Gender);
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("slotid", SlotId);
MySqlClient.SetParameter("figure", Figure);
MySqlClient.SetParameter("gender", Gender == CharacterGender.Male ? "M" : "F");
if (!mWardrobe.ContainsKey(SlotId))
{
mWardrobe.Add(SlotId, Item);
MySqlClient.ExecuteNonQuery("INSERT INTO wardrobe (user_id,slot_id,figure,gender) VALUES (@userid,@slotid,@figure,@gender)");
return;
}
mWardrobe[SlotId] = Item;
MySqlClient.ExecuteNonQuery("UPDATE wardrobe SET figure = @figure, gender = @gender WHERE user_id = @userid AND slot_id = @slotid LIMIT 1");
}
}
public void UpdateScore(SqlDatabaseClient MySqlClient, int Amount)
{
mScore += Amount;
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("score", mScore);
MySqlClient.ExecuteNonQuery("UPDATE characters SET score = @score WHERE id = @userid LIMIT 1");
}
public void UpdateMotto(SqlDatabaseClient MySqlClient, string NewMotto)
{
mMotto = NewMotto;
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("motto", NewMotto);
MySqlClient.ExecuteNonQuery("UPDATE characters SET motto = @motto WHERE id = @userid LIMIT 1");
}
public void UpdateFigure(SqlDatabaseClient MySqlClient, string NewGender, string NewFigure)
{
mGender = (NewGender == "m" ? CharacterGender.Male : CharacterGender.Female);
mFigure = NewFigure;
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("figure", mFigure);
MySqlClient.SetParameter("gender", NewGender);
MySqlClient.ExecuteNonQuery("UPDATE characters SET gender = @gender, figure = @figure WHERE id = @userid LIMIT 1");
}
public void UpdateStatus(SqlDatabaseClient MySqlClient, ModerationTicketStatus NewStatus)
{
mStatus = NewStatus;
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("status", (int)NewStatus);
MySqlClient.ExecuteNonQuery("UPDATE moderation_tickets SET status = @status WHERE id = @id LIMIT 1");
}
/// <summary>
/// Removes the SSO (Single Sign On) ticket from the database after a successful login attempt.
/// </summary>
/// <param name="UserId">The character id to remove the ticket from.</param>
private static void RemoveTicket(SqlDatabaseClient MySqlClient, uint UserId, string AddressToLog)
{
MySqlClient.SetParameter("id", UserId);
MySqlClient.SetParameter("lastip", AddressToLog);
MySqlClient.SetParameter("lastonline", UnixTimestamp.GetCurrent());
MySqlClient.ExecuteNonQuery("UPDATE characters SET auth_ticket = '', last_ip = @lastip, timestamp_lastvisit = @lastonline WHERE id = @id LIMIT 1");
}
public void SynchronizeDatabase(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mItemId);
MySqlClient.SetParameter("data1", mData1);
MySqlClient.SetParameter("data2", mData2);
MySqlClient.SetParameter("data3", mData3);
MySqlClient.SetParameter("data4", mData4);
MySqlClient.SetParameter("data5", mData5);
MySqlClient.SetParameter("time", mTime);
MySqlClient.ExecuteNonQuery("UPDATE wired_items SET data1 = @data1, data2 = @data2, data3 = @data3, data4 = @data4, data5 = @data5, time = @time WHERE item_id = @id LIMIT 1");
}
public void SetLastActivityPointsUpdate(SqlDatabaseClient MySqlClient)
{
mTimestampLastActivityPointsUpdate = UnixTimestamp.GetCurrent();
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("aplu", mTimestampLastActivityPointsUpdate);
MySqlClient.ExecuteNonQuery("UPDATE characters SET activity_points_last_update = @aplu WHERE id = @id LIMIT 1");
}
public void ApplyDecoration(SqlDatabaseClient MySqlClient, string Key, string Value)
{
lock (mSyncRoot)
{
if (!mDecorations.ContainsKey(Key))
{
mDecorations.Add(Key, Value);
}
else
{
mDecorations[Key] = Value;
}
StringBuilder DecorationString = new StringBuilder();
foreach (KeyValuePair<string, string> Decoration in mDecorations)
{
if (DecorationString.Length > 0)
{
DecorationString.Append('|');
}
DecorationString.Append(Decoration.Key);
DecorationString.Append('=');
DecorationString.Append(Decoration.Value);
}
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("decorations", DecorationString);
MySqlClient.ExecuteNonQuery("UPDATE rooms SET decorations = @decorations WHERE id = @id LIMIT 1");
}
}
public void SetHomeRoom(SqlDatabaseClient MySqlClient, uint RoomId)
{
mHomeRoom = RoomId;
MySqlClient.SetParameter("userid", mId);
MySqlClient.SetParameter("roomid", RoomId);
MySqlClient.ExecuteNonQuery("UPDATE characters SET home_room = @roomid WHERE id = @userid LIMIT 1");
}
public void Mute(SqlDatabaseClient MySqlClient, int TimeToMute)
{
mModerationMutedUntil = UnixTimestamp.GetCurrent() + TimeToMute;
// Maintain in database if this mute lasts longer than 3 minutes
if (TimeToMute >= 180)
{
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("mutetime", mModerationMutedUntil);
MySqlClient.ExecuteNonQuery("UPDATE characters SET moderation_muted_until_timestamp = @mutetime WHERE id = @id LIMIT 1");
}
}
public void UpdateBadgeOrder(SqlDatabaseClient MySqlClient, Dictionary<int, Badge> NewSettings)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.ExecuteNonQuery("UPDATE user_badges SET slot_id = 0 WHERE user_id = @userid");
foreach (KeyValuePair<int, Badge> EquippedBadge in NewSettings)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("slotid", EquippedBadge.Key);
MySqlClient.SetParameter("badgecode", EquippedBadge.Value.Code);
MySqlClient.ExecuteNonQuery("UPDATE user_badges SET slot_id = @slotid WHERE user_id = @userid AND badge_code = @badgecode LIMIT 1");
}
lock (mSyncRoot)
{
mEquippedBadges = NewSettings;
}
}
public void UpdateAchievementBadge(SqlDatabaseClient MySqlClient, string AchievementGroup, Badge NewBadge)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("sourcetype", "achievement");
MySqlClient.SetParameter("sourcedata", AchievementGroup);
MySqlClient.SetParameter("badgecode", NewBadge.Code);
lock (mSyncRoot)
{
if (mAchievementBadges.ContainsKey(AchievementGroup))
{
Badge OldBadge = mAchievementBadges[AchievementGroup];
if (OldBadge == NewBadge)
{
MySqlClient.ClearParameters();
return;
}
mIndexCache.Remove(OldBadge.Code);
mAchievementBadges[AchievementGroup] = NewBadge;
MySqlClient.ExecuteNonQuery("UPDATE badges SET badge_code = @badgecode WHERE user_id = @userid AND source_type = @sourcetype AND source_data = @sourcedata LIMIT 1");
foreach (KeyValuePair<int, Badge> Badge in mEquippedBadges)
{
if (Badge.Value.Code == OldBadge.Code)
{
mEquippedBadges[Badge.Key] = NewBadge;
break;
}
}
}
else
{
mAchievementBadges.Add(AchievementGroup, NewBadge);
MySqlClient.ExecuteNonQuery("INSERT INTO user_badges (user_id,badge_code,source_type,source_data) VALUES (@userid,@badgecode,@sourcetype,@sourcedata)");
}
mIndexCache.Add(NewBadge.Code);
}
}
public void ReloadCache(SqlDatabaseClient MySqlClient, AchievementCache UserAchievementCache)
{
Dictionary<int, Badge> EquippedBadges = new Dictionary<int, Badge>();
List<Badge> StaticBadges = new List<Badge>();
Dictionary<string, Badge> AchievementBadges = new Dictionary<string, Badge>();
List<string> IndexCache = new List<string>();
MySqlClient.SetParameter("userid", mUserId);
DataTable Table = MySqlClient.ExecuteQueryTable("SELECT badge_code,slot_id,source_type,source_data FROM user_badges WHERE user_id = @userid");
foreach (DataRow Row in Table.Rows)
{
string SourceType = Row["source_type"].ToString();
string SourceData = Row["source_data"].ToString();
Badge BadgeToEquip = null;
if (SourceType == "static")
{
BadgeToEquip = new Badge(Row["badge_code"].ToString());
StaticBadges.Add(BadgeToEquip);
}
else if (SourceType == "achievement")
{
if (AchievementBadges.ContainsKey(SourceData))
{
continue;
}
UserAchievement UserAchievement = UserAchievementCache.GetAchievementData(SourceData);
if (UserAchievement == null || UserAchievement.Level < 1)
{
MySqlClient.SetParameter("userid", mUserId);
MySqlClient.SetParameter("badgecode", Row["badge_code"].ToString());
MySqlClient.ExecuteNonQuery("DELETE FROM user_badges WHERE user_id = @userid AND badge_id = @badgeid");
continue;
}
string Code = UserAchievement.GetBadgeCodeForLevel();
BadgeToEquip = new Badge(Code);
AchievementBadges.Add(SourceData, BadgeToEquip);
}
if (BadgeToEquip != null)
{
int SlotId = (int)Row["slot_id"];
if (!EquippedBadges.ContainsKey(SlotId) && SlotId >= 1 && SlotId <= 5)
{
EquippedBadges.Add(SlotId, BadgeToEquip);
}
IndexCache.Add(BadgeToEquip.Code);
}
}
lock (mSyncRoot)
{
mEquippedBadges = EquippedBadges;
mStaticBadges = StaticBadges;
mAchievementBadges = AchievementBadges;
mIndexCache = IndexCache;
}
}
private static void PerformDatabaseCleanup(SqlDatabaseClient MySqlClient)
{
MySqlClient.ExecuteNonQuery("UPDATE rooms SET current_users = 0");
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.ExecuteNonQuery("UPDATE room_visits SET timestamp_left = @timestamp WHERE timestamp_left = 0");
MySqlClient.ExecuteNonQuery("UPDATE characters SET auth_ticket = ''");
}
public void SynchronizeRespectData(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("respectpts", mRespectPoints);
MySqlClient.SetParameter("respectcredh", mRespectCreditHuman);
MySqlClient.SetParameter("respectcredp", mRespectCreditPets);
MySqlClient.ExecuteNonQuery("UPDATE characters SET respect_points = @respectpts, respect_credit_humans = @respectcredh, respect_credit_pets = @respectcredp WHERE id = @id LIMIT 1");
}
private static void PerformDatabaseCleanup(SqlDatabaseClient MySqlClient)
{
MySqlClient.ExecuteNonQuery("UPDATE rooms SET current_users = 0");
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.ExecuteNonQuery("UPDATE room_visits SET timestamp_left = @timestamp WHERE timestamp_left = 0");
MySqlClient.ExecuteNonQuery("UPDATE characters SET auth_ticket = ''");
MySqlClient.ExecuteNonQuery("UPDATE characters SET online = '0'");
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.ExecuteNonQuery("UPDATE server_statistics SET sval = @timestamp WHERE skey = 'stamp' LIMIT 1");
MySqlClient.ExecuteNonQuery("UPDATE server_statistics SET sval = '1' WHERE skey = 'online_state' LIMIT 1");
}
public void SynchronizeStatistics(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("timestamp", UnixTimestamp.GetCurrent());
MySqlClient.SetParameter("tickets", mModerationTickets);
MySqlClient.SetParameter("ticketsabuse", mModerationTicketsAbusive);
MySqlClient.SetParameter("cooldown", mModerationTicketsCooldown);
MySqlClient.SetParameter("bans", mModerationBans);
MySqlClient.SetParameter("cautions", mModerationCautions);
MySqlClient.ExecuteNonQuery("UPDATE characters SET timestamp_lastvisit = @timestamp, moderation_tickets = @tickets, moderation_tickets_abusive = @ticketsabuse, moderation_tickets_cooldown = @cooldown, moderation_bans = @bans, moderation_cautions = @cautions WHERE id = @id LIMIT 1");
}
public void Stop(SqlDatabaseClient MySqlClient)
{
if (Stopped)
{
return;
}
mSocket.Close();
mSocket = null;
if (Authenticated)
{
mCharacterInfo.SynchronizeStatistics(MySqlClient);
if (CurrentRoomId > 0)
{
RoomManager.RemoveUserFromRoom(this, false);
}
MessengerHandler.MarkUpdateNeeded(this, 0, true);
MySqlClient.ExecuteNonQuery("UPDATE characters SET online = '0' WHERE id = " + CharacterId + " LIMIT 1");
}
Output.WriteLine("Stopped and disconnected client " + Id + ".", OutputLevel.DebugInformation);
mStoppedTimestamp = UnixTimestamp.GetCurrent();
}
public void Unmute(SqlDatabaseClient MySqlClient)
{
mModerationMutedUntil = 0;
MySqlClient.SetParameter("id", mId);
MySqlClient.ExecuteNonQuery("UPDATE characters SET moderation_muted_until_timestamp = 0 WHERE id = @id LIMIT 1");
}
/// <summary>
/// Attemps to authenticate an user using an SSO (Single Sign On) ticket.
/// </summary>
/// <param name="Ticket">The ticket string.</param>
/// <returns>Character id on success, 0 on authentication failure.</returns>
public static uint TryAuthenticate(SqlDatabaseClient MySqlClient, string Ticket, string RemoteAddress)
{
lock (mAuthSyncRoot)
{
// Remove any spacing from single sign on ticket
Ticket = Ticket.Trim();
// Ensure the ticket meets the minimum length requirement
if (Ticket.Length <= 5)
{
mFailedLoginCount++;
Output.WriteLine("Login from " + RemoteAddress + " rejected: SSO ticket too short.");
return 0;
}
// Debug
string DebugTicket = (string)ConfigManager.GetValue("debug.sso");
if (DebugTicket.Length > 0 && Ticket == DebugTicket)
return 1;
// Check the database for a matching single sign on ticket
uint UserId = 0;
string LogName = string.Empty;
MySqlClient.SetParameter("ticket", Ticket);
DataRow Row = MySqlClient.ExecuteQueryRow("SELECT id,username FROM characters WHERE auth_ticket = @ticket LIMIT 1");
if (Row != null)
{
UserId = (uint)Row["id"];
LogName = (string)Row["username"];
RemoveTicket(MySqlClient, (uint)Row["id"], RemoteAddress);
}
// Check if ticket was OK + Check for user id bans
if (UserId <= 0)
{
mFailedLoginCount++;
Output.WriteLine("Login from " + RemoteAddress + " rejected: invalid SSO ticket.");
return 0;
}
if (ModerationBanManager.IsUserIdBlacklisted(UserId))
{
mFailedLoginCount++;
Output.WriteLine("Login from " + RemoteAddress + " rejected: blacklisted IP address.");
return 0;
}
// Disconnect any previous sessions for this account
if (SessionManager.ContainsCharacterId(UserId))
{
Session TargetSession = SessionManager.GetSessionByCharacterId(UserId);
SessionManager.StopSession(TargetSession.Id);
}
// Mark as a successful login and continue
Output.WriteLine("User " + LogName + " (ID " + UserId + ") has logged in from " + RemoteAddress + ".");
MySqlClient.ExecuteNonQuery("UPDATE characters SET online = '1' WHERE id = " + UserId + " LIMIT 1");
mSuccessfulLoginCount++;
return UserId;
}
}
public void UpdateActivityPointsBalance(SqlDatabaseClient MySqlClient, int Amount)
{
ActivityPointsBalance += Amount;
MySqlClient.SetParameter("id", Id);
MySqlClient.SetParameter("ap", ActivityPointsBalance);
MySqlClient.ExecuteNonQuery("UPDATE characters SET activity_points_balance = @ap WHERE id = @id LIMIT 1");
}
public void SynchronizeDatabase(SqlDatabaseClient MySqlClient)
{
MySqlClient.SetParameter("id", mId);
MySqlClient.SetParameter("roomid", mRoomId);
MySqlClient.SetParameter("roompos", mRoomPosition.ToString());
MySqlClient.SetParameter("exp", mExperience);
MySqlClient.SetParameter("energy", mEnergy);
MySqlClient.SetParameter("happy", mHappiness);
MySqlClient.SetParameter("score", mScore);
MySqlClient.ExecuteNonQuery("UPDATE pets SET room_id = @roomid, room_pos = @roompos, experience = @exp, energy = @energy, happiness = @happy, score = @score WHERE id = @id LIMIT 1");
}
public void UpdateCreditsBalance(SqlDatabaseClient MySqlClient, int Amount)
{
CreditsBalance += Amount;
MySqlClient.SetParameter("id", Id);
MySqlClient.SetParameter("credits", CreditsBalance);
MySqlClient.ExecuteNonQuery("UPDATE characters SET credits_balance = @credits WHERE id = @id LIMIT 1");
}
