[jbinkley60]

Kodi Selective Cleaner

This is a Kodi program addon to allow selective deletion of data in the Kodi Video and Music databases. 

The concept of this addon came from another thread with the desire for a tool which can be used to cleanup orphaned information in the Kodi databases.  

Some goals for the tool are:

• 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
• Removal of all associated data with selected deletions (i.e. artwork, actors, directors, textures cache etc..)
• 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
• Detailed logging with history

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 exist
• Full path table entry removals with all associated data / files
• Search capability by movie
• Orphaned data identification and removal

The GitHub site has moved and the initial 1.0.0 release code has been posted. 


What's working:

• Main menu with count of records for each data type
• Kodi video database analyzer with CSV export and cleaning
• Detailed logging, log viewing and log CSV export
• Drill downs for all media types Movies, TV Shows, Music Videos and Albums
• CSV Export for most tables in the Video, Music and Textures databases
• Selective Kodi database backups and backup limit setting (Kodi local only)
• Support for Kodi 19- 21
• Support for both Kodi local and MySQL databases


What's not working:

• Everything else

Testing and feedback are very welcome.  

If you'd like to help with this project, the GitHub page is here.

The latest updates are in this forum and on the Kodi Selective Cleaner Wiki .



Thanks,

Jeff

[jbinkley60]

I have posted v0.0.2 alpha to GitHub. 

This release does the following:

-  Fixes a couple of minor bugs related to the music count being inaccurate and an error occurred if you attempted to select a media type with 0 records
- Completed initial drill down capabilities for all media types including Movies, TV Shows, Music Videos and Music

With this release you can start browsing your video and music libraries to see all entries.  I would like some feedback on the Music drill down and whether to go down to the track level on an album.  Basically the question is whether someone thinks they would want to delete individual tracks or just an entire album ?  Right now the music drill down stops at the album level. 

I've been giving some thoughts to the orphaned data issue.  My initial thoughts is that this is doable on a table by table basis.  I could add an analysis module which would allow you to analyze a table for orphaned entries.  For example the actor (and actor_link) table where there are entries but no associated link to another table (i.e. actor_link points to TV Episode which has been deleted).   The analysis module would give you a report of all orphaned data and then you can chose to delete / clean all of it.  


Feedback welcome,

Jeff

[Klojum]

Perhaps a --dry-run option is optional? Just to see what the intentions are at that point.
If Kodi 19 & 20 is supported, this is no longer necessary in multiple sections:

python:
  if installed_version == '18':

I'll try and give it a spin later today. My brain & body are not yet in optimal mode after getting a flu shot yesterday.

[jbinkley60]

Perhaps a --dry-run option is optional? Just to see what the intentions are at that point.
If Kodi 19 & 20 is supported, this is no longer necessary in multiple sections:

Thanks.  I pulled out the earlier Kodi versions 10 - 17 and forgot to remove 18.  I 'll give the dry run thought some thought on how to do it with the menuing.  It is doable.  Part of the challenge is that if if a higher level element is deleted then the database triggers take effect for all the lower elements (i.e. delete a movie and the delete_movie trigger fires).  It becomes a deign decision on using the higher level triggers where they exist and then cleanup orphan data or recreate deleting all elements starting at the lowest level and working up.  The later would make it easier to see what would be deleted in advance. 

I just posted v0.0.3 which has a few fixes, added full CSV export capabilities, including the textures database.  I decided to pull over the CSV Export code since that will be helpful for testing and so folks can export parts of their database without the separate CSV Export addon.


Jeff

[jbinkley60]

I have posted v0.0.4 alpha to GitHub. 

This release does the following:

• Added CSV export capability for almost all video, music and textures tables
• Added selective Kodi database backups and backup limit setting

In addition to having a full hierarchical browser for your Kodi databases with this release you can quickly backup your Video, Music and Textures databases with a setting to select how many backup copies to keep (currently 1 - 20) or 0 for unlimited.  This could also be turned into a background service which could run on a regular basis to keep backup copies of the databases.  I'll consider this if there is interest.   

The next phase of this addon development is to write the SQL deletion code as well as some cleanup code for the textures database and underlying artwork.  I am going to pause a little on this and wait for some feedback on the GUI structure and where folks would like me to start.  I've hard he music database is the least mature but I am happy to start with movies, TV shows etc...  Also, the addon currently does not have browse support for the the textures table.  I am not sure if folks would want that and the ability to selectively delete textures entries ?  That feels like something better handled by an orphaned data function.   


Feedback welcome,

Jeff

[jbinkley60]

Perhaps a --dry-run option is optional? Just to see what the intentions are at that point.

I am going to pivot a bit and focus on a couple of new menu options I am adding called:  Video DB Data Analyzer and Music DB Data Analyzer.  These new menu options will give you a table to select (i.e. actor, actor_link, art etc..) and then the option of Analyze, CSV Export of Clean.  They will look for orphaned data in the tables:

