Kodi-18.5-multi-profile-mysql

/* prepare the master files table to support profile-specific information */
ALTER TABLE  `MyVideos116`.`files` ADD  `playCountXXX` INT( 11 ) NULL DEFAULT NULL , ADD  `lastPlayedXXX` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

/* create slave database and the only slave table required */
CREATE DATABASE  `XXX116`;
USE XXX116;
CREATE TABLE bookmark ( idBookmark integer primary key AUTO_INCREMENT, idFile integer, timeInSeconds double, totalTimeInSeconds double, thumbNailImage text, player text, playerState text, type integer);
CREATE INDEX ix_bookmark ON bookmark (idFile, type);

/* from here to the end of the paste is creating views in slave database */
CREATE ALGORITHM=MERGE VIEW `XXX116`.`files` AS
SELECT idFile, idPath, strFilename, playCountXXX AS playCount, lastPlayedXXX AS lastPlayed, dateAdded FROM `MyVideos116`.`files`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`actor_link` AS
SELECT * FROM `MyVideos116`.`actor_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`actor` AS
SELECT * FROM `MyVideos116`.`actor`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`art` AS
SELECT * FROM `MyVideos116`.`art`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`country` AS
SELECT * FROM `MyVideos116`.`country`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`country_link` AS
SELECT * FROM `MyVideos116`.`country_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`director_link` AS
SELECT * FROM `MyVideos116`.`director_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`episode` AS
SELECT * FROM `MyVideos116`.`episode`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`genre` AS
SELECT * FROM `MyVideos116`.`genre`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`genre_link` AS
SELECT * FROM `MyVideos116`.`genre_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`movie` AS
SELECT * FROM `MyVideos116`.`movie`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`movielinktvshow` AS
SELECT * FROM `MyVideos116`.`movielinktvshow`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`musicvideo` AS
SELECT * FROM `MyVideos116`.`musicvideo`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`path` AS
SELECT * FROM `MyVideos116`.`path`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`rating` AS
SELECT * FROM `MyVideos116`.`rating`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`seasons` AS
SELECT * FROM `MyVideos116`.`seasons`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`sets` AS
SELECT * FROM `MyVideos116`.`sets`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`settings` AS
SELECT * FROM `MyVideos116`.`settings`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`stacktimes` AS
SELECT * FROM `MyVideos116`.`stacktimes`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`streamdetails` AS
SELECT * FROM `MyVideos116`.`streamdetails`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`studio` AS
SELECT * FROM `MyVideos116`.`studio`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`studio_link` AS
SELECT * FROM `MyVideos116`.`studio_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`tag` AS
SELECT * FROM `MyVideos116`.`tag`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`tag_link` AS
SELECT * FROM `MyVideos116`.`tag_link`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`tvshow` AS
SELECT * FROM `MyVideos116`.`tvshow`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`tvshowlinkpath` AS
SELECT * FROM `MyVideos116`.`tvshowlinkpath`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`uniqueid` AS
SELECT * FROM `MyVideos116`.`uniqueid`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`version` AS
SELECT * FROM `MyVideos116`.`version`;

CREATE ALGORITHM=MERGE VIEW `XXX116`.`writer_link` AS
SELECT * FROM `MyVideos116`.`writer_link`;


CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`movie_view` AS
select
    `XXX116`.`movie`.`idMovie` AS `idMovie`,
    `XXX116`.`movie`.`idFile` AS `idFile`,
    `XXX116`.`movie`.`c00` AS `c00`,
    `XXX116`.`movie`.`c01` AS `c01`,
    `XXX116`.`movie`.`c02` AS `c02`,
    `XXX116`.`movie`.`c03` AS `c03`,
    `XXX116`.`movie`.`c04` AS `c04`,
    `XXX116`.`movie`.`c05` AS `c05`,
    `XXX116`.`movie`.`c06` AS `c06`,
    `XXX116`.`movie`.`c07` AS `c07`,
    `XXX116`.`movie`.`c08` AS `c08`,
    `XXX116`.`movie`.`c09` AS `c09`,
    `XXX116`.`movie`.`c10` AS `c10`,
    `XXX116`.`movie`.`c11` AS `c11`,
    `XXX116`.`movie`.`c12` AS `c12`,
    `XXX116`.`movie`.`c13` AS `c13`,
    `XXX116`.`movie`.`c14` AS `c14`,
    `XXX116`.`movie`.`c15` AS `c15`,
    `XXX116`.`movie`.`c16` AS `c16`,
    `XXX116`.`movie`.`c17` AS `c17`,
    `XXX116`.`movie`.`c18` AS `c18`,
    `XXX116`.`movie`.`c19` AS `c19`,
    `XXX116`.`movie`.`c20` AS `c20`,
    `XXX116`.`movie`.`c21` AS `c21`,
    `XXX116`.`movie`.`c22` AS `c22`,
    `XXX116`.`movie`.`c23` AS `c23`,
    `XXX116`.`movie`.`idSet` AS `idSet`,
    `XXX116`.`movie`.`userrating` AS `userrating`,
    `XXX116`.`movie`.`premiered` AS `premiered`,
    `XXX116`.`sets`.`strSet` AS `strSet`,
    `XXX116`.`sets`.`strOverview` AS `strSetOverview`,
    `XXX116`.`files`.`strFilename` AS `strFileName`,
    `XXX116`.`path`.`strPath` AS `strPath`,
    `XXX116`.`files`.`playCount` AS `playCount`,
    `XXX116`.`files`.`lastPlayed` AS `lastPlayed`,
    `XXX116`.`files`.`dateAdded` AS `dateAdded`,
    `XXX116`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `XXX116`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
    `XXX116`.`bookmark`.`playerState` AS `playerState`,
    `XXX116`.`rating`.`rating` AS `rating`,
    `XXX116`.`rating`.`votes` AS `votes`,
    `XXX116`.`rating`.`rating_type` AS `rating_type`,
    `XXX116`.`uniqueid`.`value` AS `uniqueid_value`,
    `XXX116`.`uniqueid`.`type` AS `uniqueid_type`
from ((((((`XXX116`.`movie` left join `XXX116`.`sets` on((`XXX116`.`sets`.`idSet` = `XXX116`.`movie`.`idSet`))) join `XXX116`.`files` on((`XXX116`.`files`.`idFile` = `XXX116`.`movie`.`idFile`))) join `XXX116`.`path` on((`XXX116`.`path`.`idPath` = `XXX116`.`files`.`idPath`))) left join `XXX116`.`bookmark` on(((`XXX116`.`bookmark`.`idFile` = `XXX116`.`movie`.`idFile`) and (`XXX116`.`bookmark`.`type` = 1)))) left join `XXX116`.`rating` on((`XXX116`.`rating`.`rating_id` = `XXX116`.`movie`.`c05`))) left join `XXX116`.`uniqueid` on((`XXX116`.`uniqueid`.`uniqueid_id` = `XXX116`.`movie`.`c09`)));

CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`musicvideo_view` AS
select
    `XXX116`.`musicvideo`.`idMVideo` AS `idMVideo`,
    `XXX116`.`musicvideo`.`idFile` AS `idFile`,
    `XXX116`.`musicvideo`.`c00` AS `c00`,
    `XXX116`.`musicvideo`.`c01` AS `c01`,
    `XXX116`.`musicvideo`.`c02` AS `c02`,
    `XXX116`.`musicvideo`.`c03` AS `c03`,
    `XXX116`.`musicvideo`.`c04` AS `c04`,
    `XXX116`.`musicvideo`.`c05` AS `c05`,
    `XXX116`.`musicvideo`.`c06` AS `c06`,
    `XXX116`.`musicvideo`.`c07` AS `c07`,
    `XXX116`.`musicvideo`.`c08` AS `c08`,
    `XXX116`.`musicvideo`.`c09` AS `c09`,
    `XXX116`.`musicvideo`.`c10` AS `c10`,
    `XXX116`.`musicvideo`.`c11` AS `c11`,
    `XXX116`.`musicvideo`.`c12` AS `c12`,
    `XXX116`.`musicvideo`.`c13` AS `c13`,
    `XXX116`.`musicvideo`.`c14` AS `c14`,
    `XXX116`.`musicvideo`.`c15` AS `c15`,
    `XXX116`.`musicvideo`.`c16` AS `c16`,
    `XXX116`.`musicvideo`.`c17` AS `c17`,
    `XXX116`.`musicvideo`.`c18` AS `c18`,
    `XXX116`.`musicvideo`.`c19` AS `c19`,
    `XXX116`.`musicvideo`.`c20` AS `c20`,
    `XXX116`.`musicvideo`.`c21` AS `c21`,
    `XXX116`.`musicvideo`.`c22` AS `c22`,
    `XXX116`.`musicvideo`.`c23` AS `c23`,
    `XXX116`.`musicvideo`.`userrating` AS `userrating`,
    `XXX116`.`musicvideo`.`premiered` AS `premiered`,
    `XXX116`.`files`.`strFilename` AS `strFileName`,
    `XXX116`.`path`.`strPath` AS `strPath`,
    `XXX116`.`files`.`playCount` AS `playCount`,
    `XXX116`.`files`.`lastPlayed` AS `lastPlayed`,
    `XXX116`.`files`.`dateAdded` AS `dateAdded`,
    `XXX116`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `XXX116`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
    `XXX116`.`bookmark`.`playerState` AS `playerState`
from (((`XXX116`.`musicvideo` join `XXX116`.`files` on((`XXX116`.`files`.`idFile` = `XXX116`.`musicvideo`.`idFile`))) join `XXX116`.`path` on((`XXX116`.`path`.`idPath` = `XXX116`.`files`.`idPath`))) left join `XXX116`.`bookmark` on(((`XXX116`.`bookmark`.`idFile` = `XXX116`.`musicvideo`.`idFile`) and (`XXX116`.`bookmark`.`type` = 1))));

CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`episode_view` AS
select
    `XXX116`.`episode`.`idEpisode` AS `idEpisode`,
    `XXX116`.`episode`.`idFile` AS `idFile`,
    `XXX116`.`episode`.`c00` AS `c00`,
    `XXX116`.`episode`.`c01` AS `c01`,
    `XXX116`.`episode`.`c02` AS `c02`,
    `XXX116`.`episode`.`c03` AS `c03`,
    `XXX116`.`episode`.`c04` AS `c04`,
    `XXX116`.`episode`.`c05` AS `c05`,
    `XXX116`.`episode`.`c06` AS `c06`,
    `XXX116`.`episode`.`c07` AS `c07`,
    `XXX116`.`episode`.`c08` AS `c08`,
    `XXX116`.`episode`.`c09` AS `c09`,
    `XXX116`.`episode`.`c10` AS `c10`,
    `XXX116`.`episode`.`c11` AS `c11`,
    `XXX116`.`episode`.`c12` AS `c12`,
    `XXX116`.`episode`.`c13` AS `c13`,
    `XXX116`.`episode`.`c14` AS `c14`,
    `XXX116`.`episode`.`c15` AS `c15`,
    `XXX116`.`episode`.`c16` AS `c16`,
    `XXX116`.`episode`.`c17` AS `c17`,
    `XXX116`.`episode`.`c18` AS `c18`,
    `XXX116`.`episode`.`c19` AS `c19`,
    `XXX116`.`episode`.`c20` AS `c20`,
    `XXX116`.`episode`.`c21` AS `c21`,
    `XXX116`.`episode`.`c22` AS `c22`,
    `XXX116`.`episode`.`c23` AS `c23`,
    `XXX116`.`episode`.`idShow` AS `idShow`,
    `XXX116`.`episode`.`userrating` AS `userrating`,
    `XXX116`.`episode`.`idSeason` AS `idSeason`,
    `XXX116`.`files`.`strFilename` AS `strFileName`,
    `XXX116`.`path`.`strPath` AS `strPath`,
    `XXX116`.`files`.`playCount` AS `playCount`,
    `XXX116`.`files`.`lastPlayed` AS `lastPlayed`,
    `XXX116`.`files`.`dateAdded` AS `dateAdded`,
    `XXX116`.`tvshow`.`c00` AS `strTitle`,
    `XXX116`.`tvshow`.`c08` AS `genre`,
    `XXX116`.`tvshow`.`c14` AS `studio`,
    `XXX116`.`tvshow`.`c05` AS `premiered`,
    `XXX116`.`tvshow`.`c13` AS `mpaa`,
    `XXX116`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
    `XXX116`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
    `XXX116`.`bookmark`.`playerState` AS `playerState`,
    `XXX116`.`rating`.`rating` AS `rating`,
    `XXX116`.`rating`.`votes` AS `votes`,
    `XXX116`.`rating`.`rating_type` AS `rating_type`,
    `XXX116`.`uniqueid`.`value` AS `uniqueid_value`,
    `XXX116`.`uniqueid`.`type` AS `uniqueid_type`
from (((((((`XXX116`.`episode` join `XXX116`.`files` on((`XXX116`.`files`.`idFile` = `XXX116`.`episode`.`idFile`))) join `XXX116`.`tvshow` on((`XXX116`.`tvshow`.`idShow` = `XXX116`.`episode`.`idShow`))) join `XXX116`.`seasons` on((`XXX116`.`seasons`.`idSeason` = `XXX116`.`episode`.`idSeason`))) join `XXX116`.`path` on((`XXX116`.`files`.`idPath` = `XXX116`.`path`.`idPath`))) left join `XXX116`.`bookmark` on(((`XXX116`.`bookmark`.`idFile` = `XXX116`.`episode`.`idFile`) and (`XXX116`.`bookmark`.`type` = 1)))) left join `XXX116`.`rating` on((`XXX116`.`rating`.`rating_id` = `XXX116`.`episode`.`c03`))) left join `XXX116`.`uniqueid` on((`XXX116`.`uniqueid`.`uniqueid_id` = `XXX116`.`episode`.`c20`)));


CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`tvshowcounts` AS
select
    `XXX116`.`tvshow`.`idShow` AS `idShow`,
    max(`XXX116`.`files`.`lastPlayed`) AS `lastPlayed`,
    nullif(count(`XXX116`.`episode`.`c12`),0) AS `totalCount`,
    count(`XXX116`.`files`.`playCount`) AS `watchedcount`,
    nullif(count(distinct `XXX116`.`episode`.`c12`),0) AS `totalSeasons`,
    max(`XXX116`.`files`.`dateAdded`) AS `dateAdded`
from ((`XXX116`.`tvshow` left join `XXX116`.`episode` on((`XXX116`.`episode`.`idShow` = `XXX116`.`tvshow`.`idShow`))) left join `XXX116`.`files` on((`XXX116`.`files`.`idFile` = `XXX116`.`episode`.`idFile`))) group by `XXX116`.`tvshow`.`idShow`;

CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`tvshow_view` AS
select
    `XXX116`.`tvshow`.`idShow` AS `idShow`,
    `XXX116`.`tvshow`.`c00` AS `c00`,
    `XXX116`.`tvshow`.`c01` AS `c01`,
    `XXX116`.`tvshow`.`c02` AS `c02`,
    `XXX116`.`tvshow`.`c03` AS `c03`,
    `XXX116`.`tvshow`.`c04` AS `c04`,
    `XXX116`.`tvshow`.`c05` AS `c05`,
    `XXX116`.`tvshow`.`c06` AS `c06`,
    `XXX116`.`tvshow`.`c07` AS `c07`,
    `XXX116`.`tvshow`.`c08` AS `c08`,
    `XXX116`.`tvshow`.`c09` AS `c09`,
    `XXX116`.`tvshow`.`c10` AS `c10`,
    `XXX116`.`tvshow`.`c11` AS `c11`,
    `XXX116`.`tvshow`.`c12` AS `c12`,
    `XXX116`.`tvshow`.`c13` AS `c13`,
    `XXX116`.`tvshow`.`c14` AS `c14`,
    `XXX116`.`tvshow`.`c15` AS `c15`,
    `XXX116`.`tvshow`.`c16` AS `c16`,
    `XXX116`.`tvshow`.`c17` AS `c17`,
    `XXX116`.`tvshow`.`c18` AS `c18`,
    `XXX116`.`tvshow`.`c19` AS `c19`,
    `XXX116`.`tvshow`.`c20` AS `c20`,
    `XXX116`.`tvshow`.`c21` AS `c21`,
    `XXX116`.`tvshow`.`c22` AS `c22`,
    `XXX116`.`tvshow`.`c23` AS `c23`,
    `XXX116`.`tvshow`.`userrating` AS `userrating`,
    `XXX116`.`tvshow`.`duration` AS `duration`,
    `XXX116`.`path`.`idParentPath` AS `idParentPath`,
    `XXX116`.`path`.`strPath` AS `strPath`,
    `XXX116`.`tvshowcounts`.`dateAdded` AS `dateAdded`,
    `XXX116`.`tvshowcounts`.`lastPlayed` AS `lastPlayed`,
    `XXX116`.`tvshowcounts`.`totalCount` AS `totalCount`,
    `XXX116`.`tvshowcounts`.`watchedcount` AS `watchedcount`,
    `XXX116`.`tvshowcounts`.`totalSeasons` AS `totalSeasons`,
    `XXX116`.`rating`.`rating` AS `rating`,
    `XXX116`.`rating`.`votes` AS `votes`,
    `XXX116`.`rating`.`rating_type` AS `rating_type`,
    `XXX116`.`uniqueid`.`value` AS `uniqueid_value`,
    `XXX116`.`uniqueid`.`type` AS `uniqueid_type`
from (((((`XXX116`.`tvshow` left join `XXX116`.`tvshowlinkpath` on((`XXX116`.`tvshowlinkpath`.`idShow` = `XXX116`.`tvshow`.`idShow`))) left join `XXX116`.`path` on((`XXX116`.`path`.`idPath` = `XXX116`.`tvshowlinkpath`.`idPath`))) join `XXX116`.`tvshowcounts` on((`XXX116`.`tvshow`.`idShow` = `XXX116`.`tvshowcounts`.`idShow`))) left join `XXX116`.`rating` on((`XXX116`.`rating`.`rating_id` = `XXX116`.`tvshow`.`c04`))) left join `XXX116`.`uniqueid` on((`XXX116`.`uniqueid`.`uniqueid_id` = `XXX116`.`tvshow`.`c12`))) group by `XXX116`.`tvshow`.`idShow`;


CREATE OR REPLACE ALGORITHM=MERGE VIEW `XXX116`.`season_view` AS
select
    `XXX116`.`seasons`.`idSeason` AS `idSeason`,
    `XXX116`.`seasons`.`idShow` AS `idShow`,
    `XXX116`.`seasons`.`season` AS `season`,
    `XXX116`.`seasons`.`name` AS `name`,
    `XXX116`.`seasons`.`userrating` AS `userrating`,
    `XXX116`.`tvshow_view`.`strPath` AS `strPath`,
    `XXX116`.`tvshow_view`.`c00` AS `showTitle`,
    `XXX116`.`tvshow_view`.`c01` AS `plot`,
    `XXX116`.`tvshow_view`.`c05` AS `premiered`,
    `XXX116`.`tvshow_view`.`c08` AS `genre`,
    `XXX116`.`tvshow_view`.`c14` AS `studio`,
    `XXX116`.`tvshow_view`.`c13` AS `mpaa`,
    count(distinct `XXX116`.`episode`.`idEpisode`) AS `episodes`,
    count(`XXX116`.`files`.`playCount`) AS `playCount`,
    min(`XXX116`.`episode`.`c05`) AS `aired`
from (((`XXX116`.`seasons` join `XXX116`.`tvshow_view` on((`XXX116`.`tvshow_view`.`idShow` = `XXX116`.`seasons`.`idShow`))) join `XXX116`.`episode` on(((`XXX116`.`episode`.`idShow` = `XXX116`.`seasons`.`idShow`) and (`XXX116`.`episode`.`c12` = `XXX116`.`seasons`.`season`)))) join `XXX116`.`files` on((`XXX116`.`files`.`idFile` = `XXX116`.`episode`.`idFile`))) group by `XXX116`.`seasons`.`idSeason`;