Login at Kodi Home

**black_eagle** · 2018-08-31, 21:34

(2018-08-09, 12:33)calibos Wrote: Have the Devs still not made database sharing other than via UPNP intregrated into the latest version of Kodi? We still need to do this complicated MySQL thing?? Must never be happening at this stage. Must be too hard to do or require a full rewrite of the Library code and they believe that UPNP is good enough for most who probably just have a PC Kodi install and a Kodi box under their TV. Sucks for us with 6 Kodi machines in our house! ;-)

I have 5 kodi instances in my house. MySql is pretty much essential for the way I have my media shared ! Kids stuff is shared over smb and my own stuff is shared over nfs. If I were to use uPnp, my own movie and music libraries would be shared to my kids devices (which is not what I want at all). The way I have things set up, my kids can only access their own media, whereas I can access it all. Implementing uPnP would negate this. The only other way would be to use Kodi's own sqlite db's and forget about watched status etc. That kind of negates the reason for using a shared db. The way I have it at the minute, I can see what I have watched, what my kids have watched, stop and resume my stuff in several rooms and keep it all separate from my kids stuff whilst still keeping an eye on what they have access to.

Personally, I think MySql (or derivatives) are much more flexible than uPnP and I would be more than gutted if Kodi ever dropped support for MySql.

Gugelhupf03 · 2018-11-17, 15:52

After running into several of the known issues with Kodi 17 (esp. with Sony ATV2), I gave the lates V18-Beta5 a try on all my clients - with success. Leia fixes a lot of issues.

Has anyone migrated this Multi-User-SQL-thingy to V18 already AND is willing to write a Fool-Proof-Guide?

I have no knowledge whatsoever about SQL and only the purest-basic-idea about Linux.

I am running a central SQL-Kodi-Database on one of my Synology-NAS (old DS-413j-low-power-energy-saving model).
That's slow, but stable, and I REALLY REALLY would give my twen-daughter her own watched-status-capability without her needing to maintain the library on her Kodi-Clients herself.

nickty · 2018-11-23, 13:57

I'm doing this for the second time Wink

new setup

thanks for this.

Michal_K · 2019-02-06, 20:59

Hi, is possible to run this on Kodi 18 - Leia??? I tried it but does not work. Movie and TV Shows are empty.
Thank you.

BigMong · 2019-02-06, 23:05

(2019-02-06, 20:59)Michal_K Wrote: Hi, is possible to run this on Kodi 18 - Leia I tried it but does not work. Movie and TV Shows are empty.
Thank you.

Already have done it, i'm just testing it.
everything is looking good so far will update soon

BigMong

Update: Database Version 119 [aka Matrix]

Database Version 116 [aka Leia]
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)
```
sql:
CREATE USER 'KODI_18'@'%' IDENTIFIED BY 'password';
```
Change password to what you want to use.
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

xml:
<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>

I also set within Libre-ELEC to wait for network on boot

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 ;

Michal_K · 2019-02-07, 10:29

Thank you very much.

birt · 2019-02-13, 12:35

(2019-02-07, 07:01)BigMong Wrote:
Code:
Todo:
- Play with the idea to sync the Thumbnails from the main device to the rest

First of all, thank you for porting this for 116.

Regarding thumbnails, I used a shared folder with Xbmc 11 if I remember correctly and loading the thumbnails from the network over smb was rather laggy even though the setup was not bad performance-wise. I guess it has to do with the way they are stored - if you have a lot of content the folders are going to be very large.

If you want to sync them to each device, that might work fine but it's quite a lot of work and it probably requires writing a specialized Kodi plugin.

Supay · 2019-07-03, 00:33

Good evening. I think I lack some fundamental understanding about how this works as I can't get it to work as expected.

1) I have gone through the steps and I have my primary user working perfectly, but none of the other users pick up the library data. Is there a common mistake I should be aware of please? I have gone through the thread to try and work it out but I'm not sure what I am doing wrong.

2) I have a new Pi4. I have upgraded my current Kodi version on my Pi3+ from 18.2 to 18.3 and it completed successfully, and my library is all working as expected on my primary user. I then setup my new Pi4 and copied my advancedsettings.xml over, however when I add the sources I don't get any library data pulled through for them. Is there some option or step I am missing here as well please?

Any advice would be very appreciated.

BigMong · 2019-07-03, 12:29

