Utility to Fix Scene/TVDB season/episode mismatches

If you've made an extra_script, made a 3rd party tool, or just added some functionality in a fork you can post about it here.

Utility to Fix Scene/TVDB season/episode mismatches

Postby centax » Fri Dec 09, 2011 3:20 pm

This utility modifies the sickbeard.db file that Sick Beard uses to store episode info.

THIS IS NOT A PERMANENT FIX

Every time Sick Beard updates it DB it will erase any changes. This utility works by using SQL queries, I am not an expert on SQL in fact I only know the basics. If anyone out there finds a way to create triggers in the DB to make these fixes permanent I would appreciate the help.

To use this utility you need to have sqlite3.exe in your Sick Beard directory. You can download it from http://www.sqlite.org/download.html

Then you will need to create a bat file to run your queries.

1. create a new txt document in your Sick Beard directory.

2. Copy the following text into the new txt document.

:::Begin Copy:::
sqlite3 sickbeard.db < query.txt
:::End Copy:::

3. Save the file

4. Rename the file and extension I chose to rename mine to "seasonfixer.bat" you can choose whatever you want as long as it ends with ".bat". Make sure you DO NOT have "Hide extensions for known file types" checked in your folder options.

5. Create a new text file named query.txt in your Sick Beard directory. This is where you will store any changes to to shows I will give you some examples later.

6. Once you have the changes you want done put into the query.txt file you just run your .bat file and it will update the database. Do not run it multiple times in a row as some of these changes will cause further problems if run repeatedly. Check Sick Beard to see if the changes have taken effect if so good leave it alone. If not force a full update and run the .bat again when it finishes.

Here are some examples of fixes just paste these into your query.txt and save.

----------------------------
--American Dad Begin--
----------------------------
UPDATE tv_episodes
SET episode = (episode + 7)
WHERE showid = 73141 and season = 2
;

UPDATE tv_episodes
SET season = (season - 1)
WHERE showid = 73141 and season > 1
;
--------------------------
--American Dad End--
--------------------------

------------------------
--Pawn Stars Start--
------------------------
UPDATE tv_episodes
SET season = 3
WHERE showid = 111051 and season = 2 and episode > 31 and episode <60
;

UPDATE tv_episodes
SET episode = episode-31
WHERE showid = 111051 and season = 3 and episode > 31
;

UPDATE tv_episodes
SET season = 4
WHERE showid = 111051 and season = 2 and episode > 59
;

UPDATE tv_episodes
SET episode = episode-59
WHERE showid = 111051 and season = 4 and episode > 59
;
----------------------
--Pawn Stars End--
----------------------

-----------------------------------------------
--Beavis and Butthead Season 9 Start--
-----------------------------------------------
UPDATE tv_episodes
SET episode = 1
WHERE showid = 75863 and season = 9 and episode = 2
;

UPDATE tv_episodes
SET episode = 2
WHERE showid = 75863 and season = 9 and episode = 3
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 4
;

UPDATE tv_episodes
SET episode = 3
WHERE showid = 75863 and season = 9 and episode = 5
;

UPDATE tv_episodes
SET episode = 4
WHERE showid = 75863 and season = 9 and episode = 6
;

UPDATE tv_episodes
SET episode = 5
WHERE showid = 75863 and season = 9 and episode = 7
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 8
;

UPDATE tv_episodes
SET episode = 6
WHERE showid = 75863 and season = 9 and episode = 9
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 10
;

UPDATE tv_episodes
SET episode = 7
WHERE showid = 75863 and season = 9 and episode = 11
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 12
;

UPDATE tv_episodes
SET episode = 8
WHERE showid = 75863 and season = 9 and episode = 13
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 14
;

UPDATE tv_episodes
SET episode = 9
WHERE showid = 75863 and season = 9 and episode = 15
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 16
;
---------------------------------------------
--Beavis and Butthead Season 9 End--
---------------------------------------------

I only created the American Dad Changes
credit for Pawn Stars goes to dasaint in this post http://sickbeard.com/forums/viewtopic.php?f=8&t=487&start=80#p15112
credit for Beavis and Butthead goes to Tufflaw in this post http://sickbeard.com/forums/viewtopic.php?f=8&t=487&start=90#p15127

If you want to create your own changes I suggest you read up on SQL and learn how to do queries. I encourage others to create their own fixes and post them here and to use the following format so we can keep things consistent. Enclosing the shows names in "--" makes that line a comment meaning it can be copied into the query.txt and not cause any problems.

------------------------------------------------------------------------
--Show Name (if specific season only then) Season # Start--
------------------------------------------------------------------------
All Queries
----------------------------------------------------------------------
--Show Name (if specific season only then) Season # End--
----------------------------------------------------------------------
centax
 
Posts: 5
Joined: Wed Nov 09, 2011 12:07 am

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby Tufflaw » Wed Dec 21, 2011 10:55 pm

Thanks for the mention, here's the updated Beavis and Butthead, as I update my own copy I'll come back here and edit this reply - warning, it doesn't work perfectly but it at least numbers them correctly:

UPDATE tv_episodes
SET episode = 1
WHERE showid = 75863 and season = 9 and episode = 2
;

