kodi mysql videos93

/* rename master files table and add fields to track watchlist of slave user */
RENAME TABLE  `MyVideos93`.`files` TO  `MyVideos93`.`globalfiles` ;
ALTER TABLE  `MyVideos93`.`globalfiles` ADD  `playCount1` INT( 11 ) NULL DEFAULT NULL , ADD  `lastPlayed1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE  `MyVideos93`.`globalfiles` CHANGE  `playCount`  `playCount0` INT( 11 ) NULL DEFAULT NULL;
ALTER TABLE  `MyVideos93`.`globalfiles` CHANGE  `lastPlayed`  `lastPlayed0` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

/* create slave database and the only slave table required */
CREATE DATABASE  `User1Videos93`;
USE User1Videos93;
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);

/* create files view in master database */
CREATE VIEW `MyVideos93`.`files` AS
SELECT idFile, idPath, strFilename, playCount0 AS playCount, lastPlayed0 AS lastPlayed, dateAdded FROM `MyVideos93`.`globalfiles`;

/* from here to the end of the paste is creating views in slave database */
CREATE VIEW `User1Videos93`.`files` AS
SELECT idFile, idPath, strFilename, playCount1 AS playCount, lastPlayed1 AS lastPlayed, dateAdded FROM `MyVideos93`.`globalfiles`;
CREATE VIEW `User1Videos93`.`actor_link` AS
SELECT * FROM `MyVideos93`.`actor_link`;
CREATE VIEW `User1Videos93`.`actor` AS
SELECT * FROM `MyVideos93`.`actor`;
CREATE VIEW `User1Videos93`.`art` AS
SELECT * FROM `MyVideos93`.`art`;
CREATE VIEW `User1Videos93`.`country` AS
SELECT * FROM `MyVideos93`.`country`;
CREATE VIEW `User1Videos93`.`country_link` AS
SELECT * FROM `MyVideos93`.`country_link`;
CREATE VIEW `User1Videos93`.`director_link` AS
SELECT * FROM `MyVideos93`.`director_link`;
CREATE VIEW `User1Videos93`.`episode` AS
SELECT * FROM `MyVideos93`.`episode`;
CREATE VIEW `User1Videos93`.`genre` AS
SELECT * FROM `MyVideos93`.`genre`;
CREATE VIEW `User1Videos93`.`genre_link` AS
SELECT * FROM `MyVideos93`.`genre_link`;
CREATE VIEW `User1Videos93`.`movie` AS
SELECT * FROM `MyVideos93`.`movie`;
CREATE VIEW `User1Videos93`.`movielinktvshow` AS
SELECT * FROM `MyVideos93`.`movielinktvshow`;
CREATE VIEW `User1Videos93`.`musicvideo` AS
SELECT * FROM `MyVideos93`.`musicvideo`;
CREATE VIEW `User1Videos93`.`path` AS
SELECT * FROM `MyVideos93`.`path`;
CREATE VIEW `User1Videos93`.`seasons` AS
SELECT * FROM `MyVideos93`.`seasons`;
CREATE VIEW `User1Videos93`.`sets` AS
SELECT * FROM `MyVideos93`.`sets`;
CREATE VIEW `User1Videos93`.`settings` AS
SELECT * FROM `MyVideos93`.`settings`;
CREATE VIEW `User1Videos93`.`stacktimes` AS
SELECT * FROM `MyVideos93`.`stacktimes`;
CREATE VIEW `User1Videos93`.`streamdetails` AS
SELECT * FROM `MyVideos93`.`streamdetails`;
CREATE VIEW `User1Videos93`.`studio` AS
SELECT * FROM `MyVideos93`.`studio`;
CREATE VIEW `User1Videos93`.`studio_link` AS
SELECT * FROM `MyVideos93`.`studio_link`;
CREATE VIEW `User1Videos93`.`tag` AS
SELECT * FROM `MyVideos93`.`tag`;
CREATE VIEW `User1Videos93`.`tag_link` AS
SELECT * FROM `MyVideos93`.`tag_link`;
CREATE VIEW `User1Videos93`.`tvshow` AS
SELECT * FROM `MyVideos93`.`tvshow`;
CREATE VIEW `User1Videos93`.`tvshowlinkpath` AS
SELECT * FROM `MyVideos93`.`tvshowlinkpath`;
CREATE VIEW `User1Videos93`.`version` AS
SELECT * FROM `MyVideos93`.`version`;
CREATE VIEW `User1Videos93`.`writer_link` AS
SELECT * FROM `MyVideos93`.`writer_link`;

CREATE VIEW `User1Videos93`.`movie_view` AS select
`User1Videos93`.`movie`.`idMovie` AS `idMovie`,
`User1Videos93`.`movie`.`idFile` AS `idFile`,
`User1Videos93`.`movie`.`c00` AS `c00`,
`User1Videos93`.`movie`.`c01` AS `c01`,
`User1Videos93`.`movie`.`c02` AS `c02`,
`User1Videos93`.`movie`.`c03` AS `c03`,
`User1Videos93`.`movie`.`c04` AS `c04`,
`User1Videos93`.`movie`.`c05` AS `c05`,
`User1Videos93`.`movie`.`c06` AS `c06`,
`User1Videos93`.`movie`.`c07` AS `c07`,
`User1Videos93`.`movie`.`c08` AS `c08`,
`User1Videos93`.`movie`.`c09` AS `c09`,
`User1Videos93`.`movie`.`c10` AS `c10`,
`User1Videos93`.`movie`.`c11` AS `c11`,
`User1Videos93`.`movie`.`c12` AS `c12`,
`User1Videos93`.`movie`.`c13` AS `c13`,
`User1Videos93`.`movie`.`c14` AS `c14`,
`User1Videos93`.`movie`.`c15` AS `c15`,
`User1Videos93`.`movie`.`c16` AS `c16`,
`User1Videos93`.`movie`.`c17` AS `c17`,
`User1Videos93`.`movie`.`c18` AS `c18`,
`User1Videos93`.`movie`.`c19` AS `c19`,
`User1Videos93`.`movie`.`c20` AS `c20`,
`User1Videos93`.`movie`.`c21` AS `c21`,
`User1Videos93`.`movie`.`c22` AS `c22`,
`User1Videos93`.`movie`.`c23` AS `c23`,
`User1Videos93`.`movie`.`idSet` AS `idSet`,
`User1Videos93`.`sets`.`strSet` AS `strSet`,
`User1Videos93`.`files`.`strFilename` AS `strFileName`,
`User1Videos93`.`path`.`strPath` AS `strPath`,
`User1Videos93`.`files`.`playCount` AS `playCount`,
`User1Videos93`.`files`.`lastPlayed` AS `lastPlayed`,
`User1Videos93`.`files`.`dateAdded` AS `dateAdded`,
`User1Videos93`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`User1Videos93`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
from ((`User1Videos93`.`movie` left join `User1Videos93`.`sets` on((`User1Videos93`.`sets`.`idSet` = `User1Videos93`.`movie`.`idSet`)) join `User1Videos93`.`files` on((`User1Videos93`.`files`.`idFile` = `User1Videos93`.`movie`.`idFile`))) join `User1Videos93`.`path` on((`User1Videos93`.`path`.`idPath` = `User1Videos93`.`files`.`idPath`))left join `User1Videos93`.`bookmark` on((`User1Videos93`.`bookmark`.`idFile` = `User1Videos93`.`movie`.`idFile` AND `User1Videos93`.`bookmark`.`type` = 1)));

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

CREATE VIEW `User1Videos93`.`episode_view` AS select
`User1Videos93`.`episode`.`idEpisode` AS `idEpisode`,
`User1Videos93`.`episode`.`idFile` AS `idFile`,
`User1Videos93`.`episode`.`c00` AS `c00`,
`User1Videos93`.`episode`.`c01` AS `c01`,
`User1Videos93`.`episode`.`c02` AS `c02`,
`User1Videos93`.`episode`.`c03` AS `c03`,
`User1Videos93`.`episode`.`c04` AS `c04`,
`User1Videos93`.`episode`.`c05` AS `c05`,
`User1Videos93`.`episode`.`c06` AS `c06`,
`User1Videos93`.`episode`.`c07` AS `c07`,
`User1Videos93`.`episode`.`c08` AS `c08`,
`User1Videos93`.`episode`.`c09` AS `c09`,
`User1Videos93`.`episode`.`c10` AS `c10`,
`User1Videos93`.`episode`.`c11` AS `c11`,
`User1Videos93`.`episode`.`c12` AS `c12`,
`User1Videos93`.`episode`.`c13` AS `c13`,
`User1Videos93`.`episode`.`c14` AS `c14`,
`User1Videos93`.`episode`.`c15` AS `c15`,
`User1Videos93`.`episode`.`c16` AS `c16`,
`User1Videos93`.`episode`.`c17` AS `c17`,
`User1Videos93`.`episode`.`c18` AS `c18`,
`User1Videos93`.`episode`.`c19` AS `c19`,
`User1Videos93`.`episode`.`c20` AS `c20`,
`User1Videos93`.`episode`.`c21` AS `c21`,
`User1Videos93`.`episode`.`c22` AS `c22`,
`User1Videos93`.`episode`.`c23` AS `c23`,
`User1Videos93`.`episode`.`idShow` AS `idShow`,
`User1Videos93`.`files`.`strFilename` AS `strFileName`,
`User1Videos93`.`path`.`strPath` AS `strPath`,
`User1Videos93`.`files`.`playCount` AS `playCount`,
`User1Videos93`.`files`.`lastPlayed` AS `lastPlayed`,
`User1Videos93`.`files`.`dateAdded` AS `dateAdded`,
`User1Videos93`.`tvshow`.`c00` AS `strTitle`,
`User1Videos93`.`tvshow`.`c14` AS `strStudio`,
`User1Videos93`.`tvshow`.`c05` AS `premiered`,
`User1Videos93`.`tvshow`.`c13` AS `mpaa`,
`User1Videos93`.`tvshow`.`c16` AS `strShowPath`,
`User1Videos93`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`User1Videos93`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`User1Videos93`.`seasons`.`idSeason` AS `idSeason`
 from ((`User1Videos93`.`episode` join `User1Videos93`.`files` on((`User1Videos93`.`files`.`idFile` = `User1Videos93`.`episode`.`idFile`))) join `User1Videos93`.`tvshow` on((`User1Videos93`.`tvshow`.`idShow` = `User1Videos93`.`episode`.`idShow`)) left join `User1Videos93`.`seasons` on((`User1Videos93`.`seasons`.`idShow` = `User1Videos93`.`episode`.`idShow` AND `User1Videos93`.`seasons`.`season` = `User1Videos93`.`episode`.`c12`)) join `User1Videos93`.`path` on((`User1Videos93`.`files`.`idPath` = `User1Videos93`.`path`.`idPath`))left join `User1Videos93`.`bookmark` on((`User1Videos93`.`bookmark`.`idFile` = `User1Videos93`.`episode`.`idFile` AND `User1Videos93`.`bookmark`.`type` = 1)) );

CREATE VIEW `User1Videos93`.`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 ((`User1Videos93`.`tvshow` left join `User1Videos93`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `User1Videos93`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE VIEW `User1Videos93`.`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`,`tvshowcounts`.`dateAdded` AS `dateAdded`,`tvshowcounts`.`lastPlayed` AS `lastPlayed`,`tvshowcounts`.`totalCount` AS `totalCount`,`tvshowcounts`.`watchedcount` AS `watchedcount`,`tvshowcounts`.`totalSeasons` AS `totalSeasons` from (((`User1Videos93`.`tvshow` left join `User1Videos93`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `User1Videos93`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) join `User1Videos93`.`tvshowcounts` on((`tvshow`.`idShow` = `tvshowcounts`.`idShow`))) group by `tvshow`.`idShow`;

CREATE VIEW `User1Videos93`.`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 `strStudio`,`tvshow_view`.`c13` AS `mpaa`,count(distinct `episode_view`.`idEpisode`) AS `episodes`,count(`files`.`playCount`) AS `playCount` from (((`User1Videos93`.`seasons` join `User1Videos93`.`tvshow_view` on((`tvshow_view`.`idShow` = `seasons`.`idShow`))) join `User1Videos93`.`episode_view` on(((`episode_view`.`idShow` = `seasons`.`idShow`) and (`episode_view`.`c12` = `seasons`.`season`)))) join `User1Videos93`.`files` on((`files`.`idFile` = `episode_view`.`idFile`))) group by `seasons`.`idSeason`;