[jbinkley60]

Hmmm, you are not actually writing to, or deleting records, directly. You are just reading? I wonder if that is ok.

Probably the reason why Milhouse's tool was never assimilated into the Kodi repo. It can change/remove quite a bit.

OFF-TOPIC: A stand-alone, dedicated Library Clean up GUI add-on for video and/or music databases would be useful that way, and its development would be quicker and more precise than the current built-in database triggers for cleaning things.

Correct, the Kodi CSV Export utility is just a single read select query:

curm = dbexport.execute('SELECT * FROM '+selectname+'')

I've never submitted the Mezzmo Kodi addon to the Kodi repo because it uses many many direct SQL calls to rewrite the entire Kodi video database each day.  I didn't think I could get an exception for that


OFF-TOPIC:

Triggers are generally pretty efficient.  Are you thinking of an addon where you could browse and selectively delete orphaned records in the video or music databases ?  If so, that wouldn't be too difficult to write but displaying entire rows for some tables might be a challenge.  Selecting and deleting rows might stretch the limits on how many entries a xbmcgui.Dialog().select object can have but that aside, it would be very straightforward.


Jeff

[Klojum]

Triggers are generally pretty efficient. 

They can be. But IMO the current schema of the Kodi video database (the music database I kinda lost track of) as well as Kodi's internal scrubbing is still pretty outdated and lots of stuff gets left behind, not to mention the whole thumbnail cache which only gets data added to. With the triggers, now only data from 4-5 video database tables is erased, while a TV show can hold data in upto 18 tables. In absolute numbers, the actor_link table has the most records.

After some tinkering I use this subroutine in my PHP toolkit to clean out a full TV show, and something similar for Movies. It certainly helped when the Refresh option in TVshows wouldn't work for some reason in earlier Kodi versions. You could also make it more selective and run it for only a season or a single episode. I still think the current cleaning is under par.

[jbinkley60]

They can be. But IMO the current schema of the Kodi video database (the music database I kinda lost track of) as well as Kodi's internal scrubbing is still pretty outdated and lots of stuff gets left behind, not to mention the whole thumbnail cache which only gets data added to. With the triggers, now only data from 4-5 video database tables is erased, while a TV show can hold data in upto 18 tables. In absolute numbers, the actor_link table has the most records.

After some tinkering I use this subroutine in my PHP toolkit to clean out a full TV show, and something similar for Movies. It certainly helped when the Refresh option in TVshows wouldn't work for some reason in earlier Kodi versions. You could also make it more selective and run it for only a season or a single episode. I still think the current cleaning is under par.

Understood.  I wrote  similar cleaning routine in Python for my daily resync in the Mezzmo addon.  I had to go through and figure out which data had triggers and which didn't.  For me it was fairly easy.  I just deleted things and see what got left behind and then wrote a delete query for it or reverse engineer the insert SQL sequence. To your point, triggers are effective but they need to be maintained.  For the video database they partially cover the schema.  My actor_link table has 332k entries in it but it gets rebuilt each day so no stale entries.  I can see how it would build up otherwise.

Writing an addon which reads in the TV show and Seasons tables joined together so you can see all seasons of a TV Show and allowing a selective delete for a season would be fairly easy.   Removing actors which exist for those seasons and no longer exist after the season deletion is a bit more work.  This is where a trigger would help.  Maybe have the addon create any missing triggers and then remove them when the addon is done ?  The thumbnail cache is a bit more of a challenge.  I handle it easily in the Mezzmo addon because the Mezzmo hosts the artwork so I just reset the lasthashcheck date value in the texture table and Kodi handles the aching and freshness.  With local static artwork that won't work but the textures table does maintain the url to cachedurl mapping so it is possible.  

If you would like me to pull together the beginnings of the selective TV season delete addon, I'd be happy to do it.  I'd need some help testing and a cute name for it..




Thanks,

Jeff

[Klojum]

We're fully off-topic by now.. (*cough*)

Actors are linked per tv episode, not so much a season. So you could create an subroutine/trigger/whatever to find only those entries that fit the selection for a tvshow-season-episode. I'd say you can pinpoint things better using scripts, c++, python or otherwise. Adding triggers temporarily or otherwise also involves database user rights. Although 95% of Kodi users with a MySQL/MariaDB will probably have full rights by default.

For removing from the Kodi GUI a single episode or a season (when does that ever happen?) or a full show, you will still need to find per episode individually:
- all related entries in the local Textures13.db to be removed
- all related thumbnail files to be deleted
- all related entries in video tables to be removed
Removing a full TV show may seem easier, but the local cache may be more difficult to scrub. I didn't dive into that part. Too lazy to get php_sqlite3 to work with networked .db files...

Things get a bit more ugly when videos are removed without Kodi's knowing by the user manually off the source drive. Cleaning up the video library can certainly be problematic for those people juggling multiple external/USB drives which are not connected at the same time. Things may also take a bit longer, but I prefer things to work properly.

