AtlasGeoDBInterface.py

Go to the documentation of this file.

# Copyright (C) 2002-2024 CERN for the benefit of the ATLAS collaboration
 
from AthenaCommon import Logging
 
import coral
import os
 
# -------------------------------------------------------------------------------------
#  AtlasGeoDBInterface : Interface with the geometry DB
# -------------------------------------------------------------------------------------
 
class AtlasGeoDBInterface:
 
    def __init__(self,dbTag,verbose=False):
 
        self.dbGeoTag=dbTag
        self.dbGeoTagId=""
        self.dbSession=0
        self.dbSchema=0
        self.bVerbose=verbose
 
        self.TagAndNodeVersionDict={}
 
    def __del__(self):
 
        Logging.log.verbose("-> close DB connection")
        del self.dbSession
 
 
    def ConnectToDB(self):
 
        svc = coral.ConnectionService()
        svcconfig = svc.configuration()
        svcconfig.disablePoolAutomaticCleanUp()
        svcconfig.setConnectionTimeOut(0)
 
        # Check that the FRONTIER_SERVER is set properly, if not reduce the retrial period and time out values
        if not ('FRONTIER_SERVER' in os.environ and os.environ['FRONTIER_SERVER']):
            svcconfig.setConnectionRetrialPeriod(1)
            svcconfig.setConnectionRetrialTimeOut(1)
        else:
            svcconfig.setConnectionRetrialPeriod(300)
            svcconfig.setConnectionRetrialTimeOut(3600)
        
        self.dbSession = svc.connect( 'ATLASDD', accessMode = coral.access_ReadOnly )
        transaction = self.dbSession.transaction()
 
        self.dbSchema = self.dbSession.nominalSchema()
        transaction.start(True)
 
    def ConnectAndBrowseGeoDB(self):
 
        self.ConnectToDB()
        self.ReadGeometryTagId()
        self.ReadTagAndNodeVersionsFromDB()
 
 
    def ReadGeometryTagId(self):
        """ Read the geometry Id corresponding to the geometry tag name in order to collect the child node Id's """
        
        query = self.dbSchema.tableHandle("HVS_TAG2NODE").newQuery()
 
        query.addToOutputList('TAG_NAME')
        query.addToOutputList('TAG_ID')
 
        bindstag2node = coral.AttributeList() 
        bindstag2node.extend('tagN','string') 
        bindstag2node[0].setData(self.dbGeoTag)
        condString='TAG_NAME=:tagN'
        
        query.setCondition(condString,bindstag2node)
 
        for currentRow in iter(query.execute()):
            for i in range(0,currentRow.size()):
                if currentRow[i].specification().name()=="TAG_ID":
                    Logging.log.verbose("*** GeoTagId ******** "+str(currentRow))
                    self.dbGeoTagId=currentRow[i].data()
                    continue
 
        del query
        return
 
 
    def AnalyseDBRequestOutput_Query(self,request,keyName=""):
        """ Analyze request output -> return a list of dictionaries and a list of parameter types """
        
        dataList=[]
        typeList=[]
 
        # Loop over row
        iCmpt=0
        for row in iter(request.execute()):
            dictLoc={}
            # loop over parameters read fron the DB
            for j in range(0,row.size()):
                name=row[j].specification().name()
                paramType=row[j].specification().typeName()
                value=row[j].data()
                dictLoc[name]=value
                # collect parameter name/types
                if iCmpt==0: typeList.append((name,paramType))
            iCmpt+=1
            dataList.append(dictLoc)
 
        # return datalist as a list of dictionaries ( 1 dictionary per line)
        if keyName=="":
            return dataList,typeList
 
        # reorganize dataList as a dictionary with keys based on the keyName input parameter
        dataDict={}
        for d in dataList:
            k=d[keyName]
            v=[d[paramName] for paramName,tmp in typeList if paramName!=keyName]
            dataDict[k]=v
 
        return dataDict,typeList
 
 
    def ReadTagAndNodeVersionsFromDB(self):
        """ Read tag and node versions defined for the geometry tag """
 
        # Proceed by looping over the chilg Id's as long as childs are defined 
        
        bStopLoop=False
        tagDictGbl={}
 
        # Check if the geometry tag was found in the DB
        if self.dbGeoTagId=="":
            Logging.log.error("The geometry tag %s could not be found in the database.", self.dbGeoTag)
            Logging.log.error("Its name might be misspelled and/or the script might access a local DB that is not up to date.")
            import sys
            sys.exit()
 
        # Get node ids for the geometry tag
        tagIdList=[int(self.dbGeoTagId)]    # start with the geometry tag Id
 
        # Loop while child tags are found
        while not bStopLoop :
 
            query0 = self.dbSchema.newQuery()
 
            query0.addToOutputList('C.NODE_NAME',"nodename")
            query0.addToOutputList('A.TAG_NAME',"tagname")
            query0.addToOutputList('A.TAG_ID',"tagid")
 
            query0.addToTableList( "HVS_TAG2NODE","A" )
            query0.addToTableList( "HVS_LTAG2LTAG","B")
            query0.addToTableList( "HVS_NODE","C" )
 
            bindstag2node = coral.AttributeList()
            condString="C.NODE_ID=A.NODE_ID AND A.TAG_ID=B.CHILD_TAG and B.PARENT_TAG IN (%s)" % str(tagIdList)[1:-1]
            Logging.log.verbose("----------------------------\n"+condString)
            query0.setCondition(condString,bindstag2node)
 
            # Analyze the output and collect the new tag and node versions
            dictRes,paramType=self.AnalyseDBRequestOutput_Query(query0,"nodename")
            tagDictGbl.update(dictRes)
            del query0
            
            if len(dictRes.keys())==0:
                # No child found
                bStopLoop=True
            else:
                # create child Id's list for the next loop
                tagIdList=[int(dictRes[k][1]) for k in dictRes.keys()]
 
 
        self.TagAndNodeVersionDict.update(tagDictGbl)
 
    
    def GetCurrentLeafContent(self, leafName):
        """ Read a DB table content and return 3 lists :
                     - dbId : the data_id list corresponding to each entry in the table
                     - dbContent : the table values as a dictionary with keys corresponding to the dbId list
                     - paramName : the name of the table parameters """
        
        upLeafName=leafName.upper()
 
        # check if table is defined in the current geometry
        if leafName not in self.TagAndNodeVersionDict:
            dbId=[]
            dbContent={}
            paramName=[]
            return dbId,dbContent,paramName
 
 
        # ------ leafName_DATA_ID entries        
        leafTagId=self.TagAndNodeVersionDict[leafName][1]
 
        query = self.dbSchema.tableHandle(upLeafName+"_DATA2TAG").newQuery()
        query.addToOutputList(upLeafName+'_DATA_ID')
        
        bindstag2node = coral.AttributeList() 
        condString=upLeafName+'_TAG_ID='+str(leafTagId)
        query.setCondition(condString,bindstag2node)
 
        dictRes,typeList=self.AnalyseDBRequestOutput_Query(query)
        dataIdList=[int(s[upLeafName+'_DATA_ID']) for s in dictRes]
        del query
        
        # ------- values corresponding to leafName_DATA_ entries
 
        query = self.dbSchema.tableHandle(upLeafName+"_DATA").newQuery()
 
        condString=upLeafName+'_DATA_ID IN ('+str(dataIdList)[1:-1]+") ORDER BY "+upLeafName+"_DATA_ID" 
        query.setCondition(condString,bindstag2node)
 
        dictRes,typeList=self.AnalyseDBRequestOutput_Query(query)
        del query
 
        # ------- build output
        #   paramNames : name of the DB parameters (following the table columns order)
        #   dbId       : data_id vector
        #   dbContent  : table content (key = dbId, value=list of value fooliwin the paramName ranking scheme)
 
        paramName=[s[0] for s in typeList]
 
        paramKey=typeList[0][0]
        dbId=[]
        dbContent={}
        for s in dictRes:
            key=int(s[paramKey])
            dbId.append(key)
            v=[s[k[0]] for k in typeList]
            dbContent[key]=v
 
        return dbId,dbContent,paramName
 
