How to remove dead podcasts (and add orphaned ones) in MM3

[Mr Spouse]

At last, thanks to great work by Bex and Jiri - Bex's SQL-Viewer 2.0 Script gives us the chance to properly edit MM3 database entries!

My biggest irritation are the podcast entries - I have dozens of mainly BBC podcasts that I have deleted from my PC but still show up in the MM Podcast Subscriptions node. Now we can amend the tables I've put together a few SQL queries that work with the Viewer script. (And it should be possible also now to also make these actions work in vbs...)

I have 8 queries so far. They query the PodcastEpisodes and Songs tables - PodcastEpisodes is the one that populates the Podcast Subscriptions node in MM3. Just paste the code into the Viewer window and press 'Run Select Query' for the SELECT queries or 'Execute DDL/DML Statement' for the others.

Here goes:

I strongly recommend you make a backup of your MM.db file before attempting changes!

1. All podcasts in PodcastEpisodes table

Code: Select all

SELECT * FROM PodcastEpisodes ORDER BY IDTrack

2. Empty podcasts - entry in PodcastEpisodes table does not have a corresponding entry in the Songs table

Code: Select all

SELECT * FROM PodcastEpisodes WHERE IDTrack IN (SELECT PodcastEpisodes.IDTrack AS 'tmp' FROM PodcastEpisodes LEFT JOIN Songs ON PodcastEpisodes.IDTrack = Songs.ID WHERE Songs.ID Is Null)

3. Delete empty podcasts from in PodcastEpisodes table

Code: Select all

DELETE FROM PodcastEpisodes WHERE IDTrack IN (SELECT PodcastEpisodes.IDTrack AS 'tmp' FROM PodcastEpisodes LEFT JOIN Songs ON PodcastEpisodes.IDTrack = Songs.ID WHERE Songs.ID Is Null)

Notes:
- This will remove ALL entries where the Songs entry is missing.
- If you have the file but it has become 'orphaned' see queries 7 & 8
- If a podcast is still active on the server it will be added back to the PodcastEpisodes table on the next update.
- If you want to prevent an active podcast from downloading change the Downloaded flag to 1 (IDTrack can be any value). See query 6.

4. Podcasts in PodcastEpisodes table awaiting download (i.e. will be picked up at next update)

Code: Select all

SELECT * FROM PodcastEpisodes WHERE IDTrack = -1 AND Downloaded = 0

Notes:
- If you want to prevent the podcast from downloading change the Downloaded flag to 1 (IDTrack can be any value).
- If the podcast is not active you can delete it using the next query:

5. Delete single podcast from table (e.g. after manually removing the file)

Code: Select all

DELETE FROM PodcastEpisodes WHERE ID = nnn

Notes:
- If the podcast is still active on the podcast server it will be added back to the PodcastEpisodes table on the next update.
nnn = PodcastEpisodes.ID (NOT Songs.ID)

6. Change Downloaded flag

Code: Select all

UPDATE PodcastEpisodes SET Downloaded = 0 WHERE ID = nnn

Notes:
- Downloaded = 0 will attempt to save the podcast at next update (if still active)
- Downloaded = 1 will ignore updates
nnn = PodcastEpisodes.ID (NOT Songs.ID)

7. Add orphaned (existing) podcast files to PodcastEpisodes table

Code: Select all

REPLACE INTO PodcastEpisodes
( ID, IDPodcast, IDTrack, Downloaded, title, autor, link, description, subtitle, summary, PubDate, NetSource, keywords, category )

SELECT  
PodcastEpisodes.ID, PodcastEpisodes.IDPodcast, Songs.ID, 1, PodcastEpisodes.title, PodcastEpisodes.autor, PodcastEpisodes.link, PodcastEpisodes.description, PodcastEpisodes.subtitle, PodcastEpisodes.summary, PodcastEpisodes.PubDate, PodcastEpisodes.NetSource, PodcastEpisodes.IDTrack, PodcastEpisodes.category 

FROM PodcastEpisodes INNER JOIN Songs ON (Songs.Artist = PodcastEpisodes.autor AND Songs.Songtitle = PodcastEpisodes.Title) 
WHERE 
PodcastEpisodes.IDTrack <> Songs.ID 

Notes:
- It is recommended to run this AFTER performing an Add/Rescan Tracks routine
- This query looks for matches in the Artist & Title fields of the podcast subscriptions and song library then updates the IDTrack number to the Songs ID number
- 'autor' is how the PodcastEpisodes field appears in the database!
- Downloaded flag is set to 1
- If it takes too long to scan your Songs table try changing the WHERE clause to

Code: Select all

WHERE 
PodcastEpisodes.IDTrack <> Songs.ID AND Songs.Genre = 'Podcast'

8. Show all Downloaded podcasts with Podcast ID where available

Code: Select all

SELECT  PodcastEpisodes.ID AS 'Podcast ID', PodcastEpisodes.IDTrack AS 'Podcast IDTrack', Songs.ID AS 'Song ID', Songs.IDAlbum, Songs.Artist, Songs.Album, Songs.SongTitle, Songs.SongPath, Songs.Year FROM Songs LEFT JOIN PodcastEpisodes ON Songs.ID = PodcastEpisodes.IDTrack WHERE Songs.Genre = 'Podcast'

Enjoy!

[Edit 25/1/08: Modified code for last 2 queries]

[MM3 monkey]

Can anyone help me get rid of these empty podcasts please

edit: they long gone

[nihir]

Okay, so I have been having this problem for a while and none of the queries provided did anything for me. Quite possibly cause I don't know what it does or how SQL works really.

But, because of this idea, I learned how a few basics and solved the problem of blank podcasts showing up.

Here's what I did. First I ran this SELECT Query. The number at the end is what comes up when you want to sort by DateAdded. I don't get it, but that's what shows up.

Code: Select all

SELECT * FROM Podcasts ORDER BY 39698.2476628472

If you want to do this without copying the code, here's what I did.

1. Click on SELECT
2. Click on *
3. Click on FROM
4. From the <Tables> Dropdown, select Podcasts and Click Add
5. Click ORDER BY
6. From the <Add data from Selected song> Dropdown, select DateAdded
7. Change Display Only 20 Rows to something large (I did 500 because I only had 300 blank Podcast fields)
7. Click Run Select Query

Now you should have all your podcasts showing up in the fields below. You'll notice the real podcasts will have ID numbers 1,2,3,4,etc. The blank podcasts will have higher podcast numbers.

So I thought why not delete all the podcasts with high ID numbers.

Here's the next (and last step)

Code: Select all

DELETE FROM Podcasts WHERE Podcasts.ID >99

That basically deletes all podcasts where the ID number is greater than 99. Pick whichever number is appropriate for you.


I know this can probably be done in 1 step, but this worked for me and I thought it could help someone else!

[MrQuan]

Mr Spouse,

I have my Podcasts set to delete episodes older than 1 week, and to retain the 5 most recent episodes. MM3 was leaving them listed in the library, greyed out like a broken reference. Now everything is cleaned up nicely!

Thanks very much for posting this.