2015-03-21, 14:06
A developer I used to work with (S.D. 'The Codemaster') wrote this SQL query so that we could analyze the TV part of our Ember libraries. I made some small changes to it for the latest Ember schema.
I thought it might be of some use to other Ember users. I am using SQLiteSpy to access MyVideos17.emm. No support is provided, use it only if you have a bit of SQL experience!!
@DanCooper, this could be the foundation of a useful 'TV Library Overview' feature
I thought it might be of some use to other Ember users. I am using SQLiteSpy to access MyVideos17.emm. No support is provided, use it only if you have a bit of SQL experience!!
@DanCooper, this could be the foundation of a useful 'TV Library Overview' feature
Code:
-- ## Check the quality of the TV shows
SELECT a.idShow, a.Title, a.status, a.Premiered, max(b.aired) as 'LastAired'
,max(b.season) as 'Seasons', a.source, cast(a.Rating as float) as 'Rating'
,cast((cast(sum(CASE WHEN cast(c.Video_Height as int) >= 700 THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'HD(%)' -- # 1080p/720p count as HD(%)
,sum(CASE WHEN cast(c.Video_Height as int) < 5000 AND cast(c.Video_Height as int) >= 1090 THEN 1 ELSE 0 END) as 'UHD'
,sum(CASE WHEN cast(c.Video_Height as int) < 1090 AND cast(c.Video_Height as int) >= 960 THEN 1 ELSE 0 END) as '1080p'
,sum(CASE WHEN cast(c.Video_Height as int) < 960 AND cast(c.Video_Height as int) >= 880 THEN 1 ELSE 0 END) as '900p/HD+'
,sum(CASE WHEN cast(c.Video_Height as int) < 880 AND cast(c.Video_Height as int) >= 700 THEN 1 ELSE 0 END) as '720p'
,sum(CASE WHEN cast(c.Video_Height as int) < 700 AND cast(c.Video_Height as int) >= 300 THEN 1 ELSE 0 END) as 'SD'
,sum(CASE WHEN cast(c.Video_Height as int) < 300 THEN 1 ELSE 0 END) as 'Low SD'
,sum(CASE WHEN c.tvepid is null THEN 1 ELSE 0 END) as 'Missing'
,cast((cast(sum(CASE WHEN c.tvepid is null THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'Missing(%)'
,sum(CASE WHEN c.tvepid is not null THEN 1 ELSE 0 END) as 'In Library'
,cast((cast(sum(CASE WHEN c.tvepid is not null THEN 1 ELSE 0 END) as float)/count(*))*100 as int) as 'In Library(%)'
,count(*) as 'Total(In Library&Missing)'
,min(cast(c.Video_Width as int)) as 'MinWidth', max(cast(c.Video_Width as int)) as 'MaxWidth'
,min(cast(c.Video_Height as int)) as 'MinHeight', max(cast(c.Video_Height as int)) as 'MaxHeight'
,cast(avg(Video_Duration)/60 as integer) as 'AverageRuntime'
FROM TVShow a
LEFT OUTER JOIN (select idShow, min(aired) as aired from episode where aired > date('now') group by idShow) x on x.idShow = a.idShow,
episode b LEFT OUTER JOIN TVVStreams c on b.idEpisode = c.tvepid
WHERE a.idShow = b.idShow
AND (c.streamid = 0 OR c.streamid is null) -- # Note: Only include the first video stream or missing episodes (null)
AND b.season > 0 -- # Note: comment this line to include Specials (Season 0)
AND b.aired != '' AND b.aired <= date('now') -- # Note: comment this line to get all episodes, not just those that have been aired already
GROUP BY a.Title, a.Premiered, a.Rating
ORDER BY a.Title asc