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 void UpgradeDBV13Indexes()
{
try
{
// Indices for Tracks table
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_iYear",
"CREATE INDEX idxtracks_iYear ON tracks(iYear ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_iRating",
"CREATE INDEX idxtracks_iRating ON tracks(iRating ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_iFavorite",
"CREATE INDEX idxtracks_iFavorite ON tracks(iFavorite ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strTitle",
"CREATE INDEX idxtracks_strTitle ON tracks(strTitle ASC)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strAlbumstrTitle",
"CREATE INDEX idxtracks_strAlbumstrTitle ON tracks(strAlbum, strTitle)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strArtiststrTitle",
"CREATE INDEX idxtracks_strArtiststrTitle ON tracks(strArtist, strTitle)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strArtiststrAlbumstrTitle",
"CREATE INDEX idxtracks_strArtiststrAlbumstrTitle ON tracks(strArtist, strAlbum, strTitle)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strAlbumArtiststrAlbum",
"CREATE INDEX idxtracks_strAlbumArtiststrAlbum ON tracks(strAlbumArtist, strAlbum)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strAlbumArtiststrAlbumiDisc",
"CREATE INDEX idxtracks_strAlbumArtiststrAlbumiDisc ON tracks(strAlbumArtist, strAlbum, iDisc)");
DatabaseUtility.AddIndex(MusicDbClient, "idxtracks_strAlbumArtiststrAlbumstrFileType",
"CREATE INDEX idxtracks_strAlbumArtiststrAlbumstrFileType ON tracks(strAlbumArtist, strAlbum, strFileType)");
// Indices for Album and Artist Info
DatabaseUtility.AddIndex(MusicDbClient, "idxalbuminfo_strArtiststrAlbum",
"CREATE INDEX idxalbuminfo_strArtiststrAlbum ON albuminfo(strArtist, strAlbum)");
}
catch (Exception ex)
{
Log.Error("MusicDatabase: Upgrade indexes failed. Err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
public void InitDatabase()
{
bool dbExists;
try
{
// Open database
dbExists = System.IO.File.Exists(Config.GetFile(Config.Dir.Database, "YouTubeFm_Data_V01.db3"));
m_db = new SQLiteClient(Config.GetFile(Config.Dir.Database, "YouTubeFm_Data_V01.db3"));
DatabaseUtility.SetPragmas(m_db);
if (!dbExists)
{
m_db.Execute(
"CREATE TABLE VIDEOS(ID integer primary key autoincrement,VIDEO_ID text, ARTIST_ID text, TITLE text, IMG_URL text, LENGTH integer,STATE integer, rating integer, hd integer)\n");
m_db.Execute(
"CREATE TABLE PLAY_HISTORY(ID integer primary key autoincrement,VIDEO_ID text, datePlayed timestamp, loved integer)\n");
CreateArtistTable(m_db);
DatabaseUtility.AddIndex(m_db, "idx_video_id", "CREATE INDEX idx_video_id ON VIDEOS(VIDEO_ID)");
DatabaseUtility.AddIndex(m_db, "idx_ARTIST_ID", "CREATE INDEX idx_ARTIST_ID ON VIDEOS(ARTIST_ID)");
DatabaseUtility.AddIndex(m_db, "idx_his_video_id", "CREATE INDEX idx_his_video_id ON PLAY_HISTORY(VIDEO_ID)");
DatabaseUtility.AddIndex(m_db, "idx_his_date", "CREATE INDEX idx_his_date ON PLAY_HISTORY(datePlayed DESC)");
}
else
{
if (!DatabaseUtility.TableColumnExists(m_db, "VIDEOS", "hd"))
{
m_db.Execute("ALTER TABLE VIDEOS ADD hd integer");
}
if (!DatabaseUtility.TableExists(m_db, "ARTISTS"))
{
CreateArtistTable(m_db);
}
}
}
catch (SQLiteException ex)
{
Log.Error("database exception err:{0} stack:{1}", ex.Message, ex.StackTrace);
}
}
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);
}
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);
}
private void CreateArtistTable(SQLiteClient db)
{
db.Execute("CREATE TABLE ARTISTS(ID integer primary key autoincrement,ARTIST_ID text, ARTIST_NAME text, ARTIST_IMG text, ARTIST_BIO text, ARTIST_USER text, ARTIST_TAG text, ARTIST_GENRE text, ARTIST_IMG_URL text, ARTIST_PLAYED integer)\n");
DatabaseUtility.AddIndex(m_db, "idx_artists_name", "CREATE INDEX idx_artists_name ON ARTISTS(ARTIST_NAME)");
DatabaseUtility.AddIndex(m_db, "idx_artists_ARTIST_ID", "CREATE INDEX idx_ARTIST_ID ON ARTISTS(ARTIST_ID)");
}