Multiple Source Metadata Copy

These scripts will help transfer valid metadata from one source (say Freeview) to another source (Freesat) which perhaps doesn't have enough data as the other. It is also useful to ensure programs on one source have the same programid on the other to avoid duplicated recordings.

Channel Config

This is a file which can be imported by a few different scripts listed.

Channel Set is a set of key pairs; these are service ids for matching channels, it assumes the service ids are unique across all videosources which will not always be true, but I found it was in the case of UK TV. This example if a mapping from Freeview to Freesat. Note timeshifted channels are also included which explains why service 8273 is mapped to 10220, 8373 and 10225. 10220 is the same channel on a different video source, but 8373 and 10225 are '+1' channels, 8373 on the same source and 10225 on the other video source. This ensures +1 channels also receive the identical metadata.

TS Channels is a set of service ids that should have the start time and end time of their programs adjusted by 1 hour when copied.

HD Channels is a set of HD services, this is used in another enrichment script which attempts to markup the HD flag according to the metadata descriptions.

channels.py

channel_set = set([ (4220,6421) , (4284,6422), (8273,10220) , (8273,8373) , (8273, 10225) , (8384, 9216) , (8500,7703) , (13024,7720) , (4288, 6319) , (4544, 6416) , (8325, 10070) , (15952, 10165) , (16048, 10260) , (16048, 10261) , (28032, 10072) , (28032, 10015) , (8448, 8305) , (8442, 8340) , (8442, 9230) , (4352,6405) , (22080,4704) , (27136, 9220) , (27136, 9225) , (22336, 8300) , (4608,6317) , (4672,6418) , (16032,10071) , (4736,6705) , (8452, 8316) , (53360, 53370) , (50880, 54066) , (52125,54069) , (53330,53345) , (12992,7710) , (12992,7711), (12928,7715) , (12928,7716)]);

ts_channels = set([10261, 10015,  8373, 10225, 9230,  9225, 52370, 54066, 54069, 52345, 7711, 7716]);

hd_channels = set([10000,6940,3851,3855,54206])

Dummy Run

The following script will not update your database, you will need to update the password. It will show the changes the channels.py configuration will result; it uses the videosource names and channel names to make things more readable.

demo_copy_guide_data.py

#!/usr/bin/python

import MySQLdb
import channels;

conn = MySQLdb.connect (host = "localhost",
                           user = "mythtv",
                           passwd = "password",
                           db = "mythconverg")

c0 = conn.cursor();
c1 = conn.cursor();
c2 = conn.cursor();
c3 = conn.cursor();

for channel_tuple in channels.channel_set:
	chanid = channel_tuple[0];
	dest_chanid = channel_tuple[1];

	c0.execute("""SELECT callsign FROM channel WHERE serviceid='%s'""" % chanid);
	c1.execute("""SELECT callsign FROM channel WHERE serviceid='%s'""" % dest_chanid);

	c2.execute("""SELECT videosource.name FROM channel,videosource WHERE channel.sourceid=videosource.sourceid AND channel.serviceid='%s'""" % chanid);
	c3.execute("""SELECT videosource.name FROM channel,videosource WHERE channel.sourceid=videosource.sourceid AND channel.serviceid='%s'""" % dest_chanid);

	c0array = c0.fetchone();
	c1array = c1.fetchone();
	c2array = c2.fetchone();
	c3array = c3.fetchone();

	if (c0array == None):
		print "chanid %s not found in db" % chanid 
	if (c1array == None):
		print "dest_chanid %s not found in db" % dest_chanid 
		
	if (c0array != None and c1array != None):
		c0value = c0array[0];
		c1value = c1array[0];
		c2value = c2array[0];
		c3value = c3array[0];
	
		if (dest_chanid in channels.ts_channels):
			print "%s (%s) [%s] -> %s (%s) [%s] [TS]" % (c0value, c2value, chanid, c1value, c3value, dest_chanid);
		else:	
			print "%s (%s) [%s] -> %s (%s) [%s]" % (c0value, c2value, chanid, c1value, c3value, dest_chanid);

c0.close();
c1.close();

conn.close()

