public FolderSettingsSqlLite()
{
try
{
// Open database
Log.Info("Open FolderDatabase");
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "FolderDatabase3.db3"));
_dbHealth = DatabaseUtility.IntegrityCheck(m_db);
DatabaseUtility.SetPragmas(m_db);
DatabaseUtility.AddTable(m_db, "tblPath", "CREATE TABLE tblPath ( idPath integer primary key, strPath text)");
DatabaseUtility.AddTable(m_db, "tblSetting",
"CREATE TABLE tblSetting ( idSetting integer primary key, idPath integer , tagName text, tagValue text)");
// Indexes for tblPath
DatabaseUtility.AddIndex(m_db, "idx_tblPath_strPath", "CREATE INDEX idx_tblPath_strPath ON tblPath (strPath ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblPath_idPath_strPath", "CREATE INDEX idx_tblPath_idPath_strPath ON tblPath (idPath ASC, strPath ASC)");
// Indexes for tblSetting
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_idPath", "CREATE INDEX idx_tblSetting_idPath ON tblSetting (idPath ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_tagName", "CREATE INDEX idx_tblSetting_tagName ON tblSetting (tagName ASC)");
DatabaseUtility.AddIndex(m_db, "idx_tblSetting_idPath_tagName", "CREATE INDEX idx_tblSetting_idPath_tagName ON tblSetting (idPath ASC, tagName ASC)");
// Cleanup DB
Log.Debug("Cleanup FolderDatabase");
string strSQL = String.Format("delete from tblPath where idPath not in (select idPath from tblSetting)");
m_db.Execute(strSQL);
}
catch (Exception ex)
{
Log.Error(ex);
}
}
private bool CreateTables()
{
if (m_db == null)
{
return(false);
}
DatabaseUtility.AddTable(m_db, "picture",
"CREATE TABLE picture ( idPicture integer primary key, strFile text, iRotation integer, strDateTaken text)");
return(true);
}
private FavoritesDatabase()
{
try
{
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "OnlineVideoDatabase.db3"));
DatabaseUtility.SetPragmas(m_db);
DatabaseUtility.AddTable(m_db, "FAVORITE_VIDEOS", "CREATE TABLE FAVORITE_VIDEOS(VDO_ID integer primary key autoincrement,VDO_NM text,VDO_URL text,VDO_DESC text,VDO_TAGS text,VDO_LENGTH text,VDO_OTHER_NFO text,VDO_IMG_URL text,VDO_SITE_ID text)\n");
DatabaseUtility.AddTable(m_db, "FAVORITE_Categories", "CREATE TABLE FAVORITE_Categories(CAT_ID integer primary key autoincrement,CAT_Name text,CAT_Desc text,CAT_ThumbUrl text,CAT_Hierarchy text,CAT_SITE_ID text)\n");
DatabaseUtility.AddTable(m_db, "PREFERRED_LAYOUT", "CREATE TABLE PREFERRED_LAYOUT(Site_Name text, Category_Hierarchy text, Layout integer, PRIMARY KEY (Site_Name, Category_Hierarchy) ON CONFLICT REPLACE)\n");
}
catch (SQLiteException ex)
{
Log.Instance.Error("database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public FolderSettingsSqlLite()
{
try
{
// Open database
Log.Info("open folderdatabase");
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "FolderDatabase3.db3"));
DatabaseUtility.SetPragmas(m_db);
DatabaseUtility.AddTable(m_db, "tblPath", "CREATE TABLE tblPath ( idPath integer primary key, strPath text)");
DatabaseUtility.AddTable(m_db, "tblSetting",
"CREATE TABLE tblSetting ( idSetting integer primary key, idPath integer , tagName text, tagValue text)");
}
catch (Exception ex)
{
Log.Error(ex);
}
}
private bool CreateTables()
{
if (m_db == null)
{
return(false);
}
#region Tables
DatabaseUtility.AddTable(m_db, "picture",
"CREATE TABLE picture ( idPicture integer primary key, strFile text, iRotation integer, strDateTaken text)");
#endregion
#region Indexes
DatabaseUtility.AddIndex(m_db, "idxpicture_strFile", "CREATE INDEX idxpicture_strFile ON picture (strFile ASC)");
DatabaseUtility.AddIndex(m_db, "idxpicture_strDateTaken", "CREATE INDEX idxpicture_strDateTaken ON picture (strDateTaken ASC)");
#endregion
return(true);
}
public void GetSQLSettings()
{
SQLSettings.Clear();
SQLiteClient _sqlClient = new SQLiteClient(Config.GetFile(Config.Dir.Database, "MyTorrents.db3"));
if (!DatabaseUtility.TableExists(_sqlClient, "Settings"))
{
DatabaseUtility.AddTable(_sqlClient, "Settings", "CREATE TABLE Settings (ParameterName CHAR(256), ParameterValue CHAR(256));");
_sqlClient.Execute(String.Format("INSERT INTO Settings (ParameterName, ParameterValue) VALUES (\"TorrentView\" , \"Active\");"));
_sqlClient.Execute(String.Format("INSERT INTO Settings (ParameterName, ParameterValue) VALUES (\"SortOrder\" , \"Default\");"));
_sqlClient.Execute(String.Format("INSERT INTO Settings (ParameterName, ParameterValue) VALUES (\"LabelFilter\" , \"None\");"));
}
SQLiteResultSet resultSet = _sqlClient.Execute("SELECT * FROM Settings;");
foreach (SQLiteResultSet.Row row in resultSet.Rows)
{
SQLSettings.Add(row.fields[(int)resultSet.ColumnIndices["ParameterName"]], row.fields[(int)resultSet.ColumnIndices["ParameterValue"]]);
}
if (SQLSettings.Count == 0)
{
//Fail
}
}
private bool CreateDatabase()
{
try
{
DatabaseUtility.SetPragmas(MusicDbClient);
// Tracks table containing information for songs
DatabaseUtility.AddTable(
MusicDbClient, "tracks",
@"CREATE TABLE tracks ( " +
"idTrack integer primary key autoincrement, " + // Unique id Autoincremented
"strPath text, " + // Full path of the file.
"strArtist text, " + // Artist
"strAlbumArtist text, " + // Album Artist
"strAlbum text, " + // Album
"strGenre text, " + // Genre (multiple genres)
"strComposer text, " + // Composer (multiple composers)
"strConductor text, " + // Conductor
"strTitle text, " + // Song Title
"iTrack integer, " + // Track Number
"iNumTracks integer, " + // Total Number of Tracks on Album
"iDuration integer, " + // Duration in seconds
"iYear integer, " + // Year
"iTimesPlayed integer, " + // # Times Played
"iRating integer, " + // Rating
"iFavorite integer, " + // Favorite Indicator
"iResumeAt integer, " + // Resume song from position
"iDisc integer, " + // Disc Number
"iNumDisc integer, " + // Total Number of Discs
"strLyrics text, " + // Lyric Text
"strComment text, " + // Comment
"strMBArtistId text, " + // MusicBrainz Artist Id
"strMBDiscId text, " + // MusicBrainz Disc Id
"strMBReleaseArtistId text, " + // MusicBrainz Release Artist Id
"strMBReleaseCountry text, " + // MusicBrainz Release Country
"strMBReleaseGroupId text, " + // MusicBrainz Release Group Id
"strMBReleaseId text, " + // MusicBrainz Release Id
"strMBReleaseStatus text, " + // MusicBrainz Release Status
"strMBReleaseType text, " + // MusicBrainz Release Type
"strMBTrackId text, " + // MusicBrainz Track Id
"strFileType text, " + // File Format (mp3, flac, etc.)
"strFullCodec text, " + // Full Codec Description
"strBitRateMode text, " + // Bitrate mode (CBR / VBR)
"iBPM integer, " + // Beats per Minute
"iBitRate integer, " + // Bitrate
"iChannels integer, " + // Channels
"iSampleRate integer, " + // Sample Rate
"dateLastPlayed timestamp, " + // Date, Last Time Played
"dateAdded timestamp" + // Date added. Either Insertion date, Creation date, LastWrite
")"
);
MusicDbClient.Execute(strSQL);
// Indices for Tracks table
DatabaseUtility.AddIndex(MusicDbClient, "idxpath_strPath", "CREATE INDEX idxpath_strPath ON tracks(strPath ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxartist_strArtist",
"CREATE INDEX idxartist_strArtist ON tracks(strArtist ASC)");
//DatabaseUtility.AddIndex(MusicDbClient, "idxartist_strArtistSortName", "CREATE INDEX idxartist_strArtistSortName ON tracks(strArtistSortName ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxalbum_strAlbumArtist",
"CREATE INDEX idxalbum_strAlbumArtist ON tracks(strAlbumArtist ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxalbum_strAlbum",
"CREATE INDEX idxalbum_strAlbum ON tracks(strAlbum ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxgenre_strGenre",
"CREATE INDEX idxgenre_strGenre ON tracks(strGenre ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxcomposer_strComposer",
"CREATE INDEX idxcomposer_strComposer ON tracks(strComposer ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxconductor_strConductor",
"CREATE INDEX idxconductor_strConductor ON tracks(strConductor ASC)");
// Artist
DatabaseUtility.AddTable(MusicDbClient, "artist",
"CREATE TABLE artist ( idArtist integer primary key autoincrement, strArtist text)");
DatabaseUtility.AddIndex(MusicDbClient, "idxartisttable_strArtist",
"CREATE INDEX idxartisttable_strArtist ON artist(strArtist ASC)");
// AlbumArtist
DatabaseUtility.AddTable(MusicDbClient, "albumartist",
"CREATE TABLE albumartist ( idAlbumArtist integer primary key autoincrement, strAlbumArtist text)");
DatabaseUtility.AddIndex(MusicDbClient, "idxalbumartisttable_strAlbumArtist",
"CREATE INDEX idxalbumartisttable_strAlbumArtist ON albumartist(strAlbumArtist ASC)");
// Genre
DatabaseUtility.AddTable(MusicDbClient, "genre",
"CREATE TABLE genre ( idGenre integer primary key autoincrement, strGenre text)");
DatabaseUtility.AddIndex(MusicDbClient, "idxgenretable_strGenre",
"CREATE INDEX idxgenretable_strGenre ON genre(strGenre ASC)");
// Composer
DatabaseUtility.AddTable(MusicDbClient, "composer",
"CREATE TABLE composer ( idComposer integer primary key autoincrement, strComposer text)");
DatabaseUtility.AddIndex(MusicDbClient, "idxcomposertable_strComposer",
"CREATE INDEX idxcomposertable_strComposer ON composer(strComposer ASC)");
// Artist Info and Album Info
DatabaseUtility.AddTable(MusicDbClient, "albuminfo",
"CREATE TABLE albuminfo ( idAlbumInfo integer primary key autoincrement, strAlbum text, strArtist text, strAlbumArtist text,iYear integer, idGenre integer, strTones text, strStyles text, strReview text, strImage text, strTracks text, iRating integer)");
DatabaseUtility.AddTable(MusicDbClient, "artistinfo",
"CREATE TABLE artistinfo ( idArtistInfo integer primary key autoincrement, strArtist text, strBorn text, strYearsActive text, strGenres text, strTones text, strStyles text, strInstruments text, strImage text, strAMGBio text, strAlbums text, strCompilations text, strSingles text, strMisc text)");
// Indices for Album and Artist Info
DatabaseUtility.AddIndex(MusicDbClient, "idxalbuminfo_strAlbum",
"CREATE INDEX idxalbuminfo_strAlbum ON albuminfo(strAlbum ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxalbuminfo_strArtist",
"CREATE INDEX idxalbuminfo_strArtist ON albuminfo(strArtist ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxalbuminfo_idGenre",
"CREATE INDEX idxalbuminfo_idGenre ON albuminfo(idGenre ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxartistinfo_strArtist",
"CREATE INDEX idxartistinfo_strArtist ON artistinfo(strArtist ASC)");
// last.fm users
DatabaseUtility.AddTable(MusicDbClient, "lastfmusers",
"CREATE TABLE lastfmusers ( idLastFMUser integer primary key, strUsername text, strSK text)");
Log.Info("MusicDatabase: New Database created successfully");
return(true);
}
catch (Exception ex)
{
Log.Error("MusicDatabase: Create of database failed. Err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
return(false);
}
public static void init()
{
if (isDBInit)
{
Logger.LogDebug("Database already initialised");
return;
}
String dbFile = Config.GetFile(Config.Dir.Database, "myEmulators_v2.db3");
lock (dbLock) //don't allow any DB calls until init completes
{
sqlDB = new SQLiteClient(dbFile);
DatabaseUtility.AddTable(sqlDB, "Emulators",
"CREATE TABLE Emulators(" +
"uid int," +
"emulator_path varchar(200)," +
"position int," +
"rom_path varchar(200)," +
"title varchar(50)," +
"filter varchar(100)," +
"working_path varchar(200)," +
"usequotes char(5)," +
"view int," +
"args varchar(200)," +
"suspend_mp char(5)," +
"enable_goodmerge char(5)," +
"goodmerge_pref1 varchar(10)," +
"goodmerge_pref2 varchar(10)," +
"goodmerge_pref3 varchar(10)," +
"goodmerge_temp_path varchar(200)," +
"Grade int," +
"Company varchar(200)," +
"Yearmade int," +
"Description varchar(2000)," +
"mountimages char(5)," +
"PRIMARY KEY(uid)" +
")"
);
DatabaseUtility.AddTable(sqlDB, "EmulatorProfiles",
"CREATE TABLE EmulatorProfiles(" +
"uid int," +
"title varchar(200)," +
"emulator_id int," +
"emulator_path varchar(200)," +
"working_path varchar(200)," +
"usequotes char(5)," +
"args varchar(200)," +
"suspend_mp char(5)," +
"goodmerge_pref1 varchar(10)," +
"goodmerge_pref2 varchar(10)," +
"goodmerge_pref3 varchar(10)," +
"mountimages char(5)," +
"PRIMARY KEY(uid)" +
")"
);
DatabaseUtility.AddTable(sqlDB, "Games",
@"CREATE TABLE Games(
gameid INTEGER PRIMARY KEY AUTOINCREMENT,
path varchar(200),
parentemu int,
title varchar(100),
grade int,
playcount int,
yearmade int,
latestplay varchar(16),
description varchar(200),
genre varchar(50),
company varchar(50),
visible char(5),
favourite char(5),
LaunchFile varchar(200),
emuprofile int,
infochecked char(5)
)"
);
DatabaseUtility.AddTable(sqlDB, "Info",
"CREATE TABLE Info(" +
"name varchar(50)," +
"value varchar(100)," +
"PRIMARY KEY(name)" +
")"
);
//Check version and upgrade if neccessary
SQLiteResultSet result = sqlDB.Execute("SELECT value FROM Info WHERE name='version'");
if (result.Rows.Count == 0)
{
//Fresh install
sqlDB.Execute("INSERT INTO Info VALUES('version'," + Options.getVersionNumber() + ")");
//Update result to new version number
result = sqlDB.Execute("SELECT value FROM Info WHERE name='version'");
}
//Upgrade to 3.2
if (result.GetRow(0).fields[0].CompareTo("3.2") < 0)
{
//Add the suspend_mp column to the emulators table
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN suspend_mp char(5)");
}
catch { }
//Add default value
sqlDB.Execute("UPDATE Emulators SET suspend_mp='False'");
}
// add goodmerge support
if (result.GetRow(0).fields[0].CompareTo("3.4") < 0)
{
//Add the suspend_mp column to the emulators table
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN enable_goodmerge char(5)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN goodmerge_pref1 varchar(10)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN goodmerge_pref2 varchar(10)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN goodmerge_pref3 varchar(10)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN goodmerge_temp_path varchar(200)");
}
catch { }
//Add default value
sqlDB.Execute("UPDATE Emulators SET enable_goodmerge='False'");
try
{
sqlDB.Execute("ALTER TABLE Games ADD COLUMN LaunchFile varchar(200)");
}
catch { }
}
if (result.GetRow(0).fields[0].CompareTo("3.5") < 0)
{
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN Company varchar(200)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN Description varchar(2000)");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN Grade int");
}
catch { }
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN Yearmade int");
}
catch { }
sqlDB.Execute("UPDATE Emulators SET Grade=0");
}
if (result.GetRow(0).fields[0].CompareTo("4.0") < 0)
{
try
{
sqlDB.Execute("ALTER TABLE Games ADD COLUMN emuprofile int");
}
catch { }
sqlDB.Execute("UPDATE Games SET emuprofile=-1");
}
if (result.GetRow(0).fields[0].CompareTo("4.1") < 0)
{
try
{
sqlDB.Execute("ALTER TABLE Games ADD COLUMN infochecked char(5)");
}
catch { }
sqlDB.Execute("UPDATE Games SET infochecked = 'False'");
}
if (result.GetRow(0).fields[0].CompareTo("4.2") < 0)
{
try
{
sqlDB.Execute("ALTER TABLE Emulators ADD COLUMN mountimages char(5)");
}
catch { }
sqlDB.Execute("UPDATE Emulators SET mountimages = 'False'");
try
{
sqlDB.Execute("ALTER TABLE EmulatorProfiles ADD COLUMN mountimages char(5)");
}
catch { }
sqlDB.Execute("UPDATE EmulatorProfiles SET mountimages = 'False'");
//Recreate games table
sqlDB.Execute("CREATE TABLE Gamestemp(gameid int,path varchar(200),parentemu int,title varchar(100),grade int,playcount int,yearmade int,latestplay varchar(16),description varchar(200),genre varchar(50),company varchar(50),visible char(5),favourite char(5),LaunchFile varchar(200),emuprofile int, infochecked char(5))");
sqlDB.Execute("INSERT INTO Gamestemp SELECT * FROM games;");
sqlDB.Execute("DROP TABLE games;");
sqlDB.Execute("CREATE TABLE Games(gameid INTEGER PRIMARY KEY AUTOINCREMENT, path varchar(200),parentemu int,title varchar(100),grade int,playcount int,yearmade int,latestplay varchar(16),description varchar(200),genre varchar(50),company varchar(50),visible char(5),favourite char(5),LaunchFile varchar(200),emuprofile int, infochecked char(5))");
sqlDB.Execute("INSERT INTO games SELECT NULL,path,parentemu,title,grade,playcount,yearmade,latestplay,description,genre,company,visible,favourite,LaunchFile,emuprofile,infochecked FROM gamestemp;");
sqlDB.Execute("DROP Table Gamestemp");
}
//To always be in sync with the version number
sqlDB.Execute("UPDATE Info SET value='" + Options.getVersionNumber() + "' WHERE name='version'");
//remove pc emulator if left over from previous version
sqlDB.Execute("DELETE FROM Emulators WHERE uid=-1");
/*
* //Bundle installation
* //TODO: gör bundle-mpi
* result = sqlDB.Execute("SELECT uid FROM Emulators WHERE emulator_path LIKE '%--EMU_BASE_PATH--%'");
* if (result.Rows.Count > 0)
* {
* for (int i = 0; i < result.Rows.Count; i++)
* {
* Emulator item = getEmulator(DatabaseUtility.GetAsInt(result, i, 0));
* item.PathToEmulator = item.PathToEmulator.Replace("--EMU_BASE_PATH--", Config.GetFolder(Config.Dir.Base) + @"\Emulators");
* item.PathToRoms = item.PathToRoms.Replace("--GAME_BASE_PATH--", item.PathToEmulator.Remove(item.PathToEmulator.LastIndexOf('\\')) + @"\Place your games here");
* saveEmulator(item);
* }
* }
*/
}
isDBInit = true;
}
void init()
{
lock (syncRoot)
{
if (isInit)
{
return;
}
string dbFile = Config.GetFile(Config.Dir.Database, DB_FILE_NAME);
bool exists = File.Exists(dbFile);
sqlClient = new SQLiteClient(dbFile);
DatabaseUtility.AddTable(sqlClient, Emulator.TABLE_NAME, Emulator.DBTableString);
DatabaseUtility.AddTable(sqlClient, EmulatorProfile.TABLE_NAME, EmulatorProfile.DBTableString);
DatabaseUtility.AddTable(sqlClient, Game.TABLE_NAME, Game.DBTableString);
DatabaseUtility.AddTable(sqlClient, GameDisc.TABLE_NAME, GameDisc.DBTableString);
DatabaseUtility.AddTable(sqlClient, "Info",
"CREATE TABLE Info(" +
"name varchar(50)," +
"value varchar(100)," +
"PRIMARY KEY(name)" +
")"
);
isInit = true;
SQLiteResultSet result = sqlClient.Execute("SELECT value FROM Info WHERE name='version'");
if (result.Rows.Count == 0)
{
if (exists)
{
sqlClient.Execute("INSERT INTO Info VALUES('version','1.7')");
isInit = false;
sqlClient.Dispose();
init();
}
else
{
UpdateDBVersion();
}
return;
}
double currentVersion = double.Parse(result.Rows[0].fields[0], System.Globalization.CultureInfo.InvariantCulture);
if (currentVersion == DB_VERSION)
{
return;
}
if (!backupDBFile())
{
isInit = false;
return;
}
if (updateDatabase(currentVersion))
{
UpdateDBVersion();
return;
}
isInit = false;
sqlClient.Dispose();
Logger.LogInfo("Deleting incompatible database (backup has been created)");
try
{
File.Delete(dbFile);
}
catch (Exception ex)
{
Logger.LogError("Failed to delete database file, try deleting {0} manually - {1}", dbFile, ex.Message);
return;
}
init();
}
}