[craigd]

Apologies if this has been covered but had a search and couldn't find and don't remember it being discussed in the running mysql threads.

I'm using mysql now throughout my flat and loving it, the improved functionality and ability to stop and pickup movies between rooms is awesome but I'm not sure how profiles are interacting with mysql.

At the moment I'm not using profiles with xbmc though I would like to as it would make it easier for me tracking what tv I've watched now all the fall shows are starting back up.

As far as I was aware the watched status had been moved to the user settings for profiles but when using mysql the watched status seems to be part of the db as it cascades across all systems connected to it so I'm unsure how this is being stored.

So if using my sql and separate profiles will the watched status differ between users or would each profile also need a separate db?

[jmarshall]

Yes, separate watched requires separate dbs at this point.

[Japaja]

I know it's an old thread, but I didn't find this info anywhere else, so I was wondering is there any update on this?
I just want to have user profiles with separate watched lists. Meaning same media info, same sources, everything the same except watched status. What's currently the easiest way to do that?

[Sk3wby]

XMBC (margro's mp tv server verison) with shared database is awesome.

It's great being able to share media via mysql with other house-mates but annoying when any one of them watches an episode on their pc and mine gets marked as watched too.

Is there an option to store the watched status locally on each computer if needed or ever likely to be added?

[Dak0ta]

Any update on this one? I'd really like to get something rigged up.

The only thing I can think of offhand is some type of database where the tables are all a view of the main database, but the watched history is a real table in that database only.

[Dak0ta]

I just had a quick look at it, and at a glance it looks like it could be done by converting all the tables to view except for `xbmc_video`.`files` - `files` is the table that seems to store the watched data. But having a second database that's 99% a view of the first, but with a files table that is local, we should be able to have a working secondary unit (which is what I really want, units not profiles) that can write it's own watched data.

I'll just need to write a trigger to replicate everything over from the real `files` table on insert/update/delete except for the playCount and lastPlayed fields.

I'll post an update once I get a change to actually do it, but so far I've got a working version where `tvshowview` is working off of views except for the `files` table.

[Dak0ta]

So this isn't a final version of what I've got going on, but this gives me the ability to have a secondary MySQL database that shares the library of the main database, but keeps it's own watched status.

For my purpose this is to run a second unit off the same DB (scraping done by the main) but have the watched status be independent. If there's a way to configure each profile to use a different MySQL database this could be used to maintain a watched list per profile as an interim solution. Ideally I'd like to get involved with the XBMC development and work on busting the watched status out into a separate table that could be isolated on a profile-by-profile basis which should be a fairly easy fix, just requires the profile name be incorporated into a bunch of the view queries and the updates.... but I digress.


DB: MySQL
Main Video DB: xbmc_video
Secondary Video DB: xbmc_video1

XBMC video one contains only 1 table, `files`. The rest of them are views of xbmc_video

Ie...

CREATE VIEW `xbmc_video1`.`actors` AS
SELECT * FROM `xbmc_video`.actors`

As the only thing we need to actually reference locally is the `xbmc_video1`.`files` table, and make a small change to the view called `tvshowview` to use the `xbmc_video1` copy of `files` in it's view instead of the main one.


Then once we've got xbmc_video1 entirely setup as views, we need to add triggers to `xbmc_video`.`files` to ensure all inserts, updates, and deletions are replicated over to `xbmc_video1`.`files` with the exception of the playcount data.

=================TRIGGER CREATION SQL=================

delimiter $$

CREATE
TRIGGER `xbmc_video`.`replicate_files_insert`
AFTER INSERT ON `xbmc_video`.`files`
FOR EACH ROW
BEGIN
INSERT INTO xbmc_video1.files (idFile, idPath, strFilename, playCount, lastPlayed)
VALUES ( NEW.idFile,
NEW.idPath,
NEW.strFilename,
NEW.playCount,
NEW.lastPlayed );

END
$$

CREATE
TRIGGER `xbmc_video`.`replicate_files_update`
AFTER UPDATE ON `xbmc_video`.`files`
FOR EACH ROW
BEGIN
UPDATE `xbmc_video1`.`files`
SET idFile = NEW.idFile,
idPath = NEW.idPath,
strFilename = NEW.strFilename
/*
playCount = untouched
lastPlayed = untouched
*/
WHERE idFile = new.idFile;


END
$$

CREATE
TRIGGER `xbmc_video`.`replicate_files_delete`
AFTER DELETE ON `xbmc_video`.`files`
FOR EACH ROW
BEGIN
DELETE FROM `xbmc_video1`.`files` where idFile = OLD.idFile;
END
$$

=============================================================

These triggers will replicate the data over but not touch playCount or lastPlayed when the DB tried to update

Here's a standard update request that happens:
update files set playCount=1,lastPlayed='2011-07-16 20:55:34' where idFile=1399




So after you've got this setup, you need to change the DB on your second machine (or hopefully second profile if this is doable) to point to xbmc_video1 instead of xbmc_video, and make sure the one pointed to that DB doesn't have the media sources configured and is not scraping (ideally only 1 machine should be doing the scraping).


There is another option I'm going to look into which might be simpler than the triggers which is converting `files` into a update-able view. This may simplify things and allow multiple scrapers.


Anyway - looking for any feedback anyone has on this topic, and also if anyone has any info on if advancedsettings.xml will work on a profile-by-profile basis.


More to come!

[Dak0ta]

So I've tested out the view method rather than the triggers and it seems like a viable method as well. The only restriction will be is that a client using the xbmc_video1 database won't be able to delete records, but that's probably ideal anyway.

So quick setup was this.... On the xbmc_video1 database we create a view that points to a join between xbmc_video.files and a new table called xbmc_video1.watched_history

Create new table:

USE xbmc_video1;
delimiter $$
CREATE TABLE `watched_history` (
`idFile` int(11) NOT NULL,
`playCount` int(11) DEFAULT NULL,
`lastPlayed` text,
PRIMARY KEY (`idFile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$



Create the view for the copy DB to use:

use `xbmc_video1`;
DROP TABLE IF EXISTS `files`;
delimiter $$
CREATE VIEW `files` AS
select
`f`.`idFile` AS `idFile`,
`f`.`idPath` AS `idPath`,
`f`.`strFilename` AS `strFilename`,
`wh`.`playCount` AS `playCount`,
`wh`.`lastPlayed` AS `lastPlayed`

from (`xbmc_video`.`files` `f` join `xbmc_video1`.`watched_history` `wh` on((`f`.`idFile` = `wh`.`idFile`))) $$


This in addition to creating views of all the other tables mentioned earlier should let a second device fully use the primary library, but maintain it's own watched list.

Now I just need to see if there's a way to configure a database by profile and apply this to that.

[Dak0ta]

Of course I think the ideal situation is to break out the playCount and lastPlayed fields into their own table, and store a unique values for each profile.

I'm going to look into how to contribute to the development and possibly get this implemented as it seems to be a large feature gap right now as shared DB and multi-user requirements grow.

[akevit]

I'm not sure if I should post my own thread (from what I've found this is the only thread on the topic) but I'm curious if there's been any development on this idea since July?

