[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!