public void flush(ISL.Server.Account.Account account)
{
//assert(account.getID() >= 0);
#if !DEBUG
try
{
#endif
mDb.StartTransaction();
//PerformTransaction transaction(mDb);
// Update the account
string sqlUpdateAccountTable=String.Format("UPDATE {0} SET username=\"{1}\", password=\"{2}\", email=\"{3}\", level=\"{4}\", lastlogin=\"{5}\" WHERE id = {6}",
ACCOUNTS_TBL_NAME, account.getName(), account.getPassword(), account.getEmail(), account.getLevel(), account.getLastLogin(), account.getID());
mDb.ExecuteNonQuery(sqlUpdateAccountTable);
// Get the list of characters that belong to this account.
Dictionary<uint, Character> characters=account.getCharacters();
// Insert or update the characters.
foreach(KeyValuePair<uint, Character> pair in characters)
{
Character character=pair.Value;
if(character.getDatabaseID()>=0)
{
updateCharacter(character);
}
else
{
// Insert the character
// This assumes that the characters name has been checked for
// uniqueness
string sqlInsertCharactersTable=String.Format("insert into {0} (user_id, name, gender, hair_style, hair_color, level, char_pts, correct_pts, x, y, map_id, slot) values (", CHARACTERS_TBL_NAME);
sqlInsertCharactersTable+=String.Format("{0}, \"{1}\", {2}, {3}, {4}, ", account.getID(), character.getName(), character.getGender(), (int)character.getHairStyle(), (int)character.getHairColor());
sqlInsertCharactersTable+=String.Format("{0}, {1}, {2}, ", (int)character.getLevel(), character.getCharacterPoints(), character.getCorrectionPoints());
sqlInsertCharactersTable+=String.Format("{0}, {1}, {2}, {3});", character.getPosition().x, character.getPosition().y, character.getMapId(), character.getCharacterSlot());
//mDb.ExecuteNonQuery(sqlInsertCharactersTable);
mDb.ExecuteNonQuery(sqlInsertCharactersTable);
//charID ermitteln
string sqlGetCharId=String.Format("SELECT id FROM {0} WHERE user_id={1} AND name='{2}'", CHARACTERS_TBL_NAME, account.getID(), character.getName());
DataTable tmp=mDb.ExecuteQuery(sqlGetCharId);
int lastID=Convert.ToInt32(tmp.Rows[0]["id"]);
// Update the character ID.
character.setDatabaseID(lastID);
// Update all attributes.
foreach(KeyValuePair<uint, AttributeValue> attributePair in character.mAttributes)
{
updateAttribute(character.getDatabaseID(), attributePair.Key, attributePair.Value.@base, attributePair.Value.modified);
}
// Update the characters skill
foreach(KeyValuePair<int, int> experiencePair in character.mExperience)
{
updateExperience(character.getDatabaseID(), experiencePair.Key, experiencePair.Value);
}
}
}
// Existing characters in memory have been inserted
// or updated in database.
// Now, let's remove those who are no more in memory from database.
string sqlSelectNameIdCharactersTable=String.Format("select name, id from {0} where user_id = '{1}';", CHARACTERS_TBL_NAME, account.getID());
DataTable charInMemInfo=mDb.ExecuteQuery(sqlSelectNameIdCharactersTable);
// We compare chars from memory and those existing in db,
// and delete those not in mem but existing in db.
bool charFound;
for(uint i = 0;i < charInMemInfo.Rows.Count;++i) // In database
{
charFound=false;
foreach(Character characterInMemory in characters.Values) // In memory
{
if(charInMemInfo.Rows[(int)i][0].ToString()==characterInMemory.getName())
{
charFound=true;
break;
}
}
if(!charFound)
{
// The char is in db but not in memory,
// it will be removed from database.
// We store the id of the char to delete,
// because as deleted, the RecordSet is also emptied,
// and that creates an error.
uint charId=(uint)(charInMemInfo.Rows[(int)i][1]);
delCharacter((int)charId);
}
}
mDb.CommitTransaction();
#if !DEBUG
}
catch(Exception e)
{
Logger.Write(LogLevel.Error, "SQL query failure: {0}", e);
}
#endif
}
void delAccount(ISL.Server.Account.Account account)
{
// Sync the account info into the database.
flush(account);
string sql=String.Format("DELETE FROM {0} WHERE id = '{1}';", ACCOUNTS_TBL_NAME, account.getID());
mDb.ExecuteNonQuery(sql);
// Remove the account's characters.
account.setCharacters(account.getCharacters()); //TODO Überprüfen ob das so funktioniert?
}
Character getCharacterBySQL(ISL.Server.Account.Account owner)
{
Character character=null;
string sql=String.Format("SELECT * FROM {0} WHERE user_id = {1}", CHARACTERS_TBL_NAME, owner.getID());
DataTable charInfo=mDb.ExecuteQuery(sql);
// If the character is not even in the database then
// we have no choice but to return nothing.
if(charInfo.Rows.Count==0)
return null;
character=new Character(charInfo.Rows[0]["name"].ToString(), Convert.ToInt32(charInfo.Rows[0]["id"]));
character.setGender(Convert.ToInt32(charInfo.Rows[0]["gender"]));
character.setHairStyle(Convert.ToInt32(charInfo.Rows[0]["hair_style"]));
character.setHairColor(Convert.ToInt32(charInfo.Rows[0]["hair_color"]));
character.setLevel(Convert.ToInt32(charInfo.Rows[0]["level"]));
character.setCharacterPoints(Convert.ToInt32(charInfo.Rows[0]["char_pts"]));
character.setCorrectionPoints(Convert.ToInt32(charInfo.Rows[0]["correct_pts"]));
Point pos=new Point(Convert.ToInt32(charInfo.Rows[0]["x"]), Convert.ToInt32(charInfo.Rows[0]["y"]));
character.setPosition(pos);
int mapId=Convert.ToInt32(charInfo.Rows[0]["map_id"]);
if(mapId>0)
{
character.setMapId(mapId);
}
else
{
// Set character to default map and one of the default location
// Default map is to be 1, as not found return value will be 0.
character.setMapId(Configuration.getValue("char_defaultMap", 1));
}
character.setCharacterSlot(Convert.ToUInt32(charInfo.Rows[0]["slot"]));
// Fill the account-related fields. Last step, as it may require a new
// SQL query.
if(owner!=null)
{
character.setAccount(owner);
}
else
{
int id=Convert.ToInt32(charInfo.Rows[0]["user_id"]);
character.setAccountID(id);
string s=String.Format("SELECT level FROM {0} WHERE id = '{1}';", ACCOUNTS_TBL_NAME, id);
DataTable levelInfo=mDb.ExecuteQuery(s);
character.setAccountLevel(Convert.ToInt32(levelInfo.Rows[0]["level"]), true);
}
// Load attributes."
string s2=String.Format("SELECT attr_id, attr_base, attr_mod FROM {0} WHERE char_id = {1};", CHAR_ATTR_TBL_NAME, character.getDatabaseID());
DataTable attrInfo=mDb.ExecuteQuery(s2);
if(attrInfo.Rows.Count>0)
{
uint nRows=(uint)attrInfo.Rows.Count;
for(uint row = 0;row < nRows;++row)
{
uint id=Convert.ToUInt32(charInfo.Rows[0]["attr_id"]);
character.setAttribute(id, Convert.ToDouble(charInfo.Rows[0]["attr_base"]));
character.setModAttribute(id, Convert.ToDouble(charInfo.Rows[0]["attr_mod"]));
}
}
// Load the skills of the char from CHAR_SKILLS_TBL_NAME
string s3=String.Format("SELECT status_id, status_time FROM {0} WHERE char_id = {1};", CHAR_STATUS_EFFECTS_TBL_NAME, character.getDatabaseID());
DataTable skillInfo=mDb.ExecuteQuery(s3);
if(skillInfo.Rows.Count>0)
{
uint nRows=(uint)skillInfo.Rows.Count;
for(uint row = 0;row < nRows;row++)
{
character.setExperience(
Convert.ToInt32(skillInfo.Rows[0]["status_id"]), // Skill Id
Convert.ToInt32(skillInfo.Rows[0]["status_time"])); // Experience
}
}
// Load the status effect
string s4=String.Format("SELECT status_id, status_time FROM {0} WHERE char_id = {1};", CHAR_STATUS_EFFECTS_TBL_NAME, character.getDatabaseID());
DataTable statusInfo=mDb.ExecuteQuery(s4);
if(statusInfo.Rows.Count>0)
{
uint nRows=(uint)statusInfo.Rows.Count;
for(uint row = 0;row < nRows;row++)
{
character.applyStatusEffect(
Convert.ToInt32(statusInfo.Rows[0]["status_id"]), // Status Id
Convert.ToInt32(statusInfo.Rows[0]["status_time"])); // Time
}
}
// Load the kill stats
string s5=String.Format("SELECT monster_id, kills FROM {0} WHERE char_id = {1};", CHAR_KILL_COUNT_TBL_NAME, character.getDatabaseID());
DataTable killsInfo=mDb.ExecuteQuery(s5);
if(killsInfo.Rows.Count>0)
{
uint nRows=(uint)killsInfo.Rows.Count;
for(uint row = 0;row < nRows;row++)
{
character.setKillCount(
Convert.ToInt32(killsInfo.Rows[0]["monster_id"]), // MonsterID
Convert.ToInt32(killsInfo.Rows[0]["kills"])); // Kills
}
}
// Load the special status
string s6=String.Format("SELECT special_id FROM {0} WHERE char_id = {1};", CHAR_SPECIALS_TBL_NAME, character.getDatabaseID());
DataTable specialsInfo=mDb.ExecuteQuery(s6);
if(specialsInfo.Rows.Count>0)
{
uint nRows=(uint)specialsInfo.Rows.Count;
for(uint row = 0;row < nRows;row++)
{
character.giveSpecial(Convert.ToInt32(specialsInfo.Rows[0]["special_id"]));
}
}
Possessions poss=character.getPossessions();
string s7=String.Format("SELECT slot_type, item_id, item_instance FROM {0} WHERE owner_id = '{1}' ORDER BY slot_type desc;", CHAR_EQUIPS_TBL_NAME, character.getDatabaseID());
DataTable equipInfo=mDb.ExecuteQuery(s7);
Dictionary< uint, EquipmentItem > equipData=new Dictionary<uint, EquipmentItem>();
if(equipInfo.Rows.Count>0)
{
EquipmentItem equipItem=new EquipmentItem();
for(int k = 0, size = equipInfo.Rows.Count;k < size;++k)
{
equipItem.itemId=Convert.ToUInt32(equipInfo.Rows[0]["item_id"]);
equipItem.itemInstance=Convert.ToUInt32(equipInfo.Rows[0]["item_instance"]);
equipData.Add(Convert.ToUInt32(equipInfo.Rows[0]["slot_type"]), equipItem);
}
}
poss.setEquipment(equipData);
string s8=String.Format("SELECT * FROM {0} WHERE owner_id = '{1}' ORDER by slot ASC", INVENTORIES_TBL_NAME, character.getDatabaseID());
DataTable itemInfo=mDb.ExecuteQuery(s8);
Dictionary<uint, InventoryItem > inventoryData=new Dictionary<uint, InventoryItem>();
if(itemInfo.Rows.Count>0)
{
for(int k = 0, size = itemInfo.Rows.Count;k < size;++k)
{
InventoryItem item=new InventoryItem();
ushort slot=Convert.ToUInt16(itemInfo.Rows[0]["slot"]);
item.itemId=Convert.ToUInt32(itemInfo.Rows[0]["class_id"]);
item.amount=Convert.ToUInt32(itemInfo.Rows[0]["amount"]);
inventoryData[slot]=item;
}
}
poss.setInventory(inventoryData);
return character;
}
public void updateLastLogin(ISL.Server.Account.Account account)
{
string sql=String.Format("UPDATE {0} SET lastlogin = '******' WHERE id = '{2}';", ACCOUNTS_TBL_NAME, account.getLastLogin(), account.getID());
mDb.ExecuteNonQuery(sql);
}
