dave
e5158cfdc7
if filesystem permissions on the directory the sqlite database database file is in are such that the app cannot list or create files, sqlite gives a vague error:
```
sqlite3.OperationalError: unable to open database file
```
whereas python's open() will give a better hint ("permission denied"). So, we try opening the database file with python first.
also, add chmods to the startup scripts to avoid this issue in the future
555 lines
19 KiB
Python
555 lines
19 KiB
Python
import os
|
|
import sqlite3
|
|
import logging
|
|
from hashlib import sha512
|
|
from time import time
|
|
from contextlib import closing
|
|
from collections import Iterable
|
|
|
|
|
|
from pysonic.scanner import PysonicFilesystemScanner
|
|
|
|
|
|
logger = logging.getLogger("database")
|
|
|
|
|
|
LETTER_GROUPS = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t",
|
|
"u", "v", "w", "xyz", "0123456789"]
|
|
|
|
|
|
keys_in_table = ["title", "album", "artist", "type", "size"]
|
|
|
|
|
|
def dict_factory(c, row):
|
|
d = {}
|
|
for idx, col in enumerate(c.description):
|
|
d[col[0]] = row[idx]
|
|
return d
|
|
|
|
|
|
class NotFoundError(Exception):
|
|
pass
|
|
|
|
|
|
class DuplicateRootException(Exception):
|
|
pass
|
|
|
|
|
|
def hash_password(unicode_string):
|
|
return sha512(unicode_string.encode('UTF-8')).hexdigest()
|
|
|
|
|
|
def cursor(func):
|
|
"""
|
|
Provides a cursor to the wrapped method as the first arg.
|
|
"""
|
|
def wrapped(*args, **kwargs):
|
|
self = args[0]
|
|
if len(args) >= 2 and isinstance(args[1], sqlite3.Cursor):
|
|
return func(*args, **kwargs)
|
|
else:
|
|
with closing(self.db.cursor()) as c:
|
|
return func(self, c, *args[1:], **kwargs)
|
|
return wrapped
|
|
|
|
|
|
class PysonicDatabase(object):
|
|
def __init__(self, path):
|
|
self.sqlite_opts = dict(check_same_thread=False)
|
|
self.path = path
|
|
self.db = None
|
|
self.open()
|
|
self.migrate()
|
|
self.scanner = PysonicFilesystemScanner(self)
|
|
|
|
def open(self):
|
|
with open(self.path, "rb"): # sqlite doesn't give very descriptive permission errors, but this does
|
|
pass
|
|
self.db = sqlite3.connect(self.path, **self.sqlite_opts)
|
|
self.db.row_factory = dict_factory
|
|
|
|
def update(self):
|
|
"""
|
|
Start the library media scanner ands
|
|
"""
|
|
self.scanner.init_scan()
|
|
|
|
def migrate(self):
|
|
# Create db
|
|
queries = ["""CREATE TABLE 'libraries' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'name' TEXT,
|
|
'path' TEXT UNIQUE);""",
|
|
"""CREATE TABLE 'dirs' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'parent' INTEGER,
|
|
'name' TEXT,
|
|
UNIQUE(parent, name)
|
|
)""",
|
|
"""CREATE TABLE 'genres' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'name' TEXT UNIQUE)""",
|
|
"""CREATE TABLE 'artists' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'libraryid' INTEGER,
|
|
'dir' INTEGER UNIQUE,
|
|
'name' TEXT)""",
|
|
"""CREATE TABLE 'albums' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'artistid' INTEGER,
|
|
'coverid' INTEGER,
|
|
'dir' INTEGER,
|
|
'name' TEXT,
|
|
'added' INTEGER NOT NULL DEFAULT -1,
|
|
'played' INTEGER,
|
|
'plays' INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE (artistid, dir));""",
|
|
"""CREATE TABLE 'songs' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'albumid' BOOLEAN,
|
|
'genre' INTEGER DEFAULT NULL,
|
|
'file' TEXT UNIQUE, -- path from the library root
|
|
'size' INTEGER NOT NULL DEFAULT -1,
|
|
'title' TEXT NOT NULL,
|
|
'lastscan' INTEGER NOT NULL DEFAULT -1,
|
|
'format' TEXT,
|
|
'length' INTEGER,
|
|
'bitrate' INTEGER,
|
|
'track' INTEGER,
|
|
'year' INTEGER,
|
|
'plays' INTEGER NOT NULL DEFAULT 0
|
|
)""",
|
|
"""CREATE TABLE 'covers' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
'library' INTEGER,
|
|
'type' TEXT,
|
|
'size' TEXT,
|
|
'path' TEXT UNIQUE);""",
|
|
"""CREATE TABLE 'users' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
'username' TEXT UNIQUE NOT NULL,
|
|
'password' TEXT NOT NULL,
|
|
'admin' BOOLEAN DEFAULT 0,
|
|
'email' TEXT)""",
|
|
"""CREATE TABLE 'stars' (
|
|
'userid' INTEGER,
|
|
'songid' INTEGER,
|
|
primary key ('userid', 'songid'))""",
|
|
"""CREATE TABLE 'playlists' (
|
|
'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
|
|
'ownerid' INTEGER,
|
|
'name' TEXT,
|
|
'public' BOOLEAN,
|
|
'created' INTEGER,
|
|
'changed' INTEGER,
|
|
'cover' INTEGER,
|
|
UNIQUE ('ownerid', 'name'))""",
|
|
"""CREATE TABLE 'playlist_entries' (
|
|
'playlistid' INTEGER,
|
|
'songid' INTEGER,
|
|
'order' FLOAT)""",
|
|
"""CREATE TABLE 'meta' (
|
|
'key' TEXT PRIMARY KEY NOT NULL,
|
|
'value' TEXT);""",
|
|
"""INSERT INTO meta VALUES ('db_version', '1');"""]
|
|
|
|
with closing(self.db.cursor()) as c:
|
|
c.execute("SELECT * FROM sqlite_master WHERE type='table' AND name='meta'")
|
|
|
|
# Initialize DB
|
|
if len(c.fetchall()) == 0:
|
|
logger.warning("Initializing database")
|
|
for query in queries:
|
|
c.execute(query)
|
|
c.execute("COMMIT")
|
|
else:
|
|
# Migrate if old db exists
|
|
# c.execute("""UPDATE meta SET value=? WHERE key="db_version";""", (str(version), ))
|
|
# logger.warning("db schema is version {}".format(version))
|
|
pass
|
|
|
|
def get_artist_info(self, item_id):
|
|
#TODO
|
|
return {"biography": "placeholder biography",
|
|
"musicBrainzId": "playerholder",
|
|
"lastFmUrl": "https://www.last.fm/music/Placeholder",
|
|
"smallImageUrl": "",
|
|
"mediumImageUrl": "",
|
|
"largeImageUrl": "",
|
|
"similarArtists": []}
|
|
|
|
@cursor
|
|
def get_stats(self, c):
|
|
songs = c.execute("SELECT COUNT(*) as cnt FROM songs").fetchone()['cnt']
|
|
artists = c.execute("SELECT COUNT(*) as cnt FROM artists").fetchone()['cnt']
|
|
albums = c.execute("SELECT COUNT(*) as cnt FROM albums").fetchone()['cnt']
|
|
return dict(songs=songs, artists=artists, albums=albums)
|
|
|
|
# Music related
|
|
@cursor
|
|
def add_root(self, c, path, name="Library"):
|
|
"""
|
|
Add a new library root. Returns the root ID or raises on collision
|
|
:param path: normalized absolute path to add to the library
|
|
:type path: str:
|
|
:return: int
|
|
:raises: sqlite3.IntegrityError
|
|
"""
|
|
path = os.path.abspath(os.path.normpath(path))
|
|
try:
|
|
c.execute("INSERT INTO libraries ('name', 'path') VALUES (?, ?)", (name, path, ))
|
|
c.execute("COMMIT")
|
|
return c.lastrowid
|
|
except sqlite3.IntegrityError:
|
|
raise DuplicateRootException("Root '{}' already exists".format(path))
|
|
|
|
@cursor
|
|
def get_libraries(self, c, id=None):
|
|
libs = []
|
|
q = "SELECT * FROM libraries"
|
|
params = []
|
|
conditions = []
|
|
if id:
|
|
conditions.append("id = ?")
|
|
params.append(id)
|
|
if conditions:
|
|
q += " WHERE " + " AND ".join(conditions)
|
|
c.execute(q, params)
|
|
for row in c:
|
|
libs.append(row)
|
|
return libs
|
|
|
|
@cursor
|
|
def get_artists(self, c, id=None, dirid=None, sortby="name", order=None, name_contains=None):
|
|
assert order in ["asc", "desc", None]
|
|
artists = []
|
|
q = "SELECT * FROM artists"
|
|
params = []
|
|
conditions = []
|
|
if id:
|
|
conditions.append("id = ?")
|
|
params.append(id)
|
|
if dirid:
|
|
conditions.append("dir = ?")
|
|
params.append(dirid)
|
|
if name_contains:
|
|
conditions.append("name LIKE ?")
|
|
params.append("%{}%".format(name_contains))
|
|
if conditions:
|
|
q += " WHERE " + " AND ".join(conditions)
|
|
if sortby:
|
|
q += " ORDER BY {} {}".format(sortby, order.upper() if order else "ASC")
|
|
c.execute(q, params)
|
|
for row in c:
|
|
artists.append(row)
|
|
return artists
|
|
|
|
@cursor
|
|
def get_albums(self, c, id=None, artist=None, sortby="name", order=None, limit=None, name_contains=None):
|
|
"""
|
|
:param limit: int or tuple of int, int. translates directly to sql logic.
|
|
"""
|
|
if order:
|
|
order = {"asc": "ASC", "desc": "DESC"}[order]
|
|
|
|
if sortby == "random":
|
|
sortby = "RANDOM()"
|
|
|
|
albums = []
|
|
|
|
q = """
|
|
SELECT
|
|
alb.*,
|
|
art.name as artistname,
|
|
dirs.parent as artistdir
|
|
FROM albums as alb
|
|
INNER JOIN artists as art
|
|
on alb.artistid = art.id
|
|
INNER JOIN dirs
|
|
on dirs.id = alb.dir
|
|
"""
|
|
params = []
|
|
|
|
conditions = []
|
|
if id:
|
|
conditions.append("id = ?")
|
|
params.append(id)
|
|
if artist:
|
|
conditions.append("artistid = ?")
|
|
params.append(artist)
|
|
if name_contains:
|
|
conditions.append("alb.name LIKE ?")
|
|
params.append("%{}%".format(name_contains))
|
|
if conditions:
|
|
q += " WHERE " + " AND ".join(conditions)
|
|
|
|
if sortby:
|
|
q += " ORDER BY {}".format(sortby)
|
|
if order:
|
|
q += " {}".format(order)
|
|
|
|
if limit:
|
|
q += " LIMIT {}".format(limit) if isinstance(limit, int) \
|
|
else " LIMIT {}, {}".format(*limit)
|
|
|
|
c.execute(q, params)
|
|
for row in c:
|
|
albums.append(row)
|
|
return albums
|
|
|
|
@cursor
|
|
def get_songs(self, c, id=None, genre=None, sortby="title", order=None, limit=None, title_contains=None):
|
|
# TODO make this query massively uglier by joining albums and artists so that artistid etc can be a filter
|
|
# or maybe lookup those IDs in the library layer?
|
|
if order:
|
|
order = {"asc": "ASC", "desc": "DESC"}[order]
|
|
|
|
if sortby == "random":
|
|
sortby = "RANDOM()"
|
|
|
|
songs = []
|
|
|
|
q = """
|
|
SELECT
|
|
s.*,
|
|
lib.path as root,
|
|
alb.name as albumname,
|
|
alb.coverid as albumcoverid,
|
|
art.name as artistname,
|
|
g.name as genrename,
|
|
albdir.id as albumdir
|
|
FROM songs as s
|
|
INNER JOIN libraries as lib
|
|
on s.library == lib.id
|
|
INNER JOIN albums as alb
|
|
on s.albumid == alb.id
|
|
INNER JOIN dirs as albdir
|
|
on albdir.id = alb.dir
|
|
INNER JOIN artists as art
|
|
on alb.artistid = art.id
|
|
LEFT JOIN genres as g
|
|
on s.genre == g.id
|
|
"""
|
|
|
|
params = []
|
|
|
|
conditions = []
|
|
if id and isinstance(id, int):
|
|
conditions.append("s.id = ?")
|
|
params.append(id)
|
|
elif id and isinstance(id, Iterable):
|
|
conditions.append("s.id IN ({})".format(",".join("?" * len(id))))
|
|
params += id
|
|
if genre:
|
|
conditions.append("g.name = ?")
|
|
params.append(genre)
|
|
if title_contains:
|
|
conditions.append("s.title LIKE ?")
|
|
params.append("%{}%".format(title_contains))
|
|
if conditions:
|
|
q += " WHERE " + " AND ".join(conditions)
|
|
|
|
if sortby:
|
|
q += " ORDER BY {}".format(sortby)
|
|
if order:
|
|
q += " {}".format(order)
|
|
|
|
if limit:
|
|
q += " LIMIT {}".format(limit) # TODO support limit pagination
|
|
|
|
c.execute(q, params)
|
|
for row in c:
|
|
songs.append(row)
|
|
return songs
|
|
|
|
@cursor
|
|
def get_genres(self, c, genre_id=None):
|
|
genres = []
|
|
q = "SELECT * FROM genres"
|
|
params = []
|
|
conditions = []
|
|
if genre_id:
|
|
conditions.append("id = ?")
|
|
params.append(genre_id)
|
|
if conditions:
|
|
q += " WHERE " + " AND ".join(conditions)
|
|
c.execute(q, params)
|
|
for row in c:
|
|
genres.append(row)
|
|
return genres
|
|
|
|
@cursor
|
|
def get_cover(self, c, cover_id):
|
|
cover = None
|
|
for cover in c.execute("SELECT * FROM covers WHERE id = ?", (cover_id, )):
|
|
return cover
|
|
|
|
def get_cover_path(self, cover_id):
|
|
cover = self.get_cover(cover_id)
|
|
library = self.get_libraries(cover["library"])[0]
|
|
return os.path.join(library["path"], cover["path"])
|
|
|
|
@cursor
|
|
def get_subsonic_musicdir(self, c, dirid):
|
|
"""
|
|
The world is a harsh place.
|
|
Again, this bullshit exists only to serve subsonic clients. Given a directory ID it returns a dict containing:
|
|
- the directory itself
|
|
- its parent
|
|
- its child dirs
|
|
- its child media
|
|
|
|
that's a lie, it's a tuple and it's full of BS. read the code
|
|
"""
|
|
# find directory
|
|
dirinfo = None
|
|
for dirinfo in c.execute("SELECT * FROM dirs WHERE id = ?", (dirid, )):
|
|
pass
|
|
assert dirinfo
|
|
|
|
ret = None
|
|
|
|
# see if it matches the artists or albums table
|
|
artist = None
|
|
for artist in c.execute("SELECT * FROM artists WHERE dir = ?", (dirid, )):
|
|
pass
|
|
|
|
# if artist:
|
|
# get child albums
|
|
if artist:
|
|
ret = ("artist", dirinfo, artist)
|
|
children = []
|
|
for album in c.execute("SELECT * FROM albums WHERE artistid = ?", (artist["id"], )):
|
|
children.append(("album", album))
|
|
ret[2]['children'] = children
|
|
return ret
|
|
|
|
# else if album:
|
|
# get child tracks
|
|
album = None
|
|
for album in c.execute("SELECT * FROM albums WHERE dir = ?", (dirid, )):
|
|
pass
|
|
if album:
|
|
ret = ("album", dirinfo, album)
|
|
|
|
artist_info = c.execute("SELECT * FROM artists WHERE id = ?", (album["artistid"], )).fetchall()[0]
|
|
|
|
children = []
|
|
for song in c.execute("SELECT * FROM songs WHERE albumid = ? ORDER BY track, title ASC;", (album["id"], )):
|
|
song["_artist"] = artist_info
|
|
children.append(("song", song))
|
|
ret[2]['children'] = children
|
|
return ret
|
|
|
|
# Playlist related
|
|
@cursor
|
|
def add_playlist(self, c, ownerid, name, song_ids, public=False):
|
|
"""
|
|
Create a playlist
|
|
"""
|
|
now = time()
|
|
c.execute("INSERT INTO playlists (ownerid, name, public, created, changed) VALUES (?, ?, ?, ?, ?)",
|
|
(ownerid, name, public, now, now))
|
|
plid = c.lastrowid
|
|
for song_id in song_ids:
|
|
self.add_to_playlist(c, plid, song_id)
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def add_to_playlist(self, c, playlist_id, song_id):
|
|
# TODO deal with order column
|
|
c.execute("INSERT INTO playlist_entries (playlistid, songid) VALUES (?, ?)", (playlist_id, song_id))
|
|
|
|
@cursor
|
|
def get_playlist(self, c, playlist_id):
|
|
return c.execute("SELECT * FROM playlists WHERE id=?", (playlist_id, )).fetchone()
|
|
|
|
@cursor
|
|
def get_playlist_songs(self, c, playlist_id):
|
|
songs = []
|
|
q = """
|
|
SELECT
|
|
s.*,
|
|
alb.name as albumname,
|
|
alb.coverid as albumcoverid,
|
|
art.name as artistname,
|
|
art.name as artistid,
|
|
g.name as genrename
|
|
FROM playlist_entries as pe
|
|
INNER JOIN songs as s
|
|
on pe.songid == s.id
|
|
INNER JOIN albums as alb
|
|
on s.albumid == alb.id
|
|
INNER JOIN artists as art
|
|
on alb.artistid = art.id
|
|
LEFT JOIN genres as g
|
|
on s.genre == g.id
|
|
WHERE pe.playlistid = ?
|
|
ORDER BY pe.'order' ASC;
|
|
"""
|
|
for row in c.execute(q, (playlist_id, )):
|
|
songs.append(row)
|
|
return songs
|
|
|
|
@cursor
|
|
def get_playlists(self, c, user_id):
|
|
playlists = []
|
|
for row in c.execute("SELECT * FROM playlists WHERE ownerid=? or public=1", (user_id, )):
|
|
playlists.append(row)
|
|
return playlists
|
|
|
|
@cursor
|
|
def remove_index_from_playlist(self, c, playlist_id, index):
|
|
c.execute("DELETE FROM playlist_entries WHERE playlistid=? LIMIT ?, 1", (playlist_id, index, ))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def empty_playlist(self, c, playlist_id):
|
|
#TODO combine with delete_playlist
|
|
c.execute("DELETE FROM playlist_entries WHERE playlistid=?", (playlist_id, ))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def delete_playlist(self, c, playlist_id):
|
|
c.execute("DELETE FROM playlist_entries WHERE playlistid=?", (playlist_id, ))
|
|
c.execute("DELETE FROM playlists WHERE id=?", (playlist_id, ))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def update_album_played(self, c, album_id, last_played=None):
|
|
c.execute("UPDATE albums SET played=? WHERE id=?", (last_played, album_id, ))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def increment_album_plays(self, c, album_id):
|
|
c.execute("UPDATE albums SET plays = plays + 1 WHERE id=?", (album_id, ))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def increment_track_plays(self, c, track_id):
|
|
c.execute("UPDATE songs SET plays = plays + 1 WHERE id=?", (track_id, ))
|
|
c.execute("COMMIT")
|
|
|
|
# User related
|
|
@cursor
|
|
def add_user(self, c, username, password, is_admin=False):
|
|
c.execute("INSERT INTO users (username, password, admin) VALUES (?, ?, ?)",
|
|
(username, hash_password(password), is_admin))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def update_user(self, c, username, password, is_admin=False):
|
|
c.execute("UPDATE users SET password=?, admin=? WHERE username=?;",
|
|
(hash_password(password), is_admin, username))
|
|
c.execute("COMMIT")
|
|
|
|
@cursor
|
|
def get_user(self, c, user):
|
|
try:
|
|
column = "id" if type(user) is int else "username"
|
|
return c.execute("SELECT * FROM users WHERE {}=?;".format(column), (user, )).fetchall()[0]
|
|
except IndexError:
|
|
raise NotFoundError("User doesn't exist")
|