PDA

View Full Version : Querying the slim database using SQL



adhir
2005-05-31, 14:19
Simply download and install the sqlite cli program. Then find out where
..slimserversql.db is located -- mine is in /usr/local/slimserver, and type:

sqlite .slimserversql.db

Use ".help" for basic usage instructions.

Al

max.spicer wrote:

>I'm in the process of tidying up my music's tags, having gone through
>the process of ripping it all. At some point in the future, I intend
>to go through the process of enjoying listening to my music. ;-) When
>I browse year's in slimserver, I get several lines for seemingly blank
>years, and others for years with non numeric characters in (see
>screenshot). Unfortunately, the slimserver web interface doesn't let
>me find out what albums have generated these entries, as there isn't a
>clickable link in many cases, and in others clicking the link gives a
>blank list on the next page. What I'd like to do, is have look at the
>database itself to see what tracks have the dodgy years. Could someone
>suggest an easy way of firing sql at the database? I have no problem
>with SQL, but no experience of SQLite.
>
>Thanks,
>
>Max
>
>PS My first thought was to do a grep of the source code for something
>like select *, but I thought I'd ask here first. :-)
>
>
>+-------------------------------------------------------------------+
>|Filename: screenshot.jpg |
>|Download: http://forums.slimdevices.com/attachment.php?attachmentid=146|
>+-------------------------------------------------------------------+
>
>
>

max.spicer
2005-05-31, 14:19
I'm in the process of tidying up my music's tags, having gone through the process of ripping it all. At some point in the future, I intend to go through the process of enjoying listening to my music. ;-) When I browse years in slimserver, I get several lines for seemingly blank years, and others for years with non numeric characters in (see screenshot). Unfortunately, the slimserver web interface doesn't let me find out what albums have generated these entries, as there isn't a clickable link in many cases, and in others clicking the link gives a blank list on the next page. What I'd like to do is have look at the database itself to see what tracks have the dodgy years. Could someone suggest an easy way of firing sql at the database? I have no problem with SQL, but no experience of SQLite.

Thanks,

Max

PS My first thought was to do a grep of the source code for something like select *, but I thought I'd ask here first. :-)

JJZolx
2005-05-31, 14:37
What I'd like to do, is have look at the database itself to see what tracks have the dodgy years. Could someone suggest an easy way of firing sql at the database? I have no problem with SQL, but no experience of SQLite.

I've used SQLite Database Browser, which will do what you want. Nothing fancy, but it works:

http://sqlitebrowser.sourceforge.net

A larger list of SQLite tools can be found here:

http://www.sqlite.org/cvstrac/wiki?p=SqliteTools

Of course if you were to move to MySQL there are many, many more software tools available. I don't know how stable the MySQL stuff is, though... I didn't have much luck with it.

Two things to keep in mind, as I've found out when poking data into the database. First, even tiny idiosyncrasies in the data can cause SlimServer to crash. Second, if you insert or update data in the database, even if perfectly valid, there's a very good chance that SlimServer's scanning will just wipe it out the next time it scans your library. Make that a 100% chance if you do a 'wipe cache' before a rescan. If you're only planning on doing SELECT queries, though, you have nothing to worry about.
________
vaporizer pipe (http://vaporizers.net/vapor-genie)

max.spicer
2005-06-01, 11:14
Thanks for the links. I'll go and play. I have more than enough things to get working properly before I even consider complicating things further by moving to MySQL. None of them are the fault of the squeezebox or slimserver though.

Max


I've used SQLite Database Browser, which will do what you want. Nothing fancy, but it works:

http://sqlitebrowser.sourceforge.net

A larger list of SQLite tools can be found here:

http://www.sqlite.org/cvstrac/wiki?p=SqliteTools

dSw
2006-03-12, 11:19
Good tip about the SQLite browser!

I had a look at my db using the a query that was mentioned in another thread:

"select distinct 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 = 1
order by a.title"

This brings back all albums that are designated as compilations, which is fine, however the following two albums are incorrectly marked as compilation:

Athlete - Tourist
St. Germain - Tourist

I've checked the tags and neither have multiple or various artist tags. Is this some kind of derived compilation based on the fact that the album title is the same?

What's the best way to fix it? Makes you wonder what else is wrong...