James O'Neill's Blog

June 17, 2007

PowerShell , VB , Media Center and SQL

Filed under: Music and Media,Powershell,Windows Vista — jamesone111 @ 12:19 pm

I hesitate to describe myself as a dilettante (1) programmer. Not because it isn’t true but because we have a running joke in the office about vocabulary deficiency. Eileen said to someone “What’s it like to be surrounded by People more Erudite (2)  than you “?  I quipped “Don’t say that, he thinks it’s a kind of glue”. A couple of people in the education team were having trouble pronouncing the word Pedagogue (3) (ped-a-gog) never mind knowing what it means. “OK I know obscure words” I said “I heard Stephen Fry on the Radio saying people don’t know the meaning of words like Anthropophagi (4) or Thaumaturgy (5)  and I did ! ” I know the former from Flanders and Swann and the latter from Terry Pratchett

Long words aside, I only really dabble with coding. I’ve become a convert to Media Center. When he did, the Vista after hours event, James Senior had a sidebar gadget which showed the Electronic Program Guide. (EPG). I tracked the gadget  down to Ascii Express, but I’ve found it a bit flaky and I wanted to investigate how you get the data out of media center.  

Eventually I found a post on the Media Center Sandbox Forums which gave a VB sample. Another post there gave me a registry key I’d need and a link to the very useful MCE Query. With this I was able to build up some SQL queries. There are tables with names like Lineups, Services, Programs, Credits, Genres (all plural) and others which join them together with names like “serviceLineups”. So if you want to the Services and Lineups you join those 2 tables with serviceLineups.  The tables all call their primary key “identifier”, and when it’s referred to in is called by name like Service or Lineup (all singular). Here’s an example: a query to get a list of stations, when the guide information starts and finishes, and their assigned channel numbers e.g. Radio stations are on Freeview – the British DVB-T service – have station numbers in the 800 range.

select serviceLineups.service  ,  lineups.displayedNumber  , Services.serviceName, services.callsign, services.startTime, Services.endtime  
from servicelineups , services, lineups
where (
       (serviceLineups.service = services.identifier) and (serviceLineups.Lineup=Lineups.identifier)
        and  (servicelineups.isAvailable = 1) and  (servicelineups.ProgramInfo = 1)
Order by lineups.displayedNumber

This one gives a list of programs – be warned with 14 days of guide and 60+ stations this is about 20,000 rows, so you may want to add a condition –  e.g. a specific value of Station.identifier taken from the ServiceLineups.Service value in the previous one

select programs.identifier , programs.title as programTitle, programs.description as programDescription, 
programs.episodeTitle, programs.year , programs.originalairdate ,
ScheduleEntries.StartTime, ScheduleEntries.Endtime, services.serviceName 
from scheduleentries , services , programs
where (
(services.identifier = scheduleentries.service) and  (programs.identifier = scheduleentries.program)

Once you’ve got the Programs.identifier you can query things like keywords

select keyword, keywordType, keywordIndex from keywords where program=2639 

Your might want to use a where Program = condition on these 4 which get attributes, credits, genres and ratings.

select programattributes.program, attributes.value as attributeValue 
from programattributes , attributes 
where (programattributes.attribute = attributes.identifier)


select programCredits.program, Credits.name as CreditName , Credits.type as CreditType
from   programCredits , Credits
where (programCredits.Credit = Credits.identifier)


select programgenres.program, genres.name as GenreName
from programgenres , genres
where (programgenres.genre = genres.identifier)


select programratings.program, ratings.name as RatingsName
from programratings , Ratings
where (programratings.rating = ratings.identifier)

This gave me what I needed to query the information in power shell. First get the registry Key which tells me where the EPG is

$key = get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Media Center\Service\epg'

Then load the DLL which lets me address it, and get a SQL Lite Database connection, set it up

$conn = New-Object microsoft.ehome.epg.database.sqlLiteConnection
$conn.init($key.currentEPG , $true)

Normally I’d just pass the SQLstring put to make it easier to read I’m building it up here, then I can create a command object with that query to run against the database

$sql = 'SELECT  lineups.displayedNumber  , Services.serviceName '
$sql = $SQL + ' FROM ServiceLineups , Services, Lineups '
$sql = $SQL + ' WHERE  ( '
$sql = $SQL + ' (serviceLineups.service = services.identifier) '
$sql = $SQL + ' and (serviceLineups.Lineup=Lineups.identifier) '
$sql = $SQL + ' and (servicelineups.isAvailable = 1) and  (servicelineups.ProgramInfo = 1) '
$sql = $SQL + ' )   Order by lineups.displayedNumber '
$sqlcomm.CommandText=  $SQL

Now I set up a dataset object and use the Data Adapter object to fill it.

$Ds= new-object system.data.dataset
$sqladap = New-Object microsoft.ehome.epg.database.sqllitedataadapter
$sqladap.SelectCommand = $sqlcomm

And powershell understands what it can do with a table …

$ds.Tables[0] | Format-Table 

Cool stuff. I did pipe the second query above (the big one) into convertTo-Hml as well. Internet explorer sweats a bit loading it, but it comes into Excel nicely. I’ve got to figure out how to put the data to use now I can get it.

Next up … SQL to query vista’s index


(1) Dilettante: “Somebody who is interested in an art or a specialized field of knowledge, but who has only a superficial understanding of it”

(2) Erudite:  “Having or showing great knowledge gained from study and reading”

(3) Pedagogue “an educator or schoolteacher – esp. One who teaches in a particularly pedantic or dogmatic manner”

(4) Anthropophagi A cannibal

(5) Thaumaturgy  “The performance of Miracles or Magic”

This post originally appeared on my technet blog.


Create a free website or blog at WordPress.com.

%d bloggers like this: