Recording Existing Episode Already Present

[Carlo 4561]

Hitting a snag in setting up series for DVR recordings.

I've got a couple of TV show Series that seem to want to schedule/record basically every episode and I'm only missing a couple of episodes from each series. I of course have the option checked to not record existing items.

 

I have a feeling it might be a "mismatch" of the show name but I'm not certain.  A couple example shows I'm having this issue with are The Office (us version) & Law & Order: Criminal Intent.  The XMLTV listing has both the series and the episode and they match (I have them).

 

Anyone have any ideas what to check?

Edited March 22, 2018 by cayars

[Carlo 4561]

After much playing with this and running some SQL against the database I found that if I rename "The Office (US)" to "The Office" in my library that fixes this type of issue.

[Carlo 4561]

@@Luke

 

Playing around a bit to see what can be done for EPG series setup to record that don't have season or episode numbers in the XMLTV listing.

I used "The Three Stooges" as an example since it's a problem series for me.

 

If I shutdown, run the below SQL then restart Emby this series will now have the proper season and episode numbers.  However I then have to do something like uncheck "Don't record episodes that are already in my library" and recheck it to get Emby to recalculate the schedule for this show.

 

I've got 3 or 4 shows like this that don't come through with proper season/episode numbers and it's a pain to have them in my recording schedule as they will hog the tuners for episodes I already have which is a waist of resources and processing time after they are recorded (cutting commercials and converting to H.264).

 

Three Questions:

1) Is there a way I can easily automate this?  I found I can't update the DB while Emby is running via the command line and then tell Emby to recalculate the recording schedules?  I'm aware I could change the info in the xml file but prefer to work in DBs and the info is already there.

 

2) Any chance you can populate these missing season/episode numbers during insert into the DB so none of this would be needed?

 

3) Why does LiveTv.LiveTvProgram type use different fields than Entities.TV.Episode does to store Show name and episode name?

for example in the where clause I had to match Name to SeriesName depending on type and also EpisodeTitle to Name

Name contains a series for one but an episode for the other type.

 

Thanks,

Carlo

Edited March 28, 2018 by cayars

[Carlo 4561]

There is also the other issue of season/episode numbers not matching.  Example:

 

Naked and Afraid Season 6 Episode "Strength in Pain"

Zap will have it as episode 13 while our database will have it listed as 11.

 

There are lots of mismatches like that between the EPG providers and imdb or tvdb.

This means we'll either have wrong descriptions in our listing (if recorded, processed and added to a lib) or not be able to record missing shows.

 

The above episode wants to record even though I have "S06E11 Strength in Pain on disk.  There is no season 13.

 

We need to have a way of properly matching our EPG data to our DB or vice versa.  If I don't manually look at what's scheduled to record each day to remove dupes I'd easily record 10 shows I don't need/want.  But more importantly, I'll never get missing shows recorded unless the season/episode/name completely matches.

 

Carlo

Edited March 23, 2018 by cayars

[Carlo 4561]

removed

Edited March 28, 2018 by cayars

[Carlo 4561]

@@Luke,

Any chance we could have something like this run automatically after import before recalculating the schedules?

 

We would need a text field to enter shows to put in the IN CLAUSE.  For example for me at present the where clause needs to be

Deadliest Catch,Dirty Jobs,Lone Star Law,Moonshiners,MythBusters,Naked and Afraid,Sea Rescue,The Office,The Three Stooges

 

If we had a fast true SQL relational database back end I would have just created an insert trigger.

 

Carlo

[Luke 42079]

I haven't yet reviewed all of this but will soon, thanks.

[PenkethBoy 2068]

@@Luke,

Any chance we could have something like this run automatically after import before recalculating the schedules?

 

We would need a text field to enter shows to put in the IN CLAUSE.  For example for me at present the where clause needs to be

Deadliest Catch,Dirty Jobs,Lone Star Law,Moonshiners,MythBusters,Naked and Afraid,Sea Rescue,The Office,The Three Stooges

 

If we had a fast true SQL relational database back end I would have just created an insert trigger.

 

Carlo

@@cayars

 

Carlo - you can setup Insert Triggers in Sqlite as well as Update and Delete

 

