Login at Kodi Home

a13x · 2012-12-25, 15:50

@DLow and @timmyj9: Thanks for sharing your SQL Scripts ! I will give it a try in the next days.

But what is your experience with Thumbnail Cache and the new Textures DB ?
Do you share the Thumbnail Cache / Textures DB or have each XBMC his own cache path and Textures DB ?
If each XBMC Client has it's own and you scan for new files on one Client, do the other clients refresh the Thumbnail Cache and Textures DB as soon as I access the new file for the first time on this client or do I need to scan on each client separately ?

Hack_kid · 2012-12-26, 06:23

they automaticly refresh the thumb on its own. like magic lol

RonM · 2012-12-27, 10:11

I've created the databases and views like timmyj9 showed. So far I've made it working on one device with 3 different profiles and each maintain their own watched and bookmark status while sharing the libraries and artwork.
Will now start adding the other XBMC devices in the house (PC's and RaspberryPi's) and will report back on that.

BTW, I'm currently running the OpenELEC 2.95.6 beta (Frodo RC2).

Thanks timmyj9, for all the work you've done figuring out the details of the tables and fields involved!

timmyj9 · 2013-01-11, 09:47

a13x

i'm afraid i've not looked into the Textures DB at all, so I assume each client is just replicating it locally

however I do have a thumbnails folder on my zfs server which is centralised and shared by all the users just using path substitution for each corresponding advancedsettings.xml

RonM · (This post was last modified: 2013-01-11, 23:20 by RonM.)

At last I was able to test my second XBMC device using the same central MySQL database, configured as described by timmyj9. It's a RaspberryPi, so things are a bit slow (especially building the list of TV Shows before showing the screen with the 15 TV Show banners).
But anyway, it works! The watched status is synchronized and (most) thumbnails, banners and synopsis are also available on the Pi. I noticed that the thumbnails of some episodes of a Dutch TV Show didn't appear on the Pi as they do on the other (Atom-based) XBMC system. Will need to do some more investigating to find the reason for that.

BTW, I am running OpenELEC RC1 (2.99.1) on both devices; the Pi from a SD card connected to the network with an ethernet connection, the Atom (ION2) from an SSD also ethernet connected (GBE).

Thanks timmyj9!

mcfang · 2013-10-20, 09:16

XBMC alpha has updated to video database version 76. XBMC automatically creates a new database and copies the views, but the views still select from the old version 75 database.

This sample script will update the views, for a video database named User1Videos

bakslash · 2013-12-01, 23:18

Here is a workaround that does not need as much space as two databases.

claymic · 2014-02-16, 21:50

Thank you guys, this is exactly what i was looking for.
Now, can you guys tell me if when i insert a new movie the movie will be automatically added to the second database ? or i need to do something ?
Thanks again.
Clayton

bohdans · (This post was last modified: 2014-04-21, 11:20 by bohdans.)

Hi Guys,
Sorry to hit up and old thread, But I have been using this method for a long time, and thought I might update this thread with a v13 compatible script.
Your "master" (the one that scans for new media) should have this in the advancedsettings.xml

Code:
<videodatabase>

        <type>mysql</type>

        <host>[mysqlserver]</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

    </videodatabase>

And your first user will have the following in the advancedsettings.xml

Code:
<videodatabase>

        <type>mysql</type>

        <host>[mysqlserver]</host>

        <name>User1Videos</name>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

    </videodatabase>

Run the following script: (change User1Videos to User2Videos etc... for extra users)

Code:
CREATE DATABASE User1Videos78;

USE User1Videos78;

CREATE VIEW `actorlinkepisode` AS SELECT * FROM `MyVideos78`.`actorlinkepisode`;

CREATE VIEW `actorlinkmovie` AS SELECT * FROM `MyVideos78`.`actorlinkmovie`;

CREATE VIEW `actorlinktvshow` AS SELECT * FROM `MyVideos78`.`actorlinktvshow`;

CREATE VIEW `actors` AS SELECT * FROM `MyVideos78`.`actors`;

CREATE VIEW `art` AS SELECT * FROM `MyVideos78`.`art`;

CREATE VIEW `artistlinkmusicvideo` AS SELECT * FROM `MyVideos78`.`artistlinkmusicvideo`;

CREATE VIEW `country` AS SELECT * FROM `MyVideos78`.`country`;

CREATE VIEW `countrylinkmovie` AS SELECT * FROM `MyVideos78`.`countrylinkmovie`;

CREATE VIEW `directorlinkepisode` AS SELECT * FROM `MyVideos78`.`directorlinkepisode`;

CREATE VIEW `directorlinkmovie` AS SELECT * FROM `MyVideos78`.`directorlinkmovie`;

