Completed Show Intro Skip Option

[Cheesegeezer 3089]

5 hours ago, chef said:

So I do have to make a new query for series that are ended and check to see if it is in the list.

 

Thank you sir.

I don’t know to be honest... but i would say yes.  That method will return all items that are still airing, so there shouldn't be any ended shows in there.  However, not sure on your code and i have no idea about sqlite, but i think you could use that method and it should return a list of series that are still running, then you can do what you need with it. 

can you share on github with me?  Maybe i might be able to help out.

Cheers

Edited September 2, 2021 by Cheesegeezer

[rbjtech 4316]

Hmm - The coding is well beyond my skillset - but by looking in the database directly and editing via 'Metadata' to confirm - there is a direct 'Status' that changes when you modify the Continuing or Ended state of that show.

Is it this - public SeriesStatus[] SeriesStatuses { get; set; } ?

 

All we need the logic to do (I think) is to remove the FP data IF -

a) The show has ended.  AND

b) There are no episodes missing AND

c) The Intro Detection is 100% complete for that Show (all seasons, all episodes)

 

For me, if the above is all true - then the FP data has served it purpose and can be safely removed.

If any of the above are not true (ie any other condition) then keep the FP data for the show and move onto the next show.  During the next scheduled scan, it will be re-analysed and if the conditions have changed it may get removed - or left indefinitely if the condition is never met.  

 

If the dB is still too big - then we could think about doing this at a Season level instead of a Show level.  ie

a) The Season has ended. AND

b) There are no episodes missing AND

c) The Intro Detection is 100% complete for that Season.

 

Maybe that is easier to code and it will keep the dB as small as possible...  only leaving the FP data for a season if missing an episode for example.

 

[Cheesegeezer 3089]

9 minutes ago, rbjtech said:

Hmm - The coding is well beyond my skillset - but by looking in the database directly and editing via 'Metadata' to confirm - there is a direct 'Status' that changes when you modify the Continuing or Ended state of that show.

Is it this - public SeriesStatus[] SeriesStatuses { get; set; } ?

let me check

 

[Cheesegeezer 3089]

16 minutes ago, rbjtech said:

Hmm - The coding is well beyond my skillset - but by looking in the database directly and editing via 'Metadata' to confirm - there is a direct 'Status' that changes when you modify the Continuing or Ended state of that show.

Is it this - public SeriesStatus[] SeriesStatuses { get; set; } ?

 

So yes that is correct and is an enum in the metadata.... see below

namespace MediaBrowser.Model.Entities
{
    public enum SeriesStatus
    {
        Continuing = 1,
        Ended = 2
    }
}

So we can query the seriesstatus

[Cheesegeezer 3089]

@rbjtech  

So something like this could added to the query

query.SeriesStatuses = new SeriesStatus[2];

[Cheesegeezer 3089]

for an api call

private SeriesStatus? GetSeriesStatus(BaseItemDto item)
        {
            if (string.IsNullOrEmpty(item.Status))
                return null;
            }

            return (SeriesStatus)Enum.Parse(typeof(SeriesStatus), item.Status, true);

        }

 

[Cheesegeezer 3089]

we shouldn't need an api call as this is already available as a property somewhere, in the Query or in another accessable class

namespace MediaBrowser.Controller.Providers
{
    public class EpisodeInfo : ItemLookupInfo
    {
        public EpisodeInfo();

        public Dictionary<string, string> SeriesProviderIds { get; set; }
        public int? IndexNumberEnd { get; set; }
        public bool IsMissingEpisode { get; set; }
        public SeriesDisplayOrder SeriesDisplayOrder { get; set; }
    }

 

[chef 3749]

I got it!

It lives in BaseItemDto, not the BaseItem.

So what you do is utilize the IDtoService.

 

Add it to your constructor and then feed the BaseItem.Id to GetBaseItemDto.

 

Edited September 2, 2021 by chef

[Cheesegeezer 3089]

6 minutes ago, chef said:

I got it!

It lives in BaseItemDto, not the BaseItem.

So what you do is utilize the IDtoService.

 

Add it to your constructor and then feed the BaseItem.Id to GetBaseItemDto.

 

Nice one centurion... like it, like it!!

 

[chef 3749]

Okay now we look like this.

//The season has been processed and all episodes have a sequence.                        
if (processedEpisodeResults.Count() == episodeQuery.TotalRecordCount )
{                        
    if (processedEpisodeResults.All(result => result.HasSequence || result.Confirmed) && DtoService.GetBaseItemDto(series, new DtoOptions()).Status == "Ended")
    {
        Log.Info($"{series.Name} - {seasonQuery.Items[seasonIndex].Name} has complete title sequence profile.");
        foreach(var result in processedEpisodeResults)
        {
            if(result.Fingerprint.Any()) //Only attempt to remove the fingerprint if there are any
            {
                result.Fingerprint.Clear();                  //Empty fingerprint List
                repo.Delete(result.InternalId.ToString());
                repo.SaveResult(result, cancellationToken);
             }
             
        }
        continue;
    }
}

 

However, the question still remains if I have created the vacuum pragma properly when constructing the initialization strings for the database.

From what I understand, when adding the vacuum pragma it has to be declared first before any other connection string command.

I have done that. But, we'll have to see if it actually works as expected.

I'm going to run this and see what happens.

Edited September 2, 2021 by chef

[rbjtech 4316]

