public static void InitializeStorage()
{
if (File.Exists(Constants.NewDatabaseFile)) {
try {
if (File.Exists(Constants.OldDatabaseFile)) {
File.Delete(Constants.OldDatabaseFile);
}
File.Move(Constants.DatabaseFile, Constants.OldDatabaseFile);
File.Move(Constants.NewDatabaseFile, Constants.DatabaseFile);
// new database file present, update the database
conn = new SQLiteConnection(String.Format("Data Source={0};Version=3;", Constants.DatabaseFile));
conn.Open();
SQLiteConnection oldConnection = new SQLiteConnection(String.Format("Data Source={0};Version=3;", Constants.OldDatabaseFile));
oldConnection.Open();
UpdateDatabase(oldConnection);
oldConnection.Close();
} catch(Exception ex) {
try {
if (File.Exists(Constants.OldDatabaseFile)) {
File.Move(Constants.OldDatabaseFile, Constants.DatabaseFile);
}
if (File.Exists(Constants.NewDatabaseFile)) {
File.Delete(Constants.NewDatabaseFile);
}
} catch {
}
MainForm.mainForm.DisplayWarning("Failed to update database: " + ex.Message);
conn = new SQLiteConnection(String.Format("Data Source={0};Version=3;", Constants.DatabaseFile));
conn.Open();
}
} else {
conn = new SQLiteConnection(String.Format("Data Source={0};Version=3;", Constants.DatabaseFile));
conn.Open();
}
SQLiteCommand command;
SQLiteDataReader reader;
// Quests
command = new SQLiteCommand("SELECT id, title, name, minlevel, premium, city, legend FROM Quests", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Quest quest = new Quest();
quest.id = reader.GetInt32(0);
quest.title = reader.GetString(1);
quest.name = reader.GetString(2);
quest.minlevel = reader.GetInt32(3);
quest.premium = reader.GetBoolean(4);
quest.city = reader.IsDBNull(5) ? "-" : reader.GetString(5);
quest.legend = reader.IsDBNull(6) ? "No legend available." : reader.GetString(6);
if (quest.legend == "..." || quest.legend == "")
quest.legend = "No legend available.";
questIdMap.Add(quest.id, quest);
questNameMap.Add(quest.name.ToLower(), quest);
}
// Quest Rewards
command = new SQLiteCommand("SELECT questid, itemid FROM QuestRewards", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].rewardItems.Add(reader.GetInt32(1));
}
// Quest Outfits
command = new SQLiteCommand("SELECT questid, outfitid FROM QuestOutfits", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int questid = reader.GetInt32(0);
int outfitid = reader.GetInt32(1);
questIdMap[questid].rewardOutfits.Add(outfitid);
}
// Quest Dangers
command = new SQLiteCommand("SELECT questid, creatureid FROM QuestDangers", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].questDangers.Add(reader.GetInt32(1));
}
// Quest Item Requirements
command = new SQLiteCommand("SELECT questid, count, itemid FROM QuestItemRequirements", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].questRequirements.Add(new Tuple<int, int>(reader.GetInt32(1), reader.GetInt32(2)));
}
// Quest Additional Requirements
command = new SQLiteCommand("SELECT questid, requirementtext FROM QuestAdditionalRequirements", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].additionalRequirements.Add(reader.GetString(1));
}
// Quest Instructions
command = new SQLiteCommand("SELECT questid, beginx, beginy, beginz, endx, endy, endz, description, ordering, missionname, settings FROM QuestInstructions ORDER BY ordering", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
QuestInstruction instruction = new QuestInstruction();
instruction.questid = reader.GetInt32(0);
instruction.begin = new Coordinate(reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3));
if (reader.IsDBNull(4)) {
instruction.end = new Coordinate(DATABASE_NULL, DATABASE_NULL, reader.GetInt32(6));
} else {
instruction.end = new Coordinate(reader.GetInt32(4), reader.GetInt32(5), reader.GetInt32(6));
}
instruction.description = reader.IsDBNull(7) ? "" : reader.GetString(7);
instruction.ordering = reader.GetInt32(8);
instruction.settings = reader.IsDBNull(10) ? null : reader.GetString(10);
string missionName = reader.IsDBNull(9) ? "Guide" : reader.GetString(9);
Quest quest = questIdMap[instruction.questid];
if (!quest.questInstructions.ContainsKey(missionName))
quest.questInstructions.Add(missionName, new List<QuestInstruction>());
quest.questInstructions[missionName].Add(instruction);
}
// Cities
command = new SQLiteCommand("SELECT id, name, x, y, z FROM Cities", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
City city = new City();
city.id = reader.GetInt32(0);
city.name = reader.GetString(1).ToLower();
city.location = new Coordinate(reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));
cityIdMap.Add(city.id, city);
cityNameMap.Add(city.name, city);
}
// City Utilities
command = new SQLiteCommand("SELECT cityid,name,x,y,z FROM CityUtilities", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int cityid = reader.GetInt32(0);
Utility utility = new Utility();
utility.name = reader.GetString(1).ToLower();
utility.location = new Coordinate(reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));
cityIdMap[cityid].utilities.Add(utility);
}
// Events
command = new SQLiteCommand("SELECT id, title, location, creatureid FROM Events", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int eventid = reader.GetInt32(0);
Event ev = new Event();
ev.id = eventid;
ev.title = reader.GetString(1);
ev.location = reader.GetString(2);
ev.creatureid = reader.GetInt32(3);
eventIdMap.Add(eventid, ev);
}
// Event Messages
command = new SQLiteCommand("SELECT eventid,message FROM EventMessages ", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Event ev = eventIdMap[reader.GetInt32(0)];
ev.eventMessages.Add(reader.GetString(1));
}
// Task Groups
command = new SQLiteCommand("SELECT id,name FROM TaskGroups", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int id = reader.GetInt32(0);
string name = reader.GetString(1);
taskList.Add(name.ToLower(), new List<Task>());
taskGroups.Add(id, name);
questNameMap["killing in the name of... quest"].questInstructions.Add(name, new List<QuestInstruction> { new QuestInstruction { specialCommand = "task" + Constants.CommandSymbol + name } });
}
// Tasks
command = new SQLiteCommand("SELECT id,groupid,count,taskpoints,bossid,bossx,bossy,bossz,name FROM Tasks", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Task task = new Task();
task.id = reader.GetInt32(0);
task.groupid = reader.GetInt32(1);
task.groupname = taskGroups[task.groupid];
task.count = reader.GetInt32(2);
task.taskpoints = reader.IsDBNull(3) ? DATABASE_NULL : reader.GetInt32(3);
task.bossid = reader.IsDBNull(4) ? DATABASE_NULL : reader.GetInt32(4);
task.bossposition = new Coordinate();
task.bossposition.x = reader.IsDBNull(5) ? task.bossposition.x : reader.GetInt32(5);
task.bossposition.y = reader.IsDBNull(6) ? task.bossposition.y : reader.GetInt32(6);
task.bossposition.z = reader.IsDBNull(7) ? task.bossposition.z : reader.GetInt32(7);
task.name = reader.GetString(8);
taskIdMap.Add(task.id, task);
// Task Creatures
SQLiteCommand command2 = new SQLiteCommand(String.Format("SELECT creatureid FROM TaskCreatures WHERE taskid={0}", task.id), conn);
SQLiteDataReader reader2 = command2.ExecuteReader();
while (reader2.Read()) {
task.creatures.Add(reader2.GetInt32(0));
}
command2 = new SQLiteCommand(String.Format("SELECT huntingplaceid FROM TaskHunts WHERE taskid={0}", task.id), conn);
reader2 = command2.ExecuteReader();
while (reader2.Read()) {
task.hunts.Add(reader2.GetInt32(0));
}
taskList[task.groupname.ToLower()].Add(task);
}
command = new SQLiteCommand("SELECT command, description FROM CommandHelp", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
helpCommands.Add(new HelpCommand { command = reader["command"].ToString(), description = reader["description"].ToString() });
}
// Maps
command = new SQLiteCommand("SELECT z FROM WorldMap", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Map m = new Map();
m.z = reader.GetInt32(0);
StorageManager.mapFiles.Add(m);
}
// Houses
command = new SQLiteCommand("SELECT id,name,city,x,y,z,sqm,beds,guildhall FROM Houses", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
House house = new House();
house.id = reader.GetInt32(0);
house.name = reader[1].ToString();
house.city = reader[2].ToString();
house.pos.x = reader.GetInt32(3);
house.pos.y = reader.GetInt32(4);
house.pos.z = reader.GetInt32(5);
house.sqm = reader.GetInt32(6);
house.beds = reader.GetInt32(7);
house.guildhall = reader.GetBoolean(8);
if (house.guildhall) {
guildHallIdMap.Add(house.id, house);
} else {
houseIdMap.Add(house.id, house);
}
}
}
private void loadDatabaseData() {
SQLiteCommand command;
SQLiteDataReader reader;
// Quests
command = new SQLiteCommand("SELECT id, title, name, minlevel, premium, city, legend FROM Quests", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Quest quest = new Quest();
quest.id = reader.GetInt32(0);
quest.title = reader.GetString(1);
quest.name = reader.GetString(2);
quest.minlevel = reader.GetInt32(3);
quest.premium = reader.GetBoolean(4);
quest.city = reader.IsDBNull(5) ? "-" : reader.GetString(5);
quest.legend = reader.IsDBNull(6) ? "No legend available." : reader.GetString(6);
if (quest.legend == "..." || quest.legend == "")
quest.legend = "No legend available.";
questIdMap.Add(quest.id, quest);
questNameMap.Add(quest.name.ToLower(), quest);
}
// Quest Rewards
command = new SQLiteCommand("SELECT questid, itemid FROM QuestRewards", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].rewardItems.Add(reader.GetInt32(1));
}
// Quest Outfits
command = new SQLiteCommand("SELECT questid, outfitid FROM QuestOutfits", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int questid = reader.GetInt32(0);
int outfitid = reader.GetInt32(1);
questIdMap[questid].rewardOutfits.Add(outfitid);
}
// Quest Dangers
command = new SQLiteCommand("SELECT questid, creatureid FROM QuestDangers", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].questDangers.Add(reader.GetInt32(1));
}
// Quest Item Requirements
command = new SQLiteCommand("SELECT questid, count, itemid FROM QuestItemRequirements", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].questRequirements.Add(new Tuple<int, int>(reader.GetInt32(1), reader.GetInt32(2)));
}
// Quest Additional Requirements
command = new SQLiteCommand("SELECT questid, requirementtext FROM QuestAdditionalRequirements", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
questIdMap[reader.GetInt32(0)].additionalRequirements.Add(reader.GetString(1));
}
// Quest Instructions
command = new SQLiteCommand("SELECT questid, beginx, beginy, beginz, endx, endy, endz, description, ordering, missionname, settings FROM QuestInstructions ORDER BY ordering", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
QuestInstruction instruction = new QuestInstruction();
instruction.questid = reader.GetInt32(0);
instruction.begin = new Coordinate(reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3));
if (reader.IsDBNull(4)) {
instruction.end = new Coordinate(DATABASE_NULL, DATABASE_NULL, reader.GetInt32(6));
} else {
instruction.end = new Coordinate(reader.GetInt32(4), reader.GetInt32(5), reader.GetInt32(6));
}
instruction.description = reader.IsDBNull(7) ? "" : reader.GetString(7);
instruction.ordering = reader.GetInt32(8);
instruction.settings = reader.IsDBNull(10) ? null : reader.GetString(10);
string missionName = reader.IsDBNull(9) ? "Guide" : reader.GetString(9);
Quest quest = questIdMap[instruction.questid];
if (!quest.questInstructions.ContainsKey(missionName))
quest.questInstructions.Add(missionName, new List<QuestInstruction>());
quest.questInstructions[missionName].Add(instruction);
}
// Cities
command = new SQLiteCommand("SELECT id, name, x, y, z FROM Cities", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
City city = new City();
city.id = reader.GetInt32(0);
city.name = reader.GetString(1).ToLower();
city.location = new Coordinate(reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));
cityIdMap.Add(city.id, city);
cityNameMap.Add(city.name, city);
}
// City Utilities
command = new SQLiteCommand("SELECT cityid,name,x,y,z FROM CityUtilities", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int cityid = reader.GetInt32(0);
Utility utility = new Utility();
utility.name = reader.GetString(1).ToLower();
utility.location = new Coordinate(reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4));
cityIdMap[cityid].utilities.Add(utility);
}
// Events
command = new SQLiteCommand("SELECT id, title, location, creatureid FROM Events", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int eventid = reader.GetInt32(0);
Event ev = new Event();
ev.id = eventid;
ev.title = reader.GetString(1);
ev.location = reader.GetString(2);
ev.creatureid = reader.GetInt32(3);
eventIdMap.Add(eventid, ev);
}
// Event Messages
command = new SQLiteCommand("SELECT eventid,message FROM EventMessages ", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Event ev = eventIdMap[reader.GetInt32(0)];
ev.eventMessages.Add(reader.GetString(1));
}
// Task Groups
command = new SQLiteCommand("SELECT id,name FROM TaskGroups", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
int id = reader.GetInt32(0);
string name = reader.GetString(1);
taskList.Add(name.ToLower(), new List<Task>());
taskGroups.Add(id, name);
questNameMap["killing in the name of... quest"].questInstructions.Add(name, new List<QuestInstruction> { new QuestInstruction { specialCommand = "task" + MainForm.commandSymbol + name } });
}
// Tasks
command = new SQLiteCommand("SELECT id,groupid,count,taskpoints,bossid,bossx,bossy,bossz,name FROM Tasks", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
Task task = new Task();
task.id = reader.GetInt32(0);
task.groupid = reader.GetInt32(1);
task.groupname = taskGroups[task.groupid];
task.count = reader.GetInt32(2);
task.taskpoints = reader.IsDBNull(3) ? DATABASE_NULL : reader.GetInt32(3);
task.bossid = reader.IsDBNull(4) ? DATABASE_NULL : reader.GetInt32(4);
task.bossposition = new Coordinate();
task.bossposition.x = reader.IsDBNull(5) ? task.bossposition.x : reader.GetInt32(5);
task.bossposition.y = reader.IsDBNull(6) ? task.bossposition.y : reader.GetInt32(6);
task.bossposition.z = reader.IsDBNull(7) ? task.bossposition.z : reader.GetInt32(7);
task.name = reader.GetString(8);
// Task Creatures
SQLiteCommand command2 = new SQLiteCommand(String.Format("SELECT creatureid FROM TaskCreatures WHERE taskid={0}", task.id), conn);
SQLiteDataReader reader2 = command2.ExecuteReader();
while (reader2.Read()) {
task.creatures.Add(reader2.GetInt32(0));
}
command2 = new SQLiteCommand(String.Format("SELECT huntingplaceid FROM TaskHunts WHERE taskid={0}", task.id), conn);
reader2 = command2.ExecuteReader();
while (reader2.Read()) {
task.hunts.Add(reader2.GetInt32(0));
}
taskList[task.groupname.ToLower()].Add(task);
}
command = new SQLiteCommand("SELECT command, description FROM CommandHelp", conn);
reader = command.ExecuteReader();
while (reader.Read()) {
helpCommands.Add(new HelpCommand { command = reader["command"].ToString(), description = reader["description"].ToString() });
}
}