XBMC Frodo Multiuser single media Database

RENAME TABLE  `MyVideos75`.`files` TO  `MyVideos75`.`globalfiles` ;

ALTER TABLE  `MyVideos75`.`globalfiles` ADD  `playCount2` INT( 11 ) NULL DEFAULT NULL , ADD  `lastPlayed2` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE  `MyVideos75`.`globalfiles` CHANGE  `playCount`  `playCount1` INT( 11 ) NULL DEFAULT NULL;
ALTER TABLE  `MyVideos75`.`globalfiles` CHANGE  `lastPlayed`  `lastPlayed1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

CREATE DATABASE  `UserVideos75` ;
CREATE VIEW `MyVideos75`.`files` AS
SELECT idFile, idPath, strFilename, playCount1 AS playCount, lastPlayed1 AS lastPlayed, dateAdded FROM `MyVideos75`.`globalfiles`;
CREATE VIEW `UserVideos75`.`files` AS
SELECT idFile, idPath, strFilename, playCount2 AS playCount, lastPlayed2 AS lastPlayed, dateAdded FROM `MyVideos75`.`globalfiles`;
CREATE VIEW `UserVideos75`.`actorlinkepisode` AS
SELECT * FROM `MyVideos75`.`actorlinkepisode`;
CREATE VIEW `UserVideos75`.`actorlinkmovie` AS
SELECT * FROM `MyVideos75`.`actorlinkmovie`;
CREATE VIEW `UserVideos75`.`actorlinktvshow` AS
SELECT * FROM `MyVideos75`.`actorlinktvshow`;
CREATE VIEW `UserVideos75`.`actors` AS
SELECT * FROM `MyVideos75`.`actors`;
CREATE VIEW `UserVideos75`.`art` AS
SELECT * FROM `MyVideos75`.`art`;
CREATE VIEW `UserVideos75`.`artistlinkmusicvideo` AS
SELECT * FROM `MyVideos75`.`artistlinkmusicvideo`;
CREATE VIEW `UserVideos75`.`country` AS
SELECT * FROM `MyVideos75`.`country`;
CREATE VIEW `UserVideos75`.`countrylinkmovie` AS
SELECT * FROM `MyVideos75`.`countrylinkmovie`;
CREATE VIEW `UserVideos75`.`directorlinkepisode` AS
SELECT * FROM `MyVideos75`.`directorlinkepisode`;
CREATE VIEW `UserVideos75`.`directorlinkmovie` AS
SELECT * FROM `MyVideos75`.`directorlinkmovie`;
CREATE VIEW `UserVideos75`.`directorlinkmusicvideo` AS
SELECT * FROM `MyVideos75`.`directorlinkmusicvideo`;
CREATE VIEW `UserVideos75`.`directorlinktvshow` AS
SELECT * FROM `MyVideos75`.`directorlinktvshow`;
CREATE VIEW `UserVideos75`.`episode` AS
SELECT * FROM `MyVideos75`.`episode`;
CREATE VIEW `UserVideos75`.`genre` AS
SELECT * FROM `MyVideos75`.`genre`;
CREATE VIEW `UserVideos75`.`genrelinkmovie` AS
SELECT * FROM `MyVideos75`.`genrelinkmovie`;
CREATE VIEW `UserVideos75`.`genrelinkmusicvideo` AS
SELECT * FROM `MyVideos75`.`genrelinkmusicvideo`;
CREATE VIEW `UserVideos75`.`genrelinktvshow` AS
SELECT * FROM `MyVideos75`.`genrelinktvshow`;
CREATE VIEW `UserVideos75`.`movie` AS
SELECT * FROM `MyVideos75`.`movie`;
CREATE VIEW `UserVideos75`.`movielinktvshow` AS
SELECT * FROM `MyVideos75`.`movielinktvshow`;
CREATE VIEW `UserVideos75`.`musicvideo` AS
SELECT * FROM `MyVideos75`.`musicvideo`;
CREATE VIEW `UserVideos75`.`path` AS
SELECT * FROM `MyVideos75`.`path`;
CREATE VIEW `UserVideos75`.`seasons` AS
SELECT * FROM `MyVideos75`.`seasons`;
CREATE VIEW `UserVideos75`.`sets` AS
SELECT * FROM `MyVideos75`.`sets`;
CREATE VIEW `UserVideos75`.`settings` AS
SELECT * FROM `MyVideos75`.`settings`;
CREATE VIEW `UserVideos75`.`stacktimes` AS
SELECT * FROM `MyVideos75`.`stacktimes`;
CREATE VIEW `UserVideos75`.`streamdetails` AS
SELECT * FROM `MyVideos75`.`streamdetails`;
CREATE VIEW `UserVideos75`.`studio` AS
SELECT * FROM `MyVideos75`.`studio`;
CREATE VIEW `UserVideos75`.`studiolinkmovie` AS
SELECT * FROM `MyVideos75`.`studiolinkmovie`;
CREATE VIEW `UserVideos75`.`studiolinkmusicvideo` AS
SELECT * FROM `MyVideos75`.`studiolinkmusicvideo`;
CREATE VIEW `UserVideos75`.`studiolinktvshow` AS
SELECT * FROM `MyVideos75`.`studiolinktvshow`;
CREATE VIEW `UserVideos75`.`tag` AS
SELECT * FROM `MyVideos75`.`tag`;
CREATE VIEW `UserVideos75`.`taglinks` AS
SELECT * FROM `MyVideos75`.`taglinks`;
CREATE VIEW `UserVideos75`.`tvshow` AS
SELECT * FROM `MyVideos75`.`tvshow`;
CREATE VIEW `UserVideos75`.`tvshowlinkpath` AS
SELECT * FROM `MyVideos75`.`tvshowlinkpath`;
CREATE VIEW `UserVideos75`.`version` AS
SELECT * FROM `MyVideos75`.`version`;
CREATE VIEW `UserVideos75`.`writerlinkepisode` AS
SELECT * FROM `MyVideos75`.`writerlinkepisode`;
CREATE VIEW `UserVideos75`.`writerlinkmovie` AS
SELECT * FROM `MyVideos75`.`writerlinkmovie`;
/* in order to maintain unique resume times - establish new bookmark */
USE UserVideos75;
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);

/* end here - start again */

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

CREATE VIEW `UserVideos75`.`musicvideoview` AS SELECT
`UserVideos75`.`musicvideo`.`idMVideo` AS `idMVideo`,
`UserVideos75`.`musicvideo`.`idFile` AS `idFile`,
`UserVideos75`.`musicvideo`.`c00` AS `c00`,
`UserVideos75`.`musicvideo`.`c01` AS `c01`,
`UserVideos75`.`musicvideo`.`c02` AS `c02`,
`UserVideos75`.`musicvideo`.`c03` AS `c03`,
`UserVideos75`.`musicvideo`.`c04` AS `c04`,
`UserVideos75`.`musicvideo`.`c05` AS `c05`,
`UserVideos75`.`musicvideo`.`c06` AS `c06`,
`UserVideos75`.`musicvideo`.`c07` AS `c07`,
`UserVideos75`.`musicvideo`.`c08` AS `c08`,
`UserVideos75`.`musicvideo`.`c09` AS `c09`,
`UserVideos75`.`musicvideo`.`c10` AS `c10`,
`UserVideos75`.`musicvideo`.`c11` AS `c11`,
`UserVideos75`.`musicvideo`.`c12` AS `c12`,
`UserVideos75`.`musicvideo`.`c13` AS `c13`,
`UserVideos75`.`musicvideo`.`c14` AS `c14`,
`UserVideos75`.`musicvideo`.`c15` AS `c15`,
`UserVideos75`.`musicvideo`.`c16` AS `c16`,
`UserVideos75`.`musicvideo`.`c17` AS `c17`,
`UserVideos75`.`musicvideo`.`c18` AS `c18`,
`UserVideos75`.`musicvideo`.`c19` AS `c19`,
`UserVideos75`.`musicvideo`.`c20` AS `c20`,
`UserVideos75`.`musicvideo`.`c21` AS `c21`,
`UserVideos75`.`musicvideo`.`c22` AS `c22`,
`UserVideos75`.`musicvideo`.`c23` AS `c23`,
`UserVideos75`.`files`.`strFilename` AS `strFileName`,
`UserVideos75`.`path`.`strPath` AS `strPath`,
`UserVideos75`.`files`.`playCount` AS `playCount`,
`UserVideos75`.`files`.`lastPlayed` AS `lastPlayed`,
`UserVideos75`.`files`.`dateAdded` AS `dateAdded`,
`UserVideos75`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`UserVideos75`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM ((`UserVideos75`.`musicvideo` JOIN `UserVideos75`.`files` ON((`UserVideos75`.`files`.`idFile` = `UserVideos75`.`musicvideo`.`idFile`))) JOIN `UserVideos75`.`path` ON((`UserVideos75`.`path`.`idPath` = `UserVideos75`.`files`.`idPath`))LEFT JOIN `UserVideos75`.`bookmark` ON((`UserVideos75`.`bookmark`.`idFile` = `UserVideos75`.`musicvideo`.`idFile` AND `UserVideos75`.`bookmark`.`type` = 1)));

/* fine to here debug rest episodeview/tvshowview */

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


CREATE VIEW `UserVideos75`.`tvshowview` AS SELECT
`UserVideos75`.`tvshow`.`idShow` AS `idShow`,
`UserVideos75`.`tvshow`.`c00` AS `c00`,
`UserVideos75`.`tvshow`.`c01` AS `c01`,
`UserVideos75`.`tvshow`.`c02` AS `c02`,
`UserVideos75`.`tvshow`.`c03` AS `c03`,
`UserVideos75`.`tvshow`.`c04` AS `c04`,
`UserVideos75`.`tvshow`.`c05` AS `c05`,
`UserVideos75`.`tvshow`.`c06` AS `c06`,
`UserVideos75`.`tvshow`.`c07` AS `c07`,
`UserVideos75`.`tvshow`.`c08` AS `c08`,
`UserVideos75`.`tvshow`.`c09` AS `c09`,
`UserVideos75`.`tvshow`.`c10` AS `c10`,
`UserVideos75`.`tvshow`.`c11` AS `c11`,
`UserVideos75`.`tvshow`.`c12` AS `c12`,
`UserVideos75`.`tvshow`.`c13` AS `c13`,
`UserVideos75`.`tvshow`.`c14` AS `c14`,
`UserVideos75`.`tvshow`.`c15` AS `c15`,
`UserVideos75`.`tvshow`.`c16` AS `c16`,
`UserVideos75`.`tvshow`.`c17` AS `c17`,
`UserVideos75`.`tvshow`.`c18` AS `c18`,
`UserVideos75`.`tvshow`.`c19` AS `c19`,
`UserVideos75`.`tvshow`.`c20` AS `c20`,
`UserVideos75`.`tvshow`.`c21` AS `c21`,
`UserVideos75`.`tvshow`.`c22` AS `c22`,
`UserVideos75`.`tvshow`.`c23` AS `c23`,
`UserVideos75`.`path`.`strPath` AS `strPath`,
`UserVideos75`.`path`.`dateAdded` AS `dateAdded`,
MAX(`UserVideos75`.`files`.`lastPlayed`) AS `lastPlayed`,
NULLIF(COUNT(`UserVideos75`.`episode`.`c12`),0) AS `totalCount`,
COUNT(`UserVideos75`.`files`.`playCount`) AS `watchedcount`,
NULLIF(COUNT(DISTINCT `UserVideos75`.`episode`.`c12`),0) AS `totalSeasons`
FROM (((`UserVideos75`.`tvshow` LEFT JOIN `UserVideos75`.`tvshowlinkpath` ON((`UserVideos75`.`tvshowlinkpath`.`idShow` = `UserVideos75`.`tvshow`.`idShow`))) LEFT JOIN `UserVideos75`.`path` ON((`UserVideos75`.`path`.`idPath` = `UserVideos75`.`tvshowlinkpath`.`idPath`))) LEFT JOIN `UserVideos75`.`episode` ON((`UserVideos75`.`episode`.`idShow` = `UserVideos75`.`tvshow`.`idShow`)) LEFT JOIN `UserVideos75`.`files` ON((`UserVideos75`.`files`.`idFile` = `UserVideos75`.`episode`.`idFile`))) GROUP BY `UserVideos75`.`tvshow`.`idShow`;