(2019-07-03, 00:33)Supay Wrote: Good evening. I think I lack some fundamental understanding about how this works as I can't get it to work as expected.

1) I have gone through the steps and I have my primary user working perfectly, but none of the other users pick up the library data. Is there a common mistake I should be aware of please? I have gone through the thread to try and work it out but I'm not sure what I am doing wrong.

2) I have a new Pi4. I have upgraded my current Kodi version on my Pi3+ from 18.2 to 18.3 and it completed successfully, and my library is all working as expected on my primary user. I then setup my new Pi4 and copied my advancedsettings.xml over, however when I add the sources I don't get any library data pulled through for them. Is there some option or step I am missing here as well please?

Any advice would be very appreciated.

Good Evening,

Have you set the Pi's to wait for network on boot? other wise it boots up too fast without a SQL database meaning no library data.

Supay · 2019-07-04, 22:32

Quote:Good Evening,

Have you set the Pi's to wait for network on boot? other wise it boots up too fast without a SQL database meaning no library data.

Oh wow, haha. Thank you. I've always set that for that reason but for some bizarre reason my brain decided that I liked the idea of a fast startup and turned off the delay. Thank you, immediately worked on reboot. I blame my aging mind being destroyed by two young children!

BigMong · 2019-07-05, 00:14

(2019-07-04, 22:32)Supay Wrote:
Quote:Good Evening,

Have you set the Pi's to wait for network on boot? other wise it boots up too fast without a SQL database meaning no library data.

Oh wow, haha. Thank you. I've always set that for that reason but for some bizarre reason my brain decided that I liked the idea of a fast startup and turned off the delay. Thank you, immediately worked on reboot. I blame my aging mind being destroyed by two young children!

Hahaha, children will do that to us all Smile

Glad that was the problem

OtheA · (This post was last modified: 2019-08-19, 03:27 by OtheA.)

Hello. I'm starting a clean install and I think I'm messing up step 3. It's making the sub... databases? But SQL is responding with the below. Is this normal and should be ignored? Thanks.

Quote:
Code:
Error SQL query: -- FILES VIEW FOR _U04 -- CREATE ALGORITHM=MERGE DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `files` AS SELECT `Kodi_U01_Video_116`.`global_files`.`idFile` AS `idFile`, `Kodi_U01_Video_116`.`global_files`.`idPath` AS `idPath`, `Kodi_U01_Video_116`.`global_files`.`strFilename` AS `strFilename`, `Kodi_U01_Video_116`.`global_files`.`playCount_U04` AS `playCount`, `Kodi_U01_Video_116`.`global_files`.`lastPlayed_U04` AS `lastPlayed`, `Kodi_U01_Video_116`.`global_files`.`dateAdded` AS `dateAdded` FROM `Kodi_U01_Video_116`.`global_files` MySQL said: #1050 - Table 'files' already exists

BigMong · 2019-08-19, 09:39

(2019-08-19, 03:26)OtheA Wrote: Hello. I'm starting a clean install and I think I'm messing up step 3. It's making the sub... databases? But SQL is responding with the below. Is this normal and should be ignored? Thanks.

Quote:
Code:
Error SQL query: -- FILES VIEW FOR _U04 -- CREATE ALGORITHM=MERGE DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `files` AS SELECT `Kodi_U01_Video_116`.`global_files`.`idFile` AS `idFile`, `Kodi_U01_Video_116`.`global_files`.`idPath` AS `idPath`, `Kodi_U01_Video_116`.`global_files`.`strFilename` AS `strFilename`, `Kodi_U01_Video_116`.`global_files`.`playCount_U04` AS `playCount`, `Kodi_U01_Video_116`.`global_files`.`lastPlayed_U04` AS `lastPlayed`, `Kodi_U01_Video_116`.`global_files`.`dateAdded` AS `dateAdded` FROM `Kodi_U01_Video_116`.`global_files` MySQL said: #1050 - Table 'files' already exists

looks like the view "files" is already created for user 4.
did it work fine for when creating user 2 and 3?
If it did you can just drop Kodi_U04_Video_116 and re-run the code from step 3

OtheA · (This post was last modified: 2019-08-19, 20:23 by OtheA.)

It did it for all users I created _U02 though _U05. Maybe I missed something in a previous step? Is it possible it's because I'm running MariaDB on my QNAP NAS rather than MySQL?

It's a fresh install, so I think I'm going to blow all the databases away and start over to make sure I didn't screw something up.