[GhoudiOnPi]

@jbinkley60 First of all: Thank you very much for your great tool! It does exactly what I was looking for.

Would you mind to think about the following idea until your planned feature "Removal of all associated data with selected deletions (i.e. artwork, actors, directors, textures cache etc..)" becomes available:
+ Export a CSV with all needed picture filenames from the database
or even better
+ Export a CSV with all no more needed picture filenames found in the local (thumbnail) directories with no link to the database anymore

Background:
Yesterday I had to start from scratch with a new MyVideo121.db. That took more then 6 hours to read the metadata for all the movies and tv shows.
Unfortunately, I forgot to delete all the "associated data" beforehand.
Now, I have thousands of files from older (no more existing) movies, actors, fan art, posters and I have no idea how I could identify them.

With a CSV it would not be too hard to expand it with some Windows CMD line DEL in front of the path (or adjust the smb:// part with \\libreelec or a mapped drived letter, like K:\)

[jbinkley60]

@jbinkley60 First of all: Thank you very much for your great tool! It does exactly what I was looking for.

Would you mind to think about the following idea until your planned feature "Removal of all associated data with selected deletions (i.e. artwork, actors, directors, textures cache etc..)" becomes available:
+ Export a CSV with all needed picture filenames from the database
or even better
+ Export a CSV with all no more needed picture filenames found in the local (thumbnail) directories with no link to the database anymore

Background:
Yesterday I had to start from scratch with a new MyVideo121.db. That took more then 6 hours to read the metadata for all the movies and tv shows.
Unfortunately, I forgot to delete all the "associated data" beforehand.
Now, I have thousands of files from older (no more existing) movies, actors, fan art, posters and I have no idea how I could identify them.

With a CSV it would not be too hard to expand it with some Windows CMD line DEL in front of the path (or adjust the smb:// part with \\libreelec or a mapped drived letter, like K:\)

Some of this is already covered by exiting addon functionality and some is not.  What is covered from your list:

Deletion of actors, directors and artwork table entries

What isn't covered:

The artwork files themselves
Texture cache and thumbnails

For the texture cache and thumbnails you can just delete both and Kodi will rebuild them as you fetch them.  There is also a textures cleaner addon.  Having said this, you are not the first to ask for this so I will consider it.

For the artwork and such, this becomes a bit more challenging.  First off, I am not a Kodi local library tools / scraper user.  With my Mezzmo solution my video database is rebuilt daily and artwork is hosted centrally so be patient if I ask naive questions here.  I don't use the Kodi Selective Cleaner tool much but provide it to help folks out to keep their databases cleaner.

The questions I have about artwork files is how to identify them.  In your scenario you deleted everything and rebuilt it.  So I have no old data to go off of in the current database unless you rebuilt the same paths in the new video database.  In that scenario I think I could look at the path table entries, find all associated artwork entries in the art table and then look at the folder pointed to by the path entry to see what additional files are there.  So a possibility is to give a list of path table entries and let the user select which ones to analyze in this method (find files in the paths with no associated art table entry).  However, if the new path table entries are different from the old ones then this will not work and we'd be looking to having the user type in a folder path to analyze but since the path doesn't exist anyway going forward, anything in that folder should likely be deleted regardless.

So if we assume the old / new paths are the same help me understand what types of files should I be looking for that don't belong (i.e. jpg files, NFO etc..) ?  What is unclear to me is the typical Kodi folder structures folks use and what files should and shouldn't be in them. My solution only has media files and nothing else.  If you can help me with this then I am sure I can code some logic to potentially do this.  Likewise I believe some folks use an extras addon or similar structure which adds additional files and such.

I hope this makes sense.  Basically help me understand the logic you would go through manually to know a specific file or set of files don't belong and should be deleted.


Thanks,

Jeff

[mrob]

I would like to clean up my movie database without losing the indication of which films I've already watched.

Tried out the add-on Kodi Selective Cleaner, but actually I'm not sure how to proceed with it.

Analyzing the database, I get 12 pages of (I guess) orphaned rows.

I run "analyze / clean DB" for the movies table, and the Cleaner very quickly reports "successfully cleaned: movie - 0 records".

I then tried "analyze" again, and the unmatched data analysis reports "Clean count: 0 Data integrity check: 147" and it still gives me a list of 12 pages of (I guess) rows from the database that are (I guess, again) orphans.

The author of the add-on has helped to explain some of this, but it seems like some further steps are required.

Any suggestions?

[Karellen]

@mrob

Best to ask your question the correct thread so the developer can see it.
I have merged your post.

[GhoudiOnPi]

I then tried "analyze" again, and the unmatched data analysis reports "Clean count: 0 Data integrity check: 147" and it still gives me a list of 12 pages of (I guess) rows from the database that are (I guess, again) orphans.

I'm not the author of this addon. 
If you have a look at the pictures in
https://github.com/jbinkley60/plugin.pro...a-analyzer
you see, that only if the 
Clean count: 
is not zero, then there is something to clean for KSC.
The second value is only for your information. Nothing will be changed or deleted with the integrity number.

Official explanation:

Note that items which have a blue highlight are orphaned data records and can be cleaned by the clean function. Items not in blue are simple unmatched data integrity items which should be fixed but are unlikely to cause a problem with normal Kodi operations.

[GhoudiOnPi]

So if we assume the old / new paths are the same help me understand what types of files should I be looking for that don't belong (i.e. jpg files, NFO etc..) ?  What is unclear to me is the typical Kodi folder structures folks use and what files should and shouldn't be in them. My solution only has media files and nothing else.  If you can help me with this then I am sure I can code some logic to potentially do this.  Likewise I believe some folks use an extras addon or similar structure which adds additional files and such.

Today I started to search for answers to your questions.
In a first step I exported the entire MyVideos121.db as an MyVides121.sql text file.
Then I tried to find one of my recent thumbnail file names like f386ebbb.jpg in this textfile by a simple text search.

Guess what: I could not find a single matching entry. Not even for pictures matching movies or tv shows just added.

Then I searched for the tv show- and movie names recently added.
They all look like this:
INSERT INTO "movie" ("idMovie","idFile","c00","c01","c02","c03","c04","c05","c06","c07","c08","c09","c10","c11","c12","c13","c14","c15","c16","c17","c18","c19","c20","c21","c22","c23","idSet","userrating","premiered") VALUES (1,1,'20th Century Girl','Im Auftrag ihrer hoffnungslos verliebten Freundin, beobachtet ein M„dchen im Jugendalter heimlich einen Jungen ihrer Schule im Jahr 1999. Daraufhin wird sie allerdings selbst in ihre eigene Liebesgeschichte hineingezogen.','','A first love pov story.',NULL,'1','Bang Woo-ri',NULL,'<thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/mTS9D9BDpwNvaBOGNwusSU9ePZs.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/od22ftNnyag0TTxcnJhlsu3aLoU.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/qhWPZdEEyht9Vqpj0vl3FB6CSr0.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/j73DItU1L0N7nmGA9S6sjwxKzPM.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/4G0GIdrgEeaYCTv7Ksvt3cgRuiK.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/ovxMAMyBlbevglS45Fl67C4u7un.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/s8cNx1MKoL17has4O4avHlt6CMn.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/oiF7fzjwmRDmRQWKSbTvqOkh1wR.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/uFTYeoqjILiKdJoyxPyTYzbJF4D.jpg</thumb><thumb spoof="" cache="" aspect="poster" preview="">https://image.tmdb.org/t/p/original/re42isAXjME92ngP0G9kdRvRyuY.jpg</thumb><thumb spoof="" cache="" aspect="clearlogo" preview="">https://image.tmdb.org/t/p/original/cT5t0754dRzgxbOOcG3q4Tk4yRG.png</thumb><thumb spoof="" cache="" aspect="clearlogo" preview="">https://image.tmdb.org/t/p/original/qW3wh5dvCvY7bcogZZQpz82xTWZ.png</thumb>','2','','7260','Rated 12','0','Liebesfilm / Drama','Bang Woo-ri','20?? ??',NULL,'Yong Film','','<fanart />','South Korea','smb://MyNASName/Filme/20th Century Girl 2022 GERMAN DL/','1',NULL,NULL,'2022-10-06');

As you can see, the pictures seems all to be fetched from an online source, not from the local thumbnail folder.
I could not find a single entry with something like
smb://*.jpg

This leads me to the insight to your advise:

For the texture cache and thumbnails you can just delete both and Kodi will rebuild them as you fetch them. 

As soon I have tried it, I will add my findings here in this thread.

[jbinkley60]

I would like to clean up my movie database without losing the indication of which films I've already watched.

Tried out the add-on Kodi Selective Cleaner, but actually I'm not sure how to proceed with it.

Analyzing the database, I get 12 pages of (I guess) orphaned rows.

I run "analyze / clean DB" for the movies table, and the Cleaner very quickly reports "successfully cleaned: movie - 0 records".

I then tried "analyze" again, and the unmatched data analysis reports "Clean count: 0 Data integrity check: 147" and it still gives me a list of 12 pages of (I guess) rows from the database that are (I guess, again) orphans.

Good question.  The response above points to the right answer but I can see where the tool doesn't provide the entire explanation.  This chart on the Wiki explains the checks and which ones are flagged for fixing by the tool and which ones are not.   You can see with the movie table there are 5 checks done against these tables:  files, rating, path, uniqueid, and sets.  four of them will create unmatched entries and only files will be cleaned since it would cause a Kodi playback problem by trying to play a movie with no file table entry (i.e. you'd be telling Kodi to play a file which doesn't exist.

The other 4 are data mismatch type errors which the tool doesn't try to fix right now.  In this example the movie with a title of Beast, which has movieid 3 and fileid 21 has an entry in the movie table for ratings (fyi, this is column c05 in the movie table) that does not exist in the rating table.  It isn't going to cause a problem but could be incorrect data.  The tool really can't tell what is wrong (i.e. the value in c05 is incorrect or the rating entry is missing in the rating table).  I have considered adding a deep cleaning capability which, in this case, would remove the c05 entry from the movie but that would then just leave you without a rating value in the movie. You can see the incorrect values in CSV export.  Here's a link to the Kodi MyVideos Video Database schema for the movie table.

The best fix for this, if you want to fix it, is to delete the movie and rescrape it to your library.  Unfortunately that would cause a loss of the play count values.  You would need to use the Kodi Mark as Watched function.

I hope this helps some.  As I get more feedback I'll try updating the Wiki more.


Thanks,

Jeff

[jbinkley60]

This leads me to the insight to your advise:

For the texture cache and thumbnails you can just delete both and Kodi will rebuild them as you fetch them. 

As soon I have tried it, I will add my findings here in this thread.

I should be clear here.  I am referring to static artwork for media files.  If you are running preview artwork for chapters in your media I am not sure on this.  Someone else more knowledgeable in this area will need to opine.


Thanks,

Jeff

[GhoudiOnPi]

I just deleted the entire content (and folders) in the \Thumbnails subfolder of \Userdata. About 49'000 *.jpg pictures.
(Of course after I've created a backup of it).

Then I restarted KODI and checked my Movie and TV Shows.
The pictures get fetched as you predicted @jbinkley60.
First as dithered blurry version and after some seconds as clear version.

Then I checked my previously cleared \Thumbnails subfolder again.
Currently I see 547 pictures in it (this is approx the amount of Movies and TV Shows in my current collection).

So I can confirm:
If someone is using the same default metadata scraper then I (tmdb.org) the pictures for Movies and TV Shows will be re-fetched from the online source.
Also the TV Shows season poster and the preview of the episode will be re-fetched (or rebuilded from the episode files?).
Also the actors profile photo will be re-fetched for Movies and TV Shows.

In this case, there is NO NEED to create an additional check in your KSC tool. Just delete the entire \Thumbnails folder and let KODI recreate it.

To be clear:
If someone is using another metadata scaper then tmdb.org, I can not guarantee that the thumbnails will behave in the same way!
I cannot rule out the possibility that other metadata scrapers store a local copy only once.

Small update:
I have just learned how the caching procedure in KODI works:
https://kodi.wiki/view/Artwork/Cache

[mrob]

Thanks for explaining more.

The best fix for this, if you want to fix it, is to delete the movie and rescrape it to your library.

Do you mean delete the movie using Kodi? Or, somehow delete the row from the database?

I don't follow, sorry.

Two points...

(1) There are hundreds of lines in the CSV file. It's too much work to delete all of these from Kodi by hand.

I thought(?) this is what the database cleaner is supposed to help with. Is there some way it can just delete all of these rows, and then I update normally, so that Kodi will re-scan all of the missing files and get the database in sync with what's on the disc (I'm just guessing here).

(2) If I output the movie table to CSV, I'm seeing many rows for which the title in "c00" doesn't correspond to any file.

E.g., let's say there's a line in the CSV file where "c00" is "The Life of Dr. Foobar". If I try $ find /media -name "*The Life of Dr. Foobar*" in a shell, nothing is found. IIUC, this is an orphaned row in the Kodi database that doesn't point to a file that exists.

Can the Selective Cleaner help me to clean all the rows like this?

I think I'm confused about a really basic point, which is that if I delete a movie folder from one of my sources, then update the Kodi database and then clean it, why can't Kodi just figure out that the movie is gone and delete the orphaned row?

I assume this is where Kodi Selective Cleaner enters the picture, but AFAICT it won't actually clean any of these orphaned rows in the movie table.

Probably I'm failing to grasp something here.

Thanks

[jbinkley60]

Do you mean delete the movie using Kodi? Or, somehow delete the row from the database?

Yes, I was referencing deleting in Kodi and the adding it back to your library but let's talk through this a bit more.
 

(1) There are hundreds of lines in the CSV file. It's too much work to delete all of these from Kodi by hand.

I thought(?) this is what the database cleaner is supposed to help with. Is there some way it can just delete all of these rows, and then I update normally, so that Kodi will re-scan all of the missing files and get the database in sync with what's on the disc (I'm just guessing here).

Understood on the scope of the issue.  Let's start with 2 definitions so we are talking similar terms.  The Kodi Selective Cleaner looks for two types of main issues:

1.  Orphaned records - These are situations where a record in one table points to a record in another table that doesn't exist which will impact functionality in Kodi.  This could be causing an item not to play, not to properly work in things like smart playlistes, filtering etc. 
2.  Integrity issues - These are data mismatches which are cosmetic and don't impact functionality.  They are incorrect pieces of data in your database but will not negatively impact Kodi operation.

The Kodi Selective Cleaner cleaning functionality addresses fixing #1 but only highlights #2 items because when two pieces of data are in a mismatch state, it really don't know which one is correct nor why.  These are all caused by some type of prior Kodi scraping issue but the root cause cannot be determined.  Hence deleting the item from the Kodi database and letting it be rescraped should be one option for a fix.  Also, I mentioned the concept of a deep cleaning option.  Mismatches don't mean records need deleted like orphaned records, they just mean there are data errors.  The errors could be deleted from the record (vs. deleting the record itself in #1) but then you'll have missing data (i.e. a movie with no genre etc..)
 

(2) If I output the movie table to CSV, I'm seeing many rows for which the title in "c00" doesn't correspond to any file.

E.g., let's say there's a line in the CSV file where "c00" is "The Life of Dr. Foobar". If I try $ find /media -name "*The Life of Dr. Foobar*" in a shell, nothing is found. IIUC, this is an orphaned row in the Kodi database that doesn't point to a file that exists.

Can the Selective Cleaner help me to clean all the rows like this?

c00 in the movie table is not the file name.  It is the title in your Kodi database so it may not match.  File names are in the file table.  The movie table analysis takes care of finding movies with no corresponding file table entries.  That would be an orphan so you are good there.  To use your example of "The Life of Dr. Foobar" look at the idFile column and see what the number is.  Then look in the file table for this entry and you will see the filename.  But again, the Kodi Selective Cleaner already has done this for you.

I think it would be good now to look at a few specific examples of integrity issues.  Can you post a screen shot of the first page of a movie table clean function where you have integrity issues ?  I want to see if they are mostly systemic (or possibly a Kodi Selective Cleaner bug) to a specific table (i.e. genre, uniqueid, movie sets etc..).  Then we can determine what the best steps are.


Thanks,

Jeff

[jbinkley60]

@mrob

I've posted a test release of the Kodi selective cleaner which should detect the version of  Python and put it in the logs.  Can you try this version start the addon and then click on View Logs -> Most Recent ? 

You should see a line like: Kodi Selective Cleaner Python version is: 3.8  or whatever version you are running.  

This should give us insight to the prior issues.

If this works I can use it to detect 3.6 and below versions to do file copy backups.


Thanks,

Jeff

[jbinkley60]

@mrob

I've gone ahead and posted a newer test release which now should do file copy database backups for Python 3.0 - 3.6 and SQL based database backups for Python 3.7+.  I've tested it and it is working for me.  Please try it when you get time.  In the logs you will see the Python version noted and the backup will either say normal or file.  Normal is the SQL based backups and file is for file based backups.  Assuming your Python version is detected as 3.6 you should see the file method.

I am pondering adding some additional information in the logs for integrity issues which would specifically state why there is an integrity issue.  I've been looking at the code and if i implement this feature, it wouldn't be available when you view the logs through the logs viewer but would be available if you do a CSV export.  The goal here would be not to pollute the log viewer screen to where it would be unmanageable.  I'd still like to see a screen shot or a CSV export of recent logs when you run the cleaner against your movie table.  This will help me see what is going on.


Thanks,

Jeff

[jbinkley60]

Personally, I don't see a use case for multiple choices of tables. I think users would rather go for a "clean all" approach without wanting to check which individual tables might actually need cleaning.

Just an FYI, this is moving up on the priority list.  I will try to have it out with version 1.4 or 1.5 of the software.  I'll create a test version for you to try, when I get close.  My goal is sometime in November.


Thanks,

Jeff

[jbinkley60]

Personally, I don't see a use case for multiple choices of tables. I think users would rather go for a "clean all" approach without wanting to check which individual tables might actually need cleaning.

I actually started working on this and it took far less time than I expected.  I have a test release here for you to try.  Note that this test version is just for functionality testing.  I still have some code cleanup to do and to add a progress dialog box so you can see what is happening (assuming you want one).  Also, you need to enable the Clean All Table feature in the settings.  It is disabled by default and won't appear in the menu until enabled.  When enabled it becomes the first menu option, above all of the individual tables, when you select Video DB Data Analyzer.

Feedback is welcome.


Thanks,

Jeff