Changeset 27

Timestamp:

07/09/07 23:49:08 (5 years ago)

Author:

dlefevre

Message:

Updates:
- fixed error in queries with special characters
- fixed SELECT song FROM genre
- fixed add album to playlist
- fixed add artist to playlist
- fixed add genre to playlist
- Added workaround for the missing INSERT IF NOT EXISTS (see mutex table)
- Added add genre method
- Added add artist method

Files:

• mymrc/trunk/mymrc/src/db/audioqueries.py (modified) (7 diffs)

mymrc/trunk/mymrc/src/db/audioqueries.py

@@ -2 +2 @@
 from settings.settings import Settings
 from db.dbdriver import DBDriverClass
+
+
 class AudioQueries(object):
     def __init__(self):
@@ -24 +26 @@
     
     def albums(self):
-        query = """SELECT title FROM audio_album;"""
+        query = """SELECT title FROM audio_album ORDER BY title;"""
         return self._iDB.execute(query)  
     
@@ -40 +42 @@
 WHERE audio_song.artist_id = audio_artist.id AND
       audio_song.album_id = audio_album.id AND
-      audio_song.album_id IN (SELECT id FROM audio_album WHERE title = '%s') AND
+      audio_song.album_id IN (SELECT id FROM audio_album WHERE title = ?) AND
       audio_song.genre_id = audio_genre.id
 ORDER BY audio_artist.name, audio_album.title, audio_song.title;"""
-        return self._iDB.execute(query%(aAlbumTitle))      
-    
-    def artists(self):
-        query = """SELECT name FROM audio_artist;"""
+        return self._iDB.execute(query, (aAlbumTitle,))      
+    
+    def artists(self, raw=False):
+        if raw:
+            query = """SELECT * FROM audio_artist ORDER BY name;"""
+        else:
+            query = """SELECT name FROM audio_artist ORDER BY name;"""
         return self._iDB.execute(query)   
     
@@ -61 +66 @@
 FROM audio_song, audio_artist, audio_album, audio_genre
 WHERE audio_song.artist_id = audio_artist.id AND
-      audio_song.artist_id = (SELECT id FROM audio_artist WHERE name = '%s') AND
+      audio_song.artist_id = (SELECT id FROM audio_artist WHERE name = ?) AND
       audio_song.album_id = audio_album.id AND
       audio_song.genre_id = audio_genre.id
 ORDER BY audio_album.title, audio_song.title;"""
-        return self._iDB.execute(query%(aArtistName))
+        return self._iDB.execute(query, (aArtistName,))
     
     
@@ -74 +79 @@
 WHERE audio_song.album_id = audio_album.id AND
       audio_song.artist_id = audio_artist.id AND
-      audio_song.artist_id = (SELECT id FROM audio_artist WHERE name = '%s')
+      audio_song.artist_id = (SELECT id FROM audio_artist WHERE name = ?)
 ORDER BY audio_album.title;"""
-        return self._iDB.execute(query%(aArtistName))   
+        return self._iDB.execute(query,(aArtistName,))   
     
     
@@ -92 +97 @@
     ## Get all songs from a genre
     def genreSongs(self, aGenre):
-        query = """SELECT audio_song.id AS id,
-       audio_song.path AS path,
-       audio_artist.name AS artist,
-       audio_song.title AS title,
-       audio_album.title AS album,
-       audio_song.track_no AS track,
-       audio_genre.name AS genre,
-       audio_song.time AS time
-FROM audio_song, audio_artist, audio_album, audio_genre
-WHERE audio_song.artist_id = audio_artist.id AND
+        query = """
+SELECT audio_song.id AS id,
+       audio_song.path AS path,
+       audio_artist.name AS artist,
+       audio_song.title AS title,
+       audio_album.title AS album,
+       audio_song.track_no AS track,
+       audio_genre.name AS genre,
+       audio_song.time AS time
+FROM audio_song, audio_artist, audio_album, audio_genre
+WHERE audio_song.genre_id = (SELECT audio_genre.id FROM audio_genre WHERE audio_genre.name = ?) AND
       audio_song.album_id = audio_album.id AND
       audio_song.genre_id = audio_genre.id AND
-      audio_song.artist_id = (SELECT id FROM audio_genre WHERE name = '%s')
-ORDER BY audio_album.title, audio_song.title;"""
-        return self._iDB.execute(query%(aGenre))
-    
-    
-
-    
-
-    
+      audio_artist.id = audio_song.artist_id 
+ORDER BY audio_album.title, audio_song.title;
+"""
+        return self._iDB.execute(query, (aGenre,))
+    
+    
+    ## Add artist if not exists.
+    #  @param self The object pointer. 
+    def addArtist(self, aArtist):
+        print "AddArtist: ", aArtist
+        query = """INSERT INTO audio_artist(name)
+SELECT ?
+FROM mutex
+    LEFT OUTER JOIN audio_artist
+        ON audio_artist.name = ?
+WHERE mutex.i = 1 AND audio_artist.name IS NULL;"""
+        return DBDriver().execute(query, (aArtist.title(), aArtist.title()))
+    
+    
+    ## Add album if not exists.
+    #  @param self The object pointer.
+    def addAlbum(self, aAlbum):
+        query = """INSERT INTO audio_album(title)
+SELECT ?
+FROM mutex
+    LEFT OUTER JOIN audio_album
+        ON audio_album.title = ?
+WHERE mutex.i = 1 AND audio_album.title IS NULL;"""
+        return DBDriver().execute(query, (aAlbum.title(), aAlbum.title()))
+   
     
     ## Get all available playlists.
@@ -139 +166 @@
       audio_song.genre_id = audio_genre.id AND
       audio_song.id = audio_playlist_data.song_id AND
-      audio_playlist_data.playlist_id = (SELECT id FROM audio_playlist WHERE name='%s')
-ORDER BY audio_artist.name, audio_album.title, audio_song.title;"""
-        return self._iDB.execute(query%(aPlaylist))
+      audio_playlist_data.playlist_id = (SELECT id FROM audio_playlist WHERE name=?)
+ORDER BY audio_playlist_data.id;"""
+        return self._iDB.execute(query, (aPlaylist,))
     
     def songById(self, aId):
-        query = """ SELECT path FROM audio_song WHERE id=%s""" %(aId)
-        return self._iDB.execute(query).fetchall()
-    
+        query = """ SELECT path FROM audio_song WHERE id=?"""
+        return self._iDB.execute(query, (aId))
+    
+    def songByTitle(self, aTitle):
+        query = """ SELECT * FROM audio_song WHERE title=?"""
+        return self._iDB.execute(query,(aTitle,))
     
     def addSongToPlaylist(self, aPlaylistId, aSongId):
         query = """ INSERT INTO audio_playlist_data(playlist_id, song_id) 
