Here is an updated version I've updated to add in the new tvshowcounts view that's now in use. I've tested and verified it's working on the newest 15 beta database version 92. All the original code of course is Bakslash's awesome work! make sure you use HeidiSQL to run the query, for some reason that i'm not quite able to figure out the bu_files trigger refuses to be run otherwise.
Code:
DROP TRIGGER IF EXISTS `bi_files`;
CREATE TRIGGER `bi_files` BEFORE INSERT ON `files` FOR EACH ROW SET NEW.dateAdded = now();
DROP TABLE IF EXISTS `bookmark`;
DROP VIEW IF EXISTS `bookmark`;
DROP TABLE IF EXISTS `bookmark_orig`;
DROP TRIGGER IF EXISTS `bi_bookmark`;
CREATE TABLE `bookmark_orig` (
`idBookmark` INT(11) NOT NULL AUTO_INCREMENT,
`idFile` INT(11) NULL DEFAULT NULL,
`timeInSeconds` DOUBLE NULL DEFAULT NULL,
`totalTimeInSeconds` DOUBLE NULL DEFAULT NULL,
`thumbNailImage` TEXT NULL,
`player` TEXT NULL,
`playerState` TEXT NULL,
`type` INT(11) NULL DEFAULT NULL,
`sqlUser` VARCHAR(250),
PRIMARY KEY (`idBookmark`),
INDEX `ix_bookmark` (`idFile`, `type`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=0;
CREATE TRIGGER `bi_bookmark` BEFORE INSERT ON `bookmark_orig` FOR EACH ROW SET NEW.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
CREATE VIEW `bookmark` AS
SELECT idBookmark, idFile, timeinSeconds, totalTimeInSeconds, thumbnailImage, player, playerstate, type
FROM bookmark_orig
WHERE bookmark_orig.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
DROP TABLE IF EXISTS `filestate`;
CREATE TABLE `filestate` (
`idFile` INT(11) NOT NULL,
`lastPlayed` TEXT,
`playCount` INT,
`sqlUser` VARCHAR(250) NOT NULL,
UNIQUE INDEX `idFile_sqlUser` (`idFile`, `sqlUser`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
DELIMITER |
DROP TRIGGER IF EXISTS `bu_files`;
CREATE TRIGGER `bu_files` BEFORE UPDATE ON `files`
FOR EACH ROW BEGIN
DELETE FROM filestate WHERE filestate.idFile = new.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
INSERT INTO filestate (idFile, lastPlayed, playCount, sqlUser) VALUES(new.idFile, new.lastPlayed, new.playCount, SUBSTRING_INDEX(USER(),'@',1));
END;
DROP VIEW IF EXISTS `episode_view`;
CREATE VIEW `episode_view` AS
select episode.*,
files.strFilename AS strFileName,
path.strPath AS strPath,
filestate.playCount AS playCount,
filestate.lastPlayed AS lastPlayed,
files.dateAdded AS dateAdded,
tvshow.c00 AS strTitle,
tvshow.c14 AS studio,
tvshow.c05 AS premiered,
tvshow.c13 AS mpaa,
bookmark_orig.timeInSeconds AS resumeTimeInSeconds,
bookmark_orig.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_orig ON bookmark_orig.idFile=episode.idFile AND bookmark_orig.type=1 AND bookmark_orig.sqlUser = SUBSTRING_INDEX(USER(),'@',1)
LEFT JOIN filestate ON filestate.idFile = files.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
DROP VIEW IF EXISTS `tvshowcounts`;
CREATE VIEW `tvshowcounts` AS
select tvshow.idShow AS idShow,
max(filestate.lastPlayed) AS lastPlayed,
nullif(count(episode.c12),0) AS totalCount,
count(filestate.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
LEFT JOIN filestate ON filestate.idFile = files.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1)
group by tvshow.idShow;
DROP VIEW IF EXISTS `season_view`;
CREATE 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(filestate.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
LEFT JOIN filestate ON filestate.idFile = episode_view.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1)
group by seasons.idSeason;
DROP VIEW IF EXISTS `movie_view`;
CREATE VIEW `movie_view` AS
select movie.*,
sets.strSet AS strSet,
files.strFilename AS strFileName,
path.strPath AS strPath,
filestate.playCount AS playCount,
filestate.lastPlayed AS lastPlayed,
files.dateAdded AS dateAdded,
bookmark_orig.timeInSeconds AS resumeTimeInSeconds,
bookmark_orig.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_orig ON bookmark_orig.idFile=movie.idFile AND bookmark_orig.type=1 AND bookmark_orig.sqlUser = SUBSTRING_INDEX(USER(),'@',1)
LEFT JOIN filestate ON filestate.idFile = files.idFile AND filestate.sqlUser = SUBSTRING_INDEX(USER(),'@',1);
(Although it was solved with triggers)
