[jbinkley60]

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.

I was looking closer the database analyzer and thinking about this comment.  The analyzer actually finds both orphaned and unmatched database records.  An intelligent clean function could have some real value.  For instance with the media tables (i.e. episode, movie and music video) if the underlying matching idFile entry doesn't exit then it the record is orphaned and can be cleaned.  The user would likely have never been able to play these media entries. 

But, to earlier your point, if there is an unmatched rating, season, show value etc.  then these are integrity issues.  Likewise most unmatched link table entries will be orphans.  I think cleaning this type of stuff up would add some value and would be easy to write.  The the user would simply be left with unmatched values.  I think I'll add this intelligent cleaning function into the analyzer clean menu option.  The file / media analyzer cleaning you describe will be a separate file analyzer menu option.  I've finished adding the GUI display column headers.  It is much easier to read now.


Thanks,

Jeff

[jbinkley60]

version 0.0.8 alpha has been posted to GitHub. 

This release has a significant number of enhancements:

• Added column header information to video data analyzer output
• Modified database backup file names to include both date and time
• Added 3 more tables to the video DB analyzer genre_id, seasons and tag_link
• CSV export now working for all video DB analyzer tables
• Added clean indicator to both video DB analyzer display and CSV outputs

In addition to adding three more tables to the video database analyzer, I have added a GUI and CSV indicator now on which records will ultimately be cleaned by the forthcoming cleaner function.  The CSV function is now fully operational and I have updated the Wiki page to show which tables are analyzed during the analysis and which unmatched records can be cleaned.  

I've added more content to the Wiki page and will continue adding as development progresses.  The next phase of development is to complete the video database analyzer clean function which can remove unmatched orphaned records.

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.   Please pay close attention to the clean indicators vs not.  The clan indicators are blue text in display mode and Yes in the clean column in the CSV exports.


Feedback welcome,

Jeff

[jbinkley60]

version 0.0.9 alpha has been posted to GitHub. 

This release now includes the cleaner function for the video analyzer:

• Added Kodi video DB analyzer clean function to clean orphaned data

In addition to a Kodi video database analyzer this Kodi Selective Cleaner addon now includes an intelligent cleaner function.   The cleaner first runs the analyzer and then takes the output and will remove orphaned data records while leaving unmatched data.  You can click here on the Wiki page to see more.

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 and cleaner against your video database tables, as well as testing out the database backup utility.  Note please make a backup copy of your database before running the cleaner.  I've done extensive testing but cannot account for every possible Kodi situation.  You might be surprised what you find.   Please pay close attention to the clean indicators vs not.  The clean indicators are blue text in display mode and Yes in the clean column in the CSV exports.


Feedback welcome,

Jeff

[Klojum]

Feedback welcome

I'm trying to keep up... Testing properly takes time as well

[jbinkley60]

Feedback welcome

I'm trying to keep up... Testing properly takes time as well

No rush.  I am stopping for a bit.  I wanted to get the Video analyzer and cleaner done.  I am still thinking about the file / media  existence checker vs. moving over to repeat the video analyzer / cleaner functionality for the music database.  I've tried to document most of the working functionality on the Wiki page. 


Thanks,

Jeff

[HeresJohnny]

I'll only be able to chime in if/when MySQL support will be added. Until then keep your enthusiasm!

[jbinkley60]

I'll only be able to chime in if/when MySQL support will be added. Until then keep your enthusiasm!

Thanks for the interest.  It's potentially on the list.  I want to get a few other things done then I'll think about it.   


Jeff

[gibxxi]

With regards database naming for the media library (SQLite or MySQL) It might be an idea to check if the user has an advancedsettings.xml file with database names set-up there. I for one use customized DB names (I'm only adding a single character, but it's a change, nonetheless).

Your addon could then use the values as specified within the advancedsettings.xml to base all it's subsequent work on. This may not seem like a big issue now, but somewhere down the line it will rear it's head as an issue for someone, and if catered for in advance, save you from having to problem solve it / explain how the script works to an end user who doesn't realise why the script can't see or work on his/her databases.

Just a thought.



Dan / Gib.

[jbinkley60]

With regards database naming for the media library (SQLite or MySQL) It might be an idea to check if the user has an advancedsettings.xml file with database names set-up there. I for one use customized DB names (I'm only adding a single character, but it's a change, nonetheless).

Your addon could then use the values as specified within the advancedsettings.xml to base all it's subsequent work on. This may not seem like a big issue now, but somewhere down the line it will rear it's head as an issue for someone, and if catered for in advance, save you from having to problem solve it / explain how the script works to an end user who doesn't realise why the script can't see or work on his/her databases.

Just a thought.

Thanks for the suggestion.  I'll look at that as a future option.  It shouldn't be too difficult to implement.


Jeff

[HeresJohnny]

