Solved - SQLlite database updates advice needed

Solved SQLlite database updates advice needed - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Solved SQLlite database updates advice needed (/showthread.php?tid=353138)

SQLlite database updates advice needed - HuwSy - 2020-04-04

Hi

I am currently running daily builds of LibrELEC with a Docker container running Emby server. I prefer to use Kodi when on the device for its database, navigation etc but Emby is to facilitate usage on another tv in the house. This works well other than I end up with playcounts of videos that are out of sync so i have to remember what i have and haven't watched or go and look where I am up to at times. Currently the Emby for Kodi addon doesnt support Python 3 so i cant even investigate if that would be the solution so in the interim I had hoped a basic Pythoin script running within cron would solve my problem seeing as all the media is on the same paths I could cross match and fix the playcounts automatically. For some reason the script calculates the correct playcounts, runs the update table queries seemingly succesfully but still the apps (Kodi and Emby) are both out of sync. Can anyone see what I am doing wrong here for both or at least the Kodi side of this?

Thanks

python:


#!/usr/bin/env python3

import sqlite3

import os

import time

import datetime



os.system('docker stop emby')

os.system('systemctl stop kodi')

os.system('systemctl stop kodi')



time.sleep(15)



conK = sqlite3.connect('/storage/.kodi/userdata/Database/MyVideos116.db')

curK = conK.cursor()



conE = sqlite3.connect('/storage/.emby/data/library.db')

curE = conE.cursor()



curK.execute("select strPath || strFilename path, playcount, f.idfile from files f join path p on p.iDpath = f.idPath where path like '%' order by path;")

curE.execute("select path, playcount, u.userdatakeyid from mediaitems m join userdatas u on m.userdatakeyid = u.userdatakeyid where path like '%' order by path;")



p = {}

k = {}

e = {}



for rowK in curK:

  r = rowK[0].lower()

  if str(rowK[1]) == 'None':

    p[r] = 0

  else:

    p[r] = rowK[1]

  k[r] = rowK[2]



for rowE in curE:

  r = rowE[0].lower()

  if str(rowE[1]) == 'None':

    if not r in p:

      p[r] = 0

  else:

    if r in p:

      if rowE[1] > p[r]:

        p[r] = rowE[1]

    else:

      p[r] = rowE[1]

  e[r] = rowE[2]



for key in k:

  conK.execute('update files set lastplayed = "2020-01-01 00:00:00", playcount = ' + str(p[key]) + ' where idfile = ' + str(k[key]) + ';')



for key in e:

  conE.execute('update userdatas set played = 1, playcount = ' + str(p[key]) + ' where userdatakeyid = ' + str(e[key]) + ';')



curK.close()

conK.close()

curE.close()

conE.close()



time.sleep(5)



os.system('systemctl start kodi')

os.system('systemctl start kodi')

os.system('docker start emby')

RE: SQLlite database updates advice needed - HuwSy - 2020-04-04

spotted 2 issues but still doesn't fix my problem. committing the connections before closing and passing the updates as arguments not concat strings.

RE: SQLlite database updates advice needed - HuwSy - 2020-04-05

Found where I was going wrong...

On the Emby side I needed played = 1 and commit. Then on Kodi it was just the commit missing. Some cleanup for missing rows too like this.

python:


#!/usr/bin/env python3

import sqlite3

import os

import time



os.system('docker stop emby')

os.system('systemctl stop kodi')

time.sleep(5)



conK = sqlite3.connect('/storage/.kodi/userdata/Database/MyVideos116.db')

curK = conK.cursor()

curK.execute("select strPath || strFilename path, playcount, f.idfile from files f join path p on p.iDpath = f.idPath where path like '%' order by path;")



conE = sqlite3.connect('/storage/.emby/data/library.db')

curE = conE.cursor()

curE.execute("select path, playcount, m.userdatakeyid from mediaitems m left join userdatas u on m.userdatakeyid = u.userdatakeyid where path like '%' order by path;")



p = {}

k = {}

e = {}



for rowK in curK:

  r = rowK[0].lower()

  if str(rowK[1]) == 'None':

    p[r] = 0

  else:

    p[r] = rowK[1]

  k[r] = rowK[2]

  print(rowK)



for rowE in curE:

  r = rowE[0].lower()

  if str(rowE[1]) == 'None':

    if not r in p:

      p[r] = 0

  else:

    if r in p:

      if rowE[1] > p[r]:

        p[r] = rowE[1]

    else:

      p[r] = rowE[1]

  e[r] = rowE[2]

  print(rowE)



for key in k:

  print('update files set playcount = ' + str(p[key]) + ' where idfile = ' + str(k[key]) + ';')

  curK.execute('update files set playcount = ' + str(p[key]) + ' where idfile = ' + str(k[key]) + ';')

  conK.commit()

  print(conK.total_changes)



for key in e:

  played = '0'

  if p[key] > 0:

    played = '1'

  try:

    print('insert or ignore into userdatas values (' + str(e[key]) + ',1,0,' + played + ',' + str(p[key]) + ',0,0,0,0,0,0,0);')

    curE.execute('insert or ignore into userdatas values (' + str(e[key]) + ',1,0,' + played + ',' + str(p[key]) + ',0,0,0,0,0,0,0);')

    conE.commit()

  finally:

    print('update userdatas set played = ' + played + ', playcount = ' + str(p[key]) + ' where userdatakeyid = ' + str(e[key]) + ';')

    curE.execute('update userdatas set played = ' + played + ', playcount = ' + str(p[key]) + ' where userdatakeyid = ' + str(e[key]) + ';')

    conE.commit()

  print(conE.total_changes)



