[chimpsinties]

Database Version 119 [aka Matrix]

This has been tested with MySQL Server Version 8.X I don't know if this will (it should) work with lower versions.

Please Note:
Lower Versions or MariaDB 10 you will need to change the Character Set and Collate.
Within each file do a find and replace in this order;
utf8mb4_0900_ai_ci  ->  utf8_general_ci
utf8mb4  ->  utf8

What does this do?
This will allow you to use the main user "Kodi_U01_Video_119" 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 Matrix is "KODI_19" (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_19'@'%' 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_119
  Kodi_U02_Video_119
  ...
  Kodi_U09_Video_119
  
• 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_19</user>
  
      <pass>some_password</pass>
  
      <name>Kodi_U01_Video_</name> <!-- Change the U01 to the one you want -->
  
    </videodatabase>
  
  </advancedsettings>

  I also set within Libre-ELEC to wait for network on boot
  
• Added the sql files to Github.
  

1. Create the default tables
   If you have already got a Multi-User setup then start here as Kodi will fail to update the database
   This SQL will create all the tables and views that Kodi does.
   It also has a small fix in the "tvshow" table column "C06" as I have found scraping shows with lots of fan art fails on the field size eg. Family Guy
   Kodi V119 - 01 Default Tables.sql
   
2. Updating the base tables
   This will change the tables and make views so you can have more users.
   Kodi V119 - 02 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_119.
   Kodi V119 - 03 Creating the Users.sql
   
4. Finalizing
   This is optional but I do recommend doing the first part of it as it cleans the bookmarks when you remove files.
   Kodi V119 - 04 Finalizing.sql
   
5. Current watched status
   You should be using https://trakt.tv/ but if you have python on your Kodi box you can use the tool from https://github.com/MilhouseVH/texturecache.py
   I used this the other day to move Kodi 14 to Kodi 19 as trakt didn't support that version anymore.
   Backup:
   
   Code:

   ./texturecache.py watched movies backup movies.dat
./texturecache.py watched tvshows backup tvshows.dat
   Restore:
   
   Code:

   ./texturecache.py watched movies restore movies.dat
./texturecache.py watched tvshows restore tvshows.dat
   the .dat files can be open in a text editor if you like to see what it exports.
   

Let me know if you have any problems or questions with this.

Hi BigMong
I followed this pretty much to the letter I'm sure I did. I'm running MariaDB on my NAS so I made those changes to the scripts first. Everything seems to be working for my 2 profiles. Adult for me with 2 sources (mature films & kids movies) and a kids profile with just 1 source (kids movies). Same for TV. Adult and kids tv sources. The only thing I can spot that's not working right is recently watched TV shows on the kids profile. It works fine in the adult profile. Oh and recently watched movies works fine even in the kids profile. It's not a deal breaker but I wonder why just that one thing is not working as it should. 
Many thanks for this great bit of work. I must admit it seemed a bit daunting at first but once I read it couple of times and took my time it all worked out. You must let me buy you a beer or a coffee

[chimpsinties]

@BigMong Sorry to bump this post but I realised I didn't actually tag you in my last post. Was wondering if you have any input on this?

[valerinism]

I am building a new machine. What would be the easiest way of setting this up brand new using 19.4. I'd rather start from anew versus transferring my current setup to the new machine. Windows 10. I don't need a step by step guide, just a quick rundown of catching up to your current post for Matrix without having to upgrade for each version. I have my full library of movies and tv shows scraped with EMM, and do not use trakt for watched status as all viewing is done on the local network and not streamed via the internet.

[chimpsinties]

I am building a new machine. What would be the easiest way of setting this up brand new using 19.4. I'd rather start from anew versus transferring my current setup to the new machine. Windows 10. I don't need a step by step guide, just a quick rundown of catching up to your current post for Matrix without having to upgrade for each version. I have my full library of movies and tv shows scraped with EMM, and do not use trakt for watched status as all viewing is done on the local network and not streamed via the internet.

You just need to follow that post I copied in my reply a couple of posts above this one.

[TakedaT]

I have been using kodi (osmc) for many years now and just recently started playing with using mysql instead of individual databases on each.  Then I happened upon this beauty of work which made it even better with separate watched status for other family members.  Thanks to all who have contributed.  

The one issue so far that has been a bit of a pain, was that other users who share the database could hose the library if their pi was to lose a mount and attempt cleanup (they selected the default 'delete' option when the prompt showed).

Not sure if this has been mentioned in the previous posts, but the way I got around this was to create a read-only mysql user for those users' pis, and grant only SELECT access to all tables in their db, except granting ALL access to both the bookmark and files tables.  Been using this for a few weeks now without issues for the most part.  They can still have their own watched status and get regular updates when i update from my end.  One small issue is if a read-only user attempts to refresh certain items, OSMC will die with a sadface, but thats minimal and I just tell them to stop trying to update.

Has anyone else been plagued with other users hosing their library like this?  If so, just wondering how they approached preventing it.

[chimpsinties]

@BigMong The xbox has autoupdated to v20 RC now. There was nothing we could do about it. They pushed v20 out to the Microsoft store to solve some addon slowness issues and the xbox just auto updates itself. So now I have my Shield running v19.3 and the xbox (which was user 2 on the shared database) running v20. Obviously the xbox no longer works. in fact I needed to uninstall/reinstall it just to get kodi to open. Now all my settings are gone. 
What do you think is the best option here? I have a feeling I'm going to need to get my Shield updated to V20 and re-setup all the shared database using maybe new templates files provided by you at some point?
I know this isn't your fault at all. I'd just be grateful for some guidance. It was all working fine for ages, I'd have stayed on v19.3 for ages if it was left up to me but now my hand has been forced.

[bart330]

v20 is now officially available, when can we expect the new script? It looks like the db level has been raised.

[BigMong]

Sorry @chimpsinties been busy with RL and forgot about this, I only had a look into the Adult/Kids thing that once, I will need to check over the code and do some test

@bart330 I will have a look into any changes that are needed. Hopefully, If have some time this weekend. it looks like it has only been out for less than a week as well.

[bart330]

@BigMong Version 20 was released today.
I didn't expect you to have time so soon. Of course I will test your script when it is ready.

[chimpsinties]

Sorry @chimpsinties been busy with RL and forgot about this, I only had a look into the Adult/Kids thing that once, I will need to check over the code and do some test

@bart330 I will have a look into any changes that are needed. Hopefully, If have some time this weekend. it looks like it has only been out for less than a week as well.

No worries. My NVidia Shield got auto updated the other day and it wouldn't update the shared database. Something about the database user not having write permission the new database it tired to create or something. I don't know. I googled it and searched for ages trying to resolve it but in the end I relented and just uninstalled and reinstalled kodi on the Shield. So I'm currently running on a fresh local database with no shared library not using SQL database on my NAS. 
Would we follow the same scripts for setting up the shared database as we did for v19? Or will it need a different procedure now we're on v20?  I do want to set up my shared library again at some point so I can have it shared on the Shield, XBox and a firestick upstairs. For now I think I'll just run them seperately for a bit so at least people can watch their content. 
I'm really grateful for any help you can provide and we all definitely owe you a coffee/beer

[*MarioP*]

Hi,

the only change I've found between 119 db and 121 db was one column in the streamdetails table was added - strHdrType. So what I did was:

1. Main user:

• In phpMyAdmin I've exported my main user db (from version 119) to an SQL file.
  
• In notepadd++ I've edited the creation of the streamdetails table by adding strHdrType column. So the full create statement looks like that:
  

  sql:
  
  
  CREATE TABLE `streamdetails` (
  
        `idFile` int(11) DEFAULT NULL,
  
        `iStreamType` int(11) DEFAULT NULL,
  
        `strVideoCodec` text DEFAULT NULL,
  
        `fVideoAspect` float DEFAULT NULL,
  
        `iVideoWidth` int(11) DEFAULT NULL,
  
        `iVideoHeight` int(11) DEFAULT NULL,
  
        `strAudioCodec` text DEFAULT NULL,
  
        `iAudioChannels` int(11) DEFAULT NULL,
  
        `strAudioLanguage` text DEFAULT NULL,
  
        `strSubtitleLanguage` text DEFAULT NULL,
  
        `iVideoDuration` int(11) DEFAULT NULL,
  
        `strStereoMode` text DEFAULT NULL,
  
        `strVideoLanguage` text DEFAULT NULL,
  
        `strHdrType` text DEFAULT NULL
  
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  
  

  I did not changed any insert statements.
  
• I've also changed the version number (from 119 to 121) in version table:
  

  sql:
  
  
  INSERT INTO `version` (`idVersion`, `iCompressCount`) VALUES
  
      (121, 0);
  
  

• In phpMyAdmin I've created new db for my main user with _121 suffix (Kodi_Mario_Video_121 in my case) and imported the edited sql file.
  

2. For all other users:

• In phpMyAdmin I've exported user's db (from version 119) to an SQL file
  
• In notepad++ I've replaced the name of main user db to point to db for version 121(in my case from Kodi_Mario_Video_119 to Kodi_Mario_Video_121, there was ~300 of such entries in the file so i just used replace all)
  
• In notepadd++ I've edited the creation of the streamdetails table by adding strHdrType column. So the full create statement looks like that:
  

  sql:
  
  
      CREATE TABLE `streamdetails` (
  
      `idFile` int(11)
  
      ,`iStreamType` int(11)
  
      ,`strVideoCodec` text
  
      ,`fVideoAspect` float
  
      ,`iVideoWidth` int(11)
  
      ,`iVideoHeight` int(11)
  
      ,`strAudioCodec` text
  
      ,`iAudioChannels` int(11)
  
      ,`strAudioLanguage` text
  
      ,`strSubtitleLanguage` text
  
      ,`iVideoDuration` int(11)
  
      ,`strStereoMode` text
  
      ,`strVideoLanguage` text
  
      ,`strHdrType` text
  
      );
  
  

• I've also edited creation of streamdetails view for the user, so it looks like that:
  

  sql:
  
  
  CREATE ALGORITHM=MERGE DEFINER=`kodi`@`%` SQL SECURITY DEFINER VIEW `streamdetails` AS select
  
  `Kodi_Mario_Video_121`.`streamdetails`.`idFile` AS `idFile`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`iStreamType` AS `iStreamType`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strVideoCodec` AS `strVideoCodec`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`fVideoAspect` AS `fVideoAspect`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`iVideoWidth` AS `iVideoWidth`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`iVideoHeight` AS `iVideoHeight`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strAudioCodec` AS `strAudioCodec`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`iAudioChannels` AS `iAudioChannels`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strAudioLanguage` AS `strAudioLanguage`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strSubtitleLanguage` AS `strSubtitleLanguage`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`iVideoDuration` AS `iVideoDuration`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strStereoMode` AS `strStereoMode`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strVideoLanguage` AS `strVideoLanguage`,
  
  `Kodi_Mario_Video_121`.`streamdetails`.`strHdrType` AS `strHdrType`
  
  from `Kodi_Mario_Video_121`.`streamdetails`;
  
  

• In phpMyAdmin I've created new db for a user with _121 suffix and imported its edited sql file.
  
• Repeat for all users.
  

I've tested it only for a few moments over the weekend but so far no issues.

If you want to start your 121 db from scratch and then create your multiuser setup i think you can use existing sql's for the 119 version with small modification of this streamdetail table for the additional users.

[bart330]

.

HI *MarioP*
I started with a new DB and only extended this in the first SQL script adjusted this under the table STREAMDETAILS `strHdrType` text DEFAULT NULL
Small adjustments to the version and it fit.

It seems to work as desired.

[chimpsinties]

.

HI *MarioP*
I started with a new DB and only extended this in the first SQL script adjusted this under the table STREAMDETAILS `strHdrType` text DEFAULT NULL
Small adjustments to the version and it fit.

It seems to work as desired.

I'll be starting from scratch like you. Would you mind going into a bit more detail about you achieved it. I'm not sure I follow what you're saying you did to make the old scripts fit.

[Supay]

Sorry @chimpsinties been busy with RL and forgot about this, I only had a look into the Adult/Kids thing that once, I will need to check over the code and do some test

@bart330 I will have a look into any changes that are needed. Hopefully, If have some time this weekend. it looks like it has only been out for less than a week as well.

I still have my setup working with v119 as standard in here, no changes made.  Yet my adult and children content remains separated for each user, with no other changes made.  I will list my exact setup below, in case it helps anyone trying to do the same, though has been a couple of years since I set it all up, so hopefully I remember it correctly!

Media server
Individual folders shared via NFS export:
tv-kids
tv-adult
tv-tagalog
movies-pg
movies-12
movies-15
movies-18
movies-tagalog

Database
Kubernetes cluster with a mariadb container running with it configured as per this script here, no alterations made other than to add mutiple users as desired.

Kodi
Raspberry Pi4s plus one desktop Kodi install on my Steam Deck.
For the Pi4s, I initially setup one device exactly how I want it.  All user profiles added.  Add-ons added.  Settings tweaks etc.  Then I just clone the SD card using dd and tweak the hostname and device name on each to match the expected as per the static IP assignments on my router.
For each user, the adults get each of the above NFS shares added.  The children (3 of them) get NFS shares added as per their age i.e. the youngest two get tv-kids, movies-pg, and movies-12.  The eldest gets tv-kids, movies-pg, movies-12, movies-15.  My mother-in-law also initially had tv-tagalog and movies-tagalog added just for her though I later just gave all adults that.

For each user, they only ever see the content they have the NFS shares for.  So the adults see everything (though initially two of them did not see the tagalog shares, until they were added later).  The youngest children see just those 3 shares listed above.  And the eldest child sees everything except tv-adult, movies-18, and the tagalog shares.  All are using the same central database as above with me being the one who regularly scans and tidies it, and they all see the changes their end, but only for the specific NFS shares they have had added to their profile.


It has consistently done this for me for a while now, and it seems to be the easiest way to manage my content for the family.  I lock down the child profiles so they cannot edit settings, else they could theoretically just add the NFS shares themselves, but other than the above I don't do anything else.  And it saves messing around with the database setup, and means I can just later add a newly permitted NFS share to a profile and they start seeing the content there (as done for my eldest child).

[*MarioP*]

.

HI *MarioP*
I started with a new DB and only extended this in the first SQL script adjusted this under the table STREAMDETAILS `strHdrType` text DEFAULT NULL
Small adjustments to the version and it fit.

It seems to work as desired.

I'll be starting from scratch like you. Would you mind going into a bit more detail about you achieved it. I'm not sure I follow what you're saying you did to make the old scripts fit.

Hi @chimpsinties,

what I did with my db was a little "conversion" I would say (first I've exported all my data, then changed it a bit and then imported to new DB). But if you'd like to start from scratch, I think it is like @bart330 said only the change in first script for table STREAMDETAILS.