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