Update Script

Again you will need to update the password in this script, but it will perform the metadata updates as described above.

demo_copy_guide_data.py

#!/usr/bin/python

import MySQLdb
import channels;

from datetime import timedelta

conn = MySQLdb.connect (host = "localhost",
                           user = "mythtv",
                           passwd = "password",
                           db = "mythconverg")

one_hour = timedelta(hours=1);

c0 = conn.cursor();
c1 = conn.cursor();
c2 = conn.cursor();
c3 = conn.cursor();
c4 = conn.cursor();
c5 = conn.cursor();
c6 = conn.cursor();

for channel_tuple in channels.channel_set:
	serviceid = channel_tuple[0];
	dest_serviceid = channel_tuple[1];

	if (dest_serviceid in channels.ts_channels):
		print "%s -> %s (Timeshifted)" % (serviceid, dest_serviceid);
	else:	
		print "%s -> %s" % (serviceid, dest_serviceid);

	c4.execute("""SELECT chanid FROM channel WHERE serviceid='%s'""" % serviceid);
	c5.execute("""SELECT chanid FROM channel WHERE serviceid='%s'""" % dest_serviceid);

	chanid_row = c4.fetchone();
	dest_chanid_row = c5.fetchone();

	if (chanid_row == None):
		print "Cannot map %s to chanid" % serviceid;
		continue;
	if (dest_chanid_row == None):
		print "Cannot map %s to chanid" % serviceid;
		continue;

	chanid = chanid_row[0];
	dest_chanid = dest_chanid_row[0];

	print "channel ids: %s => %s" % (chanid,dest_chanid);
	
	#Switch of setting of EIT for channels configured for copy
	c6.execute("""UPDATE channel SET useonairguide=0 WHERE chanid='%s'""" % dest_chanid);
	c0.execute("""DELETE FROM program WHERE chanid='%s'""" % dest_chanid);
	c1.execute("""SELECT * FROM program WHERE chanid='%s'""" % chanid);

	while 1:
		row = c1.fetchone();
		if (row == None):
			break;
		else:
			starttime = row[1];
			endtime = row[2];
			newstarttime = starttime;
			newendtime = endtime;
			if (dest_serviceid in channels.ts_channels):
				newstarttime += one_hour;
				newendtime += one_hour;
		
			c2.execute("""SELECT * FROM program WHERE chanid='%s' and starttime='%s'""" % (dest_chanid, newstarttime));
			existing_entry = c2.fetchone();
			if (existing_entry == None):
				c3.execute("""INSERT INTO program (chanid,starttime,endtime,title,subtitle,description,category,category_type,airdate,stars,previouslyshown,title_pronounce,stereo,subtitled,hdtv,closecaptioned,partnumber,parttotal,seriesid,originalairdate,showtype,colorcode,syndicatedepisodenumber,programid,manualid,generic,listingsource,first,last,audioprop,subtitletypes,videoprop) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s')""" % (dest_chanid,newstarttime,newendtime,conn.escape_string(row[3]),conn.escape_string(row[4]),conn.escape_string(row[5]),row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row[20],row[21],row[22],row[23],row[24],row[25],row[26],row[27],row[28],row[29],row[30],row[31]));

c1.close();
c2.close();
c3.close();

conn.close()

HD Metadata Flag

This script will scan the metadata descriptions and attempt to guess whether the content is really HD or not. Update your password in the connection string.

channels.py

#!/usr/bin/python

import MySQLdb

conn = MySQLdb.connect (host = "localhost",
                           user = "mythtv",
                           passwd = "password",
                           db = "mythconverg")

c0 = conn.cursor();
c0.execute("""update program set hdtv=0,videoprop="WIDESCREEN" where description like "%[Upscaled HD]%";""")
c0.execute("""update program set description=replace(description,"[Upscaled HD]","") where description  like "%[Upscaled HD]%";""")
c0.execute("""update program set hdtv=1,videoprop="HDTV,WIDESCREEN" where description like "%[HD]%";""")
c0.execute("""update program set description=replace(description,"[HD]","") where description  like "%[HD]%";""")
c0.close();

conn.close ()