Req Profile specific bookmarks
#16
I have been using bohdans method with XBMC 13.2, although the library updates are not writing to the database unless the original Master Profile is logged on.

If I try this method, will the library updates work while the secondary user is logged on? schumi2004 is saying above it's "not allowed to update database with User1".
Reply
#17
(2014-09-30, 21:04)bmac88 Wrote: I have been using bohdans method with XBMC 13.2, although the library updates are not writing to the database unless the original Master Profile is logged on.

If I try this method, will the library updates work while the secondary user is logged on? schumi2004 is saying above it's "not allowed to update database with User1".

Not sure if I get you right. It should only depend on the SQL user. Each XBMC profile can have its own advanced settings.xml
Reply
#18
I understand your method uses two different SQL users, so maybe that is what he means by user1? If I follow your method, will I have two XBMC profiles, either of which can be logged on while a library update is triggered by Sickbeard or CouchPotato?

For example: if the Master Profile is logged on and a library update triggers, the new episode/movie shows up in the second XBMC profile. (this still works with bohdans method)

AND

If the second XBMC profile is logged on while a library update triggers, will the new episode/movie show up as well in the Master Profile? (this does not work with bohdans method)
Reply
#19
I'm late to the party I guess, but I finally upgraded to 14. I didn't want to re-scan the complete library, so I diff'ed the old and the new db. Here is the upgrade script I came up with.
Short "How to use":
- create a copy of the old db with correct name (eg MyVideos90)
- apply script
- If the last part of the script fails (the part regarding version) just manually edit the version table content (78 -> 90).

I'll try to update faster next time. maybe someone else can jump in and provide the parts about creating a copy of the db and the version table...


@bmac88 (if you still need it): using this method, you'll decide which bookmarks to use by setting the login data in the advancedsettings.xml. Every login should be able to update the library. both of your scenarios should work.

Quote:DELIMITER ;;
SET FOREIGN_KEY_CHECKS=0;;

DROP TRIGGER `delete_episode`;;
CREATE TRIGGER `delete_episode` AFTER DELETE ON `episode`
FOR EACH ROW BEGIN DELETE FROM actorlinkepisode WHERE idEpisode=old.idEpisode; DELETE FROM directorlinkepisode WHERE idEpisode=old.idEpisode; DELETE FROM writerlinkepisode WHERE idEpisode=old.idEpisode; DELETE FROM art WHERE media_id=old.idEpisode AND media_type='episode'; END;;

DROP TRIGGER `delete_movie`;;
CREATE TRIGGER `delete_movie` AFTER DELETE ON `movie`
FOR EACH ROW BEGIN
DELETE FROM genrelinkmovie WHERE genrelinkmovie.idMovie= old.idMovie; DELETE FROM actorlinkmovie WHERE idMovie=old.idMovie; DELETE FROM directorlinkmovie WHERE idMovie=old.idMovie; DELETE FROM studiolinkmovie WHERE idMovie=old.idMovie; DELETE FROM countrylinkmovie WHERE idMovie=old.idMovie; DELETE FROM writerlinkmovie WHERE idMovie=old.idMovie; DELETE FROM movielinktvshow WHERE idMovie=old.idMovie; DELETE FROM art WHERE media_id=old.idMovie AND media_type='movie'; DELETE FROM taglinks WHERE idMedia=old.idMovie AND media_type='movie'; END;;

DROP TRIGGER `delete_musicvideo`;;
CREATE TRIGGER `delete_musicvideo` AFTER DELETE ON `musicvideo`
FOR EACH ROW BEGIN DELETE FROM artistlinkmusicvideo WHERE idMVideo=old.idMVideo; DELETE FROM directorlinkmusicvideo WHERE idMVideo=old.idMVideo; DELETE FROM genrelinkmusicvideo WHERE idMVideo=old.idMVideo; DELETE FROM studiolinkmusicvideo WHERE idMVideo=old.idMVideo; DELETE FROM art WHERE media_id=old.idMVideo AND media_type='musicvideo'; DELETE FROM taglinks WHERE idMedia=old.idMVideo AND media_type='musicvideo'; END;;

ALTER TABLE `path` ADD COLUMN `idParentPath` int(11) NULL DEFAULT NULL AFTER `dateAdded`;;
CREATE INDEX `ix_path2` ON `path`(`idParentPath`) USING BTREE ;;

ALTER TABLE `tvshow` DROP INDEX `ixTVShowBasePath`;;

DROP TRIGGER `delete_tvshow`;;
CREATE TRIGGER `delete_tvshow` AFTER DELETE ON `tvshow`
FOR EACH ROW BEGIN DELETE FROM actorlinktvshow WHERE idShow=old.idShow; DELETE FROM directorlinktvshow WHERE idShow=old.idShow; DELETE FROM tvshowlinkpath WHERE idShow=old.idShow; DELETE FROM genrelinktvshow WHERE idShow=old.idShow; DELETE FROM movielinktvshow WHERE idShow=old.idShow; DELETE FROM seasons WHERE idShow=old.idShow; DELETE FROM art WHERE media_id=old.idShow AND media_type='tvshow'; DELETE FROM taglinks WHERE idMedia=old.idShow AND media_type='tvshow'; END;;

ALTER
ALGORITHM=UNDEFINED
SQL SECURITY DEFINER
VIEW `episodeview` AS
select `episode`.`idEpisode` AS `idEpisode`,`episode`.`idFile` AS `idFile`,`episode`.`c00` AS `c00`,`episode`.`c01` AS `c01`,`episode`.`c02` AS `c02`,`episode`.`c03` AS `c03`,`episode`.`c04` AS `c04`,`episode`.`c05` AS `c05`,`episode`.`c06` AS `c06`,`episode`.`c07` AS `c07`,`episode`.`c08` AS `c08`,`episode`.`c09` AS `c09`,`episode`.`c10` AS `c10`,`episode`.`c11` AS `c11`,`episode`.`c12` AS `c12`,`episode`.`c13` AS `c13`,`episode`.`c14` AS `c14`,`episode`.`c15` AS `c15`,`episode`.`c16` AS `c16`,`episode`.`c17` AS `c17`,`episode`.`c18` AS `c18`,`episode`.`c19` AS `c19`,`episode`.`c20` AS `c20`,`episode`.`c21` AS `c21`,`episode`.`c22` AS `c22`,`episode`.`c23` AS `c23`,`episode`.`idShow` AS `idShow`,`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 `strStudio`,`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` = `episode`.`idFile`) and (`filestate`.sqlUser = SUBSTRING_INDEX(USER(),'@',1))))) ;;

CREATE
ALGORITHM = UNDEFINED
SQL SECURITY DEFINER
VIEW `seasonview` AS
select
`seasons`.`idSeason` AS `idSeason`,
`seasons`.`idShow` AS `idShow`,
`seasons`.`season` AS `season`,
`tvshowview`.`strPath` AS `strPath`,
`tvshowview`.`c00` AS `showTitle`,
`tvshowview`.`c01` AS `plot`,
`tvshowview`.`c05` AS `premiered`,
`tvshowview`.`c08` AS `genre`,
`tvshowview`.`c14` AS `strStudio`,
`tvshowview`.`c13` AS `mpaa`,
count(distinct `episodeview`.`idEpisode`) AS `episodes`,
count(`filestate`.`playCount`) AS `playCount`
from
(((`seasons`
join `tvshowview` ON ((`tvshowview`.`idShow` = `seasons`.`idShow`)))
join `episodeview` ON (((`episodeview`.`idShow` = `seasons`.`idShow`)
and (`episodeview`.`c12` = `seasons`.`season`))))
join `files` ON ((`files`.`idFile` = `episodeview`.`idFile`))
left join `filestate` ON (((`filestate`.`idFile` = `episodeview`.`idFile`)
and (`filestate`.`sqlUser` = SUBSTRING_INDEX(USER(), '@', 1)))))
group by `seasons`.`idSeason`;;

CREATE
ALGORITHM = UNDEFINED
SQL SECURITY DEFINER
VIEW `tvshowcounts` AS
select
`tvshow`.`idShow` AS `idShow`,
max(`files`.`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` = `episode`.`idFile`)
and (`filestate`.`sqlUser` = SUBSTRING_INDEX(USER(), '@', 1)))))
group by `tvshow`.`idShow`;;

ALTER
ALGORITHM=UNDEFINED
SQL SECURITY DEFINER
VIEW `tvshowview` 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 (((`tvshow` left join `tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) join `tvshowcounts` on((`tvshow`.`idShow` = `tvshowcounts`.`idShow`))) group by `tvshow`.`idShow` ;;

SET FOREIGN_KEY_CHECKS=1;;

DELETE FROM version;;
INSERT INTO version (idVersion,iCompressCount) VALUES(90,0);;
Reply
#20
(2013-12-01, 22:00)bakslash Wrote: 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! Cool (Although it was solved with triggers)

Thanks to Kib for giving me some input Smile

*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);
Reply
#21
Hi. First of all thanks to you and to the original dev. It's the first time I try to use this kind of solution and I like this concept better than the multiple database one.
A question. Is it really necessary dropping and adding back the tables? Can't you simply alter the table and add the old bookmarks to the account the user wants? It's annoying to lose bookmarks and watched state...
Reply
#22
I didn't look into the reasons for the table drop. However, with the great "Texture Cache Maintenance utility" (http://forum.kodi.tv/showthread.php?tid=158373), you can just dump the current watched status to a file and re-import after db-migration.

I guess this is much more practical since a sql scrupt couldn't take care of existing states for multiple users (from different dbs) etc.
Reply
#23
hey
does this still work with Isengard?
Reply
#24
so, *someone* accidentally updated to the latest kodi version with their android tablet and I had to migrate all other openelec boxes to the current beta a little early.

in the process, I created two scripts. One to modify a "clean" MyVideos99 (Kodi 16) table to include the additional filestates for each user and another one to actually upgrade an existing MyVideos93 (Kodi 15, with existing filestates stuff) to the new MyVideos99 structure. maybe they are helpful to anyone.

I tested both versions with HeidiSQL, but as always - no guarantees.
CloudFlare prevents embedding the scripts as code, so I need to fall back to dropbox. sorry for that.


Upgrade-MyVideos93-to-MyVideos99.sql: You need to create a 1:1 copy of MyVideos93, name it MyVideos99 and apply the script. after that, you still need to manually edit the one entry in the "version" table.


*edit*: I noticed that kodi is doing some adjustments on the data during upgrade. Main problem here is that a new field "idSeason" is populated in the episode table. I added Upgrade-episode-table.sql to take care of this, but I'm not sure on what else might be missing. Maybe we should prefer method 2 below... If you want to risk it, just execute the new script after the actual upgrade.


If you are coming from a different version then MyVideos93 or don't have a database yet or {insert any other reason}, you can use Modify-MyVideos99.sql. Just apply to a clean db created by kodi. one could use this in combination with the very good import / export feature of texturecache.py to still import existing watched states. to do this, just import the watched states after configuring the advancedsettings.xml for the user the watched states should be applied to.
Reply



Logout Mark Read Team Forum Stats Members Help
Profile specific bookmarks0
This forum uses Lukasz Tkacz MyBB addons.