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
