PDA

View Full Version : Now that Slimserver is using MySql ...



Kyle
2006-11-01, 10:03
is there an easy way to make a list of artists, albums, tracks?

erland
2006-11-01, 10:12
is there an easy way to make a list of artists, albums, tracks?What do you mean with "make a list" ? A text file containing all artists or a list in the web interface or something else ?

Kyle
2006-11-01, 11:07
A text file or excel file that could be printed out, preferably with album art, too.

MrSinatra
2006-11-01, 11:25
awesome idea...

generated as HTML i think would be great too.

mherger
2006-11-01, 13:30
> A text file or excel file that could be printed out, preferably with
> album art, too.

You can use the MySQL ODBC connector to fetch your data using Access,
Excel or whatever you want.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

MrSinatra
2006-11-01, 13:42
where do i get this obdc connector, and what do you have to do to get it to act as gobetween for mysql and excel?

audiofi
2006-11-01, 13:46
I've used this before (not on slimserver though), should hopefully just be a case of changing the DB name, order by, dbinfo etc.

I got the code from a tutorial, can't find it now though!!



<?php

include ("../includes/dbinfo.php");

// Get database data
$select = "SELECT * FROM sales ORDER BY Date ASC";
$export = mysql_query($select);
$fields = mysql_num_fields($export);

// Extract DB fields
for ($i = 0; $i < $fields; $i++) {
$header .= mysql_field_name($export, $i) . "\t";
}

// Convert into Excel cells

while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);

// Check for data

if ($data == "") {
$data = "\n(0) Records Found!\n";
}

// Send for download

$date = date("d.m.y");
$filename = "paypalsales";
$extension = ".xls";
$fullfile = $filename . $date . $extension;

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$fullfile");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";
exit;
echo "Completed";
?>

JJZolx
2006-11-01, 13:51
> A text file or excel file that could be printed out, preferably with
> album art, too.

You can use the MySQL ODBC connector to fetch your data using Access,
Excel or whatever you want.
Let me know if anyone gets that working. I haven't yet tried the 5.0 version of the ODBC connector, but with 3.51 had a lot of problems reading the BLOB columns that SlimServer uses extensively to store text.

http://dev.mysql.com/downloads/connector/odbc/5.0.html

This might make a nice plugin if anyone wanted to do it from within SlimServer itself. Using the included Template Toolkit would give a lot of flexibility for the layout, making the design of different skins pretty easy.

mherger
2006-11-01, 13:56
> where do i get this obdc connector, and what do you have to do to get it
> to act as gobetween for mysql and excel?

http://www.mysql.com/products/connector/

There's the binary installable package for different systems plus
documentation. When connecting to your slimserver's MySQL database be sure
you us port 9092 instead of the standard port (3369 or something).

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

jeffluckett
2006-11-01, 13:57
You could try SQLYog ... that's what I use.

http://www.webyog.com/en/

bklaas
2006-11-01, 14:09
I've been scheming about doing some reporting with Slimserver's Mysql since they moved to it...

This is an unfinished piece of work (you will be very frustrated if you download it and try to run it), but I attempted to write a super-detailed mp3 database app several years ago, before I owned a squeezebox. Web-based, template driven front-end, Mysql back-end. It not only had reports, it had graphs! Everybody loves a graph...

lots of screenshots here--
http://tunevault.sourceforge.net

maybe I could rewrite this to query slimserver instead of my homegrown mysql back-end...

food for thought anyway...

cheers,
#!/ben

shabbs
2006-11-01, 14:23
Check out this thread:

http://forums.slimdevices.com/showthread.php?t=28457

Might be what you are looking for.

mherger
2006-11-01, 14:43
> Let me know if anyone gets that working. I haven't yet tried the 5.0
> version of the ODBC connector, but with 3.51 had a lot of problems
> reading the BLOB columns that SlimServer uses extensively to store
> text.

You're right. Seems harder than I thought :-( 5.0 doesn't work for me
neither.

--

Michael

-----------------------------------------------------------------
http://www.herger.net/SlimCD - your SlimServer on a CD
http://www.herger.net/slim - AlbumReview, Biography, MusicInfoSCR

NigelC
2006-11-01, 15:23
This is something that I have been after for yonks.

SQLYog lets me look at the database, but its not really a report writer. I have MS Access on my PC, but I get problems when I try and link to the tables (the BLOBs cause problems). Ive played with Emissary's java tool but cant get it to run.
{oops , I wasnt running the right java version! - Ill have another go now}

Id love to see a facility integrated with Slimserver, that will output an artist/album/track/album art listing with various selection options. I dont mind reading in a CSV and doing some formatting work

At the moment, I keep forgetting what albums I own, and end up buying repeats.

Ben, I'm sure you'd do love a new challenge

Nigel

bklaas
2006-11-01, 15:44
FWIW...just from a short time poking around, this is the SQL to produce a list of artists and albums, sorted by artist, then album:

select contributors.name, albums.title from albums, contributors where contributors.id = albums.contributor order by contributors.name, albums.title;

Personally, I see a slimserver database reporting tool as residing separately from slimserver--IMO, this kind of thing doesn't seem right integrated directly into slimserver. If I was doing this (and I just might), I'd write a small suite of Perl scripts to generate reports using DBI and Template::Toolkit.

Maybe it could be integrated as a plugin, but I guarantee that an autonomous reporting program could be whipped together with much less effort...

cheers,
#!/ben

