Mysql profiles and watched status

  Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
armss001 Offline
Junior Member
Posts: 4
Joined: May 2015
Reputation: 1
Post: #76
Okay problem solved, after digging about, I needed to add delimiter.

Code is as follows when ran from the phpmyadmin page.

Code:
delimiter //
CREATE TRIGGER insert_new_watched AFTER INSERT ON myvideos90.files
    FOR EACH ROW BEGIN
        INSERT INTO kodi_ben90.watched_history(idFile) VALUES (NEW.idFile);
        INSERT INTO kodi_dan90.watched_history(idFile) VALUES (NEW.idFile);
        INSERT INTO kodi_dem90.watched_history(idFile) VALUES (NEW.idFile);
        INSERT INTO kodi_nath90.watched_history(idFile) VALUES (NEW.idFile);
        INSERT INTO kodi_hannah90.watched_history(idFile) VALUES (NEW.idFile);
     END; //
delimiter ;
find quote
Walhalla Offline
Junior Member
Posts: 46
Joined: Apr 2009
Reputation: 1
Post: #77
Good to know. Thanks for posting the solution.
find quote
dr-oopie Offline
Junior Member
Posts: 30
Joined: Feb 2015
Reputation: 1
Post: #78
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.
(This post was last modified: 2015-07-08 03:18 by dr-oopie.)
find quote
unaszplodrmann Offline
Junior Member
Posts: 2
Joined: Jul 2015
Reputation: 0
Post: #79
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.
find quote
Walhalla Offline
Junior Member
Posts: 46
Joined: Apr 2009
Reputation: 1
Post: #80
I had no time to have a look into it. If you have some spare time it would be really cool ;-)
find quote
unaszplodrmann Offline
Junior Member
Posts: 2
Joined: Jul 2015
Reputation: 0
Post: #81
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.
find quote
iainmacleod Offline
Senior Member
Posts: 220
Joined: Feb 2006
Reputation: 0
Location: Pittsburgh area (Scotland previously)
Post: #82
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.
find quote
Deihmos Online
Senior Member
Posts: 158
Joined: Dec 2010
Reputation: 1
Post: #83
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.
find quote
atcronin Offline
Junior Member
Posts: 3
Joined: Aug 2015
Reputation: 0
Post: #84
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;
|
find quote
atcronin Offline
Junior Member
Posts: 3
Joined: Aug 2015
Reputation: 0
Post: #85
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;
|
find quote
Martijn Offline
Team Kodi
Posts: 14,170
Joined: Jul 2011
Reputation: 203
Location: Dawn of time
Post: #86
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!!

Read/follow the forum rules.
For troubleshooting and bug reporting, read this first
Interested in seeing some YouTube videos about Kodi? Go here and subscribe
find quote
atcronin Offline
Junior Member
Posts: 3
Joined: Aug 2015
Reputation: 0
Post: #87
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.
find quote
mcfang Offline
Junior Member
Posts: 31
Joined: Mar 2011
Reputation: 0
Post: #88
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.
find quote
Stormregion0 Offline
Junior Member
Posts: 4
Joined: Aug 2015
Reputation: 0
Post: #89
(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?
find quote
iainmacleod Offline
Senior Member
Posts: 220
Joined: Feb 2006
Reputation: 0
Location: Pittsburgh area (Scotland previously)
Post: #90
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
find quote
Post Reply