[MYSQL] HOW-TO: 5 User XBMC

  Thread Rating:
  • 4 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
maikito26 Offline
Senior Member
Posts: 101
Joined: Jun 2014
Reputation: 3
Post: #31
This looks great. How easy would it be to setup the following?

- My DB
- Wife's DB
- Joint DB

And then if we watch something together (Joint) it updates to both of our DB's?

I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?
find quote
schumi2004 Offline
Posting Freak
Posts: 1,728
Joined: Aug 2011
Reputation: 21
Post: #32
(2014-06-08 15:15)maikito26 Wrote:  This looks great. How easy would it be to setup the following?

- My DB
- Wife's DB
- Joint DB

And then if we watch something together (Joint) it updates to both of our DB's?

I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?

Easy. You have 5 profile options , just rename them.
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #33
(2014-06-08 15:15)maikito26 Wrote:  This looks great. How easy would it be to setup the following?

- My DB
- Wife's DB
- Joint DB

And then if we watch something together (Joint) it updates to both of our DB's?

I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?

Thats a very interesting idea that could help a lot of people out. I can imagine various scenarios where such a setup would be handy.

I will look into it and see how difficult it would be, but i think it can be done.

For now though you could try just the 2 separate ones. Just leave the last 3 users blank in the client setup and it will ignore them.

(2014-06-08 15:23)schumi2004 Wrote:  Easy. You have 5 profile options , just rename them.

He's asking if there could be a 3rd database that records watched status to both of there accounts. So when they watch something together both of their accounts have a watched flag.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
(This post was last modified: 2014-06-08 15:28 by N4TH4N.)
find quote
schumi2004 Offline
Posting Freak
Posts: 1,728
Joined: Aug 2011
Reputation: 21
Post: #34
(2014-06-08 15:26)N4TH4N Wrote:  
(2014-06-08 15:15)maikito26 Wrote:  This looks great. How easy would it be to setup the following?

- My DB
- Wife's DB
- Joint DB

And then if we watch something together (Joint) it updates to both of our DB's?

I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?

Thats a very interesting idea that could help a lot of people out. I can imagine various scenarios where such a setup would be handy.

I will look into it and see how difficult it would be, but i think it can be done.

For now though you could try just the 2 separate ones. Just leave the last 3 users blank in the client setup and it will ignore them.

(2014-06-08 15:23)schumi2004 Wrote:  Easy. You have 5 profile options , just rename them.

He's asking if there could be a 3rd database that records watched status to both of there accounts. So when they watch something together both of their accounts have a watched flag.

Ah now i got it.
Should be awesome if that works.
find quote
Ballistic Offline
Senior Member
Posts: 194
Joined: Nov 2009
Reputation: 4
Location: The low countries
Post: #35
I bud!

Thanks for your awesome work.

Could you re-post the SQL query without the music database functions?

The latest version doesn't work for me as i don't have any music in my database.
SQL query:

RENAME TABLE `a46`.`song` TO `a46`.`globalsong` ;
MySQL said: Documentation
#1017 - Can't find file: './a46/song.frm' (errno: 2)

edit: Or maybe its something else. db B to D did work. It stopped on db E
(This post was last modified: 2014-06-08 22:21 by Ballistic.)
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #36
(2014-06-08 22:19)Ballistic Wrote:  I bud!

Thanks for your awesome work.

Could you re-post the SQL query without the music database functions?

The latest version doesn't work for me as i don't have any music in my database.
SQL query:

RENAME TABLE `a46`.`song` TO `a46`.`globalsong` ;
MySQL said: Documentation
#1017 - Can't find file: './a46/song.frm' (errno: 2)

edit: Or maybe its something else. db B to D did work. It stopped on db E

You have 2 options.
Get XBMC to create a database for music called a46 by adding it to the advancedsettings.xml file or simply delete everything in the SQL script below and including RENAME TABLE `a46`.`song` TO `a46`.`globalsong` ;

The top half of the script does videos the bottom half does music. So its safe to delete everything below that line if you don't want music.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #37
BUGFIX: Windows scripts have been updated to version 1.2.1 as there was an error that affected Windows account names with spaces. This has now been fixed.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
find quote
schumi2004 Offline
Posting Freak
Posts: 1,728
Joined: Aug 2011
Reputation: 21
Post: #38
@N4TH4N
Although I don't have much knowledge about MySQL I was thinking on how to achieve @maikito26's request.
I could only figure out 1 option and that is dedicated databases for this. Db1 is his, db2 her and db3 is shared and uses triggers to update DB 1 and 2 when 3 is active or doesn't that work?
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #39
I was thinking running a 6th database with joint capabilities with user 1 and 2.

