def __init__(self, name, dbdef):
assert name and dbdef
self.name = name
self.desc = dbdef
self.db = BoundConnector(self.name)
with self:
self._init_meta()
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.normalize_basedir()
connector = BoundConnector(DBNAME, connector)
self.DBFILENAME = connector.dblocation
self.conn = connector.connection()
self.db = self.conn.cursor()
#I don't care about journaling!
self.conn.execute('PRAGMA synchronous = OFF')
self.conn.execute('PRAGMA journal_mode = MEMORY')
self.load_db_to_memory()
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.normalize_basedir()
connector = BoundConnector(DBNAME, connector)
self.DBFILENAME = connector.dblocation
self.conn = connector.connection()
self.db = self.conn.cursor()
#I don't care about journaling!
self.conn.execute('PRAGMA synchronous = OFF')
self.conn.execute('PRAGMA journal_mode = MEMORY')
self.load_db_to_memory()
def __init__(self, connector=None):
"""user configuration:
hidden values can not be set by the user in the options,
but might be subject of being set automatically, e.g. the
heartbeat.
"""
db.require(DBNAME, '0')
c = cfg.ConfigBuilder()
with c['keyboard_shortcuts'] as kbs:
kbs.valid = '\d\d?\d?'
kbs['prev'].value = 89
kbs['play'].value = 88
kbs['pause'].value = 67
kbs['stop'].value = 86
kbs['next'].value = 66
kbs['search'].value = 83
with c['misc.show_playlist_download_buttons'] as pl_download_buttons:
pl_download_buttons.value = False
with c['misc.autoplay_on_add'] as autoplay_on_add:
autoplay_on_add.value = False
with c['custom_theme.primary_color'] as primary_color:
primary_color.value = '#F02E75'
primary_color.valid = '#[0-9a-fA-F]{6}'
with c['custom_theme.white_on_black'] as white_on_black:
white_on_black.value = False
with c['last_time_online'] as last_time_online:
last_time_online.value = 0
last_time_online.valid = '\\d+'
last_time_online.hidden = True
last_time_online.doc = "UNIX TIME (1.1.1970 = never)"
self.DEFAULTS = c.to_configuration()
self.conn = BoundConnector(DBNAME, connector).connection()
def __init__(self, name, dbdef):
assert name and dbdef
self.name = name
self.desc = dbdef
self.db = BoundConnector(self.name)
with self:
self._init_meta()
class UserDB:
def __init__(self, connector=None):
database.require(DBNAME, version="1")
self.conn = BoundConnector(DBNAME, connector).connection()
def addUser(self, username, password, admin):
if not (username.strip() or password.strip()):
log.d("empty username or password!")
return
user = User.create(username, password, admin)
self.conn.execute(
"""
INSERT INTO users
(username, admin, password, salt)
VALUES (?,?,?,?)""",
(user.name, 1 if user.isadmin else 0, user.password, user.salt),
)
self.conn.commit()
msg = "added user: "******"not a valid password"
else:
newuser = User.create(username, newpassword, False) # dummy user for salt
self.conn.execute(
"""
UPDATE users SET password = ?, salt = ? WHERE username = ?
""",
(newuser.password, newuser.salt, newuser.name),
)
return "success"
def deleteUser(self, userid):
if self.isDeletable(userid):
self.conn.execute("""DELETE FROM users WHERE rowid = ?""", (userid,))
self.conn.commit()
return True
return False
def auth(self, username, password):
"""try to authenticate the given username and password. on success,
a valid user tuple will be returned; failure will return User.nobody().
will fail if username or password are empty."""
if not (username.strip() and password.strip()):
return User.nobody()
rows = self.conn.execute(
"SELECT rowid, username, admin, password, salt" " FROM users WHERE username = ?", (username,)
).fetchall()
assert len(rows) <= 1
if rows:
user = User(*rows[0])
if Crypto.scramble(password, user.salt) == user.password:
return user
return User.nobody()
def getUserList(self):
cur = self.conn.cursor()
cur.execute("""SELECT rowid, username, admin FROM users""")
ret = []
for uid, user, admin in cur.fetchall():
ret.append({"id": uid, "username": user, "admin": admin, "deletable": self.isDeletable(uid)})
return ret
def getUserCount(self):
cur = self.conn.cursor()
cur.execute("""SELECT COUNT(*) FROM users""")
return cur.fetchall()[0][0]
def getNameById(self, userid):
res = self.conn.execute("""SELECT username FROM users WHERE rowid = ?""", (userid,))
username = res.fetchone()
return username[0] if username else "nobody"
class Updater(object):
'''Handle the versioning needs of a single database.
name : str
The name of the database to manage.
dbdef : dict
The corresponding definition.
connector : :class:`cherrymusicserver.database.connect.AbstractConnector`
To connect to the database.
'''
_metatable = {
'create.sql': """CREATE TABLE IF NOT EXISTS _meta_version(
version TEXT,
_created INTEGER NOT NULL DEFAULT (datetime('now'))
);""",
'drop.sql': """DROP TABLE IF EXISTS _meta_version;"""
}
_classlock = threading.RLock()
_dblockers = {}
def __init__(self, name, dbdef):
assert name and dbdef
self.name = name
self.desc = dbdef
self.db = BoundConnector(self.name)
with self:
self._init_meta()
def __del__(self):
self._unlock()
def __repr__(self):
return 'updater({0!r}, {1} -> {2})'.format(
self.name,
self._version,
self._target,
)
def __enter__(self):
self._lock()
return self
def __exit__(self, exctype, exception, traceback):
self._unlock()
@property
def _islocked(self):
name, lockers = self.name, self._dblockers
with self._classlock:
return name in lockers and lockers[name] is self
def _lock(self):
name, lockers = self.name, self._dblockers
with self._classlock:
assert lockers.get(
name, self) is self, (name + ': is locked by another updater')
lockers[name] = self
def _unlock(self):
with self._classlock:
if self._islocked:
del self._dblockers[self.name]
@property
def needed(self):
""" ``True`` if the database is unversioned or if its version is less
then the maximum defined.
"""
self._validate_locked()
version, target = self._version, self._target
log.d('%s update check: version=[%s] target=[%s]', self.name, version,
target)
return version is None or version < target
@property
def requires_consent(self):
"""`True` if any missing updates require user consent."""
self._validate_locked()
for version in self._updates_due:
if 'prompt' in self.desc[version]:
return True
return False
@property
def prompts(self):
""" Return an iterable of string prompts for updates that require user
consent.
"""
self._validate_locked()
for version in self._updates_due:
if 'prompt' in self.desc[version]:
yield self.desc[version]['prompt']
def run(self):
"""Update database schema to the highest possible version."""
self._validate_locked()
log.i('%r: updating database schema', self.name)
log.d('from version %r to %r', self._version, self._target)
if None is self._version:
self._init_with_version(self._target)
else:
for version in self._updates_due:
self._update_to_version(version)
def reset(self):
"""Delete all content from the database along with supporting structures."""
self._validate_locked()
version = self._version
log.i('%s: resetting database', self.name)
log.d('version: %s', version)
if None is version:
log.d('nothing to reset.')
return
with self.db.connection() as cxn:
cxn.executescript(self.desc[version]['drop.sql'])
cxn.executescript(self._metatable['drop.sql'])
cxn.executescript(self._metatable['create.sql'])
self._setversion(None, cxn)
cxn.close()
def _validate_locked(self):
assert self._islocked, 'must be called in updater context (use "with")'
@property
def _version(self):
try:
return self.__version
except AttributeError:
maxv = self.db.execute(
'SELECT MAX(version) FROM _meta_version').fetchone()
maxv = maxv and maxv[0]
self.__version = maxv if maxv is None else str(maxv)
return self.__version
def _setversion(self, value, conn=None):
del self.__version
conn = conn or self.db.connection
log.d('{0}: set version to {1}'.format(self.name, value))
conn.execute('INSERT INTO _meta_version(version) VALUES (?)',
(value, ))
@property
def _target(self):
return max(self.desc)
@property
def _updates_due(self):
if None is self._version:
return ()
versions = sorted(self.desc)
start = versions.index(self._version) + 1
return versions[start:]
def _init_meta(self):
content = self.db.execute(
'SELECT type, name FROM sqlite_master;').fetchall()
content = [(t, n) for t, n in content
if n != '_meta_version' and not n.startswith('sqlite')]
with self.db.connection() as cxn:
cxn.isolation_level = "EXCLUSIVE"
cxn.executescript(self._metatable['create.sql'])
if content and self._version is None:
log.d('%s: unversioned content found: %r', self.name, content)
self._setversion(0, cxn)
cxn.isolation_level = ''
cxn.close()
def _init_with_version(self, vnum):
log.d('initializing database %r to version %s', self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
self._runscript(vnum, 'create.sql', cxn)
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ''
cxn.close()
def _update_to_version(self, vnum):
log.d('updating database %r to version %d', self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
self._runscript(vnum, 'update.sql', cxn)
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ''
cxn.close()
def _run_afterscript_if_exists(self, vnum, conn):
try:
self._runscript(vnum, 'after.sql', conn)
except KeyError:
pass
def _runscript(self, version, name, cxn):
try:
cxn.executescript(self.desc[version][name])
except sqlite3.OperationalError:
# update scripts are tested, so the problem's seems to be sqlite
# itself
log.x(_('Exception while updating database schema.'))
log.e(
_('Database error. This is probably due to your version of'
' sqlite being too old. Try updating sqlite3 and'
' updating python. If the problem persists, you will need'
' to delete the database at ' + self.db.dblocation))
import sys
sys.exit(1)
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
class PlaylistDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def deletePlaylist(self, plid, userid, override_owner=False):
cursor = self.conn.cursor()
ownerid = cursor.execute(
"SELECT userid FROM playlists WHERE rowid = ?", (plid,)).fetchone()
if not ownerid:
return "This playlist doesn't exist! Nothing deleted!"
if userid != ownerid[0] and not override_owner:
return "This playlist belongs to another user! Nothing deleted."
cursor.execute("""DELETE FROM playlists WHERE rowid = ?""", (plid,))
self.conn.commit()
return 'success'
def savePlaylist(self, userid, public, playlist, playlisttitle, overwrite=False):
if not len(playlist):
return 'I will not create an empty playlist. sorry.'
duplicateplaylistid = self.conn.execute("""SELECT rowid FROM playlists
WHERE userid = ? AND title = ?""",(userid,playlisttitle)).fetchone()
if duplicateplaylistid and overwrite:
self.deletePlaylist(duplicateplaylistid[0], userid)
duplicateplaylistid = False
if not duplicateplaylistid:
cursor = self.conn.cursor()
cursor.execute("""INSERT INTO playlists
(title, userid, public) VALUES (?,?,?)""",
(playlisttitle, userid, 1 if public else 0))
playlistid = cursor.lastrowid;
#put tracknumber to each track
numberedplaylist = []
for entry in zip(range(len(playlist)), playlist):
track = entry[0]
song = entry[1]
numberedplaylist.append((playlistid, track, song['url'], song['title']))
cursor.executemany("""INSERT INTO tracks (playlistid, track, url, title)
VALUES (?,?,?,?)""", numberedplaylist)
self.conn.commit()
return "success"
else:
return "This playlist name already exists! Nothing saved."
def loadPlaylist(self, playlistid, userid):
cursor = self.conn.cursor()
cursor.execute("""SELECT rowid FROM playlists WHERE
rowid = ? AND (public = 1 OR userid = ?) LIMIT 0,1""",
(playlistid, userid));
result = cursor.fetchone()
if result:
cursor.execute("""SELECT title, url FROM tracks WHERE
playlistid = ? ORDER BY track ASC""", (playlistid,))
alltracks = cursor.fetchall()
apiplaylist = []
for track in alltracks:
#TODO ugly hack: playlistdb saves the "serve" dir as well...
trackurl = unquote(track[1])
if trackurl.startswith('/serve/'):
trackurl = trackurl[7:]
elif trackurl.startswith('serve/'):
trackurl = trackurl[6:]
apiplaylist.append(MusicEntry(path=trackurl, repr=unquote(track[0])))
return apiplaylist
def getName(self, plid, userid ):
cur = self.conn.cursor()
cur.execute("""SELECT rowid as id,title FROM playlists WHERE
(public = 1 OR userid = ?) and rowid=?""", (userid,plid));
result = cur.fetchall()
if result:
print(result)
return result[0][1]
return 'playlist'
def setPublic(self, userid, plid, value):
ispublic = 1 if value else 0
cur = self.conn.cursor()
cur.execute("""UPDATE playlists SET public = ? WHERE rowid = ? AND userid = ?""", (ispublic, plid, userid))
def showPlaylists(self, userid):
cur = self.conn.cursor()
#change rowid to id to match api
cur.execute("""SELECT rowid as id,title, userid, public FROM playlists WHERE
public = 1 OR userid = ?""", (userid,));
res = cur.fetchall()
return list(map(lambda x: {'plid':x[0], 'title':x[1], 'userid':x[2],'public':bool(x[3]), 'owner':bool(userid==x[2])}, res))
def createPLS(self,userid,plid, addrstr):
pl = self.loadPlaylist(userid, plid)
if pl:
plsstr = '''[playlist]
NumberOfEntries={}
'''.format(len(pl))
for i,track in enumerate(pl):
trinfo = { 'idx':i+1,
'url':addrstr+'/serve/'+track.path,
'name':track.repr,
'length':-1,
}
plsstr += '''
File{idx}={url}
Title{idx}={name}
Length{idx}={length}
'''.format(**trinfo)
return plsstr
def createM3U(self,userid,plid,addrstr):
pl = self.loadPlaylist(userid, plid)
if pl:
trackpaths = map(lambda x: addrstr+'/serve/'+x.path,pl)
return '\n'.join(trackpaths)
class PlaylistDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def deletePlaylist(self, plid, userid, override_owner=False):
cursor = self.conn.cursor()
ownerid = cursor.execute(
"SELECT userid FROM playlists WHERE rowid = ?", (plid,)).fetchone()
if not ownerid:
return _("This playlist doesn't exist! Nothing deleted!")
if userid != ownerid[0] and not override_owner:
return _("This playlist belongs to another user! Nothing deleted.")
cursor.execute("""DELETE FROM playlists WHERE rowid = ?""", (plid,))
self.conn.commit()
return 'success'
def savePlaylist(self, userid, public, playlist, playlisttitle, overwrite=False):
if not len(playlist):
return _('I will not create an empty playlist. sorry.')
duplicateplaylistid = self.conn.execute("""SELECT rowid FROM playlists
WHERE userid = ? AND title = ?""",(userid,playlisttitle)).fetchone()
if duplicateplaylistid and overwrite:
self.deletePlaylist(duplicateplaylistid[0], userid)
duplicateplaylistid = False
if not duplicateplaylistid:
cursor = self.conn.cursor()
cursor.execute("""INSERT INTO playlists
(title, userid, public) VALUES (?,?,?)""",
(playlisttitle, userid, 1 if public else 0))
playlistid = cursor.lastrowid;
#put tracknumber to each track
numberedplaylist = []
for track, song in enumerate(playlist):
numberedplaylist.append((playlistid, track, song['url'], song['title']))
cursor.executemany("""INSERT INTO tracks (playlistid, track, url, title)
VALUES (?,?,?,?)""", numberedplaylist)
self.conn.commit()
return "success"
else:
return _("This playlist name already exists! Nothing saved.")
def loadPlaylist(self, playlistid, userid):
cursor = self.conn.cursor()
cursor.execute("""SELECT rowid FROM playlists WHERE
rowid = ? AND (public = 1 OR userid = ?) LIMIT 0,1""",
(playlistid, userid));
result = cursor.fetchone()
if result:
cursor.execute("""SELECT title, url FROM tracks WHERE
playlistid = ? ORDER BY track ASC""", (playlistid,))
alltracks = cursor.fetchall()
apiplaylist = []
for track in alltracks:
#TODO ugly hack: playlistdb saves the "serve" dir as well...
trackurl = unquote(track[1])
if trackurl.startswith('/serve/'):
trackurl = trackurl[7:]
elif trackurl.startswith('serve/'):
trackurl = trackurl[6:]
apiplaylist.append(MusicEntry(path=trackurl, repr=unquote(track[0])))
return apiplaylist
def getName(self, plid, userid ):
cur = self.conn.cursor()
cur.execute("""SELECT rowid as id,title FROM playlists WHERE
(public = 1 OR userid = ?) and rowid=?""", (userid,plid));
result = cur.fetchall()
if result:
return result[0][1]
return 'playlist'
def setPublic(self, userid, plid, public):
ispublic = 1 if public else 0
cur = self.conn.cursor()
cur.execute("""UPDATE playlists SET public = ? WHERE rowid = ? AND userid = ?""", (ispublic, plid, userid))
self.conn.commit()
def _searchPlaylist(self, searchterm):
q = '''SELECT DISTINCT playlists.rowid FROM playlists, tracks
WHERE ( tracks.playlistid = playlists.rowid
AND tracks.title LIKE ? )
OR
playlists.title LIKE ?'''
cur = self.conn.cursor()
res = cur.execute(q, ('%'+searchterm+'%', '%'+searchterm+'%'))
return [row[0] for row in res.fetchall()]
def showPlaylists(self, userid, filterby='', include_public=True):
filtered = None
if filterby != '':
filtered = self._searchPlaylist(filterby)
cur = self.conn.cursor()
select = "SELECT rowid, title, userid, public, _created FROM playlists"
if include_public:
where = """ WHERE public=:public OR userid=:userid"""
else:
where = """ WHERE userid=:userid"""
cur.execute(select + where, {'public': True, 'userid': userid});
results = cur.fetchall()
playlists = []
for result in results:
if not filtered is None and result[0] not in filtered:
continue
playlists.append({'plid': result[0],
'title': result[1],
'userid': result[2],
'public': bool(result[3]),
'owner': bool(userid==result[2]),
'created': result[4]
})
return playlists
def createPLS(self,userid,plid, addrstr):
pl = self.loadPlaylist(userid=userid, playlistid=plid)
if pl:
plsstr = '''[playlist]
NumberOfEntries={}
'''.format(len(pl))
for i,track in enumerate(pl):
trinfo = { 'idx':i+1,
'url':addrstr+'/serve/'+track.path,
'name':track.repr,
'length':-1,
}
plsstr += '''
File{idx}={url}
Title{idx}={name}
Length{idx}={length}
'''.format(**trinfo)
return plsstr
def createM3U(self,userid,plid,addrstr):
pl = self.loadPlaylist(userid=userid, playlistid=plid)
if pl:
trackpaths = map(lambda x: addrstr+'/serve/'+x.path,pl)
return '\n'.join(trackpaths)
class Updater(object):
'''Handle the versioning needs of a single database.
name : str
The name of the database to manage.
dbdef : dict
The corresponding definition.
connector : :class:`cherrymusicserver.database.connect.AbstractConnector`
To connect to the database.
'''
_metatable = {
'create.sql': """CREATE TABLE IF NOT EXISTS _meta_version(
version TEXT,
_created INTEGER NOT NULL DEFAULT (datetime('now'))
);""",
'drop.sql': """DROP TABLE IF EXISTS _meta_version;"""
}
_classlock = threading.RLock()
_dblockers = {}
def __init__(self, name, dbdef):
assert name and dbdef
self.name = name
self.desc = dbdef
self.db = BoundConnector(self.name)
with self:
self._init_meta()
def __del__(self):
self._unlock()
def __repr__(self):
return 'updater({0!r}, {1} -> {2})'.format(
self.name,
self._version,
self._target,
)
def __enter__(self):
self._lock()
return self
def __exit__(self, exctype, exception, traceback):
self._unlock()
@property
def _islocked(self):
name, lockers = self.name, self._dblockers
with self._classlock:
return name in lockers and lockers[name] is self
def _lock(self):
name, lockers = self.name, self._dblockers
with self._classlock:
assert lockers.get(name, self) is self, (
name + ': is locked by another updater')
lockers[name] = self
def _unlock(self):
with self._classlock:
if self._islocked:
del self._dblockers[self.name]
@property
def needed(self):
""" ``True`` if the database is unversioned or if its version is less
then the maximum defined.
"""
self._validate_locked()
version, target = self._version, self._target
log.d('%s update check: version=[%s] target=[%s]',
self.name, version, target)
return version is None or version < target
@property
def requires_consent(self):
"""`True` if any missing updates require user consent."""
self._validate_locked()
for version in self._updates_due:
if 'prompt' in self.desc[version]:
return True
return False
@property
def prompts(self):
""" Return an iterable of string prompts for updates that require user
consent.
"""
self._validate_locked()
for version in self._updates_due:
if 'prompt' in self.desc[version]:
yield self.desc[version]['prompt']
def run(self):
"""Update database schema to the highest possible version."""
self._validate_locked()
log.i('%r: updating database schema', self.name)
log.d('from version %r to %r', self._version, self._target)
if None is self._version:
self._init_with_version(self._target)
else:
for version in self._updates_due:
self._update_to_version(version)
def reset(self):
"""Delete all content from the database along with supporting structures."""
self._validate_locked()
version = self._version
log.i('%s: resetting database', self.name)
log.d('version: %s', version)
if None is version:
log.d('nothing to reset.')
return
with self.db.connection() as cxn:
cxn.executescript(self.desc[version]['drop.sql'])
cxn.executescript(self._metatable['drop.sql'])
cxn.executescript(self._metatable['create.sql'])
self._setversion(None, cxn)
cxn.close()
def _validate_locked(self):
assert self._islocked, 'must be called in updater context (use "with")'
@property
def _version(self):
try:
return self.__version
except AttributeError:
maxv = self.db.execute('SELECT MAX(version) FROM _meta_version').fetchone()
maxv = maxv and maxv[0]
self.__version = maxv if maxv is None else str(maxv)
return self.__version
def _setversion(self, value, conn=None):
del self.__version
conn = conn or self.db.connection
log.d('{0}: set version to {1}'.format(self.name, value))
conn.execute('INSERT INTO _meta_version(version) VALUES (?)', (value,))
@property
def _target(self):
return max(self.desc)
@property
def _updates_due(self):
if None is self._version:
return ()
versions = sorted(self.desc)
start = versions.index(self._version) + 1
return versions[start:]
def _init_meta(self):
content = self.db.execute('SELECT type, name FROM sqlite_master;').fetchall()
content = [(t, n) for t, n in content if n != '_meta_version' and not n.startswith('sqlite')]
with self.db.connection() as cxn:
cxn.isolation_level = "EXCLUSIVE"
cxn.executescript(self._metatable['create.sql'])
if content and self._version is None:
log.d('%s: unversioned content found: %r', self.name, content)
self._setversion(0, cxn)
cxn.isolation_level = ''
cxn.close()
def _init_with_version(self, vnum):
log.d('initializing database %r to version %s', self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
self._runscript(vnum, 'create.sql', cxn)
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ''
cxn.close()
def _update_to_version(self, vnum):
log.d('updating database %r to version %d', self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
self._runscript(vnum, 'update.sql', cxn)
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ''
cxn.close()
def _run_afterscript_if_exists(self, vnum, conn):
try:
self._runscript(vnum, 'after.sql', conn)
except KeyError:
pass
def _runscript(self, version, name, cxn):
try:
cxn.executescript(self.desc[version][name])
except sqlite3.OperationalError:
# update scripts are tested, so the problem's seems to be sqlite
# itself
log.x(_('Exception while updating database schema.'))
log.e(_('Database error. This is probably due to your version of'
' sqlite being too old. Try updating sqlite3 and'
' updating python. If the problem persists, you will need'
' to delete the database at ' + self.db.dblocation))
import sys
sys.exit(1)
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
class PlaylistDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def deletePlaylist(self, plid, userid, override_owner=False):
cursor = self.conn.cursor()
ownerid = cursor.execute(
"SELECT userid FROM playlists WHERE rowid = ?",
(plid, )).fetchone()
if not ownerid:
return _("This playlist doesn't exist! Nothing deleted!")
if userid != ownerid[0] and not override_owner:
return _("This playlist belongs to another user! Nothing deleted.")
cursor.execute("""DELETE FROM playlists WHERE rowid = ?""", (plid, ))
self.conn.commit()
return 'success'
def savePlaylist(self,
userid,
public,
playlist,
playlisttitle,
overwrite=False):
if not len(playlist):
return _('I will not create an empty playlist. sorry.')
duplicate_playlist = self.conn.execute(
"""SELECT rowid, public FROM playlists WHERE userid = ? AND title = ?""",
(userid, playlisttitle)).fetchone()
if duplicate_playlist:
if overwrite:
old_playlist_id, old_public_state = duplicate_playlist
# saving an existing playlist should keep the same public state:
public = old_public_state
self.deletePlaylist(old_playlist_id, userid)
duplicate_playlist = False
else:
return _("This playlist name already exists! Nothing saved.")
cursor = self.conn.cursor()
cursor.execute(
"""INSERT INTO playlists
(title, userid, public) VALUES (?,?,?)""",
(playlisttitle, userid, 1 if public else 0))
playlistid = cursor.lastrowid
#put tracknumber to each track
numberedplaylist = []
for track, song in enumerate(playlist):
numberedplaylist.append(
(playlistid, track, song['url'], song['title']))
cursor.executemany(
"""INSERT INTO tracks (playlistid, track, url, title)
VALUES (?,?,?,?)""", numberedplaylist)
self.conn.commit()
return "success"
def loadPlaylist(self, playlistid, userid):
cursor = self.conn.cursor()
cursor.execute(
"""SELECT rowid FROM playlists WHERE
rowid = ? AND (public = 1 OR userid = ?) LIMIT 0,1""",
(playlistid, userid))
result = cursor.fetchone()
if result:
cursor.execute(
"""SELECT title, url FROM tracks WHERE
playlistid = ? ORDER BY track ASC""", (playlistid, ))
alltracks = cursor.fetchall()
apiplaylist = []
for track in alltracks:
#TODO ugly hack: playlistdb saves the "serve" dir as well...
trackurl = unquote(track[1])
if trackurl.startswith('/serve/'):
trackurl = trackurl[7:]
elif trackurl.startswith('serve/'):
trackurl = trackurl[6:]
apiplaylist.append(
MusicEntry(path=trackurl, repr=unquote(track[0])))
return apiplaylist
def getName(self, plid, userid):
cur = self.conn.cursor()
cur.execute(
"""SELECT rowid as id,title FROM playlists WHERE
(public = 1 OR userid = ?) and rowid=?""", (userid, plid))
result = cur.fetchall()
if result:
return result[0][1]
return 'playlist'
def setPublic(self, userid, plid, public):
ispublic = 1 if public else 0
cur = self.conn.cursor()
cur.execute(
"""UPDATE playlists SET public = ? WHERE rowid = ? AND userid = ?""",
(ispublic, plid, userid))
self.conn.commit()
def _searchPlaylist(self, searchterm):
q = '''SELECT DISTINCT playlists.rowid FROM playlists, tracks
WHERE ( tracks.playlistid = playlists.rowid
AND tracks.title LIKE ? )
OR
playlists.title LIKE ?'''
cur = self.conn.cursor()
res = cur.execute(q, ('%' + searchterm + '%', '%' + searchterm + '%'))
return [row[0] for row in res.fetchall()]
def showPlaylists(self, userid, filterby='', include_public=True):
filtered = None
if filterby != '':
filtered = self._searchPlaylist(filterby)
cur = self.conn.cursor()
select = "SELECT rowid, title, userid, public, _created FROM playlists"
if include_public:
where = """ WHERE public=:public OR userid=:userid"""
else:
where = """ WHERE userid=:userid"""
cur.execute(select + where, {
'public': True,
'userid': userid
})
results = cur.fetchall()
playlists = []
for result in results:
if not filtered is None and result[0] not in filtered:
continue
playlists.append({
'plid': result[0],
'title': result[1],
'userid': result[2],
'public': bool(result[3]),
'owner': bool(userid == result[2]),
'created': result[4]
})
return playlists
def createPLS(self, userid, plid, addrstr):
pl = self.loadPlaylist(userid=userid, playlistid=plid)
if pl:
plsstr = '''[playlist]
NumberOfEntries={}
'''.format(len(pl))
for i, track in enumerate(pl):
trinfo = {
'idx': i + 1,
'url': addrstr + '/serve/' + track.path,
'name': track.repr,
'length': -1,
}
plsstr += '''
File{idx}={url}
Title{idx}={name}
Length{idx}={length}
'''.format(**trinfo)
return plsstr
def createM3U(self, userid, plid, addrstr):
pl = self.loadPlaylist(userid=userid, playlistid=plid)
if pl:
trackpaths = map(lambda x: addrstr + '/serve/' + x.path, pl)
return '\n'.join(trackpaths)
class UserDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def addUser(self, username, password, admin):
if not (username.strip() or password.strip()):
log.d(_('empty username or password!'))
return False
user = User.create(username, password, admin)
try:
exists = self.conn.execute(
'SELECT username'
' FROM users WHERE lower(username) = lower(?)',
(username, )).fetchone()
if (not exists):
self.conn.execute(
'''
INSERT INTO users
(username, admin, password, salt)
VALUES (?,?,?,?)''', (user.name, 1 if user.isadmin else 0,
user.password, user.salt))
else:
raise sqlite3.IntegrityError
except sqlite3.IntegrityError:
log.e('cannot create user "%s", already exists!' % user.name)
return False
self.conn.commit()
log.i('added user: '******'cannot change password: "******" does not exist!' % username
log.e(msg)
return msg
newuser = User.create(username, newpassword,
False) #dummy user for salt
self.conn.execute(
'''
UPDATE users SET password = ?, salt = ? WHERE lower(username) = lower(?)
''', (newuser.password, newuser.salt, newuser.name))
self.conn.commit()
return "success"
def deleteUser(self, userid):
if self.isDeletable(userid):
self.conn.execute('''DELETE FROM users WHERE rowid = ?''',
(userid, ))
self.conn.commit()
return True
return False
def auth(self, username, password):
'''try to authenticate the given username and password. on success,
a valid user tuple will be returned; failure will return User.nobody().
will fail if username or password are empty.'''
if not (username.strip() and password.strip()):
return User.nobody()
rows = self.conn.execute('SELECT rowid, username, admin, password, salt'
' FROM users WHERE lower(username) = lower(?)', (username,))\
.fetchall()
assert len(rows) <= 1
if rows:
user = User(*rows[0])
if Crypto.scramble(password, user.salt) == user.password:
return user
return User.nobody()
def getUserList(self):
cur = self.conn.cursor()
cur.execute('''SELECT rowid, username, admin FROM users''')
ret = []
for uid, user, admin in cur.fetchall():
ret.append({
'id': uid,
'username': user,
'admin': admin,
'deletable': self.isDeletable(uid)
})
return ret
def getUserCount(self):
cur = self.conn.cursor()
cur.execute('''SELECT COUNT(*) FROM users''')
return cur.fetchall()[0][0]
def getNameById(self, userid):
res = self.conn.execute(
'''SELECT username FROM users WHERE rowid = ?''', (userid, ))
username = res.fetchone()
return username[0] if username else 'nobody'
def getIdByName(self, username):
res = self.conn.execute(
'''SELECT rowid FROM users WHERE lower(username) = lower(?)''',
(username, ))
userid = res.fetchone()
if userid:
return userid[0]
class UserDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def addUser(self, username, password, admin):
if not (username.strip() or password.strip()):
log.d(_('empty username or password!'))
return False
user = User.create(username, password, admin)
try:
self.conn.execute('''
INSERT INTO users
(username, admin, password, salt)
VALUES (?,?,?,?)''',
(user.name, 1 if user.isadmin else 0, user.password, user.salt))
except sqlite3.IntegrityError:
log.e('cannot create user "%s", already exists!' % user.name)
return False
self.conn.commit()
log.i('added user: '******'cannot change password: "******" does not exist!' % username
log.e(msg)
return msg
newuser = User.create(username, newpassword, False) #dummy user for salt
self.conn.execute('''
UPDATE users SET password = ?, salt = ? WHERE username = ?
''', (newuser.password, newuser.salt, newuser.name) )
self.conn.commit()
return "success"
def deleteUser(self, userid):
if self.isDeletable(userid):
self.conn.execute('''DELETE FROM users WHERE rowid = ?''', (userid,))
self.conn.commit()
return True
return False
def auth(self, username, password):
'''try to authenticate the given username and password. on success,
a valid user tuple will be returned; failure will return User.nobody().
will fail if username or password are empty.'''
if not (username.strip() and password.strip()):
return User.nobody()
rows = self.conn.execute('SELECT rowid, username, admin, password, salt'
' FROM users WHERE username = ?', (username,))\
.fetchall()
assert len(rows) <= 1
if rows:
user = User(*rows[0])
if Crypto.scramble(password, user.salt) == user.password:
return user
return User.nobody()
def getUserList(self):
cur = self.conn.cursor()
cur.execute('''SELECT rowid, username, admin FROM users''')
ret = []
for uid, user, admin in cur.fetchall():
ret.append({'id':uid, 'username':user, 'admin':admin,'deletable':self.isDeletable(uid)})
return ret
def getUserCount(self):
cur = self.conn.cursor()
cur.execute('''SELECT COUNT(*) FROM users''')
return cur.fetchall()[0][0]
def getNameById(self, userid):
res = self.conn.execute('''SELECT username FROM users WHERE rowid = ?''',(userid,))
username = res.fetchone()
return username[0] if username else 'nobody'
def getIdByName(self, username):
res = self.conn.execute('''SELECT rowid FROM users WHERE username = ?''',(username,))
userid = res.fetchone()
if userid:
return userid[0]
class Updater(object):
"""Handle the versioning needs of a single database.
name : str
The name of the database to manage.
dbdef : dict
The corresponding definition.
connector : :class:`cherrymusicserver.database.connect.AbstractConnector`
To connect to the database.
"""
_metatable = {
"create.sql": """CREATE TABLE IF NOT EXISTS _meta_version(
version TEXT,
_created INTEGER NOT NULL DEFAULT (datetime('now'))
);""",
"drop.sql": """DROP TABLE IF EXISTS _meta_version;""",
}
_classlock = threading.RLock()
_dblockers = {}
def __init__(self, name, dbdef):
assert name and dbdef
self.name = name
self.desc = dbdef
self.db = BoundConnector(self.name)
with self:
self._init_meta()
def __del__(self):
self._unlock()
def __repr__(self):
return "updater({0!r}, {1} -> {2})".format(self.name, self._version, self._target)
def __enter__(self):
self._lock()
return self
def __exit__(self, exctype, exception, traceback):
self._unlock()
@property
def _islocked(self):
name, lockers = self.name, self._dblockers
with self._classlock:
return name in lockers and lockers[name] is self
def _lock(self):
name, lockers = self.name, self._dblockers
with self._classlock:
assert lockers.get(name, self) is self, name + ": is locked by another updater"
lockers[name] = self
def _unlock(self):
with self._classlock:
if self._islocked:
del self._dblockers[self.name]
@property
def needed(self):
""" ``True`` if the database is unversioned or if its version is less
then the maximum defined.
"""
self._validate_locked()
version, target = self._version, self._target
log.d("%s update check: version=[%s] target=[%s]", self.name, version, target)
return version is None or version < target
@property
def requires_consent(self):
"""`True` if any missing updates require user consent."""
self._validate_locked()
for version in self._updates_due:
if "prompt" in self.desc[version]:
return True
return False
@property
def prompts(self):
""" Return an iterable of string prompts for updates that require user
consent.
"""
self._validate_locked()
for version in self._updates_due:
if "prompt" in self.desc[version]:
yield self.desc[version]["prompt"]
def run(self):
"""Update database schema to the highest possible version."""
self._validate_locked()
log.i("%r: updating database schema", self.name)
log.d("from version %r to %r", self._version, self._target)
if None is self._version:
self._init_with_version(self._target)
else:
for version in self._updates_due:
self._update_to_version(version)
def reset(self):
"""Delete all content from the database along with supporting structures."""
self._validate_locked()
version = self._version
log.i("%s: resetting database", self.name)
log.d("version: %s", version)
if None is version:
log.d("nothing to reset.")
return
with self.db.connection() as cxn:
cxn.executescript(self.desc[version]["drop.sql"])
cxn.executescript(self._metatable["drop.sql"])
cxn.executescript(self._metatable["create.sql"])
self._setversion(None, cxn)
cxn.close()
def _validate_locked(self):
assert self._islocked, 'must be called in updater context (use "with")'
@property
def _version(self):
try:
return self.__version
except AttributeError:
maxv = self.db.execute("SELECT MAX(version) FROM _meta_version").fetchone()
maxv = maxv and maxv[0]
self.__version = maxv if maxv is None else str(maxv)
return self.__version
def _setversion(self, value, conn=None):
del self.__version
conn = conn or self.db.connection
log.d("{0}: set version to {1}".format(self.name, value))
conn.execute("INSERT INTO _meta_version(version) VALUES (?)", (value,))
@property
def _target(self):
return max(self.desc)
@property
def _updates_due(self):
if None is self._version:
return ()
versions = sorted(self.desc)
start = versions.index(self._version) + 1
return versions[start:]
def _init_meta(self):
content = self.db.execute("SELECT type, name FROM sqlite_master;").fetchall()
content = [(t, n) for t, n in content if n != "_meta_version" and not n.startswith("sqlite")]
with self.db.connection() as cxn:
cxn.isolation_level = "EXCLUSIVE"
cxn.executescript(self._metatable["create.sql"])
if content and self._version is None:
log.d("%s: unversioned content found: %r", self.name, content)
self._setversion(0, cxn)
cxn.isolation_level = ""
cxn.close()
def _init_with_version(self, vnum):
log.d("initializing database %r to version %s", self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
cxn.executescript(self.desc[vnum]["create.sql"])
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ""
cxn.close()
def _update_to_version(self, vnum):
log.d("updating database %r to version %d", self.name, vnum)
cxn = self.db.connection()
cxn.isolation_level = None # autocommit
cxn.executescript(self.desc[vnum]["update.sql"])
self._run_afterscript_if_exists(vnum, cxn)
self._setversion(vnum, cxn)
cxn.isolation_level = ""
cxn.close()
def _run_afterscript_if_exists(self, vnum, conn):
try:
conn.executescript(self.desc[vnum]["after.sql"])
except KeyError:
pass
class PlaylistDB:
def __init__(self, connector=None):
database.require(DBNAME, version='1')
self.conn = BoundConnector(DBNAME, connector).connection()
def deletePlaylist(self, plid, userid, override_owner=False):
cursor = self.conn.cursor()
ownerid = cursor.execute(
"SELECT userid FROM playlists WHERE rowid = ?",
(plid, )).fetchone()
if not ownerid:
return "This playlist doesn't exist! Nothing deleted!"
if userid != ownerid[0] and not override_owner:
return "This playlist belongs to another user! Nothing deleted."
cursor.execute("""DELETE FROM playlists WHERE rowid = ?""", (plid, ))
self.conn.commit()
return 'success'
def savePlaylist(self,
userid,
public,
playlist,
playlisttitle,
overwrite=False):
if not len(playlist):
return 'I will not create an empty playlist. sorry.'
duplicateplaylistid = self.conn.execute(
"""SELECT rowid FROM playlists
WHERE userid = ? AND title = ?""",
(userid, playlisttitle)).fetchone()
if duplicateplaylistid and overwrite:
self.deletePlaylist(duplicateplaylistid[0], userid)
duplicateplaylistid = False
if not duplicateplaylistid:
cursor = self.conn.cursor()
cursor.execute(
"""INSERT INTO playlists
(title, userid, public) VALUES (?,?,?)""",
(playlisttitle, userid, 1 if public else 0))
playlistid = cursor.lastrowid
#put tracknumber to each track
numberedplaylist = []
for entry in zip(range(len(playlist)), playlist):
track = entry[0]
song = entry[1]
numberedplaylist.append(
(playlistid, track, song['url'], song['title']))
cursor.executemany(
"""INSERT INTO tracks (playlistid, track, url, title)
VALUES (?,?,?,?)""", numberedplaylist)
self.conn.commit()
return "success"
else:
return "This playlist name already exists! Nothing saved."
def loadPlaylist(self, playlistid, userid):
cursor = self.conn.cursor()
cursor.execute(
"""SELECT rowid FROM playlists WHERE
rowid = ? AND (public = 1 OR userid = ?) LIMIT 0,1""",
(playlistid, userid))
result = cursor.fetchone()
if result:
cursor.execute(
"""SELECT title, url FROM tracks WHERE
playlistid = ? ORDER BY track ASC""", (playlistid, ))
alltracks = cursor.fetchall()
apiplaylist = []
for track in alltracks:
#TODO ugly hack: playlistdb saves the "serve" dir as well...
trackurl = unquote(track[1])
if trackurl.startswith('/serve/'):
trackurl = trackurl[7:]
elif trackurl.startswith('serve/'):
trackurl = trackurl[6:]
apiplaylist.append(
MusicEntry(path=trackurl, repr=unquote(track[0])))
return apiplaylist
def getName(self, plid, userid):
cur = self.conn.cursor()
cur.execute(
"""SELECT rowid as id,title FROM playlists WHERE
(public = 1 OR userid = ?) and rowid=?""", (userid, plid))
result = cur.fetchall()
if result:
print(result)
return result[0][1]
return 'playlist'
def setPublic(self, userid, plid, value):
ispublic = 1 if value else 0
cur = self.conn.cursor()
cur.execute(
"""UPDATE playlists SET public = ? WHERE rowid = ? AND userid = ?""",
(ispublic, plid, userid))
def showPlaylists(self, userid):
cur = self.conn.cursor()
#change rowid to id to match api
cur.execute(
"""SELECT rowid as id,title, userid, public FROM playlists WHERE
public = 1 OR userid = ?""", (userid, ))
res = cur.fetchall()
return list(
map(
lambda x: {
'plid': x[0],
'title': x[1],
'userid': x[2],
'public': bool(x[3]),
'owner': bool(userid == x[2])
}, res))
def createPLS(self, userid, plid, addrstr):
pl = self.loadPlaylist(userid, plid)
if pl:
plsstr = '''[playlist]
NumberOfEntries={}
'''.format(len(pl))
for i, track in enumerate(pl):
trinfo = {
'idx': i + 1,
'url': addrstr + '/serve/' + track.path,
'name': track.repr,
'length': -1,
}
plsstr += '''
File{idx}={url}
Title{idx}={name}
Length{idx}={length}
'''.format(**trinfo)
return plsstr
def createM3U(self, userid, plid, addrstr):
pl = self.loadPlaylist(userid, plid)
if pl:
trackpaths = map(lambda x: addrstr + '/serve/' + x.path, pl)
return '\n'.join(trackpaths)
