Changeset 13

Show
Ignore:
Timestamp:
07/02/07 17:32:55 (5 years ago)
Author:
ciro
Message:

added sample queries for playlist management

Location:
mymrc/trunk/mymrc/temp
Files:
2 modified

Legend:

Unmodified
Added
Removed

  • mymrc/trunk/mymrc/temp/samples.sql

    r12 r13  
    173173 
    174174 
    175  
     175----- 
     176--  playlist handling 
     177----- 
     178 
     179--- 
     180-- insert a whole artist into a playlist 
     181--- 
     182INSERT INTO playlist_data(playlist_id, song_id) 
     183    SELECT playlist.id, song.id 
     184    FROM playlist, song 
     185    WHERE artist_id IN (SELECT id FROM artist WHERE name = '<artist name>') AND 
     186          playlist.name = '<playlist name>'; 
     187 
     188--- 
     189-- insert a whole album into a playlist 
     190--- 
     191INSERT INTO playlist_data(playlist_id, song_id) 
     192    SELECT playlist.id, song.id 
     193    FROM playlist, song 
     194    WHERE song.album_id IN (SELECT id FROM album WHERE title = '<album>') AND 
     195          playlist.name = '<playlist name>'; 
     196 
     197--- 
     198-- insert a whole genre into a playlist 
     199--- 
     200INSERT INTO playlist_data(playlist_id, song_id) 
     201    SELECT playlist.id, song.id 
     202    FROM playlist, song 
     203    WHERE song.genre_id IN (SELECT id FROM genre WHERE name = '<genre>') AND 
     204          playlist.name = '<playlist name>'; 
     205 
     206-- 
     207-- would be even easier if the id of the playlist and the artist/album/.. is 
     208-- already known or stored in the script 
     209-- 
     210 
     211 
     212--- 
     213-- delete a playlist 
     214-- note: since SQLite doesn't really deal with foreign keys, we can't just 
     215--       use an ON DELETE CASCADE in the table creation, so we have to delete 
     216--       all entries manually :< 
     217--- 
     218DELETE FROM playlist_data WHERE playlist_id = <playlist id to delete>; 
     219DELETE FROM playlist WHERE id = <playlist id to delete>; 
     220 
     221 
     222 

  • mymrc/trunk/mymrc/temp/samples.sql.html

    r12 r13  
    181181<br> 
    182182<br> 
     183<font color="#00ffff"><b>-----</b></font><br> 
     184<font color="#00ffff"><b>--&nbsp;&nbsp;playlist handling</b></font><br> 
     185<font color="#00ffff"><b>-----</b></font><br> 
     186<br> 
     187<font color="#00ffff"><b>---</b></font><br> 
     188<font color="#00ffff"><b>-- insert a whole artist into a playlist</b></font><br> 
     189<font color="#00ffff"><b>---</b></font><br> 
     190<font color="#ffff00"><b>INSERT</b></font>&nbsp;<font color="#ff6060"><b>INTO</b></font>&nbsp;playlist_data(playlist_id, song_id)<br> 
     191&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ffff00"><b>SELECT</b></font>&nbsp;playlist.id, song.id<br> 
     192&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>FROM</b></font>&nbsp;playlist, song<br> 
     193&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>WHERE</b></font>&nbsp;artist_id <font color="#ffff00"><b>IN</b></font>&nbsp;(<font color="#ffff00"><b>SELECT</b></font>&nbsp;id <font color="#ff6060"><b>FROM</b></font>&nbsp;artist <font color="#ff6060"><b>WHERE</b></font>&nbsp;name = <font color="#ff40ff"><b>'&lt;artist name&gt;'</b></font>) <font color="#ffff00"><b>AND</b></font><br> 
     194&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;playlist.name = <font color="#ff40ff"><b>'&lt;playlist name&gt;'</b></font>;<br> 
     195<br> 
     196<font color="#00ffff"><b>---</b></font><br> 
     197<font color="#00ffff"><b>-- insert a whole album into a playlist</b></font><br> 
     198<font color="#00ffff"><b>---</b></font><br> 
     199<font color="#ffff00"><b>INSERT</b></font>&nbsp;<font color="#ff6060"><b>INTO</b></font>&nbsp;playlist_data(playlist_id, song_id)<br> 
     200&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ffff00"><b>SELECT</b></font>&nbsp;playlist.id, song.id<br> 
     201&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>FROM</b></font>&nbsp;playlist, song<br> 
     202&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>WHERE</b></font>&nbsp;song.album_id <font color="#ffff00"><b>IN</b></font>&nbsp;(<font color="#ffff00"><b>SELECT</b></font>&nbsp;id <font color="#ff6060"><b>FROM</b></font>&nbsp;album <font color="#ff6060"><b>WHERE</b></font>&nbsp;title = <font color="#ff40ff"><b>'&lt;album&gt;'</b></font>) <font color="#ffff00"><b>AND</b></font><br> 
     203&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;playlist.name = <font color="#ff40ff"><b>'&lt;playlist name&gt;'</b></font>;<br> 
     204<br> 
     205<font color="#00ffff"><b>---</b></font><br> 
     206<font color="#00ffff"><b>-- insert a whole genre into a playlist</b></font><br> 
     207<font color="#00ffff"><b>---</b></font><br> 
     208<font color="#ffff00"><b>INSERT</b></font>&nbsp;<font color="#ff6060"><b>INTO</b></font>&nbsp;playlist_data(playlist_id, song_id)<br> 
     209&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ffff00"><b>SELECT</b></font>&nbsp;playlist.id, song.id<br> 
     210&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>FROM</b></font>&nbsp;playlist, song<br> 
     211&nbsp;&nbsp;&nbsp;&nbsp;<font color="#ff6060"><b>WHERE</b></font>&nbsp;song.genre_id <font color="#ffff00"><b>IN</b></font>&nbsp;(<font color="#ffff00"><b>SELECT</b></font>&nbsp;id <font color="#ff6060"><b>FROM</b></font>&nbsp;genre <font color="#ff6060"><b>WHERE</b></font>&nbsp;name = <font color="#ff40ff"><b>'&lt;genre&gt;'</b></font>) <font color="#ffff00"><b>AND</b></font><br> 
     212&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;playlist.name = <font color="#ff40ff"><b>'&lt;playlist name&gt;'</b></font>;<br> 
     213<br> 
     214<font color="#00ffff"><b>--</b></font><br> 
     215<font color="#00ffff"><b>-- would be even easier if the id of the playlist and the artist/album/.. is</b></font><br> 
     216<font color="#00ffff"><b>-- already known or stored in the script</b></font><br> 
     217<font color="#00ffff"><b>--</b></font><br> 
     218<br> 
     219<br> 
     220<font color="#00ffff"><b>---</b></font><br> 
     221<font color="#00ffff"><b>-- delete a playlist</b></font><br> 
     222<font color="#00ffff"><b>-- </b></font><span style="background-color: #ffff00"><font color="#808080">note</font></span><font color="#00ffff"><b>: since SQLite doesn't really deal with foreign keys, we can't just</b></font><br> 
     223<font color="#00ffff"><b>--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; use an ON DELETE CASCADE in the table creation, so we have to delete</b></font><br> 
     224<font color="#00ffff"><b>--&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; all entries manually :&lt;</b></font><br> 
     225<font color="#00ffff"><b>---</b></font><br> 
     226<font color="#ffff00"><b>DELETE</b></font>&nbsp;<font color="#ff6060"><b>FROM</b></font>&nbsp;playlist_data <font color="#ff6060"><b>WHERE</b></font>&nbsp;playlist_id = &lt;playlist id <font color="#ff6060"><b>to</b></font>&nbsp;<font color="#ffff00"><b>delete</b></font>&gt;;<br> 
     227<font color="#ffff00"><b>DELETE</b></font>&nbsp;<font color="#ff6060"><b>FROM</b></font>&nbsp;playlist <font color="#ff6060"><b>WHERE</b></font>&nbsp;id = &lt;playlist id <font color="#ff6060"><b>to</b></font>&nbsp;<font color="#ffff00"><b>delete</b></font>&gt;;<br> 
     228<br> 
     229<br> 
    183230<br> 
    184231</font></body>