Skip to content

Instantly share code, notes, and snippets.

@jamiepenney
Created June 10, 2014 02:35
Show Gist options
  • Save jamiepenney/66eff1058675dea05355 to your computer and use it in GitHub Desktop.
Save jamiepenney/66eff1058675dea05355 to your computer and use it in GitHub Desktop.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Threading.Tasks;
using Caliburn.Micro;
using ListenToMe.Models;
using ListenToMe.ViewModels;
using ListenToMe.ViewModels.Pages;
using QEDCode;
using SQLite;
namespace ListenToMe.Services
{
public class MusicDatabase
{
private readonly ILibraryScanner _libraryScanner;
private LibraryViewModel _currentLibrary;
private readonly AwaitableCriticalSection _criticalSection = new AwaitableCriticalSection();
private readonly SQLiteAsyncConnection _connection = new SQLiteAsyncConnection("music.db");
public MusicDatabase(ILibraryScanner libraryScanner)
{
_libraryScanner = libraryScanner;
}
public async Task CreateDb()
{
await _connection.CreateTableAsync<LibraryEntryModel>();
await _connection.CreateTableAsync<PlaylistModel>();
await _connection.CreateTableAsync<PlaylistEntryModel>();
await _connection.ExecuteAsync("CREATE VIRTUAL TABLE IF NOT EXISTS tracks USING fts4(artist, album, title);");
}
public async Task<List<SearchResult>> SearchForTracks(string text)
{
return await _connection.QueryAsync<SearchResult>("SELECT rowid, * FROM tracks WHERE tracks MATCH ?;", text);
}
public async Task DeleteAllPlaylists()
{
using (await _criticalSection.EnterAsync())
{
await _connection.ExecuteAsync("delete from PlaylistEntries");
await _connection.ExecuteAsync("delete from Playlist");
}
}
/// <summary>
/// Connects to the database, loads the full music library from it, then attaches event handlers to that
/// instance to do automatic inserts/deletes when new items are added/deleted
/// </summary>
/// <returns></returns>
public async Task<LibraryViewModel> LoadFullLibrary(IEventAggregator eventAggregator)
{
List<LibraryEntryModel> entries;
using (await _criticalSection.EnterAsync())
{
if (_currentLibrary != null)
{
_currentLibrary.EntryAdded -= Library_OnEntryAdded;
_currentLibrary.EntryRemoved -= Library_OnEntryRemoved;
_currentLibrary.LibraryCleared -= Library_OnLibraryCleared;
_currentLibrary = null;
}
else
{
// On first run, attempt to create the database tables if they don't already exist.
await CreateDb();
}
entries = await _connection.Table<LibraryEntryModel>().ToListAsync();
}
_currentLibrary = new LibraryViewModel(entries, _libraryScanner, eventAggregator, this);
_currentLibrary.EntryAdded += Library_OnEntryAdded;
_currentLibrary.EntryRemoved += Library_OnEntryRemoved;
_currentLibrary.LibraryCleared += Library_OnLibraryCleared;
return _currentLibrary;
}
public async Task<List<PlaylistModel>> LoadAllPlaylists()
{
Dictionary<int, PlaylistModel> playlists;
List<PlaylistEntryModel> playlistItems;
using (await _criticalSection.EnterAsync())
{
playlists = (await _connection.Table<PlaylistModel>().ToListAsync()).ToDictionary(p => p.Id);
playlistItems = await _connection.Table<PlaylistEntryModel>().ToListAsync();
}
return playlists
.GroupJoin(
playlistItems,
kv => kv.Key,
e => e.PlaylistId,
(kv, e) => LoadPlaylistFromDB(kv.Value, e))
.Select(AttachToPlaylist)
.ToList();
}
private PlaylistModel LoadPlaylistFromDB(PlaylistModel dbModel, IEnumerable<PlaylistEntryModel> dbEntries)
{
dbModel.Entries.AddRange(dbEntries);
return dbModel;
}
public async Task<PlaylistModel> CreateNewPlaylist()
{
var model = new PlaylistModel { Title = "New Playlist" };
await _connection.InsertAsync(model);
AttachToPlaylist(model);
return model;
}
private PlaylistModel AttachToPlaylist(PlaylistModel model)
{
model.PropertyChanged += Playlist_PropertyChanged;
model.EntryAdded += Playlist_EntryAdded;
model.PlaylistCleared += Playlist_PlaylistCleared;
return model;
}
public async Task CreateOrUpdate(LibraryEntryViewModel entry)
{
if (entry.Id == 0)
await Create(entry);
else
await Update(entry);
}
private async Task Create(LibraryEntryViewModel entry)
{
using (await _criticalSection.EnterAsync())
{
await _connection.InsertAsync(entry.Model);
await _connection.ExecuteAsync("insert into tracks(rowid, artist, album, title) VALUES (?, ?, ?, ?)",
entry.Id, entry.Artist, entry.Album, entry.Title);
}
}
private async Task Update(LibraryEntryViewModel entry)
{
using (await _criticalSection.EnterAsync())
{
await _connection.UpdateAsync(entry.Model);
await _connection.ExecuteAsync("update tracks set artist = ?, album = ?, title = ? where rowid = ?",
entry.Artist, entry.Album, entry.Title, entry.Id);
}
}
private async void Playlist_PlaylistCleared(PlaylistModel sender, PlaylistClearedEventArgs args)
{
await _connection.ExecuteAsync("delete from PlaylistEntries where PlaylistId = ?", sender.Id);
}
private async void Playlist_EntryAdded(PlaylistModel sender, PlaylistEntryEventArgs args)
{
await _connection.InsertAsync(args.Entry);
}
private async void Playlist_PropertyChanged(object sender, PropertyChangedEventArgs e)
{
var playlist = sender as PlaylistModel;
if (playlist != null)
{
await _connection.UpdateAsync(playlist);
}
}
private async void Library_OnLibraryCleared(LibraryViewModel sender, LibraryClearedEventArgs args)
{
using (await _criticalSection.EnterAsync())
{
await _connection.ExecuteAsync("delete from PlaylistEntries");
await _connection.ExecuteAsync("delete from Playlist");
await _connection.ExecuteAsync("delete from LibraryEntries");
await _connection.ExecuteAsync("delete from tracks");
}
}
private async void Library_OnEntryAdded(LibraryViewModel sender, LibraryEntryEventArgs args)
{
await CreateOrUpdate(args.Entry);
}
private async void Library_OnEntryRemoved(LibraryViewModel sender, LibraryEntryEventArgs args)
{
using (await _criticalSection.EnterAsync())
{
await _connection.DeleteAsync(args.Entry.Model);
await _connection.ExecuteAsync("delete from tracks where rowid = ?", args.Entry.Id);
}
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment