def get(self, id): db = Database() db.commit_statement("""select * from playlists where id = ?""", [int(id)]) row = db.fetchone() if row == None: raise ValueError("Could not get playlist with id=" + str(id)) return Playlist(id, row["name"], db)
def get_by_name(self, name): db = Database() db.commit_statement('''select * from playlists where name = ?''', [name]) row = db.fetchone() if row == None: raise DogError, 'Could not get playlist with id=' + id return Playlist(row['id'], row['name'], db)
def get_by_name(self, name): db = Database() db.commit_statement("""select * from playlists where name = ?""", [name]) row = db.fetchone() if row == None: raise ValueError("Could not get playlist with id=" + str(id)) return Playlist(row["id"], row["name"], db)
def get(self, id): db = Database() db.commit_statement('''select * from sources where id = ?''', [int(id)]) row = db.fetchone() if row == None: raise ValueError('Could not get source with id=' + str(id)) return Source(id, row['user'], row['password'], row['type'], db)
def get(self, id): db = Database() db.commit_statement('''select * from playlists where id = ?''', [int(id)]) row = db.fetchone() if row == None: raise DogError, 'Could not get playlist with id=' + id return Playlist(id, row['name'], db)
def name_exists(self, name): db = Database() db.commit_statement('''select * from playlists where name = ?''', [name]) row = db.fetchone() if row == None: return False else: return True
def get_all(self): db = Database() db.commit_statement('''select * from sources''') row = db.fetchone() sources = [] while row != None: sources.append(Source(row['id'], row['user'], row['password'], row['type'], db)) row = db.fetchone() return sources
def get_all(self): db = Database() db.commit_statement('''select * from playlists''') row = db.fetchone() playlists = [] while row != None: playlists.append(Playlist(row['id'], row['name'], db)) row = db.fetchone() return playlists
def get_all(self): db = Database() db.commit_statement("""select * from playlists""") row = db.fetchone() playlists = [] while row != None: playlists.append(Playlist(row["id"], row["name"], db)) row = db.fetchone() return playlists
def create_track_from_uri(self, uri): db = Database() # TODO: better way than searching through ALL tracks? db.commit_statement('''select * from tracks where uri = ?''', [uri]) row = db.fetchone() if row != None: del row['id'] # the id isn't part of a Track object return Track(**row) return None
def rename(self, playlist_id, name): self.tick_version() db = Database() db.commit_statement('''select * from playlists where id = ?''', [int(playlist_id)]) row = db.fetchone() if row == None: raise ValueError('Could not get playlist with id=' + playlist_id) db.add_statement('''update playlists set name = ? where id = ?''', [name, int(playlist_id)]) db.commit()
def find_by_username(self, username): db = Database() db.commit_statement('''select * from users where username = ?''', [username]) u = db.fetchone() if u == None: return None user = User(username, u['avatar_url']) user.id = u['id'] user.votes = u['votes'] return user
def find_by_username(self, username): db = Database() db.commit_statement("""select * from users where username = ?""", [username]) u = db.fetchone() if u == None: return None user = User(username, u["avatar_url"]) user.id = u["id"] user.votes = u["votes"] return user
def remove(self, id): self.tick_version() db = Database() db.commit_statement('''select * from playlists where id = ?''', [int(id)]) row = db.fetchone() if row == None: raise ValueError('Could not get playlist with id=' + id) db.add_statement('''delete from playlist_tracks where playlist_id = ?''', [int(id)]) db.add_statement('''delete from playlists where id = ?''', [int(id)]) db.commit()
def store(self): db = Database() # TODO: don't search on uri, do a comparison on more metadata db.commit_statement('''select * from tracks where uri = ?''', [self.uri]) row = db.fetchone() if row == None: db.commit_statement('''insert into tracks (title, artist, album, uri, duration) values (?, ?, ?, ?, ?)''', (self.title, self.artist, self.album, self.uri, self.duration)) return db.inserted_id() else: return row['id']
def search(self, query): db = Database() ret = [] query = "%" + query + "%" # TODO: do a join between tracks and collection here. Not search on uri db.commit_statement('''select * from tracks where (title LIKE ? or artist LIKE ? or album LIKE ?) and uri LIKE ?''', (query, query, query, 'file://%')) row = db.fetchone() while row != None: del row['id'] # the id isn't part of a Track object ret.append(row) # TODO: return a Track here instead row = db.fetchone() return ret
def length(self): db = Database() # FIXME this is insane we need to do a real sql count here i = 0 db.commit_statement('''select * from sources''') row = db.fetchone() while row != None: i = i + 1 row = db.fetchone() return i
def get_all_voting_users(self, track_id): db = Database() ret = [] db.commit_statement('''select * from votes join users on votes.user_id = users.id where track_id = ?''', [track_id]) row = db.fetchone() while row != None: ret.append({"username":row['username'], "avatar_url":row['avatar_url']}) row = db.fetchone() return ret
def find(self, id): db = Database() db.commit_statement('''select * from playlist_tracks where id = ?''', [int(id)]) row = db.fetchone() if row == None: raise ValueError('Could not get track with id=' + str(id)) track_id = row['track_id'] votes = row['votes'] # TODO: this could be a part of the previous statement db.commit_statement('''select * from tracks where id = ?''', [track_id]) row = db.fetchone() return Track(row['uri'], row['title'], row['artist'], row['album'], row['duration'], row['album_uri'], votes)
def list(self, type): db = Database() ret = [] if type.lower() == "artist": db.commit_statement('''SELECT DISTINCT artist FROM tracks WHERE artist > '' ORDER BY artist ASC''') elif type.lower() == "album": db.commit_statement('''SELECT DISTINCT album,artist FROM tracks WHERE album > '' ORDER BY artist ASC''') else: return [] row = db.fetchone() while row != None: #del row['id'] # the id isn't part of a Track object ret.append(row) # TODO: return a Track here instead row = db.fetchone() return ret
def get_voted_tracks(self): db = Database() db.commit_statement('''select votes.track_id from votes join users on votes.user_id = users.id where users.id = ?''', [str(self.id)]) row = db.fetchone() track_ids = [] while row != None: track_ids.append(row['track_id']) row = db.fetchone() tracks = [] for track_id in track_ids: db.commit_statement('''select * from tracks where tracks.id = ?''', [track_id]) row = db.fetchone() del row['id'] tracks.append(row) return tracks
def remove_vote(self, track, username, avatar_url): self.tick_version() user = User(username, avatar_url) user_id = user.store() track_id = track.store() if not track.has_vote_from(user_id): logging.debug("%s has no vote for track, ignoring" % user.username) return if self.has_track(self.id, track_id): #DOWNVOTE TRACK logging.debug("Downvote track_id = %s" % track_id) playlist_id = self.id db = Database() db.commit_statement('''select * from playlist_tracks where playlist_id = ? AND track_id = ? LIMIT 1''', [playlist_id, track_id]) row = db.fetchone() pos = row['position'] votes = row['votes'] id = row['id'] votes = votes - 1 if votes == 0: logging.debug("Track has no more votes remove track from playlist") db.commit_statement('''select * from playlist_tracks where track_id = ? and playlist_id = ?''', [track_id, self.id]) row = db.fetchone() self.remove_playlist_tracks_id(row['id']) return # find all with same amount of votes, and move pass them db.commit_statement('''select max(position) as new_pos,* from playlist_tracks where playlist_id = ? AND votes > ? AND position > 1''', [playlist_id, votes]) # update votes row = db.fetchone() if row == None: logging.debug("no need to move, no one to pass with %s votes" % votes) else: #we have some tracks to jump over new_pos = row['new_pos'] if new_pos <= 1: logging.debug("cap movement to pos=2 (let playing song be first)") new_pos=2 logging.debug("update pos, move from %s to %s" % (str(pos), str(new_pos))) self.move_track(id, new_pos) #update the votes on the track db.commit_statement('''update playlist_tracks set votes = votes - 1 where playlist_id = ? and track_id = ?''', [self.id, track_id]) user.votedown(track_id) else: logging.debug("Down voting track does not exists in database")
def voteup(self, track_id): if self.votes_left() < 1: logging.warning("No votes left for %s" % self.username) return db = Database() db.commit_statement("""select * from tracks where id=?""", [track_id]) row = db.fetchone() now = time.time() self.all_votes.append( { "id": track_id, "title": row["title"], "artist": row["artist"], "album": row["album"], "user": self.username, "avatar_url": self.avatar_url, "time": now, "votes": 1, "duration": row["duration"], "votes": 3, "uri": row["uri"], } ) db.commit_statement("""insert into votes (track_id, user_id) values (?, ?)""", [track_id, self.id]) # take vote from user db.commit_statement("""update users set votes = votes - 1 where id = ?""", [self.id]) logging.debug("Update number of votes and track votes for user = %s" % self.username)
def store(self): db = Database() db.commit_statement('''select * from users where username = ?''', [self.username]) u = db.fetchone() if u == None: db.commit_statement('''insert into users (username, avatar_url, votes) VALUES(?, ?, ?)''', [self.username, self.avatar_url, self.votes]) db.commit_statement('''select * from users where username = ?''', [self.username]) u = db.fetchone() logging.debug("Added user=%s" % self.username) self.id = u['id'] return u['id']
def add_vote(self, track, username, avatar_url): self.tick_version() user = User(username, avatar_url) user_id = user.store() track_id = track.store() if user.votes_left() < 1: logging.debug("No more votes left for %s" % user.username) return if track.has_vote_from(user_id): logging.debug("%s already voted for track, ignoring" % user.username) return if self.has_track(self.id, track_id): #UPVOTE TRACK logging.debug("Upvote track_id = %s" % track_id) playlist_id = self.id db = Database() db.commit_statement('''select * from playlist_tracks where playlist_id = ? AND track_id = ? LIMIT 1''', [playlist_id, track_id]) row = db.fetchone() pos = row['position'] votes = row['votes'] id = row['id'] # we dont want to move pass the playing track if pos <= 2: logging.debug("no need to move, at position = %s" % pos) else: # find all with same amount of votes, and move pass them db.commit_statement('''select min(position) as new_pos,* from playlist_tracks where playlist_id = ? AND votes <= ? AND position > 1''', [playlist_id, votes]) # update votes row = db.fetchone() if row == None: logging.debug("no need to move, no one to pass with %s votes" % votes) else: #we have some tracks to jump over new_pos = row['new_pos'] if new_pos <= 1: logging.debug("cap movement to pos=2 (let playing song be first)") new_pos=2 logging.debug("update pos, move from %s to %s" % (str(pos), str(new_pos))) self.move_track(id, new_pos) #update the votes on the track db.commit_statement('''update playlist_tracks set votes = votes + 1 where playlist_id = ? and track_id = ?''', [self.id, track_id]) else: #ADD TRACK logging.debug("add track with track_id = %s" % track_id) self.add_track(track, 1) #update user votes user.voteup(track_id)
def remove_all_voting_users(self, track_id): db = Database() db.commit_statement('''select * from votes where track_id = ?''', [track_id]) logging.debug("Give votes back to all users who voted for removed track %s" % track_id) user_ids = [] row = db.fetchone() while row != None: user_ids.append(row['user_id']) row = db.fetchone() for id in user_ids: logging.debug("%s voted for track %s now got one vote back" % (id, track_id)) # give vote back to user db.commit_statement('''update users set votes = votes + 1 where id = ?''', [id]) row = db.fetchone() db.commit_statement('''delete from votes where track_id = ?''', [track_id])
def API_cleanDatabase(self, request): db = Database() db.commit_statement('''delete from votes''') db.commit_statement('''delete from collection''') db.commit_statement('''delete from users''') db.commit_statement('''delete from tracks''') db.commit_statement('''delete from playlists where id != 1''') db.commit_statement('''delete from entries''') request.finish()
def create(self, name): db = Database() db.commit_statement('''insert into playlists (name) values (?)''', [name]) print "Adding playlist '" + name + "'" return Playlist(db.inserted_id(), name, db)
def add_track(self, track): db = Database() track_id = track.store() db.commit_statement('''select * from collection where track_id = ?''', [int(track_id)]) if db.fetchone() == None: db.commit_statement('''insert into collection (track_id) values (?)''', [track_id])
def has_vote_from(self, user_id): db = Database() db.commit_statement('''select * from votes where track_id = ? AND user_id = ?''', [self.id, user_id]) row = db.fetchone() return row != None