http://www.sqlitetutorial.net/sqlite-trigger/

 

Not tried with your sql - but on the face of it - should be doable

[Carlo 4561]

I wasn't expecting the performance of SQLite to be good enough but after a couple of different tries have figured out how to get this working fast.

This now solves 95%+ of my EPG issues.  This will fill in missing season/episode info as well as remap the season/episode info when it's wrong from the EPG source.

 

Since our libraries use TVDB (whatever) for the actual listings in our libraries it's important to make sure the EPG info matches the libraries.  This keeps us from recording shows we already have and maybe more importantly allows us to record missing episodes that we need.

 

Once installed there is nothing that needs to be done moving forward. Basically I've added two new indexes as well as a database trigger.

I'm including all needed info for anyone wanted to try/test this.  

 

Prerequisites

This uses the "missing episode" feature of Emby.  You have to make sure this is turned on.  

Edit your TV Show library using the "Manage Folders" option. 

Scroll down and make sure the option "Display missing episodes within seasons" is checked.

Refresh your TV Show library and give it time to download the missing episode info for each of your shows.

 

You will also want to make sure the titles of your episodes are correct.  If the Name is something like "White Collar - S02E13 - Countermeasures" then it's wrong and needs to be adjusted to "Countermeasures" without the showname and numbering in it.  Basically this name has to match the name in the xmltv file.  Plus you will just want this cleaned up so it looks nice on your system anyway.  You can Refresh a season or show if needed to pull down and fix this info.

 

Instructions for Windows:

1) download sqlite3.exe

  a) Go to https://www.sqlite.org/download.html and scroll down to Precompiled Binaries for Windows

  Download sqlite-tools-win32-x86-3220000.zip

  c) Extract sqlite3.exe to a working directory you are going to use such as c:\fix-epg

 

2) In this same working directory c:\fix-epg create a file called fix-epg.bat and put the following contents in that file:

sqlite3 < FixEPG.txt

 

3) Create a file called FixEPG.txt and put the following contents in that file:

.open 'C:\Users\Administrator\AppData\Roaming\MediaBrowser-Server\data\library.db'

.output FixEPG-result.txt

CREATE INDEX idx_CayarsTypedBaseItems1 on TypedBaseItems(UnratedType,SeriesName,Name);

CREATE INDEX idx_CayarsTypedBaseItems2 on TypedBaseItems(UnratedType,Name,EpisodeTitle);

DROP TRIGGER fix_epg;

CREATE TRIGGER fix_epg AFTER INSERT ON TypedBaseItems

WHEN New.UnratedType='LiveTvProgram'

BEGIN

UPDATE TypedBaseItems

SET

ParentIndexNumber = (select coalesce(TBI2.ParentIndexNumber,New.ParentIndexNumber)

  from TypedBaseItems as TBI2

  where TBI2.UnratedType='Series'

  and TBI2.SeriesName=New.Name

  and Lower(Trim(Replace(TBI2.Name,'-',' ')))=Lower(Trim(Replace(New.EpisodeTitle,'-',' ')))

),

IndexNumber = (select coalesce(TBI2.IndexNumber,New.IndexNumber)

  from TypedBaseItems as TBI2

  where TBI2.UnratedType='Series'

  and TBI2.SeriesName=New.Name

  and Lower(Trim(Replace(TBI2.Name,'-',' ')))=Lower(Trim(Replace(New.EpisodeTitle,'-',' ')))

)

where TypedBaseItems.UnratedType='LiveTvProgram'

and TypedBaseItems.Name=New.Name

and TypedBaseItems.EpisodeTitle=New.EpisodeTitle

and EXISTS (

  select TBI2.ParentIndexNumber,TBI2.IndexNumber

  from TypedBaseItems as TBI2

  where TBI2.UnratedType='Series'

  and TBI2.SeriesName=New.Name

  and Lower(Trim(Replace(TBI2.Name,'-',' ')))=Lower(Trim(Replace(New.EpisodeTitle,'-',' ')))

);

END;

 

Modify the first line of this file to make sure you it has the proper path for your system to the library.db file

save the file

 

4) Shutdown Emby

 

