Accessing the guide data database file?

[jriker1]

As a lot of us know there are times we lose access to guide data and the countdown begins. After the last 3 or 4 times this happens my family has become obsessed with checking how much guide data is downloaded. So obsessed they have repeating calendar entries to make sure they don't forget. I have a monitoring system at home for all my systems and was thinking I could maybe see the latest date of the guide data. The physical guide data not the file date. Then parse the file and find out the latest guide entry. If it is less than say 8 days alert. I have determined in my case I guess the data is in a file called mcepg2-7.db? Varies I know per system. I can not access this file and although I heard it was SQLite based can't find a way to access it. Found an article for powershell but required C:\windows\ehome\ehepg.dll to access it. Any thoughts on how to access and query the tables in this file to see what the latest guide data date is? Or other method?

Thanks.

JR

[glugglug]

HKLM\Software\Microsoft\Windows\CurrentVersion\Media Center\Service\Epg\endTime controls how far to the right you can scroll in the guide and should be updated when the guide data is imported.

Other than that I think you would need to loop through the ScheduleEntries using mcepg.dll.

[jriker1]

Thanks for the reply. OK so in powershell I try:

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

Loading the dll:
[Reflection.Assembly]::LoadFile("C:\windows\ehome\mcepg.dll")

Try making a connection:
$conn = New-Object microsoft.ehome.epg.database.sqlLiteConnection

I get in PowerSHell:

New-Object : Cannot find type [microsoft.ehome.epg.database.sqlLiteConnection]: make sure the assembly containing this
type is loaded.
At line:1 char:19
+ $conn = New-Object <<<< microsoft.ehome.epg.database.sqlLiteConnection
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentException
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Is microsoft.ehome.epg.database.sqlLiteConnection no longer valid?

Thanks.

JR

EDIT: As a side note, thanks for the registry link. Curious how reliable that is. I know when my guide stops updating I can still scroll thru pages of No Data Available screens that displays on each channel.

[glugglug]

I don't know where you are getting that microsoft.ehome.epg.database.sqlLiteConnection class name from.
You'll also need mcstore.dll for the ObjectStore.

