Mysql profiles and watched status

Mysql profiles and watched status - 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: Mysql profiles and watched status (/showthread.php?tid=81095)

Pages: 1 2 3 4 5 6 7 8

RE: Mysql profiles and watched status - Walhalla - 2015-05-29

Good to know. Thanks for posting the solution.

RE: Mysql profiles and watched status - dr-oopie - 2015-07-07

i have a mysql installed on linux but it has multiple databases. how can i point it to the kodi database so its not in the root? i think by default it goes to MyVideos but i want it inside of kodi

thought id edit it, instead of making a MyVideos and those automatically created, i rather then be created inside a Kodi database with the same structure within. so not just to rename MyVideos to Kodi but put MyVideos itself into Kodi database.

RE: Mysql profiles and watched status - unaszplodrmann - 2015-07-28

Just had a look at the new video db schema from Isengard... lots of structural changes. Has anyone updated the linked multi-user setup yet? I'll do it, if not.

RE: Mysql profiles and watched status - Walhalla - 2015-07-29

I had no time to have a look into it. If you have some spare time it would be really cool ;-)

RE: Mysql profiles and watched status - unaszplodrmann - 2015-07-30

Been years since I messed about with SQL queries, but I'm working on it. I think have the existing modifications ported over, but I'd like per-user video settings and resume points, so gonna fiddle some more tomorrow before testing it all.

RE: Mysql profiles and watched status - iainmacleod - 2015-08-21

Just like to say this is a really compelling subject for me. I would really love to see some native support for network based users (central DB) to work in conjunction with MySQL DB. The ability to log in as different users throughout various rooms and retrieve personalized watched status and resume points sounds great - however I will note that while I am typing this, this can all be achieved using Plex.

Since Plex isn't a custom based OS for hardware I prefer to run OpenELEC on my Chromebox and Pi's.

I guess my short term solution is to use PleXBMC.

Where can I submit feature requests? I think that Kodi is awesome but I would love to see some of the functionality provided by spin-offs ported back to Kodi.

RE: Mysql profiles and watched status - Deihmos - 2015-08-21

That has been requested for years. I switched to using emby which has a kodi plugin that syncs with the native kodi database which means you can use any skin with different users unlike plexbmc.

RE: Mysql profiles and watched status - atcronin - 2015-08-22

Here is my script for Kodi 15.0
Only started working with databases in general, and MySQL in particular, last night so please forgive any newbie mistakes.

Tests
1. Library item Add as primary user -> appeared for both users (insert Trigger updated watched_history table)
2. Separate Watched and Playback position -> working for both users
3. Remove item from library as primary user -> removed from both users library (delete Trigger updated bookmark and watched_history tables)
4. Library item Add and Remove as secondary user -> updates primary library (Still need to figure this out)

Code:
/*

Copy database "MyVideos93" to "User1MyVideos93" enabling per user watched status

Replace "User1MyVideos93" with preferred db name

Tested for Kodi 15.0 on MySQL 5.5.44 on ubuntu 14.04.1

**** User Creation Commands ****

CREATE USER 'KodiUserMain' IDENTIFIED BY 'password'; #Primary User

GRANT ALL ON `%MyVideos%`.* TO 'KodiUserMain';

GRANT ALL ON `%MyMusic%`.* TO 'KodiUserMain';

CREATE USER 'KodiUser1' IDENTIFIED BY 'password'; #Additional User

GRANT ALL ON `User1MyVideos%`.* TO 'KodiUser1';

GRANT ALL ON `User1MyMusic%`.* TO 'KodiUser1';

FLUSH PRIVILEGES;

*********************************

Pieced together from various forum posts, blogs and backs of cereal boxes.

Credit to those that actually did the hard work

No guarantees this will work or is even an optimal or recommended solution

*/

CREATE DATABASE User1MyVideos93;

USE User1MyVideos93;

CREATE VIEW `actor_link` AS SELECT * FROM `MyVideos93`.`actor_link`;

CREATE VIEW `actor` AS SELECT * FROM `MyVideos93`.`actor`;

CREATE VIEW `art` AS SELECT * FROM `MyVideos93`.`art`;

CREATE VIEW `country` AS SELECT * FROM `MyVideos93`.`country`;

CREATE VIEW `country_link` AS SELECT * FROM `MyVideos93`.`country_link`;

CREATE VIEW `director_link` AS SELECT * FROM `MyVideos93`.`director_link`;

