#!/usr/bin/env python import os import sys import xml.etree.ElementTree as ET import sqlite3 as lite # This will fill in a tt.db file's run range information based on existing # CCDB entries. # # This script will read each entry from the Translation/DAQ2detector table # of the CCDB. It will parse the XML and fill in the tt.db with new entries # for the run range. This will result in many duplicate entries that are # the same for different run ranges. That may be cleaned up later with a # separate script. # Get python module for interfacing with tt.db import halld_tt as tt tt.Initialize() tt.UpdateSchema() # Get ccdb API ccdb_home = os.environ["CCDB_HOME"] # Should be set! sys.path.append(os.path.join(ccdb_home, "python")) import ccdb from ccdb import Directory, TypeTable, Assignment, ConstantSet # Connect to CCDB DB sqlite_connect_str = os.environ["JANA_CALIB_URL"] provider = ccdb.AlchemyProvider() # this class has all CCDB manipulation functions provider.connect(sqlite_connect_str) # use usual connection string to connect to database provider.authentication.current_user_name = "anonymous" # to have a name in logs # 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 # Get list of users by id so we can print names later users = {} ccdb_users = provider.get_users() for user in ccdb_users: users[user.id] = user.name # Get the DAQ2Detector table table = provider.get_type_table("/Translation/DAQ2detector") # It turns out that doing this for every CCDB entry takes a VERY long # time since as the DB grows, the operations take longer. (I stopped # it after about 2 hours when it was less than half done and getting # progressively slower!) So, we check the run range for each entry and # do not process older entries if they would be obscured by newer ones # anyway. table.constant_sets.reverse() # make most recent CCDB entries first print str(len(table.constant_sets)) + ' Constant sets found in CCDB' entries_to_process = {} for s in table.constant_sets: if not s.assignment : continue # due to bad entry that Mark I. had to remove reference to minRun = s.assignment.run_range.min maxRun = s.assignment.run_range.max ignore = False for (key,v) in entries_to_process.iteritems(): if v[0]<=minRun and v[1]>=maxRun: ignore = True break if ignore : continue entries_to_process[s.assignment.request] = (minRun, maxRun) print 'Will process ' + str(len(entries_to_process)) + ' CCDB entries' # The rocST crate had 3 f125 modules added that do not appear to be in # the tt.db at all. Try adding them now. #try: # # Get crateid for rocid=94 # cur.execute('SELECT crateid FROM crate WHERE rocid=94') # crateid = cur.fetchone()['crateid'] # cur.execute('INSERT INTO module (crateid,slot,type,SN) VALUES(%d,13,"fADC125","N/A")' % crateid) # cur.execute('INSERT INTO module (crateid,slot,type,SN) VALUES(%d,14,"fADC125","N/A")' % crateid) # cur.execute('INSERT INTO module (crateid,slot,type,SN) VALUES(%d,15,"fADC125","N/A")' % crateid) # con.commit() # print 'Added fADC125 modules into slots 13,14,15 of rocST crate' #except: # pass # The CCAL tables were added later and may not exist in input tt.db file try: tt.CreateSystem('CCAL', 'row INT, col INT, adc_chanid INTEGER') tt.CreateSystem('CCAL_REF', 'id INT, adc_chanid INTEGER') tt.AddCrate(rocid=90,area='D2', rack='4', location='BOT') con.commit() # print 'Added CCAL table and crate' except: print 'ERROR: problem adding CCAL system or crate' sys.exit(-1) # Restore original order of CCDB table so oldest entries are first. # This is so the largest runrangeid values correspond to the most recent # CCDB entries. table.constant_sets.reverse() # Loop over all constant sets for s in table.constant_sets: assert (isinstance(s, ConstantSet)) if not s.assignment : continue # due to bad entry that Mark I. had to remove reference to if s.assignment.request not in entries_to_process: print ' -- skipping CCDB entry ' + s.assignment.request continue # print s.vault # sys.exit(-1) minRun = s.assignment.run_range.min maxRun = s.assignment.run_range.max name = 'CCDB_' + str(minRun) + '_' + str(maxRun) # Create ElementTree from XML try: root = ET.fromstring(s.vault) except: print 'problem parsing ' + s.assignment.request continue # Create RunRange for this entry. Make it a unique name runrangeid = tt.GetRunRangeID(name) cnt = 0 while runrangeid != -1: cnt = cnt + 1 name = 'CCDB_' + str(minRun) + '_' + str(maxRun) + '_' + str(cnt) runrangeid = tt.GetRunRangeID(name) print 'Creating run range "' + name + '" for runs ' + str(minRun) + ' - ' + str(maxRun) runrangeid = tt.CreateRunRange(minRun, maxRun, name) # Save some time in querying the DB by storing results and reusing them query_cache = {} # Loop over crates (xml) for crate_elem in root.iter('crate'): crate = int(crate_elem.attrib['number']) print ' ' + name + ' : processing rocid ' + str(crate) # Loop over slots (xml) for slot_elem in crate_elem.iter('slot'): slot = int(slot_elem.attrib['number']) # Get channel info from tt.db for all channels in this CS # (Doing this and storing the results indexed by channel speeds things # up considerably compared to querying every channel independently) sql = 'SELECT * FROM crate,module,channel where rocid=%d AND slot=%d AND channel.moduleid=module.moduleid AND module.crateid=crate.crateid ORDER BY runrangeid' % (crate,slot) cur.execute(sql) ttdb_chans = {} for t in cur.fetchall(): # n.b. the query will return entries for multiple run rangeids, but since they # are in ascending order, only the most recent will be left in ttdb_chans ttdb_chans[ int(t['channel']) ] = t # Loop over channels (xml) for chan_elem in slot_elem.iter('channel'): chan = int(chan_elem.attrib['number']) detector = chan_elem.attrib['detector'] #print detector + ': ' + str(crate) + ', ' + str(slot) + ', ' + str(chan) # Set some defaults in this scope so if SELECT query below fails, we can # recover by deriving the values in a different way chanid = -1 moduleid = -1 chan_name = '' channel = -1 system = '' col_name = '' # Get channel info from tt.db for this CSC # sql = 'SELECT * FROM crate,module,channel where rocid=%d AND slot=%d AND channel=%d AND channel.moduleid=module.moduleid AND module.crateid=crate.crateid ORDER BY runrangeid DESC' % (crate,slot,chan) # #print sql # if sql in query_cache: # ttdb = query_cache[sql] # else: # cur.execute(sql) # ttdb = cur.fetchone() # #if ttdb: query_cache[sql] = ttdb ttdb = None if chan in ttdb_chans.keys(): ttdb = ttdb_chans[chan] if ttdb : chanid = ttdb['chanid' ] moduleid = ttdb['moduleid'] chan_name = ttdb['name' ] channel = ttdb['channel' ] system = ttdb['system' ] col_name = ttdb['col_name'] enable = ttdb['enable' ] # It turns out col_name is empty sometimes (??) if len(col_name)<4 : type = ttdb['type'] if type in ['FADC250', 'fADC125', 'fADC250'] : col_name = 'adc_chanid' if type in ['F1TDCV2', 'F1TDCV3', 'VX1290A'] : col_name = 'tdc_chanid' if type in ['DISC'] : col_name = 'disc_chanid' else: # Above query failed. Need to derive values some other way for insert below print 'INFO: No entry in Channel table for rocid=%d slot=%d channel=%d' % (crate,slot,chan) print ttdb_chans.keys() # First, get largest chanid currently in DB and set chanid to this +1 sql = 'SELECT chanid FROM Channel ORDER BY chanid DESC' cur.execute(sql) chanid = 1 + cur.fetchone()['chanid'] # Next, get moduleid and module type for this crate,slot sql = 'SELECT moduleid,type FROM crate,module where rocid=%d AND slot=%d AND module.crateid=crate.crateid' % (crate,slot) cur.execute(sql) res = cur.fetchone() if res == None: # Query failed. Possibly due to missing module or crate. Try creating it tt.AddModule(crate, slot, slot_elem.attrib['type']) cur.execute(sql) res = cur.fetchone() if res == None: print 'ERROR: Unable to moduleid and type for the crate,slot using:' print sql sys.exit(-1) moduleid = res['moduleid'] type = res['type'] # Form channel name. Originally, this came from Fernando's spreadsheet. Here, we have to make something up chan_name = detector + '_' + str(slot) + '_' + str(chan) # Set channel and system based on XML channel = chan system = detector # col_name should be 'adc_chanid' or 'tdc_chanid' depending on module type if type in ['FADC250', 'fADC125', 'fADC250'] : col_name = 'adc_chanid' if type in ['F1TDCV2', 'F1TDCV3', 'VX1290A'] : col_name = 'tdc_chanid' if type in ['DISC'] : col_name = 'disc_chanid' # Verify that system from ttdb matches XML and notify user if it doesn't if detector != system: print 'INFO: System mismatch: ' + detector + ' != ' + system + ' for ' + s.assignment.request # Make new entry into Channel table for this runrangeid try: sql = 'INSERT INTO Channel (chanid,moduleid,name,channel,system,col_name,enable,runrangeid) VALUES(%d,%d,"%s",%d,"%s","%s",%d,%d)' %(chanid,moduleid,chan_name,channel,detector,col_name,enable,runrangeid) cur.execute(sql) except lite.Error as e: print "ERROR:", e.args[0] print sql sys.exit(-1) # Make entry in detector table. # Attributes other than "number" and "detector" should all be detector indices # What we really need to do here is an UPSRT since ther may be both adc_chanid # and tdc_chanid entries. SQLite does not support that though so we use the trick # of trying to UPDATE and doing an INSERT only if that fails. keys = '' vals = '' keyvals = '' for key,val in chan_elem.attrib.iteritems(): if key=='detector' or key=='number': continue if isinstance(val, basestring) : val = '"' + val + '"' keys += key + ',' vals += val + ',' keyvals += key + '=' + val + ' AND ' keys += col_name + ',runrangeid' vals += str(chanid) + ',' + str(runrangeid) keyvals += 'runrangeid=' + str(runrangeid) try: sql = 'UPDATE ' + detector + ' SET ' + col_name + '=' + str(chanid) + ' WHERE ' + keyvals cur.execute(sql) except lite.Error as e: print "ERROR:", e.args[0] print sql sys.exit(-1) if cur.rowcount==0 : try: sql = 'INSERT INTO ' + detector + ' (' + keys + ') VALUES(' + vals + ')' cur.execute(sql) except lite.Error as e: print "ERROR:", e.args[0] print sql sys.exit(-1) # Commit changes to DB con.commit() # Get entry from appropriate detector table in tt.db # sql = 'SELECT * FROM ' + detector + ' WHERE ' + col_name + '=' + str(chanid) # cur.execute(sql) # det = cur.fetchone() # only work on first entry for now #break # # print '-----------------------------------------------------' # print ' variation: ' + constant_set.assignment.variation.name # print ' run range: ' + str(constant_set.assignment.run_range.min) + ' - ' + str(constant_set.assignment.run_range.max) # print ' info: ' + constant_set.assignment.request # print 'requester id: ' + str(constant_set.assignment.author_id) + ' (' + users[constant_set.assignment.author_id] + ')' # print ' '