[sloth85]

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.
  

 

1. 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
   
2. Updating the base tables
   This will update the tables and views for more users
   Kodi V116 Update Base Tables.sql
   
3. 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
   
4. 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 */;

5. 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;

6. 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 ;

The amount of times i have referred back to this post is crazy. I should have broken my installs so much. 

Thanks as always Mong