[jmgibson1981]

I had an issue recently where I lost my tv shows. I solved this with help from the forums here by renaming my original source folder and rescanning. The problem is I just went through an sql dump file and it still shows references to the old folder. I've done a clean and so forth but I cannot figure why these references are still here, or how to remove them. I know barely enough to even get this working, much less clean it up. I'd like to remove these old useless references, no sense keepign them around if i can do it easily.

[Karellen]

I've done a clean and so forth but I cannot figure why these references are still here

That is normal. A Clean Library is as the name implies, it is not a Clean Database. Entries in the Path, Streamdetails, Files and Bookmarks tables still remain. These should not cause a problem.

If you still want to clean, then others will have to explain how to clean on Linux.

[jmgibson1981]

As long as they are no issue then its ok. I was doing some digging, i found references to stuff from a few years ago that I no longer have for one reason or another. Long as it's ok I have no worries. Thank you.

[Klojum]

i found references to stuff from a few years ago that I no longer have for one reason or another

Kodi is still a bit messy when it comes to cleaning up after itself. Not every linked database table is cleaned up when tv series or movies are removed, so you end up with plenty of ghost entries that won't do harm but they just take up a bit of disk space.

If you want to clean up your video database, then
- do a (full) export of the video database
- delete/rename the current database
- restart kodi for a new database
- rescrape your video collection

It'll only have your existing file entries in it. Also entries like Youtube videos will have been wiped away.

[teriyaki]

Kodi is still a bit messy when it comes to cleaning up after itself. Not every linked database table is cleaned up when tv series or movies are removed, so you end up with plenty of ghost entries that won't do harm but they just take up a bit of disk space.

I'd imagine over time a sizeable amount of ghost/dead entries would be present. While disk space may not be a concern, what about performance? The more bloat the database has, the slower searches and future cleanings will get right? If so, that's a bad thing, especially for those who use Kodi on minimal/slow hardware.

[Klojum]

Database engine are pretty nifty things, and searches with indexes can go quite speedy, also on the less powerful devices.
And although people won't notice the difference in the beginning, in the (very) long run, databases that become clogged up will become slower on 'simple' devices with small caches.

I'm doing some testing on my mysql database with PHP scripts, and it should be possible to root out 99.5% of data from a deleted movie or tv show.

[teriyaki]

I think users will appreciate anything that can keep their database nice & clean but why would anything need to be rooted out in the first place? Kodi is responsible for adding info to the database so wouldn't it simply be a matter of undo'ing what it adds? And for data added via plugins (like a scraper), it must add stuff either append existing data or create a new entry with some kind of cross-reference right? If so then wouldn't cleaning just consist of removing the original entry, and everything associated with its' cross-reference/identifier? Or, if Kodi doesn't want to be responsible for cleaning up entries from things like scrapers, maybe a requirement should be that any plugin that adds data to the database must include a function to remove it as well, if that plugin wants to be included in the official repo.

Just trying to figure out why this problem exists and possible solutions to remedy it.

[black_eagle]

Just trying to figure out why this problem exists and possible solutions to remedy it.

Perhaps this will go some way to explaining why.

[Preacher]

I think users will appreciate anything that can keep their database nice & clean

They probably appreciate anything that can keep Kodi working quickly. Run a "library cleanup" and see how long it takes (5 seconds?) - that's running through the entire list and making per-record comparisons. Detritus in there generally don't slow a great deal down - you're kinda talking about washing off all the accumulated mud on your car to improve fuel consumption rates.

Basically, users won't care about an unclean DB unless it's something that impacts performance or affects the accuracy of their library.

Kodi is responsible for adding info to the database so wouldn't it simply be a matter of undo'ing what it adds?

It's easier to detect new content added and insert a corresponding record than detect things deleted and remove the row. The safest method has already been mentioned: purge the entire DB of all entries then let the DB get recreated with new stuff (I've done this when a "clean library" didn't remove deleted entries).

And for data added via plugins (like a scraper), it must add stuff either append existing data or create a new entry with some kind of cross-reference right? If so then wouldn't cleaning just consist of removing the original entry, and everything associated with its' cross-reference/identifier?

Think about a scenario of how this works:

1. add a file, Kodi finds it
   
2. remove a file; Kodi has no record it's been removed and therefore doesn't know what the original entry is (unless Kodi is used to remove it and can trigger an event).
   

Just trying to figure out why this problem exists and possible solutions to remedy it.

I kinda think you're onto something here... Kodi could compare directory entries (what's found on disk versus what's in the DB) then identify the redundant entries and offer to scrub them - which I think how BatterPudding's plugin works.

[black_eagle]

Kodi could compare directory entries (what's found on disk versus what's in the DB) then identify the redundant entries and offer to scrub them

Unfortunately, it's not that simple because that doesn't take into account any removable storage that may be disconnected at the time or an offline NAS.  Kodi already checks for the existence of files when it does a clean library, but that can slow things down an awful lot because it'll check every single streamed video from Youtube, every trailer, indeed every link to every video you've ever watched through it.  And if those URL's still exist, which many of them will, then they don't get removed because the file is still valid and how does Kodi know you don't want that particular link any more ?

BP's addon works by removing the references to all the streaming stuff from the files table.   It also removes any files that aren't referenced through your sources.xml file so it'll remove, for example, anything you've watched via the file manager from a source that you haven't added to Kodi.  It then calls the built in clean library routine which identifies stuff in the other tables that isn't referenced in the files table and cleans them up.

If people remove sources correctly from inside Kodi, then it does a pretty good job of cleaning that up but if you just delete stuff on disk without doing that, then your db can end up in a mess eventually.

[Preacher]

that doesn't take into account any removable storage that may be disconnected at the time or an offline NAS.

Yeah, I fathomed that when re-reading BP's post. Doh!

I was thinking whether it's possible to do a sources detect first then build that as an exclusion list (NAS offline), but ultimately it comes down to leaving the records intact when people want them removed versus scrubbing them in the absence of remote paths (which aren't always easily detectable) and having the overhead of rebuilding all of the entries when the remote storage becomes available.

No matter which choice is picked, it won't suit everyone, so I feel it's wasted developer effort implementing a process that doesn't ahev a strnog justification behind it.

Kodi already checks for the existence of files when it does a clean library

The problem I encountered was the files (and directory) being removed but the series still presented itself as a thumbnail in the library which "clean library" didn't clear out.  I fixed it through manually scrubbing some entries (using sqllite3 - possibly broken referential integrity - not sure if there's any PK/FK constraints between series and episodes)

It wasn't a problem having a series show up with no episodes - those entries were cleaned as expected - just that I wanted those series themselves being hidden. (is there an option for that? I didn't look)

If people remove sources correctly from inside Kodi, then it does a pretty good job of cleaning that up but if you just delete stuff on disk without doing that, then your db can end up in a mess eventually.

Yeah, I've done the latter as it was quicker to scrub stuff through the command line than do it piecemeal through Kodi. If there's a way of bulk-scrubbing through Kodi, I'll take that.

Anyhow, this is largely academic. I think we agree a dirty DB doesn't indicate a dirty library; RDBMS are pretty efficient at manipulating records and there are ways and means of achieving the desired outcome without needing Kodi to explicitly have this feature.

All good!