Login at Kodi Home

je55eg · (This post was last modified: 2012-09-06, 19:19 by je55eg.)

I'm trying to get this to work on v11.0, unfortunately I keep getting the following error message in the XBMC log file:

Code:
T:3480   ERROR: SQL: The table does not exist

                                            Query: SELECT idVersion FROM version

I'm trying to use the "view" method. I've tried to take into account the fact that whatever DB is specified in the XML file XBMC automatically appends a 60 to it.

here is my advancesettings.xml:

Code:
<advancedsettings>

<videodatabase>

        <type>mysql</type>

        <host>192.168.1.200</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

        <name>xbmc_video1</name>

    </videodatabase> 

    <musicdatabase>

        <type>mysql</type>

        <host>192.168.1.200</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

        <name>xbmc_music</name>

    </musicdatabase>

</advancedsettings>

Here are the commands I'm running in mysql:

Code:
CREATE database xbmc_video160;

CREATE database xbmc_music160;

GRANT ALL ON *.* TO 'xbmc';

-------------------------

Code:
USE xbmc_video160;

delimiter $$

CREATE TABLE `watched_history` (

`idFile` int(11) NOT NULL,

`playCount` int(11) DEFAULT NULL,

`lastPlayed` text,

PRIMARY KEY (`idFile`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

----------------------

Code:
use `xbmc_video160`;

DROP TABLE IF EXISTS `files`;

delimiter $$

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` 

from (`xbmc_video60`.`files` `f` join `xbmc_video160`.`watched_history` `wh` on((`f`.`idFile` = `wh`.`idFile`))) $$

-------------------------------

DLow · (This post was last modified: 2012-11-14, 14:44 by DLow.)

I just wanted to share my MYSQL Database Structure, cause its working pretty well. I created a new Table, which is a copy of the "files" Table, but this Table has 2 more columns for the second user. I create a lot of new Views, for usage through XBMC

I have to Put it into two posts because else it would be too long Rofl

This is just the whole query, you can put it into your PhpMyAdmin or whatever administration tool you use.

Hope it helps, greetings

DLow

First Part:

Code:
RENAME TABLE  `xbmc-database-a60`.`files` TO  `xbmc-database-a60`.`globalfiles` ;

ALTER TABLE  `xbmc-database-a60`.`globalfiles` ADD  `playCount2` INT( 11 ) NULL DEFAULT NULL ,ADD  `lastPlayed2` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

ALTER TABLE  `xbmc-database-a60`.`globalfiles` CHANGE  `playCount`  `playCount1` INT( 11 ) NULL DEFAULT NULL;

ALTER TABLE  `xbmc-database-a60`.`globalfiles` CHANGE  `lastPlayed`  `lastPlayed1` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

CREATE DATABASE  `xbmc-database-b60` ;

CREATE VIEW `xbmc-database-a60`.`files` AS

SELECT idFile, idPath, strFilename, playCount1 as playCount, lastPlayed1 as lastPlayed FROM `xbmc-database-a60`.`globalfiles`;

CREATE VIEW `xbmc-database-b60`.`files` AS

SELECT idFile, idPath, strFilename, playCount2 as playCount, lastPlayed2 as lastPlayed FROM `xbmc-database-a60`.`globalfiles`;

CREATE VIEW `xbmc-database-b60`.`actorlinkepisode` AS

SELECT * FROM `xbmc-database-a60`.`actorlinkepisode`;

CREATE VIEW `xbmc-database-b60`.`actorlinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`actorlinkmovie`;

CREATE VIEW `xbmc-database-b60`.`actorlinktvshow` AS

SELECT * FROM `xbmc-database-a60`.`actorlinktvshow`;

CREATE VIEW `xbmc-database-b60`.`actors` AS

SELECT * FROM `xbmc-database-a60`.`actors`;

CREATE VIEW `xbmc-database-b60`.`artistlinkmusicvideo` AS

SELECT * FROM `xbmc-database-a60`.`artistlinkmusicvideo`;

CREATE VIEW `xbmc-database-b60`.`bookmark` AS

SELECT * FROM `xbmc-database-a60`.`bookmark`;

CREATE VIEW `xbmc-database-b60`.`country` AS

SELECT * FROM `xbmc-database-a60`.`country`;

CREATE VIEW `xbmc-database-b60`.`countrylinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`countrylinkmovie`;

CREATE VIEW `xbmc-database-b60`.`directorlinkepisode` AS

SELECT * FROM `xbmc-database-a60`.`directorlinkepisode`;

CREATE VIEW `xbmc-database-b60`.`directorlinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`directorlinkmovie`;

CREATE VIEW `xbmc-database-b60`.`directorlinkmusicvideo` AS

SELECT * FROM `xbmc-database-a60`.`directorlinkmusicvideo`;

CREATE VIEW `xbmc-database-b60`.`directorlinktvshow` AS

SELECT * FROM `xbmc-database-a60`.`directorlinktvshow`;

CREATE VIEW `xbmc-database-b60`.`episode` AS

SELECT * FROM `xbmc-database-a60`.`episode`;

CREATE VIEW `xbmc-database-b60`.`writerlinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`writerlinkmovie`;

CREATE VIEW `xbmc-database-b60`.`genre` AS

SELECT * FROM `xbmc-database-a60`.`genre`;

CREATE VIEW `xbmc-database-b60`.`genrelinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`genrelinkmovie`;

CREATE VIEW `xbmc-database-b60`.`genrelinkmusicvideo` AS

SELECT * FROM `xbmc-database-a60`.`genrelinkmusicvideo`;

CREATE VIEW `xbmc-database-b60`.`genrelinktvshow` AS

SELECT * FROM `xbmc-database-a60`.`genrelinktvshow`;

CREATE VIEW `xbmc-database-b60`.`movie` AS

SELECT * FROM `xbmc-database-a60`.`movie`;

CREATE VIEW `xbmc-database-b60`.`movielinktvshow` AS

SELECT * FROM `xbmc-database-a60`.`movielinktvshow`;

CREATE VIEW `xbmc-database-b60`.`musicvideo` AS

SELECT * FROM `xbmc-database-a60`.`musicvideo`;

CREATE VIEW `xbmc-database-b60`.`path` AS

SELECT * FROM `xbmc-database-a60`.`path`;

CREATE VIEW `xbmc-database-b60`.`setlinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`setlinkmovie`;

CREATE VIEW `xbmc-database-b60`.`sets` AS

SELECT * FROM `xbmc-database-a60`.`sets`;

CREATE VIEW `xbmc-database-b60`.`settings` AS

SELECT * FROM `xbmc-database-a60`.`settings`;

CREATE VIEW `xbmc-database-b60`.`stacktimes` AS

SELECT * FROM `xbmc-database-a60`.`stacktimes`;

CREATE VIEW `xbmc-database-b60`.`streamdetails` AS

SELECT * FROM `xbmc-database-a60`.`streamdetails`;

CREATE VIEW `xbmc-database-b60`.`studio` AS

SELECT * FROM `xbmc-database-a60`.`studio`;

CREATE VIEW `xbmc-database-b60`.`studiolinkmovie` AS

SELECT * FROM `xbmc-database-a60`.`studiolinkmovie`;

CREATE VIEW `xbmc-database-b60`.`studiolinkmusicvideo` AS

SELECT * FROM `xbmc-database-a60`.`studiolinkmusicvideo`;

CREATE VIEW `xbmc-database-b60`.`studiolinktvshow` AS

SELECT * FROM `xbmc-database-a60`.`studiolinktvshow`;

CREATE VIEW `xbmc-database-b60`.`tvshow` AS

SELECT * FROM `xbmc-database-a60`.`tvshow`;

CREATE VIEW `xbmc-database-b60`.`tvshowlinkepisode` AS

SELECT * FROM `xbmc-database-a60`.`tvshowlinkepisode`;

CREATE VIEW `xbmc-database-b60`.`tvshowlinkpath` AS

SELECT * FROM `xbmc-database-a60`.`tvshowlinkpath`;

CREATE VIEW `xbmc-database-b60`.`version` AS

SELECT * FROM `xbmc-database-a60`.`version`;

CREATE VIEW `xbmc-database-b60`.`writerlinkepisode` AS

SELECT * FROM `xbmc-database-a60`.`writerlinkepisode`;

Second Part:

Code:
CREATE VIEW `xbmc-database-b60`.`episodeview` AS select `xbmc-database-b60`.`episode`.`idEpisode` AS `idEpisode`,`xbmc-database-b60`.`episode`.`idFile` AS `idFile`,`xbmc-database-b60`.`episode`.`c00` AS `c00`,`xbmc-database-b60`.`episode`.`c01` AS `c01`,`xbmc-database-b60`.`episode`.`c02` AS `c02`,`xbmc-database-b60`.`episode`.`c03` AS `c03`,`xbmc-database-b60`.`episode`.`c04` AS `c04`,`xbmc-database-b60`.`episode`.`c05` AS `c05`,`xbmc-database-b60`.`episode`.`c06` AS `c06`,`xbmc-database-b60`.`episode`.`c07` AS `c07`,`xbmc-database-b60`.`episode`.`c08` AS `c08`,`xbmc-database-b60`.`episode`.`c09` AS `c09`,`xbmc-database-b60`.`episode`.`c10` AS `c10`,`xbmc-database-b60`.`episode`.`c11` AS `c11`,`xbmc-database-b60`.`episode`.`c12` AS `c12`,`xbmc-database-b60`.`episode`.`c13` AS `c13`,`xbmc-database-b60`.`episode`.`c14` AS `c14`,`xbmc-database-b60`.`episode`.`c15` AS `c15`,`xbmc-database-b60`.`episode`.`c16` AS `c16`,`xbmc-database-b60`.`episode`.`c17` AS `c17`,`xbmc-database-b60`.`episode`.`c18` AS `c18`,`xbmc-database-b60`.`episode`.`c19` AS `c19`,`xbmc-database-b60`.`episode`.`c20` AS `c20`,`xbmc-database-b60`.`episode`.`c21` AS `c21`,`xbmc-database-b60`.`episode`.`c22` AS `c22`,`xbmc-database-b60`.`episode`.`c23` AS `c23`,`xbmc-database-b60`.`files`.`strFilename` AS `strFileName`,`xbmc-database-b60`.`path`.`strPath` AS `strPath`,`xbmc-database-b60`.`files`.`playCount` AS `playCount`,`xbmc-database-b60`.`files`.`lastPlayed` AS `lastPlayed`,`xbmc-database-b60`.`tvshow`.`c00` AS `strTitle`,`xbmc-database-b60`.`tvshow`.`c14` AS `strStudio`,`xbmc-database-b60`.`tvshow`.`idShow` AS `idShow`,`xbmc-database-b60`.`tvshow`.`c05` AS `premiered`,`xbmc-database-b60`.`tvshow`.`c13` AS `mpaa`,`xbmc-database-b60`.`tvshow`.`c16` AS `strShowPath` from ((((`xbmc-database-b60`.`episode` join `xbmc-database-b60`.`files` on((`xbmc-database-b60`.`files`.`idFile` = `xbmc-database-b60`.`episode`.`idFile`))) join `xbmc-database-b60`.`tvshowlinkepisode` on((`xbmc-database-b60`.`episode`.`idEpisode` = `xbmc-database-b60`.`tvshowlinkepisode`.`idEpisode`))) join `xbmc-database-b60`.`tvshow` on((`xbmc-database-b60`.`tvshow`.`idShow` = `xbmc-database-b60`.`tvshowlinkepisode`.`idShow`))) join `xbmc-database-b60`.`path` on((`xbmc-database-b60`.`files`.`idPath` = `xbmc-database-b60`.`path`.`idPath`)));

CREATE VIEW `xbmc-database-b60`.`movieview` AS select `xbmc-database-b60`.`movie`.`idMovie` AS `idMovie`,`xbmc-database-b60`.`movie`.`idFile` AS `idFile`,`xbmc-database-b60`.`movie`.`c00` AS `c00`,`xbmc-database-b60`.`movie`.`c01` AS `c01`,`xbmc-database-b60`.`movie`.`c02` AS `c02`,`xbmc-database-b60`.`movie`.`c03` AS `c03`,`xbmc-database-b60`.`movie`.`c04` AS `c04`,`xbmc-database-b60`.`movie`.`c05` AS `c05`,`xbmc-database-b60`.`movie`.`c06` AS `c06`,`xbmc-database-b60`.`movie`.`c07` AS `c07`,`xbmc-database-b60`.`movie`.`c08` AS `c08`,`xbmc-database-b60`.`movie`.`c09` AS `c09`,`xbmc-database-b60`.`movie`.`c10` AS `c10`,`xbmc-database-b60`.`movie`.`c11` AS `c11`,`xbmc-database-b60`.`movie`.`c12` AS `c12`,`xbmc-database-b60`.`movie`.`c13` AS `c13`,`xbmc-database-b60`.`movie`.`c14` AS `c14`,`xbmc-database-b60`.`movie`.`c15` AS `c15`,`xbmc-database-b60`.`movie`.`c16` AS `c16`,`xbmc-database-b60`.`movie`.`c17` AS `c17`,`xbmc-database-b60`.`movie`.`c18` AS `c18`,`xbmc-database-b60`.`movie`.`c19` AS `c19`,`xbmc-database-b60`.`movie`.`c20` AS `c20`,`xbmc-database-b60`.`movie`.`c21` AS `c21`,`xbmc-database-b60`.`movie`.`c22` AS `c22`,`xbmc-database-b60`.`movie`.`c23` AS `c23`,`xbmc-database-b60`.`files`.`strFilename` AS `strFileName`,`xbmc-database-b60`.`path`.`strPath` AS `strPath`,`xbmc-database-b60`.`files`.`playCount` AS `playCount`,`xbmc-database-b60`.`files`.`lastPlayed` AS `lastPlayed` from ((`xbmc-database-b60`.`movie` join `xbmc-database-b60`.`files` on((`xbmc-database-b60`.`files`.`idFile` = `xbmc-database-b60`.`movie`.`idFile`))) join `xbmc-database-b60`.`path` on((`xbmc-database-b60`.`path`.`idPath` = `xbmc-database-b60`.`files`.`idPath`)));

CREATE VIEW `xbmc-database-b60`.`musicvideoview` AS select `xbmc-database-b60`.`musicvideo`.`idMVideo` AS `idMVideo`,`xbmc-database-b60`.`musicvideo`.`idFile` AS `idFile`,`xbmc-database-b60`.`musicvideo`.`c00` AS `c00`,`xbmc-database-b60`.`musicvideo`.`c01` AS `c01`,`xbmc-database-b60`.`musicvideo`.`c02` AS `c02`,`xbmc-database-b60`.`musicvideo`.`c03` AS `c03`,`xbmc-database-b60`.`musicvideo`.`c04` AS `c04`,`xbmc-database-b60`.`musicvideo`.`c05` AS `c05`,`xbmc-database-b60`.`musicvideo`.`c06` AS `c06`,`xbmc-database-b60`.`musicvideo`.`c07` AS `c07`,`xbmc-database-b60`.`musicvideo`.`c08` AS `c08`,`xbmc-database-b60`.`musicvideo`.`c09` AS `c09`,`xbmc-database-b60`.`musicvideo`.`c10` AS `c10`,`xbmc-database-b60`.`musicvideo`.`c11` AS `c11`,`xbmc-database-b60`.`musicvideo`.`c12` AS `c12`,`xbmc-database-b60`.`musicvideo`.`c13` AS `c13`,`xbmc-database-b60`.`musicvideo`.`c14` AS `c14`,`xbmc-database-b60`.`musicvideo`.`c15` AS `c15`,`xbmc-database-b60`.`musicvideo`.`c16` AS `c16`,`xbmc-database-b60`.`musicvideo`.`c17` AS `c17`,`xbmc-database-b60`.`musicvideo`.`c18` AS `c18`,`xbmc-database-b60`.`musicvideo`.`c19` AS `c19`,`xbmc-database-b60`.`musicvideo`.`c20` AS `c20`,`xbmc-database-b60`.`musicvideo`.`c21` AS `c21`,`xbmc-database-b60`.`musicvideo`.`c22` AS `c22`,`xbmc-database-b60`.`musicvideo`.`c23` AS `c23`,`xbmc-database-b60`.`files`.`strFilename` AS `strFileName`,`xbmc-database-b60`.`path`.`strPath` AS `strPath`,`xbmc-database-b60`.`files`.`playCount` AS `playCount`,`xbmc-database-b60`.`files`.`lastPlayed` AS `lastPlayed` from ((`xbmc-database-b60`.`musicvideo` join `xbmc-database-b60`.`files` on((`xbmc-database-b60`.`files`.`idFile` = `xbmc-database-b60`.`musicvideo`.`idFile`))) join `xbmc-database-b60`.`path` on((`xbmc-database-b60`.`path`.`idPath` = `xbmc-database-b60`.`files`.`idPath`)));

CREATE VIEW `xbmc-database-b60`.`tvshowview` AS select `xbmc-database-b60`.`tvshow`.`idShow` AS `idShow`,`xbmc-database-b60`.`tvshow`.`c00` AS `c00`,`xbmc-database-b60`.`tvshow`.`c01` AS `c01`,`xbmc-database-b60`.`tvshow`.`c02` AS `c02`,`xbmc-database-b60`.`tvshow`.`c03` AS `c03`,`xbmc-database-b60`.`tvshow`.`c04` AS `c04`,`xbmc-database-b60`.`tvshow`.`c05` AS `c05`,`xbmc-database-b60`.`tvshow`.`c06` AS `c06`,`xbmc-database-b60`.`tvshow`.`c07` AS `c07`,`xbmc-database-b60`.`tvshow`.`c08` AS `c08`,`xbmc-database-b60`.`tvshow`.`c09` AS `c09`,`xbmc-database-b60`.`tvshow`.`c10` AS `c10`,`xbmc-database-b60`.`tvshow`.`c11` AS `c11`,`xbmc-database-b60`.`tvshow`.`c12` AS `c12`,`xbmc-database-b60`.`tvshow`.`c13` AS `c13`,`xbmc-database-b60`.`tvshow`.`c14` AS `c14`,`xbmc-database-b60`.`tvshow`.`c15` AS `c15`,`xbmc-database-b60`.`tvshow`.`c16` AS `c16`,`xbmc-database-b60`.`tvshow`.`c17` AS `c17`,`xbmc-database-b60`.`tvshow`.`c18` AS `c18`,`xbmc-database-b60`.`tvshow`.`c19` AS `c19`,`xbmc-database-b60`.`tvshow`.`c20` AS `c20`,`xbmc-database-b60`.`tvshow`.`c21` AS `c21`,`xbmc-database-b60`.`tvshow`.`c22` AS `c22`,`xbmc-database-b60`.`tvshow`.`c23` AS `c23`,`xbmc-database-b60`.`path`.`strPath` AS `strPath`,nullif(count(`xbmc-database-b60`.`episode`.`c12`),0) AS `totalCount`,count(`xbmc-database-b60`.`files`.`playCount`) AS `watchedcount`,nullif(count(distinct `xbmc-database-b60`.`episode`.`c12`),0) AS `totalSeasons` from (((((`xbmc-database-b60`.`tvshow` left join `xbmc-database-b60`.`tvshowlinkpath` on((`xbmc-database-b60`.`tvshowlinkpath`.`idShow` = `xbmc-database-b60`.`tvshow`.`idShow`))) left join `xbmc-database-b60`.`path` on((`xbmc-database-b60`.`path`.`idPath` = `xbmc-database-b60`.`tvshowlinkpath`.`idPath`))) left join `xbmc-database-b60`.`tvshowlinkepisode` on((`xbmc-database-b60`.`tvshowlinkepisode`.`idShow` = `xbmc-database-b60`.`tvshow`.`idShow`))) left join `xbmc-database-b60`.`episode` on((`xbmc-database-b60`.`episode`.`idEpisode` = `xbmc-database-b60`.`tvshowlinkepisode`.`idEpisode`))) left join `xbmc-database-b60`.`files` on((`xbmc-database-b60`.`files`.`idFile` = `xbmc-database-b60`.`episode`.`idFile`))) group by `xbmc-database-b60`.`tvshow`.`idShow`;

[/code]

timmyj9 · 2012-12-12, 08:56

DLow, thanks for sharing this

i've adapted your SQL code to Frodo-RC1 (and 5 users) for my home environment

hopefully allowing me to administer the master database, i.e. update the files
which will also update the files shown by all the other users
with all individual users having their own independent bookmarks, and watched status but being shown the same media across the board

if anyone would like this script it can be arranged

Hack_kid · 2012-12-12, 09:22

i would like it i want to do exactly this. but a little rusty with the mysql i'm using the frodo rc1 as well

timmyj9 · (This post was last modified: 2012-12-12, 09:49 by timmyj9.)

ok it's not 100% tested as of yet
and if you're using xbmc nightlies when the database schema gets updated you are likely to run into issues when xbmc tries to update the database version on these 'slave' databases
my sql isn't great either but i spent a bit of time trying to get this to work as best i could
so definitely back up your dbs before you proceed

the unique information between the slave databases is the pause/resume time and watched status

http://pastebin.com/1fp0huRe

i guess if it's not immediately obvious the MyVideos75 is the master database and the UserVideos75 an example of the slave

Hack_kid · 2012-12-13, 02:08

how do i set it up for 4 users it seems to only be set up for the master user and one other user

timmyj9 · 2012-12-14, 02:15

heres an example of a 2nd slave database being added
you just need to increment the playcount/lastplayed and change the correct db references
copy and paste from /* User2 */ onwards for each subsequent user
i've had no issues with it so far - seems to be working for me

as shown here

Hack_kid · 2012-12-15, 21:11

ok and how do i run this script? i'm assuming in the mysql admin command line program (iforget whats its called)

timmyj9 · (This post was last modified: 2012-12-21, 03:26 by timmyj9.)

best way is just through phpmyadmin

gives you a gui and you can just copy & paste into the SQL tab

Hack_kid · 2012-12-23, 23:41

gave this a go it seems to working great so far. i noticed a section in it with a comment that said works fine to here need to debug the rest. what was this refuring to? hve you had any problems with it? and finally its a complete copy of the master db just with its own watched tables? like it just has symbolic links for all the other tables that are identical?

timmyj9 · (This post was last modified: 2012-12-24, 02:49 by timmyj9.)

you can ignore it, i was having issues with all the stuff after that comment but its fixed in the version i posted for you
been using it for a while now also with no issues to speak of

Hack_kid · 2012-12-24, 11:37

ok one last question. for the name tag in the advancedsettings.xml i put User1Videos75 but every time xbmc connects to mysql it creates a new db called User1Videos7575. Even though is happening it still seems to be working. whats going on? is xbmc automaticaly appending the version number to the db? and if so should i even worry about it? what it seems like its doing is reading User1Videos75 and createing a replica of that with the 75 appended to it and is using that db instead? is this what is happening/ is this happening on your system? and just to recap only problem i will run into is when they do a db update right? as my master xbmc will make a mew MyVideosXX which no longer has the modificatioons by you as well as the uservideos dbs pointing to the old db? would i just have to run your script again? or do the database schemas change so much from version to version it would have to be re done

nephalim3 · 2012-12-24, 23:32

Xbmc automatically adds the database version (75 in this case) to the end of your set database name. You need to set the database name in advanced settings.XML to user1videos not user1videos75. This a feature of the database update feature as far as I can tell. While i'm sure it looks good now i doubt changes to the master database will be propagated to the new database. Every time xbmc updates the database with new changes I think you will need to rerun the script for the new database. I could be wrong however because I have not used the script yet. I'm going to try this later today.

Hack_kid · 2012-12-25, 02:30

Ya I know I'll have to re run it I guess I meant will the script be compatible with newer dB's

nephalim3 · 2012-12-25, 03:02

It depends what they change in the new versions. If they change any of the columns referenced in the script then a new script will need to be made