first_run usage

[janoonk]

Hi,

For example on Discovery you have a show called "Gold rush: White water" where same episode is aired multiple times. I want to prevent duplicate recording so i set my recording rule to "Record Series (NEW episodes)".
That didn't work because the column first_run for all EPG_EVENT data is always set to 'Y' and is not updated. I am using the EPG data from DVB-C from my provider.
Since it was not set I thought let's make a config/PostLoadEPG.sh script which runs a complex .sql statement that uses the episode-num in the metadata of the description and checks the start_time for episodes having the same episode-num and then updates the first_run column, this works. But I ran into a problem because every 24 hours all EPG data is deleted from EPG_EVENT table including old events (laying in the past), so my previously and correctly first_run='N' episode is now updated by my SQL query back to first_run='Y' and stays Y until aired.

Can I somehow retain old EPG_EVENT data for some days before being deleted? Or do I need to save a copy of all first_run='N' instances?
I do wonder how this `first_run` can be used reliably when its not set by EPG data directly.

[Bobins]

Obvious question,

Have you ticked the "Avoid duplicates where possible" box on the General Settings page?

Ray.

[janoonk]

Obvious question,

Have you ticked the "Avoid duplicates where possible" box on the General Settings page?

Ray.

yes it's ticked

[mvallevand]

This value is set by the EPG source. If the guide data doesn't include S/E information or provide a unique ID there is no easiy way to avoid duplicates.

If NextPVR is setting the unique id then the setting you need to changes is <DVBAssumeNew>true</DVBAssumeNew> to false. You also need <ShowNewInGuide>true</ShowNewInGuide> as false to control the visible display.

For some guide data NextPVR is able to parse the S/E from the description, I am not sure about your source. The major issue is this might not be reliable for every station of every channel and it up to you to understand this. If you have a reliable way of setting the unique ID or the first run flag run your script in PostLoadEPG which runs before the recordings are created.

If this is important to you maybe try Schedules Direct. It is available in the Nederlands and in many countries as very good unique ID process.

Martin

[janoonk]

This value is set by the EPG source.  If the guide data doesn't include S/E information or provide a unique ID there is no easiy way to avoid duplicates. 

If NextPVR is setting the unique id then the setting you need to changes is  <DVBAssumeNew>true</DVBAssumeNew> to false.  You also need  <ShowNewInGuide>true</ShowNewInGuide> as false to control the visible display.

For some guide data NextPVR is able to parse the S/E from the description, I am not sure about your source.  The major issue is this might not be reliable for every station of every channel and it up to you to understand this.  If you have a reliable way of setting the unique ID or the first run flag run your script in PostLoadEPG which runs before the recordings are created.

If this is important to you maybe try Schedules Direct.  It is available in the Nederlands and in many countries as very good unique ID process.

Martin

Well the EPG guide from my DVB-C source does provide episode-num info which I use to set first-run correctly. NextPVR then works correctly when using Record only NEW episodes.
The only problem is that every day all epg data is removed and refreshed, after which my postloadepg update sql query kicks in, but since it can happen that the first episode lays in the past and then gets removed from the epg data and then my update SQL query sees the duplicate first episode incorrectly as new (first-run='Y').
I guess I have to persist the first-run='N' records somewhere after running my update query. And then the next day after runnning my update query use the persisted data to set the first-run field of some records back to 'N'.

[mvallevand]

You need more than just S/E information to set the new flag We might need to see a copy of the raw npvr.db3 data (before running your script) to understand what you mean by New since by definition if a show is new on Monday the same show can't be new on Tuesday and you shouldn't need to keep any history.

Maybe the issue isn't New but recording duplicates of the same S/E Maybe you aren't setting the unique id. For example if the same show is repeated during the day, it is the unique id that prevents duplicates not the new flag.

Martin

[BrettB]

You need more than just S/E information to set the new flag We might need to see a copy of the raw npvr.db3 data (before running your script) to understand what you mean by New since by definition if a show is new on Monday the same show can't be new on Tuesday and you shouldn't need to keep any history.

Maybe the issue isn't New but recording duplicates of the same S/E Maybe you aren't setting the unique id. For example if the same show is repeated during the day, it is the unique id that prevents duplicates not the new flag.

