(2021-04-13, 21:15)BigMong Wrote: 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.

(2022-07-17, 07:51)valerinism Wrote: 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.

(2023-01-16, 15:35)BigMong Wrote: 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.

• 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.
  

• 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.
  

(2023-01-23, 10:12)*MarioP* Wrote: .

(2023-01-24, 00:30)bart330 Wrote:

(2023-01-23, 10:12)*MarioP* Wrote: .

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.

(2023-01-16, 15:35)BigMong Wrote: 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.

(2023-01-24, 22:55)chimpsinties Wrote:

(2023-01-24, 00:30)bart330 Wrote:

(2023-01-23, 10:12)*MarioP* Wrote: .

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.