What you want (in C#) is something more like this:

Code: Select all

using Microsoft.MediaCenter.Guide;
...
DateTime GetLatestStartTime() {
  ScheduleEntries entriesInStore = new ScheduleEntries(Microsoft.MediaCenter.Store.ObjectStore.DefaultSingleton);
  DateTime latestStartTime = new DateTime(0);
  using (StoredObjectsEnumerator<ScheduleEntry> enumerator = entriesInStore.GetStoredObjectsEnumerator()) {
    do {
      ScheduleEntry entry = enumerator.Current;
  
       // This is either a "deleted" entry or one of those "No data available" default entries.
      if (entry.Service == null || entry.Program == null) continue;  

      if (entry.startTime > latestStartTime) latestStartTime = entry.startTime;
    } while (enumerator.MoveNext());
  }
  return latestStartTime;
}

[jriker1]

OK, got VS C# installed, gonna be fun. My degree is in C++ however I've been out of the game (management ugh!!!) for a while. Loaded in the appropriate DLL's for MediaCenter.Guide and Store. You example above causes a lot of errors but assuming it's just a conceptual so will need some work or I'm placing it in the wrong part of the project.

Was curious. Is the scheduled shows dynamic when you scroll thru them or is that stored in the DB or that backup folder? So I have a list of shows I want to record, and can go to what's coming up and see the last 10 days of shows I want to record with shows listed in the guide it matched. That tends to be the most accurate list as once the guide goes blank, even if there are entries for the next 10 days that say no guide data available, the "scheduled shows" will only show until the last physical day there is actual guide data.

[glugglug]

Everything must be in the DB, but I don't know of a way to query it directly instead of going through the objectstore/mcepg stuff.

Based on how the StoredObjectsEnumerator<ScheduleEntry>.Seek gets used, the ScheduleEntrys have a primary key formed by the combination of Service UID and startTime, so the enumerator can be seeked to the beginning of the time window you are looking at for each channel viewed in the guide when you are scrolling through.

[jriker1]

No problem, I'm good with that if I can figure it out. I know when what you provided goes thru the do loop, I end up getting a wonderful NullReferenceException. By the way, on the below I am just having the program return the current date time now just so it does something but having it run thru the code you provided as well until it doesn't error in runtime before I try to return anything from it. I have the db file but my computer is not the actual mediacenter so technically has no guide data but the file is still 300k. EDIT: No, tried on the mediacenter PC same deal. Here is what I have right now.

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.MediaCenter.Guide;
using Microsoft.MediaCenter.Store;

namespace ConsoleApplication1
{
    class Program
    {
        
    static void Main()
        {
            DateTime ddd = GetLatestStartTime();
            Console.WriteLine(ddd);
        }
               static DateTime GetLatestStartTime()
                {
            ScheduleEntries entriesInStore = new ScheduleEntries(Microsoft.MediaCenter.Store.ObjectStore.DefaultSingleton);
            DateTime latestStartTime = new DateTime(0);
            using (StoredObjectsEnumerator<ScheduleEntry> enumerator = entriesInStore.GetStoredObjectsEnumerator())
            {
                do
                    { 
                        ScheduleEntry entry = enumerator.Current;
                    
                        // This is either a "deleted" entry or one of those "No data available" default entries.
                        if (entry.Service == null || entry.Program == null) continue;

                        if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
                } while (enumerator.MoveNext());
            }

            DateTime datetime = DateTime.Now;
            return datetime; }  
         }
    }

[glugglug]

Important gotcha:

the WMC libraries are .NET 2.0

By default, current versions of Visual Studio want to use 4.6. The standard solution to this you'll find many places on the net is to have this in your app.config:

Code: Select all

<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

But you'll notice this says supportedRuntime version="4.0".
If your project is set to use 4.5 or 4.6 but this says 4.0, it uses the 2.0 libraries in a weird broken way, seeing an empty ObjectStore. Have to set the app.config and your selected project runtime libraries to match each other (in all configurations).

[jriker1]

Yeah I noticed that at one point so is included slightly different and giving that null error assuming my implementation of your code is even right. This is what's in my .exe.config file that apparently I have to include no matter where I copy the executable at:

Code: Select all

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup useLegacyV2RuntimeActivationPolicy="true"> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
</configuration>

Something in this block is causing the object reference not set to an instance of an object:

Code: Select all

                do
                    {
                     ScheduleEntry entry = enumerator.Current;
                    Console.WriteLine(entry.EndTime);
                        // This is either a "deleted" entry or one of those "No data available" default entries.
                        if (entry.Service == null || entry.Program == null) continue;

                        if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
                } while (enumerator.MoveNext());

JR

EDIT:

I switched the do while into a while and used movenext and not erroring. Oddly the WriteLine(entry.EndTime) statement never prints anything. Is this accessing the database?

[glugglug]

If you have no guide data maybe entry is null?

Try making it a while do instead of do/while -- enumerator.MoveNext should return false in that case and since the date is part of the key, the first entry should never be what your are looking for.

Just saw your edit. It should be printing a lot. No output from that means either the guide db is empty or its still doing that weird stuff from the .NET version mismatch. I usually just target .NET 4.0 because it's more likely to be on every machine.

[jriker1]

Think our posts or my edit crossed your message. See the last part of my last message. Hopefully getting close. There is a pause after it outputs the datetime I have listed, but the writeline never shows anything. So code right now.

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.MediaCenter.Guide;
using Microsoft.MediaCenter.Store;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
        {
            DateTime ddd = GetLatestStartTime();
            Console.WriteLine(ddd);
        }
               static DateTime GetLatestStartTime()
                {
            ScheduleEntries entriesInStore = new ScheduleEntries(Microsoft.MediaCenter.Store.ObjectStore.DefaultSingleton);
            DateTime latestStartTime = new DateTime(0);
            using (StoredObjectsEnumerator<ScheduleEntry> enumerator = entriesInStore.GetStoredObjectsEnumerator())
            {
                while (enumerator.MoveNext())
                    {
                      ScheduleEntry entry = enumerator.Current;
                      Console.WriteLine(entry.EndTime);

                        // This is either a "deleted" entry or one of those "No data available" default entries.
                        if (entry.Service == null || entry.Program == null) continue;

                        if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
                } 
            }

            DateTime datetime = DateTime.Now;
            return datetime; }  
         }
    }

Interesting. I adding an incrementing variable and console writeline'd it and nothing ever showed. Guess I can't see it within the method. That said, if I tell it to return latestStartTime I get 1/1/0001 12:00:00AM which is obviously not valid.

