Ticket #4138: tzap2myth.awk

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