Mysql profiles and watched status

  Thread Rating:
  • 0 Votes - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
N4TH4N Offline
Member
Posts: 77
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,300
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.
(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
XBraMC Offline
Junior Member
Posts: 1
Joined: Aug 2014
Reputation: 0
Post: #52
Hi,

First of all, I would like to thank you all for the help I already got from you :-D
Second, I looked already on the forum, and I’m not quite sure if I could post a own thread…
Third, I will gladly spend hours in playing, toying and fixing this, but I first wanted to get some information and the opinion of some experienced people ;-)
First a little inventory:
1 desktop computer, with all the files for sharing between XBMC-devices.
this is my main computer, MySQL is running, a ftp-server, Couch potato, Sick beard and SABNZB)
this pc contains my main movie-, series- and music-directory (accessible through SMB share and FTP)
connected to home-network, fixed IP
Notebook1, always connected in home-network
Notebook2, portable/traveling
smartphone, portable/traveling
tablet, portable/traveling.

With the help of you guys, I already installed a shared library, first home network only. Watch-status, pause-status, everything worked perfectly and quick.
in order to use my XBMC library from everywhere (like friends and family, streaming) I changed in my library the file path’s from the sources from SMB to FTP. This is working, just fine BUT.. (and I guess some of you see it already coming) since I changed this, the systems within my home network I experienced buffering, waiting etc. it is just not working flawless..

So I came up with the following idea:
I create 2 almost Identical libraries in MySQL, with only one different setting: The File path.
for my main desktop and notebook1, I will only use the database with the SMB file paths, for my portable devices I will use the library with the FTP-file paths.
and, to finish this off, I will synchronize the watch-status, between both libraries.
So, my question is, before a demolish my already working XBMC configuration, is this plan going to work?
Is it possible to create 2 different libraries (I thought maybe by setting up two profiles, one for portable with FTP file paths, and one for ‘local’ using SMB file path)?
Is it possible to add within my existing SQL a new database for the SMB library? If this is possible, how can I be sure that profile: local is using the database with the smb paths, and de portable profile is using the database with the FTP paths?
And is it possible to share the watch-status between to libraries? This last one isn’t that important, but, this whole ‘problem’ is just a matter of fine tuning, so it’s better to be perfect.

So, I hope somebody could advice my on this matter, there is no deadline, so I do have the time !!

Thanks for taking the time to help me ;-)
find quote
Post Reply