/// <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 = AuraDb.Instance.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>
/// 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 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();
}
}
