Release - WatchedList - service to automatically save/restore watched state

  Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
schapplm Offline
Junior Member
Posts: 48
Joined: Apr 2013
Reputation: 1
Post: #61
(2015-07-31 05:57)dags Wrote:  
(2015-07-14 18:10)Steihl Wrote:  Getting following error of late when attempting to update WL:
...

Hi All,
I am now having this same issue too.
Code:
...
13:42:39 T:6764   ERROR: service.watchedlist: write_wl_wdata: MySQL Database error 1292 (22007): Incorrect datetime value: '1436576466' for column 'lastPlayed' at row 15 ...

I finally updated the official version to 1.0.4. Please notify, if this still occurs with this version.

My addon: service.watchedlist
My HTPC: Kodi 15.1; Ubuntu 14.04 LTS x64; AMD A6-6400K; MSI FM2-A55M-E33; 4GB Kingston HyperX RAM; Digital Devices Cine CT V6 DVB-C; Streacom FC8B Evo with 120W picoPSU
find quote
dags Offline
Junior Member
Posts: 15
Joined: Sep 2013
Reputation: 0
Post: #62
(2015-08-16 19:07)schapplm Wrote:  I finally updated the official version to 1.0.4. Please notify, if this still occurs with this version.

Thank you it seems to be working perfectly at the moment with no errors again..
Appreciate the fix.
find quote
ShadowTek Offline
Posting Freak
Posts: 895
Joined: Nov 2012
Reputation: 65
Location: In a dark cave
Post: #63
Wow, just what I was looking for, great thanks.
find quote
Win a 2 Offline
Junior Member
Posts: 34
Joined: Feb 2012
Reputation: 0
Post: #64
Hi,

OpenELEC arrived at the Isengard-Era. I would like to update :-)

Is WatchedList running fine on Kodi 15.2?
find quote
micahg Offline
Senior Member
Posts: 243
Joined: Sep 2011
Reputation: 1
Post: #65
(2015-11-07 00:35)Win a 2 Wrote:  Hi,

OpenELEC arrived at the Isengard-Era. I would like to update :-)

Is WatchedList running fine on Kodi 15.2?
Works for me.
find quote
micahg Offline
Senior Member
Posts: 243
Joined: Sep 2011
Reputation: 1
Post: #66
Hello,

I've added support to sync your sqlite files to dropbox automatically. I've tested on windows 10, ubuntu 15.10 and openelec 6. You can configure your dropbox account from the addon configuration. Go to the database settings and click "Dropbox Auth Code". Follow the steps and your db should automatically upload to the apps/kodiwatchedlist folder on your dropbox account.

There is a problem on Ubuntu 15.10 with they python-cryptography package. If you run into you have to remove the package with apt-get and install 'cryptography' with pip. Something like:

Code:
sudo apt-get remove python-cryptography
sudo pip install cryptography

Anyway, if anyone wants to help me test, my version of the addon is available from github here. Before installing, you will have to remove your existing watchedlist addon.
find quote
Jeffers24 Offline
Member
Posts: 75
Joined: Nov 2014
Reputation: 0
Post: #67
I have been using Kodi for a while now stable version 15.2 build from the Google PlayStore and have watched via streaming (not stored locally) a number of TV shows and movies which are all showing correctly as 'watched'.
I wish to use a build wizard and was looking for a straight forward once (hopefully) only way of copying the file/s that store the watched status before running the build wizard and then re-installing the copied file/s to restore my watched flags.
I have not found details of which file/s the watched indicators are stored in so have not found my easy copy, run wizard and re-install file/s option.
In my search I came across WatchedList, which I have installed and it runs automatically 5 minutes after I start up Kodi 15.2.
I see WatchList start and update messages displayed at the top of my screen, but for all items the reported number of saves is 0?
What am I missing?
Am I going down a false road, my thoughts were that I would create an external to Kodi copy of my watched TV/Movie watched indicators using WatchedList, run the Build Wizard, install/then use WatchList to re-install my TV/Movie watched indicators but cannot see an option to import the copy created by WatchList??
find quote
schapplm Offline
Junior Member
Posts: 48
Joined: Apr 2013
Reputation: 1
Post: #68
(2015-12-09 08:30)Jeffers24 Wrote:  I have not found details of which file/s the watched indicators are stored in so have not found my easy copy, run wizard and re-install file/s option.
The watched flag is stored inside the Kodi video database, which is e.g. ~/.kodi/userdata/Database/MyVideos90.db. So maybe restoring the database file already works for you.
(2015-12-09 08:30)Jeffers24 Wrote:  In my search I came across WatchedList, which I have installed and it runs automatically 5 minutes after I start up Kodi 15.2.
I see WatchList start and update messages displayed at the top of my screen, but for all items the reported number of saves is 0?
What am I missing?
Is only the number of videos newly marked as watched zero or also the total number of videos in the WatchedList?
Both values should be displayed if you enable the debugging mode (see here for settings).
(2015-12-09 08:30)Jeffers24 Wrote:  Am I going down a false road, my thoughts were that I would create an external to Kodi copy of my watched TV/Movie watched indicators using WatchedList, run the Build Wizard, install/then use WatchList to re-install my TV/Movie watched indicators but cannot see an option to import the copy created by WatchList??
Your described workflow is correct.
The addon saves the watched indicators in a database file, usuallly ~/.kodi/userdata/addon_data/service.watchedlist/watchedlist.db.
You have to make sure that this file still exists after the re-installation of Android/Kodi (I don't have that system myself so I don't know the steps and am not sure what the build-wizard is).
Alternatively, you can set the WatchedList database to be on a network share or on a remote mySQL server. Then the data is not altered by a re-installation.