I usually don't like to nag people how they spend their free time but is there a chance you'll extend this add-on to clean a central SQL database?

[jbinkley60]

I usually don't like to nag people how they spend their free time but is there a chance you'll extend this add-on to clean a central SQL database?

Thanks for reaching back out.  This has been on my To-Do list to investigate and get a MySQL environment setup.  I took some vacation finally and have been busy with a number of updates for my Mezzmo addon.  I've got one more update to push out tomorrow.  Then I'll look to get a couple of new LibreElec VMs built and MySQL setup.  Hopefully in the next couple of weeks.  From there I'll look at the best way to integrate with MySQL. 

I've also considered a slightly different approach where I would duplicate all the Kodi tables locally in an SQLite database via JSON RPC queries against Kodi. Then I could run the same analysis queries as I have now and use JSON RPC.  That would leave Kodi to handle the underlying database connectivity, regardless of local, MySQL or MariaDB.  I don't like working with JSON RPC because I find it clunky and slow but that's just me.  


Jeff

[HeresJohnny]

Sounds like you got a full plate, so thanks for wanting to pursue this further!. I'm curious about your unifying SQLite and SQL project... I'm no programmer but I have gleaned from other projects that there are some slight differences in syntax which might make such an approach challenging.

BTW, there's a very interesting api for async background processing, see an example at https://forum.kodi.tv/showthread.php?tid=368326. Unfortunately, the underlying code has a bug concerning multithreading at the moment but when this will be resolved, Kodi could potentially run many tasks in parallel.

[jbinkley60]

Sounds like you got a full plate, so thanks for wanting to pursue this further!. I'm curious about your unifying SQLite and SQL project... I'm no programmer but I have gleaned from other projects that there are some slight differences in syntax which might make such an approach challenging.

BTW, there's a very interesting api for async background processing, see an example at https://forum.kodi.tv/showthread.php?tid=368326. Unfortunately, the underlying code has a bug concerning multithreading at the moment but when this will be resolved, Kodi could potentially run many tasks in parallel.

I've only looked a little at the differences, which I believe are in the date formatting for those data types.  In the case of Kodi Selective Cleaner that shouldn't be an issue because I am primarily looking for NULL / missing data vs. doing an exact value match on the all fields.  In the cases where I am matching, they are string fields which should match across SQLite and MySQL.  But it will be interesting to see.  I don't need to do any threading on this.  It runs so fast that performance should be an issue.  Properly written SQL queries and with proper indexing takes care of that.


Jeff

[jbinkley60]

With regards database naming for the media library (SQLite or MySQL) It might be an idea to check if the user has an advancedsettings.xml file with database names set-up there. I for one use customized DB names (I'm only adding a single character, but it's a change, nonetheless).

Your addon could then use the values as specified within the advancedsettings.xml to base all it's subsequent work on. This may not seem like a big issue now, but somewhere down the line it will rear it's head as an issue for someone, and if catered for in advance, save you from having to problem solve it / explain how the script works to an end user who doesn't realise why the script can't see or work on his/her databases.

Just a thought.



Dan / Gib.

@HeresJohnny 

I've begun  researching support for MySQL in Kodi Selective cleaner.  I've gotten the MySQL library loading now, which will allow connectivity to MySQL databases from the addon.  I think of parsing the advancedsettings.xml file to grab the database connection information (i.e. server, database, login info etc..).  What I am also thinking of doing is adding a setting in the addon to allow selecting local or MySQL databases.  If the user selects MySQL a popup dialog box would appear, when they start the addon, to allow them to select which database to run Kodi Selective Cleaner against (if there is more than one defined for each type i.e. video, music etc..).

Any thoughts ?  If a couple of folks interested in MySQL support would be willing to share their advancedsettings.xml file via a private message, that would help me ensure the parsing code works properly.  Since I run Mezzmo for sharing I don't have any needs for an advancedsettings.xml file so I'd be guessing what some real ones look like.


Thanks,

Jeff

[HeresJohnny]

Hi Jeff,

thanks for tackling this! I'm not sure there's a use case for local MySQL but the docker users might prove me wrong. In any case, the advancedsettings should be similar for local or LAN/remote. In my case it looks like

xml:
<advancedsettings>

	<videodatabase>

		<type>mysql</type>

		<host>192.168.1.185</host>

		<port>3306</port>

		<user>root</user>

		<pass>XXX</pass>

		<compression>true</compression>

	</videodatabase>

	<musicdatabase>

		<type>mysql</type>

		<host>192.168.1.185</host>

		<port>3306</port>

		<user>root</user>

		<pass>XXX</pass>

		<compression>true</compression>

	</musicdatabase>

</advancedsettings>

Parsing the settings from advancedsettings would certainly be elegant but I'm not sure if that is desirable by admins (data protection issue). I'm leaning towards a dedicated config setting, like the WatchedList add-on requires.