• 1
  • 25
  • 26
  • 27(current)
  • 28
  • 29
  • 39
v19 Video Database Cleaner add-on
@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. Wink
Reply
@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.
(2020-05-26, 13:50)Klojum Wrote: Too bad my C++ skills are non-existent at this time.

Meh, just jump in!
Learning Linux the hard way !!
Reply
(2020-05-26, 13:45)black_eagle Wrote: @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
Reply
Quote:<!--- The three settings will go in this space, between the two network tags. --->

That comment is not valid xml Smile 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.
Learning Linux the hard way !!
Reply
(2020-05-26, 16:09)black_eagle Wrote:
Quote:<!--- The three settings will go in this space, between the two network tags. --->

That comment is not valid xml Smile 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  Angel

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!! Laugh

br bobdude
Reply
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 Sad now this gone Smile

Now onwards I will use this addon. THANK YOU!
Reply
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.
Learning Linux the hard way !!
Reply
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? ¿¿¿Huh

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

bye!
Reply
(2020-06-17, 20:27)peque Wrote: 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 ?
Reply
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!
Reply
(2020-06-19, 10:52)peque Wrote: 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.
Reply
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!
Learning Linux the hard way !!
Reply
(2020-06-19, 14:10)Klojum Wrote:
(2020-06-19, 10:52)peque Wrote: 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.
Reply
(2020-06-19, 14:29)black_eagle Wrote: 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.
Reply
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
Nexus Beta 1
Aeon Nox:Silvo
Rapier 12.2.16
Nvidia Shield 2017
Reply
  • 1
  • 25
  • 26
  • 27(current)
  • 28
  • 29
  • 39

Logout Mark Read Team Forum Stats Members Help
Video Database Cleaner add-on5