Sometime a little nudge is what we need :)
I had been meaning to install and get familiar with SQLite Expert but
have found any number of reasons not to. But when I decided to try LMS
Extractor it was immediately obvious that I would need something to
reveal the table structures if I wanted to do much beyond your default
queries.
So I now am using both, SQLite Expert as a query building tool and LMS
Extractor as an easy way to save the results in a useful format.
I have long wanted a way to build a catalog of my albums and in addition
to the artist, title, genre and date, identify which format (codec and
bitrate) they are in.
The following SQL query works pretty well. It's not necessarily 100%
accurate as it only uses data from the first track of the album, but
it's close enough for my needs. Note the need for single quotes around
Compilation. That threw me for a few minutes when "1" kept showing up as
the Artist for compilation albums.
SELECT
CASE
WHEN al.compilation = 1 THEN CAST('Compilation' AS CHAR(150))
ELSE TRIM(CAST(co.name AS CHAR(150)))
END AS "Artist",
TRIM(CAST(al.title AS CHAR(150))) AS "Album Title",
TRIM(CAST(gn.name AS CHAR(150))) AS "Genre" ,
al.year AS "Year",
CASE
WHEN tr.content_type = "flc" THEN "FLAC"
WHEN tr.content_type = "mp3" THEN "MP3"
WHEN tr.content_type = "mp4" THEN "MP4"
WHEN tr.content_type = "wma" THEN "WMA"
ELSE tr.content_type
END AS "Codec",
ROUND((tr.bitrate/1000),0) AS "Bitrate (Kbps)",
CASE
WHEN tr.vbr_scale = 1 THEN "VBR"
ELSE "CBR"
END AS "VBR/CBR",
CASE
WHEN tr.lossless = 1 THEN "YES"
ELSE "NO"
END AS "Lossless"
FROM albums al,
tracks tr,
contributor_track ct,
contributors co,
genre_track gt,
genres gn
WHERE al.id = tr.album
AND tr.tracknum = "1"
AND ct.track = tr.id
AND ct.role IN (1, 5)
AND co.id = ct.contributor
AND gt.track = tr.id
AND gn.id = gt.genre
ORDER BY 1, 2
Win7Pro(x64)[3.3Ghz i5, 8GB RAM, 120GB SSD system, 15TB storage], LMS
7.7.3 -> Logitech Squeezebox Classic V.3 -> Cambridge Audio DacMagic ->
NAD C160 -> 2 x NAD C272 -> Quad 22L2
------------------------------------------------------------------------
get.amped's Profile: http://forums.slimdevices.com/member.php?userid=10022
View this thread: http://forums.slimdevices.com/showthread.php?t=101600
_______________________________________________
discuss mailing list
discuss@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/discuss