5) Copy/backup the Data directory just in case something goes wrong! For me on windows this is: C:\Users\Administrator\AppData\Roaming\MediaBrowser-Server\data

 

6) With Emby shut down click on the fix-epg.bat to execute it.  You'll see a command box open for a duration of near instant to 15 seconds (guessing) depending on the speed of your computer and how many entries you have in your library.

 

7) Restart Emby - Check navigating to your movies and TV libs to make sure you don't get any errors (you shouldn't!).

If you do get any unusual errors shutdown Emby and restore the data directory we backed up previously to put your system back the way it was then restart Emby again.

 

8) Run the command to import your EPG as usual.

Once complete check your schedules, a few series set to record (look at the listings at the bottom) and make sure everything appears to be correct.

From now on your EPG season & episode numbers will auto adjust to your library!

 

If you find any series that doesn't seem to update properly you need to make sure that the Show Name is the same as the EPG source.  For example on my system zap2it has "The Office" but my library had "The Office (US)" so I had to change my library name to also be "The Office" so they would match.  You might also find problems like this for shows in your library that have the year in the name.  Basically just make sure the library TV Show name is the same as used in the XMLTV file.

 

Carlo

 

PS  After a few people give this a work out I'll see if I can modify it to handle certain problem shows like Hawaii Five-0/O where the EPG might not distinguish the right series. I'm sure we'll find some problem show like that and will need to handle them special.

 

Let me know how you make out or if you think I missed any steps.  PLEASE, PLEASE make sure you backup the data directory just in case!

Edited March 28, 2018 by cayars

[Luke 42079]

is this going over multiple things or is it focused on the single topic from the first post?

[PenkethBoy 2068]

A couple of questions

 

1) Does this apply "only" to xmlTV in that the recording of episodes is much more reliable using Schedules Direct epg data? I get the occasional duplicate but i think that's either a spelling error or when i move episodes from my recording directory to my main libraries and its a timing thing.

 

2) Can follow all the sql code and what you are doing - but not quite sure what the Trim(Replace - lines are actually doing (not run this on my db to check)

 

Can i suggest you explain each step the sql undertakes so we have an expectation of what is being done to the db file - so 1) i can check the result 2) extract the data from the old and new db files to compare what changed and did it succeed etc

 

I know some of the code is sql 101 but want to be sure we are all understanding each other

 

Thanks

[Carlo 4561]

is this going over multiple things or is it focused on the single topic from the first post?

It's a fix to keep xmltv season/episode numbers in sync with the season/episode numbers already present in our libraries.

It fixes bad epg shows without the numbers or when they use different episode numbers.  I use it with zap2xml data.

 

Hence no more recording episodes you already have because it had a different episode number and fixes the issue when you could never record a missing show because the EPG source used a different episode number for the show you are missing.

 

Also fixes shows like The Three Stooges that don't ever seem to have season/episode info in the xmltv file.

[Carlo 4561]

A couple of questions

 

1) Does this apply "only" to xmlTV in that the recording of episodes is much more reliable using Schedules Direct epg data? I get the occasional duplicate but i think that's either a spelling error or when i move episodes from my recording directory to my main libraries and its a timing thing.

 

2) Can follow all the sql code and what you are doing - but not quite sure what the Trim(Replace - lines are actually doing (not run this on my db to check)

 

Can i suggest you explain each step the sql undertakes so we have an expectation of what is being done to the db file - so 1) i can check the result 2) extract the data from the old and new db files to compare what changed and did it succeed etc

 

I know some of the code is sql 101 but want to be sure we are all understanding each other

 

Thanks

1) Check the duplicate next time you see one.  If the season/episode number from the EPG source doesn't match your library entry then this is the fix for that.

2) Trim simple removes spaces from the front and back of the string. I found many entries in my database that had excess spaces at the end of the strings.

The replace function changes the dash to a space.  This is needed as it's common to see "show name" in the db while "show-name" in the EPG source. 

 

3) It should be self explanatory if you can read the SQL.  It's basically using the existing numbering already in the system instead of what's in the EPG source.  It DOES NOT change library data but only EPG data in the DB.

 

The only thing that is confusing is the mismatch of SeriesName, Name, EpisodeTitle fields.  This is beyond my control.  These fields are used weirdly in the existing code.  By that I mean:

