2015-05-29, 15:03
Good to know. Thanks for posting the solution.
2015-07-07, 07:23
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.
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.
2015-07-28, 19:55
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.
2015-07-29, 20:25
I had no time to have a look into it. If you have some spare time it would be really cool ;-)
2015-07-30, 02:49
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.
2015-08-21, 23:15
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.
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.
2015-08-21, 23:46
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.
2015-08-22, 12:23
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)
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;
|
2015-08-22, 14:33
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
Create new database - run as secondary user (KodiUser1)
Create Triggers - Run as primary user (KodiUserMain)
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;
|
2015-08-22, 14:34
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!!
Just use the Kodi itself for creating the tables!!
2015-08-22, 14:47
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.
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.
2015-08-24, 07:34
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.
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.
2015-08-26, 19:25
(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?
2015-08-26, 19:34
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
http://forum.kodi.tv/showthread.php?tid=222006
2015-09-14, 21:57
(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?