- Analyze will analyze the table and present orphaned data on the screen using the text Viewer
- CSV Export will analyze the table but export the results in a CSV file
- Clean will take the analyzer results and delete the orphaned records

I've already written and tested the SQL queries for analyzing and cleaning the actor_link table.  I'll have this out in the next version, 0.0.5 to test in a couple of days.  Once I have one table working and folks like the results, it will be fairly easy to add other tables.

feedback is welcome.


Thanks,

Jeff

 


l

[jbinkley60]

version 0.0.5 alpha has been posted to GitHub. 

This release does the following:

• Added initial support for the Video DB analyzer which looks for orphaned data

The first two tables in the selector, actor and actor_link are both working where it will find unmatched data against the episode, movie, tvshow, musicvideo and seasons table.  The CSV export and Clean Table options are not working yet but this will begin to demonstrate the capabilities of the addon. 

Here's a sample output of the actor_link table:



I've started a Wiki page  for the Kodi Selective Cleaner.  I'll add content as development continues. 

I'd appreciate if some folks could download the addon and run the Video DB analyzer against your actor and actor_link tables, as well as testing out the database backup utility.  None of these functions write to or make any changes to your databases but you might be surprised what you find.   


Feedback welcome,

Jeff

[HeresJohnny]

I've installed v0.5 in Kodi 19 then ran the cleaner against my actors table. No errors where thrown and it found no inconsistencies. I also run the backup function for video and music. It did create a backup but in my case that is just a nice illusion because the backup was of the sqlite databases whereas I use a MySQL setup. So the backup was empty :-).

I'm really looking forward to when cleaning of other video and music tables will be functional.

[HeresJohnny]

Also installed v0.5 in Kodi 20 but it wouldn't run. The following error was thrown:

python:
2022-10-15 13:00:19.819 T:4792     INFO <general>: KS Cleaner logging database check successful.  Addon started.

2022-10-15 13:00:19.836 T:4792    ERROR <general>: KS Cleaner main menu error. 

2022-10-15 13:00:19.837 T:4792    ERROR <general>: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

                                                    - NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!

                                                   Error Type: <class 'UnboundLocalError'>

                                                   Error Contents: local variable 'curpf' referenced before assignment

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 42, in displayMenu

                                                       curpf = kvfile.execute('SELECT count (c00) FROM movie',)

                                                   sqlite3.OperationalError: no such table: movie

                                                   

                                                   During handling of the above exception, another exception occurred:

                                                   

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py", line 2, in <module>

                                                       from resources.lib import menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 118, in <module>

                                                       displayMenu()                                                   #  Display main menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 80, in displayMenu

                                                       del curpf

                                                   UnboundLocalError: local variable 'curpf' referenced before assignment

                                                   -->End of Python script error report<--

[jbinkley60]

I've installed v0.5 in Kodi 19 then ran the cleaner against my actors table. No errors where thrown and it found no inconsistencies. I also run the backup function for video and music. It did create a backup but in my case that is just a nice illusion because the backup was of the sqlite databases whereas I use a MySQL setup. So the backup was empty :-).

I'm really looking forward to when cleaning of other video and music tables will be functional.

Can you try the actor_link table ? That one is more interesting because it compares to 6 other tables vs. 1 for actors. I'll finish the remaining tables once I fix some formatting issues.

Jeff

[jbinkley60]

Also installed v0.5 in Kodi 20 but it wouldn't run. The following error was thrown:

python:
2022-10-15 13:00:19.819 T:4792     INFO <general>: KS Cleaner logging database check successful.  Addon started.

2022-10-15 13:00:19.836 T:4792    ERROR <general>: KS Cleaner main menu error. 

2022-10-15 13:00:19.837 T:4792    ERROR <general>: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

                                                    - NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!

                                                   Error Type: <class 'UnboundLocalError'>

                                                   Error Contents: local variable 'curpf' referenced before assignment

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 42, in displayMenu

                                                       curpf = kvfile.execute('SELECT count (c00) FROM movie',)

                                                   sqlite3.OperationalError: no such table: movie

                                                   

                                                   During handling of the above exception, another exception occurred:

                                                   

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py", line 2, in <module>

                                                       from resources.lib import menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 118, in <module>

                                                       displayMenu()                                                   #  Display main menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 80, in displayMenu

                                                       del curpf

                                                   UnboundLocalError: local variable 'curpf' referenced before assignment

                                                   -->End of Python script error report<--

This is an issue where the addon can't find your video database. It is looking for MyVideos121.db . What version of version 20 are you running ? It works on my v20 instance.

Jeff

[HeresJohnny]

