| 1 | #!/usr/bin/gawk -f |
|---|
| 2 | # Script to update channel and transport definitions on mythtv |
|---|
| 3 | # with dvb-utils's scan utility (which stores data in channels.conf) |
|---|
| 4 | # Written by Mario Chisari, v1.1 2008-08-12 |
|---|
| 5 | # Supports mythtv 0.21 |
|---|
| 6 | # This software is public domain |
|---|
| 7 | # |
|---|
| 8 | # HOWTO |
|---|
| 9 | # scan -5 -n -v <init-location-DVB-stations> > ~/.tzap/channels.conf |
|---|
| 10 | # mysqldump -u mythtv -p --opt mythconverg dtv_multiplex > dtv_multiplex_export.sql |
|---|
| 11 | # mysqldump -u mythtv -p --opt mythconverg channel > channel_export.sql |
|---|
| 12 | # <this_script> ~/.tzap/channels.conf |
|---|
| 13 | # mysql -u mythtv -p mythconverg < dtv_multiplex_import.sql |
|---|
| 14 | # mysql -u mythtv -p mythconverg < channel_import.sql |
|---|
| 15 | # |
|---|
| 16 | # Set debug level with '-v debug=<n>' from command |
|---|
| 17 | # line (<this_script> -v debug=<n> ~/.tzap/channels.conf) |
|---|
| 18 | # n=1-4 user level debug; n=5-9 developer level debug |
|---|
| 19 | |
|---|
| 20 | # Function to strip quote characters around a string |
|---|
| 21 | function dequote(s, tmps) { |
|---|
| 22 | tmps = s |
|---|
| 23 | sub(/^'/, "", tmps) |
|---|
| 24 | sub(/'$/, "", tmps) |
|---|
| 25 | if (debug>8) print "dequote: " s "->" tmps |
|---|
| 26 | return tmps |
|---|
| 27 | } |
|---|
| 28 | |
|---|
| 29 | # This function returns a network id number for a transport, given its frequency |
|---|
| 30 | # You can modify values according to your location, or simply return 0 (unharmful) |
|---|
| 31 | function defaultnid(freq, retval) { |
|---|
| 32 | # Zona Roma |
|---|
| 33 | if (debug>8) print "Defaultnid: called with freq=" freq |
|---|
| 34 | retval=0 |
|---|
| 35 | if (freq==186000000) retval=318 |
|---|
| 36 | if (freq==626000000) retval=272 |
|---|
| 37 | if (freq==698000000) retval=318 |
|---|
| 38 | if (freq==730000000) retval=29 |
|---|
| 39 | if (freq==762000000) retval=272 |
|---|
| 40 | if (freq==810000000) retval=272 |
|---|
| 41 | if (debug>8) print "Defaultnid: returning " retval |
|---|
| 42 | return retval |
|---|
| 43 | } |
|---|
| 44 | |
|---|
| 45 | # Ditto, for transport id |
|---|
| 46 | function defaulttid(freq, retval) { |
|---|
| 47 | if (debug>8) print "Defaulttid: called with freq=" freq |
|---|
| 48 | retval=0 |
|---|
| 49 | if (freq=762000000) retval=901 |
|---|
| 50 | if (debug>8) print "Defaulttid: returning " retval |
|---|
| 51 | return 0 |
|---|
| 52 | } |
|---|
| 53 | |
|---|
| 54 | BEGIN { |
|---|
| 55 | # Modify here your file names |
|---|
| 56 | CHAN_SQL_OUT="channel_import.sql" # Output file; will be imported in mysql |
|---|
| 57 | CHAN_SQL_IN="channel_export.sql" # Input file, used to get old data |
|---|
| 58 | CHAN_SQL_CMD="INSERT INTO `channel` VALUES " # SQL command to insert channel data |
|---|
| 59 | MPX_SQL_OUT="dtv_multiplex_import.sql" # Output file; will be imported in mysql |
|---|
| 60 | MPX_SQL_IN="dtv_multiplex_export.sql" # Input file, used to get old data |
|---|
| 61 | MPX_SQL_CMD="INSERT INTO `dtv_multiplex` VALUES " # SQL command to insert mpx data |
|---|
| 62 | |
|---|
| 63 | # Field separator for input files |
|---|
| 64 | FS="," |
|---|
| 65 | # Read dtv_multiplex export file, and copy it to output line by line |
|---|
| 66 | # until an "insert" statement found |
|---|
| 67 | r=getline <MPX_SQL_IN |
|---|
| 68 | print > MPX_SQL_OUT |
|---|
| 69 | do { |
|---|
| 70 | r=getline <MPX_SQL_IN |
|---|
| 71 | # Get rid of AUTO_INCREMENT in output |
|---|
| 72 | sub(/AUTO_INCREMENT=[0-9]* /,"") |
|---|
| 73 | if (r>0 && $0 !~ "^" MPX_SQL_CMD) \ |
|---|
| 74 | print >> MPX_SQL_OUT |
|---|
| 75 | } while (r>0 && $0 !~ "^" MPX_SQL_CMD) |
|---|
| 76 | if (r==0) { print "No insert clause found in " MPX_SQL_IN ; exit } |
|---|
| 77 | # Strip leading insert clause from input, to parse each record in it |
|---|
| 78 | sub(MPX_SQL_CMD, "") ; if (debug>7) print |
|---|
| 79 | # Strip trailing semicolon from input |
|---|
| 80 | if (sub( /;$/ , "")==0) { |
|---|
| 81 | print "Not a whole insert line in " MPX_SQL_IN ; exit} |
|---|
| 82 | # Prepare a variable where we will build a new insert command. We will write |
|---|
| 83 | # it all at once at the end |
|---|
| 84 | sql_insert_mpx = MPX_SQL_CMD |
|---|
| 85 | |
|---|
| 86 | # Split old transport individual records |
|---|
| 87 | print "Previous transports read: " split ($0, prevmpx, /\),\(/) |
|---|
| 88 | for (r in prevmpx) { |
|---|
| 89 | $0 = prevmpx[r] |
|---|
| 90 | # Strip leading and trailing brackets |
|---|
| 91 | sub(/^\(|\)$/,"") ; gsub(/\\'/,"'") |
|---|
| 92 | if (debug>6) print |
|---|
| 93 | if (debug>4) print "Storing old trsp id " $1 " freq " $5 " nid/tid " $4 "/" $3 |
|---|
| 94 | # Store references: old transportid->frequency, frequency->nid, frequency->tid |
|---|
| 95 | oldtr_freq[$1]=$5 |
|---|
| 96 | oldtr_nid[$5]=$4 |
|---|
| 97 | oldtr_tid[$5]=$3 |
|---|
| 98 | } |
|---|
| 99 | |
|---|
| 100 | # Like above, read channel export file, and copy it to output line by line |
|---|
| 101 | # until an "insert" statement found |
|---|
| 102 | r=getline <CHAN_SQL_IN |
|---|
| 103 | print > CHAN_SQL_OUT |
|---|
| 104 | do { |
|---|
| 105 | r=getline <CHAN_SQL_IN |
|---|
| 106 | sub(/AUTO_INCREMENT=[0-9]* /,"") |
|---|
| 107 | if (r>0 && $0 !~ CHAN_SQL_CMD) \ |
|---|
| 108 | print >> CHAN_SQL_OUT |
|---|
| 109 | } while (r>0 && $0 !~ CHAN_SQL_CMD) |
|---|
| 110 | if (r==0) { print "No insert clause found in " CHAN_SQL_IN ; exit} |
|---|
| 111 | # Strip leading insert clause from input, to parse each record in it |
|---|
| 112 | sub( CHAN_SQL_CMD, "") ; if (debug>7) print |
|---|
| 113 | # Strip trailing semicolon from input |
|---|
| 114 | if (sub( /;$/ , "")==0) { |
|---|
| 115 | print "Not a whole insert line in " CHAN_SQL_IN ; exit} |
|---|
| 116 | # Prepare a variable where we will build a new insert command. We will write |
|---|
| 117 | # it all at once at the end |
|---|
| 118 | sql_insert_chan = CHAN_SQL_CMD |
|---|
| 119 | |
|---|
| 120 | print "Previous channels read:" split ($0, prevchan, /\),\(/) |
|---|
| 121 | for (r in prevchan) { |
|---|
| 122 | # Split individual records from old channel data |
|---|
| 123 | $0=prevchan[r] |
|---|
| 124 | # Strip leading/trailing brackets, replace "\\'" with "'" |
|---|
| 125 | sub(/^\(|\)$/,"") ; gsub(/\\'/,"'") |
|---|
| 126 | if (debug>6) print |
|---|
| 127 | if (debug>4) print "Storing old chan " $5 " with key " $22, oldtr_freq[$21] |
|---|
| 128 | # Store old channels data, indexed with transport frequency and program id |
|---|
| 129 | oldch_channelid[$22,oldtr_freq[$21]]=$1 |
|---|
| 130 | oldch_channum[$22,oldtr_freq[$21]]=dequote($2) |
|---|
| 131 | oldch_sourceid[$22,oldtr_freq[$21]]=$4 |
|---|
| 132 | oldch_callsign[$22,oldtr_freq[$21]]=dequote($5) |
|---|
| 133 | oldch_name[$22,oldtr_freq[$21]]=dequote($6) |
|---|
| 134 | oldch_icon[$22,oldtr_freq[$21]]=dequote($7) |
|---|
| 135 | oldch_videofilters[$22,oldtr_freq[$21]]=dequote($9) |
|---|
| 136 | oldch_xmltvid[$22,oldtr_freq[$21]]=dequote($10) |
|---|
| 137 | oldch_recpriority[$22,oldtr_freq[$21]]=$11 |
|---|
| 138 | oldch_contrast[$22,oldtr_freq[$21]]=$12 |
|---|
| 139 | oldch_brightness[$22,oldtr_freq[$21]]=$13 |
|---|
| 140 | oldch_colour[$22,oldtr_freq[$21]]=$14 |
|---|
| 141 | oldch_hue[$22,oldtr_freq[$21]]=$15 |
|---|
| 142 | oldch_commfree[$22,oldtr_freq[$21]]=$17 |
|---|
| 143 | oldch_visible[$22,oldtr_freq[$21]]=$18 |
|---|
| 144 | oldch_outputfilters[$22,oldtr_freq[$21]]=dequote($19) |
|---|
| 145 | oldch_useonairguide[$22,oldtr_freq[$21]]=$20 |
|---|
| 146 | oldch_tmoffset[$22,oldtr_freq[$21]]=$24 |
|---|
| 147 | oldch_lastrecord[$22,oldtr_freq[$21]]=$25 |
|---|
| 148 | oldch_authority[$22,oldtr_freq[$21]]=$26 |
|---|
| 149 | oldch_commmethod[$22,oldtr_freq[$21]]=$27 |
|---|
| 150 | # "chans" stores assigned channel numbers, preventing duplicates |
|---|
| 151 | # Will specify which program id and frequency got it |
|---|
| 152 | chans[$1]=$22 " " oldtr_freq[$21] |
|---|
| 153 | } |
|---|
| 154 | # Now stop processing and begin reading channels.conf |
|---|
| 155 | FS=":"; OFS="|" |
|---|
| 156 | } |
|---|
| 157 | |
|---|
| 158 | { |
|---|
| 159 | if (debug>6) print |
|---|
| 160 | if ($12 == 0) { |
|---|
| 161 | # Field 12 is audio id; if null, it's not an audio or video channel |
|---|
| 162 | if (debug>0) print " Skipping " $1 " for not's a TV/Radio channel" |
|---|
| 163 | next |
|---|
| 164 | } |
|---|
| 165 | if (mplexid[$2] == 0) { # First occurence of transport (frequency) |
|---|
| 166 | bw = ( $4=="BANDWIDTH_8_MHZ" ? 8 : 7) |
|---|
| 167 | timestamp = strftime("%Y-%m-%d %H:%M:%S") |
|---|
| 168 | nid = oldtr_nid[$2]; |
|---|
| 169 | if (nid==0) nid=defaultnid($2) |
|---|
| 170 | tid = oldtr_tid[$2] |
|---|
| 171 | if (tid==0) tid=defaulttid($2); |
|---|
| 172 | # Append comma to being build insert clause, as a delimiter for new record |
|---|
| 173 | if (transportid++ > 0) sql_insert_mpx = sql_insert_mpx "," |
|---|
| 174 | # Append insert data to work variable |
|---|
| 175 | sql_insert_mpx = sql_insert_mpx "(" transportid ",1," tid "," nid "," $2 ",'a',NULL,'auto',NULL,'auto','" bw "','auto','a','auto',0,'auto','n','auto','dvb',33,'" timestamp "')" |
|---|
| 176 | # Store reference frequency->transportid; will be used by channel definition |
|---|
| 177 | mplexid[$2] = transportid |
|---|
| 178 | printf "Adding transport %d: %d nid/tid (%d/%d)\n", transportid, $2, nid, tid |
|---|
| 179 | } |
|---|
| 180 | if (debug>4) print "Retrieving old channel info for " $1 " with key " $13 " " $2 |
|---|
| 181 | # Append comma to being build insert clause, as a delimiter for new record |
|---|
| 182 | if (channelcount++ > 0) sql_insert_chan = sql_insert_chan "," |
|---|
| 183 | channelid = oldch_channelid[$13,$2] |
|---|
| 184 | # We must avoid that two channels are assigned the same channelid. |
|---|
| 185 | # First we get the old assignment; if it is 0, then it has never |
|---|
| 186 | # been assigned to this channel before. |
|---|
| 187 | # if id is already in "chans" list, and freq/pid do not match, then this |
|---|
| 188 | # it is already taken by another, so we have to search for a new one |
|---|
| 189 | if (channelid==0 || (channelid in chans && chans[channelid] != $13 " " $2)) { |
|---|
| 190 | if (debug>1) print " --- Generating new channelid for " $1 |
|---|
| 191 | if (debug>5) print "(I'm doing this because channelid=" channelid " and chans[" channelid "]=" chans[channelid] |
|---|
| 192 | # First try: add 1000 to program id |
|---|
| 193 | channelid = 1000+$13 |
|---|
| 194 | # If it's already taken, increment until a free number is found |
|---|
| 195 | while (channelid in chans && chans[channelid] != $13 " " $2) { |
|---|
| 196 | if (debug>5) print "Trying with " channelid "... -> chans[" channelid "]=" chans[channelid] |
|---|
| 197 | channelid++ |
|---|
| 198 | } |
|---|
| 199 | } |
|---|
| 200 | # Now store new channelid assignment, to prevent subsequent channels to get it |
|---|
| 201 | chans[channelid]=$13 " " $2 |
|---|
| 202 | # Get old other channel properties. If no one is found, assign sensible defaults |
|---|
| 203 | channum = oldch_channum[$13,$2] ; if (channum==0) channum=channelid |
|---|
| 204 | sourceid = oldch_sourceid[$13,$2] ; if (sourceid==0) sourceid=1 |
|---|
| 205 | callsign = oldch_callsign[$13,$2] ; if (callsign==0) callsign=$1 |
|---|
| 206 | name = oldch_name[$13,$2] ; if (name==0) name=$1 |
|---|
| 207 | icon = oldch_icon[$13,$2] |
|---|
| 208 | videofilters = oldch_videofilters[$13,$2] |
|---|
| 209 | xmltvid = oldch_xmltvid[$13,$2] |
|---|
| 210 | recpriority = oldch_recpriority[$13,$2] ; if (recpriority=="") recpriority=0 |
|---|
| 211 | contrast = oldch_contrast[$13,$2] ; if (contrast=="") contrast=32768 |
|---|
| 212 | brightness = oldch_brightness[$13,$2] ; if (brightness=="") brightness=32768 |
|---|
| 213 | colour = oldch_colour[$13,$2] ; if (colour=="") colour=32768 |
|---|
| 214 | hue = oldch_hue[$13,$2] ; if (hue=="") hue=32768 |
|---|
| 215 | commfree = oldch_commfree[$13,$2] ; if (commfree=="") commfree=0 |
|---|
| 216 | visible = oldch_visible[$13,$2] ; if (visible=="") visible=1 |
|---|
| 217 | outputfilters = oldch_outputfilters[$13,$2] |
|---|
| 218 | useonairguide = oldch_useonairguide[$13,$2] ; if (useonairguide=="") useonairguide=1 |
|---|
| 219 | tmoffset = oldch_tmoffset[$13,$2] ; if (tmoffset=="") tmoffset=0 |
|---|
| 220 | lastrecord = oldch_lastrecord[$13,$2] ; if (lastrecord=="") lastrecord="0000-00-00 00:00:00" |
|---|
| 221 | authority = oldch_authority[$13,$2] |
|---|
| 222 | commmethod = oldch_commmethod[$13,$2] ; if (commmethod=="") commmethod=-1 |
|---|
| 223 | |
|---|
| 224 | printf "%6s %4d - Channel %4s: %s\n", (oldch_callsign[$13,$2]!=""?"":"(new)"), channelid, channum, $1 |
|---|
| 225 | if (debug>5) print "old: " oldch_channelid[$13,$2], oldch_channum[$13,$2], oldch_sourceid[$13,$2], oldch_callsign[$13,$2], oldch_name[$13,$2], oldch_icon[$13,$2], oldch_videofilters[$13,$2], oldch_xmltvid[$13,$2], oldch_recpriority[$13,$2], oldch_contrast[$13,$2], oldch_brightness[$13,$2], oldch_colour[$13,$2], oldch_hue[$13,$2], oldch_commfree[$13,$2], oldch_visible[$13,$2], oldch_outputfilters[$13,$2], oldch_useonairguide[$13,$2], oldch_tmoffset[$13,$2], oldch_lastrecord[$13,$2], oldch_authority[$13,$2], oldch_commmethod[$13,$2] |
|---|
| 226 | if (debug>5) print "new: " channelid, channum, sourceid, callsign, name, icon, videofilters, xmltvid, recpriority, contrast, brightness, colour, hue, commfree, visible, outputfilters, useonairguide, tmoffset, lastrecord, authority, commmethod |
|---|
| 227 | # Add escape character "\" before single quotes; mysql will rant otherwise |
|---|
| 228 | gsub(/'/,"\\'",name) ; gsub(/'/,"\\'",callsign) |
|---|
| 229 | # Finally, append record data to being build insert clause |
|---|
| 230 | sql_insert_chan = sql_insert_chan "(" channelid "," channum ",NULL," sourceid ",'" callsign "','" name "','" icon "',NULL,'" videofilters "','" xmltvid "'," recpriority "," contrast "," brightness "," colour "," hue ",'Default'," commfree "," visible ",'" outputfilters "'," useonairguide "," mplexid[$2] "," $13 ",NULL," tmoffset ",0,0,'" lastrecord "','" authority "'," commmethod ")" |
|---|
| 231 | } |
|---|
| 232 | |
|---|
| 233 | END { |
|---|
| 234 | # Finally, write contructed both sql insert clauses to respective output files |
|---|
| 235 | print sql_insert_chan ";" >> CHAN_SQL_OUT |
|---|
| 236 | print sql_insert_mpx ";" >> MPX_SQL_OUT |
|---|
| 237 | print channelcount " channels and " transportid " transport written" |
|---|
| 238 | # Finalize by appending the rest of original sql export files |
|---|
| 239 | do { |
|---|
| 240 | r=getline <CHAN_SQL_IN |
|---|
| 241 | if (r>0) print >> CHAN_SQL_OUT |
|---|
| 242 | } while (r>0) |
|---|
| 243 | do { |
|---|
| 244 | r=getline <MPX_SQL_IN |
|---|
| 245 | if (r>0) print >> MPX_SQL_OUT |
|---|
| 246 | } while (r>0) |
|---|
| 247 | } |
|---|
| 248 | |
|---|
| 249 | # END OF PROGRAM |
|---|
| 250 | |
|---|