UPDATE tv_episodes
SET episode = 2
WHERE showid = 75863 and season = 9 and episode = 3
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 4
;

UPDATE tv_episodes
SET episode = 3
WHERE showid = 75863 and season = 9 and episode = 5
;

UPDATE tv_episodes
SET episode = 4
WHERE showid = 75863 and season = 9 and episode = 6
;

UPDATE tv_episodes
SET episode = 5
WHERE showid = 75863 and season = 9 and episode = 7
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 8
;

UPDATE tv_episodes
SET episode = 6
WHERE showid = 75863 and season = 9 and episode = 9
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 10
;

UPDATE tv_episodes
SET episode = 7
WHERE showid = 75863 and season = 9 and episode = 11
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 12
;

UPDATE tv_episodes
SET episode = 8
WHERE showid = 75863 and season = 9 and episode = 13
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 14
;

UPDATE tv_episodes
SET episode = 9
WHERE showid = 75863 and season = 9 and episode = 15
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 16
;

UPDATE tv_episodes
SET episode = 10
WHERE showid = 75863 and season = 9 and episode = 17
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 18
;

UPDATE tv_episodes
SET episode = 11
WHERE showid = 75863 and season = 9 and episode = 19
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 20
;

UPDATE tv_episodes
SET episode = 12
WHERE showid = 75863 and season = 9 and episode = 21
;

DELETE FROM tv_episodes
WHERE showid = 75863 and season = 9 and episode = 22
;
Tufflaw
 
Posts: 45
Joined: Mon Jan 10, 2011 11:32 pm

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby meissen » Thu Dec 29, 2011 9:58 pm

Because TheTVDB.com recently updated Pawn Stars to have Season 3, I modified the query posted above to correctly fix the seasons to match TV Rage's database. I still think it's pathetic that we have to do this to get it to work, but I'm happy to see my SABnzbd is swamped with nzbs to download so I can finally catch up on Pawn Stars.

UPDATE tv_episodes
SET season = 4
where showid = 111051 and season = 3
;

UPDATE tv_episodes
SET episode = episode+52
WHERE showid = 111051 and season = 4
;

UPDATE tv_episodes
SET season = 3
WHERE showid = 111051 and season = 2 and episode > 31 and episode <60
;

UPDATE tv_episodes
SET episode = episode-31
WHERE showid = 111051 and season = 3 and episode > 31
;

UPDATE tv_episodes
SET season = 4
WHERE showid = 111051 and season = 2 and episode > 59
;

UPDATE tv_episodes
SET episode = episode-59
WHERE showid = 111051 and season = 4 and episode > 59 and airdate < 734469
;
meissen
 
Posts: 19
Joined: Tue Mar 01, 2011 6:26 am

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby aaearon » Sun Jan 01, 2012 9:19 am

These queries rule. Request: one for Frontline. Scene releases it as S01,02,03etc while TVDB has it listed as S1990,1991, etc
aaearon
 
Posts: 1
Joined: Sun Jan 01, 2012 9:16 am

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby zi99y » Sat Jan 14, 2012 8:39 am

Brilliant thanks for this - I would recommend using Navicat to manually tweak the data if you are confident, it's very easy to use with little / no sql knowledge. I just made changes to allow the latest season of horizon to work.

Futurama is one that has bugged me - all my precious episodes got rewritten with the wrong data. Will have a go at this sometime.

Does anyone know if this data gets wiped when sickbeard checks thetvdb for updates?
zi99y
 
Posts: 2
Joined: Fri Sep 16, 2011 5:50 am

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby rascalli » Sat Jan 14, 2012 9:56 am

centax wrote:This utility modifies the sickbeard.db file that Sick Beard uses to store episode info.

THIS IS NOT A PERMANENT FIX

Every time Sick Beard updates it DB it will erase any changes.


@zi99s , see what is posted by the author .. so it gets wiped with every update.
So you need to make a scheduled run of this script ather the update at 3 AM
NL newsserver starting €44,40 a year

Image

Hope you will use my refereal link
rascalli
 
Posts: 4913
Joined: Tue Aug 10, 2010 2:26 pm

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby zi99y » Mon Jan 16, 2012 3:27 am

DOH!
zi99y
 
Posts: 2
Joined: Fri Sep 16, 2011 5:50 am

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby todd3835 » Fri Jan 27, 2012 7:24 pm

I'm wondering if this could be done to ADD episodes to a show. My example would be WWE Friday Night Smackdown, which is currently a locked show on TVDB. This show doesn't work right in SB anyways because it's a sports show, but for the sake of having SB *process* the files (it was doing that properly before it was locked on TVDB), I need episodes added. For instance S13E03 on 1/20/12 and S13E04 in 1/27/12. If someone could make an example for those two, I could quickly add the whole season and just call it a day. I didn't really understand the examples, so if someone could show me an example, I'd gladly do the work of adding S13E05-E52 and post it. I know atleast one other SB user follows this show. We both use RSS feeds within SABnzbd to get the show (since search doesn't work for it), so this would get it working for us again! Thanks in advance!

-Todd
todd3835
 
