mherger wrote:
> > I've been using SQLite Expert to chase down some tagging consistency
> > issues. OCD enabler?
>
> OCD enabler? http://de.pinterest.com/dkniep/ocd-enabler/ ?
>
> > I'll put together some queries that would result in only FLAC,
> > non-Classical, non-Pop + whatever else it takes to pare things down
> to
> > less than 100K tracks. Even though the filtering is based on tracks,
> > joining with the genres table should not be an issue?
>
> The problem is that the track -> genre mapping is based on a genre_id
> which changes during scan. You'll therefore have to have some sub-select
>
> to get the ids based on the genre names you provide. It's probably not
> an issue if you speak SQL fluently :-).
>
> --
>
> Michael
Well I did a couple of queries to get some idea just what would fit in
100K tracks. This one just gives me a count of tracks by genre for
lossless (FLAC) only:
SELECT
ge.namesearch, count (tr.id)
FROM
tracks tr,
genre_track gt,
genres ge
WHERE tr.content_type = 'flc'
AND tr.id = gt.track
AND gt.genre = ge.id
GROUP BY ge.namesearch
ORDER BY ge.namesearch
Unfortunately, ROLLUP is not supported by SQLite, so I can't get a grand
total in the same query.
But I put the results (134 rows) into Excel and played with different
combinations of genres until I got one that pares the 233,143 tracks
down to less than 100,000.
This query returns those rows:
SELECT
tr.*
FROM
tracks tr,
genre_track gt,
genres ge
WHERE tr.content_type = 'flc'
AND tr.id = gt.track
AND gt.genre = ge.id
AND ge.namesearch IN ('ALTERNATIVE ROCK','ART ROCK','BLUES
ROCK','DEAD','ELECTRONICA','FOLK JAZZ','FUNK','FUNK R B','FUNK
SOUL','FUSION','GRUNGE','HENDRIX','INDIE','JAZZ FOLK','JAZZ FUNK','JAZZ
FUSION','JAZZ ROCK','LATIN ROCK','NEW WAVE','PROGRESSIVE
METAL','PROGRESSIVE ROCK','PUNK ROCK','R B','R B FUNK','R B
SOUL','ROCK','SOUL','SOUL FUNK','SOUL R B','TRIP HOP')
It's not clear to me whether the query would have to be structured like
this to deal with the changing genre ids:
SELECT
tr.*
FROM
tracks tr,
genre_track gt,
genres ge
WHERE tr.content_type = 'flc'
AND tr.id = gt.track
AND gt.genre = ge.id
AND ge.id IN (SELECT x.id from genres x where x.namesearch IN
('ALTERNATIVE ROCK','ART ROCK','BLUES ROCK','DEAD','ELECTRONICA','FOLK
JAZZ','FUNK','FUNK R B','FUNK
SOUL','FUSION','GRUNGE','HENDRIX','INDIE','JAZZ FOLK','JAZZ FUNK','JAZZ
FUSION','JAZZ ROCK','LATIN ROCK','NEW WAVE','PROGRESSIVE
METAL','PROGRESSIVE ROCK','PUNK ROCK','R B','R B FUNK','R B
SOUL','ROCK','SOUL','SOUL FUNK','SOUL R B','TRIP HOP'))
Or if there is any substantive difference between the two.
Win7Pro(x64)[3.3Ghz i5, 8GB RAM, 120GB SSD system, 15TB storage], LMS
7.9.0 -> 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=101701
_______________________________________________
beta mailing list
beta@lists.slimdevices.com
http://lists.slimdevices.com/mailman/listinfo/beta
