func (s *SqliteBackend) SongsInPath(path string) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"WHERE songs.file_name LIKE ?;", path+"%")
}
SongsInPath loads a slice of all Song structs residing under the specified
filesystem path from the database
package data
import (
	"database/sql"
	"log"
	"os"
	"path"
	"github.com/mdlayher/wavepipe/common"
	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)
type SqliteBackend struct {
	Path string
	db   *sqlx.DB
}
func (s *SqliteBackend) DSN(path string) {
	s.Path = common.ExpandHomeDir(path)
}
func (s *SqliteBackend) Setup() error {
	_, err := os.Stat(s.Path)
	if err == nil {
		return nil
	}
	if !os.IsNotExist(err) {
		return err
	}
	log.Println("db: creating new database file:", s.Path)
	dir := path.Dir(s.Path) + "/"
	file := path.Base(s.Path)
	if err := os.MkdirAll(dir, 0775); err != nil {
		return err
	}
	dbBuf, err := Asset("res/sqlite/wavepipe.db")
	if err != nil {
		return err
	}
	dest, err := os.Create(dir + file)
	if err != nil {
		return err
	}
	if _, err := dest.Write(dbBuf); err != nil {
		return err
	}
	if err := dest.Close(); err != nil {
		return err
	}
	return nil
}
func (s *SqliteBackend) Open() error {
	db, err := sqlx.Open("sqlite3", s.Path)
	if err != nil {
		return err
	}
	if _, err := db.Exec("PRAGMA synchronous = OFF;"); err != nil {
		return err
	}
	if _, err := db.Exec("PRAGMA journal_mode = MEMORY;"); err != nil {
		return err
	}
	s.db = db
	return nil
}
func (s *SqliteBackend) Close() error {
	return s.db.Close()
}
func (s *SqliteBackend) ArtInPath(path string) ([]Art, error) {
	return s.artQuery("SELECT * FROM art WHERE file_name LIKE ?;", path+"%")
}
func (s *SqliteBackend) ArtNotInPath(path string) ([]Art, error) {
	return s.artQuery("SELECT * FROM art WHERE file_name NOT LIKE ?;", path+"%")
}
func (s *SqliteBackend) CountArt() (int64, error) {
	return s.integerQuery("SELECT COUNT(*) AS int FROM art;")
}
func (s *SqliteBackend) DeleteArt(a *Art) error {
	tx := s.db.MustBegin()
	tx.Exec("DELETE FROM art WHERE id = ?;", a.ID)
	tx.Exec("UPDATE songs SET art_id = 0 WHERE art_id = ?;", a.ID)
	return tx.Commit()
}
func (s *SqliteBackend) LoadArt(a *Art) error {
	if a.ID != 0 {
		if err := s.db.Get(a, "SELECT * FROM art WHERE id = ?;", a.ID); err != nil {
			return err
		}
		return nil
	}
	if err := s.db.Get(a, "SELECT * FROM art WHERE file_name = ?;", a.FileName); err != nil {
		return err
	}
	return nil
}
func (s *SqliteBackend) SaveArt(a *Art) error {
	query := "INSERT INTO art (`file_name`, `file_size`, `last_modified`) VALUES (?, ?, ?);"
	tx := s.db.MustBegin()
	tx.Exec(query, a.FileName, a.FileSize, a.LastModified)
	if err := tx.Commit(); err != nil {
		return err
	}
	if a.ID == 0 {
		if err := s.LoadArt(a); err != nil {
			return err
		}
	}
	return nil
}
func (s *SqliteBackend) AllArtists() ([]Artist, error) {
	return s.artistQuery("SELECT * FROM artists;")
}
func (s *SqliteBackend) AllArtistsByTitle() ([]Artist, error) {
	return s.artistQuery("SELECT * FROM artists ORDER BY title;")
}
func (s *SqliteBackend) LimitArtists(offset int, count int) ([]Artist, error) {
	return s.artistQuery("SELECT * FROM artists LIMIT ?, ?;", offset, count)
}
func (s *SqliteBackend) SearchArtists(query string) ([]Artist, error) {
	return s.artistQuery("SELECT * FROM artists WHERE title LIKE ?;", "%"+query+"%")
}
func (s *SqliteBackend) CountArtists() (int64, error) {
	return s.integerQuery("SELECT COUNT(*) AS int FROM artists;")
}
func (s *SqliteBackend) PurgeOrphanArtists() (int, error) {
	rows, err := s.db.Queryx("SELECT artists.id FROM artists LEFT JOIN songs ON " +
		"artists.id = songs.artist_id WHERE songs.artist_id IS NULL;")
	if err != nil && err != sql.ErrNoRows {
		return -1, err
	}
	defer rows.Close()
	tx := s.db.MustBegin()
	artist := new(Artist)
	total := 0
	for rows.Next() {
		if err := rows.StructScan(artist); err != nil {
			return -1, err
		}
		tx.Exec("DELETE FROM artists WHERE id = ?;", artist.ID)
		total++
	}
	if err := rows.Err(); err != nil {
		return -1, err
	}
	return total, tx.Commit()
}
func (s *SqliteBackend) DeleteArtist(a *Artist) error {
	tx := s.db.MustBegin()
	if a.ID != 0 {
		tx.Exec("DELETE FROM artists WHERE id = ?;", a.ID)
		return tx.Commit()
	}
	tx.Exec("DELETE FROM artists WHERE title = ?;", a.Title)
	return tx.Commit()
}
func (s *SqliteBackend) LoadArtist(a *Artist) error {
	if a.ID != 0 {
		if err := s.db.Get(a, "SELECT * FROM artists WHERE id = ?;", a.ID); err != nil {
			return err
		}
		return nil
	}
	if err := s.db.Get(a, "SELECT * FROM artists WHERE title = ?;", a.Title); err != nil {
		return err
	}
	return nil
}
func (s *SqliteBackend) SaveArtist(a *Artist) error {
	query := "INSERT INTO artists (`title`) VALUES (?);"
	tx := s.db.MustBegin()
	tx.Exec(query, a.Title)
	if err := tx.Commit(); err != nil {
		return err
	}
	if a.ID == 0 {
		if err := s.LoadArtist(a); err != nil {
			return err
		}
	}
	return nil
}
func (s *SqliteBackend) AllAlbums() ([]Album, error) {
	return s.albumQuery("SELECT albums.*,artists.title AS artist FROM albums " +
		"JOIN artists ON albums.artist_id = artists.id;")
}
func (s *SqliteBackend) LimitAlbums(offset int, count int) ([]Album, error) {
	return s.albumQuery("SELECT albums.*,artists.title AS artist FROM albums "+
		"JOIN artists ON albums.artist_id = artists.id LIMIT ?, ?;", offset, count)
}
func (s *SqliteBackend) AlbumsForArtist(ID int) ([]Album, error) {
	return s.albumQuery("SELECT albums.*,artists.title AS artist FROM albums "+
		"JOIN artists ON albums.artist_id = artists.id WHERE albums.artist_id = ?;", ID)
}
func (s *SqliteBackend) SearchAlbums(query string) ([]Album, error) {
	return s.albumQuery("SELECT albums.*,artists.title AS artist FROM albums "+
		"JOIN artists ON albums.artist_id = artists.id WHERE albums.title LIKE ?;", "%"+query+"%")
}
func (s *SqliteBackend) CountAlbums() (int64, error) {
	return s.integerQuery("SELECT COUNT(*) AS int FROM albums;")
}
func (s *SqliteBackend) PurgeOrphanAlbums() (int, error) {
	rows, err := s.db.Queryx("SELECT albums.id FROM albums LEFT JOIN songs ON " +
		"albums.id = songs.album_id WHERE songs.album_id IS NULL;")
	if err != nil && err != sql.ErrNoRows {
		return -1, err
	}
	defer rows.Close()
	tx := s.db.MustBegin()
	album := new(Album)
	total := 0
	for rows.Next() {
		if err := rows.StructScan(album); err != nil {
			return -1, err
		}
		tx.Exec("DELETE FROM albums WHERE id = ?;", album.ID)
		total++
	}
	if err := rows.Err(); err != nil {
		return -1, err
	}
	return total, tx.Commit()
}
func (s *SqliteBackend) DeleteAlbum(a *Album) error {
	tx := s.db.MustBegin()
	if a.ID != 0 {
		tx.Exec("DELETE FROM albums WHERE id = ?;", a.ID)
		return tx.Commit()
	}
	tx.Exec("DELETE FROM albums WHERE artist_id = ? AND title = ?;", a.ArtistID, a.Title)
	return tx.Commit()
}
func (s *SqliteBackend) LoadAlbum(a *Album) error {
	if a.ID != 0 {
		if err := s.db.Get(a, "SELECT albums.*,artists.title AS artist FROM albums "+
			"JOIN artists ON albums.artist_id = artists.id WHERE albums.id = ?;", a.ID); err != nil {
			return err
		}
		return nil
	}
	if err := s.db.Get(a, "SELECT albums.*,artists.title AS artist FROM albums "+
		"JOIN artists ON albums.artist_id = artists.id WHERE albums.artist_id = ? AND albums.title = ?;", a.ArtistID, a.Title); err != nil {
		return err
	}
	return nil
}
func (s *SqliteBackend) SaveAlbum(a *Album) error {
	query := "INSERT INTO albums (`artist_id`, `title`, `year`) VALUES (?, ?, ?);"
	tx := s.db.MustBegin()
	tx.Exec(query, a.ArtistID, a.Title, a.Year)
	if err := tx.Commit(); err != nil {
		return err
	}
	if a.ID == 0 {
		if err := s.LoadAlbum(a); err != nil {
			return err
		}
	}
	return nil
}
func (s *SqliteBackend) AllFolders() ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders;")
}
func (s *SqliteBackend) LimitFolders(offset int, count int) ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders LIMIT ?, ?;", offset, count)
}
func (s *SqliteBackend) Subfolders(parentID int) ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders WHERE parent_id = ?;", parentID)
}
func (s *SqliteBackend) FoldersInPath(path string) ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders WHERE path LIKE ?;", path+"%")
}
func (s *SqliteBackend) FoldersNotInPath(path string) ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders WHERE path NOT LIKE ?;", path+"%")
}
func (s *SqliteBackend) SearchFolders(query string) ([]Folder, error) {
	return s.folderQuery("SELECT * FROM folders WHERE title LIKE ?;", "%"+query+"%")
}
func (s *SqliteBackend) CountFolders() (int64, error) {
	return s.integerQuery("SELECT COUNT(*) AS int FROM folders;")
}
func (s *SqliteBackend) DeleteFolder(f *Folder) error {
	tx := s.db.MustBegin()
	if f.ID != 0 {
		tx.Exec("DELETE FROM folders WHERE id = ?;", f.ID)
		return tx.Commit()
	}
	tx.Exec("DELETE FROM folders WHERE path = ?;", f.Path)
	return tx.Commit()
}
func (s *SqliteBackend) LoadFolder(f *Folder) error {
	if f.ID != 0 {
		if err := s.db.Get(f, "SELECT * FROM folders WHERE id = ?;", f.ID); err != nil {
			return err
		}
		return nil
	}
	if err := s.db.Get(f, "SELECT * FROM folders WHERE path = ?;", f.Path); err != nil {
		return err
	}
	return nil
}
func (s *SqliteBackend) SaveFolder(f *Folder) error {
	query := "INSERT INTO folders (`parent_id`, `title`, `path`) VALUES (?, ?, ?);"
	tx := s.db.MustBegin()
	tx.Exec(query, f.ParentID, f.Title, f.Path)
	if err := tx.Commit(); err != nil {
		return err
	}
	if f.ID == 0 {
		if err := s.LoadFolder(f); err != nil {
			return err
		}
	}
	return nil
}
func (s *SqliteBackend) AllSongs() ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs " +
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id;")
}
func (s *SqliteBackend) LimitSongs(offset int, count int) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"LIMIT ?, ?;", offset, count)
}
func (s *SqliteBackend) RandomSongs(n int) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"ORDER BY RANDOM() LIMIT ?;", n)
}
func (s *SqliteBackend) SearchSongs(query string) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"WHERE songs.title LIKE ?;", "%"+query+"%")
}
func (s *SqliteBackend) SongsForAlbum(ID int) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"WHERE songs.album_id = ?;", ID)
}
func (s *SqliteBackend) SongsForArtist(ID int) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"WHERE songs.artist_id = ?;", ID)
}
func (s *SqliteBackend) SongsForFolder(ID int) ([]Song, error) {
	return s.songQuery("SELECT songs.*,artists.title AS artist,albums.title AS album FROM songs "+
		"JOIN artists ON songs.artist_id = artists.id JOIN albums ON songs.album_id = albums.id "+
		"WHERE songs.folder_id = ?;", ID)
}