JJZolx
2006-11-01, 15:50
> Let me know if anyone gets that working. I haven't yet tried the 5.0
> version of the ODBC connector, but with 3.51 had a lot of problems
> reading the BLOB columns that SlimServer uses extensively to store
> text.

You're right. Seems harder than I thought :-( 5.0 doesn't work for me
neither.

I think tonight I'll try 5.0 and see if changing the BLOB columns to TEXT has any affect on what I can do through the ODBC connector. Also, I'd be curious if SlimServer would still work with this change. From what I'm reading in the MySQL docs, there's very little difference between the two datatypes.

http://dev.mysql.com/doc/refman/5.0/en/blob.html

BLOB columns are treated as binary strings (byte strings). TEXT columns are treated as non-binary strings (character strings). BLOB columns have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT columns have a character set, and values are sorted and compared based on the collation of the character set.

NigelC
2006-11-01, 16:12
FWIW...just from a short time poking around, this is the SQL to produce a list of artists and albums, sorted by artist, then album:

select contributors.name, albums.title from albums, contributors where contributors.id = albums.contributor order by contributors.name, albums.title;

Personally, I see a slimserver database reporting tool as residing separately from slimserver--IMO, this kind of thing doesn't seem right integrated directly into slimserver. If I was doing this (and I just might), I'd write a small suite of Perl scripts to generate reports using DBI and Template::Toolkit.

Maybe it could be integrated as a plugin, but I guarantee that an autonomous reporting program could be whipped together with much less effort...

cheers,
#!/ben

Ben
You're right. A reporting tool doesnt really need to be integrated. I'm just after an easy life, and want a button to press within the Slimserver web interface.
(I'll have to dust off my SQL skills - I tried your query in SQLYog- I cant see an albums.contributor field.

Nigel

bklaas
2006-11-01, 16:15
Ben
You're right. A reporting tool doesnt really need to be integrated. I'm just after an easy life, and want a button to press within the Slimserver web interface.
(I'll have to dust off my SQL skills - I tried your query in SQLYog- I cant see an albums.contributor field.

Nigel

ah yes, quite right...my current slimserver appears to not be using my standalone mysql. This "slimserver" database I've got in my running mysql server may be outdated.

#!/ben

NigelC
2006-11-01, 16:49
ah yes, quite right...my current slimserver appears to not be using my standalone mysql. This "slimserver" database I've got in my running mysql server may be outdated.

#!/ben

This seems to work in SQLYog

select contributors.name , albums.title from contributors , albums , contributor_album where contributors.id=contributor_album.contributor and albums.id=contributor_album.album order by contributors.name, albums.title

but:
- it repeats albums where there are multiple contributors
- I still need a nice pretty print out

N

tommypeters
2006-11-02, 00:57
...SELECT DISTINCT to only get album printed once...?

ceejay
2006-11-02, 01:44
Or, even better,

select distinct contributors.name , albums.title from contributors , albums , contributor_album where contributors.id=contributor_album.contributor and contributor_album.role=1 and albums.id=contributor_album.album order by contributors.name, albums.title


... which additionally selects only "artist" contributors, stops cluttering the list with composers and conductors which you may have listed...

Remaining snag for this query is that although it does list compilation albums exactly once, which is good, it appears to pick an arbitrary artist from the possibilities to go in column one. Any suggestions?

Ceejay

smc2911
2006-11-02, 02:27
How about this:

SELECT DISTINCT c.name AS artist, a.title AS album
FROM contributors c, albums a, contributor_album ca
WHERE c.id=ca.contributor AND (ca.role=1 or ca.role=5)
AND a.id=ca.album and (a.compilation IS NULL OR a.compilation=0)
UNION
SELECT DISTINCT 'Various Artists', a.title
FROM albums a
WHERE a.compilation=1
ORDER BY artist, album

Note that I've included album artists (aka Band) role=5

ceejay
2006-11-02, 04:45
I was all set to reply that this is all very well but I don't set the compilation tag - however I just checked and of course slimserver does this for me, based on the various artists logic - so this query works nicely.

One day I really must learn SQL instead of just hacking at it...

Ceejay

ceejay
2006-11-02, 04:57
One oddity, however, with all of these queries - not of any importance, I'm just fascinated ...

I ran the query in SQLyog, copied the result to the clipboard using "Excel friendy values" for escape characters, and copied into Excel. No problem at all, looks lovely.

But... one of my album titles is "True" and in the resulting Excel file it, and it alone, is capitalised and centred - "TRUE". I assume this is due to some part of the process assuming this is a boolean value instead of a string, though there's no obvious difference in the SQLyog results pane or in the Excel cell format...

Curious.

Ceejay

JJZolx
2006-11-02, 05:02
But... one of my album titles is "True" and in the resulting Excel file it, and it alone, is capitalised and centred - "TRUE". I assume this is due to some part of the process assuming this is a boolean value instead of a string, though there's no obvious difference in the SQLyog results pane or in the Excel cell format...
The default format for Excel cells is 'General' and it does what it can to figure out the type of data automatically. Change the format to type 'Text' and this should go away.

ceejay
2006-11-02, 05:11
The default format for Excel cells is 'General' and it does what it can to figure out the type of data automatically. Change the format to type 'Text' and this should go away.


Thanks Jim - a quick test suggests that changing the format of the cell from General to Text after it has been populated doesn't change the appearance. However setting the format to Text BEFORE pasting in the data fixes the problem.

You really do learn something new every day, here!

Ceejay