[N4TH4N]

THIS TUTORIAL IS CONSIDERED ADVANCED

BACKUP YOUR XBMC USERDATA FOLDER AND MYSQL DATABASES












Do you have multiple XBMC clients and want an efficient but simple solution to keep track of watched status and bookmarks for multiple profiles.

Do you want to scan once for all of your XBMC clients and profiles.

Then this tutorial is for you.





If you have any questions or problems please reply to this thread.

PM's will not be replied to as they are not useful for the community.

If you use these instructions please post a reply telling us how it went.


*** The Krypton / v17 guide can be found at Post #234. ***


[b]Contents

1. Overview
2. Prerequisites
3. How
- a. Configure XBMC to create a master database
- b. Configure MySQL with 4 slave databases
- c. Configure XBMC clients
4. Upcoming Features
5. Bugs




1. Overview

This tutorial will guide you through the process of creating 5 XBMC profiles which are all linked.

Each profile has its own watched status and bookmarks.

XBMC by itself is good for managing multiple profiles on a single client.

XBMC with MySQL is good at managing a single profile on multiple clients.

XBMC with MySQL and the following setup is good for managing multiple profiles on multiple clients.

In my setup i have 6 clients with 5 databases. If i was to scan for each profile on every machine i would have to scan a total of 14 times.

With this simple SQL magic i only need to scan once.

It also gives me the added benefit of being able to pause a video in one room and continue watching in another.

If i'm watching a season in one room i can go to another and continue where i was up to.

But the main benefit of this setup is that me and my housemates can be watching the same videos whilst keeping separate watched status and bookmarks.

This setup now supports video and music.

For more info on my setup look at my showcase thread.





2. Prerequisites

- XBMC 13, 13.1, 13.2
- MySQL

Check out the 5 User XBMC - Master Server tutorial here...

NOTE: ALL XBMC CLIENTS MUST BE THE SAME VERSION

If you need extra help installing MySQL or wish to use a operating system other then "Ubuntu 12.04 Desktop" please checkout the official wiki.





3. How

NOTE: If you have a database already stored in MySQL you will first need to rename it to 'a78' then skip step 3a.



3a. Configure XBMC to create a master database



Windows

Download 5 User XBMC - Server Setup 1.2.1 for Windows

If your having problems downloading the file, copy any paste this code into notepad and save as xbmc-server-setup.bat

Right click "Run as administrator"





3b. Configure MySQL with 4 slave databases

Using your favourite SQL client (Terminal, phpMyAdmin or my personal favourite Sequel Pro) run the following query.

Download 5 User XBMC - SQL Setup 1.2

If your having problems downloading the file, copy any paste this code directly into your SQL client.





3c. Configure XBMC clients



NOTE: You may leave extra users blank so they are not used on a certain client.

Users can be in any order as long as the name matches the database.



Windows

Download 5 User XBMC - Client Setup 1.3 for Windows

If your having problems downloading the file, copy any paste this code into notepad and save as xbmc-client-setup.bat

Right click "Run as administrator"





5. Bugs

Please report any bugs you find.

[Ned Scott]

Very awesome! Thanks for posting this.

[schumi2004]

Don't you have issues using named databases in advancedsettings.xml?

/edit
I have read and experienced it myself that when using named databases it can give some issues.
Therefor I'm using this method which also works and only has a single database.

[N4TH4N]

Hey, I have not experienced any problems with using named databases. Since my databases each have their own bookmarks table there is no need for different SQL logins or any other workaround. I use the single login for every client/profile. Just with the different databases. Also no triggers are necessary.

Previous to using a78, b78, etc on 13 I had been using a75, b75 etc on 12.3 all the way back to a60, b60 which was version 11 i think with no problems.

Its very easy to do an upgrade from a75, a78 for example. So each time i upgrade versions its a pinch.

[schumi2004]

Hey, I have not experienced any problems with using named databases. Since my databases each have their own bookmarks table there is no need for different SQL logins or any other workaround. I use the single login for every client/profile. Just with the different databases. Also no triggers are necessary.

Previous to using a78, b78, etc on 13 I had been using a75, b75 etc on 12.3 all the way back to a60, b60 which was version 11 i think with no problems.

Its very easy to do an upgrade from a75, a78 for example. So each time i upgrade versions its a pinch.

Okay, I'll give it a try. Master profile uses name A I presume?

[N4TH4N]

Okay, I'll give it a try. Master profile uses name A I presume?

Yes, master is a78 which uses the name a.

Code:

<advancedsettings>
  <videodatabase>
    <type>mysql</type>
    <host>IPADDRESS</host>
    <port>3306</port>
    <user>USERNAME</user>
    <pass>PASSWORD</pass>
    <name>a</name>
  </videodatabase>
</advancedsettings>

Although you can scan in from any user. Master just refers to the SQL structure where all data is stored except for the bookmarks table.

[schumi2004]

Great

Any plans to implement such for music?