Perhaps you can also have a look at the Video Database Cleaner add-on. It was reignited for Kodi 19 and included an matching version of Milhouse's Texture Cache cleaner I believe. However, things have gone silent again on the development/support front unfortunately.

[Klojum]

@jbinkley60 New thread created.

[jbinkley60]

Perhaps you can also have a look at the Video Database Cleaner add-on. It was reignited for Kodi 19 and included an matching version of Milhouse's Texture Cache cleaner I believe. However, things have gone silent again on the development/support front unfortunately.

The Video Database Cleaner is a bit more of a bulk automated approach to the problem.  I am thinking of an addon which is more surgical GUI based in nature where folks could browse existing database entries and selective delete items, including all associated video table data, textures and underlying artwork files. 

Here's a potential initial set of features:
 

• Select either movies, tvshows, or music videos
  
• TV Shows will give the option of an entire season(s) or episode(s) within a season
  
• Movies and music videos will allow individual or multiple selections of deletions
  
• Menu item for user to backup the video database with a setting on how many backups to keep
  
• Addon setting to remind user to backup database before starting any deletions
  
• Deletion confirmation notification
  
• Kodi 19 and 20 support
  

Additional future options:
 

• Expand to music database
  
• Database restore option with executebuiltin UpdateLibrary to refresh skin
  
• GUI indication on whether underlying video / music file actually exists
  
• Full path table entry removals with all associated data / files
  
• Detailed logging with history
  
• Search capability by movie
  

If this is of interest to folks, I'll consider writing it.  I have much of the code already available.  It isn't something I would likely use since my Kodi database hygiene is already fully automated across all clients but it could be of help to a number of Kodi users.  If there's interest I could quickly pull together the menu GUI with select queries to display movies, TV shows seasons and episodes.

I'll see what the feedback is like.


Thanks,

Jeff

[Klojum]

TV Shows will give the option of an entire season(s) or episode(s) within a season

Some other things to consider:

- Some TVshows have 26 or more episodes. Can you deal with such numbers in a Kodi GUI window including multiple selections?
- Movies are easier to start with, TVshows can have upto 20+ seasons. Movies can be stored in subfolders too when collections have been created (i use that method too)
- Making backups of local databases should be easy, MySQL backups could be triggered via a command line tool.
- A SkinRefresh is pretty easy.

[jbinkley60]

- Some TVshows have 26 or more episodes. Can you deal with such numbers in a Kodi GUI window including multiple selections?
- Movies are easier to start with, TVshows can have upto 20+ seasons. Movies can be stored in subfolders too when collections have been created (i use that method too)
- Making backups of local databases should be easy, MySQL backups could be triggered via a command line tool.
- A SkinRefresh is pretty easy.

Thanks for the feedback.  I've been giving this some thought and looking at a chunk of code I already have from the Mezzmo Kodi addon and quite a lot of it is reusable for the GUI, logging, database queries and skinrefresh.  I don't see the GUI limits as an issue.  I'll test up to a few hundred with some loop code just to be sure.  One thought though is folks will need systems with page up/page down capability along with moveup / movedown for any GUI selection boxes with a large list.  For TV Shows I am thinking a drill down menu approach might work. 

Initial menu structure might be something like:

Movies
Music Videos
TV Shows --> Seasons --> Episodes
Music
Database backup --> Select database to backup
View Logs --> View all logs or Logs by date

The movies table is flat so I can chop it up pretty much however makes sense (i.e. by first letter, by quantity, strSet / collections etc..)  I figure the largest databases probably don't have more than 5,000 movies with most being much smaller.  The music database menu could likely be a drill down like TV Shows.  Album  --> track .

I'll see if I have some time this week to cobble together the basic structure and GUI menus with the select queries to populate.  The delete queries can then be easily added to the item(s) selected by the user.


Jeff

[Klojum]

This is my set of queries where movies are concerned. It's incomplete, but should give you an idea about the tables involved.

php:
		dump ('Start removing movie from database...');

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

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



		# REMOVE ACTORS FROM MOVIE

		$number   = remove_actors_from_media($idFile,"movie");

		dump ('Number of actors removed linked to movie: '.$number);



		$query = array();

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

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

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

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

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

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

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

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



		$query[] = 'DELETE FROM art            WHERE media_type="movie" AND media_id='.$idMovie; # MOVIE POSTER

		$query[] = 'DELETE FROM art            WHERE media_type="actor" AND media_id='.$idMovie; # ACTOR THUMBS

		// $query[] = 'DELETE FROM art            WHERE media_type="set"   AND media_id='.$idSet;   # MOVIE COLLECTION POSTER IF NECESSARY



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

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

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

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

		$query[] = 'DELETE FROM movie          WHERE idMovie='.$idMovie;



		//dump ($query);



		# REMOVE MOVIE SETS

		$num_rmv = movieset_remove();

		echo '<br />';

		if ($num_rmv > 0 )

		{

			echo $num_rmv.' moviesets have been removed.';

		}

		else

		{

			echo 'No moviesets were removed.';

		}



		exit;

