[metaron]

For several years, I've been getting reliable Season/Episode data for mythv via xmltv grabbers (UK-RT, UK-ATLAS, now SDJSON). The xmltv grabbers store this as 'syndicatedepisodenumber' in the 'program' table and this is copied to 'recordedprogram' when a recording is made.

Mythfrontend will present 'syndicatedepisodenumber' ([E2S1]) in list views if it can't find 'season' & 'episode' in the 'recorded' table (s01e02). The problem is Kodi's pvr.mythtv client can't access 'syndicatedepisodenumber'. It needs 'season' & 'episode'.

You could run metadata lookup to populate 'season' & 'episode' but it is very hit and miss. You could enter this information all manually using mythfrontend, but would you bother?

The solution I use is an SQL query which converts 'syndicatedepisodenumber' into 'season' & 'episode'. This runs as a custom job after each recording completes.

The Concept

1. New recording rules created by kodi use the Default mythtv template
   
2. The Default mythtv recording template runs 'User Job #1'
   
3. User Job #1 runs 'fix_thisSeasonEpisode'
   
4. fix_thisSeasonEpisode populates 'season' and 'episode' in the 'recorded' table based on 'syndicatedepisodenumber' from the 'recordedprogram' table
   
5. kodi picks this information up and presents it using a modified skin (https://github.com/xbmc/xbmc/pull/11969) or (https://github.com/xbmc/skin.confluence/pull/22)
   

How to do it

1. Back up your myth database
   • This step isn't strictly necessary. If you regularly bungee jump without checking the rope first feel free to skip it.
     
   • Run /usr/share/mythtv/mythconverg_backup.pl --verbose and ensure it worked. If not get this working first.
     
   
   
2. Set up Kodi
   • Go to Add-ons->My AddOns->PVR clients->MythTV PVR Client->Configure->Recording template
     
   • Ensure 'Template provider' is set to MythTV.
     
   
   
3. Set up User Job #1
   • mythtv-setup -> 'General' settings, Job Queue (Job Commands) page
     
   • User Job #1 description: FillSeasonEpisode
     
   • User Job #1 command: /home/mythtv/bin/fix_thisSeasonEpisode "%CHANID%" "%STARTTIME%" "%TITLE%"
     
   • mythtv-setup -> 'General' settings, Job Queue (Backend-Specific) page
     
   • Ensure "Allow FillSeasonEpisode jobs" is ticked,
     
   
   
4. Set up the Default recording template
   • mythfrontend ->Manage Recordings->Recording Rules
     
   • find "Default (Template)" in the list and select it
     
   • press the 'm' key to bring up the menu and select 'Post Processing'
     
   • Ensure 'Run 'FillSeasonEpisode'' is ticked
     
   • Select 'Back' then 'Save' to save the template
     
   • (optional) repeat this sequence for any existing rules you want season/episode data for
     
   
   
5. Ensure your system has the correct configuration
   • Ensure that the /var/log/mythtv directory exists and the 'mythtv' user has read/write access to it
     
   • Ensure that the /tmp directory exists and the 'mythtv' user has read/write access to it
     
   • Ensure the /home/mythtv/bin directory exists and the 'mythtv' user has at least read access to it
     
   • Create files fix_thisSeasonEpisode and this_seasonepisode.sql in /home/mythtv/bin and paste in the content from below
     
   • Adjust the content of fix_thisSeasonEpisode, especially 'mysql -umythtv -pmythtv --database mythconverg' as necessary (mysql database password).
     
   • make fix_thisSeasonEpisode executable (hint: chmod +x)
     

Any new rules you create (via kodi, mythfrontend or mythweb) should now automatically populate season/episode information after the recordings finish. /var/log/mythtv/this_seasonepisode.log will keep track of what happens.

fix_thisSeasonEpisode (put it in /home/mythtv/bin and make it executable)

Code:

#!/bin/bash
echo `/bin/date` ": updating season/episode for chanid \"$1\" starttime \"$2\" \"$3\"" &>> /var/log/mythtv/this_seasonepisode.log
echo "SET @ChanID=\"$1\", @StartTime=\"$2\";" > /tmp/this_seasonepisode.sql
cat /home/mythtv/bin/this_seasonepisode.sql >> /tmp/this_seasonepisode.sql
mysql -umythtv -pmythtv --database mythconverg < /tmp/this_seasonepisode.sql &>> /var/log/mythtv/this_seasonepisode.log

this_seasonepisode.sql (put it in /home/mythtv/bin)

Code:

-- MSQL Query to update season/episode based on UK_RadioTimes xmltvid data format ExxSxx (most series)
update recorded
inner join recordedprogram on
(
recorded.programid=recordedprogram.programid and
recorded.title=recordedprogram.title and
recorded.subtitle=recordedprogram.subtitle
)
set
recorded.season = mid(recordedprogram.syndicatedepisodenumber,locate("S",recordedprogram.syndicatedepisodenumber)+1),
recorded.episode = mid(recordedprogram.syndicatedepisodenumber,2,locate("S",recordedprogram.syndicatedepisodenumber)-2)
where
recorded.chanid = @ChanId and
recorded.starttime = @StartTime and
recorded.title!="" and
recorded.programid!="" and
recordedprogram.syndicatedepisodenumber!="" and
recorded.episode=0 and
recorded.season=0 and
recordedprogram.syndicatedepisodenumber like "E%S%";
-- MSQL Query to update season/episode based on UK_RadioTimes xmltvid data format Exxxx (Mostly soaps and specials)
update recorded
inner join recordedprogram on
(
recorded.programid=recordedprogram.programid and
recorded.title=recordedprogram.title and
recorded.subtitle=recordedprogram.subtitle
)
set
recorded.season = 0,
recorded.episode = mid(recordedprogram.syndicatedepisodenumber,2)
where
recorded.chanid = @ChanId and
recorded.starttime = @StartTime and
recorded.title!="" and
recorded.programid!="" and
recordedprogram.syndicatedepisodenumber!="" and
recorded.episode=0 and
recorded.season=0 and
recordedprogram.syndicatedepisodenumber like "E%" and
recordedprogram.syndicatedepisodenumber not like "%S%";

What about all my existing recordings!

1. create fix_seasonepisode and fix_seasonepisode.sql in the /home/mythtv/bin directory and paste in the content below
   
2. Adjust the content of fix_seasonepisode, especially 'mysql -umythtv -pmythtv --database mythconverg' as necessary (mysql database password).
   
3. make fix_seasonepisode executable
   
4. run fix_seasonepisode
   

fix_seasonepisode (put it in /home/mythtv/bin and make it executable)

Code:

#!/bin/bash
echo "Updating recorded season/episode based on recordedprogram syndicatedepisodenumber where they are currently zero based on matching non-zero programid,title,subtitle"
mysql -umythtv -pmythtv --database mythconverg < /home/mythtv/bin/fix_seasonepisode.sql

fix_seasonepisode.sql (put it in /home/mythtv/bin)

Code:

-- MSQL Query to update season/episode based on UK_RadioTimes xmltvid data format ExxSxx (most series)
update recorded
inner join recordedprogram on
(
recorded.programid=recordedprogram.programid and
recorded.title=recordedprogram.title and
recorded.subtitle=recordedprogram.subtitle
)
set
recorded.season = mid(recordedprogram.syndicatedepisodenumber,locate("S",recordedprogram.syndicatedepisodenumber)+1),
recorded.episode = mid(recordedprogram.syndicatedepisodenumber,2,locate("S",recordedprogram.syndicatedepisodenumber)-2)
where
recorded.title!="" and
recorded.programid!="" and
recordedprogram.syndicatedepisodenumber!="" and
recorded.episode=0 and
recorded.season=0 and
recordedprogram.syndicatedepisodenumber like "E%S%";
-- MSQL Query to update season/episode based on UK_RadioTimes xmltvid data format Exxxx (Mostly soaps and specials)
update recorded
inner join recordedprogram on
(
recorded.programid=recordedprogram.programid and
recorded.title=recordedprogram.title and
recorded.subtitle=recordedprogram.subtitle
)
set
recorded.season = 0,
recorded.episode = mid(recordedprogram.syndicatedepisodenumber,2)
where
recorded.title!="" and
recorded.programid!="" and
recordedprogram.syndicatedepisodenumber!="" and
recorded.episode=0 and
recorded.season=0 and
recordedprogram.syndicatedepisodenumber like "E%" and
recordedprogram.syndicatedepisodenumber not like "%S%";

Notes
This code will only populate 'season' and 'episode' if:

• it finds a 100% match using 'programid', 'title' and 'subtitle'
  
• 'title' and 'programid' are not empty and
  
• 'season' and 'episode' are currently set to 0.
  

This means it won't over-write any existing manually entered or metadata lookup sourced season/episode information, even if it is 'wrong'. I see this as a safety feature.

If like me you deleted the content of 'programid' when you switched from UK-ATLAS to SDJSON, I'm afraid you are on your own. You could delete the condition which checks for a non-empty programid, add a match condition on 'description' and try to run the script like that. It might work as you expect, but probably won't.

If you use this and it works for you, let me know.
If it doesn't work, post that as well. I might be able to help!

You did make sure you had a recent backup of your mythtv database before starting, didn't you?

[bonelifer]

Thanks for posting this. I had to switch to the new sdjson-sqlite utility after I had to do a reinstall and couldn't get mfdb-json to reinstall. It's nice not to have to add the ep/s manually via phpmyadmin. I've made some changes so that users don't have to put their username/password in yet another file. It gets it from the same config.xml that myth frontend uses. Hope this helps someone.

fix_seasonepisode

Code:

#!/bin/bash

#Try getting mythtv database information from existent files in the following order
MYTHCONFIGFILE="/etc/mythtv/config.xml"
MYCONFIGFILE='~/.mythtv/config.xml'

if [ -f "$MYTHCONFIGFILE" ]; then
        DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYTHCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"
        DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYTHCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"
        DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYTHCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"
        DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYTHCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"
elif [ -f "$MYCONFIGFILE" ]; then
        DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"
        DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"
        DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"
        DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"
fi


echo "Updating recorded season/episode based on recordedprogram syndicatedepisodenumber where they are currently zero based on matching non-zero programid,title,subtitle"
mysql -u"$DBUSER" -p"$DBPASS" --database "$DBNAME" < /home/mythtv/bin/fix_seasonepisode.sql


fix_thisSeasonEpisode

Code:

#!/bin/bash

#Try getting mythtv database information from existent files in the following order
MYTHCONFIGFILE="/etc/mythtv/config.xml"
MYCONFIGFILE='~/.mythtv/config.xml'

if [ -f "$MYTHCONFIGFILE" ]; then
DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYTHCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"
DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYTHCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"
DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYTHCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"
DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYTHCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"
elif [ -f "$MYCONFIGFILE" ]; then
DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"
DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"
DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"
DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"
fi


echo `/bin/date` ": updating season/episode for chanid \"$1\" starttime \"$2\" \"$3\"" &>> /var/log/mythtv/this_seasonepisode.log
echo "SET @ChanID=\"$1\", @StartTime=\"$2\";" > /tmp/this_seasonepisode.sql
cat /home/mythtv/bin/this_seasonepisode.sql >> /tmp/this_seasonepisode.sql
mysql -u"$DBUSER" -p"$DBPASS" --database "$DBNAME" < /tmp/this_seasonepisode.sql &>> /var/log/mythtv/this_seasonepisode.log


[metaron]

Thanks for posting this. I had to switch to the new sdjson-sqlite utility after I had to do a reinstall and couldn't get mfdb-json to reinstall. It's nice not to have to add the ep/s manually via phpmyadmin. I've made some changes so that users don't have to put their username/password in yet another file. It gets it from the same config.xml that myth frontend uses. Hope this helps someone.

Thanks @bonelifer. I tried your revised version but had some trouble with the script on my gentoo (bash based) system (anyone know what's up with quoted stings and [] ?)
I got it working and made what I believe are some minor improvements;

1. check you can read the file, not just that it exists,
   
2. prefer ~/.mythtv/config.txt over /etc/mythtv/config.txt
   
3. print an error if it fails to find either file
   
4. cater for different database host
   

let me know if this works for you. If it does i'll update the first post as this seems a more maintainable approach.

fix_thisSeasonEpisode

bash:
#!/bin/bash



#Try getting mythtv database information from existent files in the following order

MYTHCONFIGFILE=/etc/mythtv/config.xml

MYCONFIGFILE=~/.mythtv/config.xml

DBCONFIG=false



if [ -r "$MYCONFIGFILE" ]; then

DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"

DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"

DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"

DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"

DBCONFIG=true

elif [ -r "$MYTHCONFIGFILE" ]; then

DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYTHCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"

DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYTHCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"

DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYTHCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"

DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYTHCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"

DBCONFIG=true

fi



if [ $DBCONFIG == true ]; then

echo `/bin/date` ": updating season/episode for chanid \"$1\" starttime \"$2\" \"$3\"" &>> /var/log/mythtv/this_seasonepisode.log

echo "SET @ChanID=\"$1\", @StartTime=\"$2\";" > /tmp/this_seasonepisode.sql

cat /home/mythtv/bin/this_seasonepisode.sql >> /tmp/this_seasonepisode.sql

mysql -h"$DBSERVER" -u"$DBUSER" -p"$DBPASS" --database "$DBNAME" < /tmp/this_seasonepisode.sql &>> /var/log/mythtv/this_seasonepisode.log

else

echo ERROR: Unable to access $MYCONFIGFILE or $MYTHCONFIGFILE

fi

fix_seasonepisode

bash:
#!/bin/bash



#Try getting mythtv database information from existent files in the following order

MYTHCONFIGFILE=/etc/mythtv/config.xml

MYCONFIGFILE=~/.mythtv/config.xml

DBCONFIG=false



if [ -r $MYCONFIGFILE ]; then

DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"

DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"

DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"

DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"

DBCONFIG=true

elif [ -r $MYTHCONFIGFILE ]; then

DBSERVER="$( grep -E -m 1 -o "<Host>(.*)</Host>" $MYTHCONFIGFILE | sed -e 's,.*<Host>\([^<]*\)</Host>.*,\1,g' )"

DBUSER="$( grep -E -m 1 -o "<UserName>(.*)</UserName>" $MYTHCONFIGFILE | sed -e 's,.*<UserName>\([^<]*\)</UserName>.*,\1,g' )"

DBNAME="$( grep -E -m 1 -o "<DatabaseName>(.*)</DatabaseName>" $MYTHCONFIGFILE | sed -e 's,.*<DatabaseName>\([^<]*\)</DatabaseName>.*,\1,g' )"

DBPASS="$( grep -E -m 1 -o "<Password>(.*)</Password>" $MYTHCONFIGFILE | sed -e 's,.*<Password>\([^<]*\)</Password>.*,\1,g' )"

DBCONFIG=true

fi



if [ $DBCONFIG == true ]; then

echo "Updating recorded season/episode based on recordedprogram syndicatedepisodenumber where they are currently zero based on matching non-zero programid,title,subtitle"

mysql -h"$DBSERVER" -u"$DBUSER" -p"$DBPASS" --database "$DBNAME" < /home/mythtv/bin/fix_seasonepisode.sql

else

echo ERROR: Unable to access $MYCONFIGFILE or $MYTHCONFIGFILE

fi