EDIT: Also if I tell it to return the count of the incrementing variable instead of the date it returns only "1" so it never loops anything.

[glugglug]

Sounds like it is opening an empty guide db instead of the real one. Maybe try .net 4.0 setting in both the project settings and app.config?

Also, I think since you aren't actually relying on the ScheduleEntrys being sorted you can do

foreach(ScheduleEntry entry in entriesInStore) {...}
Instead of using the enumerator.

[jriker1]

I think we are getting somewhere. Note haven't touched your foreach suggestion yet. I switched from .net 4.5.2 to .net 4.0 and after clearing up a couple errors about assemblies it couldn't find in the project, the app looped thru 249,826 entries in the database. Oddly it's not returning anything. When I had the "Console.WriteLine(entry.EndTime);" in the while loop, it output "1/1/0001 12:00:00 AM" 249,827 times.

If I clear out the screen output and just go after what your original code was suggesting still get 1/1/0001 12:00:00 AM. Here is where the code is right now.

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.MediaCenter.Guide;
using Microsoft.MediaCenter.Store;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main()
          {
            DateTime ddd = GetLatestStartTime();
            Console.WriteLine(ddd);
          }

        static DateTime GetLatestStartTime()
          {
            ScheduleEntries entriesInStore = new ScheduleEntries(Microsoft.MediaCenter.Store.ObjectStore.DefaultSingleton);
            DateTime latestStartTime = new DateTime(0);

            using (StoredObjectsEnumerator<ScheduleEntry> enumerator = entriesInStore.GetStoredObjectsEnumerator())
              {
                while (enumerator.MoveNext())
                  {
                    ScheduleEntry entry = enumerator.Current;

                    // This is either a "deleted" entry or one of those "No data available" default entries.
                    if (entry.Service == null || entry.Program == null) continue;

                    if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
                  } 
              }
            return latestStartTime;
          }  
    }
}

Note the entry.StartTime variable comparison in the code is always pulling no data as it's also 1/1/0001... Same thing if I switch to a foreach loop but didn't think that would change that.

[jriker1]

Oh yeah, and it probably should be noted if it makes a difference, this is Windows 7 not 8.1. If that matters from a connection perspective. See some people commenting about For Windows 7 download this tool and 8.1 this other one for other WMC related stuff.

[jriker1]

Oh and also I'm running 64-bit. Saw this thread that you may have referenced before glugglug

https://social.msdn.microsoft.com/Forum ... netfx64bit

Talks about issues with returning all zero data with 64-bit but not 32-bit. Seemed related to the method ObjectStore logged it?

And in case it wasn't noticed, for some reason the few references out there to "startTime" are lowercase 's" where mine comes up as upper case "S" as "StartTime". Same with endTime/EndTime.

[glugglug]

The private member variables in the scheduleEntry which I think are populated by loadmxf thru reflection are lowercase. The accessor properties for them we should be using are capital.

Doesn't make sense that you would see them as 0 (Jan 1 0001) when enumerating them.

They also have a ToString method that Console.Writeln knows how to use which will include both start time, end time, and a program title.

[jriker1]

Thanks for the info. Appreciate your help. Guessing all the 01/01.... entries are from the initialization of the variable and the fact that it's probably just returning a bunch of null data setup (just a guess) so is never populated.

Console.WriteLine(entry.StartTime.ToString); returns a can't convert a method group to bool. Without the enumerator below is where I'm at. Also has the WriteLine statement but won't compile right now with it in there. Curious of a couple things.

1. Is there a way thru code to tell what db file this app is accessing? Assuming with all the empty/null entries being returned it's probably hitting the right one as my other computer that doesn't have a tuner returns just one null entry as MCE isn't active but not sure?
2. Is there a way to tell if there is a failure to open the database for read?
3. Not sure if you have an environment to test this but does what I've done work in your environment if you still have one?

Thanks.

JR

Code: Select all

using System;
using Microsoft.MediaCenter.Guide;

