#!/usr/bin/env python # # 1/10/2019 DL # # This script attempts to fix the Channel.name values in the # tt.db file. Many entries have names generated by the script # that mined the information from the XML in the CCDB. The XML # does not contain the channel names. # # The Channel.name values are used by the EPICS system for scalers. # # This script will modify the file "tt.db" to fix the names and # will then run a check to make sure they appear to be correct. # At this point there are some complications with the RF and TPOL # naming. It looks like some channels may have been reassigned # at some point. I'm punting on getting that completely sorted at # the moment since it seems a bit convoluted. # import os import sys import xml.etree.ElementTree as ET import sqlite3 as lite # Create cursor for tt.db con = lite.connect('tt.db') # Open DB con.row_factory = lite.Row # Specify that next cursor should be "dictionary" cur = con.cursor() # Create Cursor #--------------------------- # UpdateNames # # This is called to make the update. # where_clause: used to select channels to update # name_func: lambda function to generate channel name from channel info #--------------------------- def UpdateNames(system, where_clause, name_func): print '%s: Selecting channels for update ...' % system sql = 'SELECT * FROM Channel,%s WHERE %s' % (system, where_clause) cur.execute(sql) rows = cur.fetchall() Ntot = len(rows) N = 0 for row in rows: new_name = name_func(row) if new_name=='SPARE': sql = 'UPDATE Channel SET name="'+new_name+'",system="" WHERE chanid="'+str(row['chanid'])+'"' else: sql = 'UPDATE Channel SET name="'+new_name+'" WHERE chanid="'+str(row['chanid'])+'" AND system="'+row['system']+'"' cur.execute(sql) N += 1 sys.stdout.write(' %s : %3.1f%%\r' % (system, 100.0*N/Ntot)) sys.stdout.flush() #print sql print '' #-------------------------------------- # GetColNames #-------------------------------------- def GetColNames(tablename): sql = 'SELECT sql FROM sqlite_master WHERE name="%s"' % tablename cur.execute(sql) tabledef = cur.fetchone()[0].split('(')[1].split(')')[0] col_names = [] for col_def in tabledef.split(','): col_name = col_def.split()[0] col_names.append( col_name ) return col_names #-------------------------------------- # GetNBadNames #-------------------------------------- def GetNBadNames(system, col_name): # Name matching pattern for "good" names pat = 'name LIKE "%s%%"' % system # Default if system.startswith('FDC'): pat = 'name LIKE "FDC%"' # Handle FDC_Wires and FDC_Cathodes if system=='TAGM': pat = 'name LIKE "TAGM%" OR name="SPARE"' # Handle "SPARE" channels in TAGM if system=='TAC': pat = 'name LIKE "TAC%" OR name="FCAL-unused"' # Handle "FCAL-unused" channels in TAC sql = 'SELECT count(*) FROM Channel,%s WHERE chanid=%s AND NOT (%s)' % (system,col_name,pat) cur.execute(sql) Nbad = cur.fetchone()[0] sql = 'SELECT count(*) FROM Channel,%s WHERE chanid=%s AND %s' % (system,col_name,pat) cur.execute(sql) Ngood = cur.fetchone()[0] return (Nbad,Ngood) #------- CDC # The CDC naming convention is based on a pin assignment map that cannot be # directly calculated from the ring/straw numbers. We therefore try to recycle # names by selecting only those that start with "CDC" via the where_claus. cdc_name_func = lambda row : row['name'] UpdateNames('CDC', 'adc_chanid=chanid AND name LIKE "CDC%"', cdc_name_func) #------- BCAL # Don't know why disc channels were named "T" while tdc channels were named "E" (??) bcal_name_func = lambda row : 'BCAL-' + str(row['module']) + '-' + row['end'] + '-A-' + str(row['sector']-1+4*(row['layer']-1)) UpdateNames('BCAL', 'adc_chanid=chanid AND name NOT LIKE "BCAL%"', bcal_name_func) bcal_name_func = lambda row : 'BCAL-' + str(row['module']) + '-' + row['end'] + '-E-' + str(row['sector']-1+4*(row['layer']-1)) UpdateNames('BCAL', 'tdc_chanid=chanid AND name NOT LIKE "BCAL%"', bcal_name_func) bcal_name_func = lambda row : 'BCAL-' + str(row['module']) + '-' + row['end'] + '-T-' + str(row['sector']-1+4*(row['layer']-1)) UpdateNames('BCAL', 'disc_chanid=chanid AND name NOT LIKE "BCAL%"', bcal_name_func) #------- CCAL ccal_name_func = lambda row : 'CCAL:' + str(row['row']) + ':' + str(row['col']) UpdateNames('CCAL', 'adc_chanid=chanid', ccal_name_func) #------- DIRC dirc_name_func = lambda row : 'DIRC:' + str(row['pixel']) UpdateNames('DIRC', 'ssp_chanid=chanid', dirc_name_func) #------- FCAL fcal_name_func = lambda row : 'FCAL:' + str(row['col']-29) + ':' + str(row['row']-29) UpdateNames('FCAL', 'adc_chanid=chanid AND name NOT LIKE "FCAL%"', fcal_name_func) #------- TOF # disc and tdc channels seem to have been given same name (??) tof_name_func = lambda row : 'TOF-' + row['end'] + '-A-' + str(row['bar']) UpdateNames('TOF', 'adc_chanid=chanid', tof_name_func) tof_name_func = lambda row : 'TOF-' + row['end'] + '-T-' + str(row['bar']) UpdateNames('TOF', 'tdc_chanid=chanid', tof_name_func) UpdateNames('TOF', 'disc_chanid=chanid', tof_name_func) #------- ST # disc and tdc channels seem to have been given same name (??) st_name_func = lambda row : 'ST-A-' + str(row['sector']) UpdateNames('ST', 'adc_chanid=chanid', st_name_func) st_name_func = lambda row : 'ST-T-' + str(row['sector']) UpdateNames('ST', 'tdc_chanid=chanid', st_name_func) UpdateNames('ST', 'disc_chanid=chanid', st_name_func) #------- PSC # disc and tdc channels seem to have been given same name (??) psc_name_func = lambda row : 'PSC-A-' + str(row['id']) UpdateNames('PSC', 'adc_chanid=chanid', psc_name_func) psc_name_func = lambda row : 'PSC-T-' + str(row['id']) UpdateNames('PSC', 'tdc_chanid=chanid', psc_name_func) UpdateNames('PSC', 'disc_chanid=chanid', psc_name_func) #------- PS ps_name_func = lambda row : 'PS-' + row['side'] + '-' + str(row['id']) UpdateNames('PS', 'adc_chanid=chanid', ps_name_func) #------- FDC_Wires # Not immediately obvious how to easily derive name from indexes so we # use the one existing entry to set the others. Does not appear to be # any entries for discriminator channels fdc_name_func = lambda row : row['name'] UpdateNames('FDC_Wires', 'tdc_chanid=chanid AND name LIKE "FDC%"', fdc_name_func) #------- FDC_Cathodes # Ditto comment for FDC_Wires fdc_name_func = lambda row : row['name'] UpdateNames('FDC_Cathodes', 'adc_chanid=chanid AND name LIKE "FDC%"', fdc_name_func) #------- TAGM # TAGM has funny naming where certain columns have multiple rows so the # format of those channels changes. Again, we'll use the original name # to set the others. We also make a special pass to copy the channels # named "SPARE". tagm_name_func = lambda row : row['name'] UpdateNames('TAGM', 'adc_chanid=chanid AND name LIKE "TAGM%"', tagm_name_func) UpdateNames('TAGM', 'tdc_chanid=chanid AND name LIKE "TAGM%"', tagm_name_func) UpdateNames('TAGM', 'disc_chanid=chanid AND name LIKE "TAGM%"', tagm_name_func) tagm_name_func = lambda row : 'SPARE' UpdateNames('TAGM', 'tdc_chanid=chanid AND name="SPARE"', tagm_name_func) UpdateNames('TAGM', 'disc_chanid=chanid AND name="SPARE"', tagm_name_func) #------- TAGH # The TAGH uses the id up to 132 and then diverges since the id skips # counters that would be in the microscope region, but the name maintains # sequential numbering. tagh_name_func = lambda row : row['name'] UpdateNames('TAGH', 'adc_chanid=chanid AND name LIKE "TAGH%"', tagh_name_func) UpdateNames('TAGH', 'tdc_chanid=chanid AND name LIKE "TAGH%"', tagh_name_func) UpdateNames('TAGH', 'disc_chanid=chanid AND name LIKE "TAGH%"', tagh_name_func) #------- RF rf_name_func = lambda row : row['name'] UpdateNames('RF', 'adc_chanid=chanid AND name LIKE "RF%"', rf_name_func) UpdateNames('RF', 'tdc_chanid=chanid AND name LIKE "RF%"', rf_name_func) #------- TPOL tpol_name_func = lambda row : row['name'] UpdateNames('TPOL', 'adc_chanid=chanid AND name LIKE "TPOL%"', tpol_name_func) #------- HALO halo_name_func = lambda row : row['name'] UpdateNames('HALO', 'disc_chanid=chanid AND name LIKE "HALO%"', halo_name_func) #------- Active_Target Active_Target_name_func = lambda row : row['name'] UpdateNames('Active_Target', 'disc_chanid=chanid AND name LIKE "Active_Target%"', Active_Target_name_func) #------- TAC # Some TAC channels were assigned "FCAL-unused" in the original. Not sure why. # For consistency we keep that name for other assignments of the same channel. # Other channels have system="TAC" but have a chanid that has no entry with a # TAC like name. This is only for the adc_chanid. We correct this by hand below. tac_name_func = lambda row : row['name'] UpdateNames('TAC', 'adc_chanid=chanid AND name LIKE "TAC%"', tac_name_func) UpdateNames('TAC', 'tdc_chanid=chanid AND name LIKE "TAC%"', tac_name_func) UpdateNames('TAC', 'disc_chanid=chanid AND name LIKE "TAC%"', tac_name_func) tac_name_func = lambda row : 'FCAL-unused' UpdateNames('TAC', 'adc_chanid=chanid AND name="FCAL-unused"', tac_name_func) cur.execute('UPDATE Channel SET name="TAC-A" WHERE col_name="adc_chanid" AND system="TAC"') # Commit all changes con.commit() # Check if all names seem OK # Get list of tables that have a column with "_chanid" sql = 'SELECT name from sqlite_master WHERE sql LIKE "%_chanid%"' cur.execute(sql) for row in cur.fetchall(): system = row['name'] cols = GetColNames( system ) print '--- %s ---' % system for col_name in ['adc_chanid','tdc_chanid','disc_chanid']: if col_name not in cols: continue (Nbad,Ngood) = GetNBadNames(system, col_name) print ' %12s -- Bad: %4d Good: %4d' % (col_name, Nbad, Ngood)