GB-PVR => NPVR migration; video library missing episode ID information

[sub]

I just took a closer look at npvr.db3 using SQLite Maestro. The output attenuation that kept me from seeing any imported Unique ID data has to do with the fact that the SCHEDULED_RECORDINGS.event_details column shows up typed as varchar(50), even though it ends up longer than that. I tried changing the definition to varchar(500) and was able to see all the data.

This is just a SQLite Maestro display problem. The data is all there.

This reveals another issue: the recordings import preserves all the event strings, so that recording title, subtitle etc. show up as expected. But no corresponding rows are created in the EPG_EVENT table and the embedded event OID is left 0. So as it stands, NPVR can't detect imported duplicates.

NextPVR doesn't need an entry in EPG_EVENT, since it stores a cached copy of the EPG_EVENT in the recording.

In the logs, you should see a "UpdateRecurringRecording" message for your recording, and just you should see a pile of lines saying "- Already has completed episode : xxxxxxxx", where these have come from the EPG_EVENT cached in your recordings.

[bgowland]

I just took a closer look at npvr.db3 using SQLite Maestro. The output attenuation that kept me from seeing any imported Unique ID data has to do with the fact that the SCHEDULED_RECORDINGS.event_details column shows up typed as varchar(50), even though it ends up longer than that. I tried changing the definition to varchar(500) and was able to see all the data.

Really? If that's the case then SQLite Maestro is broken.

VARCHAR (in SQL terms) defines a "textual" column of type VARYING CHARACTER with an initial size allocated to the number in the parentheses. A column created as VARCHAR(50), for example, simply has space pre-allocated for 50 characters but as it's defined as "varying", it can grow beyond that. If SQLite Maestro is only showing you 50 characters when the data is longer than that then it isn't working right. Try using a different SQLite tool instead of modifying the column.

Cheers,
Brian

[Braklet]

In the logs, you should see a "UpdateRecurringRecording" message for your recording, and just you should see a pile of lines saying "- Already has completed episode : xxxxxxxx", where these have come from the EPG_EVENT cached in your recordings.

I'm afraid my NRecord.log has no examples of those "completed episode" events. It doesn't seem to reject any new recordings based on SCHEDULED_RECORDING.event_details. Here's one example I pulled out of my logs and database. Unfortunately it isn't fresh, because I deleted the duplicate recording NPVR tried to schedule. But it is an example of NPVR seemingly not finding a duplicate.

(from NRecord.log)

Code:

2013-02-13 04:09:03.616    [INFO][8]    UpdateRecurringRecording(90, 'Black Dynamite'):
<Rules><EPGTitle>Black Dynamite</EPGTitle><ChannelOID>7218</ChannelOID><ChannelName>TOON</ChannelName><StartTime>0001-01-01T05:00:00.0000000Z</StartTime><EndTime>0001-01-01T05:00:00.0000000Z</EndTime><PrePadding>5</PrePadding><PostPadding>5</PostPadding><Quality>3</Quality><Keep>0</Keep></Rules>
2013-02-13 04:09:03.959    [DEBUG][8]     - recently deleted episode : EP007259620113
[many "deleted episode : XXXXXX" ...]
2013-02-13 04:09:03.975    [DEBUG][8]     - recently deleted episode : EP000173090019
2013-02-13 04:09:04.037    [DEBUG][8]    LoadListingsMatchingTitle(channel=7218,title=Black Dynamite)
2013-02-13 04:09:04.037    [DEBUG][8]     - Potential episode at: 2/16/2013 11:00:00 PM
2013-02-13 04:09:04.037    [DEBUG][8]       ...check IsValid()
2013-02-13 04:09:04.037    [DEBUG][8]       ...already got this episode: [color=#FF0000]EP015841940001 [/color](recently deleted)
2013-02-13 04:09:04.037    [DEBUG][8]       ...already got this episode scheduled

I went digging through SCHEDULED_RECORDING and found the matching episode, here is the event_details column:

Code:

<Event>
      <OID>0</OID>
      <Title>Black Dynamite</Title>
      <SubTitle>Jackson Five Across Yo&apos; Eyes or Just Beat It</SubTitle>
      <Description>[2DVD]</Description>
      <ChannelOID>0</ChannelOID>
      <StartTime>2012-07-16T06:30:00.0000000Z</StartTime>
      <EndTime>2012-07-16T07:00:00.0000000Z</EndTime>
  <UniqueID>[color=#FF0000]EP015841940001[/color]</UniqueID>
    </Event>

I realize this isn't the best example, but if you'd like I can extract data for the next fresh duplicate. Might take a day or two, though.

[Braklet]

Try using a different SQLite tool instead of modifying the column.

Know of one, preferably free?

I tried SQLite Maestro (which I actually paid for), and SQLite Expert. Both truncated display for SCHEDULED_RECORDING.event_details. Very annoying to have to modify the table in order to actually see the data. After I modify with Maestro it can display all data.

[bgowland]

Know of one, preferably free?

I use SQLiteSpy - it's donate-ware but otherwise free to download and use. I haven't really looked at the SCHEDULED_RECORDING unique_id field (we don't get those in the UK although npvr does auto-generate some). I do know that the description field of EPG_EVENT is also a VARCHAR(50) by default and my descriptions are regularly much longer than that. SQLiteSpy displays them fine.

[sub]

I'm afraid my NRecord.log has no examples of those "completed episode" events.

If you zip and attach your npvr.db3, I'll check it out.

[Braklet]

If you zip and attach your npvr.db3, I'll check it out.

Thanks, sub. I'll keep my eyes on the daily EPG updates, and extract some log info once I spot a duplicate. I record some heavily-rerun material so it shouldn't take too long.

Uh oh, forum says NYET:

npvr.zip - Your file of 3.68 MB bytes exceeds the forum's limit of 2.29 MB for this filetype.

Any alternate methods?

[Braklet]

I use SQLiteSpy - it's donate-ware but otherwise free to download and use.

Oh man, GREAT utility! I was all set to whine that it truncated event_details like the others, but then I spotted a data frame at window bottom right and everything was there.

I probably can't use it for most of my DB admin since I rely on the graphic crutches provided by Maestro (hence my paying for it) but for quick examination SQLiteSpy is much better. Thanks for the lead!

[Jaggy]

Uh oh, forum says NYET:

Any alternate methods?

Rapidshare or Dropbox or similar are what is normally recommended

[sub]

Yep, what he said.