(2022-10-08, 07:00)Klojum Wrote:

(2022-10-08, 04:10)Karellen Wrote: 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.

(2022-10-08, 07:23)jbinkley60 Wrote: Triggers are generally pretty efficient. 

(2022-10-08, 07:53)Klojum Wrote: 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.

(2022-10-08, 12:38)Klojum Wrote: 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.

• 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
  

• 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
  

(2022-10-08, 14:20)jbinkley60 Wrote: TV Shows will give the option of an entire season(s) or episode(s) within a season

(2022-10-08, 16:52)Klojum Wrote: - 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.

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;

(2022-10-09, 10:25)Klojum Wrote: This is my set of queries where movies are concerned. It's incomplete, but should give you an idea about the tables involved.

(2022-10-09, 10:25)Klojum Wrote: This is my set of queries where movies are concerned. It's incomplete, but should give you an idea about the tables involved.

(2022-10-09, 13:41)jbinkley60 Wrote: Your list is a bit cleaner and more organized than mine

(2022-10-08, 12:38)Klojum Wrote: 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.

(2022-10-10, 09:54)Klojum Wrote: 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.