gistfile1.cs

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);
            }
        }
    }
}