My addon: service.watchedlist
My HTPC: Kodi 15.1; Ubuntu 14.04 LTS x64; AMD A6-6400K; MSI FM2-A55M-E33; 4GB Kingston HyperX RAM; Digital Devices Cine CT V6 DVB-C; Streacom FC8B Evo with 120W picoPSU
find quote
brando56894 Offline
Senior Member
Posts: 134
Joined: Jan 2010
Reputation: 1
Post: #69
I'm getting notices in PhpMyAdmin that I need to add indexes, but I'm not too proficient with SQL. What columns would be best to index in which tables? I mainly watch TV shows and I have a very large database (7500 shows), while looking at the WatchedList database, the Episode_Watched table has 4227 rows, so I'm assuming this would be the best table to index, but I'm not sure which columns would be the best. My database is running off of my FreeNAS box (Intel 8 core Atom @ 2.4 GHz, 32 GB RAM, Samsung 850 Evo SSD) and when I run the plugin on my Nvidia Shield TV or my Windows 8 PC, it runs through the whole scanning process pretty quickly (30-60 seconds), but PhpMyAdmin makes me believe it could be faster.

These are the notices I'm getting:
  • There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins
  • The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
  • The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.

Here's my query list after 12 days usage
[Image: 12b18a454153480.jpg]
find quote
schapplm Offline
Junior Member
Posts: 48
Joined: Apr 2013
Reputation: 1
Post: #70
(2015-12-23 19:54)brando56894 Wrote:  I'm getting notices in PhpMyAdmin that I need to add indexes, but I'm not too proficient with SQL. What columns would be best to index in which tables? I mainly watch TV shows and I have a very large database (7500 shows), while looking at the WatchedList database, the Episode_Watched table has 4227 rows, so I'm assuming this would be the best table to index, but I'm not sure which columns would be the best. My database is running off of my FreeNAS box (Intel 8 core Atom @ 2.4 GHz, 32 GB RAM, Samsung 850 Evo SSD) and when I run the plugin on my Nvidia Shield TV or my Windows 8 PC, it runs through the whole scanning process pretty quickly (30-60 seconds), but PhpMyAdmin makes me believe it could be faster.

From my understanding of mySQL, the index should be the column that is preferably unique and is used in WHERE clauses often.
So the best indexes for the WatchedList tables would be the columns already marked as primary:

Code:
ALTER TABLE `tvshows` ADD INDEX(`idShow`);
ALTER TABLE `episode_watched` ADD INDEX( `idShow`, `season`, `episode`);
ALTER TABLE `movie_watched` ADD INDEX(`idMovieImdb`);

(2015-12-23 19:54)brando56894 Wrote:  These are the notices I'm getting:
  • There are too many joins without indexes. This means that joins are doing full table scans. Adding indexes for the columns being used in the join conditions will greatly speed up table joins
  • The rate of reading data from a fixed position is high. This indicates that many queries need to sort results and/or do a full table scan, including join queries that do not use indexes. Add indexes where applicable.
  • The rate of reading the next table row is high. This indicates that many queries are doing full table scans. Add indexes where applicable.

