•   
  • 1
  • 14
  • 15
  • 16(current)
  • 17
  • 18
  • 22
  •   
[MYSQL] HOW-TO: 5 User XBMC
just wondering how hard this would be to get set up without windows or linux using mysql on a synology (or other) nas?

there's a somewhat outdated tutorial on setting it up here for xbmc on an appleTV with a synology: http://quixventure.com/2011/12/configure...-for-xbmc/

just thinking it would be the perfect set-up if it could be done with nothing but a NAS and a few android tv boxes?

EDIT: read through the thread properly and have answered my own question. Smile
Reply
(2016-08-01, 15:19)ascona Wrote:
(2016-07-28, 11:28)gaxander Wrote:
(2016-06-28, 14:43)BigMong Wrote: @Sushi7

I spotted that error and I have tested the manually running the SQL
it's strange that it works when your watch an episode but that is calling a different SQL string

After a few mins looking at the source on GitHub https://github.com/xbmc/xbmc

In the /xbmc/video/VideoDatabase.cpp file, line 5155 I believe is the SQL that is Prepared
Code:
if (!date.IsValid())
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed=NULL where idFile=%i", id);
      else
        strSQL = PrepareSQL("update files set playCount=NULL,lastPlayed='%s' where idFile=%i", date.GetAsDBDateTime().c_str(), id);
    }

I will setup my 16.1 Kodi again and do some testing over the weekend

Have you made any progress??

Yep I'm getting the same issue and still can't figure it out after spending most of the night on it!


I tried to manually change the playCount or lastPlayed in phpMyAdmin and came to the same error. the table ist just not updateable.

I don't have much knowledge of mySQL but i guess the following page is relevant:

http://dev.mysql.com/doc/refman/5.7/en/v...ility.html
Reply
(2016-06-28, 13:24)Sushi7 Wrote: MySQL states error 1288:
Error: 1288 SQLSTATE: HY000 (ER_NON_UPDATABLE_TABLE)
Message: The target table %s of the %s is not updatable

https://dev.mysql.com/doc/refman/5.5/en/...erver.html

I have also checked the IS_UPDATABLE column in the INFORMATION_SCHEMA.VIEWS table and it is set properly by default.
http://dev.mysql.com/doc/refman/5.7/en/v...ility.html

If I run the query manually directly in SQL on the View Files it works
Code:
update files set playCount=NULL,lastPlayed=NULL where idFile=2

Just not from within Kodi

I was testing MySQL 5.7 with Kodi 16.1 and received this same error when attempting to mark an item as Watched/Unwatched and I was also able to confirm that running the SQL query manually does work on the database.

This led me to the conclusion that there was something at runtime that was determining if this query would work or fail - and the answer is in the view updatability information.

For the view to be updatable it must use the MERGE algorithm, but unless you have specified this when you created the view it will be using the UNDEFINED algorithm. When using UNDEFINED MySQL will choose the algorithm at runtime and (for reasons I haven't delved into) sometimes chooses an algorithm which is NOT updatable.

tl;dr: you need to specify the MERGE algorithm for your files view.

Notes: in the example below `MyVideos99` is your database and 'kodi' is your MySQL user.
Code:
CREATE OR REPLACE
   ALGORITHM = MERGE
   DEFINER = `kodi`@`%`
   SQL SECURITY DEFINER
VIEW `MyVideos99`.`files` AS    
SELECT  idFile, idPath, strFilename, playCount0 AS playCount, lastPlayed0 AS lastPlayed, dateAdded
FROM `MyVideos99`.`globalfiles`;
Reply
My head is about to explode! I've got lots of questions, but try to stick to a few at a time. I've read this post and it seems this is the best group for this. I'm using phpmyadmin, apache, sql via wampserver 3.0.6 on Win10. I'm a noob to sql of any type, and after 2 weeks I finally have it running with Kodi 17. Sort of, and half of what I want to do - sync multiple profiles across multiple devices through one server. I want to only install addons and libraries onto one device, the main pc with Win10 in my office, with all other devices to sync from the server on it. I want to share a few local libraries stored on an external usb hard drive, which isn't much, just home movies, music, family photo albums, etc. I want my main profile to be locked with the other accounts open. I'd like to incororate Emby and Traktv.

I had a small win today after mysql finally communicated with Kodi and built databases for the music and video. Which it wasn't working until I added a user via phpmyadmin where the host name is the name of my computer (WorkV2), because at first I was attempting it with the ip address and 'localhost'. Anywho, that's as far as I got.

I'm considering attempting the Database Version 99 from BigMong to get the 8+ user profiles up and running.
1) It mentions Jarvis, will it work with Krypton? Which i've installed to all my devices
2) Where does the query/code get entered on the phpmyadmin? Again, I haven't used it much, but I'm guessing copy and paste into the 'SQL' tab to 'Run SQL query/queries on server'.
3) Will I still need to edit 'advancedsettings.xml' use separate 'path substitution' commands to sync other folders for profiles, addons, lists, sources, etc? Or does this take care of it all?
4) Speaking of the path substitutions, I'm a bit confused: do I need to use my ip address of the server or the workstation's name, since that's what I had to use to make mysql and kodi talk?
a) I tried pathsub a few times from the kodi client on my office pc first before trying from other clients, and errors in the kodi log, repeated for each item I'm trying to substitute, (this is the error for lists):
ERROR: XFILE::CDirectory::Create - Error creating C:\Users\Admin\AppData\Roaming\Kodi\userdata\playlists\
ERROR: XFILE::CDirectory::Create - Error creating special://profile/playlists/
5) What files still need to be copied onto the devices and where? Just the advancedsettings.xml into [userdata] of each device?
6) I tried to sync to another device, firestick, and it didn't seem it was updating anything. Rebooted device and kodi and still appeared as though it was first installed.