I was considering a trigger aswel.

It could go 2 ways though.

Database 6 could update playcount1 and playcount2 via a trigger when database 6 watches something.

But what about if user 2 watches something by themselves. Should it not also be displayed with a tick on database 6 so that you know at least one of you have watched it.

This could be handy for multiple users trying to pick a movie together.

I.E me and my housemates could have all 5 databases linked to database 6.

When we go to watch a movie we can hide watched and it will hide everything each of us have seen. So there wont be anymore asking has anyone seen this.

It could be setup in 2 different ways. The couple script that uses database 1 and 2 and the family script that uses database 1, 2, 3, 4 and 5

Im still considering on what the most efficient way to do that is. But when i do implement this feature it will be like that.

Actually come to think about it, it could be done with 2 triggers, one for each way.

Also i would make database 6 the couples database and database 7 the family database.

When installing the clients you can choose to install both, one or neither just by leaving them un-named.


Im starting to think that my simple script could change the way a lot of people use XBMC.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
(This post was last modified: 2014-06-09 00:35 by N4TH4N.)
find quote
nickr Offline
Team-Kodi Member
Posts: 18,593
Joined: May 2009
Reputation: 381
Location: Christchurch NZ
Post: #40
(2014-06-08 15:15)maikito26 Wrote:  This looks great. How easy would it be to setup the following?

- My DB
- Wife's DB
- Joint DB

And then if we watch something together (Joint) it updates to both of our DB's?

I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?

Send her out to work?

