/// <summary> /// Executes SQL update file. /// </summary> /// <param name="updateFile"></param> public void RunUpdate(string updateFile) { try { using (var conn = this.Connection) { // Run update using (var cmd = new MySqlCommand(File.ReadAllText(Path.Combine("sql", updateFile)), conn)) cmd.ExecuteNonQuery(); // Log update using (var cmd = new InsertCommand("INSERT INTO `updates` {0}", conn)) { cmd.Set("path", updateFile); cmd.Execute(); } Log.Info("Successfully applied '{0}'.", updateFile); } } catch (Exception ex) { Log.Error("RunUpdate: Failed to run '{0}': {1}", updateFile, ex.Message); CliUtil.Exit(1); } }
/// <summary> /// Returns a user for the given values, either from the db, /// or by creating a new one. /// </summary> /// <param name="accountId"></param> /// <param name="characterEntityId"></param> /// <param name="characterName"></param> /// <param name="server"></param> /// <param name="channelName"></param> /// <returns></returns> public User GetOrCreateContact(string accountId, long characterEntityId, string characterName, string server, string channelName) { using (var conn = this.Connection) { var user = new User(); user.AccountId = accountId; user.CharacterId = characterEntityId; user.Name = characterName; user.Server = server; user.ChannelName = channelName; user.Status = ContactStatus.Online; user.ChatOptions = ChatOptions.NotifyOnFriendLogIn; user.LastLogin = DateTime.Now; // Try to get contact from db using (var mc = new MySqlCommand("SELECT * FROM `contacts` WHERE `characterEntityId` = @characterEntityId", conn)) { mc.Parameters.AddWithValue("@characterEntityId", characterEntityId); using (var reader = mc.ExecuteReader()) { if (reader.Read()) { user.Id = reader.GetInt32("contactId"); user.Status = (ContactStatus)reader.GetByte("status"); user.ChatOptions = (ChatOptions)reader.GetUInt32("chatOptions"); user.Nickname = reader.GetStringSafe("nickname") ?? ""; user.LastLogin = reader.GetDateTimeSafe("lastLogin"); if (!Enum.IsDefined(typeof(ContactStatus), user.Status) || user.Status == ContactStatus.None) user.Status = ContactStatus.Online; this.UpdateLastLogin(user); return user; } } } // Create new contact using (var cmd = new InsertCommand("INSERT INTO `contacts` {0}", conn)) { cmd.Set("accountId", accountId); cmd.Set("characterEntityId", characterEntityId); cmd.Set("characterName", characterName); cmd.Set("server", server); cmd.Set("status", (byte)user.Status); cmd.Set("chatOptions", (uint)user.ChatOptions); cmd.Set("nickname", ""); cmd.Set("lastLogin", user.LastLogin); cmd.Execute(); user.Id = (int)cmd.LastId; return user; } } }
/// <summary> /// Adds group to database. /// </summary> /// <param name="user"></param> /// <param name="groupId"></param> /// <param name="groupName"></param> public void AddGroup(User user, Group group) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `groups` {0}", conn)) { cmd.Set("groupId", group.Id); cmd.Set("contactId", user.Id); cmd.Set("name", group.Name); cmd.Execute(); } }
/// <summary> /// Creates friend entry for contact on user with the blacklist status. /// </summary> /// <param name="userId"></param> /// <param name="contactId"></param> public void Blacklist(int userId, int contactId) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `friends` {0}", conn)) { cmd.Set("userId1", userId); cmd.Set("userId2", contactId); cmd.Set("groupId", -4); cmd.Set("status", (byte)FriendshipStatus.Blacklist); cmd.Execute(); } }
/// <summary> /// Adds note to database. /// </summary> /// <param name="noteId"></param> public void AddNote(string sender, string receiver, string message) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `notes` {0}", conn)) { cmd.Set("sender", sender); cmd.Set("receiver", receiver); cmd.Set("message", message); cmd.Set("time", DateTime.Now); cmd.Execute(); } }
/// <summary> /// Adds new account to the database. /// </summary> /// <param name="accountId"></param> /// <param name="password"></param> public void CreateAccount(string accountId, string password) { password = Password.Hash(password); using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `accounts` {0}", conn)) { cmd.Set("accountId", accountId); cmd.Set("password", password); cmd.Set("creation", DateTime.Now); cmd.Execute(); } }
/// <summary> /// Writes guild member to database. /// </summary> /// <param name="member"></param> public void AddGuildMember(GuildMember member) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `guild_members` {0}", conn)) { cmd.Set("guildId", member.GuildId); cmd.Set("characterId", member.CharacterId); cmd.Set("rank", (int)member.Rank); cmd.Set("joinedDate", member.JoinedDate); cmd.Set("application", member.Application); cmd.Set("messages", (int)member.Messages); cmd.Execute(); } }
/// <summary> /// Returns a contact for the given values, either from the db, /// or by creating a new one. /// </summary> /// <param name="accountId"></param> /// <param name="characterEntityId"></param> /// <param name="characterName"></param> /// <param name="server"></param> /// <param name="channelName"></param> /// <returns></returns> public Contact GetOrCreateContact(string accountId, long characterEntityId, string characterName, string server, string channelName) { using (var conn = this.Connection) { var contact = new Contact(); contact.AccountId = accountId; contact.Name = characterName; contact.Server = server; contact.ChannelName = channelName; // Try to get contact from db using (var mc = new MySqlCommand("SELECT * FROM `contacts` WHERE `characterEntityId` = @characterEntityId", conn)) { mc.Parameters.AddWithValue("@characterEntityId", characterEntityId); using (var reader = mc.ExecuteReader()) { if (reader.Read()) { contact.Id = reader.GetInt32("contactId"); return contact; } } } // Create new contact using (var cmd = new InsertCommand("INSERT INTO `contacts` {0}", conn)) { cmd.Set("accountId", accountId); cmd.Set("characterEntityId", characterEntityId); cmd.Set("characterName", characterName); cmd.Set("server", server); cmd.Execute(); contact.Id = (int)cmd.LastId; return contact; } } }
/// <summary> /// Adds guild to db and sets its id. /// </summary> /// <param name="guild"></param> public void AddGuild(Guild guild) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `guilds` {0}", conn)) { cmd.Set("name", guild.Name); cmd.Set("leaderName", guild.LeaderName); cmd.Set("title", guild.Title); cmd.Set("establishedDate", guild.EstablishedDate); cmd.Set("server", guild.Server); cmd.Set("introMessage", guild.IntroMessage); cmd.Set("welcomeMessage", guild.WelcomeMessage); cmd.Set("leavingMessage", guild.LeavingMessage); cmd.Set("rejectionMessage", guild.RejectionMessage); cmd.Set("type", (int)guild.Type); cmd.Set("visibility", (int)guild.Visibility); cmd.Execute(); guild.Id = cmd.LastId; } }
/// <summary> /// Adds trade item and points of card to character. /// </summary> /// <param name="targetCharacter"></param> /// <param name="charCard"></param> public void TradeCard(Character targetCharacter, CharCardData charCard) { using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `items` {0}", conn)) { cmd.Set("creatureId", targetCharacter.CreatureId); cmd.Set("itemId", charCard.TradeItem); cmd.Set("pocket", Pocket.Temporary); cmd.Set("color1", 0x808080); cmd.Set("color2", 0x808080); cmd.Set("color3", 0x808080); cmd.Execute(); } // TODO: Add points (pons)... }
/// <summary> /// Adds items for creature. /// </summary> /// <param name="creatureId"></param> /// <param name="items"></param> /// <param name="conn"></param> /// <param name="transaction"></param> private void AddItems(long creatureId, List<Item> items, MySqlConnection conn, MySqlTransaction transaction) { foreach (var item in items) { var dataInfo = AuraData.ItemDb.Find(item.Info.Id); if (dataInfo == null) { Log.Warning("Item '{0}' couldn't be found in the database.", item.Info.Id); continue; } using (var cmd = new InsertCommand("INSERT INTO `items` {0}", conn, transaction)) { cmd.Set("creatureId", creatureId); cmd.Set("itemId", item.Info.Id); cmd.Set("pocket", (byte)item.Info.Pocket); cmd.Set("color1", item.Info.Color1); cmd.Set("color2", item.Info.Color2); cmd.Set("color3", item.Info.Color3); cmd.Set("price", dataInfo.Price); cmd.Set("durability", dataInfo.Durability); cmd.Set("durabilityMax", dataInfo.Durability); cmd.Set("durabilityOriginal", dataInfo.Durability); cmd.Set("attackMin", dataInfo.AttackMin); cmd.Set("attackMax", dataInfo.AttackMax); cmd.Set("balance", dataInfo.Balance); cmd.Set("critical", dataInfo.Critical); cmd.Set("defense", dataInfo.Defense); cmd.Set("protection", dataInfo.Protection); cmd.Set("attackSpeed", dataInfo.AttackSpeed); cmd.Set("sellPrice", dataInfo.SellingPrice); cmd.Execute(); } } }
/// <summary> /// Creatures creature based on character and returns its id. /// </summary> /// <param name="creature"></param> /// <param name="conn"></param> /// <param name="transaction"></param> /// <returns></returns> private long CreateCreature(Character creature, MySqlConnection conn, MySqlTransaction transaction) { using (var cmd = new InsertCommand("INSERT INTO `creatures` {0}", conn, transaction)) { cmd.Set("server", creature.Server); cmd.Set("name", creature.Name); cmd.Set("age", creature.Age); cmd.Set("race", creature.Race); cmd.Set("skinColor", creature.SkinColor); cmd.Set("eyeType", creature.EyeType); cmd.Set("eyeColor", creature.EyeColor); cmd.Set("mouthType", creature.MouthType); cmd.Set("state", (uint)creature.State); cmd.Set("height", creature.Height); cmd.Set("weight", creature.Weight); cmd.Set("upper", creature.Upper); cmd.Set("lower", creature.Lower); cmd.Set("color1", creature.Color1); cmd.Set("color2", creature.Color2); cmd.Set("color3", creature.Color3); cmd.Set("lifeMax", creature.Life); cmd.Set("manaMax", creature.Mana); cmd.Set("staminaMax", creature.Stamina); cmd.Set("str", creature.Str); cmd.Set("int", creature.Int); cmd.Set("dex", creature.Dex); cmd.Set("will", creature.Will); cmd.Set("luck", creature.Luck); cmd.Set("defense", creature.Defense); cmd.Set("protection", creature.Protection); cmd.Set("ap", creature.AP); cmd.Set("creationTime", DateTime.Now); cmd.Set("lastAging", DateTime.Now); cmd.Execute(); return cmd.LastId; } }
/// <summary> /// Creates creature:x combination for the account. /// Returns false if either failed, true on success. /// character's ids are set to the new ids. /// </summary> /// <param name="table"></param> /// <param name="accountId"></param> /// <param name="character"></param> /// <param name="items"></param> /// <returns></returns> private bool Create(string table, string accountId, Character character, List<Item> items) { using (var conn = this.Connection) using (var transaction = conn.BeginTransaction()) { try { // Creature character.CreatureId = this.CreateCreature(character, conn, transaction); // Character using (var cmd = new InsertCommand("INSERT INTO `" + table + "` {0}", conn, transaction)) { cmd.Set("accountId", accountId); cmd.Set("creatureId", character.CreatureId); cmd.Execute(); character.EntityId = cmd.LastId; } // Items if (items != null) this.AddItems(character.CreatureId, items, conn, transaction); transaction.Commit(); return true; } catch (Exception ex) { character.EntityId = character.CreatureId = 0; Log.Exception(ex); return false; } } }
/// <summary> /// Creates friend entries for user and friend with status inviting/invited. /// </summary> /// <param name="userId"></param> /// <param name="friendId"></param> public void InviteFriend(int userId, int friendId) { using (var conn = this.Connection) using (var transaction = conn.BeginTransaction()) { using (var cmd = new InsertCommand("INSERT INTO `friends` {0}", conn, transaction)) { cmd.Set("userId1", userId); cmd.Set("userId2", friendId); cmd.Set("groupId", -1); cmd.Set("status", (byte)FriendshipStatus.Inviting); cmd.Execute(); } using (var cmd = new InsertCommand("INSERT INTO `friends` {0}", conn, transaction)) { cmd.Set("userId1", friendId); cmd.Set("userId2", userId); cmd.Set("groupId", -1); cmd.Set("status", (byte)FriendshipStatus.Invited); cmd.Execute(); } transaction.Commit(); } }
/// <summary> /// Adds trade item and points of card to character. /// </summary> /// <param name="account"></param> /// <param name="targetCharacter"></param> /// <param name="charCard"></param> public void TradeCard(Account account, Character targetCharacter, CharCardData charCard) { // Add item using (var conn = this.Connection) using (var cmd = new InsertCommand("INSERT INTO `items` {0}", conn)) { cmd.Set("creatureId", targetCharacter.CreatureId); cmd.Set("itemId", charCard.TradeItem); cmd.Set("pocket", Pocket.Temporary); cmd.Set("color1", 0x808080); cmd.Set("color2", 0x808080); cmd.Set("color3", 0x808080); cmd.Execute(); } // Add points using (var conn = this.Connection) using (var cmd = new InsertCommand("UPDATE `accounts` SET `points` = `points` + @points WHERE `accountId` = @accountId", conn)) { cmd.Set("accountId", account.Name); cmd.Set("points", charCard.TradePoints); cmd.Execute(); } }