PDA

View Full Version : Accessing slimserversql.db contents



davep
2005-10-29, 19:15
I would really like to be able to generate up to date lists - excel, pdf, etc - of the albums I have in my slimserversql.db. There used to be a pearl script which did this but as AFAIK this was before the change to the sqlite backend.

I have looked into the db using one or two sqlite front ends and can see that the info is there but not in readily accessible form (i.e. seperate lists of albums, tracks and contributors but not linked in an immediately usable way). I am not really interested in learning SQL syntax to construct a way of generating such a list so was wondering if anybody else had done such a thing. Goal would ideally be to have a complete list of albums, sorted by artist, on my PDA so that it could be checked during shopping binges.

davep

max.spicer
2005-10-30, 02:55
This is really trivial to do in SQL.

Max
This is trivial to do in SQL. SQL is a really easy language to pick up the basics of so it would really be worth your while learning it. Do a search for SQL Tutorial in Google. In the meantime, here's something I've just hacked up in a couple of minutes:


select a.title, c.name
from albums a, contributor_album ca, contributors c
where a.id = ca.album
and ca.contributor = c.id
and a.compilation = 0
union
select a.title, 'Various Artists'
from albums a
where a.compilation = 1
order by c.name

Max

I would really like to be able to generate up to date lists - excel, pdf, etc - of the albums I have in my slimserversql.db. There used to be a pearl script which did this but as AFAIK this was before the change to the sqlite backend.

I have looked into the db using one or two sqlite front ends and can see that the info is there but not in readily accessible form (i.e. seperate lists of albums, tracks and contributors but not linked in an immediately usable way). I am not really interested in learning SQL syntax to construct a way of generating such a list so was wondering if anybody else had done such a thing. Goal would ideally be to have a complete list of albums, sorted by artist, on my PDA so that it could be checked during shopping binges.

davep

davep
2005-10-30, 05:36
Max,
many thanks for taking the trouble to address my question. I agree that the smart thing would be to learn the rudiments of SQL and be able to construct my own queries. I did try pasting your query script into the (Windows) front end I have been playing with and it did indeed generate output - a list of a seeming random selection of 13 albums. I am sure that this could be tinkered with to ultimately produce what I am looking for - a table containing a list of artists and their albums. Then there is the problem of getting this data out of SQL into something (for me) usable and transferrable such as Excel.

I was hoping that there was somebody out there who had knocked up a nifty plugin that I hadn't seen that would do just this. I can't help feeling that I am not alone in this and that many people would like to be able to generate a neat printed list of artists and albums from their SS database.

I guess it is time to learn SQL...

davep

NigelC
2005-10-31, 07:29
I would really appreciate something like this as well. I'd like to print out an album list (to check that Ive ripped all my CDs amongst other reasons). If someone could tell us how to generate a csv file, Id be happy to format it in Excel or similar

