[BatterPudding]

SELECT * FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

delete t1 FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

Interestingly, updating my library after running this query caused Kodi to recreate a row for every 1st sub-folder of my TV sources...

Code:

smb://SERVER/TV/TV-Continuing/Show 1
smb://SERVER/TV/TV-Continuing/Show 2
smb://SERVER/TV/TV-Ended/Show 1
smb://SERVER/TV/TV-Ended/Show 2

Haven't seen any negative effects yet.

Edit: Looks like the artwork for all of my TV shows and casts is rebuilding...

This makes sense from looking at the contents of my database. I am doing some manual interrogation of my database as I am trying to make some fool proof rules. I can see that many of my correctly scraped folders have NULL in strContent. No real pattern though. I guess it could be from which version of KODI originally scraped the TV show into the database for me. Similar with the "dateAdded" field. Sometimes it has a value and sometimes not.

If I run the SQL command on the paths as suggested
SELECT * FROM `path` as t1 left join files as t2 on t1.idpath = t2.idpath where t2.idpath is null and t1.strContent is null

It would delete 80 totally legitimate parent folders. For example a show scanned in like this:

smb://SERVER/!TV/Lilyhammer/
smb://SERVER/!TV/Lilyhammer/Series 01/
smb://SERVER/!TV/Lilyhammer/Series 02/

Will have the parent folder deleted by the above SQL. NOT a pretty thing to happen in the database as there are lots of parent pointers that this would then break. A path can only really be deleted if it has no siblings.

[BatterPudding]

Ned Scott sent me on a trail of discovery... and has helped me understand where the issue is coming from.


In the SETTINGS table there is a wide list of columns of default values for audio, video and all kinds of things. Crops, resumes, video tweaks, zooms, subtitle choices... the lot.

What I don't understand is why the need to keep these settings for every video? 99% of the videos I watch I never touch settings. This whole table is an identical wall of 0, -1, 1 all the way down... So if the settings are at default values then why do they need writing to the database? Would it not make more sense to only write out the settings if they are changed from the default?

This also means that any SQL statement used to delete an entry from FILES needs to delete same from SETTINGS.

Similar situation with streams. Every stream has audio and video details stored. Which leads to some odd database entries as a separate row is kept for audio and video settings... and then another row added with the same name if those entries change...

[jeffski10]

t would delete 80 totally legitimate parent folders. For example a show scanned in like this:

smb://SERVER/!TV/Lilyhammer/
smb://SERVER/!TV/Lilyhammer/Series 01/
smb://SERVER/!TV/Lilyhammer/Series 02/

Which of course highlights the need for testing in diferent scenarios. I don't use sub folders for my seasons as I hate having empty folders left behind when I've watched and deleted the shows. So all mine are top level and it finds the files in the path so doesn't get removed.

[BatterPudding]

It would delete 80 totally legitimate parent folders. For example a show scanned in like this:

smb://SERVER/!TV/Lilyhammer/
smb://SERVER/!TV/Lilyhammer/Series 01/
smb://SERVER/!TV/Lilyhammer/Series 02/

Which of course highlights the need for testing in diferent scenarios. I don't use sub folders for my seasons as I hate having empty folders left behind when I've watched and deleted the shows. So all mine are top level and it finds the files in the path so doesn't get removed.

Backup, test, dry runs.... absolutely. Everyone uses different different folder layouts. Anyone playing with this needs to be careful. So far I haven't deleted anything... just keep doing dry runs and then looking at the lists produced. And that is with a copy of the database well away from KODI.

[black_eagle]

Having played a bit with the code posted previously, I have found that (for me anyway) if you are using a shared database with MySQL, you can use a query like this

PHP Code:

SELECT *
FROM files AS t1
INNER JOIN path AS t2 ON t1.idpath = t2.idpath
WHERE (
strPath NOT LIKE 'NFS://%'
AND strPath NOT LIKE 'smb:%'
AND strPath NOT LIKE 'pvr%'
); 


As a shared db has to use network paths, I've levered that to my advantage here. My file shares are set up so that NFS is all my stuff and SMB is all my kids stuff. Adding into that the PVR bit for my tvheadend recordings and then excluding all of those returns all the crud. Files I set up wrongly, old mounted directories that no longer apply, plugin related things.

Currently I'm running this against a copy of my current database but I will test it against the real deal as soon as the wife lets me have the TV back !!!

Note that this is SQL not SQLite although the select query will work fine on both. However, if you were to try to delete with it, SQLite will throw a syntax error. I expect @BatterPudding will be along later with a working SQLite statement for deletion

[BatterPudding]

Any newbie reading this - DON'T TRY THIS AT HOME.... Backup or Feckup.... this is liable to self destruct...


Some of us are using SQLite and can't have JOIN commands in DELETE commands... So here is the equivalent commands translated from full SQL to SQLite

Full SQL

PHP Code:

select * FROM files as t1 inner join path as t2 on t1.idpath = t2.idpath where strpath like 'http://%'; 


Same thing in SQLite

PHP Code:

DELETE FROM files WHERE idPath IN (SELECT idPath FROM path WHERE strPath like 'http://%'); 


Full SQL

PHP Code:

SELECT *
FROM files AS t1
INNER JOIN path AS t2 ON t1.idpath = t2.idpath
WHERE (
strPath NOT LIKE 'NFS://%'
AND strPath NOT LIKE 'smb:%'
AND strPath NOT LIKE 'pvr%'
); 


SQLite version

PHP Code:

SELECT * 
FROM files WHERE idPath IN (
SELECT idPath FROM path WHERE (
strPath NOT LIKE 'NFS://%'
AND strPath NOT LIKE 'smb:%'
AND strPath NOT LIKE 'pvr%'
)); 


