(2017-02-03, 09:48)BigMong Wrote: Update: Database Version 116 [aka Leia]this helped so much. thank you!!
) sql:CREATE ALGORITHM=MERGE DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `episode` AS
SELECT x.*
FROM `xbmcvid116`.`episode` x
LEFT JOIN `xbmcvid116`.`tag_link` tl
ON ( x.idShow = tl.media_id AND tl.media_type = "tvshow" )
WHERE tl.tag_id=2 ;
:'ALTER TABLE Kodi_U01_Video_116.tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL'
:#1347 - 'Kodi_U01_Video_116.tvshow' is not of type 'BASE TABLE'
(2020-01-25, 15:52)LikeTearsInTheRain Wrote: Apologies, ignore me, I didn't understand what a 'view' was, I ran 'ALTER TABLE Kodi_U01_Video_116.global_tvshow MODIFY COLUMN c06 mediumtext DEFAULT NULL NULL' and everything seems to be working fine
(2020-05-22, 13:21)LikeTearsInTheRain Wrote: I wanted to ask, I noticed 18.7 stable has been released, is it safe to update to this client? I've disabled auto updating incase any updates might corrupt my database.
(2019-02-07, 07:01)BigMong Wrote: Database Version 116 [aka Leia]The amount of times i have referred back to this post is crazy. I should have broken my installs so much.
This is based on the work started by N4TH4N
Updates:
- Fixed some typos in the SQL
- New Trigger for Ratings
Todo:
- Play with the idea to sync the Thumbnails from the main device to the rest
What does this do?
This will allow you to use the main user "Kodi_U01_Video_116" to add shows and movies to your collection and let the other users see this data as its added. (only need to sync once for everyone to get the new items)
Also this keeps the play counts, last played, and ratings separate for each user
Some things to start off with.
- The MySQL user i'm using for Leia is "KODI_18" (I change this with each major version) and this user I don't give access to anything (as I do other things with this server)
Change password to what you want to use.sql:CREATE USER 'KODI_18'@'%' IDENTIFIED BY 'password';
- I have 9 users profiles on this set up, you can remove the unneeded, leave them or even add more.
Naming for my Databases:
Kodi_U01_Video_116
Kodi_U02_Video_116
...
Kodi_U09_Video_116
- You should update the advancedsettings.xml file and reboot Kodi after the steps below are done.
My advancedsettings.xml as a reference
I also set within Libre-ELEC to wait for network on bootxml:<advancedsettings>
<samba>
<clienttimeout>30</clienttimeout> <!-- timeout (in seconds) -->
</samba>
<videolibrary>
<recentlyaddeditems>50</recentlyaddeditems> <!-- number of recently added items. Defaults to 25 -->
</videolibrary>
<videodatabase>
<type>mysql</type>
<host>X.X.X.X</host> <!-- Use IP here as its faster then DNS -->
<port>3306</port>
<user>KODI_18</user>
<pass>some_password</pass>
<name>Kodi_U01_Video_</name>
</videodatabase>
</advancedsettings>
- Added some of the sql as files as I was over the 65,500 character limit.
- Create the default tables
*** ONLY DO THIS IF UPGRADING from my last Multi-User. If doing a new setup go to Step 2 ***
Because the tables have been changed from the defaults with views and altered columns the Upgrade ran by Kodi will fail.
This will create all the base tables that Kodi does and Grant All Privileges to this Database.
Kodi V116 Default Tables.sql
- Updating the base tables
This will update the tables and views for more users
Kodi V116 Update Base Tables.sql
- Creating the users
Run this for each user with doing a find and replace for _U02 to _U03 etc, this is set to use the main Database of Kodi_U01_Video_116
Kodi V116 Creating the users.sql
- Updating the delete_file Trigger
This does housekeeping if you use the clean database from Kodi or remove files.
sql:/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
-- TRIGGER FIX FOR MULT USERS --
DROP TRIGGER IF EXISTS `Kodi_U01_Video_116`.`delete_file`;
DELIMITER $$
CREATE DEFINER=`KODI_18`@`%` TRIGGER `Kodi_U01_Video_116`.`delete_file` AFTER DELETE
ON `Kodi_U01_Video_116`.`global_files`
FOR EACH ROW
BEGIN
DELETE FROM `Kodi_U01_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U02_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U03_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U04_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U05_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U06_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U07_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U08_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U09_Video_116`.bookmark WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.settings WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.stacktimes WHERE idfile = old.idfile;
DELETE FROM `Kodi_U01_Video_116`.streamdetails WHERE idfile = old.idfile;
END$$
DELIMITER ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;- Copying over the old watched status (If Step 1 was used or the Database naming format has changed)
Would recommend using a service like https://trakt.tv/ for keeping this information in sync
Update the _U01 with the user you wish to copy over.
This may timeout so you can run more then once and it wont update the same information more then once.
sql:SET SQL_SAFE_UPDATES = 0;
UPDATE Kodi_U01_Video_116.files AS NEWTBL
INNER JOIN Kodi_U01_Video_107.files AS OLDTBL ON NEWTBL.strFilename = OLDTBL.strFilename
SET
NEWTBL.lastPlayed = OLDTBL.lastPlayed,
NEWTBL.playCount = OLDTBL.playCount
WHERE
NEWTBL.strFilename = OLDTBL.strFilename AND OLDTBL.lastPlayed IS NOT NULL AND NEWTBL.playCount IS NULL;
SET SQL_SAFE_UPDATES = 1;- Show/Season average rating
I'm the only one that likes to rate things in my setup so this is build for just me.
Can add more user by duplicating the If Statement and updating the _U01 keep the updates point to the main U01 table tho.
** I have always felt rating a Show/Season is always wrong as it should be an average of the episodes, that is what is done here **
sql:DROP TRIGGER IF EXISTS `Kodi_U01_Video_116`.`update_rating`;
DELIMITER $$
CREATE DEFINER=`KODI_18`@`%` TRIGGER `Kodi_U01_Video_116`.`update_rating` AFTER UPDATE
ON `Kodi_U01_Video_116`.`global_episode`
FOR EACH ROW
BEGIN
IF (new.userrating_U01 <> old.userrating_U01) THEN
UPDATE `Kodi_U01_Video_116`.global_tvshow SET userrating_U01 = (SELECT ROUND(AVG(userrating_U01)) FROM global_episode WHERE userrating_U01 IS NOT NULL AND idShow=old.idShow) WHERE idShow=old.idShow;
UPDATE `Kodi_U01_Video_116`.global_seasons SET userrating_U01 = (SELECT ROUND(AVG(userrating_U01)) FROM global_episode WHERE userrating_U01 IS NOT NULL AND idShow=old.idShow AND idSeason=old.idSeason) WHERE idShow=old.idShow AND idSeason=old.idSeason;
END IF;
END$$
DELIMITER ;
.
(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later
For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.
Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.
(2021-01-28, 00:20)OtheA Wrote:(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later
For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.
Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.
What I do is just use the sources.xml in the profile to give each one of my users access to what they need. I have a very granular directory structure though, so that's easiest for me.
tag .xml:<name>Kodi_U03_Video_</name>
(2021-01-29, 15:55)justenoughlinux Wrote:I can't recall. I set it up a while ago. That's just how I limited my kids from watching rated "R" movies, etc.(2021-01-28, 00:20)OtheA Wrote:(2021-01-27, 23:15)justenoughlinux Wrote: This post (and the previous posts) are a life saver!
Poking through a bunch of pages and replies I'm still not totally sure/or clear if its possible to accomplish this but I thought I would post first / ask for forgiveness later
For my home use this is absolutely perfect since all adults, everyone wants all the content stored locally, but my parents house where it gets a bit tricky. I love the scrape once & set artwork once & everyone is updated and each user has their own watch status. But what if certain users only want to see specific content? I have multiple shares that break my media up (movies, tv, anime-movies, anime-tv, kids, doc-movies, doc-tv...) Now my dad is very picky about what he wants to watch so he only wants (movies, tv), my mother loves my documentary collection so (movies, tv, doc-movies, doc-tv), and for my nieces and nephews who come over (kids, movies, tv, anime-movies, anime-tv) and for this I'm thinking a simple smart playlist that restricts content based on rating will get that job done for the kids profile.
Right now my thought is at least 2 MySQL docker containers and I will have to scrape the content twice. Which isn't a huge killer except for the fact that I go through and set a TON of custom artwork and fan art which can get very tedious. Any insight would be hugely appreciated.
What I do is just use the sources.xml in the profile to give each one of my users access to what they need. I have a very granular directory structure though, so that's easiest for me.
Hmmm did some testing and that doesn't work.
If you setup a multi-user database following this guide, every user "shares" the view created by your master profile.
1. create user & login to generate directory structure
2. push advancedsettings.xml that has your MySQL credentials as well as thetag .xml:<name>Kodi_U03_Video_</name>
3. login as user shows the identical content that I have created from the master profile just with separate watched statuses.
4. push the "sources.xml" file to the users directory, log out/in and I actually get new behaviour here. An empty structure that requires me to scrape all the media again.
Was this what you were getting at?
xml:
<advancedsettings>
<samba>
<clienttimeout>30</clienttimeout>
</samba>
<videolibrary>
<recentlyaddeditems>50</recentlyaddeditems>
</videolibrary>
<videodatabase>
<type>mysql</type>
<host>QNAP-2Bay</host>
<port>3306</port>
<user>KODI_18</user>
<pass>XXXXXXXXX</pass>
<name>Kodi_U01_Video_</name>
</videodatabase>
<pathsubstitution>
<substitute>
<from>special://masterprofile/Thumbnails</from>
<to>smb://KODI_18:XXXXXXXXX@QNAP-2Bay/Filme/_Thumbnails</to>
</substitute>
</pathsubstitution>
</advancedsettings>