So far it all seems to work

[N4TH4N]

Any plans to implement such for music?

I will look into it. I use Spotify as my only music source so ill round up some mp3's and get testing. If it looks easy ill probably just do it straight away. If it looks difficult i'll save it till i have some free time.

[N4TH4N]

UPDATE: I have made an automated client setup script that takes care of everything client side.

[schumi2004]

UPDATE: I have made an automated client setup script that takes care of everything client side.

Looks good but for Linux/OpenELEC users it will fail I guess, Windows only script?

[N4TH4N]

Looks good but for Linux/OpenELEC users it will fail I guess, Windows only script?

Its a .bat file that sets up various folders and files.

I do plan to eventually support linux and mac.

Also, i have your music database ready for testing.

Code:

RENAME TABLE `a46`.`song` to `a46`.`globalsong`;

ALTER TABLE `a46`.`globalsong` CHANGE iTimesPlayed iTimesPlayed1 INT;
ALTER TABLE `a46`.`globalsong` ADD iTimesPlayed2 INT(11) AFTER iTimesPlayed1;
ALTER TABLE `a46`.`globalsong` ADD iTimesPlayed3 INT(11) AFTER iTimesPlayed2;
ALTER TABLE `a46`.`globalsong` ADD iTimesPlayed4 INT(11) AFTER iTimesPlayed3;
ALTER TABLE `a46`.`globalsong` ADD iTimesPlayed5 INT(11) AFTER iTimesPlayed4;
ALTER TABLE `a46`.`globalsong` CHANGE lastPlayed lastPlayed1 VARCHAR(20);
ALTER TABLE `a46`.`globalsong` ADD lastPlayed2 VARCHAR(20) AFTER lastPlayed1;
ALTER TABLE `a46`.`globalsong` ADD lastPlayed3 VARCHAR(20) AFTER lastPlayed2;
ALTER TABLE `a46`.`globalsong` ADD lastPlayed4 VARCHAR(20) AFTER lastPlayed3;
ALTER TABLE `a46`.`globalsong` ADD lastPlayed5 VARCHAR(20) AFTER lastPlayed4;

CREATE VIEW `a46`.`song`
AS SELECT
   `a46`.`globalsong`.`idSong` AS `idSong`,
   `a46`.`globalsong`.`idAlbum` AS `idAlbum`,
   `a46`.`globalsong`.`idPath` AS `idPath`,
   `a46`.`globalsong`.`strArtists` AS `strArtists`,
   `a46`.`globalsong`.`strGenres` AS `strGenres`,
   `a46`.`globalsong`.`strTitle` AS `strTitle`,
   `a46`.`globalsong`.`iTrack` AS `iTrack`,
   `a46`.`globalsong`.`iDuration` AS `iDuration`,
   `a46`.`globalsong`.`iYear` AS `iYear`,
   `a46`.`globalsong`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `a46`.`globalsong`.`strFileName` AS `strFileName`,
   `a46`.`globalsong`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `a46`.`globalsong`.`iTimesPlayed1` AS `iTimesPlayed`,
   `a46`.`globalsong`.`iStartOffset` AS `iStartOffset`,
   `a46`.`globalsong`.`iEndOffset` AS `iEndOffset`,
   `a46`.`globalsong`.`idThumb` AS `idThumb`,
   `a46`.`globalsong`.`lastplayed1` AS `lastplayed`,
   `a46`.`globalsong`.`comment` AS `comment`,
   `a46`.`globalsong`.`rating` AS `rating`
FROM `a46`.`globalsong`;

CREATE DATABASE b46;
CREATE VIEW `b46`.`album` AS SELECT * FROM `a46`.`album`;
CREATE VIEW `b46`.`album_artist` AS SELECT * FROM `a46`.`album_artist`;
CREATE VIEW `b46`.`album_genre` AS SELECT * FROM `a46`.`album_genre`;
CREATE VIEW `b46`.`albuminfosong` AS SELECT * FROM `a46`.`albuminfosong`;
CREATE VIEW `b46`.`art` AS SELECT * FROM `a46`.`art`;
CREATE VIEW `b46`.`artist` AS SELECT * FROM `a46`.`artist`;
CREATE VIEW `b46`.`content` AS SELECT * FROM `a46`.`content`;
CREATE VIEW `b46`.`discography` AS SELECT * FROM `a46`.`discography`;
CREATE VIEW `b46`.`genre` AS SELECT * FROM `a46`.`genre`;
CREATE VIEW `b46`.`karaokedata` AS SELECT * FROM `a46`.`karaokedata`;
CREATE VIEW `b46`.`path` AS SELECT * FROM `a46`.`path`;
CREATE VIEW `b46`.`song_artist` AS SELECT * FROM `a46`.`song_artist`;
CREATE VIEW `b46`.`song_genre` AS SELECT * FROM `a46`.`song_genre`;
CREATE VIEW `b46`.`version` AS SELECT * FROM `a46`.`version`;

