I thought it best place to post this here instead of the user customizations as it deals with plugins. If not, my apologies.

 

I am posting this by request. This tweak will allow sickbeard (for linux/unraid of course) to update a file in XBMC as opposed to removing the old file and adding a new one. For example, say you have a 720p hdtv or 1080p web-dl of a file, or better, an entire season. Then when the blu-ray comes out sickbeard downloads that and replaces the file(s). Now your 'recently added' list is full of an entire season of a show you already watched. There are other problems with this but this is the most common I have faced (and yes, I am aware of trakt but it doesn't solve all of the associated issues and it is a very roundabout way to partially solve something simple).

 

The reason this happens is because sickbeard and XBMC treat this situation as an add and remove instead of an update. They essentially see it as a new item and cannot keep track that it is the same item, just a different file/filename.

 

So until XBMC adds a way to do this (http://forum.xbmc.org/showthread.php?tid=176018), here is a workaround I came up with. Unfortunately is relies on making a very small code change to sickbeard to accomplish this. This is a two part mod.

 

First, sickbeard has the ability to run an additional user defined script after post processing, passing along arguments to the script. The problem is in the case of a case deletion, none of the defined parameters contains the name of that file. So a added a few lines of code to include it. I submitted a pull request for this but it remains unpulled: https://github.com/midgetspy/Sick-Beard/pull/673

 

The second part is the script itself. It will search the XBMC database for the file being replaced and update the record of the file name to the new file name. I got the need info here: http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Update_Paths_In_MySQL

 

There are a few caveats to this tweak though that you need to be aware of:

1. This requires replacing postprocessor.py in sickbeard. As such updating sickbeard would break this tweak by overwriting the file. If no changes are made to postprocessor.py in a sickbeard update then just backing up the file, updating sickbeard, and restoring the file should work. If there are changes then the file must manually be edited again. The diff is in the pull request listed earlier.

2. The extra script that is used expects version 75 of the XBMC video database (Frodo). If you use Gotham it won't work unless you change the SQL queries in the script. I won't do it until Gotham is finalized. If you don't know how to do that then don't.

3. This also assumes you use a MySQL database with XBMC, located on you unraid server. While I have not had issues with this tweak, it may be a good idea to back up your XBMC MySQL database before continuing.

 

Ok, now the lengthy explanation is done, time for the instructions:

 

1: Your version of sickbeard should be f6d5fa0c6ef9010a6c0bbbfc10ffca846f640981

    Download the attached postprocessor.txt file, change the extension to .py, and replace it (backup the old one first of course). It should be located in the sickbeard folder of your sickbeard install directory

2: Download the attached extra.txt file and save is somehwhere. Change the extension to .sh. I suggest the root of your sickbeard folder.

3: Stop sickbeard and open config.ini in your sickbeard data folder and under the "general" heading add this line, substituting the location for wherever you saved the extra.sh file:

extra_scripts = /mnt/user/plugins/sickbeard/extra.sh

 

If everything is set up properly you will see output in the log stating a file has been replaced. I have been using this for the last six months and it has worked perfectly for me but if anyone has issues, let me know. I would also appreciate your support in getting XBMC to add the necessary changes to their API so programs like sickbeard can do this natively and properly by commenting in the following thread. http://forum.xbmc.org/showthread.php?tid=176018

extra.txt

postProcessor.txt

Library cleanup does it, doesn't it?

No. Library clean up removes files from the library that don't exist anymore.

bobbintb,

 

After I modify my config.ini file and restart sickbeard, the entry is replaced with

 

extra_scripts = ""

 

I do have the right path in there, it just seems to be erased

 

Any ideas?

 

Thanks

Ah. That reminds me. Make sure you stop sickbeard before you edit config.ini. I will fix the OP.

Ah. That reminds me. Make sure you stop sickbeard before you edit config.ini. I will fix the OP.

I see in the instructions that it says must be running mysql on your unraid server.  Is there something I can edit in the extra.sh script to point it to a mysql server running on a different box?

 

Getting closer...

 

Thanks!

I think it should be possible. It is a command line query on the local machine. You just need to find out how to send a remote query via command line and edit extra.sh.

Ok, so I believe that I have my script talking to a remote mysql server now.  Time for the next issue to tackle.  It seems the update is only "half" working.  What I mean by this is, I do not see a replaced show in the "newly added" section of the library, so that's good news.  What I am still seeing, however, is the old version still in the library.  I will see 2 entries for the same show.  For example, HDTV and Web-DL.

 

Here's a snippet of the log from sab

 

Found release name Once.Upon.a.Time.S03E07.720p.WEB-DL.DD5.1.H.264-ECI

 

Destination folder for this episode: /nfs/tv/Once Upon a Time (2011)/Season 3

 

Moving file from /nfs/tv/Once.Upon.a.Time.S03E07.720p.WEB-DL.DD5.1.H.264-ECI/Once.Upon.a.Time.S03E07.Dark.Hollow.720p.WEB-DL.DD5.1.H.264-ECI.mkv to /nfs/tv/Once Upon a Time (2011)/Season 3/Once.Upon.A.Time.2011.S03E07.720p WEB-DL.Dark.Hollow.mkv

 

Executing command ['/home/nzb/.sickbeard/extra.sh', u'/nfs/tv/Once Upon a Time (2011)/Season 3/Once.Upon.A.Time.2011.S03E07.720p WEB-DL.Dark.Hollow.mkv', u'/nfs/tv/Once.Upon.a.Time.S03E07.720p.WEB-DL.DD5.1.H.264-ECI/Once.Upon.a.Time.S03E07.Dark.Hollow.720p.WEB-DL.DD5.1.H.264-ECI.mkv', '248835', '3', '7', '2013-11-10', u'/nfs/tv/Once Upon a Time (2011)/Season 3/Once.Upon.A.Time.2011.S03E07.HD TV.Dark.Hollow.mkv']

 

Absolute path to script: /home/nzb/.sickbeard/extra.sh

 

Script result: Replacing Once.Upon.A.Time.2011.S03E07.HD TV.Dark.Hollow.mkv with Once.Upon.A.Time.2011.S03E07.720p WEB-DL.Dark.Hollow.mkv...

 

 

 

Deleting folder /nfs/tv/Once.Upon.a.Time.S03E07.720p.WEB-DL.DD5.1.H.264-ECI

 

Processing succeeded for /nfs/tv/Once.Upon.a.Time.S03E07.720p.WEB-DL.DD5.1.H.264-ECI/Once.Upon.a.Time.S03E07.Dark.Hollow.720p.WEB-DL.DD5.1.H.264-ECI.mkv

hmm. that's strange. the output looks normal. it might not be working at all. i don't think it can really only half work, as it really only makes one change. well two changes but they are executed in one transaction, so technically, one change. it is either all or or nothing.

 

heres a rundown of what the script actually does:

1. assigns the deleted file and new file to variables
2. adds escape characters to those variables if needed (to avoid problems with files that have apostrophes in them)
3. makes 2 updates to the table for the deleted files, done in one transaction (again either both changes succeed or both fail)
4. outputs: "Replacing $deletedfile with $newfile...")

 

so it is really just one command in step 3. this is really the only place there could be a problem and if there is, it won't tell you. this was really only meant for me so i didn't bother to follow the proper programming guidelines regarding error scenarios.

 

so i think for whatever reason, the command is failing. either the database version is wrong and it is failing because it cannot find the table (you are on frodo right?) or it isn't connecting to the remote database.

 

how did you modify extra.sh to connect to the remote database? did you verify that connection works by testing it in the terminal?

Attached is my modified script file.

 

When I run the script in a terminal window, it simply says:

 

Replacing with...

 

I am also using Frodo 12.2 on the DB version.

 

extra.txt

running the script from a terminal without arguments wont do anything. and adding arguments will still give you no error output. what you need to do is verify the modified mysql command works in the terminal.

 

however since you are indeed on database v75 the query part should be fine and it is most likely just the connection part of the command that is wrong. so you really only need to see if you can remotely connect to mysql via the terminal. mysql includes a command line interface. that should be just this part:

 

mysql -u xbmc -pxbmc -h 3.14.79.112

 

the -e part of the complete command just means "execute the following command and quit" and everything after -e is actual transaction. i am not sure if the space in "-u xbmc" will be a problem. i dont remember but i do remember following guides where there were spaces and mine not liking that for some reason.

 

I can successfully connect using the following command:

 

mysql -u xbmc -pxbmc -h 3.14.79.112

 

This works both with the space and without the space after the -u

 

 

if that works then try the entire command. make sure there is actually a file to replace. i suggest creating two dummy files and name them appropriately. throw one in the proper folder and update the xbmc library as normal. it should be added to the library. then delete the file (keeping it in the library) and put the new one in the same folder. then run the full command making sure to replace the variables with the actual file name. also make sure there are no apostrophes in the filename or that will screw it up unless you escape them. for testing it's just easier to avoid using them instead of escaping them.

Ok, so I have done a few more things since I last updated the post.

 

I have upgraded mysql to version 5.5.31 from 5.1

 

If I run the extra script by hardcoding the deletedfile and newfile entries to include the exact file names, it works.  It removes the old library entry and adds the new one.  This is the expected result of the script.

 

However, if I set the deletefile and newfile entries as variables, it does not process the update/deletion.

 

This is what my current extra.sh looks like

#!/bin/bash
deletedfile=${7##*/}
newfile=${1##*/}
#Escaped character in case there is a ' in the filename
DFescaped="${deletedfile/\'/\'}"
NFescaped="${newfile/\'/\'}"
#MySQL query
query=`mysql -uxbmc -pxbmc -h 3.14.79.112 -e 'START TRANSACTION; UPDATE MyVideos75.files set strFilename="'$NFescaped'" where strFilename="'$DFescaped'"; update MyVideos75.episode set c18 = (select concat ((select strPath from MyVideos75.path where path.idpath= (select idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$NFescaped'")) where c18 =(select concat ((select strPath from MyVideos75.path where path.idpath= (select idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$DFescaped'")); COMMIT'`
echo "Replacing $deletedfile with $newfile..."

 

Again, to summarize, if I modify the script to specify the old and new files names, the script works successfully.  This should rule out any remote connection or permissions issues to the remote database.

 

I really appreciate the help.  I feel I am almost there.

thats strange. there shouldn't be a difference. there must be something else at play. what is the value of the variable? how are you running it when you set the variables? through sickbeard or the terminal?

I ran the script through the terminal.  This was the test script that I ran, which worked.

 

#!/bin/bash
deletedfile=30.Rock.S07E09.HD.TV.Florida.mkv
newfile=30.Rock.S07E09.720p WEB-DL.Game.Over.mkv
#Escaped character in case there is a ' in the filename
DFescaped="${deletedfile/\'/\'}"
NFescaped="${newfile/\'/\'}"
#MySQL query
query=`mysql -uxbmc -pxbmc -h 3.14.79.112 -e 'START TRANSACTION; UPDATE MyVideos75.files set strFilename="'$NFescaped'" where strFilename="'$DFescaped'"; update MyVideos75.episode set c18 = (select concat ((select strPath from MyVideos75.path where path.idpath= (select idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$NFescaped'")) where c18 =(select concat ((select strPath from MyVideos75.path where path.idpath= (select idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$DFescaped'")); COMMIT'`
echo "Replacing $deletedfile with $newfile..."

im a little confused. before it was the query you thought that wasn't working. now it is and the variable isnt working? it worked before because i can see it in the sickbeard log you sent me.

I am watching this closely.  I have so wanted this feature for a long time.  I almost always watch the HD TV version first and then it upgrades to the 720P WEb-DL and I always have to go mark them as watched.  I'm holding off to see off to see if indy5 gets it working because I don't have Sickbeard or MySQL on my unRAID machine.  In fact both are running on different virtual machines.  I would love to get this working!

 

Kudos to you bobbintb for all you work on this!

I have so wanted this feature for a long time.

 

Please see this and comment with your support:

http://forum.xbmc.org/showthread.php?tid=176018

 

This is a feature request to extend the XBMC API so programs like Sickbeard can do this natively and eliminate the need for this kludgery.

Ok, so I have gone back and started this process over again to try and eliminate any unnecessary changes that I made to the script.  I will attach my script code and the log that SAB creates when the files are postprocessed.  As far as the script goes, I have only edited 2 items.  I replaced /bin/sh with /bin/bash and added the -h command to access my remote mysql server.

 

The script does run, but my library will contain both the old and new entries.

 

extra.sh

#!/bin/bash
deletedfile=${7##*/}
newfile=${1##*/}
#Escaped character in case there is a ' in the filename
DFescaped="${deletedfile/\'/\'}"
NFescaped="${newfile/\'/\'}"
#MySQL query
query=`mysql - xbmc -pxbmc -h 3.14.79.112 -e 'START TRANSACTION; 
UPDATE MyVideos75.files SET strFilename="'$NFescaped'" where strFilename="'$DFescaped'"; 
UPDATE MyVideos75.episode SET c18 = (SELECT concat ((SELECT strPath from MyVideos75.path where path.idpath= (SELECT idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$NFescaped'")) where c18 =(SELECT concat ((SELECT strPath from MyVideos75.path where path.idpath= (SELECT idpath from MyVideos75.files where strFilename="'$NFescaped'")), "'$DFescaped'")); 
COMMIT'`
echo "Replacing $deletedfile with $newfile..."

 

 

sample log from SAB

Loading config from /nfs/cache/.apps/sickbeard/autoProcessTV/autoProcessTV.cfg
Opening URL: http://3.14.79.114:8081/home/postprocess/processEpisode?nzbName=The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND.nzb&quiet=1&dir=%2Fnfs%2Ftv%2FThe.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND
Processing folder /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND

Processing /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND/the.walking.dead.s03e05.720p.bluray.x264-demand.mkv (The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND.nzb)

Found result in history: (153021, 3, [])

Parsed The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND.nzb into The Walking Dead - S3E5 - 720p.BluRay.x264 (DEMAND) [ABD: False]

Checking scene exceptions for a match on The Walking Dead

Looking up The Walking Dead in the DB

Lookup successful, using tvdb id 153021

Loading show object for tvdb_id 153021

Retrieving episode object for 3x5

The old status had a quality in it, using that: 720p BluRay

SB snatched this episode so I'm marking it as priority

Is ep a priority download: True

File /nfs/tv/The Walking Dead/Season 3/The.Walking.Dead.S03E05.720p.WEB-DL.Say.the.Word.mkv is smaller than /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND/the.walking.dead.s03e05.720p.bluray.x264-demand.mkv

This download is marked a priority download so I'm going to replace an existing file if I find one

Deleting file /nfs/tv/The Walking Dead/Season 3/The.Walking.Dead.S03E05.720p.WEB-DL.Say.the.Word.mkv

Found release name The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND

Destination folder for this episode: /nfs/tv/The Walking Dead/Season 3

Moving file from /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND/the.walking.dead.s03e05.720p.bluray.x264-demand.mkv to /nfs/tv/The Walking Dead/Season 3/The.Walking.Dead.S03E05.720p BluRay.Say.the.Word.mkv

Executing command ['/home/nzb/.sickbeard/extra.sh', u'/nfs/tv/The Walking Dead/Season 3/The.Walking.Dead.S03E05.720p BluRay.Say.the.Word.mkv', u'/nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND/the.walking.dead.s03e05.720p.bluray.x264-demand.mkv', '153021', '3', '5', '2012-11-11', u'/nfs/tv/The Walking Dead/Season 3/The.Walking.Dead.S03E05.720p.WEB-DL.Say.the.Word.mkv']

Absolute path to script: /home/nzb/.sickbeard/extra.sh

Script result: Replacing The.Walking.Dead.S03E05.720p.WEB-DL.Say.the.Word.mkv with The.Walking.Dead.S03E05.720p BluRay.Say.the.Word.mkv...



Deleting folder /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND

Processing succeeded for /nfs/tv/The.Walking.Dead.S03E05.720p.BluRay.x264-DEMAND/the.walking.dead.s03e05.720p.bluray.x264-demand.mkv

i'll take another look at the query to make sure something isn't missing. there isn't more than one file in your library with the same name or anything is there?

i'll take another look at the query to make sure something isn't missing. there isn't more than one file in your library with the same name or anything is there?

 

No, the library should be in good shape.  The only alike files would be the WEB-DL and the Bluray one, in this case. 

 

Thanks again for your continued help!

i didn't think so.

 

just for kicks, try doing "show databases" in the mysql command line and let me know if "MyVideos75" shows up or if it is a different number. i'm wondering if even though you have frodo, it might be running on a different database version due to an update or something. although, if it were the command should fail altogether.

 

i think i know what is happening now and it is likely something i overlooked, which is one reason i wanted to share this. it helps to have others look at it and use it in different ways to find thing.

 

i seem to recall having come across the same issue before but just didn't really pay attention or attributed it to something else and just cleaned the library to fix it. i think there is just some residual items needing changing.

 

also, i figured out the items in the database to change by reading the xbmc wiki but the xbmc wiki is notoriously haphazardly updated. so it might be that there was an additional item that needs to be updated that was not mentioned because it was outdated. who knows.

 

anyway, what i will do is take a sql dump before and after i add a new file and compare them to see if there is anything else that gets updated that the script is not addressing.

 

you know, on second thought, i don't think that can be it. i think it might be a caching issue with xbmc. because the query replaces EVERY instance of that filename in the database:

 

UPDATE MyVideos75.files set strFilename="'$NFescaped'" where strFilename="'$DFescaped'"

 

that part looks for the name of the old file in the table that contains every filename any replaces every instance of it with the new filename. so if it is still showing up in xbmc, i think it would have to be a caching issue or something similar.

 

try again but before you do see how many times the file shows in the library with this query (substituting of course):

 

select count(*) from MyVideos75.files where strFilename="name.of.the.file.being.replaced"

 

you should get "1" back as the result.

replace the file and then run the query again after it is replaced. it should return "0".

 

if you get any other numbers, then something is wrong. if you get those number yet you still are getting the old file showing up then i would say it is a caching issue or something to do with xbmc not expecting the sql database to be modified. if so then the script really is working as expected, just producing a small unintended side effect with xbmc. but that can easily be fixed by adding a json request to the script for xbmc to check the folder after the query.

 

and just to be clear, when a file is replaced you aren't having any issues with it showing up in 'recently added' or the watched status, right?

When I run the command: select count(*) from MyVideos75.files where strFilename="name.of.the.file.being.replaced"

 

I get back "1"

 

After the file gets replaced with a new version, the result is still "1"

 

It doesn't appear to be removing the old file name from the table.

 

To answer your final question, I actually do NOT see the episode listed under "recently added."  When I browse through the appropriate season, I see both the old and new versions of episode listed.

 

Correction.  To make matters even more confusing.  Of the past 4 episodes that have been upgraded, 3 appear under the "recently added."  One of four does not.