curK.close()

conK.close()

curE.close()

conE.close()



time.sleep(5)

os.system('systemctl start kodi')

os.system('docker start emby')

SQLlite database updates advice needed - Klojum - 2020-04-05

Thread marked solved.

RE: SQLlite database updates advice needed - HuwSy - 2020-04-06

Just in case it is of use to anyone else I have an updated version that syncs played progress too.

python:


#!/usr/bin/env python3

import sqlite3

import os

import time

from datetime import datetime



os.system('docker stop emby')

os.system('systemctl stop kodi')

time.sleep(5)



conK = sqlite3.connect('/storage/.kodi/userdata/Database/MyVideos116.db')

curK = conK.cursor()

curK.execute("select strPath || strFilename path, playcount, f.idfile, b.timeinseconds from files f join path p on p.iDpath = f.idPath left join bookmark b on b.idfile = f.idfile where path like '%' order by path;")



conE = sqlite3.connect('/storage/.emby/data/library.db')

curE = conE.cursor()

curE.execute("select path, playcount, m.userdatakeyid, u.playbackPositionTicks from mediaitems m left join userdatas u on m.userdatakeyid = u.userdatakeyid where path like '%' order by path;")



p = {}

d = {}

k = {}

e = {}



for rowK in curK:

  r = rowK[0].lower()

  if str(rowK[3]) == "None":

    d[r] = 0

  else:

    d[r] = rowK[3] * 10000000

  if str(rowK[1]) == 'None':

    p[r] = 0

  else:

    p[r] = rowK[1]

  k[r] = rowK[2]

  print(rowK)



for rowE in curE:

  r = rowE[0].lower()

  if str(rowE[3]) == "None":

    if not r in d:

      d[r] = 0

  else:

    if r in d:

      if p[r] < rowE[1]:

        d[r] = rowE[3]

      elif p[r] > rowE[1]:

        d[r] = d[r]

      elif rowE[3] > d[r]:

        d[r] = rowE[3]

    else:

      d[r] = rowE[3]

  if str(rowE[1]) == 'None':

    if not r in p:

      p[r] = 0

  else:

    if r in p:

      if rowE[1] > p[r]:

        p[r] = rowE[1]

    else:

      p[r] = rowE[1]

  e[r] = rowE[2]

  print(rowE)



for key in k:

  print('update files set playcount = ' + str(p[key]) + ' where idfile = ' + str(k[key]) + ';')

  if d[key] > 0:

    tot = conK.total_changes

    curK.execute('update bookmark set timeinseconds = ' + str(d[key]/10000000) + ' where idfile = ' + str(k[key]) + ' and type = 1;')

    conK.commit()

    if tot == conK.total_changes:

      curK.execute('insert into bookmark (idfile,timeInSeconds,player,type) values (' + str(k[key]) + ',' + str(d[key]/10000000) + ',"VideoPlayer",1);')

  else:

    curK.execute('delete from bookmark where idfile = ' + str(k[key]) + ' and type = 1;')

  if p[key] > 0:

    #curK.execute('update files set lastPlayed = "' + str(datetime.now())[:19] + '" where idfile = ' + str(k[key]) + ' and lastPlayed is null;')

    curK.execute('update files set playcount = ' + str(p[key]) + ' where idfile = ' + str(k[key]) + ';')

  else:

    curK.execute('update files set lastPlayed = null, playcount = null where idfile = ' + str(k[key]) + ';')

  conK.commit()

  print(conK.total_changes)



for key in e:

  played = '0'

  if p[key] > 0:

    played = '1'

  try:

    print('insert or ignore into userdatas values (' + str(e[key]) + ',1,0,' + played + ',' + str(p[key]) + ',0,0,0,0,0,0,0);')

    curE.execute('insert or ignore into userdatas values (' + str(e[key]) + ',1,0,' + played + ',' + str(p[key]) + ',0,' + str(d[key]) + ',0,0,0,0,0);')

    conE.commit()

  finally:

    print('update userdatas set played = ' + played + ', playcount = ' + str(p[key]) + ' where userdatakeyid = ' + str(e[key]) + ';')

    curE.execute('update userdatas set played = ' + played + ', playcount = ' + str(p[key]) + ', playbackPositionTicks = ' + str(d[key]).split('.')[0] + ' where userdatakeyid = ' + str(e[key]) + ';')

    conE.commit()

  print(conE.total_changes)



curK.close()

conK.close()

curE.close()

conE.close()



time.sleep(5)

os.system('systemctl start kodi')

os.system('docker start emby')

RE: SQLlite database updates advice needed - Klojum - 2020-04-06

(2020-04-06, 11:28)HuwSy Wrote: I have an updated version that syncs played progress too

If you post code, please add the 'python' as a syntax type for some fancy coloring.
Empty syntax tags do not do the coloring automagically.

RE: SQLlite database updates advice needed - HuwSy - 2020-04-09

Ok iv learnt something new for today Smile

. Thanks.