[jbinkley60]

This is my set of queries where movies are concerned. It's incomplete, but should give you an idea about the tables involved.

Thanks.  I'll compare that to my list when I get to the SQL queries.  Your list is a bit cleaner and more organized than mine    I just pushed out a pending release of the Mezzmo Kodi addon so I can get started on this now.


Jeff

[jbinkley60]

This is my set of queries where movies are concerned. It's incomplete, but should give you an idea about the tables involved.

I got a good start on it today.  The main menu is working and is querying the number of records in each category.  The logging is working but I haven't cut/pasted the viewer code and the drill down for TV Shows isn't done yet.  Those are next on the list along with a few settings.


Jeff

[Klojum]

Your list is a bit cleaner and more organized than mine

Well, some thought went into the whole sequence: first delete non-essential things, and delete the important parts at the end
Some could be combined into one (i.e. deletes from the art table). I'd also think of deleting the thumbnail cache entries first. If that fails for whatever reason, perhaps the rest of the delete needs to be hold off / investigated?

Also some deletes could be conditional, such as "delete a movie set" when all its movies are no longer present. Some other deletes are more delicate: right now, when deleting a movie from the GUI, only the video file gets deleted. The .nfo file, all (extra)fanart, external subtitle(s) as well as the entire folder are all untouched and stay behind as dead weight. Something you can partially understand as some lazy people place tons of movies into 1 single folder. But having an option to also delete the movie folder whenever the user has a clean file setup with 1 movie per subfolder, would have my vote.

Same goes for TV shows when you delete the last remaining episode of a season (remove that season), and/or the last season of a tvshow (remove the tv show and all its other 'tentacles'). The best 'object-related' approach IMO would be that all related items would be dealt with separately, so that also all cache art/fan/thumbs/files can be removed individually per that item, resulting a pinpointed yet proper cleanup. I know from an earlier life in programming that making things fool-proof with all kinds of fail safes can make a program 3-4 times as big, but better safe than sorry.

[HeresJohnny]

Things get a bit more ugly when videos are removed without Kodi's knowing by the user manually off the source drive. Cleaning up the video library can certainly be problematic for those people juggling multiple external/USB drives which are not connected at the same time. Things may also take a bit longer, but I prefer things to work properly.

That is the essential bit which isn't addressed properly in other cleaning routines yet. It would be great if a new routine could identify data bits which have become orphaned and mark them for deletion.

It's even more pronounced for the music database where no proper cleaning mechanism has been implemented as of yet according to the main music maintainers. If/when you want to tackle this at a later point it would probably be a good idea to involve @DaveBlake and maybe @black_eagle

[Klojum]

It *should* be simple: Kodi uses file sources. Check if the source is actually live/connected or not, and act accordingly. Right now, when removing a source, Kodi also asks to delete entries. I have no clue what is deleted or not during that particular routine. I'm guessing again only the basic 4 tables are touched.

[jbinkley60]

It *should* be simple: Kodi uses file sources. Check if the source is actually live/connected or not, and act accordingly. Right now, when removing a source, Kodi also asks to delete entries. I have no clue what is deleted or not during that particular routine. I'm guessing again only the basic 4 tables are touched.

I'll see how far I want to take this.  My initial goal is to help folks clean up some of the Kodi database issues and allow them to selectively delete things which are "a mess" and everything associated with the mess.  They can then reimport the cause of the mess and start new.  I am planning to add visualization like the simple record counts so that when you get down to individual items (i.e. movies, episodes etc..) it will tell you whether the underlying file exists or not.  This could help with the orphaned item issue.  I currently am not planning to write an entire referential integrity checker against the Kodi databases.  It doesn't mean I couldn't be convinced to in the future.

This is where I bang a drum for a second.  As mentioned previously, I will likely never use this addon.  With my Mezzmo solution I have over 17,000 movies and TV Shows, 70K+ actors and it is all 100% pristine.  All of my Kodi client databases are rebuilt completely each day, updated hourly and I never ever deal with any Kodi database issues.  My only point in saying this is that there are other ways to solve the problem. 

The challenge with the addon is that the Kodi community tends to be a mix of folks who want to get into the bits and bytes details and others who want a high level abstraction "just make it work reliably."  I think there is a need for both a full Kodi cleaning solution with is automatic (or can be called automatically) and one which is more surgical in nature.  I am going after the more surgical in nature.  Mezzmo has both a maintenance function which can remove all stale stuff against a folder, playlist or the entire database and an item delete capability which allows you to delete everything associated with a library item (i.e. video, music file etc..).  This addon is trying to create a similar item delete type of functionality and database cleanup.

I did make more progress.  The log viewer is working as well as I added a log CSV export option so you can pull out all logs into a CSV file.  The drill down for TV Shows is next.

Thanks,

Jeff