[HeresJohnny]

I Had only stated the version not a literal name. MYSQL is not file based anyway.

[jbinkley60]

I Had only stated the version not a literal name. MYSQL is not file based anyway.

Ok.  The addon won't work with MySQL at this time since it looks at normal Kodi file based databases.  I'll look at this as a future option but for centralized sharing systems I would expect it to provide the integrity checking, like the sharing solution I use does.  There is a Python connector for MySQL but it has a lot of variations depending upon the versions of MySQL and Python.  I appreciate the testing and I am adding a check for missing file databases.  One thing I am curious about is if you are running MySQL across both of your Kodi 19 and 20 setups ?  I ask because the MySQL Wiki page says it isn't supported.  I am wondering if that is something leftover from the past or still in effect.  If so, it seems a bit limiting.




Thanks,

Jeff

[HeresJohnny]

One thing I am curious about is if you are running MySQL across both of your Kodi 19 and 20 setups ? 

Sure, that has never been a problem. Kodi creates db versions as needed with the correct name and accesses the correct version as needed depending on the client version. In my case the VideoDB versions are 119 for Kodi19 (Windows client) and 121 for Kodi20 (Windows and CoreELEC clients).

I see now that it only worked in my Kodi 19 because empty SQlite databases existed there locally. So my test with actor tables was really inconclusive. For a possible setup to switch write access between SQLITE and MYSQL see default.py in this cleaner add-on.

[Klojum]

Uhm.. I'm mostly AFK this weekend so testing the add-on hasn't really started yet. But regarding MySQL support: Kodi's default databases for MySQL/MariaDB is MyVideosXXX and MyMusicXX. Users can also specify their own video and music database names via the advancedsettings.xml. However, Kodi will always add the current version sequence number to that user-selected database name. E.g., "myfirstvideodatabase" will become "myfirstvideodatabase119" for Kodi 19.

I'll also find out soon enough if the add-on is profile-friendly. That will probably be fine.

[jbinkley60]

Uhm.. I'm mostly AFK this weekend so testing the add-on hasn't really started yet. But regarding MySQL support: Kodi's default databases for MySQL/MariaDB is MyVideosXXX and MyMusicXX. Users can also specify their own video and music database names via the advancedsettings.xml. However, Kodi will always add the current version sequence number to that user-selected database name. E.g., "myfirstvideodatabase" will become "myfirstvideodatabase119" for Kodi 19.

I'll also find out soon enough if the add-on is profile-friendly. That will probably be fine.

Thanks.  Profiles shouldn't be a problem.   With regards to the MySQL databases they still require a MySQL interface vs. SQLite, correct, or is the MySQL interface just used for synchronization  ?  I will likely post v0.0.6 tomorrow which has fix for the analyzer GUI output formatting,  will detect missing databases and I might sneak in a few more tables.  If I find a bunch of folks using nonstandard database naming for normal Kodi databases I may add options for users to select their database video, music and textures DB files.  I don't think there is an RPC call for this but will look.


Jeff

[Klojum]

If I find a bunch of folks using nonstandard database naming for normal Kodi databases I may add options for users to select their database video, music and textures DB files.

User selected database names only apply to MySQL/MariaDB connections. Links/names to any of the local databases cannot be changed.

[jbinkley60]

version 0.0.6 alpha has been posted to GitHub. 

This release does the following:

• Fixed data analyzer output formatting for easier reading
• Added detection if the video or music databases cannot be found
• Added art table to the video database analyzer

The first three tables in the selector, actor, actor_link and art are all working where it will find unmatched data against the episode, movie, tvshow, musicvideo, sets 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. 


I've started a Wiki page  for the Kodi Selective Cleaner.  I've added ore content and will continue to as development progresses.

I'd appreciate if some folks could download the addon and run the Video DB analyzer against your actor, actor_link and art 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

[jbinkley60]

Sure, that has never been a problem. Kodi creates db versions as needed with the correct name and accesses the correct version as needed depending on the client version. In my case the VideoDB versions are 119 for Kodi19 (Windows client) and 121 for Kodi20 (Windows and CoreELEC clients).

I see now that it only worked in my Kodi 19 because empty SQlite databases existed there locally. So my test with actor tables was really inconclusive. For a possible setup to switch write access between SQLITE and MYSQL see default.py in this cleaner add-on.

I am glad to hear MySQL is working across Kodi versions for you but I suspect there are some limits, hence the comments on the MySQL Wiki page and that you can't directly sync database elements if the source doesn't exist in the destination (at least not simply with SQL sync software).     Kodi may handle this internally, as I do with my syncing addon.  A simple example is in Kodi 19 the path table has a new column called allAudio.  That column doesn't exist in Kodi 18. 

Thanks for the addon link.  I've taken a look at the code and see the differences in the MySQL connect methods and SQLite3.  I think what I will do in the future is disable my current sync method on 3 Kodi VMs I use for testing (v18, v19 and v20), install MySQL and do some testing.  Restoring back will be as easy as going back to the backup VM copy or blowing away the Kodi MySQL setup and reenabling my current sync processes.  This may ultimately depend upon usage of this addon.


Thanks,

Jeff

[jbinkley60]

version 0.0.7 alpha has been posted to GitHub. 

This release does the following:

• Continued improvements with data analyzer output formatting
• All tables now available in video database analyzer
• Improved checking for NULL fields

In addition to completing the remaining video database tables to the analyzer, I have created a wiki page which details which tables are compared against when you run the analyzer.   For those interested in how the analyzer works, you may find this interesting   The CSV export and Clean Table options are not working yet but GUI display will begin to demonstrate the capabilities of the addon.  To use the video database analyzer click here for instructions.  I have a little more cleanup to do on analyzer output, mainly adding column headings for the output.  I've noticed that I didn't include a few tables in the initial analyzer list like: genre_link, tag_link and seasons.  I will probably add them in the next release.