Posts: 19
Joined: Fri Dec 09, 2011 8:24 pm

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby i814u2 » Tue Jan 31, 2012 7:09 pm

Just an update, in case anyone else is having this issue:

I just applied this temp fix and found that sickbeard doesn't quite like it so much. I have episodes in season folders, which may add to the confusion.
I found that it got confused about some seasons, and then ended up incorrectly renaming some items (because it thought they were part of a season they weren't).
Example: For American Dad!, it thought season 3 files belonged under season 2 (correct) but then it was using season two episode names instead of just using the season 3 names but naming them S02. That's confusing I know, but I didn't think to write this up until after I had manually fixed everything, so I don't have great examples.

The next thing I saw was the Season 07, episode 10 (which is S08E10 at thetvdb) was showing up as "wanted" and with the Season 07 information (which is NOT correct).

I couldn't get it to play nice, even after fully removing data and placing back just video files, season by season. So I wiped that folder and placed the backup back in place with the old settings.

The point is that this temp solution may not fully work depending on what actions you need to take on the db (seems that it maybe stores some information in areas that the update query doesn't touch). There really needs to either be an option to fix this, the scene needs to change, or thetvdb needs to be updated.

Perhaps since it's such a small number of shows with this issue, it could be a manual but permanent fix (which each new "issue" being rolled into the next release). I'm not sure if that's the best, or not, though.


Or...I completely missed something, did this incorrectly, and it's all my fault. Please, if anyone has done this, had my issues, and then fixed it, i'm interested to hear the solution.


UPDATE: I got determined to check into this further and started over again. I made my backup for the American Dad files. Then I move Season 2 through 8 (as described by default) to a temp folder. Then I rescanned the files from within sickbeard. That showed that I only had the 7 eps from season 1. Then I ran the update in this thread. The entries look correct. I then added the items from my previous season 3 folder into a season 2 folder. I renamed "s03" to "s02". I removed all tbn and nfo files. I rescanned the files in sickbeard. It found them all, and started to get nfo/tbn files. However, it wouldn't find 17,18,or 19. I checked the logs and noticed that it complains about not finding those on thetvdb, as well as s01e08-s01e23 (which makes sense, since thetvdb see those as s02e01-s02e16).
So, while this might work for one aspect, it doesn't fix it enough to use it when rescanning/renaming is needed, etc. Basically, you could probably add everything as normal, keeping basic file names (without episode titles), let it grab nfo and tbn files, then manually rename the season numbers (and episode numbers for s01e08-23), then update the db,re-folder the files (if you folder them) and then rescan. then you could let sickbeard rename everything, as it wouldn't be pullling any info during that phase (i assume not, haven't tested this). The downside to all this is that future downloads will work, but will receive improper nfo/tbn files. Also, as I saw before, the upcoming episodes start to get incorrect information as well (since we are decreasing the season number, it's able to find previous season information, which isn't desired for episodes that haven't aired yet).

Just thought i'd let everyone know what I had seen. Maybe this will help someone come up with additional clever tricks to make this work, maybe not.
Perhaps something like an offset that could be specified for a show. Then sickbeard would know to display the season properly, name the files properly, and most importantly it would also know how to properly ask thetvdb for the matching info for each episode. I look forward to seeing a solution to this issue.
i814u2
 
Posts: 3
Joined: Tue Jan 31, 2012 7:00 pm

Re: Utility to Fix Scene/TVDB season/episode mismatches

Postby JoopNL » Mon Jun 18, 2012 3:34 pm

meissen wrote:Because TheTVDB.com recently updated Pawn Stars to have Season 3, I modified the query posted above to correctly fix the seasons to match TV Rage's database. I still think it's pathetic that we have to do this to get it to work, but I'm happy to see my SABnzbd is swamped with nzbs to download so I can finally catch up on Pawn Stars.
[..]


Updated Pawn Stars for Season 5. (Just added the last two commands.)

Code: Select all
------------------------
--Pawn Stars Start--
------------------------

UPDATE tv_episodes
SET season = 4
where showid = 111051 and season = 3
;

UPDATE tv_episodes
SET episode = episode+52
WHERE showid = 111051 and season = 4
;

UPDATE tv_episodes
SET season = 3
WHERE showid = 111051 and season = 2 and episode > 31 and episode <60
;

UPDATE tv_episodes
SET episode = episode-31
WHERE showid = 111051 and season = 3 and episode > 31
;

UPDATE tv_episodes
SET season = 4
WHERE showid = 111051 and season = 2 and episode > 59
;

UPDATE tv_episodes
SET episode = episode-59
WHERE showid = 111051 and season = 4 and episode > 59 and airdate < 734469
;

UPDATE tv_episodes
SET season = 5
where showid = 111051 and season = 4 and episode > 92
;

UPDATE tv_episodes
SET episode = episode-92
WHERE showid = 111051 and season = 5
;

----------------------
--Pawn Stars End--
----------------------
JoopNL
 
Posts: 1
Joined: Wed May 02, 2012 6:24 am

Next

Return to Mobile Apps, Extra Scripts, Supplementary Tools

Who is online

Users browsing this forum: No registered users and 3 guests

cron