Naming conflict (spotname in Spotweb)

notrinity · January 20, 2016, 2:59pm

Hi there,

I’m sorry if this question is already answered in some other post, I haven’t found it so here’s the thing: I have my own Spotweb config running as a breeze and it is working with API flawlessly. However it occurs to me that some posters use a different style of season and episode naming (example: Flikken Rotterdam Se.1 afl.1) Sonarr wil not find these releases. If I would manually rename them to Flikken Rotterdam S01E01 DUTCH 1080p HDTV it will be picked up by Sonarr.

Is there something I can do to resolve this or would I be stuck to renaming each episode manually when it’s aired. This is not what a PVR is for ofcourse

Thanks for any help!!

Mirx_NL · January 20, 2016, 4:14pm

Nope, and QoQ year alteration will also screw things up.

notrinity · January 20, 2016, 4:28pm

That’s very unfortunate. I suppose I will have to stick to manual renaming then. Thanks for your reply!

Mirx_NL · January 20, 2016, 5:48pm

Edit: Forget what I said here.

To explain in a little more detail.

Sonarr will search using the season and episode parameters, because Spotweb tells it supports these parameters.

http://localhost/spotweb/api?t=tvsearch&cat=5030,5040&extended=1&apikey=&offset=0&limit=100&q=Flikken Rotterdam&season=1&ep=1

In this case the &ep=1 query results in the spots not being on the XML output, so Sonarr can never parse them.

Just changing the URL will give the results, still questionable if the Sonarr parser will resolve the correct season / episode.
http://localhost/spotweb/api?t=tvsearch&cat=5030,5040&extended=1&apikey=&offset=0&limit=100&q=Flikken Rotterdam&season=1

If you do a search for all monitored episodes in the season, that is the exact URL Sonarr will use.
http://localhost/spotweb/api?t=tvsearch&cat=5030,5040&extended=1&apikey=&offset=0&limit=100&q=Flikken Rotterdam&season=1

As the parser won’t handle them correctly, searching and RSS feeds won’t work.

notrinity · January 20, 2016, 6:13pm

Would it be possible to fool the parser?

Mirx_NL · January 20, 2016, 6:25pm

Yes, as it’s based on regex queries, but then you would need to alter it every Sonarr update. And you’ll might be introducing unwanted hits.

Adding a regex to the parser would only fix the rss side though, for search you would still need to either do the season search, or also trick the spotweb API output. Easiest would be to tell Sonarr you don’t support episode search. That would only result in more intensive queries when searching for something.

I would (and actually have) just accept Dutch posters are a weird bunch, and like to do things the difficult way. Tweaking work-around is just too much hassle.

notrinity · January 20, 2016, 8:43pm

Ya I know, why not just all use the same format… Thanks for your explanation, if you ever think of a brilliant solution please keep me posted

mesa57 · January 24, 2016, 9:29am

People who spot on spotnet often doen’t care about the show naming. Of course you can leave a comment with the post to ask them to do so. But I have the impression that they do not like leeching program’s like sonarr or sickbeard, because user do not look at there posts anymore, and don’t leave any comments/thank you’s.

Mirx_NL · January 24, 2016, 9:52am

I decided I’m going to give altering the spot names in the database in a scheduled sql query a go.

If QoQ in tv -> strip any year annotion and nl-sub

Or

If poster = QoQ -> …

If that works, same could be done for adding s0xexx besed on certain criteria.

That would alter the RSS output so Sonarr can grab the files automatically.

Going to be a challenge for me though, not experienced at all with sql queries and regex.

notrinity · January 24, 2016, 10:43am

For some reason the thanks yous for posting/spotting is what some people drive on. It’s good to be recognized, but that system has less future than automated systems I suppose.

notrinity · January 24, 2016, 10:44am

Thanks Mirx, take your time! Curious to see if you can pull it off

Mirx_NL · January 26, 2016, 7:37am

Well, I did a basic integration test to see what needs to be altered in the database, and that is pretty straight forward.
Even though the database tables are massively intertwined, and similar data is stored is several places, there is only one field that needs updating.

Changed title in spots table to: XXX S03E01 H.264 (WEB-DL) DD5.1 1080p -Q o Q-

NZB download from RSS feed: XXX (2016) S03E01 H_264 (WEB-DL) DD5_1 1080p NLSubs -Q o Q-.nzb
This NZB filename is based on the name in the XML in spotsfull (different table).

SABnzbd download name passed by Sonarr: THE 100 S03E01 H.264 (WEB-DL) DD5.1 1080p -Q o Q- (same as title in spots)

Result: Succes, even with obscured filename in subfolders

Sonarr Import result:

Name:
XXX S03E01 H.264 (WEB-DL) DD5.1 1080p -Q o Q-

Source:
…/XXX S03E01 H.264 (WEB-DL)
DD5.1 1080p -Q o Q-/XXX680e2e5d3dd3bc26d5fb15d828f54/XXX828d51bf5d62cb3dd3d5e2e086301.mkv

Imported To:
…/XXX/XXX S03E01 H.264 (WEB-DL) DD5.1
1080p -Q o Q-.mkv

So this shows just altering the title in the spots table will enable RSS handling by Sonarr.

Now on to the harder part, writing a query that doesn’t stall my Synology because the spots table is too big (980,336 rows / 390 MB)

Using a multiple replace for (2014), (2015), and (2016) next to NLSubs, and limiting it to the latest 25 posts should work. Unfortunately the MariaDB version on my Synology does not support regex. Have to give wildcards a go.

Luckilly, now that the basic test is done, I can do the rest in a test database.

notrinity · January 26, 2016, 8:04am

Preliminary results sound terrific! However some things are like abracadabra to me

Mirx_NL · January 28, 2016, 7:11am

I ended up scheduling 3 SQL queries in myphpadmin. Couldn’t manage to get it into a single update command. Nested replace only works with select, and my version of MariaDB does not support regex and wildcards.

I used spotterid instead of poster to limit enabling spam in Sonarr.

Remove NL Subs from QoQ Series:
UPDATE spots SET title= REPLACE (title, ’ NLSubs ', ’ ') WHERE spotterid = ‘C7w1uw’ AND subcatz = ‘z1|’

Remove (2015) from QoQ Series:
UPDATE spots SET title= REPLACE (title, ‘(2015)’, ‘’) WHERE spotterid = ‘C7w1uw’ AND subcatz = ‘z1|’

Remove (2016) from QoQ Series:
UPDATE spots SET title= REPLACE (title, ‘(2016)’, ‘’) WHERE spotterid = ‘C7w1uw’ AND subcatz = ‘z1|’

As for the posts by Molly, it would be hard to replace her naming schema automatically as she (I assume) is inconsistent as hell.

afl.3
Se.1afl.3
Se.1 afl.3
SE.2 afl.3
Serie 2 afl.2

Everything would need it’s own query, and I have no idea how much that would impact the performance of the machine.

The best I can come up with is:
1 query to replace ’ afl.’ to E0 (Not sure if Sonarr will properly pass E010 though)
1 query to replace ‘afl.’ to E0 (Not sure if Sonarr will properly pass E010 though)
1 query per season replace, so replace ‘Se.’ to ‘S0’ (Not sure if Sonarr will properly pass S010 though, ‘SE.’ to ‘S0’,
'Serie ’ to ‘S0’, etc.

As update is permanent, you would need to test that on a separate database though, to make sure it doesn’t screw things up.