rbsteffes at gmail
Oct 28, 2004, 6:04 AM
Post #1 of 1
(1116 views)
Permalink
|
|
Re: [mythtv] [PATCH] Mythweb -- Mythmusic playlist functionality AND Request for SQL help
|
|
On Thu, 28 Oct 2004 08:02:39 -0400, Daniel C. Casimiro
<dcasimiro [at] cox> wrote:
> I can't look at my myth database right now because my myth box is down.
> (Blew out 4 capacitors)
>
> I assume that you are trying to sort against the playlist table entry
> that lists the song ids. I can't think of any automatic way to order
> on this string.
>
> However, you could re-arrange the tables slightly.
>
> First, make the playlist table simpler. The two important fields would
> be playlist id and name.
>
> Next, make a new table called "playlistitems" or something similar.
> This table would link the songs to their respective playlists. This new
> table would have at least 3 fields: Song ID, Playlist ID, Playlist
> Position(track).
>
> Now, by using SQL joins, you can grab a playlist's songs by supplying
> the playlist name, and order by the Playlist Position.
>
> For example, the MySQL query string to select all songs in playlist
> "PlaylistA" would be:
>
> string name = "PlaylistA";
> string req = "SELECT
> musicmetadata.artist,musicmetadata.title,musicmetadata.filename ";
> req += "FROM musicmetadata ";
> req += "LEFT JOIN musicplaylistitem ON ";
> req += "musicmetadata.intid=musicplaylistitem.songid ";
> req += "LEFT JOIN musicplaylist USING(playlistid) ";
> req += "WHERE musicplaylist.name='" + name + "'";
> req += "ORDER BY musicplaylistitem.track;";
>
> Or, at least something similar.
>
> Of course, the downside of this is that you need to recode some parts of
> mythmusic to use the new table schema.
> Just a suggestion...
>
> ~Dan
>
>
This is what I'm debating right now. Instead of dealing with the
issue directly, I just put in some sorting code for the moment. The
only thing I don't do right now is deal with a situation Thor brought
up that I didn't know about: Negative numbers allowing imbedded
playlists.
My initial reaction is the amount of processing time to sort the list
is probably negligible for the vast majority of cases. I thought of a
halfway decent way to sort and stuck that in, although it may need
some tweaking to allow imbedded lists.
http://www.afferentsys.com/~rbsteffes/patch-mythmusic.bz2
|