namespace ConsoleApplication1
  {
    class Program
      {
        static void Main()
          {
            DateTime ddd = GetLatestStartTime();
            Console.WriteLine(ddd);
          }

 
        static DateTime GetLatestStartTime()
          {
            ScheduleEntries entriesInStore = new ScheduleEntries(Microsoft.MediaCenter.Store.ObjectStore.DefaultSingleton);

            DateTime latestStartTime = new DateTime(0);
            foreach (ScheduleEntry entry in entriesInStore)
              {
                Console.WriteLine(entry.StartTime.ToString);
                // This is either a "deleted" entry or one of those "No data available" default entries.
                if (entry.Service == null || entry.Program == null) continue;

                    if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
              }
            return latestStartTime;
          }  
      }
  }

[jriker1]

OK the 64-bit thing was the problem. Output is a bit slow at 25 seconds to run, and still like to understand my 1 and 2 above so I can put in appropriate checks and what it's checking, but here's the final code that made it work. Essentially had to force the opening of the database with username data. Odd it was looping thru something but had no access apparently.

Code: Select all

using System;
using System.Text;
using Microsoft.MediaCenter.Guide;
using Microsoft.MediaCenter.Store;
using System.Security.Cryptography;

namespace ConsoleApplication1
  {
    class Program
      {
        static void Main()
          {
            DateTime ddd = GetLatestStartTime();
            Console.WriteLine(ddd);
          }

 
        static DateTime GetLatestStartTime()
          {
            string s = "Unable upgrade recording state.";
            byte[] bytes = Convert.FromBase64String("FAAODBUITwADRicSARc=");

            byte[] buffer2 = Encoding.ASCII.GetBytes(s);
            for (int i = 0; i != bytes.Length; i++)

            {
                bytes[i] = (byte)(bytes[i] ^ buffer2[i]);
            }

            string clientId = Microsoft.MediaCenter.Store.ObjectStore.GetClientId(true);
            SHA256Managed managed = new SHA256Managed();
            byte[] buffer = Encoding.Unicode.GetBytes(clientId);
            clientId = Convert.ToBase64String(managed.ComputeHash(buffer));
            string FriendlyName = Encoding.ASCII.GetString(bytes);
            string DisplayName = clientId;
            Console.WriteLine(FriendlyName);
            Console.WriteLine(DisplayName);
            ObjectStore TVstore = Microsoft.MediaCenter.Store.ObjectStore.Open("", FriendlyName, DisplayName, true);

            ScheduleEntries entriesInStore = new ScheduleEntries(TVstore);

            DateTime latestStartTime = new DateTime(0);
            foreach (ScheduleEntry entry in entriesInStore)
              {
                // This is either a "deleted" entry or one of those "No data available" default entries.
                if (entry.Service == null || entry.Program == null) continue;

                    if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
              }
            return latestStartTime;
          }  
      }
  }

[jriker1]

Thought this

Code: Select all

                if (!(entry.Service == null || entry.Program == null))
                  {
                    if (entry.StartTime > latestStartTime) latestStartTime = entry.StartTime;
                  }

might speed it up but didn't seem to do anything.

[glugglug]

1. Is there a way thru code to tell what db file this app is accessing? Assuming with all the empty/null entries being returned it's probably hitting the right one as my other computer that doesn't have a tuner returns just one null entry as MCE isn't active but not sure?

There is a property of the ObjectStore, I forget the name exactly, that tells you the path to the DB.

2. Is there a way to tell if there is a failure to open the database for read?

I think that would have thrown an exception?

The code in your latest post is getting an administrative object store, which I didn't think was required for reading the DB only. I've had problems updating things before I started using that but not reading, rather surprised it would be needed to read the ScheduleEntries...

The 25 seconds sounds about right, as it's looping through the entire schedule of every channel. You can probably terminate the loop when the entry.Service.Id property changes from one entry to the next (once you are past the first entry with a service assigned) to look at one channel instead of all of them and get the right result, or if you want to get really fancy, the StoredObjectsEnumerator has a Seek method to jump to an element near a specific key, where the ScheduleEntry key are composed of the service id and the date/time, so you could try to jump to the end of the data for a channel instead of looping through it all. The semantics of the Seek are a bit confusing though, and from some of the stuff I've seen in my alternate MXF import attempts, I think it is a bit buggy and the BeforeEQ seek type used by loadmxf sometimes returns one entry off from what you would expect. the "After" seek type using a 0 date is finding the beginning of a channel's data predictably for me though, and maybe just "Before" instead of "BeforeEQ" could be used to find the last entry of a channel, using a date far in the future.