CREATE VIEW `episode` AS SELECT * FROM `MyVideos93`.`episode`;

CREATE VIEW `genre` AS SELECT * FROM `MyVideos93`.`genre`;

CREATE VIEW `genre_link` AS SELECT * FROM `MyVideos93`.`genre_link`;

CREATE VIEW `movie` AS SELECT * FROM `MyVideos93`.`movie`;

CREATE VIEW `movielinktvshow` AS SELECT * FROM `MyVideos93`.`movielinktvshow`;

CREATE VIEW `musicvideo` AS SELECT * FROM `MyVideos93`.`musicvideo`;

CREATE VIEW `path` AS SELECT * FROM `MyVideos93`.`path`;

CREATE VIEW `seasons` AS SELECT * FROM `MyVideos93`.`seasons`;

CREATE VIEW `settings` AS SELECT * FROM `MyVideos93`.`settings`;

CREATE VIEW `sets` AS SELECT * FROM `MyVideos93`.`sets`;

CREATE VIEW `stacktimes` AS SELECT * FROM `MyVideos93`.`stacktimes`;

CREATE VIEW `streamdetails` AS SELECT * FROM `MyVideos93`.`streamdetails`;

CREATE VIEW `studio` AS SELECT * FROM `MyVideos93`.`studio`;

CREATE VIEW `studio_link` AS SELECT * FROM `MyVideos93`.`studio_link`;

CREATE VIEW `tag` AS SELECT * FROM `MyVideos93`.`tag`;

CREATE VIEW `tag_link` AS SELECT * FROM `MyVideos93`.`tag_link`;

CREATE VIEW `tvshow` AS SELECT * FROM `MyVideos93`.`tvshow`;

CREATE VIEW `tvshowlinkpath` AS SELECT * FROM `MyVideos93`.`tvshowlinkpath`;

CREATE VIEW `version` AS SELECT * FROM `MyVideos93`.`version`;

CREATE VIEW `writer_link` AS SELECT * FROM `MyVideos93`.`writer_link`;

