Attempting to migrate video.database.cleaner to Kodi 19
#1
Wink 
Hi there,

I have been working on migrating the infamous video.database.cleaner, written by black_eagle.

I have managed to get it to install and run in Kodi 19, new codebase can be downloaded at my fork:  https://github.com/klyco/script.database.cleaner  and the full code can be seen there.

I am stuck on getting the addon to actually delete the rows that it identifies as "needing to be deleted".  It correctly identifies the bad db entries, and runs to completion and "thinks" that it has worked, but it hasn't.  The db entries remain.

Black_eagle and I believe that the code to build the query for deletion may be the issue.  The code I am referring to can be seen here:
https://pastebin.com/kqFGk0BZ

If someone that is familiar with the video database could kindly look over the code, to see if you can identify why it is failing to create the DELETE queries correctly, I would REALLY appreciate it!!

Thanks so much for you time!
Ken
#2
Testing a bit of color coding here... Also cleaned up the code a bit, the SQL commands could also be written out over multiple lines for better readability. My code indentation may need some additional updating.
N.B.: always create/keep usable backups at hand! Code hereunder has not been tested.

python:
# Build SQL query
if not no_sources: # this is SQL for no sources
sql = """DELETE FROM files WHERE idPath IN ( SELECT idPath FROM path WHERE ((""" + my_command + """)));"""
else:
sql = """DELETE FROM files WHERE idPath IN (SELECT idPath FROM path WHERE ((strPath LIKE 'rtmp://%' OR strPath LIKE 'rtmpe:%' OR strPath LIKE 'plugin:%' OR strPath LIKE 'http://%' OR strPath LIKE 'https://%') AND (""" + my_command + """)));"""

if my_command == "":
sql=sql.replace('((strPath','(strPath').replace(' AND ()))',')')
dbglog('SQL command is %s' % sql)

if not specificpath and not replacepath:
dbglog (our_source_list)
our_select = sql.replace('DELETE FROM files','SELECT strPath FROM path',1)

if bookmarks: # have to delete from paths table rather than files as there is a conflicting trigger on the files table
our_select = sql.replace('DELETE FROM files', 'SELECT strPath FROM path WHERE idPath in (SELECT idPath FROM files', 1)
our_select = our_select.replace('bookmark)', 'bookmark))',1)
sql = sql.replace('DELETE FROM files','DELETE FROM path',1)
dbglog('Select Command is %s' % our_select)

elif not replacepath and specificpath: # cleaning a specific path

if specific_path_to_remove != '':
sql = """delete from path where idPath in(select * from (SELECT idPath FROM path WHERE (strPath LIKE '""" + specific_path_to_remove +"""%')) as temptable)"""
our_select = "SELECT strPath FROM path WHERE idPath IN (SELECT idPath FROM path WHERE (strPath LIKE'" + specific_path_to_remove + "%'))"
dbglog('Select Command is %s' % our_select)
else:
xbmcgui.Dialog().ok(addonname,'Error - Specific path selected but no path defined. Script aborted')
dbglog("Error - Specific path selected with no path defined")
exit_on_error()

else: # must be replacing a path at this point

if old_path != '' and new_path != '':
our_select = "SELECT strPath from path WHERE strPath Like '" + old_path + "%'"
else:
xbmcgui.Dialog().ok(addonname,'Error - Replace path selected but one or more paths are not defined. Script aborted')
dbglog('Error - Missing path for replacement')
exit_on_error()
xbmc.sleep(500)
#3
(2021-05-17, 18:36)Klojum Wrote: Testing a bit of color coding here... Also cleaned up the code a bit, the SQL commands could also be written out over multiple lines for better readability. My code indentation may need some additional updating.
N.B.: always create/keep usable backups at hand! Code hereunder has not been tested.
Mate, you are just a legend!  I will certainly test and let you know!!
#4
Hey again!  OK so when I amend the indentation (basically the last few lines) and run this code, I get a popup error:   Error - specific path selected but no path defined.  Script Aborted.

Here is a larger piece of the code, if that helps:

https://pastebin.com/2LFkqEwg  

Cheers!
Ken
#5
(2021-05-17, 21:04)kenmills Wrote: Here is a larger piece of the code, if that helps:

BTW, I prefer a paste site without wordwrap. I know, I'm picky. I was a developer in a previous life.
Try http://paste.ubuntu.com next time. Sample: https://paste.ubuntu.com/p/8KZfqq3Zcb/
#6
(2021-05-17, 21:31)Klojum Wrote:
(2021-05-17, 21:04)kenmills Wrote: Here is a larger piece of the code, if that helps:

