[MYSQL] HOW-TO: 5 User XBMC

  Thread Rating:
  • 3 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Post Reply
Enigma256 Offline
Junior Member
Posts: 7
Joined: Aug 2016
Reputation: 0
Post: #241
I guess my MySQL server is not quite as powerful as yours (midrange Synology NAS) Wink
find quote
nulled Offline
Junior Member
Posts: 4
Joined: Oct 2016
Reputation: 0
Post: #242
Is it possible to create a view for 'tvshows' whereby if I remove it from User A.tvshow, then it won't remove it from User B.tvshow? Basically User B would have say 20 shows and only 5 of those would be shown for User A.

Could you create a column in global_tvshows where you can populate if it is "enabled" for User A or User B and then the tvshow view for that User then determines if it shows or not?
find quote
BigMong Offline
Junior Member
Posts: 26
Joined: Jan 2015
Reputation: 1
Location: Australia
Post: #243
(2017-03-21 10:39)nulled Wrote:  Is it possible to create a view for 'tvshows' whereby if I remove it from User A.tvshow, then it won't remove it from User B.tvshow? Basically User B would have say 20 shows and only 5 of those would be shown for User A.

Could you create a column in global_tvshows where you can populate if it is "enabled" for User A or User B and then the tvshow view for that User then determines if it shows or not?

Not that I know of as this is only tweaking the DB to allow more then one user to view all the same data.

You can tho create smart playlists to do this;
EG:
Store shows something like
- TV Shows
- UserA
- Show 1
- Show 3
- UserB
- Show 4
- Show 5
- AllUser
- Show 2

UserA:
In the smart playlist use the rule path "start with" (or something like that)
smb:\\loot\TV Shows\UserA
smb:\\loot\TV Shows\AllUser

Still have the shows added into Kodi at the TV Shows level

Then change your TV Shows link in Kodi to point to your new playlist (Not all skins support this)

Or if it about having too many shows in your TV List use the in-progress list (Which is what I do as my TV list has over 650 items in it)

Hope that makes some sence
find quote
nulled Offline
Junior Member
Posts: 4
Joined: Oct 2016
Reputation: 0
Post: #244
So I didn't know a view runs the SQL that created it every time it's accessed (but it makes sense). What I ended up doing was modifying `global_tvshows` with another column called `viewers` that I populate with the values "UserA", "UserB" or "Both".

I dropped the `tvshows` for the particular user's database and recreated it with the SQL

PHP Code:
CREATE VIEW  `tvshow` AS SELECT 
FROM  `a_master_107`.`global_tvshow
WHERE  `viewers` =  "UserA" OR `viewers` =  "Both" 

User A will now only see shows where it's listed in the `global_tvshows`.`viewers` as "UserA" or "Both"
(This post was last modified: 2017-03-21 15:37 by nulled.)
find quote
BigMong Offline
Junior Member
Posts: 26
Joined: Jan 2015
Reputation: 1
Location: Australia
Post: #245
(2017-03-21 15:36)nulled Wrote:  So I didn't know a view runs the SQL that created it every time it's accessed (but it makes sense). What I ended up doing was modifying `global_tvshows` with another column called `viewers` that I populate with the values "UserA", "UserB" or "Both".

I dropped the `tvshows` for the particular user's database and recreated it with the SQL

PHP Code:
CREATE VIEW  `tvshow` AS SELECT 
FROM  `a_master_107`.`global_tvshow
WHERE  `viewers` =  "UserA" OR `viewers` =  "Both" 

User A will now only see shows where it's listed in the `global_tvshows`.`viewers` as "UserA" or "Both"

Oh yeah that would work, Two things though the "userrating" wont map in that setup unless you are not using "userrating_U01" etc and all the manual work needed to apply the "viewers" field as "tvshow" view is everything a list of all eps
if you don't mind editing the DB all the time Smile

What about editing the "tvshow" view for said user to something like this this;
I have done very little testing
Just means you will need to store the TV Shows in sub folders, Kodi wont care about that as it will merge them all
- UserA
- Both
etc.

