[Klojum]

@black_eagle

Having a scrub tool to clean out the database is a good thing to have although Kodi should be really able to clean up after itself. I read that you mainly focus on the files database table alone. That's a good start, but other video database tables will contain leftovers as well from deleted videos.

This is part of a PHP script I run whenever I need to clean out a TV show from the Kodi database. Some 17 18 tables are possibly linked to a single TV show as you can see.

php:


dump ('Remove selected TV show from video database');



$result = mysqli_autocommit($_SESSION['connection']['video'], FALSE);

$result = mysqli_begin_transaction($_SESSION['connection']['video']);



$query = array();

$query[] = 'DELETE FROM path           WHERE idParentPath='.$tvshow_root_path.' OR idPath='.$tvshow_root_path;

$query[] = 'DELETE FROM actor_link     WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM country_link   WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM director_link  WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM writer_link    WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM uniqueid       WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM studio_link    WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM rating         WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM genre_link     WHERE media_type="tvshow" AND media_id='.$idShow;

$query[] = 'DELETE FROM art            WHERE media_type IN ("tvshow", "episode") AND media_id='.$idShow;

$query[] = 'DELETE FROM streamdetails  WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

$query[] = 'DELETE FROM settings       WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

$query[] = 'DELETE FROM bookmark       WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

$query[] = 'DELETE FROM files          WHERE idFile IN ( SELECT idFile FROM episode WHERE idShow='.$idShow.')';

$query[] = 'DELETE FROM tvshowlinkpath WHERE idShow='.$idShow;

$query[] = 'DELETE FROM episode        WHERE idShow='.$idShow;

$query[] = 'DELETE FROM seasons        WHERE idShow='.$idShow;

$query[] = 'DELETE FROM tvshow         WHERE idShow='.$idShow;



foreach ($query as $key => $kwry)

{

	$result = do_query($kwry, 'video', 'DELETE rule #'.$key);

	if ($_SESSION['connection']['video']->errno != 0) { $err_flag++; }

}



if ($err_flag == 0)

{

	$result = mysqli_commit($_SESSION['connection']['video']);

	if ($result)

	{

		dump('TV show has been exterminated from MySQL database.');

	}

	else

	{

		dump('MySQL commit was NOT executed.');

	}

}

else

{

	$result = mysqli_rollback($_SESSION['connection']['video']);

	dump('TV show could not be exterminated from MySQL database.');

}

I'm hoping that something similar will ever end up in the Kodi application. Too bad my C++ skills are non-existent at this time.

And then there are also the related database entries in the local Textures13.db database as well as the subsequent local thumbnail files...
I don't know if you can use any of the above script, but perhaps it's an eye opener.

[black_eagle]

@Klojum Thanks!  I co-wrote this 'back in the day' with another forum member, mainly because every video you watch ends up in the db, not just the stuff you have added and/or scraped.  That causes issues when updating or cleaning using Kodi's internal routines because every URL gets checked to see if it still exists or not regardless of whether it's a YT video you watched three years ago or a film you watched yesterday.

I knew very little SQL back then and even less about Kodi internals. I have learnt a lot over the last year or so therefore a re-write might be a possibility in the future, time and commitments permitting.

Too bad my C++ skills are non-existent at this time.

Meh, just jump in!

[bobdude]

@bobdude Please post a copy of your advancedsettings.xml to https://paste.kodi.tv . If the addon is flagging it as an issue then it's likely either a closing tag is missing somewhere or a comment in it is incorrectly formed.  Without seeing the file though that's just a guess.

hi black_eagle,

I pasted here:
https://paste.kodi.tv/vemogarure

would be weird, though, as kodi works generally fine, including connection to the database.
on the other hand, it 'd be great, if it were as simple as that :-)

br bobdude

[black_eagle]

<!--- The three settings will go in this space, between the two network tags. --->

That comment is not valid xml There are three - when there should be only two. If you fix that, I think the rest of it looks OK and the addon will then read it.

[bobdude]

<!--- The three settings will go in this space, between the two network tags. --->