TV Series:

SeriesName column contains the Series, 'The Big Bang Theory'

Name column contains the episode name, 'The Mommy Observation'

EpisodeTitle column is not used

 

LiveTVProgram or EPG

SeriesName column is not used

Name column contains the Series, 'The Big Bang Theory'

EpisodeTitle column contains the episode name, 'The Mommy Observation'

(generally speaking this is a poor thing to do database wise, but I didn't design it)   It's not a big deal once you know what column to use per type but is confusing.

 

So that is why you see compares of SeriesName to Name and also Name to EpisodeTitle (depends if it's EPG or not)

 

If there is anything specific you don't understand just ask!

 

You'll notice in the trigger there is a when clause "WHEN New.UnratedType='LiveTvProgram'"  That makes that only EPG data is updated.  So if somehow, someway the EPG data looks bad just remove the trigger and reload the EPG data again OR restore the back I advised making.

 

Carlo

Edited March 27, 2018 by cayars

[Carlo 4561]

I've got another version a bit more advanced that may help shows are are reboots such as Hawaii Five-0/O, Will & Grace and MacGyver which the typical EPG lumps all together.

 

I don't want to post this one quite yet but if anyone is familiar with sqlite and/or Emby database and wants to test/play with it let me know,

Carlo

 

PS Let me know if you guys know of any other similar "problem" series like the above.

[Luke 42079]

Are you saying your guide data doesn't have season and episode numbers? I would rather look at the root cause if possible.

[Luke 42079]

I use it with zap2xml data.

Does the guide data have zap2it info? can you post a sample xml snippet?

[PenkethBoy 2068]

Carlo

 

Thanks for the extra info

 

The trim was not that clear why you were removing a "-" but i get it now that its only changing the show name from show-name to enable a match

 

Yes the db has a lot of duplicate and unused data - but it is what it is

 

Yes i am familiar with SQLite\SQL and the Emby DB structure

 

I don't use xmltv as i have tuners with schedules Direct EPG data - although i could test it on a test server i have if you want

 

But in summary the "problem" comes down to the quality of the EPG data rather than Emby itself being the problem or am i missing something?

[Carlo 4561]

Are you saying your guide data doesn't have season and episode numbers? I would rather look at the root cause if possible.

For some shows yes.

 

This would apply to anyone who uses zap2it,tvguide or Gracenote derived data.  This is NOT Emby specific but EPG sources in general.  I've got over a hundred series setup to record plus coming from Plex which uses Gracenote's data I've seen this plus had user reports of bad data.

 

There are a few issues that some EPG sources get wrong.  Reboot shows like MacGyver 1985 & 2016, Will & Grace 1998 & 2018, Hawaii Five 0/O 1968 & 2010 come through all combined when looking at them in Emby even though they have a different EP ID (zap2it).  This could be fixed in Emby during import.

 

Other info is missing season/episode info.  If you use zap2it info try looking for "The Three Stooges" on AMC and you'll see it's probably missing this info. Lots of similar shows like this.  So Emby will record every show since if you use the setting to record all episodes you don't have.

 

Then there is EPG info that has different episode (sometime season as well) than the tvdb info we have already imported into the DB. Shows like Dirty Jobs, The Office (us version) & Law & Order: Criminal Intent, Lone Star Law all come to mind.

 

Yet another type of show (depending on your setup/library names) that might cause grief is shows like Mythbusters.  The EPG will use standard S01, 02, 03 season numbering while we may have used year numbering as you'll find here: https://www.thetvdb.com/?tab=seasonall&id=73388&lid=7

 

So this trigger (I have a more advanced version than posted here I'm testing) "normalized" the EPG data we are importing to fix as much of these issues as possible.

 

XML:

The Stooges missing season/episode

<programme start="20180404100000 -0400" stop="20180404120000 -0400" channel="I34.14321.zap2it.com">

<title lang="en">The Three Stooges</title>

<desc lang="en">Just say Moe.</desc>

<date>2012</date>

<category lang="en">Movie</category>

<length units="minutes">120</length>

<icon src="https://zap2it.tmsimg.com/assets/p8850873_p_v5_ab.jpg"/>

<url>https://tvlistings.zap2it.com//overview.html?programSeriesId=MV00373103&tmsId=MV003731030000</url>

<episode-num system="dd_progid">MV00373103.0000</episode-num>

<subtitles type="teletext" />

<rating>

<value>TV-PG</value>

</rating>

</programme>

 

 

The Office (US) marked as S03E20 but should be S03E19

<programme start="20180328150000 -0400" stop="20180328153000 -0400" channel="I828.62420.zap2it.com">

<title lang="en">The Office</title>

<sub-title lang="en">Safety Training</sub-title>

<desc lang="en">Andy returns from anger-management classes, planning to make a fresh start with his co-workers; Michael and Dwight try to illustrate the dangers of the workplace.</desc>

<length units="minutes">30</length>

<icon src="https://zap2it.tmsimg.com/assets/p3120372_e_v5_ab.jpg"/>

<url>https://tvlistings.zap2it.com//overview.html?programSeriesId=SH00726133&tmsId=EP007261330048</url>

<episode-num system="common">S03E20</episode-num>

<episode-num system="dd_progid">EP00726133.0048</episode-num>

<episode-num system="xmltv_ns">2.19.</episode-num>

<previously-shown />

<subtitles type="teletext" />

<rating>

<value>TV-14</value>

</rating>

</programme>

 

Setup a series recording for Will & Grace, Hawaii Five-0, MacGyver in Emby and then look at the EPS episodes list for each and you'll see the old and new reboots are mixed together.

 

Shows like Mythbusters who use year seasons in tvdb vs 2 digit season in EPG should be obvious.

 

Hope that helps,

Carlo

 

PS It's amazing how many shows have different EPG episode numbers compared to what we have already pulled down ans what this script "normalizes".

[Carlo 4561]

But in summary the "problem" comes down to the quality of the EPG data rather than Emby itself being the problem or am i missing something?

 

If you read the above post in response to Luke's question then the overlap of reboot shows such as MacGyver, Will & Grace, Hawaii Five-0/O might be Emby issues but everything else is an EPG issue and NOT an Emby issue.

 

I don't have schedules direct so I don't know how good that data is compared to zap2it.  You can try searching for "Safety Training" from "The Office" to see if it's marked correctly as Episode 19 vs 20 (zap2it)  Try checking "The Three Stooges" on AMC to see if schedules direct has the season/episode info. Does Mythbusters use 2 digit season vs 4 digit year seasons?

 

How about "Mardi Gras Bladder Banger" from the show "Dirty Jobs"?  Zap2it has this as S06E29 but I want it as S08E06 for Emby. https://www.thetvdb.com/?tab=season&seriesid=78904&seasonid=344411&lid=7

 

I'd be really interested to know how Schedules direct compares to zap2it for these!

 

Carlo

[PenkethBoy 2068]

You can try SD for a few days for free to see if it has different data

[Carlo 4561]

I loaded up Schedule Direct.  It's wrong as well:

Dirty Jobs - Safety Training shows as S3E20

The Three Stooges have no season or episode data

Mythbusters use 2 digit seasons

Dirty Jobs - Mardi Gras Bladder Banger shows as S06E29

 

This makes sense since they are all use the same dataset.  Plex gets data directly from Gracenote and has the exact same issue as well.

 

[Luke 42079]

How do you know that this won't cause problems for other shows though?

[Carlo 4561]

It's called "testing" 

 

I wouldn't have posted SQL like this otherwise on the forum had I not tested it really well first.

 

This trigger actually processes every EPG record added to the DB so it does affect ALL EPG records for series a person already has in the system.  Nothing to adjust after installing the trigger.  It won't change any records for series the person doesn't have setup (can't as there is no season/episode data in the DB).

 

Actually when you look at the trigger it only allows updating of the EPG info (UnratedType='LiveTvProgram') in the DB not the actual series info.  So in the event something was to go wrong anyone could drop the trigger and rerun a guide refresh (I've done this numerous times to check results with and without the trigger).

 

On my main system I have 450+ series in my TV Show library and have 124 series setup to record any missing episodes.

Carlo

Edited March 28, 2018 by cayars