CREATE VIEW `directorlinkmusicvideo` AS SELECT * FROM `MyVideos78`.`directorlinkmusicvideo`;

CREATE VIEW `directorlinktvshow` AS SELECT * FROM `MyVideos78`.`directorlinktvshow`;

CREATE VIEW `episode` AS SELECT * FROM `MyVideos78`.`episode`;

CREATE VIEW `genre` AS SELECT * FROM `MyVideos78`.`genre`;

CREATE VIEW `genrelinkmovie` AS SELECT * FROM `MyVideos78`.`genrelinkmovie`;

CREATE VIEW `genrelinkmusicvideo` AS SELECT * FROM `MyVideos78`.`genrelinkmusicvideo`;

CREATE VIEW `genrelinktvshow` AS SELECT * FROM `MyVideos78`.`genrelinktvshow`;

CREATE VIEW `movie` AS SELECT * FROM `MyVideos78`.`movie`;

CREATE VIEW `movielinktvshow` AS SELECT * FROM `MyVideos78`.`movielinktvshow`;

CREATE VIEW `musicvideo` AS SELECT * FROM `MyVideos78`.`musicvideo`;

CREATE VIEW `path` AS SELECT * FROM `MyVideos78`.`path`;

CREATE VIEW `seasons` AS SELECT * FROM `MyVideos78`.`seasons`;

CREATE VIEW `settings` AS SELECT * FROM `MyVideos78`.`settings`;

CREATE VIEW `sets` AS SELECT * FROM `MyVideos78`.`sets`;

CREATE VIEW `stacktimes` AS SELECT * FROM `MyVideos78`.`stacktimes`;

CREATE VIEW `streamdetails` AS SELECT * FROM `MyVideos78`.`streamdetails`;

CREATE VIEW `studio` AS SELECT * FROM `MyVideos78`.`studio`;

CREATE VIEW `studiolinkmovie` AS SELECT * FROM `MyVideos78`.`studiolinkmovie`;

CREATE VIEW `studiolinkmusicvideo` AS SELECT * FROM `MyVideos78`.`studiolinkmusicvideo`;

CREATE VIEW `studiolinktvshow` AS SELECT * FROM `MyVideos78`.`studiolinktvshow`;

CREATE VIEW `tag` AS SELECT * FROM `MyVideos78`.`tag`;

CREATE VIEW `taglinks` AS SELECT * FROM `MyVideos78`.`taglinks`;

CREATE VIEW `tvshow` AS SELECT * FROM `MyVideos78`.`tvshow`;

CREATE VIEW `tvshowlinkpath` AS SELECT * FROM `MyVideos78`.`tvshowlinkpath`;

CREATE VIEW `version` AS SELECT * FROM `MyVideos78`.`version`;

CREATE VIEW `writerlinkepisode` AS SELECT * FROM `MyVideos78`.`writerlinkepisode`;

CREATE VIEW `writerlinkmovie` AS SELECT * FROM `MyVideos78`.`writerlinkmovie`;