If I have helped you or increased your knowledge, click the 'thank user' button to give thanks :) (People with less than 20 posts won't see the "Thank you" button.)
find quote
maikito26 Offline
Senior Member
Posts: 101
Joined: Jun 2014
Reputation: 3
Post: #41
(2014-06-09 00:24)N4TH4N Wrote:  I was thinking running a 6th database with joint capabilities with user 1 and 2.

But what about if user 2 watches something by themselves. Should it not also be displayed with a tick on database 6 so that you know at least one of you have watched it.

I like how you're thinking...I could definitely see benefits of this functionality if it existed. But in this couples case, I'd want a record of what we've watched together so that we know what is okay and not okay to discuss... ie spoilers. Sometimes we re-watch episodes together if one of us had seen them already. That's just the habit we're in.

(2014-06-09 00:24)N4TH4N Wrote:  Actually come to think about it, it could be done with 2 triggers, one for each way.

Also i would make database 6 the couples database and database 7 the family database.

When installing the clients you can choose to install both, one or neither just by leaving them un-named.

If you could do this, i'd be indebted to you. Having both triggers would not only solve our family's problem but also give us the best of both worlds. We can track what we've watched individually, what we've watched together, and choose from completely unwatched material easily

(2014-06-09 00:24)N4TH4N Wrote:  Im starting to think that my simple script could change the way a lot of people use XBMC.

Count me as one of them. But now that I'm thinking about it, you're right. And since I've been thinking about it, the next evolution of this would be able to get XBMC or the skins to have access to all of these DB's so you could filter/place marked multiple ticks based on it - all from some master profile. Now we're just dreaming!

(2014-06-09 00:48)nickr Wrote:  
(2014-06-08 15:15)maikito26 Wrote:  I would love to be able to keep track of what I watch individually from my wife since she is home all day using XBMC and this messes up Lazy TV and some Smart Playlists for myself?

Send her out to work?

Believe me amigo, she's at work while at home. But she has some superhuman power to be able to chase toddlers and huskies around while still paying attention to what's on the tube.
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #42
After thinking about this i am keen on making it happen, i have a pretty high workload at the moment, but ill find some spare time at some point over the coming weeks. So i wont make you wait too long.

So for your one are you just interested in the one way trigger to update your individual database when you watch things together.

I'll probably keep the main script as a 2 way, but i will make a 1 way fork for you. Which will most likely consist of the same code as the 2 way just less of it.

It would be great if there was an add-on that you could select which people are in the room and it auto watches on their profile. Or even better, face recognition. But now i'm truly dreaming.

I was just thinking for your setup it should probably display ticked if you have watched it together, or both watched it apart and not tick if one person has not watched it.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
(This post was last modified: 2014-06-09 02:19 by N4TH4N.)
find quote
N4TH4N Offline
Member
Posts: 80
Joined: Jul 2010
Reputation: 6
Location: Australia
Post: #43
Another thought, ill probably create separate ADDONS for the main script. One for Couples (A Type), One for Couple (B Type) and one for Family, but maybe a one way script for family because if you watch something together but one person is not present you want want it to tick on their profile. So i think by limiting it to just display a combination of what everyone has watched in order to help pick movies rather then keep track of them. This would mean that you would have to manually mark as watched on your individual database for family mode.

Because without individual person selection it would be problematic, thats just not currently possible with any method i can think of.

Couples modes should work ok though.

Check Out My 5 User XBMC Tutorial
If i have helped you please REP+
(This post was last modified: 2014-06-09 03:02 by N4TH4N.)
find quote
schumi2004 Offline
Posting Freak
Posts: 1,728
Joined: Aug 2011
Reputation: 21
Post: #44
(2014-06-09 00:24)N4TH4N Wrote:  I was thinking running a 6th database with joint capabilities with user 1 and 2.

I was considering a trigger aswel.

It could go 2 ways though.

Database 6 could update playcount1 and playcount2 via a trigger when database 6 watches something.

But what about if user 2 watches something by themselves. Should it not also be displayed with a tick on database 6 so that you know at least one of you have watched it.

This could be handy for multiple users trying to pick a movie together.

I.E me and my housemates could have all 5 databases linked to database 6.

When we go to watch a movie we can hide watched and it will hide everything each of us have seen. So there wont be anymore asking has anyone seen this.

It could be setup in 2 different ways. The couple script that uses database 1 and 2 and the family script that uses database 1, 2, 3, 4 and 5

Im still considering on what the most efficient way to do that is. But when i do implement this feature it will be like that.

Actually come to think about it, it could be done with 2 triggers, one for each way.

Also i would make database 6 the couples database and database 7 the family database.

When installing the clients you can choose to install both, one or neither just by leaving them un-named.
I would prefer the one way direction.

Quote:Im starting to think that my simple script could change the way a lot of people use XBMC.
I have seen request like this for separate databases before, for a long time also.
But for some reason never implemented in XBMC.

Currently I prefer your method since it works 100% were the other mod I tried gave me some issues on watched indicators for some reason.
find quote
Steini Offline
Senior Member
Posts: 138
Joined: Mar 2010
Reputation: 0
Post: #45
(2014-06-07 21:02)N4TH4N Wrote:  
(2014-06-07 20:52)Steini Wrote:  Since he only has a openelec box I was hoping to skip the mysql server there.

What I will try is to use pathsubstitution since that is only a few lines of xml which I know works.

Then I will try to open the mysql server over the internet (with some security tweaks like only listen to my parents IP addres

The Webdav works fine, but we are both on 50mb/s links which is more than enough

It will be interesting to see how the MySQL database holds up over the internet rather then local.

Also pathsubs are probably the easiest solution if using the single MySQL database.

I went with openvpn for the mysql connection and the performance is really good. I made a really limited vpn that only has access to the mysql server and initiate the connection on the openelec box using autostart.sh - I have tried restarting the router, pulling the ethernet and everything reconnects perfectly when the network comes back up - no restarts necessary.

Since Samba over vpn is too slow I use pathsubstitution and serve the actual content over webdav. Here is the relevant part of the advancedsettings.xml - My setup decided to include the username and password for music but not for movies so I had to substitute both:
Code:
<pathsubstitution>
          <substitute>
                <from>smb://USERNAME:[email protected]/</from>
                <to>HTTPS://USERNAME:[email protected]/webdavxbmc/</to>
          </substitute>
         <substitute>
            <from>smb://IPADDRESS/</from>
                <to>HTTPS://USERNAME:[email protected]/webdavxbmc/</to>
                </substitute>
    </pathsubstitution>

The next step is to set some rules so that my parents cannot change the master database.

I guess the easiest would be to limit my parents mysql user to read only for most tables / views - only give it write access to watched flags

Thanks again for your great tutorial !

Ps one afterthought - how future-proof are we when Helix will come out?
(This post was last modified: 2014-06-09 17:40 by Steini.)
find quote
Post Reply