'^smb://192.168.0.105/TV_Shows/UserA/|^smb://192.168.0.105/TV_Shows/Both/'
^ means start of line
| mean or
should be easy to get it to work with your setup
PHP Code:
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U04_Video_107`.`tvshow` AS SELECT
      
`Kodi_U01_Video_107`.`global_tvshow`.`idShow` AS `idShow`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c00` AS `c00`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c01` AS `c01`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c02` AS `c02`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c03` AS `c03`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c04` AS `c04`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c05` AS `c05`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c06` AS `c06`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c07` AS `c07`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c08` AS `c08`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c09` AS `c09`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c10` AS `c10`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c11` AS `c11`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c12` AS `c12`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c13` AS `c13`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c14` AS `c14`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c15` AS `c15`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c16` AS `c16`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c17` AS `c17`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c18` AS `c18`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c19` AS `c19`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c20` AS `c20`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c21` AS `c21`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c22` AS `c22`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c23` AS `c23`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`userrating_U04` AS `userrating`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`duration` AS `duration`
    
FROM `Kodi_U01_Video_107`.`global_tvshow`
    
JOIN `Kodi_U04_Video_107`.`tvshowlinkpathON `Kodi_U01_Video_107`.`global_tvshow`.`idShow` = `tvshowlinkpath`.`idShow`
    
JOIN `Kodi_U04_Video_107`.`pathON `tvshowlinkpath`.`idPath` = `path`.`idPath`
    
WHERE `Kodi_U04_Video_107`.`path`.`strPathREGEXP '^smb://192.168.0.105/TV_Shows/UserA/|^smb://192.168.0.105/TV_Shows/Both/'
If it failes can alway put back the default one
PHP Code:
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U04_Video_107`.`tvshow` AS SELECT
      
`Kodi_U01_Video_107`.`global_tvshow`.`idShow` AS `idShow`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c00` AS `c00`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c01` AS `c01`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c02` AS `c02`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c03` AS `c03`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c04` AS `c04`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c05` AS `c05`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c06` AS `c06`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c07` AS `c07`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c08` AS `c08`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c09` AS `c09`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c10` AS `c10`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c11` AS `c11`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c12` AS `c12`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c13` AS `c13`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c14` AS `c14`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c15` AS `c15`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c16` AS `c16`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c17` AS `c17`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c18` AS `c18`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c19` AS `c19`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c20` AS `c20`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c21` AS `c21`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c22` AS `c22`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`c23` AS `c23`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`userrating_U04` AS `userrating`,
      `
Kodi_U01_Video_107`.`global_tvshow`.`duration` AS `duration`
    
FROM `Kodi_U01_Video_107`.`global_tvshow`; 
find quote
nulled Offline
Junior Member
Posts: 4
Joined: Oct 2016
Reputation: 0
Post: #246
It's all good, I don't mind editing the fields the first time. It's a do once thing and if a new show comes up I can easily go through and tag it appropriately. 'global_tvshows' doesn't show every episode for me? It's just the show? 'episodes' has every single ep listed? Anyway it works for me at the moment (and I don't need the user rating) Smile

I wouldn't be able to put the shows in sub folders as my torrent client wouldn't know where to move the shows to haha.
(This post was last modified: 2017-03-23 14:56 by nulled.)
find quote
BigMong Offline
Junior Member
Posts: 26
Joined: Jan 2015
Reputation: 1
Location: Australia
Post: #247
(2017-03-23 14:54)nulled Wrote:  It's all good, I don't mind editing the fields the first time. It's a do once thing and if a new show comes up I can easily go through and tag it appropriately. 'global_tvshows' doesn't show every episode for me? It's just the show? 'episodes' has every single ep listed? Anyway it works for me at the moment (and I don't need the user rating) Smile

I wouldn't be able to put the shows in sub folders as my torrent client wouldn't know where to move the shows to haha.

haha all good I might be thinking the wrong table, not at home atm to check.
but if it works for you then go for it, at the end of the day it's your setup Smile
find quote
Post Reply