Kevin Walsh has a pdf creating script, (see http://www.slimp3.rtfm.info/downloads/index.html )
but having no idea about PERL I couldnt make this work

Nigel

ceejay
2005-10-31, 07:35
Kevin Walsh has a pdf creating script, (see http://www.slimp3.rtfm.info/downloads/index.html )
but having no idea about PERL I couldnt make this work

Nigel

This appears not to have been updated since May 04 so I guess is unlikely to work with the current Slimserver... May be time to get that SQL book out after all...

Ceejay

mherger
2005-10-31, 07:38
> I would really appreciate something like this as well. I'd like to print
> out an album list (to check that Ive ripped all my CDs amongst other
> reasons). If someone could tell us how to generate a csv file, Id be
> happy to format it in Excel or similar

There are ODBC drivers available for SQLite
(http://www.ch-werner.de/sqliteodbc/). You could access the db directly
from Access or Excel. But please: read-only!

--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
SlimString Translation Helper (http://www.herger.net/slim/)

ceejay
2005-10-31, 07:53
minor glitch in that URL...

http://www.ch-werner.de/sqliteodbc/

Ceejay

pfarrell
2005-10-31, 08:03
NigelC said:
> I would really appreciate something like this as well. I'd like to print
> out an album list (to check that Ive ripped all my CDs amongst other
> reasons).

Let me back up just a bit.
There are Sqlite clients, I assume they pass Sql to the database
and get the usual results.

I've had wandering arround for this kinda stuff on my back burner
for a while. But the first, and probably easilest to answer,
question is: Where is the database? what's it called?
Assuming that the sqlite client is installed, how do I do
the equivlent to
mysql slimdb

Thx

Pat
http://www.pfarrell.com

mherger
2005-10-31, 08:10
> minor glitch in that URL...
> http://www.ch-werner.de/sqliteodbc/

Depends on your browser ;-). Opera does not include closing bracket when
linking.


--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
SlimString Translation Helper (http://www.herger.net/slim/)

NigelC
2005-10-31, 09:22
Thanks
I'll see if I can remember how to use ODBC links

N

ModelCitizen
2005-10-31, 09:31
If anyone manages to get the SQLite ODBC Driver to work on XP Pro with a late version of Office (I'm using 2003 here) could they post a "how to" here?

I copied slimserversql.db to my desktop (so I did not screw up the original one), created a SQLite3 User DSN via Administrative Tools/Data Sources with a path to the db file on my desktop and a friendly name (I assume Data Source Name is a friendly name thing), put Lock Time Out to 1000 seconds (gained from SQLite ODBC Driver readme.txt). Ignored the two check boxes.

When I opened slimserversql.db in MS Access I received the message "MS Jet Database Engine could not find the object "slimserversql.db". Make sure the object exists and you spell it's name and the path name correctly". As I never typed any names or paths (used browse function) the message didn't help. The associated Help File is blank (thanks MS).

I then tried changing the User DNS to aim at C:\Program Files\SlimServer\server\Cache\slimserversql.db and then tried opening the db in Access and Excel but received the same result.

MC

mherger
2005-10-31, 09:34
> I'll see if I can remember how to use ODBC links

You don't have to remember this, you only have to know how to read and
google :-)

--

Michael

-----------------------------------------------------------
Help translate SlimServer by using the
SlimString Translation Helper (http://www.herger.net/slim/)

NigelC
2005-10-31, 09:34
I'm glad to see there's some interest in this topic. (I love Slimserver and my Squeezebox, but I would have thought that a listing capability was a basic essential).

Anyway, I found this post http://forums.slimdevices.com/showthread.php?t=15141 which I'll try when i get a chance and report back

Nigel

lostboy
2005-10-31, 12:29
MC, this worked for me on XP/SP2 - Access 2003

1. Using the Administrative Tools / Data Sources option - select the System DSN tab and configure (or add another) a SQLite3 source pointing to your copy of the db.

2. Open Access and create a new blank database, use the menu options: File - Get External Data - and either Import or Link Tables (as you prefer). In the dialogue box that opens set file type to ODBC(). This should open a Select Data Source dialogue box. Select the tab 'Machine Source' - click on the source you configured in 1. Click OK (I used 1000 msec as a time out).

3. Select the tables you want to link to in the dialogue that appears and click OK - the tables you want will appear in the Access database control.

4. The relations between the tables will not come over, but if you link rather than import the table specific indexes and keys seem to be preserved (all are lost if you import).

Happy querying

Chris

davep
2005-10-31, 15:46
NigelC,
Many thanks for pointing me at the link to the previous posting from snood which gave the nexcessary instructions for generating a csv from the sql.db.

I believe that there is an error in the mysql.txt file which snood links to as, in my case, it crashed first time reporting no such column as "albums.contributors". When I edited this to "albums.contributor" (i.e singular not plural) it ran and gave me a csv which I can now use to manipulate as I choose.

Thanks again.

davep

NigelC
2005-11-01, 04:50
Here is another useful tool http://sqlitebrowser.sourceforge.net/
to access the slimserver db with a GUI
It supports:
- browsing the structure and contents of the db
- running SQL queries
- exporting tables to CSV files

This works well for me

Nigel

max.spicer
2005-11-01, 05:24
Seemingly random list of albums? Odd! On my system it outputs a complete list of all my albums, sorted by artist.

Max


Max,
many thanks for taking the trouble to address my question. I agree that the smart thing would be to learn the rudiments of SQL and be able to construct my own queries. I did try pasting your query script into the (Windows) front end I have been playing with and it did indeed generate output - a list of a seeming random selection of 13 albums. I am sure that this could be tinkered with to ultimately produce what I am looking for - a table containing a list of artists and their albums. Then there is the problem of getting this data out of SQL into something (for me) usable and transferrable such as Excel.

ceejay
2005-11-01, 11:42
Here is another useful tool http://sqlitebrowser.sourceforge.net/
to access the slimserver db with a GUI
It supports:
- browsing the structure and contents of the db
- running SQL queries
- exporting tables to CSV files

This works well for me

Nigel

... and an endorsement from me too. Thanks, Nigel. Downloaded and seems to be working. But I think I'll just point it at a spare copy of the DB while I figure out what I'm doing!

Thanks
Ceejay

davep
2005-11-01, 15:35
Seemingly random list of albums? Odd! On my system it outputs a complete list of all my albums, sorted by artist.

Max

Max,
I have tried this several times undser both Linux and Windows using a variety of tools and get the same NOT random selection of 13 albums by 8 artists. Digging into my db I see that the table contributor_album has the same size and shape as this output data and I suspect this is the cause. I do not understand it though as I imagine that this index should reflect the whole 700+ albums in the db - which are all clearly visible in the albums index, as are the 300 odd artists in the contributors index.

Could this be a tagging thing?

Anyway I got what I wanted by doing a kludge in excel using two csv tables - one for albums with contributor id and the other a list of contributors and using a VLOOKUP to marry the two into my final list.

Thaks for your help which got me started playing with SQL syntax.

davep

Kyle
2006-02-13, 20:07
... and an endorsement from me too. Thanks, Nigel. Downloaded and seems to be working. But I think I'll just point it at a spare copy of the DB while I figure out what I'm doing!

Thanks
Ceejay
Well, where the heck is the database, and how do you create a spare copy?

EDIT: Never mind. I found it and created a copy. Next question: I created a CSV file and opened it with Excel, but all I got was one cell per track in my collection way too much stuff to be useful. How do I narrow down the data?

kdf
2006-02-13, 20:27
On 13-Feb-06, at 7:07 PM, Kyle wrote:

>
> ceejay Wrote:
>> ... and an endorsement from me too. Thanks, Nigel. Downloaded and
>> seems
>> to be working. But I think I'll just point it at a spare copy of the
>> DB
>> while I figure out what I'm doing!
>>
>> Thanks
>> Ceejay
> Well, where the heck is the database, and how do you create a spare
> copy?
>
It's called slimserversql.db, and every os has an ability to search.
To create a spare copy, just copy the file to another filename or
location
-kdf

TellyBoots
2006-02-20, 21:19
My first post so go easy on me if this has already been addressed... ;)

To create a list of all artists/albums/songs I added all my songs into the playlist in Slimserver and saved it. I then opened that playlist (*.m3u - an ASCII text written using backslash ( \ ) as the delimiter) in Excel, selected 'delimited' and then checked 'tab' as well as 'other' and entered '\' in the other box. You'll need to massage the Excel file a bit (delete the 1st two columns and sort to get rid of the blank rows) but it should be good to go.

Again, sorry if this was known to all but I couldn't seem to find this simple answer in the threads.

Eserim
2006-03-18, 03:31
back to the ODBC driver (BTW a new version was released last month)

I can't seem to set the "Data Source Name" in the admin popup box.

If I try it via the ODBC administrator all I get is "general error: invalid file dsn" - then path to DSN.

If I connect via Access, it asks for this accepts it, but only for that session. If I go back into Access it fails to connect without going into Linked Table manger and refreshing the link.

Any ideas?

Cheers
Eserim

Pale Blue Ego
2006-03-18, 06:57
Since the slimserver.db is created from file tags, why not just use a different tool to read those? No SQL required.

MPEG Audio Collection can read mp3, wav, ogg, ape, wma, aac, and flac files.

Point it at your music collection and it pulls in:

FILE path/size/type/bitrate/duration/sample rate/channel mode

TAG title/artist/album/track #/year/comment/genre

Can output customized reports in txt/html/Excel

http://mac.sourceforge.net/

Not as powerful as SQL queries, but very useful to generate printouts of tracks or albums

Eserim
2006-03-18, 07:12
that's not bad - I like tha fact that you can set it to open stuff in Foobar - my choice of PC players. Cheers.