[Jogee]

There is a long running thread in the developers forum where they've been designing a new database structure. I haven't read the entire thread, but it appears to still be a work in progress.

http://forum.kodi.tv/showthread.php?tid=200911

[ReplayHarry]

+1 on this whole topic.

I too always thought that the "Clean Library" function would clean the database of anything in there referring to a source and/or file that is no longer present. I never watch live TV so record everything (via NextPVR) and then watch through Kodi when I am ready. As I noticed things slowing down a bit (my perception only), I started doing a Clean Library manually once in awhile. I finally went to writing a python script to initiate the Clean Library function and then have that setup on the Windows scheduler on my primary HTPC. So it does it every night at 12:30am. But when I do happen to look at the logs, I see old stuff like has been mentioned in this post.

Would sure be nice if someone found a solution for this. I think it would speed up everything in Kodi.

[BatterPudding]

I wonder if there is a *real* spec for the database somewhere? Or will we have to become initiated into the KODI Dev world to learn those kinds of secrets? http://kodi.wiki/view/Databases

Sorry no spec AFAIK, I guess no one ever had time or motivation to write one. That wiki is out of date, and a list of table names and fields is not a data design anyway. What the devs do, or at least what I did for music, was read the code and some historic PRs and look at the db files in a SQLite browser and figure it all out. Sorry I haven't done the same for video, so unable to comment.

Good luck!

I was kinda joking on the Database Spec. I can see everyone is just reverse engineering it and then guessing. You only have to look at that streamdetails table to see some serious randomness!

[BatterPudding]

There is a long running thread in the developers forum where they've been designing a new database structure. I haven't read the entire thread, but it appears to still be a work in progress.

http://forum.kodi.tv/showthread.php?tid=200911

Thanks. Should be useful as it will let us see if they have spotted any of the issues we are seeing. But my head can't deal with 20 pages of thread reading tonight

[BatterPudding]

+1 on this whole topic.

I too always thought that the "Clean Library" function would clean the database of anything in there referring to a source and/or file that is no longer present. I never watch live TV so record everything (via NextPVR) and then watch through Kodi when I am ready. As I noticed things slowing down a bit (my perception only), I started doing a Clean Library manually once in awhile. I finally went to writing a python script to initiate the Clean Library function and then have that setup on the Windows scheduler on my primary HTPC. So it does it every night at 12:30am. But when I do happen to look at the logs, I see old stuff like has been mentioned in this post.

Would sure be nice if someone found a solution for this. I think it would speed up everything in Kodi.

The speed difference is noticeable. When some of this baggy crud is removed my "Clean Library" calls are almost comically smooth again. So there must be other KODI functions having similar troubles. Anything that reads that whole files table assuming these are good files.

I am guessing the "Clean Library" routine is a little old now. So when all these streams started to appear no one thought to update "Clean Library" because streams aren't in the library...

And then someone started to store the settings for every video ever played... we were then well on the way to this mess.

Wish I had a proper dev environment on this PC to let me load the whole KODI project up. Maybe another day...


Meanwhile, we'll have something a little more publicly usable soon. Anyone happy to hack their own SQL databases can run the slightly brutal delete commands. Though we are currently lost somewhere in the mists of Python jokes talking with The Knights who say "Ni" and expanding this into something a little more friendly. More idiot proofed, cleans up some of the cross references,

[DaveBlake]

There is a long running thread in the developers forum where they've been designing a new database structure. I haven't read the entire thread, but it appears to still be a work in progress.

http://forum.kodi.tv/showthread.php?tid=200911

Thanks. Should be useful as it will let us see if they have spotted any of the issues we are seeing. But my head can't deal with 20 pages of thread reading tonight

Good luck with that too
I gave up. The end result seems to be yet another list of table and field names, oh and an ERD (which actually tells you less than people think it does) but you have to install a tool to see it. In my experience to truely explain or document a data design needs a lot more narative and fewer lists.

I was kinda joking on the Database Spec. I can see everyone is just reverse engineering it and then guessing. You only have to look at that streamdetails table to see some serious randomness!