I feel i'm super close and hope you guys can help!
Reply
Looking ahead, once this sql works with Kodi, what's the method for adding to the library db? Let's start with tv shows ... in the past, I would find a show, click add to library, then go back to videos and add files via the home folder/userdata/addondata/*name*plugin/, select folder tvshows, then choose the scraper, etc.
7) do i continue this method for the my 'matt_video' database from the main pc?
a) can it new shows be added or the section modified via other device?
8) since I want to be able to log in to this db from any client, what method is used when logged into a second device? does the db update accordingly for each user across devices with this '99' code?
9) will each user have privilege to add shows or is it done only while logged into master user 'matt'?
10) i imagine this question then invokes other questions surrounding locked options in profile settings...
a)are these settings controlled only from the 'matt' profile?

I had another small win today, pasted the query from Database Version 99 into the SQL query section and everything came up green checks! I changed the info to my setup. 'matt' is 'parent', 'mace' is 'kodi4fam', etc, and I changed the numbers to reflect Kodi 17, so videos 99 to 107. I've pasted the advancedsettings.xml from the main pc/userdata folder to another device userdata, will run Kodi from that device to see if anything syncs.
Reply
Rainbow 
I'm new to all this. Has anyone heard of the Infinity TV box. I saw it advertised on Facebook. I'm having problems streaming films on it. It was ok, but then I had to do a hard reset. Not sure if the company is still going. But they are useless to get hold of. No Telephone, no email, just msg on Facebook. Then very often don't reply.
Anyone help me. Or suggest where I should post this request.
Confused
Reply
That's the issue with boxes like that,
You pay for it and the resellers give you 0% support.
Also most likely they have all kind of Banned add-ons (wiki) on it.
Also you won't get support here for it.
It has nothing to-do with Kodi,
Except they are making a bad name of it.
LibreElec Kodi | Aeon MQ ?
Reply
No I was just asking if anyone could help with the problem.
Blush
Reply
Exclamation 
Update: Database Version 116 [aka Leia]

Database Version 107 [aka Krypton]
So I was finally ready to start the Kodi 17 roll out.
here is a WIP setup for multi users (Still testing everything out, but looks good)
Only looked at getting Movies and TV Shows working, this includes U01 does all the imports to the DB and keeps it tidy, rest of the users just use this data, ratings are split out if used.
Will look at fixing if needed for Musice Videos in the coming weeks as I will prob use that in my new setup.

I have space in the Database for 8 users (That's what I need), easy to remove some or add more.
A change I have made is using U01, U02, etc as I had a duplicate name, and adding a number didn't look right.

Table Names:
  • Kodi_U01_Video_107
  • Kodi_U02_Video_107
  • etc.
My Kodi Database Username is now KODI_17 Find and replace to change this.
I am moving to a new username each Kodi Version.

Getting the Database ready:
PHP Code:
--------------------
--- 
PREP GLOBALS ---
--------------------
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

-- GLOBAL 
FILES --
RENAME TABLE `Kodi_U01_Video_107`.`filesTO `Kodi_U01_Video_107`.`global_files`;
-- 
USER 01 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesCHANGE playCount playCount_U01 INT(11);
ALTER TABLE `Kodi_U01_Video_107`.`global_filesCHANGE lastPlayed lastPlayed_U01 TEXT;
-- 
USER 02 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U02 INT(11AFTER lastPlayed_U01;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U02 TEXT AFTER playCount_U02;
-- 
USER 03 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U03 INT(11AFTER lastPlayed_U02;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U03 TEXT AFTER playCount_U03;
-- 
USER 04 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U04 INT(11AFTER lastPlayed_U03;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U04 TEXT AFTER playCount_U04;
-- 
USER 05 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U05 INT(11AFTER lastPlayed_U04;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U05 TEXT AFTER playCount_U05;
-- 
USER 06 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U06 INT(11AFTER lastPlayed_U05;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U06 TEXT AFTER playCount_U06;
-- 
USER 07 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U07 INT(11AFTER lastPlayed_U06;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U07 TEXT AFTER playCount_U07;
-- 
USER 08 --
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD playCount_U08 INT(11AFTER lastPlayed_U07;
ALTER TABLE `Kodi_U01_Video_107`.`global_filesADD lastPlayed_U08 TEXT AFTER playCount_U08;
-- 
FILES VIEW FOR U01 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U01_Video_107`.`files`
AS 
SELECT
`Kodi_U01_Video_107`.`global_files`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_files`.`idPath` AS `idPath`,
`
Kodi_U01_Video_107`.`global_files`.`strFilename` AS `strFilename`,
`
Kodi_U01_Video_107`.`global_files`.`playCount_U01` AS `playCount`,
`
Kodi_U01_Video_107`.`global_files`.`lastPlayed_U01` AS `lastPlayed`,
`
Kodi_U01_Video_107`.`global_files`.`dateAdded` AS `dateAdded`
FROM `Kodi_U01_Video_107`.`global_files`;

-- GLOBAL 
EPISODE --
RENAME TABLE `Kodi_U01_Video_107`.`episodeTO `Kodi_U01_Video_107`.`global_episode`;
-- 
USER 01 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeCHANGE userrating userrating_U01 INT(11);
-- 
USER 02 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U02 INT(11AFTER userrating_U01;
-- 
USER 03 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U03 INT(11AFTER userrating_U02;
-- 
USER 04 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U04 INT(11AFTER userrating_U03;
-- 
USER 05 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U05 INT(11AFTER userrating_U04;
-- 
USER 06 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U06 INT(11AFTER userrating_U05;
-- 
USER 07 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U07 INT(11AFTER userrating_U06;
-- 
USER 08 --
ALTER TABLE `Kodi_U01_Video_107`.`global_episodeADD userrating_U08 INT(11AFTER userrating_U07;
-- 
EPISODE VIEW FOR U01 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U01_Video_107`.`episode` AS SELECT
`Kodi_U01_Video_107`.`global_episode`.`idEpisode` AS `idEpisode`,
`
Kodi_U01_Video_107`.`global_episode`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_episode`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_episode`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_episode`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_episode`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_episode`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_episode`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_episode`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_episode`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_episode`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_episode`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_episode`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_episode`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_episode`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_episode`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_episode`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_episode`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_episode`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_episode`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_episode`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_episode`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_episode`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_episode`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_episode`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_episode`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_episode`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_episode`.`userrating_U01` AS `userrating`,
`
Kodi_U01_Video_107`.`global_episode`.`idSeason` AS `idSeason`
FROM `Kodi_U01_Video_107`.`global_episode`;

-- GLOBAL 
SEASONS --
RENAME TABLE `Kodi_U01_Video_107`.`seasonsTO `Kodi_U01_Video_107`.`global_seasons`;
-- 
USER 01 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsCHANGE userrating userrating_U01 INT(11);
-- 
USER 02 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U02 INT(11AFTER userrating_U01;
-- 
USER 03 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U03 INT(11AFTER userrating_U02;
-- 
USER 04 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U04 INT(11AFTER userrating_U03;
-- 
USER 05 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U05 INT(11AFTER userrating_U04;
-- 
USER 06 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U06 INT(11AFTER userrating_U05;
-- 
USER 07 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U07 INT(11AFTER userrating_U06;
-- 
USER 08 --
ALTER TABLE `Kodi_U01_Video_107`.`global_seasonsADD userrating_U08 INT(11AFTER userrating_U07;
-- 
SEASONS VIEW FOR U01 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U01_Video_107`.`seasons` AS SELECT
`Kodi_U01_Video_107`.`global_seasons`.`idSeason` AS `idSeason`,
`
Kodi_U01_Video_107`.`global_seasons`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_seasons`.`season` AS `season`,
`
Kodi_U01_Video_107`.`global_seasons`.`name` AS `name`,
`
Kodi_U01_Video_107`.`global_seasons`.`userrating_U01` AS `userrating`
FROM `Kodi_U01_Video_107`.`global_seasons`;

-- GLOBAL 
TVSHOWS --
RENAME TABLE `Kodi_U01_Video_107`.`tvshowTO `Kodi_U01_Video_107`.`global_tvshow`;
-- 
USER 01 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowCHANGE userrating userrating_U01 INT(11);
-- 
USER 02 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U02 INT(11AFTER userrating_U01;
-- 
USER 03 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U03 INT(11AFTER userrating_U02;
-- 
USER 04 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U04 INT(11AFTER userrating_U03;
-- 
USER 05 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U05 INT(11AFTER userrating_U04;
-- 
USER 06 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U06 INT(11AFTER userrating_U05;
-- 
USER 07 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U07 INT(11AFTER userrating_U06;
-- 
USER 08 --
ALTER TABLE `Kodi_U01_Video_107`.`global_tvshowADD userrating_U08 INT(11AFTER userrating_U07;
-- 
TVSHOW VIEW FOR U01 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U01_Video_107`.`tvshow` AS SELECT
`Kodi_U01_Video_107`.`global_tvshow`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_tvshow`.`userrating_U01` AS `userrating`,
`
Kodi_U01_Video_107`.`global_tvshow`.`duration` AS `duration`
FROM `Kodi_U01_Video_107`.`global_tvshow`;

-- GLOBAL 
MOVIE --
RENAME TABLE `Kodi_U01_Video_107`.`movieTO `Kodi_U01_Video_107`.`global_movie`;
-- 
USER 01 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieCHANGE userrating userrating_U01 INT(11);
-- 
USER 02 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U02 INT(11AFTER userrating_U01;
-- 
USER 03 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U03 INT(11AFTER userrating_U02;
-- 
USER 04 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U04 INT(11AFTER userrating_U03;
-- 
USER 05 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U05 INT(11AFTER userrating_U04;
-- 
USER 06 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U06 INT(11AFTER userrating_U05;
-- 
USER 07 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U07 INT(11AFTER userrating_U06;
-- 
USER 08 --
ALTER TABLE `Kodi_U01_Video_107`.`global_movieADD userrating_U08 INT(11AFTER userrating_U07;
-- 
MOVIE VIEW FOR U01 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U01_Video_107`.`movie` AS SELECT
`Kodi_U01_Video_107`.`global_movie`.`idMovie` AS `idMovie`,
`
Kodi_U01_Video_107`.`global_movie`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_movie`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_movie`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_movie`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_movie`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_movie`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_movie`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_movie`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_movie`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_movie`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_movie`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_movie`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_movie`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_movie`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_movie`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_movie`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_movie`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_movie`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_movie`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_movie`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_movie`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_movie`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_movie`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_movie`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_movie`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_movie`.`idSet` AS `idSet`,
`
Kodi_U01_Video_107`.`global_movie`.`userrating_U01` AS `userrating`,
`
Kodi_U01_Video_107`.`global_movie`.`premiered` AS `premiered`
FROM `Kodi_U01_Video_107`.`global_movie`;

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */

