private void InitializeDatabase()
{
//Create the tables for a fresh database
LogInstance.WriteToFile(1, "Creating tables for a fresh database.", LogSource);
if (!connectionopen)
{
DBConnector.Open();
connectionopen = true;
}
LogInstance.WriteToFile(4, "Beginning transaction for database initialization", LogSource);
FbTransaction DBTransaction = DBConnector.BeginTransaction();
//Create a boolish-type
FbCommand DBCommand = new FbCommand("CREATE DOMAIN BOOLEAN AS INT CHECK (value is null or value in (0, 1))", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
LogInstance.WriteToFile(4, "Creating settings table.", LogSource);
//Create our settings with ID-Generators
#region table - settings
DBCommand = new FbCommand("CREATE TABLE settings (ID integer not null, name_of_setting varchar(150), content varchar(450), active boolean, primary key(ID))", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE GENERATOR gen_settings_id", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("SET GENERATOR gen_settings_id TO 0", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE TRIGGER settings_TR for settings ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(gen_settings_id, 1); END", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Creating games table.", LogSource);
//Create our games
#region table - games
DBCommand = new FbCommand("CREATE TABLE games (ID integer not null, name_of_game varchar(250), info_xml BLOB SUB_TYPE TEXT, downloaded boolean, snatched boolean, wanted boolean, ReleaseDate varchar(12), InstallPath varchar(256), TGDB_id integer, Platform varchar(100), primary key (ID))", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE GENERATOR gen_game_id", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("SET GENERATOR gen_game_id TO 0", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE TRIGGER game_TR FOR games ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(gen_settings_id, 1); END", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Creating table for scene-exceptions.", LogSource);
//Create a table for scene-exceptions
#region table - exceptions
DBCommand = new FbCommand("CREATE TABLE exceptions (ID integer not null, exception varchar(250), primary key(ID))", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE GENERATOR gen_exceptions_id", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("SET GENERATOR gen_exceptions_id TO 0", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE TRIGGER exceptions_TR for exceptions ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(gen_exceptions_id, 1); END", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Creating table for plattforms.", LogSource);
//Create the platform table
#region table - platforms
DBCommand = new FbCommand("CREATE TABLE platforms (ID integer not null, name_of_platform varchar(100), gamedbid integer, active boolean, primary key(ID))", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE GENERATOR gen_platforms_id", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("SET GENERATOR gen_platforms_id TO 0", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("CREATE TRIGGER platforms_TR for platforms ACTIVE BEFORE INSERT POSITION 0 AS BEGIN if (NEW.ID is NULL) then NEW.ID = GEN_ID(gen_platforms_id, 1); END", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Commiting the changes to the database.", LogSource);
//Commit all of our work
DBTransaction.Commit();
LogInstance.WriteToFile(4, "Beginning a new transaction for the default settings.", LogSource);
//Start a new transaction
DBTransaction = DBConnector.BeginTransaction();
//Set default settings
LogInstance.WriteToFile(4, "Setting the default settings - General.", LogSource);
#region default settings - general
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('Fanartfolder', 'C:\\fanart', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('Posterfolder', 'C:\\poster', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('Shortcutfolder', 'C:\\shotcuts', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('NFOFolder', 'C:\\NFO', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('AL_Launchers.xml_Location', 'C:\\Launcher', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Setting the default settings - Provider.", LogSource);
#region default settings - provider
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('nzbsorgenabled', 'False', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('nzbmatrixenabled', 'False', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('nzbclubenabled', 'True', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Setting the default settings - sabnzbd.", LogSource);
#region default settings - sabnzbd
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('sabnzbdenabled', 'False', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
LogInstance.WriteToFile(4, "Setting the default settings - N64.", LogSource);
#region default settings - N64
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64Romfolder', 'C:\\Games\\N64Roms', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64Downloadfolder', 'C:\\Games\\N64Downloads', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64PPEnabled', 'false', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64MetaEnabled', 'false', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64MovingEnabled', 'false', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64RenamingEnabled', 'false', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
DBCommand = new FbCommand("INSERT INTO settings (name_of_setting, content, active) VALUES ('N64SpaceChar', '_', 1)", DBConnector, DBTransaction);
DBCommand.ExecuteNonQuery();
#endregion
//Get a recent platform list and insert it
LogInstance.WriteToFile(4, "Calling TheGamesDB for a current list of platforms.", LogSource);
TheGamesDB PlatformScraper = new TheGamesDB(LogInstance);
List<Platform> scrapedplatforms = PlatformScraper.GetAvailablePlatforms();
if (scrapedplatforms != null)
{
foreach (Platform platform in scrapedplatforms)
{
DBCommand = new FbCommand("INSERT INTO platforms(name_of_platform, gamedbid, active) VALUES ('" + platform.Name + "'," + platform.ID + ", 1)", DBConnector, DBTransaction);
LogInstance.WriteToFile(4, "Executing SQL Command: " + DBCommand.CommandText, LogSource);
DBCommand.ExecuteNonQuery();
}
}
else
{
DBCommand = new FbCommand("INSERT INTO platforms(name_of_platform) VALUES ('Nothing available')", DBConnector, DBTransaction);
LogInstance.WriteToFile(4, "NO PLATFORMS AVAILABLE! We just might as well kill the whole program...(but we don't).", LogSource);
DBCommand.ExecuteNonQuery();
}
LogInstance.WriteToFile(4, "Commiting the changes to the database.", LogSource);
DBTransaction.Commit();
DBConnector.Close();
connectionopen = false;
}