From the design of the addon, the full tables for movies, tvshows and episodes have to be read at startup to be compared against the tables from Kodi.
The only thing that should be improved by the indexes is the updating of the watched state and the synchronization of the tv shows list:

Code:
UPDATE movie_watched SET ... WHERE idMovieImdb LIKE ...
UPDATE episode_watched SET ... WHERE idShow LIKE %s AND season LIKE %s AND episode LIKE %s
INSERT IGNORE INTO tvshows (idShow,title) VALUES (%s, %s)

I tested using the addon with my configuration (58 tv shows, 3684 episodes, 799 movies) and it took 7 seconds with and without indexes (only reading, no updating).
Do you get improvements when you apply the table alterations above?

(2015-12-23 19:54)brando56894 Wrote:  Here's my query list after 12 days usage

Is your Kodi video library also on this mySQL server and included in these stats? I have the impression that reading the Kodi database through the JSON interface takes most of the time when running my addon.

My addon: service.watchedlist
My HTPC: Kodi 15.1; Ubuntu 14.04 LTS x64; AMD A6-6400K; MSI FM2-A55M-E33; 4GB Kingston HyperX RAM; Digital Devices Cine CT V6 DVB-C; Streacom FC8B Evo with 120W picoPSU
find quote
brando56894 Offline
Senior Member
Posts: 134
Joined: Jan 2010
Reputation: 1
Post: #71
Thanks, is the second quote something I should be entering in? I won't be able to test the results of the first set of statements for another few days. As for my libraries, they're local to each box since one is Windows 8.1 whereas the other is Android 5.1, I used to have a centralized database, but as you stated, it's kind of slow.
find quote
schapplm Offline
Junior Member
Posts: 48
Joined: Apr 2013
Reputation: 1
Post: #72
(2015-12-30 22:10)brando56894 Wrote:  is the second quote something I should be entering in?
When you open the WatchedList database in phpmyadmin, you can enter the SQL queries from the first code block (ALTER TABLE ...) in a text input box.
The second code block (UPDATE ...) is just for showing how the source code of the add-on looks like.

My addon: service.watchedlist
My HTPC: Kodi 15.1; Ubuntu 14.04 LTS x64; AMD A6-6400K; MSI FM2-A55M-E33; 4GB Kingston HyperX RAM; Digital Devices Cine CT V6 DVB-C; Streacom FC8B Evo with 120W picoPSU
(This post was last modified: 2016-01-03 11:50 by schapplm.)
find quote
brando56894 Offline
Senior Member
Posts: 134
Joined: Jan 2010
Reputation: 1
Post: #73
Thanks that's what I figured.
find quote
jeremywho Offline
Junior Member
Posts: 2
Joined: Jan 2016
Reputation: 0
Post: #74
Hi @schapplm

I'm trying to use WatchedList to sync the watched state of two Kodi installs, which looks perfectly viable based on comments throughout this thread.
The main system is Windows 8.1/Kodi 15.2, and the secondary system (not used often) is OpenElec 5.0.8/Kodi 14.2.
The WL database is located on a MySQL system, but Kodi databases are local to each install.

On the main (Windows) system, WL does not appear to update the database on the set schedule, despite being set to autorun/periodic/3 hourly.
The database is updated correctly if I manually launch the add-on, and then the OpenElec system correctly applies the changes at some time later (also periodic/3hr). However the Windows box never seems to update the WL database automatically, even if left for days.
Follow-user is set to 'on', and a 5-min wait set for start-up delay, on both systems.

Can you please tell me if these settings /should/ be working as a way to keep the databases synced (with the obvious 3-hour potential delay)?
Or advise an alternate set of settings that I can try? A delay in sync is fine, especially as the OpenElec system is a low-spec CPU and and cannot crunch the databases quickly.

Thanks for any help..
J
(This post was last modified: 2016-01-10 03:13 by jeremywho.)
find quote
jeremywho Offline
Junior Member
Posts: 2
Joined: Jan 2016
Reputation: 0
Post: #75
(2015-12-30 22:10)brando56894 Wrote:  Thanks, is the second quote something I should be entering in? I won't be able to test the results of the first set of statements for another few days. As for my libraries, they're local to each box since one is Windows 8.1 whereas the other is Android 5.1, I used to have a centralized database, but as you stated, it's kind of slow.

@brando I have the same setup, and same (similar) NAS showing the same recommendations. Did you apply the indexes from the OP, and did they make any difference?
find quote
Post Reply