[mythtv-users] [PATCH] Mythweb -- Mythmusic playlist functionality AND Request for SQL help

Christopher Flynn flynnguy at gmail.com
Thu Oct 28 13:36:23 UTC 2004


On Wed, 27 Oct 2004 16:39:18 -0400, thor <mythtv at lamedomainname.com> wrote:
>  You can have as many playlists as you want (ie. rows in the playlist table).
> The default_playlist_storage (or whatever it's called, doing this from
> memory) is just for the state of the interface the last time the user was
> selecting songs. Similarily the backup_playlist_storage allows one level of
> recall in the interface (you can swap a playlist to "current", edit it, save
> it back, and the state of the playlist before a stored one was made current
> will be restored).

Ok, I just remember briefly playing with mythmusic and I couldn't seem
to find a way to have more than one playlist. I'll have to play with
it some more.

>  One could conceivably design a better database schema to handle this. Over
> the past few years, many people have said something along the lines of, "a
> string of comma separated numbers seems like a silly way to do this, anyone
> mind if I come up with a better one?".  No one yet has.

I do think that a list of comma separated numbers in a database field
is a very silly thing to do. The best fix to this in my opinion is a
lookuptable that has the unique playlistid and the unique song id. You
could even carry over the - numbers for playlists convention. Setting
this up in the database is quite easy, the problem I would have would
be on the coding end because I haven't really done much C/C++ since
college. If I get a chance I will take a look at it.

As for the SQL, create a table similar to:
create table musicplaylistlkup 
(playlistid int(10) unsigned NOT NULL, 
songid int(10) NOT NULL, 
FOREIGN KEY(playlistid) REFERENCES musicplaylist(playlistid) ON DELETE CASCADE, 
FOREIGN KEY(songid) REFERENCES musicmetadata(intid) ON DELETE CASCADE);

Of course a little script to pull out the comma seperated values in
the existing playlist would be handy and then we could delete the
songlist field from the table musicplaylist.

select artist, album, title, filename from musicmetadata,
musicplaylistlkup where musicplaylistlkup.songid=musicmetadata.intid
and playlistid=<playlist number>;

would get you a list of songs with playlistid=4. Also the ON DELETE
CASCADE in the table creation should delete the song from the playlist
when you delete the song from the database. Unfortunately I'm testing
it and I can't seem to make the foreign constraints work. Probably
something you have to compile into mysql? I don't know, without the
foreign key stuff it should still work, it just won't have the built
in error checking/automatic deletion.  Perhaps someone with more mysql
experience could help figure out why the foreign constraints aren't
working.


-- 
You must be the change you wish to see in the world. - Gandhi


More information about the mythtv-users mailing list