PDA

View Full Version : An experiment - Slimserver and Microsoft SQL



blueranger
2006-02-16, 04:55
OK, OK, so I was bored!!

I sat down in front of my server the other afternoon, browsed these pages and saw that other people were successfully changing over from SQLite to MySQL. I already have Microsoft SQL installed on my server, the same box that I run SlimServer on, so...... "Why not?" I thought! It would be interesting to compare the performance with SQLite and MySQL.

Before I start, I know it's not supported and I'm not looking for anyone to put themselves out to get it working - I just thought I'd while away an hour or two giving it a try. Here's what happened.

I read all the threads about SlimServer and MySQL so I knew roughly what I was up against. Firstly I had to download the DBD-OBDC drivers. I unpacked these and dropped the files into the specified locations.

Next step was to to create a database and user in MSSQL. That was fairly easy. I copied the SQLite scripts and put them in C:\Program Files\SlimServer\server\SQL\OBDC\ They needed some editing to get them to run in SQL Query Analyzer but that was fairly easy too.

I then set up an OBDC connection - a "System DSN" using the Microsoft OBDC Data Source Administrator.

Then slimserver.pref needed changing to point to the new database. The changes I made were...

dbpassword: slimserver
dbsource: dbi:ODBC:SlimServerDB
dbusername: slimserver

Now, this is the point where I started slim.exe. Lots of errors!!
I've been round the above loop several times making small changes to solve some of the errors.

Example of things I tweaked were,

Fields being truncated - specified varchar(255) instead of just varchar in the schema
id fields - Microsoft use "identity(1,1)" instead of auto_increment

etc., etc.

My dbcreate.sql now looks like:



CREATE TABLE metainformation (
version integer,
track_count integer,
total_time integer
);

INSERT INTO metainformation VALUES (16, 0, 0);

CREATE TABLE tracks (
id integer PRIMARY KEY NOT NULL identity(1,1),
url varchar(255) UNIQUE NOT NULL,
title varchar(255),
titlesort varchar(255),
titlesearch varchar(255),
album integer,
tracknum integer,
ct varchar(255),
tag integer,
age integer,
fs integer,
size integer,
offset integer,
year integer,
secs float,
cover varchar(255),
covertype varchar(255),
thumb varchar(255),
thumbtype varchar(255),
vbr_scale varchar(255),
bitrate float,
rate integer,
samplesize integer,
channels integer,
blockalign integer,
endian integer,
bpm integer,
tagversion varchar(255),
tagsize integer,
drm integer,
rating integer,
disc integer,
moodlogic_id integer,
playCount integer,
lastPlayed integer,
audio integer,
lossless integer,
remote integer,
lyrics text,
moodlogic_mixable integer,
musicbrainz_id varchar(255),
musicmagic_mixable integer,
replay_gain float,
replay_peak float,
multialbumsortkey varchar(255)
);

CREATE INDEX trackURLIndex ON tracks (url);

CREATE INDEX trackTitleIndex ON tracks (title);

CREATE INDEX trackAlbumIndex ON tracks (album);

CREATE INDEX ctSortIndex ON tracks (ct);

CREATE INDEX trackSortIndex ON tracks (titlesort);

CREATE INDEX trackSearchIndex ON tracks (titlesearch);

CREATE INDEX trackRatingIndex ON tracks (rating);

CREATE INDEX trackPlayCountIndex ON tracks (playCount);

CREATE INDEX trackAudioIndex ON tracks (audio);

CREATE INDEX trackLosslessIndex ON tracks (lossless);

CREATE INDEX trackRemoteIndex ON tracks (remote);

CREATE INDEX trackSortKeyIndex ON tracks (multialbumsortkey);

CREATE TABLE playlist_track (
id integer PRIMARY KEY NOT NULL identity(1,1),
position integer,
playlist integer,
track integer
);

CREATE TABLE dirlist_track (
id integer PRIMARY KEY NOT NULL identity(1,1),
position integer,
dirlist integer,
item varchar(255)
);

CREATE TABLE albums (
id integer PRIMARY KEY NOT NULL identity(1,1),
title varchar(255),
titlesort varchar(255),
titlesearch varchar(255),
contributor varchar(255),
compilation integer,
year integer,
artwork_path varchar(255),
disc integer,
discc integer,
replay_gain float,
replay_peak float,
musicbrainz_id varchar(255),
musicmagic_mixable integer
);

CREATE INDEX albumsTitleIndex ON albums (title);

CREATE INDEX albumsSortIndex ON albums (titlesort);

CREATE INDEX albumsSearchIndex ON albums (titlesearch);

CREATE INDEX compilationSortIndex ON albums (compilation);

CREATE TABLE contributors (
id integer PRIMARY KEY NOT NULL identity(1,1),
name varchar(255),
namesort varchar(255),
namesearch varchar(255),
moodlogic_id integer,
moodlogic_mixable integer,
musicbrainz_id varchar(255),
musicmagic_mixable integer
);

CREATE INDEX contributorsNameIndex ON contributors (name);

CREATE INDEX contributorsSortIndex ON contributors (namesort);

CREATE INDEX contributorsSearchIndex ON contributors (namesearch);

CREATE TABLE contributor_track (
id integer PRIMARY KEY NOT NULL identity(1,1),
role integer,
contributor integer,
track integer,
namesort varchar(255)
);

CREATE INDEX contributor_trackContribIndex ON contributor_track (contributor);

CREATE INDEX contributor_trackRoleIndex ON contributor_track (role);

CREATE INDEX contributor_trackTrackIndex ON contributor_track (track);

CREATE INDEX contributor_trackSortIndex ON contributor_track (namesort);

CREATE TABLE contributor_album (
id integer PRIMARY KEY NOT NULL identity(1,1),
role integer,
contributor integer,
album integer
);

CREATE INDEX contributor_albumContribIndex ON contributor_album (contributor);

CREATE INDEX contributor_albumRoleIndex ON contributor_album (role);

CREATE INDEX contributor_albumAlbumIndex ON contributor_album (album);

CREATE TABLE genres (
id integer PRIMARY KEY NOT NULL identity(1,1),
name varchar(255),
namesort varchar(255),
namesearch varchar(255),
moodlogic_id integer,
moodlogic_mixable integer,
musicmagic_mixable integer
);

CREATE INDEX genreNameIndex ON genres (name);

CREATE INDEX genreSortIndex ON genres (namesort);

CREATE INDEX genreSearchIndex ON genres (namesearch);

CREATE TABLE genre_track (
id integer PRIMARY KEY NOT NULL identity(1,1),
genre integer,
track integer
);

CREATE INDEX genre_trackGenreIndex ON genre_track (genre);

CREATE INDEX genre_trackTrackIndex ON genre_track (track);

CREATE TABLE comments (
id integer PRIMARY KEY NOT NULL identity(1,1),
track integer,
value varchar(255)
);

CREATE TABLE pluginversion (
id integer PRIMARY KEY NOT NULL identity(1,1),
name varchar(255),
version integer
);



I've now reached the point where I don't know enough to get any further (A little knowledge is so dangerous!!)

SlimServer falls over with the following error (first one only listed - there are lots!)



C:\Program Files\SlimServer\server>slim.exe
2006-02-14 21:04:21.7798 Backtrace:

frame 0: Slim::DataStores::DBI::DBIStore::newTrack (/PerlApp/Slim/DataStores/DBI/DBIStore.pm line 595)
frame 1: Slim::DataStores::DBI::DBIStore::updateOrCreate (/PerlApp/Slim/DataStores/DBI/DBIStore.pm line 226)
frame 2: Slim::DataStores::DBI::DBIStore::objectForUrl (/PerlApp/Slim/Utils/Scan.pm line 499)
frame 3: Slim::Utils::Scan::readList (/PerlApp/Slim/Utils/Scan.pm line 217)
frame 4: Slim::Utils::Scan::addToList_run (/PerlApp/Slim/Utils/Scan.pm line 157)
frame 5: Slim::Utils::Scan::addToList (/PerlApp/Slim/Music/PlaylistFolderScan.pm line 55)
frame 6: Slim::Music::PlaylistFolderScan::startScan (/PerlApp/Slim/Music/Import.pm line 51)
frame 7: Slim::Music::Import::startScan (slimserver.pl line 1054)
frame 8: main::checkDataSource (slimserver.pl line 537)
frame 9: main::init (slimserver.pl line 563)
frame 10: main::main (slimserver.pl line 59)
frame 11: PerlSvc::Interactive (perlsvc.pl line 1485)
frame 12: PerlSvc::_interactive (slimserver.pl line 0)
frame 13: (eval) (slimserver.pl line 0)

2006-02-14 21:04:21.7828 Couldn't create track for file:///D:/SlimServer/Playlists : Can't insert new Slim::DataStores::
DBI::Track: Can't get last insert id at /PerlApp/Slim/DataStores/DBI/DBIStore.pm line 489
at /PerlApp/Slim/DataStores/DBI/DBIStore.pm line 489


However, 9 rows have been created in the "Tracks" table of the database at this point - pointers to playlists and music directories, so it is partially working.

If any of you SlimServer/SQL gurus out there can instantly spot what's wrong then please enlighten me (I'm probably an idiot for even trying this!). Otherwise I'll put everything back the way it was and get back to my music!

jonheal
2006-02-16, 05:19
I sent you a private message.

kdf
2006-02-16, 09:51
This kind of topic is best taken to the developers forum.
-k

blueranger
2006-02-16, 14:31
Hi K

As I'm only an enthusiastic amateur and seeing the description of the Developers Forum, I didn't think I was allowed to post in that hallowed place ;)

Shall I re-post or can a mod do the honours and move this thread?

geoffb
2006-02-20, 08:07
On 2/16/06, blueranger <blueranger.23bmmb (AT) no-mx (DOT) forums.slimdevices.com> wrote:
>
> OK, OK, so I was bored!!
>
> I sat down in front of my server the other afternoon, browsed these
> pages and saw that other people were successfully changing over from
> SQLite to MySQL. I already have Microsoft SQL installed on my server,
> the same box that I run SlimServer on, so...... "Why not?" I thought!
> It would be interesting to compare the performance with SQLite and
> MySQL.

I did exactly what you did, and got to the same point. Here's the
thread, in case you didn't find it yet:
http://forums.slimdevices.com/showthread.php?t=1930

It got down to the way that the ODBC driver was translating insert
statements, specifically for identity columns, I think.

I tried a couple of workarounds, but if I recall correctly the biggest
problem was that the SQL used to do work on the database isn't
abstracted out per platform, it's inline with the rest of the code.

I suspect that someone would need to do work on the perl code to
separate out access the data layer, but it's beyond me (if it was
Delphi / dotnet, I'd happily have a crack - I just don't have time to
learn perl at the moment).

Cheers
Geoff