# -------------------------------------------------------------------------------------
#  AtlasGeoDBInterface_SQLite : Interface with the SQLite geometry DB
# -------------------------------------------------------------------------------------
 
import sqlite3
 
class AtlasGeoDBInterface_SQLite:
 
    def __init__(self,geoTag,sqliteDBFullPath,verbose=False):
 
        self.db=None
        self.bVerbose=verbose
 
        if sqliteDBFullPath:
            self.dbFile=sqliteDBFullPath
        else:
            from AthenaCommon.Utils.unixtools import find_datafile
            pathlist = os.getenv('CALIBPATH').split(os.pathsep)
            self.dbFile=find_datafile("Geometry/"+geoTag+".db",pathlist)
 
    def ConnectToDB(self):
 
        try:
            self.db=sqlite3.connect(self.dbFile)
        except Exception as e:
            Logging.log.fatal(f'Failed to open SQLite database {self.dbFile}. {e}')
        Logging.log.debug(f'Connected to SQLite database {self.dbFile}')
 
    def GetData(self,tableName):
 
        # Check the existence of the table in the DB
        cur = self.db.cursor()
        querystring = "SELECT tbl_name FROM sqlite_master WHERE type='table' AND tbl_name='"+tableName+"'"
        cur.execute(querystring)
        checkTable = cur.fetchall()
        if len(checkTable)==0:
            Logging.log.info(f'Table {tableName} not found in the SQLite DB. Falling back on the default config')
            return []
 
        # Fetch the data
        cur = self.db.cursor()
        querystring = "SELECT * FROM "+tableName+" order by "+tableName+"_data_id"
        cur.execute(querystring)
        rows = cur.fetchall()
 
        ncols=len(cur.description)
        dbData=[]
        for row in rows:
            dbDataRow={}
            for i in range(1,ncols):
                dbDataRow[cur.description[i][0]]=row[i]
            Logging.log.debug(f'Fetched Data Row for {tableName}')
            Logging.log.debug(f'{dbDataRow}')
            dbData.append(dbDataRow)
 
        return dbData