 I think the dB needs to be 'closed' for the vacuum to actually remove the data ?

So I guess the question is, do you do this after every season, do this after every show, or maybe do this after every task/session ?  If after every task, then on the first run, you will hit the issue of a massive dB again - so maybe time limit or simply count X shows and then do a vacuum then restart the task ? 

@chef - ping me the dll via PM if you want me to test on my test setup.

  

[chef 3749]

55 minutes ago, rbjtech said:

 I think the dB needs to be 'closed' for the vacuum to actually remove the data ?

So I guess the question is, do you do this after every season, do this after every show, or maybe do this after every task/session ?  If after every task, then on the first run, you will hit the issue of a massive dB again - so maybe time limit or simply count X shows and then do a vacuum then restart the task ? 

@chef - ping me the dll via PM if you want me to test on my test setup.

  

Ah, good bit of info there. Thank you!

We could create a hidden task maybe. It can check to make sure that the two tasks are not running, then attempt the vacuum.

 

 

[chef 3749]

I think this is our working query string to get only seasons back.

string.Format("SELECT ResultId, TitleSequenceStart, TitleSequenceEnd, HasSequence, Fingerprint, Duration, SeriesId, SeasonId, IndexNumber, Confirmed, Processed from TitleSequenceResults WHERE SeasonId = {0}", query.SeasonInternalId.Value.ToString());

 

I can definitely test this one.

[chef 3749]

Thank goodness, some good news. The conditions to remove fingerprints are not interfering with the task completing. I'm 20%. 

Then I'll run it again to make sure it skips what it has to, and see if the fingerprint are removed from the db.

Im watching the file size like a hawk.

[rbjtech 4316]

2 hours ago, chef said:

Thank goodness, some good news. The conditions to remove fingerprints are not interfering with the task completing. I'm 20%. 

Then I'll run it again to make sure it skips what it has to, and see if the fingerprint are removed from the db.

Im watching the file size like a hawk.

Great news @chef !  

I'm looking forward to testing it again (without bringing my system to it's knees !)  

[chef 3749]

Sweet! The SqLite libraries that emby uses do expose a Vacuum method.

However, I'm not sure if there has to still be the "PRAGMA" in the Initialization string to control it. Probably...???.....

All I did was expose the Vacuum command to the plugin, and now, after we remove large parts of the Database (ie. Fingerprint data), we call the Vacuum command.

How this will effect the overall task time? I'm not sure.

But, if it is only going to happen after we remove large chunks of the Database, it doesn't really matter. 

There are a few blogs online explaining how 'Vacuum' might fragment the information. That should be interesting to find out.

Edited September 2, 2021 by chef

[chef 3749]

I've added some extra information into the logs, to help us better understand where the time is being used.

As you can see, each episode  is timed by a stopwatch.

I'm very interested to see how the time is used in the TitleSequence task.

 

In fact for the title sequence task, at each important moment, I will make sure there is a log entry with the elapsed time attached.

Edited September 2, 2021 by chef

[chef 3749]

things I have learned.

Do not try to vacuum a database while it is in transaction mode.

While checking values in a seasons fingerprint column (after they have been removed in a complete season), you can not check for 'result.Fingerprints.Any()'  because when it comes back from the database it is not an empty list, but instead it is 'NULL'.  You have to check for NULL.

 

Very close to something great! Vacuum works, but... it doesn't shave off as much as you would think from the file size.

 

 

[chef 3749]

 

Currently, we hold on to a lot of needless fingerprint data, in our database, if we rely on a 'SeriesStatus == Ended'.

What we need to do is look at weather or not we have a complete season.

There is no data in the API that tells us how many episodes are actually in a season.  So we can't really be sure that the library contains every episode for a season.

But we have to trim the data.

 

Can we deduce a season fingerprint data can be removed if:

1. The season EndDate is less then the current year (it is not a new or current season)

2. There are other seasons beyond the one we want to remove in the library.

 

We  will never really know if the  season is complete, and therefore run the risk or encoding fingerprints  later if more episodes are added to older seasons.

dilemma...

[samuelqwe 314]

14 minutes ago, chef said:

 

Currently, we hold on to a lot of needless fingerprint data, in our database, if we rely on a 'SeriesStatus == Ended'.