Re-reading this thread more carefully, I now believe that the OP isn't actually trying to set the "New" flag to correctly reflect "New" shows (as defined/expected by the guide sources and NextPVR). Rather, it seems that OP is trying to manually set it to reflect Yes if a show instance is the first (chronologically) airing of that particular episode in the current set of all guide listings.

But, OP is doing this to try to implement the "avoid duplicate recordings" function so that they don't get multiple recordings of the same episode. They don't mention whether or not their "complex .sql statement" is changing the unique ID or not. I suspect since they came up with this elaborate work-around that their guide data is not generating useful Unique IDs. If it was, then they wouldn't have needed to do any of this.

@janook: Rather than trying to get your "New" flag work-around to do what you want, I would suggest that you focus on getting the Unique IDs working so that you can use the functionality built-in with NextPVR to avoid recording duplicate episodes. If you would like assistance with that effort, your best bet would be to post a link to a copy of your NextPVR.db3 file somewhere (Google drive, OneDrive, etc.). That would allow us to see what Unique ID values are being received/generated and stored in your database. (Also, if your script is changing the Unique IDs, then you should post a copy of the database with the raw data as received by NextPVR not one after you have manipulated the data.)

[janoonk]

The EPG data from my DVB-C is not giving me info about New shows. Only at the end of description some meta data [...] episode-num is placed. For me "New" is defined as first airing of some episode-num (often <season> <episode> numbers) because often the same episode-num for a show is run multiple times per week.

<<Re-reading this thread more carefully, I now believe that the OP isn't actually trying to set the "New" flag to correctly reflect "New" shows (as defined/expected by the guide sources and NextPVR). Rather, it seems that OP is trying to manually set it to reflect Yes if a show instance is the first (chronologically) airing of that particular episode in the current set of all guide listings. >>

Indeed!

<<But, OP is doing this to try to implement the "avoid duplicate recordings" function so that they don't get multiple recordings of the same episode. They don't mention whether or not their "complex .sql statement" is changing the unique ID or not. I suspect since they came up with this elaborate work-around that their guide data is not generating useful Unique IDs. If it was, then they wouldn't have needed to do any of this.>>

I dont know which unique ID you mean? EPG_EVENT.oid ?
My description for a TV show serie looks like this:
"Een oude, grootse Egyptische handelsstad verdween in de 2e eeuw v.Chr. Nu wordt deze metropolis in alle glorie hersteld. Met de nieuwste technologie wordt het ontstaan en de val van de stad onthuld. [episode-num: 4/10 3, date: 2020, length: 60 minutes, country: AU]"

Sometimes the episode-num has a slightly different format like:
"De 11.000 kilometer lange snelwegen van Nieuw Zeeland behoren tot de spectaculairste en zwaarste wegen op aarde. Ruim 1000 mensen zorgen dat de wegen veilig zijn. Dat komt met gevaren, en beloningen. [episode-num: 2 7, date: 2021, length: 25 minutes, country: NZ]"

My update SQL query that's run during PostLoadEPG.sh:

Code:

WITH epg_with_epnum AS (
  SELECT
    oid,
    title,
    channel_oid,
    description,
    -- Remove trailing metadata in square brackets
    TRIM(
      CASE
        WHEN description LIKE '%[%' THEN
          SUBSTR(description, 1, INSTR(description, '[') - 1)
        ELSE
          description
      END
    ) AS clean_description,
    start_time,
    -- Extract episode-num from description
    TRIM(
      RTRIM(
        SUBSTR(
          description,
          INSTR(description, 'episode-num:') + LENGTH('episode-num:'),
          INSTR(SUBSTR(description, INSTR(description, 'episode-num:')), ',') - LENGTH('episode-num:')
        ),
        ', '
      )
    ) AS episode_num
  FROM EPG_EVENT
  WHERE trim(description) LIKE '%[%episode-num:%]'  --metadata at the end of description and episode-num somewhere inside
),

first_airing AS (
  SELECT
    channel_oid,
    episode_num,
    clean_description,
    MIN(start_time) AS first_start
  FROM epg_with_epnum
  GROUP BY channel_oid, episode_num, clean_description
)

