2019-02-07, 07:01
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.
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)
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 ;