CREATE VIEW `b46`.`song`
AS SELECT
   `a46`.`globalsong`.`idSong` AS `idSong`,
   `a46`.`globalsong`.`idAlbum` AS `idAlbum`,
   `a46`.`globalsong`.`idPath` AS `idPath`,
   `a46`.`globalsong`.`strArtists` AS `strArtists`,
   `a46`.`globalsong`.`strGenres` AS `strGenres`,
   `a46`.`globalsong`.`strTitle` AS `strTitle`,
   `a46`.`globalsong`.`iTrack` AS `iTrack`,
   `a46`.`globalsong`.`iDuration` AS `iDuration`,
   `a46`.`globalsong`.`iYear` AS `iYear`,
   `a46`.`globalsong`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `a46`.`globalsong`.`strFileName` AS `strFileName`,
   `a46`.`globalsong`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `a46`.`globalsong`.`iTimesPlayed2` AS `iTimesPlayed`,
   `a46`.`globalsong`.`iStartOffset` AS `iStartOffset`,
   `a46`.`globalsong`.`iEndOffset` AS `iEndOffset`,
   `a46`.`globalsong`.`idThumb` AS `idThumb`,
   `a46`.`globalsong`.`lastplayed2` AS `lastplayed`,
   `a46`.`globalsong`.`comment` AS `comment`,
   `a46`.`globalsong`.`rating` AS `rating`
FROM `a46`.`globalsong`;

CREATE VIEW `b46`.`albumartistview`
AS SELECT
   `album_artist`.`idAlbum` AS `idAlbum`,
   `album_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `album_artist`.`boolFeatured` AS `boolFeatured`,
   `album_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `album_artist`.`iOrder` AS `iOrder`