UPDATE EPG_EVENT
SET first_run = 'N'
WHERE oid IN (
  SELECT e.oid
  FROM epg_with_epnum e
  JOIN first_airing f
    ON e.channel_oid = f.channel_oid
      AND e.episode_num = f.episode_num
      AND e.clean_description = f.clean_description
  WHERE e.start_time != f.first_start
); 

Here is a copy of my database: https://drive.google.com/file/d/11-lrw3M...sp=sharing

[mvallevand]

There is not much for first run, but you database has good S/E and movie year information which can be used to create a unique id and it would be best for regex parsing.

Try this, it is a bit hacky but might serve your purposed

Code:

update epg_event
set unique_id = title || '-' || substring(description,instr(description,'episode-num:')+13,8)
where description like '%episode-num:%'

Martin

[janoonk]

There is not much for first run, but you database has good S/E and movie year information which can be used to create a unique id and it would be best for regex parsing.
Try this, it is a bit hacky but might serve your purposed

Code:

update epg_event
set unique_id = title || '-' || substring(description,instr(description,'episode-num:')+13,8)
where description like '%episode-num:%'

Martin

I totally missed that `unique_id column` in EPG_EVENT table, besides not knowing what to do with it 
thanks for pointing me in right direction, will try this much simpler solution. Although i already see this query won't work but i will take some parsing logic from my SQL above and apply it to your query.

Episiode-num contains episode and season. And in theory can be anywhere in metadata. All keyvalue pairs are , separated

Consider these special cases:

Notice the 1 character difference in metadata in description (rerun is 45 minutes long vs 46 minutes):

|name  |title   |start_time |episode_num|description                                                                                                                                                                                                        |
|-------|--------------|--------------------|-----------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|NPO 1  |Tijd voor MAX |2025-06-03 15:10:00 |105/188 17 |Martine van Os en Sybrand Niessen ontvangen elke werkdag een keur aan gasten. Een aangename mix van nieuws, kunst, cultuur en entertainment. [episode-num: 105/188 17, date: 2025, length: 46 minutes, country: NL]|
|NPO 1  |Tijd voor MAX |2025-06-04 09:15:00 |105/188 17 |Martine van Os en Sybrand Niessen ontvangen elke werkdag een keur aan gasten. Een aangename mix van nieuws, kunst, cultuur en entertainment. [episode-num: 105/188 17, date: 2025, length: 45 minutes, country: NL]|

Some channels cut an event into multiple parts/segments like a WK Soccer match:

|name    |title   |start_time   |episode_num|description                                                                                                                                                                                  |
|--------|--------------------------------------------|-------------------|-----------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|NPO 3 SD|Voetbal - WK-kwalificatie: Nederland - Malta|2025-06-10 18:20:00|2/8 25   |Voorbeschouwing op de WK-kwalificatiewedstrijd Nederland - Malta. De wedstrijd wordt gespeeld in de Euroborg in Groningen. [episode-num: 2/8 25, date: 2025, length: 20 minutes, country: NL]|
|NPO 3 SD|Voetbal - WK-kwalificatie: Nederland - Malta|2025-06-10 18:40:00|2/8 25   |Rechtstreeks verslag van de eerste helft van de WK-kwalificatiewedstrijd Nederland - Malta. [episode-num: 2/8 25, date: 2025, length: 62 minutes]                                            |
|NPO 3 SD|Voetbal - WK-kwalificatie: Nederland - Malta|2025-06-10 19:42:00|2/8 25    |Analyse van de eerste helft van de WK-kwalificatiewedstrijd Nederland - Malta. [episode-num: 2/8 25, date: 2025, length: 8 minutes, country: NL]                                            |
|NPO 3 SD|Voetbal - WK-kwalificatie: Nederland - Malta|2025-06-10 19:50:00|2/8 25    |Rechtstreeks verslag van de tweede helft van de WK-kwalificatiewedstrijd Nederland - Malta. [episode-num: 2/8 25, date: 2025, length: 70 minutes]                                            |
|NPO 3 SD|Voetbal - WK-kwalificatie: Nederland - Malta|2025-06-10 21:00:00|2/8 25    |Nabeschouwing van de WK-kwalificatiewedstrijd Nederland - Malta. [episode-num: 2/8 25, date: 2025, length: 25 minutes, country: NL]                                                          |