CREATE TABLE `bookmark` (`idBookmark` int(11) NOT NULL auto_increment,`idFile` int(11) default NULL,`timeInSeconds` double default NULL,`totalTimeInSeconds` double default NULL,`thumbNailImage` text,`player` text,`playerState` text,`type` int(11) default NULL,PRIMARY KEY  (`idBookmark`),KEY `ix_bookmark` (`idFile`,`type`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE TABLE `watched_history` (`idFile` int(11) NOT NULL,`playCount` int(11) DEFAULT NULL,`lastPlayed` text,PRIMARY KEY (`idFile`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW`files` AS SELECT `f`.`idFile` AS `idFile`,`f`.`idPath` AS `idPath`,`f`.`strFilename` AS `strFilename`,`wh`.`playCount` AS `playCount`,`wh`.`lastPlayed` AS `lastPlayed`, `f`.`dateAdded` AS `dateAdded` FROM `MyVideos78`.`files` `f` LEFT JOIN `User1Videos78`.`watched_history` `wh` ON `f`.`idFile` = `wh`.`idFile`;

CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `episodeview` 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`,`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`.`c14` AS `strStudio`,`tvshow`.`c05` AS `premiered`,`tvshow`.`c13` AS `mpaa`,`tvshow`.`c16` AS `strShowPath`,`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,`seasons`.`idSeason` AS `idSeason` from (((((`episode` join `files` on((`files`.`idFile` = `episode`.`idFile`))) join `tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `path` on((`files`.`idPath` = `path`.`idPath`))) left join `bookmark` on(((`bookmark`.`idFile` = `episode`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `movieview` 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`,`sets`.`strSet` AS `strSet`,`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 ((((`movie` left join `sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `files` on((`files`.`idFile` = `movie`.`idFile`))) join `path` on((`path`.`idPath` = `files`.`idPath`))) left join `bookmark` on(((`bookmark`.`idFile` = `movie`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `musicvideoview` 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`,`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 (((`musicvideo` join `files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `path` on((`path`.`idPath` = `files`.`idPath`))) left join `bookmark` on(((`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`bookmark`.`type` = 1))));

CREATE ALGORITHM=UNDEFINED DEFINER=`xbmc`@`%` SQL SECURITY DEFINER VIEW `tvshowview` 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`,`path`.`strPath` AS `strPath`,`path`.`dateAdded` AS `dateAdded`,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` from ((((`tvshow` left join `tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

This will have separate watched "ticks" for each user, and separate bookmarks for where each video is upto. Everything else will be shared,
I have around 7,000 media files, the MyVideos78 db is around 40mb, the User1Videos78 db is around 100kb.

I will have this up on my blog http://blog.bohdans.com/ with a bit more info and a tutorial in the next few days.

rebelyell · 2014-04-30, 15:24

Is this working with the latest Gotham RC1?

I tried with both MySQL 5.x and MariaDB 10.x on Windows with the same result - the watched state doesn't seem to change. Looking at the log file, the query is sent to server:

Code:
Mysql execute: update files set playCount=1,lastPlayed='2014-04-30 16:07:05' where idFile=26

No errors after, however, playCount and lastPlayed are still NULL in the User1Videos78's database.

carion · (This post was last modified: 2014-05-01, 10:07 by carion.)

(2014-04-30, 15:24)rebelyell Wrote: Is this working with the latest Gotham RC1?

I tried with both MySQL 5.x and MariaDB 10.x on Windows with the same result - the watched state doesn't seem to change. Looking at the log file, the query is sent to server:

Code:
Mysql execute: update files set playCount=1,lastPlayed='2014-04-30 16:07:05' where idFile=26

No errors after, however, playCount and lastPlayed are still NULL in the User1Videos78's database.

I am also having the same problem. Everything appears to work fine but the play count's don't update

EDIT: On the other hand the bookmark table appears to work fine, there is a problem with the watched_history table. But unfortunately my SQL skills are pretty limited haha.

sompie · 2014-05-05, 15:15

i've updated to xbmc 13 but the database upgrade fails it won't copy the views how can I solve this problem

rebelyell · (This post was last modified: 2014-05-07, 15:34 by rebelyell.)

I've tried the workaround given by bakslash - post #37 - and now it works. Thank you, bakslash.

N4TH4N · (This post was last modified: 2014-05-25, 09:20 by N4TH4N.)

Hey Guys,

Ive been running XBMC since the beginning on the original XBOX and have come along way since then.

I developed a custom 5 user SQL database structure based on what others have done but put my own spin on it.

I have been using it for over a year without any problems.

Long story short it uses a master database with 4 extra databases linked to it.

Each of the 5 databases have their own bookmarks and watched status. Other then that they are identical.

Ive tried to make it as simple as possible to understand. So here goes.

Get XBMC to create a database file called "a78" (Just add "a" as the database name in advancedsettings.xml and XBMC will create it).

Code:
<advancedsettings>

  <videodatabase>

    <type>mysql</type>

    <host>IPADDRESS</host>

    <port>3306</port>

    <user>USERNAME</user>

    <pass>PASSWORD</pass>

    <name>a</name>

  </videodatabase>

</advancedsettings>

CLOSE XBMC

Run the following query and your almost done:

Code:
RENAME TABLE `a78`.`files` to `a78`.`globalfiles`;

ALTER TABLE `a78`.`globalfiles` CHANGE playCount PlayCount1 INT;

ALTER TABLE `a78`.`globalfiles` CHANGE lastPlayed lastPlayed1 TEXT;

ALTER TABLE `a78`.`globalfiles` ADD playCount2 INT(11) AFTER lastPlayed1;

ALTER TABLE `a78`.`globalfiles` ADD lastPlayed2 TEXT AFTER playCount2;

ALTER TABLE `a78`.`globalfiles` ADD playCount3 INT(11) AFTER lastPlayed2;

ALTER TABLE `a78`.`globalfiles` ADD lastPlayed3 TEXT AFTER playCount3;

ALTER TABLE `a78`.`globalfiles` ADD playCount4 INT(11) AFTER lastPlayed3;

ALTER TABLE `a78`.`globalfiles` ADD lastPlayed4 TEXT AFTER playCount4;

ALTER TABLE `a78`.`globalfiles` ADD playCount5 INT(11) AFTER lastPlayed4;

ALTER TABLE `a78`.`globalfiles` ADD lastPlayed5 TEXT AFTER playCount5;

CREATE VIEW `a78`.`files`

AS SELECT

   `a78`.`globalfiles`.`idFile` AS `idFile`,

   `a78`.`globalfiles`.`idPath` AS `idPath`,

   `a78`.`globalfiles`.`strFilename` AS `strFilename`,

   `a78`.`globalfiles`.`playCount1` AS `playCount`,

   `a78`.`globalfiles`.`lastPlayed1` AS `lastPlayed`,

   `a78`.`globalfiles`.`dateAdded` AS `dateAdded`

FROM `a78`.`globalfiles`;

CREATE DATABASE b78;

CREATE VIEW `b78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;

CREATE VIEW `b78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;

CREATE VIEW `b78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;

CREATE VIEW `b78`.`actors` AS SELECT * FROM `a78`.`actors`;

CREATE VIEW `b78`.`art` AS SELECT * FROM `a78`.`art`;

CREATE VIEW `b78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;

CREATE VIEW `b78`.`country` AS SELECT * FROM `a78`.`country`;

CREATE VIEW `b78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;

CREATE VIEW `b78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;

CREATE VIEW `b78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;

CREATE VIEW `b78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;

CREATE VIEW `b78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;

CREATE VIEW `b78`.`episode` AS SELECT * FROM `a78`.`episode`;

CREATE VIEW `b78`.`genre` AS SELECT * FROM `a78`.`genre`;

CREATE VIEW `b78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;

CREATE VIEW `b78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;

CREATE VIEW `b78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;

CREATE VIEW `b78`.`movie` AS SELECT * FROM `a78`.`movie`;

CREATE VIEW `b78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;

CREATE VIEW `b78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;

CREATE VIEW `b78`.`path` AS SELECT * FROM `a78`.`path`;

CREATE VIEW `b78`.`seasons` AS SELECT * FROM `a78`.`seasons`;

CREATE VIEW `b78`.`sets` AS SELECT * FROM `a78`.`sets`;

CREATE VIEW `b78`.`settings` AS SELECT * FROM `a78`.`settings`;

CREATE VIEW `b78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;

CREATE VIEW `b78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;

CREATE VIEW `b78`.`studio` AS SELECT * FROM `a78`.`studio`;

CREATE VIEW `b78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;

CREATE VIEW `b78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;

CREATE VIEW `b78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;

CREATE VIEW `b78`.`tag` AS SELECT * FROM `a78`.`tag`;

CREATE VIEW `b78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;

CREATE VIEW `b78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;

CREATE VIEW `b78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;

CREATE VIEW `b78`.`version` AS SELECT * FROM `a78`.`version`;

CREATE VIEW `b78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;

CREATE VIEW `b78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;

CREATE VIEW `b78`.`files`

AS SELECT

   `a78`.`globalfiles`.`idFile` AS `idFile`,

   `a78`.`globalfiles`.`idPath` AS `idPath`,

   `a78`.`globalfiles`.`strFilename` AS `strFilename`,

   `a78`.`globalfiles`.`playCount2` AS `playCount`,

   `a78`.`globalfiles`.`lastPlayed2` AS `lastPlayed`,

   `a78`.`globalfiles`.`dateAdded` AS `dateAdded`

FROM `a78`.`globalfiles`;

CREATE TABLE `b78`.`bookmark` (

  `idBookmark` int(11) NOT NULL AUTO_INCREMENT,

  `idFile` int(11) DEFAULT NULL,

  `timeInSeconds` double DEFAULT NULL,

  `totalTimeInSeconds` double DEFAULT NULL,

  `thumbNailImage` text,

  `player` text,

  `playerState` text,

  `type` int(11) DEFAULT NULL,

  PRIMARY KEY (`idBookmark`),

  KEY `ix_bookmark` (`idFile`,`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW `b78`.`episodeview`

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`,

   `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`.`c14` AS `strStudio`,

   `tvshow`.`c05` AS `premiered`,

   `tvshow`.`c13` AS `mpaa`,

   `tvshow`.`c16` AS `strShowPath`,

   `b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,

   `seasons`.`idSeason` AS `idSeason`

FROM (((((`b78`.`episode` join `b78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `b78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `b78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `b78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));

CREATE VIEW `b78`.`movieview`

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`,

   `sets`.`strSet` AS `strSet`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM ((((`b78`.`movie` left join `b78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `b78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `b78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));

CREATE VIEW `b78`.`musicvideoview`

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`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `b78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `b78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM (((`b78`.`musicvideo` join `b78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `b78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `b78`.`bookmark` on(((`b78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`b78`.`bookmark`.`type` = 1))));

CREATE VIEW `b78`.`tvshowview`

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`,

   `path`.`strPath` AS `strPath`,

   `path`.`dateAdded` AS `dateAdded`,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`

FROM ((((`b78`.`tvshow` left join `b78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `b78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `b78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `b78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE DATABASE c78;

CREATE VIEW `c78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;

CREATE VIEW `c78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;

CREATE VIEW `c78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;

CREATE VIEW `c78`.`actors` AS SELECT * FROM `a78`.`actors`;

CREATE VIEW `c78`.`art` AS SELECT * FROM `a78`.`art`;

CREATE VIEW `c78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;

CREATE VIEW `c78`.`country` AS SELECT * FROM `a78`.`country`;

CREATE VIEW `c78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;

CREATE VIEW `c78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;

CREATE VIEW `c78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;

CREATE VIEW `c78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;

CREATE VIEW `c78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;

CREATE VIEW `c78`.`episode` AS SELECT * FROM `a78`.`episode`;

CREATE VIEW `c78`.`genre` AS SELECT * FROM `a78`.`genre`;

CREATE VIEW `c78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;

CREATE VIEW `c78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;

CREATE VIEW `c78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;

CREATE VIEW `c78`.`movie` AS SELECT * FROM `a78`.`movie`;

CREATE VIEW `c78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;

CREATE VIEW `c78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;

CREATE VIEW `c78`.`path` AS SELECT * FROM `a78`.`path`;

CREATE VIEW `c78`.`seasons` AS SELECT * FROM `a78`.`seasons`;

CREATE VIEW `c78`.`sets` AS SELECT * FROM `a78`.`sets`;

CREATE VIEW `c78`.`settings` AS SELECT * FROM `a78`.`settings`;

CREATE VIEW `c78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;

CREATE VIEW `c78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;

CREATE VIEW `c78`.`studio` AS SELECT * FROM `a78`.`studio`;

CREATE VIEW `c78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;

CREATE VIEW `c78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;

CREATE VIEW `c78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;

CREATE VIEW `c78`.`tag` AS SELECT * FROM `a78`.`tag`;

CREATE VIEW `c78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;

CREATE VIEW `c78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;

CREATE VIEW `c78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;

CREATE VIEW `c78`.`version` AS SELECT * FROM `a78`.`version`;

CREATE VIEW `c78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;

CREATE VIEW `c78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;

CREATE VIEW `c78`.`files`

AS SELECT

   `a78`.`globalfiles`.`idFile` AS `idFile`,

   `a78`.`globalfiles`.`idPath` AS `idPath`,

   `a78`.`globalfiles`.`strFilename` AS `strFilename`,

   `a78`.`globalfiles`.`playCount3` AS `playCount`,

   `a78`.`globalfiles`.`lastPlayed3` AS `lastPlayed`,

   `a78`.`globalfiles`.`dateAdded` AS `dateAdded`

FROM `a78`.`globalfiles`;

CREATE TABLE `c78`.`bookmark` (

  `idBookmark` int(11) NOT NULL AUTO_INCREMENT,

  `idFile` int(11) DEFAULT NULL,

  `timeInSeconds` double DEFAULT NULL,

  `totalTimeInSeconds` double DEFAULT NULL,

  `thumbNailImage` text,

  `player` text,

  `playerState` text,

  `type` int(11) DEFAULT NULL,

  PRIMARY KEY (`idBookmark`),

  KEY `ix_bookmark` (`idFile`,`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW `c78`.`episodeview`

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`,

   `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`.`c14` AS `strStudio`,

   `tvshow`.`c05` AS `premiered`,

   `tvshow`.`c13` AS `mpaa`,

   `tvshow`.`c16` AS `strShowPath`,

   `c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,

   `seasons`.`idSeason` AS `idSeason`

FROM (((((`c78`.`episode` join `c78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `c78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `c78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `c78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));

CREATE VIEW `c78`.`movieview`

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`,

   `sets`.`strSet` AS `strSet`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM ((((`c78`.`movie` left join `c78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `c78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `c78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));

CREATE VIEW `c78`.`musicvideoview`

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`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `c78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `c78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM (((`c78`.`musicvideo` join `c78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `c78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `c78`.`bookmark` on(((`c78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`c78`.`bookmark`.`type` = 1))));

CREATE VIEW `c78`.`tvshowview`

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`,

   `path`.`strPath` AS `strPath`,

   `path`.`dateAdded` AS `dateAdded`,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`

FROM ((((`c78`.`tvshow` left join `c78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `c78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `c78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `c78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE DATABASE d78;

CREATE VIEW `d78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;

CREATE VIEW `d78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;

CREATE VIEW `d78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;

CREATE VIEW `d78`.`actors` AS SELECT * FROM `a78`.`actors`;

CREATE VIEW `d78`.`art` AS SELECT * FROM `a78`.`art`;

CREATE VIEW `d78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;

CREATE VIEW `d78`.`country` AS SELECT * FROM `a78`.`country`;

CREATE VIEW `d78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;

CREATE VIEW `d78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;

CREATE VIEW `d78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;

CREATE VIEW `d78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;

CREATE VIEW `d78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;

CREATE VIEW `d78`.`episode` AS SELECT * FROM `a78`.`episode`;

CREATE VIEW `d78`.`genre` AS SELECT * FROM `a78`.`genre`;

CREATE VIEW `d78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;

CREATE VIEW `d78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;

CREATE VIEW `d78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;

CREATE VIEW `d78`.`movie` AS SELECT * FROM `a78`.`movie`;

CREATE VIEW `d78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;

CREATE VIEW `d78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;

CREATE VIEW `d78`.`path` AS SELECT * FROM `a78`.`path`;

CREATE VIEW `d78`.`seasons` AS SELECT * FROM `a78`.`seasons`;

CREATE VIEW `d78`.`sets` AS SELECT * FROM `a78`.`sets`;

CREATE VIEW `d78`.`settings` AS SELECT * FROM `a78`.`settings`;

CREATE VIEW `d78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;

CREATE VIEW `d78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;

CREATE VIEW `d78`.`studio` AS SELECT * FROM `a78`.`studio`;

CREATE VIEW `d78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;

CREATE VIEW `d78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;

CREATE VIEW `d78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;

CREATE VIEW `d78`.`tag` AS SELECT * FROM `a78`.`tag`;

CREATE VIEW `d78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;

CREATE VIEW `d78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;

CREATE VIEW `d78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;

CREATE VIEW `d78`.`version` AS SELECT * FROM `a78`.`version`;

CREATE VIEW `d78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;

CREATE VIEW `d78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;

CREATE VIEW `d78`.`files`

AS SELECT

   `a78`.`globalfiles`.`idFile` AS `idFile`,

   `a78`.`globalfiles`.`idPath` AS `idPath`,

   `a78`.`globalfiles`.`strFilename` AS `strFilename`,

   `a78`.`globalfiles`.`playCount4` AS `playCount`,

   `a78`.`globalfiles`.`lastPlayed4` AS `lastPlayed`,

   `a78`.`globalfiles`.`dateAdded` AS `dateAdded`

FROM `a78`.`globalfiles`;

CREATE TABLE `d78`.`bookmark` (

  `idBookmark` int(11) NOT NULL AUTO_INCREMENT,

  `idFile` int(11) DEFAULT NULL,

  `timeInSeconds` double DEFAULT NULL,

  `totalTimeInSeconds` double DEFAULT NULL,

  `thumbNailImage` text,

  `player` text,

  `playerState` text,

  `type` int(11) DEFAULT NULL,

  PRIMARY KEY (`idBookmark`),

  KEY `ix_bookmark` (`idFile`,`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW `d78`.`episodeview`

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`,

   `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`.`c14` AS `strStudio`,

   `tvshow`.`c05` AS `premiered`,

   `tvshow`.`c13` AS `mpaa`,

   `tvshow`.`c16` AS `strShowPath`,

   `d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,

   `seasons`.`idSeason` AS `idSeason`

FROM (((((`d78`.`episode` join `d78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `d78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `d78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `d78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));

CREATE VIEW `d78`.`movieview`

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`,

   `sets`.`strSet` AS `strSet`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM ((((`d78`.`movie` left join `d78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `d78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `d78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));

CREATE VIEW `d78`.`musicvideoview`

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`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `d78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `d78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM (((`d78`.`musicvideo` join `d78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `d78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `d78`.`bookmark` on(((`d78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`d78`.`bookmark`.`type` = 1))));

CREATE VIEW `d78`.`tvshowview`

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`,

   `path`.`strPath` AS `strPath`,

   `path`.`dateAdded` AS `dateAdded`,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`

FROM ((((`d78`.`tvshow` left join `d78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `d78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `d78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `d78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

CREATE DATABASE e78;

CREATE VIEW `e78`.`actorlinkepisode` AS SELECT * FROM `a78`.`actorlinkepisode`;

CREATE VIEW `e78`.`actorlinkmovie` AS SELECT * FROM `a78`.`actorlinkmovie`;

CREATE VIEW `e78`.`actorlinktvshow` AS SELECT * FROM `a78`.`actorlinktvshow`;

CREATE VIEW `e78`.`actors` AS SELECT * FROM `a78`.`actors`;

CREATE VIEW `e78`.`art` AS SELECT * FROM `a78`.`art`;

CREATE VIEW `e78`.`artistlinkmusicvideo` AS SELECT * FROM `a78`.`artistlinkmusicvideo`;

CREATE VIEW `e78`.`country` AS SELECT * FROM `a78`.`country`;

CREATE VIEW `e78`.`countrylinkmovie` AS SELECT * FROM `a78`.`countrylinkmovie`;

CREATE VIEW `e78`.`directorlinkepisode` AS SELECT * FROM `a78`.`directorlinkepisode`;

CREATE VIEW `e78`.`directorlinkmovie` AS SELECT * FROM `a78`.`directorlinkmovie`;

CREATE VIEW `e78`.`directorlinkmusicvideo` AS SELECT * FROM `a78`.`directorlinkmusicvideo`;

CREATE VIEW `e78`.`directorlinktvshow` AS SELECT * FROM `a78`.`directorlinktvshow`;

CREATE VIEW `e78`.`episode` AS SELECT * FROM `a78`.`episode`;

CREATE VIEW `e78`.`genre` AS SELECT * FROM `a78`.`genre`;

CREATE VIEW `e78`.`genrelinkmovie` AS SELECT * FROM `a78`.`genrelinkmovie`;

CREATE VIEW `e78`.`genrelinkmusicvideo` AS SELECT * FROM `a78`.`genrelinkmusicvideo`;

CREATE VIEW `e78`.`genrelinktvshow` AS SELECT * FROM `a78`.`genrelinktvshow`;

CREATE VIEW `e78`.`movie` AS SELECT * FROM `a78`.`movie`;

CREATE VIEW `e78`.`movielinktvshow` AS SELECT * FROM `a78`.`movielinktvshow`;

CREATE VIEW `e78`.`musicvideo` AS SELECT * FROM `a78`.`musicvideo`;

CREATE VIEW `e78`.`path` AS SELECT * FROM `a78`.`path`;

CREATE VIEW `e78`.`seasons` AS SELECT * FROM `a78`.`seasons`;

CREATE VIEW `e78`.`sets` AS SELECT * FROM `a78`.`sets`;

CREATE VIEW `e78`.`settings` AS SELECT * FROM `a78`.`settings`;

CREATE VIEW `e78`.`stacktimes` AS SELECT * FROM `a78`.`stacktimes`;

CREATE VIEW `e78`.`streamdetails` AS SELECT * FROM `a78`.`streamdetails`;

CREATE VIEW `e78`.`studio` AS SELECT * FROM `a78`.`studio`;

CREATE VIEW `e78`.`studiolinkmovie` AS SELECT * FROM `a78`.`studiolinkmovie`;

CREATE VIEW `e78`.`studiolinkmusicvideo` AS SELECT * FROM `a78`.`studiolinkmusicvideo`;

CREATE VIEW `e78`.`studiolinktvshow` AS SELECT * FROM `a78`.`studiolinktvshow`;

CREATE VIEW `e78`.`tag` AS SELECT * FROM `a78`.`tag`;

CREATE VIEW `e78`.`taglinks` AS SELECT * FROM `a78`.`taglinks`;

CREATE VIEW `e78`.`tvshow` AS SELECT * FROM `a78`.`tvshow`;

CREATE VIEW `e78`.`tvshowlinkpath` AS SELECT * FROM `a78`.`tvshowlinkpath`;

CREATE VIEW `e78`.`version` AS SELECT * FROM `a78`.`version`;

CREATE VIEW `e78`.`writerlinkepisode` AS SELECT * FROM `a78`.`writerlinkepisode`;

CREATE VIEW `e78`.`writerlinkmovie` AS SELECT * FROM `a78`.`writerlinkmovie`;

CREATE VIEW `e78`.`files`

AS SELECT

   `a78`.`globalfiles`.`idFile` AS `idFile`,

   `a78`.`globalfiles`.`idPath` AS `idPath`,

   `a78`.`globalfiles`.`strFilename` AS `strFilename`,

   `a78`.`globalfiles`.`playCount5` AS `playCount`,

   `a78`.`globalfiles`.`lastPlayed5` AS `lastPlayed`,

   `a78`.`globalfiles`.`dateAdded` AS `dateAdded`

FROM `a78`.`globalfiles`;

CREATE TABLE `e78`.`bookmark` (

  `idBookmark` int(11) NOT NULL AUTO_INCREMENT,

  `idFile` int(11) DEFAULT NULL,

  `timeInSeconds` double DEFAULT NULL,

  `totalTimeInSeconds` double DEFAULT NULL,

  `thumbNailImage` text,

  `player` text,

  `playerState` text,

  `type` int(11) DEFAULT NULL,

  PRIMARY KEY (`idBookmark`),

  KEY `ix_bookmark` (`idFile`,`type`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE VIEW `e78`.`episodeview`

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`,

   `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`.`c14` AS `strStudio`,

   `tvshow`.`c05` AS `premiered`,

   `tvshow`.`c13` AS `mpaa`,

   `tvshow`.`c16` AS `strShowPath`,

   `e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`,

   `seasons`.`idSeason` AS `idSeason`

FROM (((((`e78`.`episode` join `e78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) join `e78`.`tvshow` on((`tvshow`.`idShow` = `episode`.`idShow`))) left join `e78`.`seasons` on(((`seasons`.`idShow` = `episode`.`idShow`) and (`seasons`.`season` = `episode`.`c12`)))) join `e78`.`path` on((`files`.`idPath` = `path`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `episode`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));

CREATE VIEW `e78`.`movieview`

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`,

   `sets`.`strSet` AS `strSet`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM ((((`e78`.`movie` left join `e78`.`sets` on((`sets`.`idSet` = `movie`.`idSet`))) join `e78`.`files` on((`files`.`idFile` = `movie`.`idFile`))) join `e78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `movie`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));

CREATE VIEW `e78`.`musicvideoview`

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`,

   `files`.`strFilename` AS `strFileName`,

   `path`.`strPath` AS `strPath`,

   `files`.`playCount` AS `playCount`,

   `files`.`lastPlayed` AS `lastPlayed`,

   `files`.`dateAdded` AS `dateAdded`,

   `e78`.`bookmark`.`timeInSeconds` AS `resumeTimeInSeconds`,

   `e78`.`bookmark`.`totalTimeInSeconds` AS `totalTimeInSeconds`

FROM (((`e78`.`musicvideo` join `e78`.`files` on((`files`.`idFile` = `musicvideo`.`idFile`))) join `e78`.`path` on((`path`.`idPath` = `files`.`idPath`))) left join `e78`.`bookmark` on(((`e78`.`bookmark`.`idFile` = `musicvideo`.`idFile`) and (`e78`.`bookmark`.`type` = 1))));

CREATE VIEW `e78`.`tvshowview`

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`,

   `path`.`strPath` AS `strPath`,

   `path`.`dateAdded` AS `dateAdded`,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`

FROM ((((`e78`.`tvshow` left join `e78`.`tvshowlinkpath` on((`tvshowlinkpath`.`idShow` = `tvshow`.`idShow`))) left join `e78`.`path` on((`path`.`idPath` = `tvshowlinkpath`.`idPath`))) left join `e78`.`episode` on((`episode`.`idShow` = `tvshow`.`idShow`))) left join `e78`.`files` on((`files`.`idFile` = `episode`.`idFile`))) group by `tvshow`.`idShow`;

Now that were done with the SQL side of things, you just need to create 4 extra profiles via XBMC and give each of them an advancedsettings.xml file.

User 2 (b78)

Code:
<advancedsettings>

  <videodatabase>

    <type>mysql</type>

    <host>IPADDRESS</host>

    <port>3306</port>

    <user>USERNAME</user>

    <pass>PASSWORD</pass>

    <name>b</name>

  </videodatabase>

</advancedsettings>

User 3 (c78)

Code:
<advancedsettings>

  <videodatabase>

    <type>mysql</type>

    <host>IPADDRESS</host>

    <port>3306</port>

    <user>USERNAME</user>

    <pass>PASSWORD</pass>

    <name>c</name>

  </videodatabase>

</advancedsettings>

User 4 (d78)

Code:
<advancedsettings>

  <videodatabase>

    <type>mysql</type>

    <host>IPADDRESS</host>

    <port>3306</port>

    <user>USERNAME</user>

    <pass>PASSWORD</pass>

    <name>d</name>

  </videodatabase>

</advancedsettings>

User 5 (e78)

Code:
<advancedsettings>

  <videodatabase>

    <type>mysql</type>

    <host>IPADDRESS</host>

    <port>3306</port>

    <user>USERNAME</user>

    <pass>PASSWORD</pass>

    <name>e</name>

  </videodatabase>

</advancedsettings>

You can choose to run 2-5 profiles with this setup, the choice is yours.

I am planning to put together a tutorial on how to setup a "Master XBMC Virtual Machine" on Ubuntu 14.04 LTS Desktop with XBMC 13, MySQL and Squid. If thats something your interested in PM me and it might happen faster.

If this has helped you feel free to rep+

sompie · 2014-05-11, 15:47

I've remade my database as deathraiider but xbmc won't read the watched status from the extra users (i've reimported data from the 75 database in the globalvideo table)