What we need to do is look at weather or not we have a complete season.

There is no data in the API that tells us how many episodes are actually in a season.  So we can't really be sure that the library contains every episode for a season.

But we have to trim the data.

 

Can we deduce a season fingerprint data can be removed if:

1. The season EndDate is less then the current year (it is not a new or current season)

2. There are other seasons beyond the one we want to remove in the library.

 

We  will never really know if the  season is complete, and therefore run the risk or encoding fingerprints  later if more episodes are added to older seasons.

dilemma...

For that last point, I think we should probably remove the fingerprints even if there’s a risk we might need them again someday. Having to re-fingerprint is likely not gonna be a big deal once you’ve already scanned through the full library once and you’re only needing to scan new episodes. So I probably wouldn’t worry about keeping the data in the off-chance we might need it.

 

However, we can likely remove any fingerprints for seasons that aren’t the current season. Let’s say a show is currently in it’s 7th season, for example, then we know it probably won’t be getting any more episodes in those prior 6 seasons. So we can keep the fingerprints for the latest season of the show only and delete the rest, even if it’s a continuing show.

[neik 838]

2 hours ago, chef said:

There is no data in the API that tells us how many episodes are actually in a season.  So we can't really be sure that the library contains every episode for a season.

One thought: Can you check for missing episodes within a season?

If so, then we can make sure if a season is complete or not.

[chef 3749]

18 minutes ago, neik said:

One thought: Can you check for missing episodes within a season?

If so, then we can make sure if a season is complete or not.

Well, it is possible, but we have been trying to stay away from virtual items because we are comparing our list of encoded items to what is in the library and needs to be encoded.

It starts to get a bit more complicated.

I did think about that though.

 

[rbjtech 4316]

10 hours ago, chef said:

Very close to something great! Vacuum works, but... it doesn't shave off as much as you would think from the file size.

This is interesting - as when I changed ALL the FP data to NULL and did a Vacuum manually via dB Browser - my dB went from 1Gig to about 2Meg.  Obviously this would not be the case for a live system as we are always going to have some FP Data in there for the current ongoing Seasons - but I guess for a typical collection where people keep 'Archives' of completed shows - all of those should have zero FP data - so file size should not be that big.

I agree with the point about not bothering to keep FP data from old seasons with missing episodes.   I think the user will just need to manually add the Intro Data when and if they get the missing episode - (ie all items in the non current season should be marked as 'Completed' from a Detection perspective - it will not be re-calculated in a scan refresh) ?

How does your new dB Index perform ? 

Do you have a work in progress version for us to look at ? 

 

[Cheesegeezer 3089]

4 hours ago, chef said:

Well, it is possible, but we have been trying to stay away from virtual items because we are comparing our list of encoded items to what is in the library and needs to be encoded.

It starts to get a bit more complicated.

I did think about that though.

 

Just a suggestion, can you enable missing episodes before you run and identify missing episodes within the library before running your introskip code.

Another way would be to interrogate TVMD against the TV show and select only matches to process or if there there are missing episodes then skip. (not sure which route you want to go)

 

[chef 3749]

2 hours ago, rbjtech said:

This is interesting - as when I changed ALL the FP data to NULL and did a Vacuum manually via dB Browser - my dB went from 1Gig to about 2Meg.  Obviously this would not be the case for a live system as we are always going to have some FP Data in there for the current ongoing Seasons - but I guess for a typical collection where people keep 'Archives' of completed shows - all of those should have zero FP data - so file size should not be that big.

I agree with the point about not bothering to keep FP data from old seasons with missing episodes.   I think the user will just need to manually add the Intro Data when and if they get the missing episode - (ie all items in the non current season should be marked as 'Completed' from a Detection perspective - it will not be re-calculated in a scan refresh) ?

How does your new dB Index perform ? 

Do you have a work in progress version for us to look at ? 

 

I was able to shrink the database file size down substantially after looking at removing finger prints at a 'completed' season level, and not series.

The vacuum method works perfectly.

But, it currently lives inside the fingerprint task. This might not be ideal because  that task would have to run a second time after the title sequence task has completed. 

The title sequence task is such a long running task, I thought it was best to not bog it down at the end with a bunch of database clean up.

 

What do you guys think? 

 

2 hours ago, Cheesegeezer said:

Just a suggestion, can you enable missing episodes before you run and identify missing episodes within the library before running your introskip code.

Another way would be to interrogate TVMD against the TV show and select only matches to process or if there there are missing episodes then skip. (not sure which route you want to go)

 

This is a good idea. I'll see if we can create a method using virtual items, and missing items to decide is the season is complete.

I think you are right. Making a request and then look specifically for 'IsMissing' items, would tell us that the season is not a complete season.

Very nice!