2016-04-30, 20:46
Code:
UPDATE 14TH MAY 2021:
@kenmills has forked the Database Pre-Wash Scrub tool. This is now a Kodi Matrix 19+ compatible version only.
His github fork is located at https://github.com/klyco/script.database.cleaner
B3TA DISCLAIMER - This addon directly edits your database. It is a BETA release. We make a backup of the SQLite database. We also advise you to make your own backups too. MYSQL Users need to make your own backups. But you already have backups don't you?
This is a BETA so we are making assumptions on what can and cannot be removed so let us know if there is something you want to keep that we didn't think about. This is why this is a BETA TEST THREAD!!
We have tested in a selection of environments but now it is your turn. You are the mugs willing volunteers that will shake out the issues we hadn't thought of.
PROGRAM ADDON - VIDEO DATABASE CLEANER
script.database.cleaner
Created By: black_eagle and BatterPudding
What Is This Addon?
This addon will do a pre-wash clean-up of your KODI Video database removing references to old paths and files not listed in your KODI sources.
KODI's video library fills up over the years with a record of everything you have ever watched. A side effect of features in KODI means this data is never removed.
Every Internet and UPnP stream you watch, every YouTube video, each time File Manager is used to load up a video to watch once - records of these are all kept. (Yes - including that pr0n you have hidden on that network folder...)
A general assumption was that KODI's database only held the files scanned into the library. This is not the case. If you watch it, KODI remembers it.
This leads to a number of issues. One is privacy. There is nothing in the main KODI application that will clean out the database. Most other applications can clean their "recent files" lists, KODI can't. Yet.
KODI does have a built in "Clean Library" function, but this only works with the files scanned into the Media Library. It is not aware of the streams and one-off videos so it does nothing about them.
A second issue regularly rears its head where the old stream paths in the videos database cause numerous problems to other functions within KODI. Even KODI's own built in Clean Library functions spit dozens of errors into the debug logs when attempting to work with these old stream paths. For some people the Clean Library function could stall and crash due to these bad paths. (As well as the slightly worrying case that every one of these paths were being loaded up and interrogated causing some unusual network traffic)
Our script will do a pre-scrub of the database allowing KODI's own Clean Library function to do a far better job.
As a third point, running our add-on will also help make the KODI's database access more efficient. Getting rid of the old crud allows KODI to get the real data quicker. This will also help many other add-ons which don't realise this mess is kept.
The original thread that started the creation of this add-on
http://forum.kodi.tv/showthread.php?tid=269002
After the initial question from pr0xZen, an investigation kicked off between the forumites. Work has been done by black_eagle and BatterPudding to find a solution. 99% of the code is Black_eagle's work with SQL, Testing, Ideas, and generally trouble making from BatterPudding. (He also hosts the repo)
How Does It Work
Our addon aims to remove rtmp://, rtmpe://, http:// and plugin references from the files table. If it is run using the default settings, it builds an SQL query that excludes all the path sources defined in your sources.xml file and deletes everything else. This behaviour can be changed as some people don't have any sources defined and just use filemanager to navigate with. When the add-on finds an empty sources.xml it uses a default set of rules to select and delete the aforementioned paths.
When the addon has removed the required paths from the files table, the built in 'clean library' routine is called which scans the file table and removes any associated references in the rest of the tables to the links we have just deleted. This then clears up the Paths table and many other references.
The addon will prompt you before deleting anything by showing you a summary page of what paths are to be kept and confirmation of other settings. This prompt can be turned off if required. Users are encouraged to check that all their defined sources are indeed listed. Clicking on 'CLEAN' will execute the SQL clean-up, clicking on 'ABORT' will abort the script with no changes made to the database.
A backup is also kept of the database before every clean for SQLite users. VERY important in a beta test. MySQL Users need to do their own backups.
A list of remove files is stashed in KODI's temp folder (Linux: ~KODI\TEMP\database-cleaner.log, Windows: %APPDATA%\KODI\Cache\database-cleaner.log)
Users can also choose (via the add-on settings) to retain or remove old PVR information and to retain or remove bookmarks.
If Kodi's debugging is enabled, the script will write a few things in there. If the script's debugging is also enabled in the settings, it will be quite verbose as to what it is doing.
Settings
We have some optional settings to be configured.
Keep any PVR Information - tick this to keep all your PVR recordings in the database.
Keep any bookmarked files - tick this to keep bookmarks for videos not in the library. You may have bookmarked a YouTube video and this option will keep those details.
Automatically trigger clean library - strongly advise to always leave this ON. Our cleanup works best when worked in tandem with the main library clean up.
Backup local database before cleaning - strongly advice to keep backups. Especially during beta testing.
- Backup database name - ability to change the name of your backups.
Debug Settings
Prompt before actually deleting anything - will let you review the SQL statement that is about to be applied to your database. Good idea to check this as we are testing (Did I mention this is a BETA TEST!!)
Enable Debugging - dumps a pile of extra notes into your debug log to help us work out where things went wrong.
Sources
Uses sources.xml to determine files to keep - STRONGLY recommended. Otherwise the database clean will remove only rtmp:// rtmpe:// addon:// http:// references
Uses sources.xml on this machine - lets you supply a list of sources from a different location (needed for MySQL users, shared sources.xml, network locations, etc)
Path to remote sources.xml file - Supply a custom path to a different sources.xml to protect.
Advanced
Force Database Name - allows user to supply different name of a SQLite database file. The addon assume the "highest number" database is the current one. This may not be the case for people who upgraded and then downgraded again. This option lets you pick which database the addon will clean.
Note: MySQL users should back up their database using their favourite MySQL manager. If you don't have anything in place, phpMyAdmin can be used. (Discussions on how to backup an MySQL database are outside of this post really... if you don't know how to backup, then DON'T run our addon...)
The Exclusions
This add-on is pretty brutal in clearing out everything that is not listed as a source. But some people like to keep their You Tube viewing history. Or maybe they have an addon to keep a trailer with their movies. This Exclusions list is designed to handle those uses.
During testing we have found some people run KODI with a sources list that does not actually match their sources. For example - the database may be full of UNC paths from when the videos where initially scanned ( nfs://192.168.1.7/movies/ ) but the user has since changed their KODI source to mapped drive letters instead ( X: ).
To make the exclusions list, firstly the user has to decide where it goes. If this is a standard KODI install without profiles, then it goes in "KODI userdata/addon_data/script.database.cleaner". If profiles are in use then this excludes file goes in "KODI userdata/profiles/<profile_name>/addon_data/script.database.cleaner.
This means that when using profiles, different exclusions can be applied to the different databases. Note - there will already be a 'settings.xml' file in there and this one goes next to it.
The filename is "excludes.xml" and the contents are as follows :-
Code:
<excludes>
<exclude>plugin://plugin.video.youtube</exclude>
<exclude>plugin://plugin.video.iplayerwww</exclude>
<exclude>nfs://192.168.1.7/Movies</exclude>
<exclude>smb://192.168.1.7/一万年以后</exclude>
</excludes>Any number of <exclude></exclude> tags can be added and can be as precise or vague as is required by the user eg "plugin://plugin.video" would exclude all video plugin data from being deleted. "http://" would keep all the http info, whereas "http://my_favourite_streaming_site.com" would retain just the info for that site.
Database Backups
Even time a scrub is performed, we make a backup of the SQLite database. These are stored in a backups folder underneath the KODI database folder. These backups can be disabled in the settings but we STRONGLY suggest you leave the backup enabled.
In our addon's settings it is possible to change the name of the backups if you want to make a specific test. This backup name will have a date and timestamp attached and then copied to a sub folder. testclean1_2016-04-26_1345.db If no name is supplied then the backups use the standard name of the backup and attach a datestamp MyVideos99_2016-04-26_1345.db. When you first install the add-on, it defaults to no defined string in the name, so the add-on by default uses the SQLite db name.
MySQL users need to make your own backups. But as you have moved into the advanced world of MySQL I assume you already know all about backups.
How To Use
Okay... so you are ready. If you've decided you want to keep anything not listed in the library, then go write that excludes.xml file as explained above. (Yeah yeah... version 2.0 may make a GUI for this) Check the settings if you want to keep anything bookmarked or from the pvr.
Now just run the addon. You'll find it listed under Program Addons as Video Database Cleaner. When you click Run you'll be shown the confirmation page of what will be cleaned based on your settings.. Check it looks right and then hit OK. The clean will then happen, followed by KODI's own Clean Library function.
That's it. Done. Lots of waffle, but simple cleaning. Now KODI will stop trying to look at stuff long gone, normal library cleaning will be quicker, and your logs cleaner.
GIVE US FEEDBACK. What do we need to add? We know it is a bit geeky at the moment, but this is a BETA. We are open to suggestions.
If you press <Thank User> also thank black_eagle too as he did most of the coding
