+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: Add-on Support (https://forum.kodi.tv/forumdisplay.php?fid=27)
+---- Forum: Program Add-ons (https://forum.kodi.tv/forumdisplay.php?fid=151)
+---- Thread: Solved WatchedList Move to SQL database (/showthread.php?tid=350573)
WatchedList Move to SQL database - themusj - 2019-12-31
I have the add-on WatchedList on three different platforms. I read the forum on the topic of moving the local database to an sql server and I have an sql server up and running and followed the directions in the post https://kodi.wiki/view/Add-on:WatchedList. Everything was going well but during the section of replacing the syntax in the exported database to sql, nothing gets changed.
So I thought I would just export the database without the data so I could at least have the structure imported into my sql database. But when trying to import it, I get an error message "MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1"
Here it the database I am trying to import as listed in Notepad++. Can anyone advise me on what I could do to make this work? I know in Kodi if you setup the advancedsettings it will automatically create the database in SQL. But I am opting not to move my database to it at this point, just the data from WatchedList.
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "tvshows" (
"idShow" INTEGER,
"title" TEXT,
PRIMARY KEY("idShow")
);
CREATE TABLE IF NOT EXISTS "episode_watched" (
"idShow" INTEGER,
"season" INTEGER,
"episode" INTEGER,
"playCount" INTEGER,
"lastChange" INTEGER,
"lastPlayed" INTEGER,
PRIMARY KEY("idShow","season","episode")
);
CREATE TABLE IF NOT EXISTS "movie_watched" (
"idMovieImdb" INTEGER,
"playCount" INTEGER,
"lastChange" INTEGER,
"lastPlayed" INTEGER,
"title" TEXT,
PRIMARY KEY("idMovieImdb")
);
COMMIT;
RE: WatchedList Move to SQL database - Klojum - 2020-01-01
Sticking to the SQL syntax always helps:
sql:SET autocommit = OFF;
START TRANSACTION;
CREATE TABLE tvshows (
idShow INTEGER,
title TEXT,
PRIMARY KEY(idShow)
);
CREATE TABLE IF NOT EXISTS episode_watched (
idShow INTEGER,
season INTEGER,
episode INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
PRIMARY KEY(idShow,season,episode)
);
CREATE TABLE IF NOT EXISTS movie_watched (
idMovieImdb INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
title TEXT,
PRIMARY KEY(idMovieImdb)
);
COMMIT;
RE: WatchedList Move to SQL database - themusj - 2020-01-01
(2020-01-01, 00:06)Klojum Wrote: Sticking to the SQL syntax always helps:
sql:SET autocommit = OFF;
START TRANSACTION;
CREATE TABLE tvshows (
idShow INTEGER,
title TEXT,
PRIMARY KEY(idShow)
);
CREATE TABLE IF NOT EXISTS episode_watched (
idShow INTEGER,
season INTEGER,
episode INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
PRIMARY KEY(idShow,season,episode)
);
CREATE TABLE IF NOT EXISTS movie_watched (
idMovieImdb INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
title TEXT,
PRIMARY KEY(idMovieImdb)
);
COMMIT;
I work f/t in the IT Desktop profession, my son works f/t on the Server side of things, but while we might support those who use it, SQL is mostly foreign to both of us. I was hoping based on the link above, I could make this work without having to do so from the ground up. Importing this text into SQLite Database Browser returned an error code and not the expected tables.
RE: WatchedList Move to SQL database - themusj - 2020-01-01
(2020-01-01, 01:49)themusj Wrote:Looks like I have it working. Looking at the actual data in the MySQL database, I assume for example the movies listed are the ones actually listed as "watched." Right now it is running with the message, "Remember as watched (upd"(2020-01-01, 00:06)Klojum Wrote: Sticking to the SQL syntax always helps:
sql:SET autocommit = OFF;
START TRANSACTION;
CREATE TABLE tvshows (
idShow INTEGER,
title TEXT,
PRIMARY KEY(idShow)
);
CREATE TABLE IF NOT EXISTS episode_watched (
idShow INTEGER,
season INTEGER,
episode INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
PRIMARY KEY(idShow,season,episode)
);
CREATE TABLE IF NOT EXISTS movie_watched (
idMovieImdb INTEGER,
playCount INTEGER,
lastChange INTEGER,
lastPlayed INTEGER,
title TEXT,
PRIMARY KEY(idMovieImdb)
);
COMMIT;
I work f/t in the IT Desktop profession, my son works f/t on the Server side of things, but while we might support those who use it, SQL is mostly foreign to both of us. I was hoping based on the link above, I could make this work without having to do so from the ground up. Importing this text into SQLite Database Browser returned an error code and not the expected tables.
RE: WatchedList Move to SQL database - Klojum - 2020-01-01
(2019-12-31, 23:39)themusj Wrote: But when trying to import it, I get an error message "MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRANSACTION' at line 1"
(2020-01-01, 01:49)themusj Wrote: Importing this text into SQLite Database Browser returned an error code and not the expected tables.Ehm... First you started with MySQL and now it's to be imported/used with SQLite. I don't know many specifics on the latter, but I'm sure there can be small differences here and there.
(2020-01-01, 03:29)themusj Wrote: but while we might support those who use it, SQL is mostly foreign to both of usSQL to me is also 'just another' programming language, next to the proper languages like C++, PHP, Java, etc... I'm not a certified SQL engineer, so using (online) documentation is still essential to me.
(2020-01-01, 03:29)themusj Wrote: Looks like I have it working. Looking at the actual data in the MySQL database, I assume for example the movies listed are the ones actually listed as "watched."Good to hear. But I take it you will have to run that SQL-query manually (or run it via some cron script) as Kodi itself will not trigger/update those external database tables.
(2020-01-01, 03:29)themusj Wrote: I assume for example the movies listed are the ones actually listed as "watched."All watched points are stored in the "files" table (field 'playCount' >= 1), and resume points are stored of course in "bookmarks".
RE: WatchedList Move to SQL database - themusj - 2020-01-01
(2020-01-01, 10:02)Klojum Wrote:Thank you, on this last point you read the question on my mind this morning. And where my current question remains.(2019-12-31, 23:39)themusj Wrote: I assume for example the movies listed are the ones actually listed as "watched."All watched points are stored in the "files" table (field 'playCount' >= 1), and resume points are stored of course in "bookmarks".
My MySQL WatchedList database with I can view with the SQL browser program lists 197 movies in the watchedstate. I have two of my Kodi installs pointing to it on my NAS server.
Kodi itself reports 201 movies in the watchedstate status. [Check marks as well as in the system information.]
I printed the list of movies in the database out and found the 4 not in both listings.
The four are movies I had to create specifically a .nfo file, but all four have the playcount=1 or more in the nfo file.
I have manually run the Add-on WatcheList several times and it is not picking those four up. So where does the WatchedList exactly pick up the information it needs to classify a movie or tv show as watched? Is the "files' table you refer to above the answer and where is that?
The WatchedList add-on pulls the marked status from somewhere and then stores it in its own database and then I assume when it finds Kodi's method of tracking that information missing, it restores that information to Kodi so it can report properly the current watchedstate of its media. If I have that right, I just need to figure out what is happening with those four files the WatchedList add-on isn't seeing their watched state correctly.
Happy New Year.
RE: WatchedList Move to SQL database - Klojum - 2020-01-01
(2020-01-01, 15:14)themusj Wrote: So where does the WatchedList exactly pick up the information it needs to classify a movie or tv show as watched? Is the "files' table you refer to above the answer and where is that?
All video-metadata related stuff is in .. the MyVideos database.
RE: WatchedList Move to SQL database - themusj - 2020-01-02
Excellent, that really helps. But a mystery remains.
I just re-ran manually again WatchedList and after it was completed, examined the database on mysql server. Those four movies are still not listed in it, but are in the Kodi database under files.
Since I am not getting any errors running the Watchlist add-on, I "assume" the add-on is getting to the mysql database, but for some reason those four movies are not being updated.
What does the Watchlist add-on use or pull from in order to update itself. These four movies I had to create my own nfo file for, but they all have the lastplayed entry in the nfo file no longer null but at least a value of 1.
RE: WatchedList Move to SQL database - themusj - 2020-01-02
One last if I may question before I start back to work tomorrow. :-) I had selected "All" for the Amount of User Information this recent time around and noticed that the banner showing updating lists every episode and movie it is updating. Before I had Only Info.
So my question is really an observation. I notice the All option shows this process is taking quite a bit of time. When I had Only Info, does it mean the same long process is going on behind the scenes? Something I just was not aware of when I had the Only Info option checked vs the All option.
RE: WatchedList Move to SQL database - themusj - 2020-01-03
Figured it out. My nfo files need a IMDB or TVDB Id. Since these are one-off shows, mostly on PBS, they are not listed yet in those databases. I might suggest a special numeric value could be entered there in the nfo file so WatchedList would at least mark them in its database as watched. Time permitting, I'll see if I can add them to those databases for everyone else's benefit.
WatchedList Move to SQL database - Klojum - 2020-01-03
Thread marked solved.