User Template:
Find and Replace "_U02" with "_U03" etc for each user
PHP Code:
---------------
--- 
USER 02 ---
---------------
/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE DATABASE IF NOT EXISTS Kodi_U02_Video_107 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

-- 
FILES VIEW FOR U02 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`files` AS SELECT
`Kodi_U01_Video_107`.`global_files`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_files`.`idPath` AS `idPath`,
`
Kodi_U01_Video_107`.`global_files`.`strFilename` AS `strFilename`,
`
Kodi_U01_Video_107`.`global_files`.`playCount_U02` AS `playCount`,
`
Kodi_U01_Video_107`.`global_files`.`lastPlayed_U02` AS `lastPlayed`,
`
Kodi_U01_Video_107`.`global_files`.`dateAdded` AS `dateAdded`
FROM `Kodi_U01_Video_107`.`global_files`;

-- 
BOOKMARKS FOR U02 --
CREATE TABLE `Kodi_U02_Video_107`.`bookmark` (
`
idBookmarkINT(11NOT NULL,
`
idFileINT(11) DEFAULT NULL,
`
timeInSecondsDOUBLE DEFAULT NULL,
`
totalTimeInSecondsDOUBLE DEFAULT NULL,
`
thumbNailImagetext,
`
playertext,
`
playerStatetext,
`
typeINT(11) DEFAULT NULL
ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `Kodi_U02_Video_107`.`bookmarkADD PRIMARY KEY (`idBookmark`), ADD KEY `ix_bookmark` (`idFile`,`type`);
ALTER TABLE `Kodi_U02_Video_107`.`bookmarkMODIFY `idBookmarkINT(11NOT NULL AUTO_INCREMENT;

-- 
EPISODE VIEW FOR U02 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`episode` AS SELECT
`Kodi_U01_Video_107`.`global_episode`.`idEpisode` AS `idEpisode`,
`
Kodi_U01_Video_107`.`global_episode`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_episode`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_episode`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_episode`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_episode`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_episode`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_episode`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_episode`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_episode`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_episode`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_episode`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_episode`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_episode`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_episode`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_episode`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_episode`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_episode`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_episode`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_episode`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_episode`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_episode`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_episode`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_episode`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_episode`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_episode`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_episode`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_episode`.`userrating_U02` AS `userrating`,
`
Kodi_U01_Video_107`.`global_episode`.`idSeason` AS `idSeason`
FROM `Kodi_U01_Video_107`.`global_episode`;

-- 
SEASONS VIEW FOR U02 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`seasons` AS SELECT
`Kodi_U01_Video_107`.`global_seasons`.`idSeason` AS `idSeason`,
`
Kodi_U01_Video_107`.`global_seasons`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_seasons`.`season` AS `season`,
`
Kodi_U01_Video_107`.`global_seasons`.`name` AS `name`,
`
Kodi_U01_Video_107`.`global_seasons`.`userrating_U02` AS `userrating`
FROM `Kodi_U01_Video_107`.`global_seasons`;

-- 
TVSHOW VIEW FOR U02 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tvshow` AS SELECT
`Kodi_U01_Video_107`.`global_tvshow`.`idShow` AS `idShow`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_tvshow`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_tvshow`.`userrating_U02` AS `userrating`,
`
Kodi_U01_Video_107`.`global_tvshow`.`duration` AS `duration`
FROM `Kodi_U01_Video_107`.`global_tvshow`;

-- 
MOVIE VIEWS FOR U02 --
CREATE ALGORITHM=MERGE DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`movie` AS SELECT
`Kodi_U01_Video_107`.`global_movie`.`idMovie` AS `idMovie`,
`
Kodi_U01_Video_107`.`global_movie`.`idFile` AS `idFile`,
`
Kodi_U01_Video_107`.`global_movie`.`c00` AS `c00`,
`
Kodi_U01_Video_107`.`global_movie`.`c01` AS `c01`,
`
Kodi_U01_Video_107`.`global_movie`.`c02` AS `c02`,
`
Kodi_U01_Video_107`.`global_movie`.`c03` AS `c03`,
`
Kodi_U01_Video_107`.`global_movie`.`c04` AS `c04`,
`
Kodi_U01_Video_107`.`global_movie`.`c05` AS `c05`,
`
Kodi_U01_Video_107`.`global_movie`.`c06` AS `c06`,
`
Kodi_U01_Video_107`.`global_movie`.`c07` AS `c07`,
`
Kodi_U01_Video_107`.`global_movie`.`c08` AS `c08`,
`
Kodi_U01_Video_107`.`global_movie`.`c09` AS `c09`,
`
Kodi_U01_Video_107`.`global_movie`.`c10` AS `c10`,
`
Kodi_U01_Video_107`.`global_movie`.`c11` AS `c11`,
`
Kodi_U01_Video_107`.`global_movie`.`c12` AS `c12`,
`
Kodi_U01_Video_107`.`global_movie`.`c13` AS `c13`,
`
Kodi_U01_Video_107`.`global_movie`.`c14` AS `c14`,
`
Kodi_U01_Video_107`.`global_movie`.`c15` AS `c15`,
`
Kodi_U01_Video_107`.`global_movie`.`c16` AS `c16`,
`
Kodi_U01_Video_107`.`global_movie`.`c17` AS `c17`,
`
Kodi_U01_Video_107`.`global_movie`.`c18` AS `c18`,
`
Kodi_U01_Video_107`.`global_movie`.`c19` AS `c19`,
`
Kodi_U01_Video_107`.`global_movie`.`c20` AS `c20`,
`
Kodi_U01_Video_107`.`global_movie`.`c21` AS `c21`,
`
Kodi_U01_Video_107`.`global_movie`.`c22` AS `c22`,
`
Kodi_U01_Video_107`.`global_movie`.`c23` AS `c23`,
`
Kodi_U01_Video_107`.`global_movie`.`idSet` AS `idSet`,
`
Kodi_U01_Video_107`.`global_movie`.`userrating_U02` AS `userrating`,
`
Kodi_U01_Video_107`.`global_movie`.`premiered` AS `premiered`
FROM `Kodi_U01_Video_107`.`global_movie`;

-- 
CREATE REST OF THE VIEWS --
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`actor` AS SELECT FROM `Kodi_U01_Video_107`.`actor`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`actor_link` AS SELECT FROM `Kodi_U01_Video_107`.`actor_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`art` AS SELECT FROM `Kodi_U01_Video_107`.`art`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`country` AS SELECT FROM `Kodi_U01_Video_107`.`country`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`country_link` AS SELECT FROM `Kodi_U01_Video_107`.`country_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`director_link` AS SELECT FROM `Kodi_U01_Video_107`.`director_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`genre` AS SELECT FROM `Kodi_U01_Video_107`.`genre`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`genre_link` AS SELECT FROM `Kodi_U01_Video_107`.`genre_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`movielinktvshow` AS SELECT FROM `Kodi_U01_Video_107`.`movielinktvshow`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`musicvideo` AS SELECT FROM `Kodi_U01_Video_107`.`musicvideo`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`path` AS SELECT FROM `Kodi_U01_Video_107`.`path`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`rating` AS SELECT FROM `Kodi_U01_Video_107`.`rating`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`sets` AS SELECT FROM `Kodi_U01_Video_107`.`sets`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`settings` AS SELECT FROM `Kodi_U01_Video_107`.`settings`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`stacktimes` AS SELECT FROM `Kodi_U01_Video_107`.`stacktimes`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`streamdetails` AS SELECT FROM `Kodi_U01_Video_107`.`streamdetails`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`studio` AS SELECT FROM `Kodi_U01_Video_107`.`studio`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`studio_link` AS SELECT FROM `Kodi_U01_Video_107`.`studio_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tag` AS SELECT FROM `Kodi_U01_Video_107`.`tag`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tag_link` AS SELECT FROM `Kodi_U01_Video_107`.`tag_link`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tvshowlinkpath` AS SELECT FROM `Kodi_U01_Video_107`.`tvshowlinkpath`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`uniqueid` AS SELECT FROM `Kodi_U01_Video_107`.`uniqueid`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`version` AS SELECT FROM `Kodi_U01_Video_107`.`version`;
CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`writer_link` AS SELECT FROM `Kodi_U01_Video_107`.`writer_link`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`episode_view` AS SELECT
`episode`.`idEpisode` AS `idEpisode`,
`
episode`.`idFile` AS `idFile`,
`
episode`.`c00` AS `c00`,
`
episode`.`c01` AS `c01`,
`
episode`.`c02` AS `c02`,
`
episode`.`c03` AS `c03`,
`
episode`.`c04` AS `c04`,
`
episode`.`c05` AS `c05`,
`
episode`.`c06` AS `c06`,
`
episode`.`c07` AS `c07`,
`
episode`.`c08` AS `c08`,
`
episode`.`c09` AS `c09`,
`
episode`.`c10` AS `c10`,
`
episode`.`c11` AS `c11`,
`
episode`.`c12` AS `c12`,
`
episode`.`c13` AS `c13`,
`
episode`.`c14` AS `c14`,
`
episode`.`c15` AS `c15`,
`
episode`.`c16` AS `c16`,
`
episode`.`c17` AS `c17`,
`
episode`.`c18` AS `c18`,
`
episode`.`c19` AS `c19`,
`
episode`.`c20` AS `c20`,
`
episode`.`c21` AS `c21`,
`
episode`.`c22` AS `c22`,
`
episode`.`c23` AS `c23`,
`
episode`.`idShow` AS `idShow`,
`
episode`.`userrating` AS `userrating`,
`
episode`.`idSeason` AS `idSeason`,
`
files`.`strFilename` AS `strFileName`,
`
path`.`strPath` AS `strPath`,
`
files`.`playCount` AS `playCount`,
`
files`.`lastPlayed` AS `lastPlayed`,
`
files`.`dateAdded` AS `dateAdded`,
`
tvshow`.`c00` AS `strTitle`,
`
tvshow`.`c08` AS `genre`,
`
tvshow`.`c14` AS `studio`,
`
tvshow`.`c05` AS `premiered`,
`
tvshow`.`c13` AS `mpaa`,
`
bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`
bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`
rating`.`rating` AS `rating`,
`
rating`.`votes` AS `votes`,
`
rating`.`rating_type` AS `rating_type`,
`
uniqueid`.`value` AS `uniqueid_value`,
`
uniqueid`.`type` AS `uniqueid_type`
FROM (((((((`Kodi_U02_Video_107`.`episodeJOIN `Kodi_U02_Video_107`.`filesON((`files`.`idFile` = `episode`.`idFile`)))
JOIN `Kodi_U02_Video_107`.`tvshowON((`tvshow`.`idShow` = `episode`.`idShow`)))
JOIN `Kodi_U02_Video_107`.`seasonsON((`seasons`.`idSeason` = `episode`.`idSeason`)))
JOIN `Kodi_U02_Video_107`.`pathON((`files`.`idPath` = `path`.`idPath`)))
LEFT JOIN `Kodi_U02_Video_107`.`bookmarkON(((`bookmark`.`idFile` = `episode`.`idFile`) AND (`bookmark`.`type` = 1))))
LEFT JOIN `Kodi_U02_Video_107`.`ratingON((`rating`.`rating_id` = `episode`.`c03`)))
LEFT JOIN `Kodi_U02_Video_107`.`uniqueidON((`uniqueid`.`uniqueid_id` = `episode`.`c20`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tvshowcounts` AS SELECT
`tvshow`.`idShow` AS `idShow`,
MAX(`files`.`lastPlayed`) AS `lastPlayed`,
NULLIF(COUNT(`episode`.`c12`),0) AS `totalCount`,
COUNT(`files`.`playCount`) AS `watchedcount`,
NULLIF(COUNT(DISTINCT `episode`.`c12`),0) AS `totalSeasons`,
MAX(`files`.`dateAdded`) AS `dateAdded`
FROM ((`Kodi_U02_Video_107`.`tvshowLEFT JOIN `Kodi_U02_Video_107`.`episodeON((`episode`.`idShow` = `tvshow`.`idShow`)))
LEFT JOIN `Kodi_U02_Video_107`.`filesON((`files`.`idFile` = `episode`.`idFile`)))
GROUP BY `tvshow`.`idShow`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`tvshow_view` AS SELECT
`tvshow`.`idShow` AS `idShow`,
`
tvshow`.`c00` AS `c00`,
`
tvshow`.`c01` AS `c01`,
`
tvshow`.`c02` AS `c02`,
`
tvshow`.`c03` AS `c03`,
`
tvshow`.`c04` AS `c04`,
`
tvshow`.`c05` AS `c05`,
`
tvshow`.`c06` AS `c06`,
`
tvshow`.`c07` AS `c07`,
`
tvshow`.`c08` AS `c08`,
`
tvshow`.`c09` AS `c09`,
`
tvshow`.`c10` AS `c10`,
`
tvshow`.`c11` AS `c11`,
`
tvshow`.`c12` AS `c12`,
`
tvshow`.`c13` AS `c13`,
`
tvshow`.`c14` AS `c14`,
`
tvshow`.`c15` AS `c15`,
`
tvshow`.`c16` AS `c16`,
`
tvshow`.`c17` AS `c17`,
`
tvshow`.`c18` AS `c18`,
`
tvshow`.`c19` AS `c19`,
`
tvshow`.`c20` AS `c20`,
`
tvshow`.`c21` AS `c21`,
`
tvshow`.`c22` AS `c22`,
`
tvshow`.`c23` AS `c23`,
`
tvshow`.`userrating` AS `userrating`,
`
tvshow`.`duration` AS `duration`,
`
path`.`idParentPath` AS `idParentPath`,
`
path`.`strPath` AS `strPath`,
`
tvshowcounts`.`dateAdded` AS `dateAdded`,
`
tvshowcounts`.`lastPlayed` AS `lastPlayed`,
`
tvshowcounts`.`totalCount` AS `totalCount`,
`
tvshowcounts`.`watchedcount` AS `watchedcount`,
`
tvshowcounts`.`totalSeasons` AS `totalSeasons`,
`
rating`.`rating` AS `rating`,
`
rating`.`votes` AS `votes`,
`
rating`.`rating_type` AS `rating_type`,
`
uniqueid`.`value` AS `uniqueid_value`,
`
uniqueid`.`type` AS `uniqueid_type`
FROM (((((`Kodi_U02_Video_107`.`tvshowLEFT JOIN `Kodi_U02_Video_107`.`tvshowlinkpathON((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`)))
LEFT JOIN `Kodi_U02_Video_107`.`pathON((`path`.`idPath` = `tvshowlinkpath`.`idPath`)))
JOIN `Kodi_U02_Video_107`.`tvshowcountsON((`tvshow`.`idShow` = `tvshowcounts`.`idShow`)))
LEFT JOIN `Kodi_U02_Video_107`.`ratingON((`rating`.`rating_id` = `tvshow`.`c04`)))
LEFT JOIN `Kodi_U02_Video_107`.`uniqueidON((`uniqueid`.`uniqueid_id` = `tvshow`.`c12`)))
GROUP BY `Kodi_U02_Video_107`.`tvshow`.`idShow`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`season_view` AS SELECT
`seasons`.`idSeason` AS `idSeason`,
`
seasons`.`idShow` AS `idShow`,
`
seasons`.`season` AS `season`,
`
seasons`.`name` AS `name`,
`
seasons`.`userrating` AS `userrating`,
`
tvshow_view`.`strPath` AS `strPath`,
`
tvshow_view`.`c00` AS `showTitle`,
`
tvshow_view`.`c01` AS `plot`,
`
tvshow_view`.`c05` AS `premiered`,
`
tvshow_view`.`c08` AS `genre`,
`
tvshow_view`.`c14` AS `studio`,
`
tvshow_view`.`c13` AS `mpaa`,
COUNT(DISTINCT `episode`.`idEpisode`) AS `episodes`,
COUNT(`files`.`playCount`) AS `playCount`,
MIN(`episode`.`c05`) AS `aired`
FROM (((`Kodi_U02_Video_107`.`seasonsJOIN `Kodi_U02_Video_107`.`tvshow_viewON((`tvshow_view`.`idShow` = `seasons`.`idShow`)))
JOIN `Kodi_U02_Video_107`.`episodeON(((`episode`.`idShow` = `seasons`.`idShow`) AND (`episode`.`c12` = `seasons`.`season`))))
JOIN `Kodi_U02_Video_107`.`filesON((`files`.`idFile` = `episode`.`idFile`)))
GROUP BY `seasons`.`idSeason`;

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`movie_view` AS SELECT
`movie`.`idMovie` AS `idMovie`,
`
movie`.`idFile` AS `idFile`,
`
movie`.`c00` AS `c00`,
`
movie`.`c01` AS `c01`,
`
movie`.`c02` AS `c02`,
`
movie`.`c03` AS `c03`,
`
movie`.`c04` AS `c04`,
`
movie`.`c05` AS `c05`,
`
movie`.`c06` AS `c06`,
`
movie`.`c07` AS `c07`,
`
movie`.`c08` AS `c08`,
`
movie`.`c09` AS `c09`,
`
movie`.`c10` AS `c10`,
`
movie`.`c11` AS `c11`,
`
movie`.`c12` AS `c12`,
`
movie`.`c13` AS `c13`,
`
movie`.`c14` AS `c14`,
`
movie`.`c15` AS `c15`,
`
movie`.`c16` AS `c16`,
`
movie`.`c17` AS `c17`,
`
movie`.`c18` AS `c18`,
`
movie`.`c19` AS `c19`,
`
movie`.`c20` AS `c20`,
`
movie`.`c21` AS `c21`,
`
movie`.`c22` AS `c22`,
`
movie`.`c23` AS `c23`,
`
movie`.`idSet` AS `idSet`,
`
movie`.`userrating` AS `userrating`,
`
movie`.`premiered` AS `premiered`,
`
sets`.`strSet` AS `strSet`,
`
sets`.`strOverview` AS `strSetOverview`,
`
files`.`strFilename` AS `strFileName`,
`
path`.`strPath` AS `strPath`,
`
files`.`playCount` AS `playCount`,
`
files`.`lastPlayed` AS `lastPlayed`,
`
files`.`dateAdded` AS `dateAdded`,
`
bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`
bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,
`
rating`.`rating` AS `rating`,
`
rating`.`votes` AS `votes`,
`
rating`.`rating_type` AS `rating_type`,
`
uniqueid`.`value` AS `uniqueid_value`,
`
uniqueid`.`type` AS `uniqueid_type`
FROM ((((((`Kodi_U02_Video_107`.`movieLEFT JOIN `Kodi_U02_Video_107`.`setsON((`sets`.`idSet` = `movie`.`idSet`)))
JOIN `Kodi_U02_Video_107`.`filesON((`files`.`idFile` = `movie`.`idFile`)))
JOIN `Kodi_U02_Video_107`.`pathON((`path`.`idPath` = `files`.`idPath`)))
LEFT JOIN `Kodi_U02_Video_107`.`bookmarkON(((`bookmark`.`idFile` = `movie`.`idFile`) AND (`bookmark`.`type` = 1))))
LEFT JOIN `Kodi_U02_Video_107`.`ratingON((`rating`.`rating_id` = `movie`.`c05`)))
LEFT JOIN `Kodi_U02_Video_107`.`uniqueidON((`uniqueid`.`uniqueid_id` = `movie`.`c09`)));

CREATE ALGORITHM=UNDEFINED DEFINER=`KODI_17`@`%SQL SECURITY DEFINER VIEW `Kodi_U02_Video_107`.`musicvideo_view` AS SELECT
`musicvideo`.`idMVideo` AS `idMVideo`,
`
musicvideo`.`idFile` AS `idFile`,
`
musicvideo`.`c00` AS `c00`,
`
musicvideo`.`c01` AS `c01`,
`
musicvideo`.`c02` AS `c02`,
`
musicvideo`.`c03` AS `c03`,
`
musicvideo`.`c04` AS `c04`,
`
musicvideo`.`c05` AS `c05`,
`
musicvideo`.`c06` AS `c06`,
`
musicvideo`.`c07` AS `c07`,
`
musicvideo`.`c08` AS `c08`,
`
musicvideo`.`c09` AS `c09`,
`
musicvideo`.`c10` AS `c10`,
`
musicvideo`.`c11` AS `c11`,
`
musicvideo`.`c12` AS `c12`,
`
musicvideo`.`c13` AS `c13`,
`
musicvideo`.`c14` AS `c14`,
`
musicvideo`.`c15` AS `c15`,
`
musicvideo`.`c16` AS `c16`,
`
musicvideo`.`c17` AS `c17`,
`
musicvideo`.`c18` AS `c18`,
`
musicvideo`.`c19` AS `c19`,
`
musicvideo`.`c20` AS `c20`,
`
musicvideo`.`c21` AS `c21`,
`
musicvideo`.`c22` AS `c22`,
`
musicvideo`.`c23` AS `c23`,
`
musicvideo`.`userrating` AS `userrating`,
`
musicvideo`.`premiered` AS `premiered`,
`
files`.`strFilename` AS `strFileName`,
`
path`.`strPath` AS `strPath`,
`
files`.`playCount` AS `playCount`,
`
files`.`lastPlayed` AS `lastPlayed`,
`
files`.`dateAdded` AS `dateAdded`,
`
bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,
`
bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`
FROM (((`Kodi_U02_Video_107`.`musicvideoJOIN `Kodi_U02_Video_107`.`filesON((`files`.`idFile` = `musicvideo`.`idFile`)))
JOIN `Kodi_U02_Video_107`.`pathON((`path`.`idPath` = `files`.`idPath`)))
LEFT JOIN `Kodi_U02_Video_107`.`bookmarkON(((`bookmark`.`idFile` = `musicvideo`.`idFile`) AND (`bookmark`.`type` = 1))));

/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */

Update the Delete File Trigger so when you remove items it will clean up the Bookmarks for each user.
Add more lines in like the "Kodi_U02_Video_107" for each Database you have.
PHP Code:
----------------
--- 
TRIGGERS ---
----------------
-- 
TRIGGER FIX FOR MULT USERS --
DROP TRIGGER IF EXISTS `Kodi_U01_Video_107`.`delete_file`;
DELIMITER $$
CREATE DEFINER=`KODI_17`@`%TRIGGER `Kodi_U01_Video_107`.`delete_fileAFTER DELETE
ON 
`Kodi_U01_Video_107`.`global_files`
FOR 
EACH ROW
BEGIN
DELETE FROM 
`Kodi_U01_Video_107`.bookmark WHERE idfile old.idfile;
DELETE FROM `Kodi_U02_Video_107`.bookmark WHERE idfile old.idfile;
DELETE FROM settings WHERE idfile old.idfile;
DELETE FROM stacktimes WHERE idfile old.idfile;
DELETE FROM streamdetails WHERE idfile old.idfile;
END$$
DELIMITER 

Update:
Fixed a small error in the user(s) it was point to the U01 Ratings not there own.
Reply
If you like to move your Watched status over from an older Database

Just update this to point to your new and old Database and Fields
NEWTBL = New Database Table
OLDTBL = Old Database Table

PHP Code:
------------------------------
--- 
COPY OVER WATCH STATUS ---
------------------------------
SET SQL_SAFE_UPDATES 0;
UPDATE Kodi_Matt_Video_T_107.global_files AS NEWTBL
        INNER JOIN
    Kodi_Matt_Video_93
.globalfiles AS OLDTBL ON NEWTBL.strFilename OLDTBL.strFilename 
SET 
    NEWTBL
.lastPlayed_U01 OLDTBL.lastPlayedMatt,
    
NEWTBL.playCount_U01 OLDTBL.playCountMatt
WHERE
    NEWTBL
.strFilename OLDTBL.strFilename;
SET SQL_SAFE_UPDATES 1
Reply
Thanks for posting this. It saved me a lot of time.
Reply
Update:
Fixed a small error in the user(s) it was point to the U01 Ratings not there own.

code been updated in the other post
Reply
Thanks BigMong, I can confirm it worked like a charm.
Reply
The update watched status script kept timing out on me (close to 20000 rows in each of the tables)
I fixed it by adding a "IS NOT NULL" check

Using your example script, this is how I improved it:

PHP Code:
------------------------------
--- 
COPY OVER WATCH STATUS ---
------------------------------
SET SQL_SAFE_UPDATES 0;
UPDATE Kodi_Matt_Video_T_107.global_files AS NEWTBL
        INNER JOIN
    Kodi_Matt_Video_93
.globalfiles AS OLDTBL ON NEWTBL.strFilename OLDTBL.strFilename 
SET 
    NEWTBL
.lastPlayed_U01 OLDTBL.lastPlayedMatt,
    
NEWTBL.playCount_U01 OLDTBL.playCountMatt
WHERE
    NEWTBL
.strFilename OLDTBL.strFilename AND OLDTBL.lastPlayedMatt IS NOT NULL;
SET SQL_SAFE_UPDATES 1

This took the script from running into a timeout after 10 minutes down to slightly over 3 minutes
Reply
(2017-02-26, 07:14)Enigma256 Wrote: The update watched status script kept timing out on me (close to 20000 rows in each of the tables)
I fixed it by adding a "IS NOT NULL" check

Using your example script, this is how I improved it:

PHP Code:
------------------------------
--- 
COPY OVER WATCH STATUS ---
------------------------------
SET SQL_SAFE_UPDATES 0;
UPDATE Kodi_Matt_Video_T_107.global_files AS NEWTBL
        INNER JOIN
    Kodi_Matt_Video_93
.globalfiles AS OLDTBL ON NEWTBL.strFilename OLDTBL.strFilename 
SET 
    NEWTBL
.lastPlayed_U01 OLDTBL.lastPlayedMatt,
    
NEWTBL.playCount_U01 OLDTBL.playCountMatt
WHERE
    NEWTBL
.strFilename OLDTBL.strFilename AND OLDTBL.lastPlayedMatt IS NOT NULL;
SET SQL_SAFE_UPDATES 1

This took the script from running into a timeout after 10 minutes down to slightly over 3 minutes

Awesome I didn't even spot the timeout, Thank You
Reply
  •   
  • 1
  • 14
  • 15
  • 16(current)
  • 17
  • 18
  • 22
  •   
 
Thread Rating:
  • 4 Vote(s) - 5 Average



Logout Mark Read Team Forum Stats Members Help
[MYSQL] HOW-TO: 5 User XBMC54