"Reverse engineering" is a good description! I do try to limit my guessing though. I have never looked into the video lib, so really can't comment more on randomness. All I would say is remember how small the dev team is, and that we are hard working volunteers.

[BatterPudding]

There is a long running thread in the developers forum where they've been designing a new database structure. I haven't read the entire thread, but it appears to still be a work in progress.

http://forum.kodi.tv/showthread.php?tid=200911

Thanks. Should be useful as it will let us see if they have spotted any of the issues we are seeing. But my head can't deal with 20 pages of thread reading tonight

Good luck with that too
I gave up. The end result seems to be yet another list of table and field names, oh and an ERD (which actually tells you less than people think it does) but you have to install a tool to see it. In my experience to truely explain or document a data design needs a lot more narative and fewer lists.

I was kinda joking on the Database Spec. I can see everyone is just reverse engineering it and then guessing. You only have to look at that streamdetails table to see some serious randomness!

"Reverse engineering" is a good description! I do try to limit my guessing though. I have never looked into the video lib, so really can't comment more on randomness. All I would say is remember how small the dev team is, and that we are hard working volunteers.

We'll see where this takes us. Of course we start with making this awkward for ourselves by being in the Windows support folder... where few devs fear to tread. That dev thread is also going to be out of bounds to us as we are not in the special club yet. Not had the Dev Initiation. Hopefully some usable knowledge in there to read. Or it may just be looking into the mind of the insane...

(No idea what an ERD is... my dev language is from the 1990s)

Totally agree with you that a "spec" is not a list. I can also see why it is next to impossible to get done with so few people working on the project. More a case of hoping people have written a few comments... trouble is when you're just hacking around with a database you don't have the luxury of comments to read. Just table and field names. I haven't yet worked out how to even download the source of KODI, let alone read or compile it. So this is all looking from the outside.

My aim while working on this will be to stir up some reactions. Kick some flowerpots over. For example - to see if anyone knows why the streamdetails and settings tables are such a mess. And if we don't get any sane reasons... then I will get closer into the actual project code and attempt to fix it with a sharp pokey stick. For now we can at least clean up from the outside with some Python before launching the full Holy Hand Grenade of Antioch into the sauce.


Anyway... we have all read the rumours... we all know there is a dev team of hundreds all being paid six figure wages... bathing in champagne... we just want in on the gravy train and learn the special handshakes.

It is also just fun being able to put something back into the KODI project

[black_eagle]

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

[nickr]

Not sure if any of you guys are aware of this utility, which does more than just textures (ie pictures/artwork).

http://forum.kodi.tv/showthread.php?tid=...aintenance

See in particular [remove] Remove specified library item from media library, ie. remove movie 123

[scott967]

Not sure if any of you guys are aware of this utility, which does more than just textures (ie pictures/artwork).

http://forum.kodi.tv/showthread.php?tid=...aintenance

See in particular [remove] Remove specified library item from media library, ie. remove movie 123

I think as currently defined, texturecache.py only works on library items (union of movies, musicvideos, tvshows, and episodes). I don't think it has any capability to clean out other stuff such as non-source files from streams or pvr recordings.

scott s.
.

[Scott00007]

Does this only apply to SQL DB's or Kodi's native ones as well?

Thanks

[nickr]

They all use sql.

[black_eagle]

Does this only apply to SQL DB's or Kodi's native ones as well?

Thanks

When it's finished, it'll clean both MySQL and SQLite versions of the database.

Not sure if any of you guys are aware of this utility, which does more than just textures (ie pictures/artwork).

http://forum.kodi.tv/showthread.php?tid=...aintenance

See in particular [remove] Remove specified library item from media library, ie. remove movie 123

Yes I'm aware of that and have used it in the past. It doesn't however remove old streams (eg youtube links, rtmp:// etc etc) which this will.

On my home setup I have several machines sharing a MySQL database. One of the machines is used a lot by my children for youtube stuff and currently there are over 3000 lines of links in the files table. People earlier in the thread have mentioned that this is slowing down the cleaning of the database and, lets face it, they really don't need to be in there.

[nickr]

Of course, TCMU only uses stuff in the library, although it's author knows the dB pretty well and may be able to help you.

[BatterPudding]

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...