(2016-04-18, 23:11)black_eagle Wrote: OK, so I have a very alpha version of an addon to do this. There is very little error checking done currently, but it does clean the files table effectively. I need to figure out how to get the current SQLite database version number as currently you have to type in the name, so if anyone knows of a way please let me know. I'm aware that by connecting to the database directly that the addon will never make it into the official repository but I don't currently see an alternative method in Kodi to achieve this.
I need to add some settings and more error checking and then I can make it available for some brave.....or foolhardy!!.... person to test it.
BTW, ERD is 'Entity-Relationship Diagram'
I'm writing this on Linux, but obviously it'll need testing on Windows. @BatterPudding, I'm looking at you here....cough
Don't worry, it will get a good kicking from me on my stand-alone KODI install on Win10 with its SQLite database. I'll also sling it at a few other KODI installs I have done for friends and clients on a mixture of devices and uses. So I have a good handful of victims, sorry, I mean test subjects to try this out on.
As to EnTity Relationships Diagrams... that just sounds like someone's pr0n habits.
Or is it that slightly annoying side of any job where complex words are made up to explain a handful of lines scribbled on a pad of paper showing something obvious... I expect it will be as useful as that wiki page where it is just a list of field names written out in a slightly more verbose form without any attempt to actually explain anything
I've also been trying to make sense of some of the other oddities in my messy database.
That bookmarks table is odd... I was going to suggest optionally keeping that out of any scan to remove files. Based on the theory that anyone who had added a bookmark must have done so for a reason. Then I realised I had 20 bookmarks, some of them in those one-off files and streams I want to remove... yet I don't remember adding them... I doubt KODI has a GUI to list these old bookmarks so these go into an "optional junk" list.
streamdetails is weirdly bonkers. This is not "streams" as they are usually talked about on here. This is anything being played back. When I watched an episode of a show from my library last night, details of the played video file have now been stored in here. I am guessing this then gets used for the little HD and 1080p logos?
settings - yeah... well... not really dug through this fully yet. Just doesn't make sense to me to store default values for every film in here. This table needs to be cleaned out in our sweep too. Personally I'd zap it completely, other people may need to keep some of the settings. Looking at my table 99% of the rows are identical rows of default values. Those are the rows prime for deletion. Only keeping rows where a value has changed.
Slightly puzzlingly every one of my videos has ResumeTime set to zero... I had assumed this was going to keep note of how far I had got through the video when watching it... but clearly not. Resume Times seem to actually stored in the movieview \ episodeview tables. Though I have so few on them in there I guess this is written out intelligently - only storing a resume time when not in the last 5% of a film as most people skip credits.
I can also see a few other "house keeping" items that my head is constructing SQL queries for...