Nightly 2022-09-27, VideoDB 121, MySQL. Here's an excerpt from a debug log, not much difference. I'l install the latest nightly and see if that changes anything.

python:
2022-10-15 14:03:46.111 T:552     DEBUG <general>: Thread LanguageInvoker start, auto delete: false

2022-10-15 14:03:46.111 T:552     DEBUG <general>: initializing python engine.

2022-10-15 14:03:46.111 T:552     DEBUG <general>: CPythonInvoker(31, C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py): start processing

2022-10-15 14:03:46.131 T:552     DEBUG <general>: -->Python Interpreter Initialized<--

2022-10-15 14:03:46.131 T:552     DEBUG <general>: 

                                                   

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31, C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py): the source file to load is "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py"

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31): default python path:

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N\system\python\Lib

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N\python3.8.zip

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N\system\python\DLLs

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N\system\python

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):   C:\Kodi_N\system\python\lib\site-packages

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31): adding path:

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31):  C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner

2022-10-15 14:03:46.131 T:552     DEBUG <general>: CPythonInvoker(31): adding args:

2022-10-15 14:03:46.132 T:552     DEBUG <general>: CPythonInvoker(31):  default.py

2022-10-15 14:03:46.132 T:552     DEBUG <general>: CPythonInvoker(31, C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py): entering source directory C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner

2022-10-15 14:03:46.132 T:552     DEBUG <general>: CPythonInvoker(31, C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py): instantiating addon using automatically obtained id of "plugin.program.kcsleaner" dependent on version 3.0.0 of the xbmc.python api

2022-10-15 14:03:46.177 T:552      INFO <general>: KS Cleaner logging database check successful.  Addon started.

2022-10-15 14:03:46.193 T:552     ERROR <general>: KS Cleaner main menu error. 

2022-10-15 14:03:46.195 T:552     ERROR <general>: EXCEPTION Thrown (PythonToCppException) : -->Python callback/script returned the following error<--

                                                    - NOTE: IGNORING THIS CAN LEAD TO MEMORY LEAKS!

                                                   Error Type: <class 'UnboundLocalError'>

                                                   Error Contents: local variable 'curpf' referenced before assignment

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 42, in displayMenu

                                                       curpf = kvfile.execute('SELECT count (c00) FROM movie',)

                                                   sqlite3.OperationalError: no such table: movie

                                                   

                                                   During handling of the above exception, another exception occurred:

                                                   

                                                   Traceback (most recent call last):

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py", line 2, in <module>

                                                       from resources.lib import menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 118, in <module>

                                                       displayMenu()                                                   #  Display main menu

                                                     File "C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\resources\lib\menu.py", line 80, in displayMenu

                                                       del curpf

                                                   UnboundLocalError: local variable 'curpf' referenced before assignment

                                                   -->End of Python script error report<--

                                                   

2022-10-15 14:03:46.198 T:552     DEBUG <general>: CPythonInvoker::onExecutionDone(31, C:\Kodi_N\portable_data\addons\plugin.program.kcsleaner\default.py)

2022-10-15 14:03:46.210 T:12532   DEBUG <general>: ------ Window Init (DialogNotification.xml) ------

2022-10-15 14:03:46.590 T:552     DEBUG <general>: Python interpreter stopped

2022-10-15 14:03:46.590 T:552     DEBUG <general>: Thread LanguageInvoker 552 terminating

[HeresJohnny]

Can you try the actor_link table ? That one is more interesting because it compares to 6 other tables vs. 1 for actors. I'll finish the remaining tables once I fix some formatting issues.

Kodi19 result: No unmatched records found for actor_link

[jbinkley60]

Can you try the actor_link table ? That one is more interesting because it compares to 6 other tables vs. 1 for actors. I'll finish the remaining tables once I fix some formatting issues.

Kodi19 result: No unmatched records found for actor_link

That is good news.


Thanks,

Jeff

[jbinkley60]

Nightly 2022-09-27, VideoDB 121, MySQL. Here's an excerpt from a debug log, not much difference. I'l install the latest nightly and see if that changes anything.

I am not a MySQL user but this file name isn't following the standard Kodi DB naming standard.  The addon currently looks for MyVideos121.db vs. VideoDB121 .  Can you post the directory listing for your Database folder from your v20 client ?  I presume with MySQL that syncing is occurring across the clients so if one client is good the others should be unless there is a problem with the sync process.  With the next version 0.0.6 I'll add a check for the Kodi database files to avoid the error if they aren't found.. If MySQL syncs to the existing Kodi database names vs. creating new ones, then I will likely not add anything else.  If MySQL creates new database names then I may either see if there is a JSON RPC call to gt the video file name, maybe add settings options so you can specify your Video, Music and textures databases or a MySQL option which maps to the MySQL database names.


Thanks,

Jeff