FROM (`b46`.`album_artist` join `b46`.`artist` on((`album_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `b46`.`albumview`
AS SELECT
   `album`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,
   `album`.`strArtists` AS `strArtists`,
   `album`.`strGenres` AS `strGenres`,
   `album`.`iYear` AS `iYear`,
   `album`.`strMoods` AS `strMoods`,
   `album`.`strStyles` AS `strStyles`,
   `album`.`strThemes` AS `strThemes`,
   `album`.`strReview` AS `strReview`,
   `album`.`strLabel` AS `strLabel`,
   `album`.`strType` AS `strType`,
   `album`.`strImage` AS `strImage`,
   `album`.`iRating` AS `iRating`,
   `album`.`bCompilation` AS `bCompilation`,min(`song`.`iTimesPlayed`) AS `iTimesPlayed`
FROM (`b46`.`album` left join `b46`.`song` on((`album`.`idAlbum` = `song`.`idAlbum`))) group by `album`.`idAlbum`;

CREATE VIEW `b46`.`artistview`
AS SELECT
   `artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `artist`.`strBorn` AS `strBorn`,
   `artist`.`strFormed` AS `strFormed`,
   `artist`.`strGenres` AS `strGenres`,
   `artist`.`strMoods` AS `strMoods`,
   `artist`.`strStyles` AS `strStyles`,
   `artist`.`strInstruments` AS `strInstruments`,
   `artist`.`strBiography` AS `strBiography`,
   `artist`.`strDied` AS `strDied`,
   `artist`.`strDisbanded` AS `strDisbanded`,
   `artist`.`strYearsActive` AS `strYearsActive`,
   `artist`.`strImage` AS `strImage`,
   `artist`.`strFanart` AS `strFanart`
FROM `b46`.`artist`;

CREATE VIEW `b46`.`songartistview`
AS SELECT
   `song_artist`.`idSong` AS `idSong`,
   `song_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `song_artist`.`boolFeatured` AS `boolFeatured`,
   `song_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `song_artist`.`iOrder` AS `iOrder`
FROM (`b46`.`song_artist` join `b46`.`artist` on((`song_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `b46`.`songview`
AS SELECT
   `song`.`idSong` AS `idSong`,
   `song`.`strArtists` AS `strArtists`,
   `song`.`strGenres` AS `strGenres`,
   `song`.`strTitle` AS `strTitle`,
   `song`.`iTrack` AS `iTrack`,
   `song`.`iDuration` AS `iDuration`,
   `song`.`iYear` AS `iYear`,
   `song`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `song`.`strFileName` AS `strFileName`,
   `song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `song`.`iTimesPlayed` AS `iTimesPlayed`,
   `song`.`iStartOffset` AS `iStartOffset`,
   `song`.`iEndOffset` AS `iEndOffset`,
   `song`.`lastplayed` AS `lastplayed`,
   `song`.`rating` AS `rating`,
   `song`.`comment` AS `comment`,
   `song`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `path`.`strPath` AS `strPath`,
   `karaokedata`.`iKaraNumber` AS `iKaraNumber`,
   `karaokedata`.`iKaraDelay` AS `iKaraDelay`,
   `karaokedata`.`strKaraEncoding` AS `strKaraEncoding`,
   `album`.`bCompilation` AS `bCompilation`,
   `album`.`strArtists` AS `strAlbumArtists`
FROM (((`b46`.`song` join `b46`.`album` on((`song`.`idAlbum` = `album`.`idAlbum`))) join `b46`.`path` on((`song`.`idPath` = `path`.`idPath`))) left join `b46`.`karaokedata` on((`song`.`idSong` = `karaokedata`.`idSong`)));

CREATE DATABASE c46;
CREATE VIEW `c46`.`album` AS SELECT * FROM `a46`.`album`;
CREATE VIEW `c46`.`album_artist` AS SELECT * FROM `a46`.`album_artist`;
CREATE VIEW `c46`.`album_genre` AS SELECT * FROM `a46`.`album_genre`;
CREATE VIEW `c46`.`albuminfosong` AS SELECT * FROM `a46`.`albuminfosong`;
CREATE VIEW `c46`.`art` AS SELECT * FROM `a46`.`art`;
CREATE VIEW `c46`.`artist` AS SELECT * FROM `a46`.`artist`;
CREATE VIEW `c46`.`content` AS SELECT * FROM `a46`.`content`;
CREATE VIEW `c46`.`discography` AS SELECT * FROM `a46`.`discography`;
CREATE VIEW `c46`.`genre` AS SELECT * FROM `a46`.`genre`;
CREATE VIEW `c46`.`karaokedata` AS SELECT * FROM `a46`.`karaokedata`;
CREATE VIEW `c46`.`path` AS SELECT * FROM `a46`.`path`;
CREATE VIEW `c46`.`song_artist` AS SELECT * FROM `a46`.`song_artist`;
CREATE VIEW `c46`.`song_genre` AS SELECT * FROM `a46`.`song_genre`;
CREATE VIEW `c46`.`version` AS SELECT * FROM `a46`.`version`;

CREATE VIEW `c46`.`song`
AS SELECT
   `a46`.`globalsong`.`idSong` AS `idSong`,
   `a46`.`globalsong`.`idAlbum` AS `idAlbum`,
   `a46`.`globalsong`.`idPath` AS `idPath`,
   `a46`.`globalsong`.`strArtists` AS `strArtists`,
   `a46`.`globalsong`.`strGenres` AS `strGenres`,
   `a46`.`globalsong`.`strTitle` AS `strTitle`,
   `a46`.`globalsong`.`iTrack` AS `iTrack`,
   `a46`.`globalsong`.`iDuration` AS `iDuration`,
   `a46`.`globalsong`.`iYear` AS `iYear`,
   `a46`.`globalsong`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `a46`.`globalsong`.`strFileName` AS `strFileName`,
   `a46`.`globalsong`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `a46`.`globalsong`.`iTimesPlayed3` AS `iTimesPlayed`,
   `a46`.`globalsong`.`iStartOffset` AS `iStartOffset`,
   `a46`.`globalsong`.`iEndOffset` AS `iEndOffset`,
   `a46`.`globalsong`.`idThumb` AS `idThumb`,
   `a46`.`globalsong`.`lastplayed3` AS `lastplayed`,
   `a46`.`globalsong`.`comment` AS `comment`,
   `a46`.`globalsong`.`rating` AS `rating`
FROM `a46`.`globalsong`;

CREATE VIEW `c46`.`albumartistview`
AS SELECT
   `album_artist`.`idAlbum` AS `idAlbum`,
   `album_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `album_artist`.`boolFeatured` AS `boolFeatured`,
   `album_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `album_artist`.`iOrder` AS `iOrder`
FROM (`c46`.`album_artist` join `c46`.`artist` on((`album_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `c46`.`albumview`
AS SELECT
   `album`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,
   `album`.`strArtists` AS `strArtists`,
   `album`.`strGenres` AS `strGenres`,
   `album`.`iYear` AS `iYear`,
   `album`.`strMoods` AS `strMoods`,
   `album`.`strStyles` AS `strStyles`,
   `album`.`strThemes` AS `strThemes`,
   `album`.`strReview` AS `strReview`,
   `album`.`strLabel` AS `strLabel`,
   `album`.`strType` AS `strType`,
   `album`.`strImage` AS `strImage`,
   `album`.`iRating` AS `iRating`,
   `album`.`bCompilation` AS `bCompilation`,min(`song`.`iTimesPlayed`) AS `iTimesPlayed`
FROM (`c46`.`album` left join `c46`.`song` on((`album`.`idAlbum` = `song`.`idAlbum`))) group by `album`.`idAlbum`;

CREATE VIEW `c46`.`artistview`
AS SELECT
   `artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `artist`.`strBorn` AS `strBorn`,
   `artist`.`strFormed` AS `strFormed`,
   `artist`.`strGenres` AS `strGenres`,
   `artist`.`strMoods` AS `strMoods`,
   `artist`.`strStyles` AS `strStyles`,
   `artist`.`strInstruments` AS `strInstruments`,
   `artist`.`strBiography` AS `strBiography`,
   `artist`.`strDied` AS `strDied`,
   `artist`.`strDisbanded` AS `strDisbanded`,
   `artist`.`strYearsActive` AS `strYearsActive`,
   `artist`.`strImage` AS `strImage`,
   `artist`.`strFanart` AS `strFanart`
FROM `c46`.`artist`;

CREATE VIEW `c46`.`songartistview`
AS SELECT
   `song_artist`.`idSong` AS `idSong`,
   `song_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `song_artist`.`boolFeatured` AS `boolFeatured`,
   `song_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `song_artist`.`iOrder` AS `iOrder`
FROM (`c46`.`song_artist` join `c46`.`artist` on((`song_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `c46`.`songview`
AS SELECT
   `song`.`idSong` AS `idSong`,
   `song`.`strArtists` AS `strArtists`,
   `song`.`strGenres` AS `strGenres`,
   `song`.`strTitle` AS `strTitle`,
   `song`.`iTrack` AS `iTrack`,
   `song`.`iDuration` AS `iDuration`,
   `song`.`iYear` AS `iYear`,
   `song`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `song`.`strFileName` AS `strFileName`,
   `song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `song`.`iTimesPlayed` AS `iTimesPlayed`,
   `song`.`iStartOffset` AS `iStartOffset`,
   `song`.`iEndOffset` AS `iEndOffset`,
   `song`.`lastplayed` AS `lastplayed`,
   `song`.`rating` AS `rating`,
   `song`.`comment` AS `comment`,
   `song`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `path`.`strPath` AS `strPath`,
   `karaokedata`.`iKaraNumber` AS `iKaraNumber`,
   `karaokedata`.`iKaraDelay` AS `iKaraDelay`,
   `karaokedata`.`strKaraEncoding` AS `strKaraEncoding`,
   `album`.`bCompilation` AS `bCompilation`,
   `album`.`strArtists` AS `strAlbumArtists`
FROM (((`c46`.`song` join `c46`.`album` on((`song`.`idAlbum` = `album`.`idAlbum`))) join `c46`.`path` on((`song`.`idPath` = `path`.`idPath`))) left join `c46`.`karaokedata` on((`song`.`idSong` = `karaokedata`.`idSong`)));

CREATE DATABASE d46;
CREATE VIEW `d46`.`album` AS SELECT * FROM `a46`.`album`;
CREATE VIEW `d46`.`album_artist` AS SELECT * FROM `a46`.`album_artist`;
CREATE VIEW `d46`.`album_genre` AS SELECT * FROM `a46`.`album_genre`;
CREATE VIEW `d46`.`albuminfosong` AS SELECT * FROM `a46`.`albuminfosong`;
CREATE VIEW `d46`.`art` AS SELECT * FROM `a46`.`art`;
CREATE VIEW `d46`.`artist` AS SELECT * FROM `a46`.`artist`;
CREATE VIEW `d46`.`content` AS SELECT * FROM `a46`.`content`;
CREATE VIEW `d46`.`discography` AS SELECT * FROM `a46`.`discography`;
CREATE VIEW `d46`.`genre` AS SELECT * FROM `a46`.`genre`;
CREATE VIEW `d46`.`karaokedata` AS SELECT * FROM `a46`.`karaokedata`;
CREATE VIEW `d46`.`path` AS SELECT * FROM `a46`.`path`;
CREATE VIEW `d46`.`song_artist` AS SELECT * FROM `a46`.`song_artist`;
CREATE VIEW `d46`.`song_genre` AS SELECT * FROM `a46`.`song_genre`;
CREATE VIEW `d46`.`version` AS SELECT * FROM `a46`.`version`;

CREATE VIEW `d46`.`song`
AS SELECT
   `a46`.`globalsong`.`idSong` AS `idSong`,
   `a46`.`globalsong`.`idAlbum` AS `idAlbum`,
   `a46`.`globalsong`.`idPath` AS `idPath`,
   `a46`.`globalsong`.`strArtists` AS `strArtists`,
   `a46`.`globalsong`.`strGenres` AS `strGenres`,
   `a46`.`globalsong`.`strTitle` AS `strTitle`,
   `a46`.`globalsong`.`iTrack` AS `iTrack`,
   `a46`.`globalsong`.`iDuration` AS `iDuration`,
   `a46`.`globalsong`.`iYear` AS `iYear`,
   `a46`.`globalsong`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `a46`.`globalsong`.`strFileName` AS `strFileName`,
   `a46`.`globalsong`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `a46`.`globalsong`.`iTimesPlayed4` AS `iTimesPlayed`,
   `a46`.`globalsong`.`iStartOffset` AS `iStartOffset`,
   `a46`.`globalsong`.`iEndOffset` AS `iEndOffset`,
   `a46`.`globalsong`.`idThumb` AS `idThumb`,
   `a46`.`globalsong`.`lastplayed4` AS `lastplayed`,
   `a46`.`globalsong`.`comment` AS `comment`,
   `a46`.`globalsong`.`rating` AS `rating`
FROM `a46`.`globalsong`;

CREATE VIEW `d46`.`albumartistview`
AS SELECT
   `album_artist`.`idAlbum` AS `idAlbum`,
   `album_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `album_artist`.`boolFeatured` AS `boolFeatured`,
   `album_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `album_artist`.`iOrder` AS `iOrder`
FROM (`d46`.`album_artist` join `d46`.`artist` on((`album_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `d46`.`albumview`
AS SELECT
   `album`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,
   `album`.`strArtists` AS `strArtists`,
   `album`.`strGenres` AS `strGenres`,
   `album`.`iYear` AS `iYear`,
   `album`.`strMoods` AS `strMoods`,
   `album`.`strStyles` AS `strStyles`,
   `album`.`strThemes` AS `strThemes`,
   `album`.`strReview` AS `strReview`,
   `album`.`strLabel` AS `strLabel`,
   `album`.`strType` AS `strType`,
   `album`.`strImage` AS `strImage`,
   `album`.`iRating` AS `iRating`,
   `album`.`bCompilation` AS `bCompilation`,min(`song`.`iTimesPlayed`) AS `iTimesPlayed`
FROM (`d46`.`album` left join `d46`.`song` on((`album`.`idAlbum` = `song`.`idAlbum`))) group by `album`.`idAlbum`;

CREATE VIEW `d46`.`artistview`
AS SELECT
   `artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `artist`.`strBorn` AS `strBorn`,
   `artist`.`strFormed` AS `strFormed`,
   `artist`.`strGenres` AS `strGenres`,
   `artist`.`strMoods` AS `strMoods`,
   `artist`.`strStyles` AS `strStyles`,
   `artist`.`strInstruments` AS `strInstruments`,
   `artist`.`strBiography` AS `strBiography`,
   `artist`.`strDied` AS `strDied`,
   `artist`.`strDisbanded` AS `strDisbanded`,
   `artist`.`strYearsActive` AS `strYearsActive`,
   `artist`.`strImage` AS `strImage`,
   `artist`.`strFanart` AS `strFanart`
FROM `d46`.`artist`;

CREATE VIEW `d46`.`songartistview`
AS SELECT
   `song_artist`.`idSong` AS `idSong`,
   `song_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `song_artist`.`boolFeatured` AS `boolFeatured`,
   `song_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `song_artist`.`iOrder` AS `iOrder`
FROM (`d46`.`song_artist` join `d46`.`artist` on((`song_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `d46`.`songview`
AS SELECT
   `song`.`idSong` AS `idSong`,
   `song`.`strArtists` AS `strArtists`,
   `song`.`strGenres` AS `strGenres`,
   `song`.`strTitle` AS `strTitle`,
   `song`.`iTrack` AS `iTrack`,
   `song`.`iDuration` AS `iDuration`,
   `song`.`iYear` AS `iYear`,
   `song`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `song`.`strFileName` AS `strFileName`,
   `song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `song`.`iTimesPlayed` AS `iTimesPlayed`,
   `song`.`iStartOffset` AS `iStartOffset`,
   `song`.`iEndOffset` AS `iEndOffset`,
   `song`.`lastplayed` AS `lastplayed`,
   `song`.`rating` AS `rating`,
   `song`.`comment` AS `comment`,
   `song`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `path`.`strPath` AS `strPath`,
   `karaokedata`.`iKaraNumber` AS `iKaraNumber`,
   `karaokedata`.`iKaraDelay` AS `iKaraDelay`,
   `karaokedata`.`strKaraEncoding` AS `strKaraEncoding`,
   `album`.`bCompilation` AS `bCompilation`,
   `album`.`strArtists` AS `strAlbumArtists`
FROM (((`d46`.`song` join `d46`.`album` on((`song`.`idAlbum` = `album`.`idAlbum`))) join `d46`.`path` on((`song`.`idPath` = `path`.`idPath`))) left join `d46`.`karaokedata` on((`song`.`idSong` = `karaokedata`.`idSong`)));

CREATE DATABASE e46;
CREATE VIEW `e46`.`album` AS SELECT * FROM `a46`.`album`;
CREATE VIEW `e46`.`album_artist` AS SELECT * FROM `a46`.`album_artist`;
CREATE VIEW `e46`.`album_genre` AS SELECT * FROM `a46`.`album_genre`;
CREATE VIEW `e46`.`albuminfosong` AS SELECT * FROM `a46`.`albuminfosong`;
CREATE VIEW `e46`.`art` AS SELECT * FROM `a46`.`art`;
CREATE VIEW `e46`.`artist` AS SELECT * FROM `a46`.`artist`;
CREATE VIEW `e46`.`content` AS SELECT * FROM `a46`.`content`;
CREATE VIEW `e46`.`discography` AS SELECT * FROM `a46`.`discography`;
CREATE VIEW `e46`.`genre` AS SELECT * FROM `a46`.`genre`;
CREATE VIEW `e46`.`karaokedata` AS SELECT * FROM `a46`.`karaokedata`;
CREATE VIEW `e46`.`path` AS SELECT * FROM `a46`.`path`;
CREATE VIEW `e46`.`song_artist` AS SELECT * FROM `a46`.`song_artist`;
CREATE VIEW `e46`.`song_genre` AS SELECT * FROM `a46`.`song_genre`;
CREATE VIEW `e46`.`version` AS SELECT * FROM `a46`.`version`;

CREATE VIEW `e46`.`song`
AS SELECT
   `a46`.`globalsong`.`idSong` AS `idSong`,
   `a46`.`globalsong`.`idAlbum` AS `idAlbum`,
   `a46`.`globalsong`.`idPath` AS `idPath`,
   `a46`.`globalsong`.`strArtists` AS `strArtists`,
   `a46`.`globalsong`.`strGenres` AS `strGenres`,
   `a46`.`globalsong`.`strTitle` AS `strTitle`,
   `a46`.`globalsong`.`iTrack` AS `iTrack`,
   `a46`.`globalsong`.`iDuration` AS `iDuration`,
   `a46`.`globalsong`.`iYear` AS `iYear`,
   `a46`.`globalsong`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `a46`.`globalsong`.`strFileName` AS `strFileName`,
   `a46`.`globalsong`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `a46`.`globalsong`.`iTimesPlayed5` AS `iTimesPlayed`,
   `a46`.`globalsong`.`iStartOffset` AS `iStartOffset`,
   `a46`.`globalsong`.`iEndOffset` AS `iEndOffset`,
   `a46`.`globalsong`.`idThumb` AS `idThumb`,
   `a46`.`globalsong`.`lastplayed5` AS `lastplayed`,
   `a46`.`globalsong`.`comment` AS `comment`,
   `a46`.`globalsong`.`rating` AS `rating`
FROM `a46`.`globalsong`;

CREATE VIEW `e46`.`albumartistview`
AS SELECT
   `album_artist`.`idAlbum` AS `idAlbum`,
   `album_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `album_artist`.`boolFeatured` AS `boolFeatured`,
   `album_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `album_artist`.`iOrder` AS `iOrder`
FROM (`e46`.`album_artist` join `e46`.`artist` on((`album_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `e46`.`albumview`
AS SELECT
   `album`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `album`.`strMusicBrainzAlbumID` AS `strMusicBrainzAlbumID`,
   `album`.`strArtists` AS `strArtists`,
   `album`.`strGenres` AS `strGenres`,
   `album`.`iYear` AS `iYear`,
   `album`.`strMoods` AS `strMoods`,
   `album`.`strStyles` AS `strStyles`,
   `album`.`strThemes` AS `strThemes`,
   `album`.`strReview` AS `strReview`,
   `album`.`strLabel` AS `strLabel`,
   `album`.`strType` AS `strType`,
   `album`.`strImage` AS `strImage`,
   `album`.`iRating` AS `iRating`,
   `album`.`bCompilation` AS `bCompilation`,min(`song`.`iTimesPlayed`) AS `iTimesPlayed`
FROM (`e46`.`album` left join `e46`.`song` on((`album`.`idAlbum` = `song`.`idAlbum`))) group by `album`.`idAlbum`;

CREATE VIEW `e46`.`artistview`
AS SELECT
   `artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `artist`.`strBorn` AS `strBorn`,
   `artist`.`strFormed` AS `strFormed`,
   `artist`.`strGenres` AS `strGenres`,
   `artist`.`strMoods` AS `strMoods`,
   `artist`.`strStyles` AS `strStyles`,
   `artist`.`strInstruments` AS `strInstruments`,
   `artist`.`strBiography` AS `strBiography`,
   `artist`.`strDied` AS `strDied`,
   `artist`.`strDisbanded` AS `strDisbanded`,
   `artist`.`strYearsActive` AS `strYearsActive`,
   `artist`.`strImage` AS `strImage`,
   `artist`.`strFanart` AS `strFanart`
FROM `e46`.`artist`;

CREATE VIEW `e46`.`songartistview`
AS SELECT
   `song_artist`.`idSong` AS `idSong`,
   `song_artist`.`idArtist` AS `idArtist`,
   `artist`.`strArtist` AS `strArtist`,
   `artist`.`strMusicBrainzArtistID` AS `strMusicBrainzArtistID`,
   `song_artist`.`boolFeatured` AS `boolFeatured`,
   `song_artist`.`strJoinPhrase` AS `strJoinPhrase`,
   `song_artist`.`iOrder` AS `iOrder`
FROM (`e46`.`song_artist` join `e46`.`artist` on((`song_artist`.`idArtist` = `artist`.`idArtist`)));

CREATE VIEW `e46`.`songview`
AS SELECT
   `song`.`idSong` AS `idSong`,
   `song`.`strArtists` AS `strArtists`,
   `song`.`strGenres` AS `strGenres`,
   `song`.`strTitle` AS `strTitle`,
   `song`.`iTrack` AS `iTrack`,
   `song`.`iDuration` AS `iDuration`,
   `song`.`iYear` AS `iYear`,
   `song`.`dwFileNameCRC` AS `dwFileNameCRC`,
   `song`.`strFileName` AS `strFileName`,
   `song`.`strMusicBrainzTrackID` AS `strMusicBrainzTrackID`,
   `song`.`iTimesPlayed` AS `iTimesPlayed`,
   `song`.`iStartOffset` AS `iStartOffset`,
   `song`.`iEndOffset` AS `iEndOffset`,
   `song`.`lastplayed` AS `lastplayed`,
   `song`.`rating` AS `rating`,
   `song`.`comment` AS `comment`,
   `song`.`idAlbum` AS `idAlbum`,
   `album`.`strAlbum` AS `strAlbum`,
   `path`.`strPath` AS `strPath`,
   `karaokedata`.`iKaraNumber` AS `iKaraNumber`,
   `karaokedata`.`iKaraDelay` AS `iKaraDelay`,
   `karaokedata`.`strKaraEncoding` AS `strKaraEncoding`,
   `album`.`bCompilation` AS `bCompilation`,
   `album`.`strArtists` AS `strAlbumArtists`
FROM (((`e46`.`song` join `e46`.`album` on((`song`.`idAlbum` = `album`.`idAlbum`))) join `e46`.`path` on((`song`.`idPath` = `path`.`idPath`))) left join `e46`.`karaokedata` on((`song`.`idSong` = `karaokedata`.`idSong`)));

Its the same instructions as video. Get XBMC to create a database called 'a46' with advancedsettings.xml

Code:

<advancedsettings>

<videodatabase>
<type>mysql</type>
<host>10.0.0.8</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>a</name>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>10.0.0.8</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>a</name>
</musicdatabase>

</advancedsettings>

Then run the query.

It renames the 'song' table to 'globalsong' and adds a place for b, c, d and e to store iTimesPlayed and lastplayed columns. Then links everything up.

[schumi2004]

Looks good but for Linux/OpenELEC users it will fail I guess, Windows only script?

Its a .bat file that sets up various folders and files.

I do plan to eventually support linux and mac.

Also, i have your music database ready for testing.

Code:

SQL code

Its the same instructions as video. Get XBMC to create a database called 'a46' with advancedsettings.xml

Code:

<advancedsettings>

<videodatabase>
<type>mysql</type>
<host>10.0.0.8</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>a</name>
</videodatabase>

<musicdatabase>
<type>mysql</type>
<host>10.0.0.8</host>
<port>3306</port>
<user>xbmc</user>
<pass>xbmc</pass>
<name>a</name>
</musicdatabase>

</advancedsettings>

Then run the query.

It renames the 'song' table to 'globalsong' and adds a place for b, c, d and e to store iTimesPlayed and lastplayed columns. Then links everything up.

Great , thanks

[schumi2004]

I noticed something that you could add as reminder for existing shared database users.

My current users are only allowed to edit specific databases, most tutorials mention this

Database Privileges Grant Table-specific privileges Action
MyMusic% ALL PRIVILEGES Yes No Edit Privileges Edit Privileges Revoke Revoke
MyVideos% ALL PRIVILEGES Yes No Edit Privileges Edit Privileges Revoke Revoke

In such case, new users that would try this out should be reminded to edit these to %78 and %46 or just remove (revoke) database specific rules

/edit
Out of curiosity. Which method would performance wise be the best? Yours or from bakslash ?

[N4TH4N]

I noticed something that you could add as reminder for existing shared database users.
My current users are only allowed to edit specific databases, most tutorials mention this

That would be something covered in the setup of the MySQL server. But most people will probably be running an XBMC only MySQL server that either just uses a root account, or an account named xbmc with full privileges.

Unless using a shared MySQL server there is no real need to secure it properly.

I will eventually document the setup procedure of my whole setup including proxy/webcache and will include a section on MySQL permissions.

Out of curiosity. Which method would performance wise be the best? Yours or from bakslash ?

Umm, i'm not too sure as i have only briefly used other methods.

I only use tables and views so it should be as fast as it gets. Also this method does not store any duplicate data.

Its essential that you add "skip-name-resolve" to your my.cnf config file if you have not already. It makes a huge difference.

Im quite confident that it is the most complete and problem free setup. I would go out on a limb and say that its a 100% perfect SQL structure as i have been using it for some time now with zero problems.

I work with SQL databases as part of my job on a regular basis so i have more experience in SQL then most.

[N4TH4N]

Update: Added Linux client script