Prune Database Size

[cjpcjpindre]

Hi,

Not sure if this should be classified as a bug or an enhancement, or neither as it only applies to me....

I have been using RadioDownloader for many years, and have ported the database across serveral PC's. I'm starting to find the UI is getting very sluggish and unreponsive, especially when trying to add a new Podcast.

My RD Database file is nearly 10Gb in size. Browsing it, (using DBBrowser for SQLite), I see data in there for everything I've ever downloaded, including for the long since removed Radio Download Plugin.

My Episodes table has more than 25,000 rows.

Following the advice in Issue #228 I have removed all the images. This will eventually decrease the headline database size. However, it does not affect the of rows.

Is there a safe way of removing information from the database, for both defunct plugins, and for removed subscriptions?

I am quite happy running this as SQL, rather than as an actual change to the program.

Many thanks, and keep up the good work. C.

[ribbons]

Glad to hear from a long-time user and crikey, yes - that is a very large database indeed! In fact a bit of a testament to SQLite that it handles it okay (ish).

I would expect that the database will become significantly smaller when it is compacted now the images aren't linked to any programmes or episodes. If you'd like to do this now instead of in up to three months you can run the following SQL against the database and restart Radio Downloader (might take quite a while to run the cleanup at startup):

delete from settings where property in ('LastPrune', 'LastVacuum');

It wouldn't surprise me if performance improves significantly at the same time due to all the programme and episode data not being spread over gigabytes of image data, but I'm guessing you might still want to clean some of the old data up..? Do you have many items in your 'Downloads' list?

[cjpcjpindre]

Worked like a dream. Database is now 1/2 Gb, and the UI is much more responsive.

At some point it might be nice to clear out the junk of the old subscriptions, but the immeadiate problem is solved.

(I don't keep anything in the downloads list, generally.)

[ribbons]

Great - glad to hear that has made a good improvement. I think running the following SQL and then restarting Radio Downloader should make it clear away the old BBC Radio plugin programme and episode entries. Probably worth backing up your database first just in case.

update episodes set autodownload=1 where autodownload=0 and progid in (select progid from programmes where pluginid='45ecd621-f8fa-4f2f-98b5-6a2d1221787a');
delete from settings where property in ('LastPrune', 'LastVacuum');

[cjpcjpindre]

Sorry for the slow response. Yes that has removed all the episodes for the old plugin.

There are still about 13,000 episode rows for podcasts I'm no longer subscribed to:

select * from episodes where progid not in (select progid from subscriptions);

Can I simply delete these?

[ribbons]

Okay, great :smile:. You can delete those episode entries if you'd prefer but if you re-subscribe in the future Radio Downloader will potentially re-download previously downloaded episodes.