-values('%s', '%s')"""
-        return self._iDB.execute(query%(aPlaylistId, aSongId))
+values(?, ?)"""
+        return self._iDB.execute(query, (aPlaylistId, aSongId))
+    
+    
+    ## Insert a whole artist into a playlist.
+    def addArtistToPlaylist(self, aArtist, aPlaylist):
+        query = """INSERT INTO audio_playlist_data(playlist_id, song_id)
+    SELECT audio_playlist.id, audio_song.id
+    FROM audio_playlist, audio_song
+    WHERE audio_song.artist_id IN (SELECT audio_artist.id FROM audio_artist WHERE 
+    audio_artist.name = ?) AND audio_playlist.name = ?;"""
+        return self._iDB.execute(query, (aArtist, aPlaylist))
+    
+    
+    ## Insert a whole album into a playlist.
+    def addAlbumToPlaylist(self, aAlbum, aPlaylist):
+        print "aAlbum: ", aAlbum
+        print "aPlaylist: ", aPlaylist
+        query = """INSERT INTO audio_playlist_data(playlist_id, song_id)
+    SELECT audio_playlist.id, audio_song.id
+    FROM audio_playlist, audio_song
+    WHERE audio_song.album_id IN (SELECT audio_album.id FROM audio_album WHERE 
+    audio_album.title = ?) AND audio_playlist.name = ?;"""
+        return self._iDB.execute(query, (aAlbum, aPlaylist))
+    
+    
+    ## Insert a whole genre into a playlist
+    def addGenreToPlaylist(self, aGenre, aPlaylist):
+        query = """INSERT INTO audio_playlist_data(playlist_id, song_id)
+    SELECT audio_playlist.id, audio_song.id
+    FROM audio_playlist, audio_song
+    WHERE audio_song.genre_id IN (SELECT audio_genre.id FROM audio_genre WHERE 
+    audio_genre.name = ?) AND audio_playlist.name = ?;"""
+        return self._iDB.execute(query,(aGenre, aPlaylist))
+    
+    
+    ## Delete a playlist
+    #  @note: since SQLite doesn't really deal with foreign keys, we can't just
+    #  use an ON DELETE CASCADE in the table creation, so we have to delete
+    #  all entries manually
+    def deletePlaylist(self, aPlaylist):
+        query = """DELETE FROM audio_playlist_data WHERE audio_playlist_id = ?;
+DELETE FROM audio_playlist WHERE id = ?""" 
+        return self._iDB.execute(query, (aPlaylist, aPlaylist))
+    
+    
+    ## Add song to database
+    def addSong(self, aPath, aTitle, aArtist, aAlbum, aGenre, aTrackNb=None, aTime=None):
+        query = """ INSERT INTO audio_artist(name) VALUES (?) WHERE NOT EXISTS((SELECT name FROM audio_artist WHERE name=?))"""
+        aArtist
+        aAlbum,
+        
+        query = 'INSERT INTO audio_song(path, title, artist_id, album_id, genre_id, '
+        if aTrackNb:
+            query += ', track_no'
+        if aTime:
+            query += ', time'
+        query += ')'
+        query += """VALUES( ?, ?, """ % (aPath, aTitle)
+        query += """(SELECT id FROM audio_artist WHERE name=?), """ % (aArtist)
+        query += """(SELECT id FROM audio_album WHERE title=?), """ % (aAlbum)
+        query += """(SELECT id FROM audio_genre WHERE name=?), """ % (aGenre)
+        if aTrackNb:
+            query += """'%d', """
+        if aTime:
+            query += """'%d')"""
+        return self._iDB.execute(query)