Mysql profiles and watched status

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
N4TH4N Offline
Member
Posts: 75
Joined: Jul 2010
Reputation: 4
Location: Australia
Post: #46
(2014-05-11 15:47)sompie Wrote:  I've remade my database as deathraiider but xbmc won't read the watched status from the extra users (i've reimported data from the 75 database in the globalvideo table)

Sorry, but that tutorial is assuming that you are starting with a new database.

If you want send me a SQL dump, i can have a look at it. Also not sure if its a typo but i use a globalfiles table not globalvideo. Either way i may be able to help.

Also, i noted that there are only 3 differences between the old 75 and the new 78 databases which are to do with StereoMode. So it may be easier to just add those to you old existing working database and adjust from 75 to 78.

settings, StereoMode
settings, StereoInvert
streamdetails, strStereoMode
(This post was last modified: 2014-05-12 03:55 by N4TH4N.)
find quote
sompie Offline
Junior Member
Posts: 23
Joined: Jan 2011
Reputation: 0
Location: Belgium
Post: #47
I know that your tutorial is for creating a new database but I used your system for frodo. I thought if i create a new database and the re-import the previous data into globalfiles(and i just mistype the word :-)) it would work. I will send a dump in a pm.
If you edit the previous database (75) you also have to edit every view to watch for 78 database instead of 75 database
(This post was last modified: 2014-05-12 11:00 by sompie.)
find quote
schumi2004 Offline
Posting Freak
Posts: 1,222
Joined: Aug 2011
Reputation: 14
Post: #48
(2013-12-01 23:18)bakslash Wrote:  Here is a workaround that does not need as much space as two databases.

I have read this whole thread but it's unclear for me what t use for existing database and there watch statuses for Gotham.
My current situation is that i want to use 2 profiles and don't want to start from scratch but have a copy of the watched statuses and start from there.

If I understand correctly this is for resume points only and not watched statuses correct?
/edit: I now know it's for watched status also, didn't read the next two post were it says it does also keep track of watched.

Is there a guide that explains step by step what to do for my situation (and I'm probably not the only one with this setup)
I don't want to screw up existing database Wink

/edit:
I also have a difficult situation. It is possible that a library update is triggered on the main database update system when a other user then master is logged in. What will happen if that's the case? Ideal would be that database can be updated on all users but that only watched status etc is being kept separate.

[Image: all-thin-banner.jpg]
(This post was last modified: 2014-06-01 15:49 by schumi2004.)
find quote
yosubis Offline
Junior Member
Posts: 13
Joined: May 2014
Reputation: 0
Post: #49
I followed bohdans way. Really liked it but as said before, it doesn't keep the watched state. The reason is the watched_history table doesn't have the file_id for the files in the library, so it can't write the changes. Is there a way to add a row per file_id as the query is sent?
find quote
yosubis Offline
Junior Member
Posts: 13
Joined: May 2014
Reputation: 0
Post: #50
OK, Managed to get it working.

I replaced the line:
Code:
CREATE TABLE watched_history (idFile int(11) NOT NULL,playCount int(11) DEFAULT NULL,lastPlayed text,PRIMARY KEY (idFile)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

with (Pretty sure that not the way to do it, but it worked):
Code:
CREATE TABLE watched_history (idFile int(11) NOT NULL,playCount int(11) DEFAULT NULL,lastPlayed text,PRIMARY KEY (idFile)) ENGINE=InnoDB DEFAULT CHARSET=utf8 SELECT idFile FROM MyVideos78.files;


And added triggers to the original database (that of Master Profile).

One to add a row when a new file is added:
Code:
CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
    FOR EACH ROW
        INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);

And one to delete when the row is deleted from the original database:
Code:
CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
    FOR EACH ROW
        DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
find quote
notch Offline
Junior Member
Posts: 1
Joined: Jun 2014
Reputation: 0
Post: #51
(2014-06-03 02:38)yosubis Wrote:  One to add a row when a new file is added:
Code:
CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
    FOR EACH ROW
        INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);

And one to delete when the row is deleted from the original database:
Code:
CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
    FOR EACH ROW
        DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
Your code is working well, although you may have difficulties with more databases (User2Videos etc.) - e.g. MariaDB doesn't support multiple triggers with the same event for the same table (not sure about MySQL). Here is my fix for this situation:

Code:
CREATE TRIGGER insert_new_watched AFTER INSERT ON MyVideos78.files
    FOR EACH ROW BEGIN
        INSERT INTO User1Videos78.watched_history(idFile) VALUES (NEW.idFile);
        INSERT INTO User2Videos78.watched_history(idFile) VALUES (NEW.idFile);
END
Code:
CREATE TRIGGER remove_deleted_watched AFTER DELETE ON MyVideos78.files
    FOR EACH ROW BEGIN
        DELETE FROM User1Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
        DELETE FROM User2Videos78.watched_history WHERE idFile=OLD.idFile LIMIT 1;
END
Insert a line for user User3Videos etc.
find quote
Post Reply