BTW, I prefer a paste site without wordwrap. I know, I'm picky. I was a developer in a previous life.
Try http://paste.ubuntu.com next time. Sample: https://paste.ubuntu.com/p/8KZfqq3Zcb/
Mr FancyPants :-)

Nice site, i kinda like the non word wrap myself...  Will use it from now on.  Thanks for the tip!
#7
It is a strange error because I don't have  specific path specified in the settings...
#8
OK, I fixed that error - it was in indentation issue.  

The code now runs, but still does not remove the entries, nor does it trigger the Kodi database clean afterwards.

Code fixed is here:
https://paste.ubuntu.com/p/JkwC38ZGhg/
#9
Coding, developing... It's nothing like in the movies where ppl are bypassing the biggest firewalls or creating a new encryption in under 3 seconds... It's all baby steps for us in real life. Tongue

But indentation can cause such an issue? I'll stick to PHP for now, lol.

Just a fyi (in case you missed it), your database cleaner only touches the files database table, but lots more tables should be addressed when deleting movies or tvshows. This is an example of my queries for fully cleaning a tv show. And it's still not a complete clean, mind you.

(2021-05-17, 22:06)kenmills Wrote: Code fixed is here:
Don't forget the syntax colors. It can help you troubleshooting your code. Wink
#10
Yeah, the if statement was indented, so that it looked like it was a sub i of another.  Not sure if I explained that right lol.  Anyways, python throws a strop if you miss an indent.  Such a baby!

Ah, yeah sorry, I copy/pasted from Spyder, so forgot about the colour code...

Yeah this addon only attacks' the path table, and is a rudimentary check;  do any paths NOT start with the paths that are in your sources.xml .  Very basic, but effective (when it works) in cleaning the db, so that the Kodi Clean Database function doesn't take an hour.

I would like to see your TV show queries added into this script, for a deeper clean.  And the same for movies, do you have movie clean queries?  :-)

It may be work for a more senior developer than myself though, lol.

I was hoping to just get the python coverted enough to run in Kodi 19, with the basic cleaning of the path table...  But can't even get that working.  Oy vey.
#11
(2021-05-17, 22:37)kenmills Wrote: And the same for movies, do you have movie clean queries?

Yes, but I'm still finetuning that one. I'm also cleaning the sets table now. But like the tvshows, handling the thumbnail cache and the Textures13.db is not supported (yet).
I don't have Python skills either, I just mess about in PHP, which is probably a more forgiving programming language.

Also, jamming everything in a single top-down script like the database-cleaner tool is now, isn't making things easier IMO. Just like typing out SQL queries in one long line won't be. But we all learn bit by bit.
#12
(2021-05-17, 23:21)Klojum Wrote:
(2021-05-17, 22:37)kenmills Wrote: And the same for movies, do you have movie clean queries?

Yes, but I'm still finetuning that one. I'm also cleaning the sets table now. But like the tvshows, handling the thumbnail cache and the Textures13.db is not supported (yet).
I don't have Python skills either, I just mess about in PHP, which is probably a more forgiving programming language.

Also, jamming everything in a single top-down script like the database-cleaner tool is now, isn't making things easier IMO. Just like typing out SQL queries in one long line won't be. But we all learn bit by bit.

Awesome stuff.  If you could PM me with any queries you have related to the Movies/sets, that would be a great help to get me started.

No worries about the thumbnail or textures13.db - there is a python script that deals with cleaning that up, called texturecache.py and it is very effective.  I'll look into incorporating that script into the addon, to assist in further cleanup.  :-)

Yeah the code could use some cleanup for sure...
#13
(2021-05-18, 10:26)kenmills Wrote: there is a python script that deals with cleaning that up, called texturecache.py and it is very effective.

I know about it. But it can also be quite complex in terms of parameters. And the tool should have been built-in functionality a long time ago.
#14
However, my current approach is a bit different. My SQL queries are based on picking a single movie or a single TV show via the Kodi GUI that is to be deleted, and that could even be a single TV episode (because Kodi supports that option).

The Video Database Cleaner tool takes the grande detour by handling all TV Shows or movies in one go. So there is no starting point really but to go down the long list.

Some people are removing videos from their disks / sources directly, and want Kodi to cleanup afterwards. If they used the Kodi GUI, clean up could be so much faster and more accurate. I'd say I have but an average movie/tv collection. The biggest database table in terms of records is the actor_link table with now some 190,000 records filled with actors being linked to one or more movies and/or tv episodes. Still a piece of cake for the average database engine, but keeping things small helps.

