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 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.') 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 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 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"