+- 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: Mysql profiles and watched status (/showthread.php?tid=81095)
RE: Mysql profiles and watched status - N4TH4N - 2014-05-12
(2014-05-11, 15:47)sompie Wrote: I've remade my database as deathraiider but xbmc won't read the watched status from the extra users (i've reimported data from the 75 database in the globalvideo table)
Sorry, but that tutorial is assuming that you are starting with a new database.
If you want send me a SQL dump, i can have a look at it. Also not sure if its a typo but i use a globalfiles table not globalvideo. Either way i may be able to help.
Also, i noted that there are only 3 differences between the old 75 and the new 78 databases which are to do with StereoMode. So it may be easier to just add those to you old existing working database and adjust from 75 to 78.
settings, StereoMode
settings, StereoInvert
streamdetails, strStereoMode
RE: Mysql profiles and watched status - sompie - 2014-05-12
I know that your tutorial is for creating a new database but I used your system for frodo. I thought if i create a new database and the re-import the previous data into globalfiles(and i just mistype the word :-)) it would work. I will send a dump in a pm.
If you edit the previous database (75) you also have to edit every view to watch for 78 database instead of 75 database
RE: Mysql profiles and watched status - schumi2004 - 2014-06-01
(2013-12-01, 23:18)bakslash Wrote: Here is a workaround that does not need as much space as two databases.
I have read this whole thread but it's unclear for me what t use for existing database and there watch statuses for Gotham.
My current situation is that i want to use 2 profiles and don't want to start from scratch but have a copy of the watched statuses and start from there.
If I understand correctly this is for resume points only and not watched statuses correct?
/edit: I now know it's for watched status also, didn't read the next two post were it says it does also keep track of watched.
Is there a guide that explains step by step what to do for my situation (and I'm probably not the only one with this setup)
I don't want to screw up existing database
/edit:
I also have a difficult situation. It is possible that a library update is triggered on the main database update system when a other user then master is logged in. What will happen if that's the case? Ideal would be that database can be updated on all users but that only watched status etc is being kept separate.
RE: Mysql profiles and watched status - yosubis - 2014-06-02
I followed bohdans way. Really liked it but as said before, it doesn't keep the watched state. The reason is the watched_history table doesn't have the file_id for the files in the library, so it can't write the changes. Is there a way to add a row per file_id as the query is sent?
RE: Mysql profiles and watched status - yosubis - 2014-06-03
OK, Managed to get it working.
I replaced the line:
Code:
CREATE TABLE watched_history (idFile int(11) NOT NULL,playCount int(11) DEFAULT NULL,lastPlayed text,PRIMARY KEY (idFile)) ENGINE=InnoDB DEFAULT CHARSET=utf8;with (Pretty sure that not the way to do it, but it worked):
Code:
CREATE TABLE watched_history (idFile int(11) NOT NULL,playCount int(11) DEFAULT NULL,lastPlayed text,PRIMARY KEY (idFile)) ENGINE=InnoDB DEFAULT CHARSET=utf8 SELECT idFile FROM MyVideos78.files;And added triggers to the original database (that of Master Profile).
One to add a row when a new file is added:
Code:
CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
FOR EACH ROW
INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);And one to delete when the row is deleted from the original database:
Code:
CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
FOR EACH ROW
DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;RE: Mysql profiles and watched status - notch - 2014-06-08
(2014-06-03, 02:38)yosubis Wrote: One to add a row when a new file is added:Your code is working well, although you may have difficulties with more databases (User2Videos etc.) - e.g. MariaDB doesn't support multiple triggers with the same event for the same table (not sure about MySQL). Here is my fix for this situation:
Code:CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
FOR EACH ROW
INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);
And one to delete when the row is deleted from the original database:
Code:CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
FOR EACH ROW
DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
Code:
CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
FOR EACH ROW BEGIN
INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);
INSERT INTO User2Videos78.watched_history(idFile) VALUES (NEW.idFile);
ENDCode:
CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
FOR EACH ROW BEGIN
DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
DELETE FROM User2Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
ENDRE: Mysql profiles and watched status - XBraMC - 2014-08-30
Hi,
First of all, I would like to thank you all for the help I already got from you :-D
Second, I looked already on the forum, and I’m not quite sure if I could post a own thread…
Third, I will gladly spend hours in playing, toying and fixing this, but I first wanted to get some information and the opinion of some experienced people ;-)
First a little inventory:
1 desktop computer, with all the files for sharing between XBMC-devices.
this is my main computer, MySQL is running, a ftp-server, Couch potato, Sick beard and SABNZB)
this pc contains my main movie-, series- and music-directory (accessible through SMB share and FTP)
connected to home-network, fixed IP
Notebook1, always connected in home-network
Notebook2, portable/traveling
smartphone, portable/traveling
tablet, portable/traveling.
With the help of you guys, I already installed a shared library, first home network only. Watch-status, pause-status, everything worked perfectly and quick.
in order to use my XBMC library from everywhere (like friends and family, streaming) I changed in my library the file path’s from the sources from SMB to FTP. This is working, just fine BUT.. (and I guess some of you see it already coming) since I changed this, the systems within my home network I experienced buffering, waiting etc. it is just not working flawless..
So I came up with the following idea:
I create 2 almost Identical libraries in MySQL, with only one different setting: The File path.
for my main desktop and notebook1, I will only use the database with the SMB file paths, for my portable devices I will use the library with the FTP-file paths.
and, to finish this off, I will synchronize the watch-status, between both libraries.
So, my question is, before a demolish my already working XBMC configuration, is this plan going to work?
Is it possible to create 2 different libraries (I thought maybe by setting up two profiles, one for portable with FTP file paths, and one for ‘local’ using SMB file path)?
Is it possible to add within my existing SQL a new database for the SMB library? If this is possible, how can I be sure that profile: local is using the database with the smb paths, and de portable profile is using the database with the FTP paths?
And is it possible to share the watch-status between to libraries? This last one isn’t that important, but, this whole ‘problem’ is just a matter of fine tuning, so it’s better to be perfect.
So, I hope somebody could advice my on this matter, there is no deadline, so I do have the time !!
Thanks for taking the time to help me ;-)
RE: Mysql profiles and watched status - ferdinand - 2014-09-30
(2014-05-11, 08:03)N4TH4N Wrote: Hey Guys,
Ive been running XBMC since the beginning on the original XBOX and have come along way since then.
I developed a custom 5 user SQL database structure based on what others have done but put my own spin on it.
I have been using it for over a year without any problems.
Long story short it uses a master database with 4 extra databases linked to it.
Each of the 5 databases have their own bookmarks and watched status. Other then that they are identical.
Ive tried to make it as simple as possible to understand. So here goes.
Get XBMC to create a database file called "a78" (Just add "a" as the database name in advancedsettings.xml and XBMC will create it).
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>a</name>
</videodatabase>
</advancedsettings>
CLOSE XBMC
Run the following query and your almost done:
Now that were done with the SQL side of things, you just need to create 4 extra profiles via XBMC and give each of them an advancedsettings.xml file.
You can choose to run 2-5 profiles with this setup, the choice is yours.
I am planning to put together a tutorial on how to setup a "Master XBMC Virtual Machine" on Ubuntu 14.04 LTS Desktop with XBMC 13, MySQL and Squid. If thats something your interested in PM me and it might happen faster.
If this has helped you feel free to rep+
Thanks
RE: Mysql profiles and watched status - Lumute - 2014-10-22
Hi Everyone!
I found this thread looking for a way to have separated watched functionality for different profiles. As good as the separate databases, views and synchronization stuff works, this seems to me like a very comboluted and non practical solution that will easily break after upgrades, etc. It also seems to me like this would be a very usefull feature and one many users would benefit from. Is there any reason why a propper solution which adds support to independant watched flags per profile in the database not been implemented or added to the roadmap?
Please, don't get me wrong, I'm not complaining, I love XBMC and appreciate the developers for their hard work. I do know developers are busy and they do what they do from the goodness in their hearts, but I do also know they listen to the community and implement things people want. So just wondering if any formal request has been made to add such a feature to the roadmap...
Thanks!
RE: Mysql profiles and watched status - gaxander - 2014-10-30
(2014-05-11, 08:03)N4TH4N Wrote: Hey Guys,
Ive been running XBMC since the beginning on the original XBOX and have come along way since then.
I developed a custom 5 user SQL database structure based on what others have done but put my own spin on it.
I have been using it for over a year without any problems.
Long story short it uses a master database with 4 extra databases linked to it.
Each of the 5 databases have their own bookmarks and watched status. Other then that they are identical.
Ive tried to make it as simple as possible to understand. So here goes.
Get XBMC to create a database file called "a78" (Just add "a" as the database name in advancedsettings.xml and XBMC will create it).
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>a</name>
</videodatabase>
</advancedsettings>
CLOSE XBMC
Run the following query and your almost done:
Code:RENAME TABLE `a78`.`files` to `a78`.`globalfiles`;
ALTER TABLE `a78`.`globalfiles` CHANGE playCount PlayCount1 INT;
ALTER TABLE `a78`.`globalfiles` CHANGE lastPlayed lastPlayed1 TEXT;
ALTER TABLE `a78`.`globalfiles` ADD playCount2 INT(11) AFTER lastPlayed1;
ALTER TABLE `a78`.`globalfiles` ADD lastPlayed2 TEXT AFTER playCount2;
ALTER TABLE `a78`.`globalfiles` ADD playCount3 INT(11) AFTER lastPlayed2;
ALTER TABLE `a78`.`globalfiles` ADD lastPlayed3 TEXT AFTER playCount3;
ALTER TABLE `a78`.`globalfiles` ADD playCount4 INT(11) AFTER lastPlayed3;
ALTER TABLE `a78`.`globalfiles` ADD lastPlayed4 TEXT AFTER playCount4;
ALTER TABLE `a78`.`globalfiles` ADD playCount5 INT(11) AFTER lastPlayed4;
ALTER TABLE `a78`.`globalfiles` ADD lastPlayed5 TEXT AFTER playCount5;
CREATE VIEW `a78`.`files`
AS SELECT
`a78`.`globalfiles`.`idFile` AS `idFile`,
`a78`.`globalfiles`.`idPath` AS `idPath`,
`a78`.`globalfiles`.`strFilename` AS `strFilename`,
`a78`.`globalfiles`.`playCount1` AS `playCount`,
`a78`.`globalfiles`.`lastPlayed1` AS `lastPlayed`,
`a78`.`globalfiles`.`dateAdded` AS `dateAdded`
FROM `a78`.`globalfiles`;
CREATE DATABASE b78;
CREATE VIEW `b78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;
CREATE VIEW `b78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;
CREATE VIEW `b78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;
CREATE VIEW `b78`.`actors` AS SELECT * FROM `a78`.`actors`;
CREATE VIEW `b78`.`art` AS SELECT * FROM `a78`.`art`;
CREATE VIEW `b78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;
CREATE VIEW `b78`.`country` AS SELECT * FROM `a78`.`country`;
CREATE VIEW `b78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;
CREATE VIEW `b78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;
CREATE VIEW `b78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;
CREATE VIEW `b78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;
CREATE VIEW `b78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;
CREATE VIEW `b78`.`episode` AS SELECT * FROM `a78`.`episode`;
CREATE VIEW `b78`.`genre` AS SELECT * FROM `a78`.`genre`;
CREATE VIEW `b78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;
CREATE VIEW `b78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;
CREATE VIEW `b78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;
CREATE VIEW `b78`.`movie` AS SELECT * FROM `a78`.`movie`;
CREATE VIEW `b78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;
CREATE VIEW `b78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;
CREATE VIEW `b78`.`path` AS SELECT * FROM `a78`.`path`;
CREATE VIEW `b78`.`seasons` AS SELECT * FROM `a78`.`seasons`;
CREATE VIEW `b78`.`sets` AS SELECT * FROM `a78`.`sets`;
CREATE VIEW `b78`.`settings` AS SELECT * FROM `a78`.`settings`;
CREATE VIEW `b78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;
CREATE VIEW `b78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;
CREATE VIEW `b78`.`studio` AS SELECT * FROM `a78`.`studio`;
CREATE VIEW `b78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;
CREATE VIEW `b78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;
CREATE VIEW `b78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;
CREATE VIEW `b78`.`tag` AS SELECT * FROM `a78`.`tag`;
CREATE VIEW `b78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;
CREATE VIEW `b78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;
CREATE VIEW `b78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;
CREATE VIEW `b78`.`version` AS SELECT * FROM `a78`.`version`;
CREATE VIEW `b78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;
CREATE VIEW `b78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;
CREATE VIEW `b78`.`files`
AS SELECT
`a78`.`globalfiles`.`idFile` AS `idFile`,
`a78`.`globalfiles`.`idPath` AS `idPath`,
`a78`.`globalfiles`.`strFilename` AS `strFilename`,
`a78`.`globalfiles`.`playCount2` AS `playCount`,
`a78`.`globalfiles`.`lastPlayed2` AS `lastPlayed`,
`a78`.`globalfiles`.`dateAdded` AS `dateAdded`
FROM `a78`.`globalfiles`;
CREATE TABLE `b78`.`bookmark` (
`idBookmark` int(11) NOT NULL AUTO_INCREMENT,
`idFile` int(11) DEFAULT NULL,
`timeInSeconds` double DEFAULT NULL,
`totalTimeInSeconds` double DEFAULT NULL,
`thumbNailImage` text,
`player` text,
`playerState` text,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookmark`),
KEY `ix_bookmark` (`idFile`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW `b78`.`episodeview`
AS SELECT
`episode`.`idEpisode` AS `idEpisode`,
`episode`.`idFile` AS `idFile`,
`episode`.`c00` AS `c00`,
`episode`.`c01` AS `c01`,
`episode`.`c02` AS `c02`,
`episode`.`c03` AS `c03`,
`episode`.`c04` AS `c04`,
`episode`.`c05` AS `c05`,
`episode`.`c06` AS `c06`,
`episode`.`c07` AS `c07`,
`episode`.`c08` AS `c08`,
`episode`.`c09` AS `c09`,
`episode`.`c10` AS `c10`,
`episode`.`c11` AS `c11`,
`episode`.`c12` AS `c12`,
`episode`.`c13` AS `c13`,
`episode`.`c14` AS `c14`,
`episode`.`c15` AS `c15`,
`episode`.`c16` AS `c16`,
`episode`.`c17` AS `c17`,
`episode`.`c18` AS `c18`,
`episode`.`c19` AS `c19`,
`episode`.`c20` AS `c20`,
`episode`.`c21` AS `c21`,
`episode`.`c22` AS `c22`,
`episode`.`c23` AS `c23`,
`episode`.`idShow` AS `idShow`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`tvshow`.`c00` AS `strTitle`,
`tvshow`.`c14` AS `strStudio`,
`tvshow`.`c05` AS `premiered`,
`tvshow`.`c13` AS `mpaa`,
`tvshow`.`c16` AS `strShowPath`,
`b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`seasons`.`idSeason` AS `idSeason`
FROM (((((`b78`.`episode` join `b78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `b78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `b78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `b78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));
CREATE VIEW `b78`.`movieview`
AS SELECT
`movie`.`idMovie` AS `idMovie`,
`movie`.`idFile` AS `idFile`,
`movie`.`c00` AS `c00`,
`movie`.`c01` AS `c01`,
`movie`.`c02` AS `c02`,
`movie`.`c03` AS `c03`,
`movie`.`c04` AS `c04`,
`movie`.`c05` AS `c05`,
`movie`.`c06` AS `c06`,
`movie`.`c07` AS `c07`,
`movie`.`c08` AS `c08`,
`movie`.`c09` AS `c09`,
`movie`.`c10` AS `c10`,
`movie`.`c11` AS `c11`,
`movie`.`c12` AS `c12`,
`movie`.`c13` AS `c13`,
`movie`.`c14` AS `c14`,
`movie`.`c15` AS `c15`,
`movie`.`c16` AS `c16`,
`movie`.`c17` AS `c17`,
`movie`.`c18` AS `c18`,
`movie`.`c19` AS `c19`,
`movie`.`c20` AS `c20`,
`movie`.`c21` AS `c21`,
`movie`.`c22` AS `c22`,
`movie`.`c23` AS `c23`,
`movie`.`idSet` AS `idSet`,
`sets`.`strSet` AS `strSet`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM ((((`b78`.`movie` left join `b78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `b78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `b78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));
CREATE VIEW `b78`.`musicvideoview`
AS SELECT
`musicvideo`.`idMVideo` AS `idMVideo`,
`musicvideo`.`idFile` AS `idFile`,
`musicvideo`.`c00` AS `c00`,
`musicvideo`.`c01` AS `c01`,
`musicvideo`.`c02` AS `c02`,
`musicvideo`.`c03` AS `c03`,
`musicvideo`.`c04` AS `c04`,
`musicvideo`.`c05` AS `c05`,
`musicvideo`.`c06` AS `c06`,
`musicvideo`.`c07` AS `c07`,
`musicvideo`.`c08` AS `c08`,
`musicvideo`.`c09` AS `c09`,
`musicvideo`.`c10` AS `c10`,
`musicvideo`.`c11` AS `c11`,
`musicvideo`.`c12` AS `c12`,
`musicvideo`.`c13` AS `c13`,
`musicvideo`.`c14` AS `c14`,
`musicvideo`.`c15` AS `c15`,
`musicvideo`.`c16` AS `c16`,
`musicvideo`.`c17` AS `c17`,
`musicvideo`.`c18` AS `c18`,
`musicvideo`.`c19` AS `c19`,
`musicvideo`.`c20` AS `c20`,
`musicvideo`.`c21` AS `c21`,
`musicvideo`.`c22` AS `c22`,
`musicvideo`.`c23` AS `c23`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM (((`b78`.`musicvideo` join `b78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `b78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));
CREATE VIEW `b78`.`tvshowview`
AS SELECT
`tvshow`.`idShow` AS `idShow`,
`tvshow`.`c00` AS `c00`,
`tvshow`.`c01` AS `c01`,
`tvshow`.`c02` AS `c02`,
`tvshow`.`c03` AS `c03`,
`tvshow`.`c04` AS `c04`,
`tvshow`.`c05` AS `c05`,
`tvshow`.`c06` AS `c06`,
`tvshow`.`c07` AS `c07`,
`tvshow`.`c08` AS `c08`,
`tvshow`.`c09` AS `c09`,
`tvshow`.`c10` AS `c10`,
`tvshow`.`c11` AS `c11`,
`tvshow`.`c12` AS `c12`,
`tvshow`.`c13` AS `c13`,
`tvshow`.`c14` AS `c14`,
`tvshow`.`c15` AS `c15`,
`tvshow`.`c16` AS `c16`,
`tvshow`.`c17` AS `c17`,
`tvshow`.`c18` AS `c18`,
`tvshow`.`c19` AS `c19`,
`tvshow`.`c20` AS `c20`,
`tvshow`.`c21` AS `c21`,
`tvshow`.`c22` AS `c22`,
`tvshow`.`c23` AS `c23`,
`path`.`strPath` AS `strPath`,
`path`.`dateAdded` AS `dateAdded`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`
FROM ((((`b78`.`tvshow` left join `b78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `b78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `b78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `b78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;
CREATE DATABASE c78;
CREATE VIEW `c78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;
CREATE VIEW `c78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;
CREATE VIEW `c78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;
CREATE VIEW `c78`.`actors` AS SELECT * FROM `a78`.`actors`;
CREATE VIEW `c78`.`art` AS SELECT * FROM `a78`.`art`;
CREATE VIEW `c78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;
CREATE VIEW `c78`.`country` AS SELECT * FROM `a78`.`country`;
CREATE VIEW `c78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;
CREATE VIEW `c78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;
CREATE VIEW `c78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;
CREATE VIEW `c78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;
CREATE VIEW `c78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;
CREATE VIEW `c78`.`episode` AS SELECT * FROM `a78`.`episode`;
CREATE VIEW `c78`.`genre` AS SELECT * FROM `a78`.`genre`;
CREATE VIEW `c78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;
CREATE VIEW `c78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;
CREATE VIEW `c78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;
CREATE VIEW `c78`.`movie` AS SELECT * FROM `a78`.`movie`;
CREATE VIEW `c78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;
CREATE VIEW `c78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;
CREATE VIEW `c78`.`path` AS SELECT * FROM `a78`.`path`;
CREATE VIEW `c78`.`seasons` AS SELECT * FROM `a78`.`seasons`;
CREATE VIEW `c78`.`sets` AS SELECT * FROM `a78`.`sets`;
CREATE VIEW `c78`.`settings` AS SELECT * FROM `a78`.`settings`;
CREATE VIEW `c78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;
CREATE VIEW `c78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;
CREATE VIEW `c78`.`studio` AS SELECT * FROM `a78`.`studio`;
CREATE VIEW `c78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;
CREATE VIEW `c78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;
CREATE VIEW `c78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;
CREATE VIEW `c78`.`tag` AS SELECT * FROM `a78`.`tag`;
CREATE VIEW `c78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;
CREATE VIEW `c78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;
CREATE VIEW `c78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;
CREATE VIEW `c78`.`version` AS SELECT * FROM `a78`.`version`;
CREATE VIEW `c78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;
CREATE VIEW `c78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;
CREATE VIEW `c78`.`files`
AS SELECT
`a78`.`globalfiles`.`idFile` AS `idFile`,
`a78`.`globalfiles`.`idPath` AS `idPath`,
`a78`.`globalfiles`.`strFilename` AS `strFilename`,
`a78`.`globalfiles`.`playCount3` AS `playCount`,
`a78`.`globalfiles`.`lastPlayed3` AS `lastPlayed`,
`a78`.`globalfiles`.`dateAdded` AS `dateAdded`
FROM `a78`.`globalfiles`;
CREATE TABLE `c78`.`bookmark` (
`idBookmark` int(11) NOT NULL AUTO_INCREMENT,
`idFile` int(11) DEFAULT NULL,
`timeInSeconds` double DEFAULT NULL,
`totalTimeInSeconds` double DEFAULT NULL,
`thumbNailImage` text,
`player` text,
`playerState` text,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookmark`),
KEY `ix_bookmark` (`idFile`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW `c78`.`episodeview`
AS SELECT
`episode`.`idEpisode` AS `idEpisode`,
`episode`.`idFile` AS `idFile`,
`episode`.`c00` AS `c00`,
`episode`.`c01` AS `c01`,
`episode`.`c02` AS `c02`,
`episode`.`c03` AS `c03`,
`episode`.`c04` AS `c04`,
`episode`.`c05` AS `c05`,
`episode`.`c06` AS `c06`,
`episode`.`c07` AS `c07`,
`episode`.`c08` AS `c08`,
`episode`.`c09` AS `c09`,
`episode`.`c10` AS `c10`,
`episode`.`c11` AS `c11`,
`episode`.`c12` AS `c12`,
`episode`.`c13` AS `c13`,
`episode`.`c14` AS `c14`,
`episode`.`c15` AS `c15`,
`episode`.`c16` AS `c16`,
`episode`.`c17` AS `c17`,
`episode`.`c18` AS `c18`,
`episode`.`c19` AS `c19`,
`episode`.`c20` AS `c20`,
`episode`.`c21` AS `c21`,
`episode`.`c22` AS `c22`,
`episode`.`c23` AS `c23`,
`episode`.`idShow` AS `idShow`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`tvshow`.`c00` AS `strTitle`,
`tvshow`.`c14` AS `strStudio`,
`tvshow`.`c05` AS `premiered`,
`tvshow`.`c13` AS `mpaa`,
`tvshow`.`c16` AS `strShowPath`,
`c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`seasons`.`idSeason` AS `idSeason`
FROM (((((`c78`.`episode` join `c78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `c78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `c78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `c78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));
CREATE VIEW `c78`.`movieview`
AS SELECT
`movie`.`idMovie` AS `idMovie`,
`movie`.`idFile` AS `idFile`,
`movie`.`c00` AS `c00`,
`movie`.`c01` AS `c01`,
`movie`.`c02` AS `c02`,
`movie`.`c03` AS `c03`,
`movie`.`c04` AS `c04`,
`movie`.`c05` AS `c05`,
`movie`.`c06` AS `c06`,
`movie`.`c07` AS `c07`,
`movie`.`c08` AS `c08`,
`movie`.`c09` AS `c09`,
`movie`.`c10` AS `c10`,
`movie`.`c11` AS `c11`,
`movie`.`c12` AS `c12`,
`movie`.`c13` AS `c13`,
`movie`.`c14` AS `c14`,
`movie`.`c15` AS `c15`,
`movie`.`c16` AS `c16`,
`movie`.`c17` AS `c17`,
`movie`.`c18` AS `c18`,
`movie`.`c19` AS `c19`,
`movie`.`c20` AS `c20`,
`movie`.`c21` AS `c21`,
`movie`.`c22` AS `c22`,
`movie`.`c23` AS `c23`,
`movie`.`idSet` AS `idSet`,
`sets`.`strSet` AS `strSet`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM ((((`c78`.`movie` left join `c78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `c78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `c78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));
CREATE VIEW `c78`.`musicvideoview`
AS SELECT
`musicvideo`.`idMVideo` AS `idMVideo`,
`musicvideo`.`idFile` AS `idFile`,
`musicvideo`.`c00` AS `c00`,
`musicvideo`.`c01` AS `c01`,
`musicvideo`.`c02` AS `c02`,
`musicvideo`.`c03` AS `c03`,
`musicvideo`.`c04` AS `c04`,
`musicvideo`.`c05` AS `c05`,
`musicvideo`.`c06` AS `c06`,
`musicvideo`.`c07` AS `c07`,
`musicvideo`.`c08` AS `c08`,
`musicvideo`.`c09` AS `c09`,
`musicvideo`.`c10` AS `c10`,
`musicvideo`.`c11` AS `c11`,
`musicvideo`.`c12` AS `c12`,
`musicvideo`.`c13` AS `c13`,
`musicvideo`.`c14` AS `c14`,
`musicvideo`.`c15` AS `c15`,
`musicvideo`.`c16` AS `c16`,
`musicvideo`.`c17` AS `c17`,
`musicvideo`.`c18` AS `c18`,
`musicvideo`.`c19` AS `c19`,
`musicvideo`.`c20` AS `c20`,
`musicvideo`.`c21` AS `c21`,
`musicvideo`.`c22` AS `c22`,
`musicvideo`.`c23` AS `c23`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM (((`c78`.`musicvideo` join `c78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `c78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));
CREATE VIEW `c78`.`tvshowview`
AS SELECT
`tvshow`.`idShow` AS `idShow`,
`tvshow`.`c00` AS `c00`,
`tvshow`.`c01` AS `c01`,
`tvshow`.`c02` AS `c02`,
`tvshow`.`c03` AS `c03`,
`tvshow`.`c04` AS `c04`,
`tvshow`.`c05` AS `c05`,
`tvshow`.`c06` AS `c06`,
`tvshow`.`c07` AS `c07`,
`tvshow`.`c08` AS `c08`,
`tvshow`.`c09` AS `c09`,
`tvshow`.`c10` AS `c10`,
`tvshow`.`c11` AS `c11`,
`tvshow`.`c12` AS `c12`,
`tvshow`.`c13` AS `c13`,
`tvshow`.`c14` AS `c14`,
`tvshow`.`c15` AS `c15`,
`tvshow`.`c16` AS `c16`,
`tvshow`.`c17` AS `c17`,
`tvshow`.`c18` AS `c18`,
`tvshow`.`c19` AS `c19`,
`tvshow`.`c20` AS `c20`,
`tvshow`.`c21` AS `c21`,
`tvshow`.`c22` AS `c22`,
`tvshow`.`c23` AS `c23`,
`path`.`strPath` AS `strPath`,
`path`.`dateAdded` AS `dateAdded`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`
FROM ((((`c78`.`tvshow` left join `c78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `c78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `c78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `c78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;
CREATE DATABASE d78;
CREATE VIEW `d78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;
CREATE VIEW `d78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;
CREATE VIEW `d78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;
CREATE VIEW `d78`.`actors` AS SELECT * FROM `a78`.`actors`;
CREATE VIEW `d78`.`art` AS SELECT * FROM `a78`.`art`;
CREATE VIEW `d78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;
CREATE VIEW `d78`.`country` AS SELECT * FROM `a78`.`country`;
CREATE VIEW `d78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;
CREATE VIEW `d78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;
CREATE VIEW `d78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;
CREATE VIEW `d78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;
CREATE VIEW `d78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;
CREATE VIEW `d78`.`episode` AS SELECT * FROM `a78`.`episode`;
CREATE VIEW `d78`.`genre` AS SELECT * FROM `a78`.`genre`;
CREATE VIEW `d78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;
CREATE VIEW `d78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;
CREATE VIEW `d78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;
CREATE VIEW `d78`.`movie` AS SELECT * FROM `a78`.`movie`;
CREATE VIEW `d78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;
CREATE VIEW `d78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;
CREATE VIEW `d78`.`path` AS SELECT * FROM `a78`.`path`;
CREATE VIEW `d78`.`seasons` AS SELECT * FROM `a78`.`seasons`;
CREATE VIEW `d78`.`sets` AS SELECT * FROM `a78`.`sets`;
CREATE VIEW `d78`.`settings` AS SELECT * FROM `a78`.`settings`;
CREATE VIEW `d78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;
CREATE VIEW `d78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;
CREATE VIEW `d78`.`studio` AS SELECT * FROM `a78`.`studio`;
CREATE VIEW `d78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;
CREATE VIEW `d78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;
CREATE VIEW `d78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;
CREATE VIEW `d78`.`tag` AS SELECT * FROM `a78`.`tag`;
CREATE VIEW `d78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;
CREATE VIEW `d78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;
CREATE VIEW `d78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;
CREATE VIEW `d78`.`version` AS SELECT * FROM `a78`.`version`;
CREATE VIEW `d78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;
CREATE VIEW `d78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;
CREATE VIEW `d78`.`files`
AS SELECT
`a78`.`globalfiles`.`idFile` AS `idFile`,
`a78`.`globalfiles`.`idPath` AS `idPath`,
`a78`.`globalfiles`.`strFilename` AS `strFilename`,
`a78`.`globalfiles`.`playCount4` AS `playCount`,
`a78`.`globalfiles`.`lastPlayed4` AS `lastPlayed`,
`a78`.`globalfiles`.`dateAdded` AS `dateAdded`
FROM `a78`.`globalfiles`;
CREATE TABLE `d78`.`bookmark` (
`idBookmark` int(11) NOT NULL AUTO_INCREMENT,
`idFile` int(11) DEFAULT NULL,
`timeInSeconds` double DEFAULT NULL,
`totalTimeInSeconds` double DEFAULT NULL,
`thumbNailImage` text,
`player` text,
`playerState` text,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookmark`),
KEY `ix_bookmark` (`idFile`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW `d78`.`episodeview`
AS SELECT
`episode`.`idEpisode` AS `idEpisode`,
`episode`.`idFile` AS `idFile`,
`episode`.`c00` AS `c00`,
`episode`.`c01` AS `c01`,
`episode`.`c02` AS `c02`,
`episode`.`c03` AS `c03`,
`episode`.`c04` AS `c04`,
`episode`.`c05` AS `c05`,
`episode`.`c06` AS `c06`,
`episode`.`c07` AS `c07`,
`episode`.`c08` AS `c08`,
`episode`.`c09` AS `c09`,
`episode`.`c10` AS `c10`,
`episode`.`c11` AS `c11`,
`episode`.`c12` AS `c12`,
`episode`.`c13` AS `c13`,
`episode`.`c14` AS `c14`,
`episode`.`c15` AS `c15`,
`episode`.`c16` AS `c16`,
`episode`.`c17` AS `c17`,
`episode`.`c18` AS `c18`,
`episode`.`c19` AS `c19`,
`episode`.`c20` AS `c20`,
`episode`.`c21` AS `c21`,
`episode`.`c22` AS `c22`,
`episode`.`c23` AS `c23`,
`episode`.`idShow` AS `idShow`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`tvshow`.`c00` AS `strTitle`,
`tvshow`.`c14` AS `strStudio`,
`tvshow`.`c05` AS `premiered`,
`tvshow`.`c13` AS `mpaa`,
`tvshow`.`c16` AS `strShowPath`,
`d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`seasons`.`idSeason` AS `idSeason`
FROM (((((`d78`.`episode` join `d78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `d78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `d78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `d78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));
CREATE VIEW `d78`.`movieview`
AS SELECT
`movie`.`idMovie` AS `idMovie`,
`movie`.`idFile` AS `idFile`,
`movie`.`c00` AS `c00`,
`movie`.`c01` AS `c01`,
`movie`.`c02` AS `c02`,
`movie`.`c03` AS `c03`,
`movie`.`c04` AS `c04`,
`movie`.`c05` AS `c05`,
`movie`.`c06` AS `c06`,
`movie`.`c07` AS `c07`,
`movie`.`c08` AS `c08`,
`movie`.`c09` AS `c09`,
`movie`.`c10` AS `c10`,
`movie`.`c11` AS `c11`,
`movie`.`c12` AS `c12`,
`movie`.`c13` AS `c13`,
`movie`.`c14` AS `c14`,
`movie`.`c15` AS `c15`,
`movie`.`c16` AS `c16`,
`movie`.`c17` AS `c17`,
`movie`.`c18` AS `c18`,
`movie`.`c19` AS `c19`,
`movie`.`c20` AS `c20`,
`movie`.`c21` AS `c21`,
`movie`.`c22` AS `c22`,
`movie`.`c23` AS `c23`,
`movie`.`idSet` AS `idSet`,
`sets`.`strSet` AS `strSet`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM ((((`d78`.`movie` left join `d78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `d78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `d78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));
CREATE VIEW `d78`.`musicvideoview`
AS SELECT
`musicvideo`.`idMVideo` AS `idMVideo`,
`musicvideo`.`idFile` AS `idFile`,
`musicvideo`.`c00` AS `c00`,
`musicvideo`.`c01` AS `c01`,
`musicvideo`.`c02` AS `c02`,
`musicvideo`.`c03` AS `c03`,
`musicvideo`.`c04` AS `c04`,
`musicvideo`.`c05` AS `c05`,
`musicvideo`.`c06` AS `c06`,
`musicvideo`.`c07` AS `c07`,
`musicvideo`.`c08` AS `c08`,
`musicvideo`.`c09` AS `c09`,
`musicvideo`.`c10` AS `c10`,
`musicvideo`.`c11` AS `c11`,
`musicvideo`.`c12` AS `c12`,
`musicvideo`.`c13` AS `c13`,
`musicvideo`.`c14` AS `c14`,
`musicvideo`.`c15` AS `c15`,
`musicvideo`.`c16` AS `c16`,
`musicvideo`.`c17` AS `c17`,
`musicvideo`.`c18` AS `c18`,
`musicvideo`.`c19` AS `c19`,
`musicvideo`.`c20` AS `c20`,
`musicvideo`.`c21` AS `c21`,
`musicvideo`.`c22` AS `c22`,
`musicvideo`.`c23` AS `c23`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM (((`d78`.`musicvideo` join `d78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `d78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));
CREATE VIEW `d78`.`tvshowview`
AS SELECT
`tvshow`.`idShow` AS `idShow`,
`tvshow`.`c00` AS `c00`,
`tvshow`.`c01` AS `c01`,
`tvshow`.`c02` AS `c02`,
`tvshow`.`c03` AS `c03`,
`tvshow`.`c04` AS `c04`,
`tvshow`.`c05` AS `c05`,
`tvshow`.`c06` AS `c06`,
`tvshow`.`c07` AS `c07`,
`tvshow`.`c08` AS `c08`,
`tvshow`.`c09` AS `c09`,
`tvshow`.`c10` AS `c10`,
`tvshow`.`c11` AS `c11`,
`tvshow`.`c12` AS `c12`,
`tvshow`.`c13` AS `c13`,
`tvshow`.`c14` AS `c14`,
`tvshow`.`c15` AS `c15`,
`tvshow`.`c16` AS `c16`,
`tvshow`.`c17` AS `c17`,
`tvshow`.`c18` AS `c18`,
`tvshow`.`c19` AS `c19`,
`tvshow`.`c20` AS `c20`,
`tvshow`.`c21` AS `c21`,
`tvshow`.`c22` AS `c22`,
`tvshow`.`c23` AS `c23`,
`path`.`strPath` AS `strPath`,
`path`.`dateAdded` AS `dateAdded`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`
FROM ((((`d78`.`tvshow` left join `d78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `d78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `d78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `d78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;
CREATE DATABASE e78;
CREATE VIEW `e78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;
CREATE VIEW `e78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;
CREATE VIEW `e78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;
CREATE VIEW `e78`.`actors` AS SELECT * FROM `a78`.`actors`;
CREATE VIEW `e78`.`art` AS SELECT * FROM `a78`.`art`;
CREATE VIEW `e78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;
CREATE VIEW `e78`.`country` AS SELECT * FROM `a78`.`country`;
CREATE VIEW `e78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;
CREATE VIEW `e78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;
CREATE VIEW `e78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;
CREATE VIEW `e78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;
CREATE VIEW `e78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;
CREATE VIEW `e78`.`episode` AS SELECT * FROM `a78`.`episode`;
CREATE VIEW `e78`.`genre` AS SELECT * FROM `a78`.`genre`;
CREATE VIEW `e78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;
CREATE VIEW `e78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;
CREATE VIEW `e78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;
CREATE VIEW `e78`.`movie` AS SELECT * FROM `a78`.`movie`;
CREATE VIEW `e78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;
CREATE VIEW `e78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;
CREATE VIEW `e78`.`path` AS SELECT * FROM `a78`.`path`;
CREATE VIEW `e78`.`seasons` AS SELECT * FROM `a78`.`seasons`;
CREATE VIEW `e78`.`sets` AS SELECT * FROM `a78`.`sets`;
CREATE VIEW `e78`.`settings` AS SELECT * FROM `a78`.`settings`;
CREATE VIEW `e78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;
CREATE VIEW `e78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;
CREATE VIEW `e78`.`studio` AS SELECT * FROM `a78`.`studio`;
CREATE VIEW `e78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;
CREATE VIEW `e78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;
CREATE VIEW `e78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;
CREATE VIEW `e78`.`tag` AS SELECT * FROM `a78`.`tag`;
CREATE VIEW `e78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;
CREATE VIEW `e78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;
CREATE VIEW `e78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;
CREATE VIEW `e78`.`version` AS SELECT * FROM `a78`.`version`;
CREATE VIEW `e78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;
CREATE VIEW `e78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;
CREATE VIEW `e78`.`files`
AS SELECT
`a78`.`globalfiles`.`idFile` AS `idFile`,
`a78`.`globalfiles`.`idPath` AS `idPath`,
`a78`.`globalfiles`.`strFilename` AS `strFilename`,
`a78`.`globalfiles`.`playCount5` AS `playCount`,
`a78`.`globalfiles`.`lastPlayed5` AS `lastPlayed`,
`a78`.`globalfiles`.`dateAdded` AS `dateAdded`
FROM `a78`.`globalfiles`;
CREATE TABLE `e78`.`bookmark` (
`idBookmark` int(11) NOT NULL AUTO_INCREMENT,
`idFile` int(11) DEFAULT NULL,
`timeInSeconds` double DEFAULT NULL,
`totalTimeInSeconds` double DEFAULT NULL,
`thumbNailImage` text,
`player` text,
`playerState` text,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`idBookmark`),
KEY `ix_bookmark` (`idFile`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE VIEW `e78`.`episodeview`
AS SELECT
`episode`.`idEpisode` AS `idEpisode`,
`episode`.`idFile` AS `idFile`,
`episode`.`c00` AS `c00`,
`episode`.`c01` AS `c01`,
`episode`.`c02` AS `c02`,
`episode`.`c03` AS `c03`,
`episode`.`c04` AS `c04`,
`episode`.`c05` AS `c05`,
`episode`.`c06` AS `c06`,
`episode`.`c07` AS `c07`,
`episode`.`c08` AS `c08`,
`episode`.`c09` AS `c09`,
`episode`.`c10` AS `c10`,
`episode`.`c11` AS `c11`,
`episode`.`c12` AS `c12`,
`episode`.`c13` AS `c13`,
`episode`.`c14` AS `c14`,
`episode`.`c15` AS `c15`,
`episode`.`c16` AS `c16`,
`episode`.`c17` AS `c17`,
`episode`.`c18` AS `c18`,
`episode`.`c19` AS `c19`,
`episode`.`c20` AS `c20`,
`episode`.`c21` AS `c21`,
`episode`.`c22` AS `c22`,
`episode`.`c23` AS `c23`,
`episode`.`idShow` AS `idShow`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`tvshow`.`c00` AS `strTitle`,
`tvshow`.`c14` AS `strStudio`,
`tvshow`.`c05` AS `premiered`,
`tvshow`.`c13` AS `mpaa`,
`tvshow`.`c16` AS `strShowPath`,
`e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`seasons`.`idSeason` AS `idSeason`
FROM (((((`e78`.`episode` join `e78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `e78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `e78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `e78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));
CREATE VIEW `e78`.`movieview`
AS SELECT
`movie`.`idMovie` AS `idMovie`,
`movie`.`idFile` AS `idFile`,
`movie`.`c00` AS `c00`,
`movie`.`c01` AS `c01`,
`movie`.`c02` AS `c02`,
`movie`.`c03` AS `c03`,
`movie`.`c04` AS `c04`,
`movie`.`c05` AS `c05`,
`movie`.`c06` AS `c06`,
`movie`.`c07` AS `c07`,
`movie`.`c08` AS `c08`,
`movie`.`c09` AS `c09`,
`movie`.`c10` AS `c10`,
`movie`.`c11` AS `c11`,
`movie`.`c12` AS `c12`,
`movie`.`c13` AS `c13`,
`movie`.`c14` AS `c14`,
`movie`.`c15` AS `c15`,
`movie`.`c16` AS `c16`,
`movie`.`c17` AS `c17`,
`movie`.`c18` AS `c18`,
`movie`.`c19` AS `c19`,
`movie`.`c20` AS `c20`,
`movie`.`c21` AS `c21`,
`movie`.`c22` AS `c22`,
`movie`.`c23` AS `c23`,
`movie`.`idSet` AS `idSet`,
`sets`.`strSet` AS `strSet`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM ((((`e78`.`movie` left join `e78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `e78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `e78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));
CREATE VIEW `e78`.`musicvideoview`
AS SELECT
`musicvideo`.`idMVideo` AS `idMVideo`,
`musicvideo`.`idFile` AS `idFile`,
`musicvideo`.`c00` AS `c00`,
`musicvideo`.`c01` AS `c01`,
`musicvideo`.`c02` AS `c02`,
`musicvideo`.`c03` AS `c03`,
`musicvideo`.`c04` AS `c04`,
`musicvideo`.`c05` AS `c05`,
`musicvideo`.`c06` AS `c06`,
`musicvideo`.`c07` AS `c07`,
`musicvideo`.`c08` AS `c08`,
`musicvideo`.`c09` AS `c09`,
`musicvideo`.`c10` AS `c10`,
`musicvideo`.`c11` AS `c11`,
`musicvideo`.`c12` AS `c12`,
`musicvideo`.`c13` AS `c13`,
`musicvideo`.`c14` AS `c14`,
`musicvideo`.`c15` AS `c15`,
`musicvideo`.`c16` AS `c16`,
`musicvideo`.`c17` AS `c17`,
`musicvideo`.`c18` AS `c18`,
`musicvideo`.`c19` AS `c19`,
`musicvideo`.`c20` AS `c20`,
`musicvideo`.`c21` AS `c21`,
`musicvideo`.`c22` AS `c22`,
`musicvideo`.`c23` AS `c23`,
`files`.`strFilename` AS `strFileName`,
`path`.`strPath` AS `strPath`,
`files`.`playCount` AS `playCount`,
`files`.`lastPlayed` AS `lastPlayed`,
`files`.`dateAdded` AS `dateAdded`,
`e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM (((`e78`.`musicvideo` join `e78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `e78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));
CREATE VIEW `e78`.`tvshowview`
AS SELECT
`tvshow`.`idShow` AS `idShow`,
`tvshow`.`c00` AS `c00`,
`tvshow`.`c01` AS `c01`,
`tvshow`.`c02` AS `c02`,
`tvshow`.`c03` AS `c03`,
`tvshow`.`c04` AS `c04`,
`tvshow`.`c05` AS `c05`,
`tvshow`.`c06` AS `c06`,
`tvshow`.`c07` AS `c07`,
`tvshow`.`c08` AS `c08`,
`tvshow`.`c09` AS `c09`,
`tvshow`.`c10` AS `c10`,
`tvshow`.`c11` AS `c11`,
`tvshow`.`c12` AS `c12`,
`tvshow`.`c13` AS `c13`,
`tvshow`.`c14` AS `c14`,
`tvshow`.`c15` AS `c15`,
`tvshow`.`c16` AS `c16`,
`tvshow`.`c17` AS `c17`,
`tvshow`.`c18` AS `c18`,
`tvshow`.`c19` AS `c19`,
`tvshow`.`c20` AS `c20`,
`tvshow`.`c21` AS `c21`,
`tvshow`.`c22` AS `c22`,
`tvshow`.`c23` AS `c23`,
`path`.`strPath` AS `strPath`,
`path`.`dateAdded` AS `dateAdded`,max(`files`.`lastPlayed`) AS `lastPlayed`,nullif(count(`episode`.`c12`),0) AS `totalCount`,count(`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `episode`.`c12`),0) AS `totalSeasons`
FROM ((((`e78`.`tvshow` left join `e78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `e78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `e78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `e78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;
Now that were done with the SQL side of things, you just need to create 4 extra profiles via XBMC and give each of them an advancedsettings.xml file.
User 2 (b78)
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>b</name>
</videodatabase>
</advancedsettings>
User 3 (c78)
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>c</name>
</videodatabase>
</advancedsettings>
User 4 (d78)
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>d</name>
</videodatabase>
</advancedsettings>
User 5 (e78)
Code:<advancedsettings>
<videodatabase>
<type>mysql</type>
<host>IPADDRESS</host>
<port>3306</port>
<user>USERNAME</user>
<pass>PASSWORD</pass>
<name>e</name>
</videodatabase>
</advancedsettings>
You can choose to run 2-5 profiles with this setup, the choice is yours.
I am planning to put together a tutorial on how to setup a "Master XBMC Virtual Machine" on Ubuntu 14.04 LTS Desktop with XBMC 13, MySQL and Squid. If thats something your interested in PM me and it might happen faster.
If this has helped you feel free to rep+
Works Like A Charm
Thanks!
Mysql profiles and watched status - Memphiz - 2014-10-30
gosh use pastebin.com for those huge snippets!
RE: Mysql profiles and watched status - ferdinand - 2014-10-31
(2014-10-22, 00:37)Lumute Wrote: Hi Everyone!
I found this thread looking for a way to have separated watched functionality for different profiles. As good as the separate databases, views and synchronization stuff works, this seems to me like a very comboluted and non practical solution that will easily break after upgrades, etc. It also seems to me like this would be a very usefull feature and one many users would benefit from. Is there any reason why a propper solution which adds support to independant watched flags per profile in the database not been implemented or added to the roadmap?
Please, don't get me wrong, I'm not complaining, I love XBMC and appreciate the developers for their hard work. I do know developers are busy and they do what they do from the goodness in their hearts, but I do also know they listen to the community and implement things people want. So just wondering if any formal request has been made to add such a feature to the roadmap...
Thanks!
+1
RE: Mysql profiles and watched status - Ned Scott - 2014-10-31
(2014-10-22, 00:37)Lumute Wrote: Hi Everyone!
I found this thread looking for a way to have separated watched functionality for different profiles. As good as the separate databases, views and synchronization stuff works, this seems to me like a very comboluted and non practical solution that will easily break after upgrades, etc. It also seems to me like this would be a very usefull feature and one many users would benefit from. Is there any reason why a propper solution which adds support to independant watched flags per profile in the database not been implemented or added to the roadmap?
Please, don't get me wrong, I'm not complaining, I love XBMC and appreciate the developers for their hard work. I do know developers are busy and they do what they do from the goodness in their hearts, but I do also know they listen to the community and implement things people want. So just wondering if any formal request has been made to add such a feature to the roadmap...
Thanks!
The short answer is that MySQL will eventually be replaced by other features that are easier to set up and work better for sharing the library, so very few people are interested in expanding MySQL features that will eventually be rewritten with something else.
RE: Mysql profiles and watched status - Kib - 2014-10-31
What Ned said, but replacing 'very few people' with 'basically noone'. I'm sure we would accept proper code that fixed mysql issues, but it is ultimately wasted work.
RE: Mysql profiles and watched status - Nogler - 2015-01-07
hi
I'm using N4TH4N way. Thank you
Is there a method to updating to Helix?