[xrsxj]

UPDATE
I use this with Frodo 12.2 - I do not know if this will run on future versions.

I have a really simple SQL workaround that consider the sql login. That means you need to have more than one login to your SQL-Server (e.g. xbmc1, xbmc2, ..., xbmcN).
You can have now profiles with different resume bookmarks.
For "real" Bookmark this will not work, but the resume bookmarks are now divided by SQL login. I think this is pretty cool! (Although it was solved with triggers)

Thanks to Kib for giving me some input

*removed old code*

Always newest code is on my github repo

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);