Still looking for a decent (free) Python online learning course...
#15
If you'd like I can give you a hand with this in Python.  I've written a Kodi DB cleaner specific to my uPNP source which will clear old Kodi data based upon the uPNP server URL and path.   Some of the queries and logic could be modified and amended to your code.  It's more of a bulk delete approach limited to just data related to the uPNP source but the concept is exactly the same.  I also have looked to ensure the the cleaner SQL code doesn't replicate any existing delete triggers in the Kodi DB (i.e. delete_episode, delete_movie etc...).  So to your point about actor_link, that is taken care of by the delete triggers for movies, episodes and such.  No need to delete them by hand.  Likewise bookmarks and streamdetails are handled by the delete_file trigger. 

You might want to checkout the free DB Browser for SqlLite software.  it has a pretty good database visualizer and you can see the triggers already in Kodi.   My Kodi DB cleaner is called from a service which checks whether a video is playing and if not, will clean the Kodi DB once a day between midnight and 6AM. 

For Python learning I highly recommend LeanPub Python Apprentice ..  It's PDF and not online and has a small cost but it is very good.  They have an online version but I am not sure how much it is. 

Here's some code from my cleaner:
python:

def get_installedversion():
    # retrieve current installed version
    json_query = xbmc.executeJSONRPC('{ "jsonrpc": "2.0", "method": "Application.GetProperties", "params": {"properties": ["version", "name"]}, "id": 1 }')
    json_query = json.loads(json_query)
    version_installed = []
    if 'result' in json_query and 'version' in json_query['result']:
        version_installed  = json_query['result']['version']['major']
    return str(version_installed)

installed_version = get_installedversion()


def getDatabaseName():
    if installed_version == '10':
        return "MyVideos37.db"
    elif installed_version == '11':
        return "MyVideos60.db"
    elif installed_version == '12':
        return "MyVideos75.db"
    elif installed_version == '13':
        return "MyVideos78.db"
    elif installed_version == '14':
        return "MyVideos90.db"
    elif installed_version == '15':
        return "MyVideos93.db"
    elif installed_version == '16':
        return "MyVideos99.db"
    elif installed_version == '17':
        return "MyVideos107.db"
    elif installed_version == '18':
        return "MyVideos116.db"
    elif installed_version == '19':
        return "MyVideos119.db"
       
    return ""   


def openKodiDB():                                 #  Open Kodi database
    try:
        from sqlite3 import dbapi2 as sqlite
    except:
        from pysqlite2 import dbapi2 as sqlite
                      
    DB = os.path.join(xbmc.translatePath("special://database"), getDatabaseName())  
    db = sqlite.connect(DB)

    return(db)

def kodiCleanDB(ContentDeleteURL, force):

    if addon.getSetting('kodiclean') == 'true' or force == 1:  #  clears Kodi DB Mezzmo data if enabled in settings

        db = openKodiDB()
        xbmc.log('Content delete URL: ' + ContentDeleteURL, xbmc.LOGDEBUG)
         
        rfpos = ContentDeleteURL.find(':',7)        #  Get Mezzmo server info
        serverport = '%' + ContentDeleteURL[rfpos+1:rfpos+6] + '%'

        db.execute('DELETE FROM art WHERE url LIKE ?', (serverport,))
        db.execute('DELETE FROM actor WHERE art_urls LIKE ?', (serverport,))
        db.execute('DELETE FROM tvshow WHERE c17 LIKE ?', (serverport,))
        xbmc.log('Mezzmo serverport is: ' + serverport, xbmc.LOGDEBUG)
        curf = db.execute('SELECT idFile FROM files INNER JOIN path USING (idPath) WHERE         \
        strpath LIKE ?', (serverport,))             #  Get file and movie list
        idlist = curf.fetchall()
        for a in range(len(idlist)):                #  Delete Mezzmo file and Movie data
            # xbmc.log('Clean rows found: ' + str(idlist[a][0]), xbmc.LOGINFO)
            db.execute('DELETE FROM files WHERE idFile=?',(idlist[a][0],))
            db.execute('DELETE FROM movie WHERE idFile=?',(idlist[a][0],))
            db.execute('DELETE FROM episode WHERE idFile=?',(idlist[a][0],))
        db.execute('DELETE FROM path WHERE strPath LIKE ?', (serverport,))

        xbmc.log('Kodi database Mezzmo data cleared.', xbmc.LOGINFO)
        curf.close()
        db.commit()
        db.close()

        addon.setSetting('kodiclean', 'false')     # reset back to false after clearing

Jeff
Running with the Mezzmo Kodi addon.  The easier way to share your media with multiple Kodi clients.
Service.autostop , CBC Sports, Kodi Selective Cleaner and Mezzmo Kodi addon author.

Logout Mark Read Team Forum Stats Members Help
Attempting to migrate video.database.cleaner to Kodi 190