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