That comment is not valid xml There are three - when there should be only two. If you fix that, I think the rest of it looks OK and the addon will then read it.   

damn xml 

h.o.l.y.  f.r.i.g.g.i.n crap!!! 
I shall include you in my nightly prayers! 

My library now cleans up within a couple of seconds.
your script is awesome!!
Thank you so much!!

br bobdude

[bob007]

Hi, 
this is fantastic tool... I am not aware how the Kodi clean library works, but nowadays the simple kodi clean library takes me about an hour. (normally it is just 1-2 mins). With this tool it took 1 min again and after the log I recognised that kodi kept my "old" ftp connections what i have visited years ago. Inside the kodi.log i recognized that simple kodi clean library waits for each video/folder for a 30s timeout... multiplied with the videos what i have left in there gives me the hour now this gone

Now onwards I will use this addon. THANK YOU!

[black_eagle]

Thanks!  Always nice to know it's worked out well for someone.  I can't take all the credit though, @BatterPudding wrote all the important library cleaning stuff.  In spite of him being MIA for a while I hope he's OK in these difficult times.  If it hadn't been for his input and skills this addon wouldn't exist.

[peque]

I've discovered this script today trying to remove old paths, and it has really speed up my kodi stock clean database times after removing more than 40 old paths... Thank you!!!

BUT!!!

What could make that there's one old video path that the script "says" it has deleted, but it hasn't, and when you run the script it shows again and again? ¿¿¿

There's no relevant info in the logs, that's why I don't paste them here... It just says "x path removed"... but it's not true.

bye!

[Klojum]

What could make that there's one old video path that the script "says" it has deleted, but it hasn't,

Got an example of such a full folder path ?

[peque]

Yes, of course! It's the only one that the script doesn't clean:

smb://acmt/Masivos04/Series/La Abeja Maya (original)/Temporada 1/

I've browsed video db and I've found this path in the next tables:

path
tvshow_view (associated with a description that has nothing to do with actual content, btw)

bye!

[Klojum]

I've browsed video db and I've found this path in the next tables:
path
tvshow_view (associated with a description that has nothing to do with actual content, btw)

tvshow_view is a 'view', which combines two or more tables into one listing, so the tvshow show should be in the path too.

if the tvshow_view entry is somehow not linked to the correct tvshow/path, then you have a corrupted database.

[black_eagle]

In the settings for the add-on there is an option to remove a specific path. If you use that and put in smb://acmt/Masivos04/Series/La Abeja Maya (original)/Temporada 1/ then the add-on should nuke that from the path table.  Note that there is absolutely no checking that the path you entered is valid or that it is indeed an orphaned path so make doubly sure you get it right!

[peque]

I've browsed video db and I've found this path in the next tables:
path
tvshow_view (associated with a description that has nothing to do with actual content, btw)

tvshow_view is a 'view', which combines two or more tables into one listing, so the tvshow show should be in the path too.

if the tvshow_view entry is somehow not linked to the correct tvshow/path, then you have a corrupted database.

Ok. Understood. View tables are somehow predefined queries available. Good. And yes, may be this is and invalid entry.

[peque]

In the settings for the add-on there is an option to remove a specific path. If you use that and put in smb://acmt/Masivos04/Series/La Abeja Maya (original)/Temporada 1/ then the add-on should nuke that from the path table.  Note that there is absolutely no checking that the path you entered is valid or that it is indeed an orphaned path so make doubly sure you get it right!

That did the trick. I had the doubt if even forcing it, script would be able to erase it. But yes, it did. The other option was just to delete the row at db level with a db browser app. But I didn't want as I'm new to kodi db structure.

Thanks a lot.

[RB26]

First, thanks to the original authors for this tool, I have used this in the past with Leia, and it worked great,
and Thanks to Scott967 for fixing it for Matrix.

Just FYI:
I had a small problem getting this working with Matrix,
solution was changing line 53 in Default.py from: MAX_VIDEODB_VERSION = 116 to MAX_VIDEODB_VERSION = 117

Cheers,
Ron