I've added more content to the Wiki page and will continue adding as development progresses.

I'd appreciate if some folks could download the addon and run the Video DB analyzer against your video database 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.   Using this addon I actually found a very obscure temporary synchronization bug in my sync addon. 


Feedback welcome,

Jeff

[Klojum]

The backup of a locale SQLite video database only get the time part (hhmmss) of a datetime stamp, and no date... e.g. 141926_MyVideos119
The two "Unknown" seasons of a TV show are probably the "-1" season for the TV show root path, and the "0" season for any specials.

"Episode table ratings unmatched" for a small number of episodes...
"Files table streamdetails unmatched" for a bigger number of files...
Not sure yet what these mean.

[jbinkley60]

The backup of a locale SQLite video database only get the time part (hhmmss) of a datetime stamp, and no date... e.g. 141926_MyVideos119
The two "Unknown" seasons of a TV show are probably the "-1" season for the TV show root path, and the "0" season for any specials.

"Episode table ratings unmatched" for a small number of episodes...
"Files table streamdetails unmatched" for a bigger number of files...
Not sure yet what these mean.

Correct, no date stamp on the backups.  I figured the actual file date stamp would be sufficient.  It is easy enough to add the date into the backup file name.  Also correct that a season value of 0 will show up as unknown.

Episode table ratings unmatched" for a small number of episodes   -  This means that you have episodes with a rating value that does not match any values in the ratings table.  The first value is the idEpisode value and he second he idFile value from the offending entry in the episode table.  The field with the issue is c03, the rating value.

Files table streamdetails unmatched means you have entries in the files table without a corresponding streamdetails entry.   if there is an entry for both audio and video then it means there are no streamdetails information.  if it just says audio but not video then there is no audio entry.  It is possible some video files may not have sound and thus no matching audio entry.

Adding the column headings will help, as will the CSV export which will have the column headings.  I will try to get this done in the next release.  Thanks for testing.  Hopefully this will be useful for folks and I can extend it into the music database, if there is value.


Jeff

[Klojum]

Episodes not having ratings, and, files not having streamdetails, both I technically do not see as orphaned records. I doubt if anyone has a video database/collection that has a fully linked set of records. I have plenty of TV episodes yet to be watched in my collection, so getting that fully linked database to me is like a Utopia.

Orphaned records to me are those database entries to which no actual files are present on a video or music source. Analyzing certain tables on their own (e.g. actor_links) is kinda besides the point. You can see in my "cleanup tool script", that upto 18 tables can be linked to a single TV episode. I would check for the presence of the physical video (or music) file, and if non-existent, remove all related data from any related tables as well as the Textures13.db and its thumbnail(s). Having to examine tables individually as now seems to be the case is too technical for the average HTPC user and too tedious and approaching the problem from the wrong angle IMO.

The database should reflect to the contents that is available via the source(s). Anything that is no longer there on disk or wherever should be cleansed 'in an easy manner' from the database.

[jbinkley60]

Orphaned records to me are those database entries to which no actual files are present on a video or music source. Analyzing certain tables on their own (e.g. actor_links) is kinda besides the point. You can see in my "cleanup tool script", that upto 18 tables can be linked to a single TV episode. I would check for the presence of the physical video (or music) file, and if non-existent, remove all related data from any related tables as well as the Textures13.db and its thumbnail(s). Having to examine tables individually as now seems to be the case is too technical for the average HTPC user and too tedious and approaching the problem from the wrong angle IMO.

The database should reflect to the contents that is available via the source(s). Anything that is no longer there on disk or wherever should be cleansed 'in an easy manner' from the database.

Your testing and feedback are very welcome.  I am kind of trending this towards a multipurpose tool which can have multiple uses:

Database visualization - CSV export
Database integrity - Database table analyzer
File / media integrity - What you are describing above

There are times when integrity issues causes users problems but to your point maybe not as often as file / media integrity.  I think the database integrity can be useful for certain types of user issues like unexpected smart playlist results and metadata questions.  I see folks with those questions in the forums.  I think I'll finish up the analyzer module by adding the column heading to the GUI display and enabling the CSV export so folks can see unmatched data results.  The full table CSV export function already exists off the main menu.  

For the file analyzer that is pretty straightforward once we decide on the starting point.  Based upon your comments it can either be directly with the files table or start with the 3 media tables (i.e. episode, movie and musicvideo) and then see if the underlying files exist.  I presume you would like to start with the 3 media tables ?  One question with the textures table is whether there is interest in coming the other direction (i.e. orphaned texture records and underlying files in the textures table ?   That could get quite complex since other addons can cause texture files.  


Thanks,

Jeff

[HeresJohnny]

One question with the textures table is whether there is interest in coming the other direction (i.e. orphaned texture records and underlying files in the textures table ?   That could get quite complex since other addons can cause texture files.  

You seem to be wanting to implement some functionality that exists in another add-on, KCleaner. Maybe check their code if you can use anything, see HERE. While that add-on works in its current state the author doesn't seem to be active anymore and it's always best to support active authors.

[jbinkley60]

One question with the textures table is whether there is interest in coming the other direction (i.e. orphaned texture records and underlying files in the textures table ?   That could get quite complex since other addons can cause texture files.  

You seem to be wanting to implement some functionality that exists in another add-on, KCleaner. Maybe check their code if you can use anything, see HERE. While that add-on works in its current state the author doesn't seem to be active anymore and it's always best to support active authors.

Thanks.  Much appreciated.  My focus really have been on the Kodi databases and their integrity.  There could be a byproduct of some filsystem cleanup including the textures database / underlying files.  There definitely seems like some of the code you provided is reusable. 


Jeff


.