PowerShell , VB , Media Center and SQL
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
[Reflection.Assembly]::LoadFile("C:\windows\ehome\ehepg.dll")
$conn = New-Object microsoft.ehome.epg.database.sqlLiteConnection
$conn.init($key.currentEPG , $true)
$conn.open()
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=$conn.CreateCommand()
$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
$sqladap.fill($ds)
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
Technorati tags: Microsoft, Windows, Powershell, Visual Basic, Media Center, MCE
FOOTNOTES
(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”