Also, couldn't you configure a database by profile by just adding a separate advancedsettings.xml to the other profile's folder? Or maybe I'm not understanding you correctly.

[Ned Scott]

I'm not sure if I should post my own thread (from what I've found this is the only thread on the topic) but I'm curious if there's been any development on this idea since July?

Also, couldn't you configure a database by profile by just adding a separate advancedsettings.xml to the other profile's folder? Or maybe I'm not understanding you correctly.

Putting the separate advancedsettings.xml file in the other profile is how it's done, so basically each profile has its own advancedsettings.xml and its own MySQL DB.

I was meaning to do some research into this for our wiki guide (HOW-TO:Synchronize multiple XBMC libraries). I wonder if it's possible to have these two separate MySQL DBs, but have them sync their tables (all except the watched/playcount part, that is). I'm guessing that could possibly be done using generic MySQL tools/scripts.

[vikjon0]

I wonder if it's possible to have these two separate MySQL DBs, but have them sync their tables (all except the watched/playcount part, that is). I'm guessing that could possibly be done using generic MySQL tools/scripts.

Ned, I have been considering this and planned to look into it as soon as I move to MYSQL. I am pretty good with databases but have to read up on MYSQL.

Syncing is probably a valid possiblity but I was considering to use views, stored procedures etc to create separte databases for each profile but with many tables shared and filtered. I.e. All profiles will use the same library but some profiles will not see all content. I want to use meta data to exclude/include categores and freetext. (I am sure sume peolple can think of some free text to exclude....like YYY )

Separate watch status is not a problem in this scenario. What we need is to create tools to generate the database and to edit meta deta.

Perhaps we should start on a design document?

BTW, I am pretty sure this would work but I was thinking about the thumbnails. I have read somewhere that some scripts will display random thumbnails with no regards to what is in the current libray. If this is still the case it will not be handled by this solution. The only way I can think of is to sync the thumbnails based on lib. I guess someone should consider a move to access the thumbnails via API not directly on disk.

[akevit]

Ned, I have been considering this and planned to look into it as soon as I move to MYSQL. I am pretty good with databases but have to read up on MYSQL.

Syncing is probably a valid possiblity but I was considering to use views, stored procedures etc to create separte databases for each profile but with many tables shared and filtered. I.e. All profiles will use the same library but some profiles will not see all content. I want to use meta data to exclude/include categores and freetext. (I am sure sume peolple can think of some free text to exclude....like YYY )

Separate watch status is not a problem in this scenario. What we need is to create tools to generate the database and to edit meta deta.

Perhaps we should start on a design document?

BTW, I am pretty sure this would work but I was thinking about the thumbnails. I have read somewhere that some scripts will display random thumbnails with no regards to what is in the current libray. If this is still the case it will not be handled by this solution. The only way I can think of is to sync the thumbnails based on lib. I guess someone should consider a move to access the thumbnails via API not directly on disk.

The theory of two separate databases but one mirroring the other (minus watched status) seems like it should work just fine. I have extremely basic knowledge of MySQL and databases so I wouldn't be of much help regarding that.

Regarding the thumbnails, maybe I'm mistaken but won't using a centrally stored thumbnail database fix this? Each advancedsettings.xml file would just point to the same location ala http://wiki.xbmc.org/index.php?title=HOW...and_fanart

[a13x]

Hi together,

I'm in the same Situation, 3 XBMC Clients should have same content, but with seperate "watched/unwatched" state.
Any updates on this ?

greetings

a13x

[lanesman]

UP.
I am having the same issue but worse, even the mirroring DB isn't a valid solution for me as I use the 'Master Profile' and I have an issue where if I put in 'AdvancedSetting.xml' the DB name XBMC doesn't load properly for that profile. So, when I go to a different DB for another profile, then come back to master, the DB used is for the other profile.

A single DB, different tables- seem to me as the right way to go programming-wise as users don't generally need different data but different people in the same household do need different watched data.