[black_eagle]

Wouldn't be this would it

PHP Code:

DELETE FROM files WHERE idPath IN (SELECT idPath FROM path WHERE strPath NOT LIKE 'NFS://%' AND strPath NOT LIKE 'smb://%' AND strPath NOT LIKE 'pvr://%'); 


---EDIT---

I learn quickly and I am not cooking.......I do have some wine to finish off though

[BatterPudding]

I learn quickly and I am not cooking.......I do have some wine to finish off though

I did intentionally leave the DELETE bit out and leave it as a SELECT to slow down the copy and pastas....


My variation from my own personal database had been looking at the bad stuff and was going to select it for delete...

This command deletes streams

PHP Code:

DELETE FROM files 
WHERE idPath IN (
SELECT idPath FROM path 
WHERE (strPath LIKE 'rtmp://%'
OR strPath LIKE 'rtmpe:%'
OR strPath LIKE 'plugin:%'
OR strPath LIKE 'http://%'
)); 


USB Drives? look for removeable:// (This is good for me, but bad for people using USB drives to store media on)

Did I mention backing up? BACKUP THE VIDEOS99.DB and don't complain to us if you trash everything... this is a Work in Progress game here...

I also want to take out my BluRay player which is on D:\ for me. And specific servers I know I have retired. smb://OLDSERVER/


For any one else following this, the problem the above is causing is it is *only* deleting the file entries. It isn't deleting the references to these files, or the paths they were in. So these statements are just the start of the cleanup...

[black_eagle]

Same query parameters but with the 'paths' table rather than the 'files' table.

PHP Code:

SELECT *
FROM path AS t1
INNER JOIN path AS t2 ON t1.idpath = t2.idpath
WHERE (
t1.strPath NOT LIKE 'NFS://%'
AND t1.strPath NOT LIKE 'smb:%'
AND t1.strPath NOT LIKE 'pvr%'
); 


[BatterPudding]

Wow!! okay... I just did a test on my system. In my case I have ALL the files I want to keep in very obvious paths. So I ran this clean-up on my database using a variation of the above. Remember my post #15 where I started to talk about how my Clean Library *always* stalls and aborts on first run and always needs to be run twice. And how our debug logs fill with duff links and streams that can't be cleaned...

Well now the Clean Library doesn't stall. It is also comedy fast as I see it whizz past, but clearly working well now. As to the debug log? Spotless!!

This run of Clean Library now does does clean out most of the broken paths for the removed junk - especially to those streams. Settings table seems to have shrunk, but the streamdetails table seems to have been kept even though all the streams have gone now...


This is a Work in Progress as we have done a bit of a mess to clean up here. But it is proving a theory so more work will be done.


Thanks Black_Eagle and other people helping out.

[BatterPudding]

Same query parameters but with the 'paths' table rather than the 'files' table.

PHP Code:

SELECT *
FROM path AS t1
INNER JOIN path AS t2 ON t1.idpath = t2.idpath
WHERE (
t1.strPath NOT LIKE 'NFS://%'
AND t1.strPath NOT LIKE 'smb:%'
AND t1.strPath NOT LIKE 'pvr%'
); 


No need to do that... the PATHS table will be cleaned up by KODI's current <Clean Library> command as long as it is run after our new delete files commands. I just did a test on my live system! (Well, copied the database over to see what progress we have made)

[black_eagle]

Yes, I'm going to look at what other tables will need things removing (based around this path based concept) and see if I can come up with some python to pull it all together.

As suggested by @BatterPudding, this should load a users sources.xml and obtain the (wanted) paths from there. We should then be able to go on and safely remove anything that isn't in those paths. I think a prompt at a 'removeable://' path would be appropriate as many people use USB storage and I'm not sure if they get added to the sources file ??

Anyway, that's for another day when the wine is all gone

----EDIT----

Ah, maybe I don't need to look at other tables then. That makes things rather easier !!

[BatterPudding]

There needs to be some exceptions added to the delete list.

For example, a table like bookmarks is highly likely to have files that need to be kept, but aren't listed in the main library. I could see someone bookmarking something part way through a BluRay disk and wanting that bookmark to survive our Deep SQLeenup.

That settings table is a headache... if it wasn't full of all those default values it too could be used to spot when someone changed a setting that may want to be preserved.

The Streamdetails table just looks wrong... it is a weird mess. It also does not get cleaned up when the streams are deleted from the files table. So this is certainly one for a clean out.


Maybe if this is becoming a real add-on \ script then we need an option "Clean out old streams?" which will then just let us delete that whole streamdetails table and start fresh with it.

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

It is especially this note that would be good to expand on: In addition to indexing media files when activated by user-selected Content settings, Kodi also puts a video in its database if you change any OSD setting while watching it. Resume points are stored in this database as well. These entries are added to the database whether the affected video is part of the Video Library or not.

So that adds "Resume Points" to the list of reasons to keep other files.

I still think the settings table is showing a KODI bug though. As per the above statement - that table should only get a record added for a video if you change any OSD setting while watching. Which implies - no change, then don't pointlessly write the settings out.

[black_eagle]

I have some python that parses the sources.xml and pulls out all the video paths, excludes them all and then deletes everything else from the 'files' table.

I'm a bit unsure though how to handle PVR stuff. pvr:// is an internal path used by Kodi. I have lots of old (and now deleted) recordings in my database. If I delete all of them, does anyone know if Kodi pulls the current ones back from the backend each time it 'talks' to it, or is it a one-shot deal ?

I did notice that the current recordings have 'active' in the path and the older deleted ones don't but that could just be because TVHeadend has changed several times since the database was originally written. On top of this, other backends may exhibit different behaviour so any info in this area would be appreciated.

[DaveBlake]

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!