private void InitializeFreshDatabase (bool refresh_metadata)
{
Execute("DROP TABLE IF EXISTS CoreConfiguration");
Execute("DROP TABLE IF EXISTS CoreTracks");
Execute("DROP TABLE IF EXISTS CoreArtists");
Execute("DROP TABLE IF EXISTS CoreAlbums");
Execute("DROP TABLE IF EXISTS CorePlaylists");
Execute("DROP TABLE IF EXISTS CorePlaylistEntries");
Execute("DROP TABLE IF EXISTS CoreSmartPlaylists");
Execute("DROP TABLE IF EXISTS CoreSmartPlaylistEntries");
Execute("DROP TABLE IF EXISTS CoreRemovedTracks");
Execute("DROP TABLE IF EXISTS CoreTracksCache");
Execute("DROP TABLE IF EXISTS CoreCache");
Execute(@"
CREATE TABLE CoreConfiguration (
EntryID INTEGER PRIMARY KEY,
Key TEXT,
Value TEXT
)
");
Execute (String.Format ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'DatabaseVersion', {0})", CURRENT_VERSION));
if (!refresh_metadata) {
Execute (String.Format ("INSERT INTO CoreConfiguration (EntryID, Key, Value) VALUES (null, 'MetadataVersion', {0})", CURRENT_METADATA_VERSION));
}
Execute(@"
CREATE TABLE CorePrimarySources (
PrimarySourceID INTEGER PRIMARY KEY,
StringID TEXT UNIQUE,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
)
");
Execute ("INSERT INTO CorePrimarySources (StringID) VALUES ('MusicLibrarySource-Library')");
// TODO add these:
// Others to consider:
// AlbumArtist (TPE2) (in CoreAlbums?)
Execute(String.Format (@"
CREATE TABLE CoreTracks (
PrimarySourceID INTEGER NOT NULL,
TrackID INTEGER PRIMARY KEY,
ArtistID INTEGER,
AlbumID INTEGER,
TagSetID INTEGER,
ExternalID INTEGER,
MusicBrainzID TEXT,
Uri TEXT,
MimeType TEXT,
FileSize INTEGER,
BitRate INTEGER,
SampleRate INTEGER,
BitsPerSample INTEGER,
Attributes INTEGER DEFAULT {0},
LastStreamError INTEGER DEFAULT {1},
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
TrackNumber INTEGER,
TrackCount INTEGER,
Disc INTEGER,
DiscCount INTEGER,
Duration INTEGER,
Year INTEGER,
Genre TEXT,
Composer TEXT,
Conductor TEXT,
Grouping TEXT,
Copyright TEXT,
LicenseUri TEXT,
Comment TEXT,
Rating INTEGER,
Score INTEGER,
PlayCount INTEGER,
SkipCount INTEGER,
LastPlayedStamp INTEGER,
LastSkippedStamp INTEGER,
DateAddedStamp INTEGER,
DateUpdatedStamp INTEGER,
MetadataHash TEXT,
BPM INTEGER,
LastSyncedStamp INTEGER,
FileModifiedStamp INTEGER
)
", (int)TrackMediaAttributes.Default, (int)StreamPlaybackError.None));
Execute("CREATE INDEX CoreTracksPrimarySourceIndex ON CoreTracks(ArtistID, AlbumID, PrimarySourceID, Disc, TrackNumber, Uri)");
Execute("CREATE INDEX CoreTracksAggregatesIndex ON CoreTracks(FileSize, Duration)");
Execute("CREATE INDEX CoreTracksExternalIDIndex ON CoreTracks(PrimarySourceID, ExternalID)");
Execute("CREATE INDEX CoreTracksUriIndex ON CoreTracks(PrimarySourceID, Uri)");
Execute("CREATE INDEX CoreTracksCoverArtIndex ON CoreTracks (PrimarySourceID, AlbumID, DateUpdatedStamp)");
Execute(@"
CREATE TABLE CoreAlbums (
AlbumID INTEGER PRIMARY KEY,
ArtistID INTEGER,
TagSetID INTEGER,
MusicBrainzID TEXT,
Title TEXT,
TitleLowered TEXT,
TitleSort TEXT,
TitleSortKey BLOB,
ReleaseDate INTEGER,
Duration INTEGER,
Year INTEGER,
IsCompilation INTEGER DEFAULT 0,
ArtistName TEXT,
ArtistNameLowered TEXT,
ArtistNameSort TEXT,
ArtistNameSortKey BLOB,
Rating INTEGER,
ArtworkID TEXT
)
");
Execute ("CREATE INDEX CoreAlbumsIndex ON CoreAlbums(ArtistID, TitleSortKey)");
Execute ("CREATE INDEX CoreAlbumsArtistIndex ON CoreAlbums(TitleSortKey, ArtistNameSortKey)");
Execute(@"
CREATE TABLE CoreArtists (
ArtistID INTEGER PRIMARY KEY,
TagSetID INTEGER,
MusicBrainzID TEXT,
Name TEXT,
NameLowered TEXT,
NameSort TEXT,
NameSortKey BLOB,
Rating INTEGER
)
");
Execute ("CREATE INDEX CoreArtistsIndex ON CoreArtists(NameSortKey)");
Execute(@"
CREATE TABLE CorePlaylists (
PrimarySourceID INTEGER,
PlaylistID INTEGER PRIMARY KEY,
Name TEXT,
SortColumn INTEGER NOT NULL DEFAULT -1,
SortType INTEGER NOT NULL DEFAULT 0,
Special INTEGER NOT NULL DEFAULT 0,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0
)
");
Execute(@"
CREATE TABLE CorePlaylistEntries (
EntryID INTEGER PRIMARY KEY,
PlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL,
ViewOrder INTEGER NOT NULL DEFAULT 0,
Generated INTEGER NOT NULL DEFAULT 0
)
");
Execute("CREATE INDEX CorePlaylistEntriesIndex ON CorePlaylistEntries(PlaylistID, TrackID)");
Execute(@"
CREATE TABLE CoreSmartPlaylists (
PrimarySourceID INTEGER,
SmartPlaylistID INTEGER PRIMARY KEY,
Name TEXT NOT NULL,
Condition TEXT,
OrderBy TEXT,
LimitNumber TEXT,
LimitCriterion TEXT,
CachedCount INTEGER,
IsTemporary INTEGER DEFAULT 0,
IsHiddenWhenEmpty INTEGER DEFAULT 0
)
");
Execute(@"
CREATE TABLE CoreSmartPlaylistEntries (
EntryID INTEGER PRIMARY KEY,
SmartPlaylistID INTEGER NOT NULL,
TrackID INTEGER NOT NULL
)
");
Execute ("CREATE INDEX CoreSmartPlaylistEntriesIndex ON CoreSmartPlaylistEntries(SmartPlaylistID, TrackID)");
Execute(@"
CREATE TABLE CoreRemovedTracks (
TrackID INTEGER NOT NULL,
Uri TEXT,
DateRemovedStamp INTEGER
)
");
Execute(@"
CREATE TABLE CoreCacheModels (
CacheID INTEGER PRIMARY KEY,
ModelID TEXT
)
");
// This index slows down queries were we shove data into the CoreCache.
// Since we do that frequently, not using it.
//Execute("CREATE INDEX CoreCacheModelId ON CoreCache(ModelID)");
Execute(@"
CREATE TABLE CoreShuffles (
ShufflerId INTEGER,
TrackID INTEGER,
LastShuffledAt INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
)
");
Execute("CREATE INDEX CoreShufflesIndex ON CoreShuffles (ShufflerId, TrackID, LastShuffledAt)");
Execute(@"
CREATE TABLE CoreShufflers (
ShufflerId INTEGER PRIMARY KEY,
Id TEXT UNIQUE
)
");
Execute (@"
CREATE TABLE CoreShuffleModifications (
ShufflerId INTEGER,
TrackID INTEGER,
LastModifiedAt INTEGER,
ModificationType INTEGER,
CONSTRAINT one_entry_per_track UNIQUE (ShufflerID, TrackID)
)
");
Execute ("CREATE INDEX CoreShuffleModificationsIndex ON CoreShuffleModifications (ShufflerId, TrackID, LastModifiedAt, ModificationType)");
}