LinHES Forums • View topic - HOWTO: MythMusic

rickbassham · **Joined:** Sun Apr 16, 2006 10:30 am **Posts:** 13

I have a fairly large collection of MP3's, and wanted to be able to list them in MythMusic both by Artists and by Album. I couldn't figure out how to do this with the smart playlists, but, since I am fairly well versed in SQL, I decided to write a script that would create a playlist for each of my albums in MythMusic. This way, I can sort my collection by Artist, and then in playlists, have all my albums.

I'm using R5D1 but upgraded to the latest mythtv that debian-multimedia.org has, so I'm not sure if the db schema is the same for the stock R5D1, but it should be fine for R5E50 (I think).

Let me know if this works for you.

I'm not in front of my machine right now, so the steps may not be completely correct, if not, please correct me.

Anyways, basically you have to run the script on your mythtv database. Unfortunately, you will have to run this script every time you add a new album to your collection as well.

Anyways, here is the script:

Code:

CREATE TEMPORARY TABLE _temp
(
   playlist_id INT,
   playlist_name VARCHAR(255),
   playlist_songs TEXT,
   last_accessed TIMESTAMP,
   length INT,
   songcount SMALLINT,
   hostname varchar(255)
);

INSERT INTO _temp (playlist_id, playlist_name, playlist_songs, last_accessed, length, songcount, hostname)
   SELECT
      (SELECT playlist_id FROM music_playlists WHERE playlist_name = music_albums.album_name) AS playlist_id,
      music_albums.album_name AS playlist_name,
      GROUP_CONCAT(song_id) AS playlist_songs,
      CURDATE() AS last_accessed,
      SUM(music_songs.length),
      COUNT(music_songs.song_id) AS songcount,
      'mythtv-01' AS hostname

   FROM music_albums

   INNER JOIN music_songs
      ON music_albums.album_id = music_songs.album_id
   INNER JOIN music_artists
      ON music_songs.artist_id = music_artists.artist_id

   GROUP BY
     music_albums.album_name;

INSERT INTO music_playlists
   SELECT * FROM _temp ORDER BY playlist_name
   ON DUPLICATE KEY UPDATE playlist_id = VALUES(playlist_id);

DROP TEMPORARY TABLE _temp;

Save that script into a file called CreateAlbums.sql. NOTE: Be sure to change the hostname in the script above from 'mythtv-01' to whatever your mythtv box's hostname is.

Then run the script on the database:

Code:

mysql -u mythtv -p mythconverg < CreateAlbums.sql

Be sure to enter the password you set up for the mythtv user in the database when it prompts you. (Probably 'mythtv');

Then check out your Playlists in MythMusic. You should have a playlist set up for each of your albums.

When you add a new album to your collection, after you add it to the database (when it scans for music), you can run this to add it to the playlists. This will not remove any playlists you have created, nor will it remove the ones created with this script, but if you add a single song to an album, as long as the album has the same name, then it will add it to the playlist for that album. NOTE: Different Albums with the same name will probably show up under a single entry in the playlists. I'm not sure of a way around this.

I hope this fills a need out there. It sure did in my house.

HOWTO: MythMusic - Create playlists for each of your albums

Who is online