// Called directly by GetCharacterList() webservice
public static bool GetAccountCharacterList(
string connection_string,
int account_id,
out CharacterState[] character_list,
out string result)
{
bool success = true;
character_list = null;
result = SuccessMessages.GENERAL_SUCCESS;
using (AsyncRPGDataContext context = new AsyncRPGDataContext(connection_string))
{
try
{
CharacterQueries.GetAccountCharacterList(context, account_id, out character_list);
}
catch (System.Exception)
{
success = false;
result = ErrorMessages.DB_ERROR + "(Failed to get account character list state)";
}
}
return(success);
}
public static void InsertMobs(
AsyncRPGDataContext db_context,
RoomKey roomKey,
List <Mob> mobs)
{
foreach (Mob mob in mobs)
{
Mobs dbMob = new Mobs
{
MobTypeID = mob.MobType.ID,
GameID = mob.RoomKey.game_id,
RoomX = mob.RoomKey.x,
RoomY = mob.RoomKey.y,
RoomZ = mob.RoomKey.z,
X = mob.Position.x,
Y = mob.Position.y,
Z = mob.Position.z,
Angle = mob.Angle,
Health = mob.Health,
Energy = mob.Energy,
AiData = Mob.SerializeAIData(mob.AIState)
};
db_context.Mobs.InsertOnSubmit(dbMob);
db_context.SubmitChanges();
mob.ID = dbMob.MobID;
}
}
// Called directly by GetCharacterFullState() webservice
public static bool GetFullCharacterState(
string connection_string,
int character_id,
out CharacterState character_state,
out string result)
{
bool success = true;
result = SuccessMessages.GENERAL_SUCCESS;
character_state = new CharacterState();
using (AsyncRPGDataContext context = new AsyncRPGDataContext(connection_string))
{
try
{
character_state = CharacterQueries.GetFullCharacterState(context, character_id);
}
catch (System.Exception)
{
success = false;
result = ErrorMessages.DB_ERROR + "(Failed to get full character state)";
}
}
return(success);
}
public static void UpdatePlayer(
AsyncRPGDataContext db_context,
Player player)
{
var dbCharacter =
(from p in db_context.Characters
where p.CharacterID == player.ID
select p).Single();
dbCharacter.CharacterID = player.ID;
dbCharacter.Name = player.Name;
dbCharacter.Archetype = (int)player.Archetype;
dbCharacter.Gender = (player.Gender == GameConstants.eGender.Male);
dbCharacter.PictureID = player.PictureID;
dbCharacter.PowerLevel = player.PowerLevel;
dbCharacter.Energy = player.Energy;
//TODO: dbCharacter.Health = this.Health;
dbCharacter.GameID = player.RoomKey.game_id;
dbCharacter.RoomX = player.RoomKey.x;
dbCharacter.RoomY = player.RoomKey.y;
dbCharacter.RoomZ = player.RoomKey.z;
dbCharacter.X = player.Position.x;
dbCharacter.Y = player.Position.y;
dbCharacter.Z = player.Position.z;
dbCharacter.Angle = player.Angle;
db_context.SubmitChanges();
}
public static Dictionary <int, MobSpawnTable> LoadMobSpawnTables(
AsyncRPGDataContext context,
MobTypeSet mobTypeSet)
{
Dictionary <int, MobSpawnTable> spawnTables = new Dictionary <int, MobSpawnTable>();
var db_spawn_tables = from t in context.MobSpawnTables select t;
foreach (MobSpawnTables db_spawn_table in db_spawn_tables)
{
var db_spawn_table_entries =
from e in context.MobSpawnTableEntries
where e.MobSpawnTableID == db_spawn_table.MobSpawnTableID
select e;
MobSpawnTable spawnTable =
new MobSpawnTable(
spawnTables,
mobTypeSet,
db_spawn_table,
db_spawn_table_entries.ToList());
spawnTables.Add(spawnTable.ID, spawnTable);
}
return(spawnTables);
}
public static void GetGameEventsAfterId(
AsyncRPGDataContext context,
int game_id,
int last_event_id,
out GameEvent[] event_list,
out int new_last_event_id)
{
List <GameEvent> temp_event_list = new List <GameEvent>();
var eventQuery =
(from e in context.GameEvents
where e.EventID > last_event_id && e.GameID == game_id
select new { e.EventID, e.EventTime, e.EventType, e.JsonParameters }).Take(20);
new_last_event_id = last_event_id;
foreach (var dbEvent in eventQuery)
{
GameEvent gameEvent = new GameEvent();
new_last_event_id = Math.Max(new_last_event_id, dbEvent.EventID);
gameEvent.timestamp = dbEvent.EventTime.Ticks / 10000; // Convert Ticks (100ns intervals to ms)
gameEvent.event_type = (eGameEventType)dbEvent.EventType;
gameEvent.parameters = DeserializeParametersByType(gameEvent.event_type, dbEvent.JsonParameters);
temp_event_list.Add(gameEvent);
}
event_list = temp_event_list.ToArray();
}
public static void CreateCharacter(
AsyncRPGDataContext context,
int account_id,
string name,
GameConstants.eGender gender,
GameConstants.eArchetype archetype,
int picture_id)
{
Characters newCharacter = new Characters
{
AccountID = account_id,
GameID = -1,
RoomX = 0,
RoomY = 0,
RoomZ = 0,
LastPingTime = DateTime.Now,
LastSentEventID = -1,
NewEventsPosted = false,
X = 0.0f,
Y = 0.0f,
Z = 0.0f,
Angle = 0.0f,
Name = name,
Gender = (gender == GameConstants.eGender.Male),
Archetype = (int)archetype,
PictureID = picture_id,
PowerLevel = 1,
Energy = 0
};
context.Characters.InsertOnSubmit(newCharacter);
context.SubmitChanges();
}
// Called directly by SetAccountOpsLevel web service call
public static bool SetAccountOpsLevel(
string connection_string,
int account_id,
DatabaseConstants.OpsLevel ops_level,
out string result)
{
bool success = false;
result = SuccessMessages.GENERAL_SUCCESS;
using (AsyncRPGDataContext context = new AsyncRPGDataContext(connection_string))
{
try
{
SetAccountOpsLevel(context, account_id, ops_level);
success = true;
}
catch (System.Exception)
{
result = ErrorMessages.DB_ERROR + "(Failed to set account ops level)";
}
}
return(success);
}
public static void CreateAccount(
AsyncRPGDataContext context,
string username,
string clientHashedPassword,
string emailAddress,
out string emailVerificationString)
{
// Salt and re-hash the given password
byte[] saltBytes = null;
string saltString = CreateRandomSalt(out saltBytes);
string saltedPassword = SaltAndHashPassword(clientHashedPassword, saltBytes);
emailVerificationString = CreateNonDeterministicRandomString();
// Actually create an account entry in the database
{
Accounts account = new Accounts
{
CreationDate = DateTime.Now,
OpsLevel = (int)DatabaseConstants.OpsLevel.player,
UserName = username,
PasswordHash = saltedPassword,
Salt = saltString,
EmailAddress = emailAddress,
EmailVerificationKey = emailVerificationString
};
context.Accounts.InsertOnSubmit(account);
context.SubmitChanges();
}
}
public static void DeleteCharacter(
AsyncRPGDataContext context,
int character_id)
{
context.Characters.DeleteOnSubmit(context.Characters.Single(c => c.CharacterID == character_id));
context.SubmitChanges();
}
public static void BindCharacterToGame(
AsyncRPGDataContext context,
int character_id,
int new_game_id)
{
// Get the most recent event that occurred in the new game
int last_game_event_id = GameEventQueries.GetLastGameEvent(context, new_game_id);
// Tell the character which game they are bound to
// and set an initial location
var dbCharacter =
(from c in context.Characters
where c.CharacterID == character_id
select c).Single();
dbCharacter.GameID = new_game_id;
dbCharacter.RoomX = 0;
dbCharacter.RoomY = 0;
dbCharacter.RoomZ = 0;
dbCharacter.X = 0;
dbCharacter.Y = 0;
dbCharacter.Z = 0;
dbCharacter.Angle = 0;
dbCharacter.LastSentEventID = last_game_event_id;
dbCharacter.NewEventsPosted = false;
context.SubmitChanges();
}
public static GameResponseEntry[] GetGameList(
AsyncRPGDataContext context)
{
List <GameResponseEntry> gameList = new List <GameResponseEntry>();
var query = from g in context.Games
join a in context.Accounts on g.OwnerAccountID equals a.AccountID into sr
from x in sr.DefaultIfEmpty()
select new
{
g.GameID,
g.Name,
g.OwnerAccountID,
OwnerAccountName = x.UserName ?? "" // Can be null if the game isn't owned by anyone
};
foreach (var dbEntry in query)
{
GameResponseEntry entry = new GameResponseEntry();
entry.game_id = dbEntry.GameID;
entry.game_name = dbEntry.Name;
entry.owner_account_id = dbEntry.OwnerAccountID;
entry.owner_account_name = dbEntry.OwnerAccountName;
entry.character_names = null;
gameList.Add(entry);
}
//join g in context.games on c.gameid equals g.gameid
return(gameList.ToArray());
}
public static void CreateAccountNoEmailVerify(
AsyncRPGDataContext context,
string username,
string clientHashedPassword,
string emailAddress,
DatabaseConstants.OpsLevel opsLevel)
{
// Then Salt and re-hash like the client password
byte[] saltBytes = null;
string saltString = CreateRandomSalt(out saltBytes);
string saltedPassword = SaltAndHashPassword(clientHashedPassword, saltBytes);
// Actually create an account entry in the database
{
Accounts account = new Accounts
{
CreationDate = DateTime.Now,
OpsLevel = (int)opsLevel,
UserName = username,
PasswordHash = saltedPassword,
Salt = saltString,
EmailAddress = emailAddress,
EmailVerificationKey = ""
};
context.Accounts.InsertOnSubmit(account);
context.SubmitChanges();
}
}
// Called directly by the accounts Web service
public static bool VerifiedEmailAddress(
string connection_string,
string username,
string key,
out string result)
{
bool success = true;
result = SuccessMessages.GENERAL_SUCCESS;
using (AsyncRPGDataContext context = new AsyncRPGDataContext(connection_string))
{
try
{
VerifiedEmailAddress(context, username, key);
}
catch (System.Exception)
{
success = false;
result = ErrorMessages.DB_ERROR + "(Failed to verify e-mail address)";
}
}
return(success);
}
// Database Config Interface
//--------------------------
public static void InitializeConfig(
AsyncRPGDataContext context,
int version,
string appUrl,
string appDebugUrl,
string clientUrl,
string emailAddress,
string emailHost,
int emailPort,
string emailUsername,
string emailPassword,
string ircServer,
int ircPort)
{
Config config = new Config
{
Version = version,
AppURL = appUrl,
AppDebugURL = appDebugUrl,
ClientURL = clientUrl,
EmailAddress = emailAddress,
EmailHost = emailHost,
EmailPort = emailPort,
EmailUserName = emailUsername,
EmailPassword = emailPassword,
IrcServer = ircServer,
IrcPort = ircPort
};
context.Configs.InsertOnSubmit(config);
context.SubmitChanges();
}
public static void AddEvent <T>(
AsyncRPGDataContext context,
int game_id,
T event_parameters) where T : GameEventParameters
{
// Post a new event to the events table
{
GameEvents dbGameEvent = new GameEvents
{
GameID = game_id,
EventTime = DateTime.Now,
EventType = (int)event_parameters.GetEventType(),
JsonParameters = SerializeParametersByType(event_parameters)
};
context.GameEvents.InsertOnSubmit(dbGameEvent);
}
// Tell all Characters associated with the game that some new events got posted
{
var dbCharacterQuery =
from c in context.Characters
where c.GameID == game_id
select c;
foreach (Characters dbCharacter in dbCharacterQuery)
{
dbCharacter.NewEventsPosted = true;
}
}
context.SubmitChanges();
}
public static bool LoadMobSpawnTables(
string connectionString,
MobTypeSet mobTypeSet,
out Dictionary <int, MobSpawnTable> spawnTables,
out string result)
{
bool success;
try
{
AsyncRPGDataContext context = new AsyncRPGDataContext(connectionString);
spawnTables = LoadMobSpawnTables(context, mobTypeSet);
success = true;
result = SuccessMessages.GENERAL_SUCCESS;
}
catch (System.Exception ex)
{
spawnTables = new Dictionary <int, MobSpawnTable>();
success = false;
result = ex.Message;
}
return(success);
}
public override void WriteDirtyObjectToDatabase(AsyncRPGDataContext db_context)
{
if (IsDirty)
{
CharacterQueries.UpdatePlayer(db_context, this);
IsDirty = false;
}
}
public static string[] GetCharacterNamesInGame(
AsyncRPGDataContext context,
int gameid)
{
return((from c in context.Characters
where c.GameID == gameid
select c.Name).ToArray());
}
public override void WriteDirtyObjectToDatabase(AsyncRPGDataContext db_context)
{
if (IsDirty)
{
EnergyTankQueries.UpdateEnergyTank(db_context, this);
IsDirty = false;
}
}
public RequestCache(
ICacheAdapter sessionCache,
AsyncRPGDataContext dbContext)
{
m_sessionCache = sessionCache;
m_dbContext = dbContext;
m_requestRoomCache = null;
}
public static bool VerifyCredentials(
AsyncRPGDataContext context,
string username,
string password,
out int accountId,
out string emailAddress,
out int opsLevel,
out string resultCode)
{
bool success = false;
var query =
(from a in context.Accounts
where a.UserName == username
select a).SingleOrDefault();
accountId = -1;
emailAddress = "";
resultCode = "";
opsLevel = 0;
if (query != null)
{
bool emailVerified = query.EmailVerificationKey.Length == 0;
if (emailVerified)
{
byte[] saltBytes = Convert.FromBase64String(query.Salt);
string saltedPassword = SaltAndHashPassword(password, saltBytes);
if (query.PasswordHash == saltedPassword)
{
accountId = query.AccountID;
emailAddress = query.EmailVerificationKey;
opsLevel = query.OpsLevel;
resultCode = SuccessMessages.GENERAL_SUCCESS;
success = true;
}
else
{
resultCode = ErrorMessages.INVALID_AUTHENTICATION;
success = false;
}
}
else
{
resultCode = ErrorMessages.UNVERIFIED_EMAIL;
success = false;
}
}
else
{
resultCode = ErrorMessages.INVALID_USERNAME;
success = false;
}
return(success);
}
public static string GetCharacterName(
AsyncRPGDataContext context,
int character_id)
{
return
((from c in context.Characters
where c.CharacterID == character_id
select c.Name).SingleOrDefault());
}
public static DateTime GetCharacterLastPingTime(
AsyncRPGDataContext context,
int character_id)
{
return
((from c in context.Characters
where c.CharacterID == character_id
select c.LastPingTime).SingleOrDefault());
}
public static int GetCharacterEnergy(
AsyncRPGDataContext context,
int character_id)
{
return
((from c in context.Characters
where c.CharacterID == character_id
select c.Energy).SingleOrDefault());
}
public static bool DoesRoomExist(
AsyncRPGDataContext context,
RoomKey roomKey)
{
return
((from r in context.Rooms
where r.GameID == roomKey.game_id && r.X == roomKey.x && r.Y == roomKey.y && r.Z == roomKey.z
select r).Count() > 0);
}
public static int GetRoomRandomSeed(
AsyncRPGDataContext context,
RoomKey roomKey)
{
return
((from r in context.Rooms
where r.GameID == roomKey.game_id && r.X == roomKey.x && r.Y == roomKey.y && r.Z == roomKey.z
select r.RandomSeed).Single());
}
public static int GetLastGameEvent(
AsyncRPGDataContext context,
int game_id)
{
return((from e in context.GameEvents
where e.GameID == game_id
orderby e.EventID descending
select e.EventID).First());
}
public static string GetRoomStaticData(
AsyncRPGDataContext context,
RoomKey roomKey)
{
return
((from r in context.Rooms
where r.GameID == roomKey.game_id && r.X == roomKey.x && r.Y == roomKey.y && r.Z == roomKey.z
select r.StaticData).Single());
}
public static void GetAccountCharacterList(
AsyncRPGDataContext context,
int account_id,
out CharacterState[] character_list)
{
var query =
from c in context.Characters
join g in context.Games on c.GameID equals g.GameID into sr
from x in sr.DefaultIfEmpty()
where c.AccountID == account_id
select new
{
c.GameID,
GameName = x.Name ?? "", // Can be null if character isn't in a game
c.CharacterID,
c.Name,
c.Archetype,
c.Gender,
c.PictureID,
c.PowerLevel,
c.RoomX,
c.RoomY,
c.RoomZ,
c.X,
c.Y,
c.Z,
c.Angle
};
List <CharacterState> temp_character_list = new List <CharacterState>();
foreach (var dbCharacter in query)
{
CharacterState entry = new CharacterState();
entry.character_id = dbCharacter.CharacterID;
entry.character_name = dbCharacter.Name;
entry.archetype = dbCharacter.Archetype;
entry.gender = dbCharacter.Gender ? 1 : 0;
entry.picture_id = dbCharacter.PictureID;
entry.power_level = dbCharacter.PowerLevel;
entry.room_x = dbCharacter.RoomX;
entry.room_y = dbCharacter.RoomY;
entry.room_z = dbCharacter.RoomZ;
entry.x = (double)dbCharacter.X;
entry.y = (double)dbCharacter.Y;
entry.z = (double)dbCharacter.Z;
entry.angle = (double)dbCharacter.Angle;
entry.game_id = dbCharacter.GameID;
entry.game_name = dbCharacter.GameName;
temp_character_list.Add(entry);
}
character_list = temp_character_list.ToArray();
}