CREATE TABLE `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=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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 `MyVideos93`.`files`;

CREATE VIEW `files` AS SELECT `f`.`idFile` AS `idFile`,`f`.`idPath` AS `idPath`,`f`.`strFilename` AS `strFilename`,`wh`.`playCount` AS `playCount`,`wh`.`lastPlayed` AS `lastPlayed`, `f`.`dateAdded` AS `dateAdded` FROM `MyVideos93`.`files` `f` LEFT JOIN `User1MyVideos93`.`watched_history` `wh` ON `f`.`idFile` = `wh`.`idFile`;

CREATE VIEW `tvshowcounts` AS SELECT `tvshow`.`idShow` AS `idShow`,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`,max(`files`.`dateAdded`) AS `dateAdded` FROM ((`tvshow` LEFT JOIN `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

#Replace user "KodiUserMain" with an account with required permissions

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUserMain`@`%` SQL SECURITY DEFINER VIEW `episode_view` 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 `Studio`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,`seasons`.`idSeason` AS `idSeason` FROM (((((`episode` JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) JOIN `tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) LEFT JOIN `seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) JOIN `path` on((`files`.`idPath` = `path`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `episode`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUserMain`@`%` SQL SECURITY DEFINER VIEW `movie_view` 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`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds` FROM ((((`movie` LEFT JOIN `sets` on((`sets`.`idSet` = `movie`.`idSet`))) JOIN `files` on((`files`.`idFile` = `movie`.`idFile`))) JOIN `path` on((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `movie`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUserMain`@`%` SQL SECURITY DEFINER VIEW `musicvideo_view` 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`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds` FROM (((`musicvideo` JOIN `files` on((`files`.`idFile` = `musicvideo`.`idFile`))) JOIN `path` on((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUserMain`@`%` SQL SECURITY DEFINER VIEW `tvshow_view` 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`.`idParentPath` AS `idParentPath`,`path`.`strPath` AS `strPath`,max(`files`.`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 ((((`tvshow` LEFT JOIN `tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) LEFT JOIN `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUserMain`@`%` SQL SECURITY DEFINER VIEW `season_view` AS SELECT `seasons`.`idSeason` AS `idSeason`,`seasons`.`idShow` AS `idShow`,`seasons`.`season` AS `season`,`tvshow_view`.`strPath` AS `strPath`,`tvshow_view`.`c00` AS `showTitle`,`tvshow_view`.`c01` AS `plot`,`tvshow_view`.`c05` AS `premiered`,`tvshow_view`.`c08` AS `genre`,`tvshow_view`.`c14` AS `studio`,`tvshow_view`.`c13` AS `mpaa`,count(distinct `episode_view`.`idEpisode`) AS `episodes`,count(`files`.`playCount`) AS `playCount` FROM (((`seasons` JOIN `tvshow_view` on((`tvshow_view`.`idShow` = `seasons`.`idShow`))) JOIN `episode_view` on(((`episode_view`.`idShow` = `seasons`.`idShow`) and (`episode_view`.`c12` = `seasons`.`season`)))) JOIN `files` on((`files`.`idFile` = `episode_view`.`idFile`))) group by `seasons`.`idSeason`;

USE MyVideos93;

#Remove existing custom trigger on .files insert

DROP TRIGGER IF EXISTS MyVideos93.insert_new_watched;

#Remove existing Kodi trigger on .files deletion

DROP TRIGGER IF EXISTS MyVideos93.delete_file;

#Create custom .files insert trigger with commands for new database 

#Expand and run separately for additional databases eq. User2 etc

delimiter |

CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos93.files

    FOR EACH ROW

        BEGIN 

        INSERT INTO User1MyVideos93.watched_history(idFile) VALUES (NEW.idFile);

        END;

|

#Recreate Kodi .files delete trigger with additional commands for new database 

#Expand and run separately for additional databases eq. User2 etc

delimiter |

CREATE TRIGGER delete_file AFTER DELETE ON MyVideos93.files

    FOR EACH ROW

        BEGIN 

            DELETE FROM bookmark WHERE idFile=old.idFile; 

            DELETE FROM settings WHERE idFile=old.idFile; 

            DELETE FROM stacktimes WHERE idFile=old.idFile; 

            DELETE FROM streamdetails WHERE idFile=old.idFile; 

            DELETE FROM User1MyVideos93.watched_history WHERE idFile=OLD.idFile; 

            DELETE FROM User1MyVideos93.bookmark WHERE idFile=OLD.idFile;

        END;

|

RE: Mysql profiles and watched status - atcronin - 2015-08-22

Following on from my previous post in regards to preventing the secondary user from modifying the library I have adjusted my script into 3 separate parts.
This also makes it clearer for creating multiple secondary users and databases.

This does result in a few sql errors being logged for the secondary user but user experience is unaffected.
Naturally the errors logged are about failed attempts to update library item details.

There is probably a better way to allow updates to the User1MyVideos93.files view without granting update privilege to MyVideos93.files, however this was the solution I came to.
Despite the privilege the secondary user does not update the primary users .files table playCounts etc.

Create Users - run as root user

Code:
CREATE USER 'KodiUserMain' IDENTIFIED BY 'password'; #Primary User

GRANT ALL ON `%MyVideos%`.* TO 'KodiUserMain';

GRANT ALL ON `%MyMusic%`.* TO 'KodiUserMain';

CREATE USER 'KodiUser1' IDENTIFIED BY 'password'; #Secondary User

GRANT ALL ON `User1MyVideos%`.* TO 'KodiUser1';

GRANT ALL ON `User1MyMusic%`.* TO 'KodiUser1';

GRANT SELECT ON `MyVideos93`.* TO 'KodiUser1';

GRANT UPDATE ON `MyVideos93`.`files` TO 'KodiUser1';

FLUSH PRIVILEGES;

Create new database - run as secondary user (KodiUser1)

Code:
CREATE DATABASE User1MyVideos93;

USE User1MyVideos93;

CREATE VIEW `actor_link` AS SELECT * FROM `MyVideos93`.`actor_link`;

CREATE VIEW `actor` AS SELECT * FROM `MyVideos93`.`actor`;

CREATE VIEW `art` AS SELECT * FROM `MyVideos93`.`art`;

CREATE VIEW `country` AS SELECT * FROM `MyVideos93`.`country`;

CREATE VIEW `country_link` AS SELECT * FROM `MyVideos93`.`country_link`;

CREATE VIEW `director_link` AS SELECT * FROM `MyVideos93`.`director_link`;

CREATE VIEW `episode` AS SELECT * FROM `MyVideos93`.`episode`;

CREATE VIEW `genre` AS SELECT * FROM `MyVideos93`.`genre`;

CREATE VIEW `genre_link` AS SELECT * FROM `MyVideos93`.`genre_link`;

CREATE VIEW `movie` AS SELECT * FROM `MyVideos93`.`movie`;

CREATE VIEW `movielinktvshow` AS SELECT * FROM `MyVideos93`.`movielinktvshow`;

CREATE VIEW `musicvideo` AS SELECT * FROM `MyVideos93`.`musicvideo`;

CREATE VIEW `path` AS SELECT * FROM `MyVideos93`.`path`;

CREATE VIEW `seasons` AS SELECT * FROM `MyVideos93`.`seasons`;

CREATE VIEW `settings` AS SELECT * FROM `MyVideos93`.`settings`;

CREATE VIEW `sets` AS SELECT * FROM `MyVideos93`.`sets`;

CREATE VIEW `stacktimes` AS SELECT * FROM `MyVideos93`.`stacktimes`;

CREATE VIEW `streamdetails` AS SELECT * FROM `MyVideos93`.`streamdetails`;

CREATE VIEW `studio` AS SELECT * FROM `MyVideos93`.`studio`;

CREATE VIEW `studio_link` AS SELECT * FROM `MyVideos93`.`studio_link`;

CREATE VIEW `tag` AS SELECT * FROM `MyVideos93`.`tag`;

CREATE VIEW `tag_link` AS SELECT * FROM `MyVideos93`.`tag_link`;

CREATE VIEW `tvshow` AS SELECT * FROM `MyVideos93`.`tvshow`;

CREATE VIEW `tvshowlinkpath` AS SELECT * FROM `MyVideos93`.`tvshowlinkpath`;

CREATE VIEW `version` AS SELECT * FROM `MyVideos93`.`version`;

CREATE VIEW `writer_link` AS SELECT * FROM `MyVideos93`.`writer_link`;

CREATE TABLE `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=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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 `MyVideos93`.`files`;

CREATE VIEW `files` AS SELECT `f`.`idFile` AS `idFile`,`f`.`idPath` AS `idPath`,`f`.`strFilename` AS `strFilename`,`wh`.`playCount` AS `playCount`,`wh`.`lastPlayed` AS `lastPlayed`, `f`.`dateAdded` AS `dateAdded` FROM `MyVideos93`.`files` `f` LEFT JOIN `User1MyVideos93`.`watched_history` `wh` ON `f`.`idFile` = `wh`.`idFile`;

CREATE VIEW `tvshowcounts` AS SELECT `tvshow`.`idShow` AS `idShow`,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`,max(`files`.`dateAdded`) AS `dateAdded` FROM ((`tvshow` LEFT JOIN `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUser1`@`%` SQL SECURITY DEFINER VIEW `episode_view` 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 `Studio`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,`seasons`.`idSeason` AS `idSeason` FROM (((((`episode` JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) JOIN `tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) LEFT JOIN `seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) JOIN `path` on((`files`.`idPath` = `path`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `episode`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUser1`@`%` SQL SECURITY DEFINER VIEW `movie_view` 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`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds` FROM ((((`movie` LEFT JOIN `sets` on((`sets`.`idSet` = `movie`.`idSet`))) JOIN `files` on((`files`.`idFile` = `movie`.`idFile`))) JOIN `path` on((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `movie`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUser1`@`%` SQL SECURITY DEFINER VIEW `musicvideo_view` 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`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds` FROM (((`musicvideo` JOIN `files` on((`files`.`idFile` = `musicvideo`.`idFile`))) JOIN `path` on((`path`.`idPath` = `files`.`idPath`))) LEFT JOIN `bookmark` on(((`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUser1`@`%` SQL SECURITY DEFINER VIEW `tvshow_view` 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`.`idParentPath` AS `idParentPath`,`path`.`strPath` AS `strPath`,max(`files`.`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 ((((`tvshow` LEFT JOIN `tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) LEFT JOIN `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) LEFT JOIN `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KodiUser1`@`%` SQL SECURITY DEFINER VIEW `season_view` AS SELECT `seasons`.`idSeason` AS `idSeason`,`seasons`.`idShow` AS `idShow`,`seasons`.`season` AS `season`,`tvshow_view`.`strPath` AS `strPath`,`tvshow_view`.`c00` AS `showTitle`,`tvshow_view`.`c01` AS `plot`,`tvshow_view`.`c05` AS `premiered`,`tvshow_view`.`c08` AS `genre`,`tvshow_view`.`c14` AS `studio`,`tvshow_view`.`c13` AS `mpaa`,count(distinct `episode_view`.`idEpisode`) AS `episodes`,count(`files`.`playCount`) AS `playCount` FROM (((`seasons` JOIN `tvshow_view` on((`tvshow_view`.`idShow` = `seasons`.`idShow`))) JOIN `episode_view` on(((`episode_view`.`idShow` = `seasons`.`idShow`) and (`episode_view`.`c12` = `seasons`.`season`)))) JOIN `files` on((`files`.`idFile` = `episode_view`.`idFile`))) group by `seasons`.`idSeason`;

Create Triggers - Run as primary user (KodiUserMain)

Code:
USE MyVideos93;

#Remove existing custom trigger on .files insert

DROP TRIGGER IF EXISTS MyVideos93.insert_new_watched;

#Remove existing Kodi trigger on .files deletion

DROP TRIGGER IF EXISTS MyVideos93.delete_file;

#Create custom .files insert trigger with commands for new database 

#Expand and run separately for additional databases eq. User2 etc

delimiter |

CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos93.files

    FOR EACH ROW

        BEGIN 

        INSERT INTO User1MyVideos93.watched_history(idFile) VALUES (NEW.idFile);

        END;

|

#Recreate Kodi .files delete trigger with additional commands for new database 

#Expand and run separately for additional databases eq. User2 etc

delimiter |

CREATE TRIGGER delete_file AFTER DELETE ON MyVideos93.files

    FOR EACH ROW

        BEGIN 

            DELETE FROM bookmark WHERE idFile=old.idFile; 

            DELETE FROM settings WHERE idFile=old.idFile; 

            DELETE FROM stacktimes WHERE idFile=old.idFile; 

            DELETE FROM streamdetails WHERE idFile=old.idFile; 

            DELETE FROM User1MyVideos93.watched_history WHERE idFile=OLD.idFile; 

            DELETE FROM User1MyVideos93.bookmark WHERE idFile=OLD.idFile;

        END;

|

RE: Mysql profiles and watched status - Martijn - 2015-08-22

you should never start creating your own database structure. This can and will break on each Kodi version.
Just use the Kodi itself for creating the tables!!

RE: Mysql profiles and watched status - atcronin - 2015-08-22

That's a given.
This is a guide only for people who accept that caveat.

However we are only creating 2 new tables to store the watched/resume status.

The procedure will need to be fixed for each new version with a schema revision, and the user would just need to transfer the data from the 2 tables into the new secondary database.
Or perhaps put the 2 tables in a separate database to make upgrades easier.

RE: Mysql profiles and watched status - mcfang - 2015-08-24

I'm still using the design from timmyj9's post and it's been very easy to upgrade for each successive version of XBMC since Frodo.

This is the design updated to Kodi v15: http://pastebin.com/rtaLhFBj

Simply it adds a new playCount and lastWatched fields to the master files table for secondary user and creates a new database with views to the master database for everything except for the bookmarks table.

RE: Mysql profiles and watched status - Stormregion0 - 2015-08-26

(2015-08-22, 14:47)atcronin Wrote: That's a given.
This is a guide only for people who accept that caveat.

However we are only creating 2 new tables to store the watched/resume status.

The procedure will need to be fixed for each new version with a schema revision, and the user would just need to transfer the data from the 2 tables into the new secondary database.
Or perhaps put the 2 tables in a separate database to make upgrades easier.

Im not sure if this is the right place but..

I would appreciate if it would be a built in option to have mysql profiles with seperate watched status without doing all this stuff posted in this thread. More like activate it in an xml-file. I dont know how it works. Is there an To-Do-List or where can we say which function we would appreciate?

RE: Mysql profiles and watched status - iainmacleod - 2015-08-26

I came into this thread a little late, and after watching KordKutters I realized that profiles were being review as part of Google summer of code - its probably too late now, but suggestions should maybe be posted here:

http://forum.kodi.tv/showthread.php?tid=222006

RE: Mysql profiles and watched status - bmac88 - 2015-09-14

(2015-08-24, 07:34)mcfang Wrote: I'm still using the design from timmyj9's post and it's been very easy to upgrade for each successive version of XBMC since Frodo.

This is the design updated to Kodi v15: http://pastebin.com/rtaLhFBj

Simply it adds a new playCount and lastWatched fields to the master files table for secondary user and creates a new database with views to the master database for everything except for the bookmarks table.

I'm guessing not because there are no references to triggers in the script, but would library updates/deletions work regardless of which user is logged on? Also, am I supposed to let Kodi 15 build a db from scratch and then run your query?