def retrieve(client_id,client_tag='0',service_call=None): global db_config logging.debug('retrieving logged service call '+str(client_id)+','+str(client_tag)+','+str(service_call)) try: if service_call==None: sqlStr=Dialect.buildQuery("ProdCom.Query2",{'client_id':client_id,\ 'client_tag':client_tag}) Session.execute(sqlStr) rows=Session.fetchall() if len(rows)!=1: raise ProdException("No entries found for client ID: "+str(client_id)+" and tag "+str(client_tag),1002) service_results=cPickle.loads(base64.decodestring(rows[0][0])) service_parameters=cPickle.loads(base64.decodestring(rows[0][2])) return [str(rows[0][1]),service_parameters,service_results] sqlStr=Dialect.buildQuery("ProdCom.Query3",{'client_id':client_id,\ 'client_tag':client_tag,'service_call':service_call}) Session.execute(sqlStr) rows=Session.fetchall() if len(rows)!=1: raise ProdException("No entries found for client ID: "+str(client_id)+" and tag "+str(client_tag),1002) service_results=cPickle.loads(base64.decodestring(rows[0][0])) service_parameters=cPickle.loads(base64.decodestring(rows[0][1])) return [service_results,service_parameters] except Exception,ex: raise ProdException(exceptions[4001]+str(ex),4001)
def retrieve(client_id, client_tag='0', service_call=None): global db_config logging.debug('retrieving logged service call ' + str(client_id) + ',' + str(client_tag) + ',' + str(service_call)) try: if service_call == None: sqlStr=Dialect.buildQuery("ProdCom.Query2",{'client_id':client_id,\ 'client_tag':client_tag}) Session.execute(sqlStr) rows = Session.fetchall() if len(rows) != 1: raise ProdException( "No entries found for client ID: " + str(client_id) + " and tag " + str(client_tag), 1002) service_results = cPickle.loads(base64.decodestring(rows[0][0])) service_parameters = cPickle.loads(base64.decodestring(rows[0][2])) return [str(rows[0][1]), service_parameters, service_results] sqlStr=Dialect.buildQuery("ProdCom.Query3",{'client_id':client_id,\ 'client_tag':client_tag,'service_call':service_call}) Session.execute(sqlStr) rows = Session.fetchall() if len(rows) != 1: raise ProdException( "No entries found for client ID: " + str(client_id) + " and tag " + str(client_tag), 1002) service_results = cPickle.loads(base64.decodestring(rows[0][0])) service_parameters = cPickle.loads(base64.decodestring(rows[0][1])) return [service_results, service_parameters] except Exception, ex: raise ProdException(exceptions[4001] + str(ex), 4001)
def migrateWorkflows(): """ Needs at least one successful job for each workflow """ #get workflows from job tables Session.execute("SELECT DISTINCT workflow_spec_id FROM st_job_success", sessionID=db_id) workflows_success = [removeTuple(workflow) for workflow in Session.fetchall(sessionID=db_id)] # Session.execute("SELECT DISTINCT workflow_spec_id FROM st_job_failure WHERE workflow_spec_id != \"None\"") # workflows_failure = [removeTuple(workflow) for workflow in Session.fetchall()] # # #get workflows for all failed jobs # # first part of job_spec_id is workflow, strip job number (9 characters long) # Session.execute("select DISTINCT REPLACE(job_spec_id, RIGHT(job_spec_id, 9), \"\") from st_job_failure") # workflows_failure2 = [removeTuple(workflow) for workflow in Session.fetchall()] #add workflows with successful jobs for workflow in workflows_success: #get output dataset Session.execute("""SELECT DISTINCT attr_value FROM st_job_attr JOIN st_job_success WHERE st_job_success.job_index = st_job_attr.job_index AND st_job_attr.attr_class = "output_datasets" AND st_job_success.workflow_spec_id = %s""" % addQuotes(workflow), sessionID=db_id) output_datasets = Session.fetchall(sessionID=db_id) #change to DBS2 dataset format for dataset in output_datasets: dataset = removeTuple(dataset).tostring() #array for some reason dataset = re.sub('(.*)/(.*)/(.*CMSSW.*)', '\g<1>/\g<3>/\g<2>' , dataset) #insert workflow with fake request_id of 0, #no input datasets and an unknown CMSSW version insertNewWorkflow(workflow, 0, (), output_datasets, "Unknown") #for failed don't worry about output_datasets, as long as one job succeeded have info #if not - well... # #ignore workflow "None" - when job not linked back to workflow in db # for workflow in workflows_failure: # if workflow not in (workflows_success): # insertNewWorkflow(workflow, 0, (), (), "Unknown") # # for workflow in workflows_failure2: # if workflow not in (workflows_success, workflows_failure): # insertNewWorkflow(workflow, 0, (), (), "Unknown") return
def queryJobsByStatus(status): """ _queryJobsByStatus_ Returns a list of jobs in the Job Emulator database that have a particular status. Each list item consists of the following tuple: (Job ID, Job Type (processing, merge or cleanup), Job Start Time, Job Status (new, finished, failed)) """ sqlStr = \ """ SELECT * FROM job_emulator WHERE status = '%s' order by start_time """ % status # return values are # job_id, job_type, start_time, status, worker_node_id # (x[0], x[1], x[2], x[3], x[4]) Session.execute(sqlStr) result = Session.fetchall() result = [ (x[0], x[1], x[2], x[3], x[4]) for x in result ] return result
def get(workflowID=[]): """ __getWorkflows__ returns workflow entries """ if(type(workflowID)!=list): workflowID=[str(workflowID)] if len(workflowID)==0: return [] if len(workflowID)==1: sqlStr="""SELECT events_processed,id,owner,priority,prod_mgr_url, workflow_spec_file,workflow_type,max_sites FROM we_Workflow WHERE id="%s" """ %(str(workflowID[0])) else: sqlStr="""SELECT events_processed,id,owner,priority,prod_mgr_url, workflow_spec_file,workflow_type,max_sites FROM we_Workflow WHERE id IN %s """ %(str(tuple(workflowID))) Session.execute(sqlStr) description=['events_processed','id','owner','priority','prod_mgr_url',\ 'workflow_spec_file','workflow_type', 'max_sites'] result=Session.convert(description,Session.fetchall()) if len(result)==1: return result[0] return result
def workmapWFName2ID(workmap): """ do the name possible ID translation for a dictionary {WFname:cmssw_version} use merge_dataset WFs that have no merge_dataset entry, use full name --> should not be processing """ sqlStr='select workflow,id from merge_dataset;' Session.execute(sqlStr) rows=Session.fetchall() w2i={} for i in rows: w2i[str(i[0])]=str(i[1]) logging.debug("workmapWFName2ID "+str(w2i)) workmap2={} for wf,cmssw in workmap.items(): if w2i.has_key(wf): workmap2[w2i[wf]]=cmssw else: workmap2[wf]=cmssw return workmap2
def constraintID2WFname(constr): """ do the (possible) ID 2 WFname translation for a workflowconstraint (ie a commaseparated list of WFs) use merge_dataset WFs that have no merge_dataset entry, use full name --> but these should not be processing !!! """ ## empty constr = None if not constr: return None sqlStr='select id,workflow from merge_dataset;' Session.execute(sqlStr) rows=Session.fetchall() i2w={} for i in rows: i2w[str(i[0])]=str(i[1]) listt=[] for wf in constr.split(','): if i2w.has_key(wf): listt.append(i2w[wf]) else: listt.append(wf) listt.sort() return ','.join(listt)
def retrieve(serverURL=None,method_name=None,componentID=None): try: if serverURL==None and method_name==None and componentID==None: sqlStr="""SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE call_state="call_placed" GROUP BY server_url; """ elif serverURL==None and method_name==None and componentID!=None: sqlStr="""SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND call_state="call_placed" GROUP BY server_url; """ %(componentID) elif serverURL==None and method_name!=None and componentID!=None: sqlStr="""SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND service_call="%s" AND call_state="call_placed" GROUP BY server_url; """ %(componentID,method_name) elif serverURL!=None and method_name==None and componentID!=None: sqlStr="""SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND server_url="%s" AND call_state="call_placed" GROUP BY server_url; """ %(componentID,serverURL) Session.execute(sqlStr) rows=Session.fetchall() if len(rows)==0: raise ProdException("No result in local last service call table with componentID :"+\ str(componentID),1000) server_url=rows[0][0] service_call=rows[0][1] component_id=rows[0][2] return [server_url,service_call,component_id] except Exception,ex: raise ProdAgentException("Service commit Error: "+str(ex))
def retrieveReleasedJobs(self, count = 1, jobType = None, workflow = None): """ _retrieveReleasedJobs_ Retrieve released Jobs without specifying site information This is a history method for job queue """ sqlStr = \ """ SELECT DISTINCT job_index FROM jq_queue WHERE status = 'released' """ if workflow != None: sqlStr +=" AND workflow_id=\"%s\" " % workflow if jobType != None: sqlStr += " AND job_type=\"%s\" " % jobType sqlStr += " ORDER BY priority DESC, time DESC LIMIT %s;" % count Session.execute(sqlStr) result = Session.fetchall() result = [ x[0] for x in result ] return result
def get(allocationID=[]): """ __get__ returns the allocations associated to particular ID """ if(type(allocationID)!=list): allocationID=[str(allocationID)] if len(allocationID)==0: return if len(allocationID)==1: sqlStr="""SELECT id,events_missed,events_allocated,events_missed_cumul,events_processed,details,prod_mgr_url,workflow_id,allocation_spec_file FROM we_Allocation WHERE id="%s" """ %(str(allocationID[0])) else: sqlStr="""SELECT id,events_missed,events_allocated,events_missed_cumul,events_processed,details,prod_mgr_url,workflow_id,allocation_spec_file FROM we_Allocation WHERE id IN %s """ %(str(tuple(allocationID))) Session.execute(sqlStr) description=['id','events_missed','events_allocated','events_missed_cumul','events_processed','details','prod_mgr_url','workflow_id','allocation_spec_file'] result=Session.convert(description,Session.fetchall(),oneItem=False,decode=['details']) if len(result)==0: return None if len(result)==1: return result[0] return result
def retrieve(serverURL=None, method_name=None, componentID=None): try: if serverURL == None and method_name == None and componentID == None: sqlStr = """SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE call_state="call_placed" GROUP BY server_url; """ elif serverURL == None and method_name == None and componentID != None: sqlStr = """SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND call_state="call_placed" GROUP BY server_url; """ % (componentID) elif serverURL == None and method_name != None and componentID != None: sqlStr = """SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND service_call="%s" AND call_state="call_placed" GROUP BY server_url; """ % (componentID, method_name) elif serverURL != None and method_name == None and componentID != None: sqlStr = """SELECT server_url,service_call,component_id, max(log_time) FROM ws_last_call WHERE component_id="%s" AND server_url="%s" AND call_state="call_placed" GROUP BY server_url; """ % (componentID, serverURL) Session.execute(sqlStr) rows = Session.fetchall() if len(rows) == 0: raise ProdException("No result in local last service call table with componentID :"+\ str(componentID),1000) server_url = rows[0][0] service_call = rows[0][1] component_id = rows[0][2] return [server_url, service_call, component_id] except Exception, ex: raise ProdAgentException("Service commit Error: " + str(ex))
def getNewRunNumber(workflowID,amount=1): """ __getNewRunNumber__ returns a new run number. The increment is bassed on the run number offset this offset is unique to every prodagent and we assume there is an upperbound of "increment" agents where the offset is smaller tan "increment" but larget than 0 """ global increment sqlStr="""UPDATE we_Workflow SET run_number_count = run_number_count+ %s WHERE id='%s' """ %(str(amount*increment), workflowID) Session.execute(sqlStr) sqlStr="""SELECT run_number_count FROM we_Workflow WHERE id='%s' """ %( workflowID) Session.execute(sqlStr) rows=Session.fetchall() # we retrieve the highest run number now count back result=[] for i in xrange(0,amount): result.append(rows[0][0]-i*increment) result.sort() return result
def hasURL(url): sqlStr = """SELECT COUNT(*) FROM pm_cooloff WHERE url = "%s"; """ %(url) Session.execute(sqlStr) rows = Session.fetchall() if rows[0][0] == 0: return False return True
def hasURL(url): sqlStr="""SELECT COUNT(*) FROM ws_queue WHERE server_url="%s"; """ %(url) Session.execute(sqlStr) rows=Session.fetchall() if rows[0][0]==0: return False return True
def isDone(workflowID): sqlStr="""SELECT done FROM we_Workflow WHERE id='%s' """ %(str(workflowID)) Session.execute(sqlStr) rows=Session.fetchall() if len(rows)==0: return False if rows[0][0]=='true': return True return False
def hasMissingEvents(): sqlStr= """ SELECT we_Allocation.id,we_Allocation.events_missed,we_Allocation.events_missed_cumul,we_Allocation.events_processed,we_Allocation.details,we_Allocation.prod_mgr_url,we_Allocation.workflow_id,we_Allocation.allocation_spec_file FROM we_Allocation WHERE we_Allocation.id NOT IN (SELECT DISTINCT allocation_id FROM we_Job) AND events_missed > 0; """ Session.execute(sqlStr) description=['id','events_missed','events_missed_cumul','events_processed','details','prod_mgr_url','workflow_id','allocation_spec_file'] result=Session.convert(description,Session.fetchall(),oneItem=False,decode=['details']) if len(result)==0: return [] return result
def amount(): """ __amount__ returns the amount of workflows the PA currently works on. """ sqlStr="""SELECT count(*) FROM we_Workflow""" Session.execute(sqlStr) rows=Session.fetchall() return rows[0][0]
def get(id): sqlStr='SELECT id,state,parameters FROM pm_state WHERE '+\ ' id="'+str(id)+'";' Session.execute(sqlStr) rows=Session.fetchall() if len(rows)==0: return {} result={} result['id']=rows[0][0] result['state']=rows[0][1] result['parameters']=cPickle.loads(rows[0][2]) return result
def isFinished(workflowID): """ __isDone__ returns true if this workflow id finished """ sqlStr="""SELECT count(*) FROM we_Allocation WHERE workflow_id="%s" """ %(str(workflowID)) Session.execute(sqlStr) rows=Session.fetchall() if rows[0][0]>0: return False return True
def exists(workflowID): """ __exists__ returns true if an entry exists for this ID """ sqlStr="""SELECT count(*) FROM we_Workflow WHERE id="%s" """ %(str(workflowID)) Session.execute(sqlStr) rows=Session.fetchall() if rows[0][0]==1: return True return False
def retrieveReleasedJobsAtSites(self, count = 1, jobType = None, workflow = None, *sites): """ _retrieveReleasedJobsAtSites_ Get a list of size count matching job indexes from the DB tables matched by: optional workflow id optional job type required list of site index values. that have been released from the DB. This is a history method for job queue """ sqlStr = \ """ SELECT DISTINCT jobQ.job_index FROM jq_queue jobQ LEFT OUTER JOIN jq_site siteQ ON jobQ.job_index = siteQ.job_index WHERE status = 'released' """ if workflow != None: sqlStr +=" AND workflow_id=\"%s\" " % workflow if jobType != None: sqlStr += " AND job_type=\"%s\" " % jobType sqlStr += " AND " if len(sites) > 0: siteStr = "" for s in sites: siteStr += "%s," % s siteStr = siteStr[:-1] sqlStr += " ( siteQ.site_index IN (%s) " % siteStr sqlStr += " OR siteQ.site_index IS NULL ) " else: sqlStr += " siteQ.site_index IS NULL " sqlStr += " ORDER BY priority DESC, time DESC LIMIT %s;" % count Session.execute(sqlStr) result = Session.fetchall() result = [ x[0] for x in result ] return result
def countQueuedActiveJobs(self, sites=None, jobTypes=None): """ get number of queued jobs at sites and """ if sites is None: sites = () if jobTypes is None: jobTypes = () sqlStr = \ """ SELECT COUNT(jobQ.job_index), we_Job.job_type, jobQ.released_site FROM jq_queue jobQ LEFT OUTER JOIN we_Job ON jobQ.job_spec_id = we_Job.id WHERE jobQ.status = 'released' AND we_Job.status IN ('released', 'create', 'submit', 'inProgress') """ if len(sites) > 0: siteStr = "" for s in sites: siteStr += "%s," % s siteStr = siteStr[:-1] sqlStr += " AND siteQ.site_index IN (%s) " % siteStr #sqlStr += " OR siteQ.site_index IS NULL ) " #else: # sqlStr += " siteQ.site_index IS NULL " if len(jobTypes) > 0: typeStr = "" for t in jobTypes: typeStr += "%s," % s typeStr = typeStr[:-1] sqlStr += " AND job_type IN (%s) " % siteStr sqlStr += " GROUP BY we_Job.job_type, jobQ.released_site" Session.execute(sqlStr) temp = Session.fetchall() result = {} [ result.setdefault(site, {}).__setitem__(type, int(jobs)) for \ jobs, type, site in temp ] for site in result.keys(): result[site]['Total'] = sum(result[site].values()) return result
def getNotDownloaded(): """ __getNotDownloaded__ returns the workflows this PA should work on but from which the workflow file has not been downloaded yet. """ sqlStr="""SELECT events_processed,id,priority,prod_mgr_url, workflow_spec_file,workflow_type FROM we_Workflow WHERE workflow_spec_file="not_downloaded" """ Session.execute(sqlStr) description=['events_processed','id','priority','prod_mgr_url',\ 'workflow_spec_file','workflow_type'] return Session.convert(description,Session.fetchall())
def isAllocationsFinished(workflowID): """ __isAllocationsFinished__ returns true if the allocations this PA was working on for this workflow are finshed. """ sqlStr="""SELECT COUNT(*) FROM we_Allocation WHERE workflow_id='%s' """ %(str(workflowID)) Session.execute(sqlStr) rows=Session.fetchall() if rows[0][0]==0: return True return False
def testA(self): print('Inserting workflows') try: Session.set_database(dbConfig) Session.connect() Session.start_transaction() Session.execute("SELECT asdf FROM bla;") return workflows=[] for i in xrange(0,10): workflow={} workflow['events_processed']=1000 workflow['id']='workflow_id'+str(i) workflow['owner']='elmo' workflow['priority']=123 workflow['prod_mgr_url']='http://some.where.over.the.rainbow' workflows.append(workflow) Session.insert("we_Workflow",workflows) #now we will brake the session object deliberatly Session.session['default']['connection']=None Session.session['default']['cursor']=None sqlStr="""SELECT COUNT(*) FROM we_Workflow""" print('Case 1 (none type connection and cursor)*****************') Session.execute(sqlStr) rows=Session.fetchall() self.assertEqual(int(rows[0][0]),10) #now we will brake the session object again deliberatly Session.session['default']['connection']=None Session.session['default']['cursor']=None print('Case 2 (none type connection and cursor)****') Session.commit() #put in a query that is incorrect (should raise an error) print('Case 2 (malformed query)*********************************') try: sqlStr="""INSERT some garbage""" Session.execute(sqlStr) except Exception,ex: print("Error testing successful : "+str(ex)) #put in a query that violates a db constraint (should raise an error) print('Case 3 (wellformed query with db constraint violation)***') try: sqlStr="""INSERT INTO we_Workflow(events_processed,id,owner,priority,prod_mgr_url) VALUES("1000","%s","elmo","123","http://some.where.over.the.rainbow") """ %(str("workflow_id"+str(1))) Session.execute(sqlStr) except Exception,ex: print("Error testing successful : "+str(ex))
def retrieveJobIDs(workflowIDs=[]): if type(workflowIDs)==list: if len(workflowIDs)==1: workflowIDs=workflowIDs[0] if len(workflowIDs)==0: return if type(workflowIDs)==list: sqlStr=""" SELECT id FROM we_Job WHERE workflow_id IN %s """ %(str(tuple(workflowIDs))) else: sqlStr=""" SELECT id FROM we_Job WHERE workflow_id="%s" """ %(str(workflowIDs)) Session.execute(sqlStr) result=Session.fetchall() return result
def getAssociatedSites(workflowID): """ _getAssociatedSites_ Return a list of associated site indices from rc_site that have associations to the workflow ID provided """ sqlStr = """SELECT site_index FROM we_workflow_site_assoc WHERE workflow_id="%s"; """ % workflowID Session.execute(sqlStr) result = Session.fetchall() result = [ x[0] for x in result ] return result
def getHighestPriority(nth=0): """ ___getHighestPriority___ gets the nth highest priority if exists. """ sqlStr="""SELECT events_processed,id,priority,prod_mgr_url, workflow_spec_file,workflow_type FROM we_Workflow ORDER by priority limit %s """ %(str(nth+1)) Session.execute(sqlStr) rows=Session.fetchall() if nth>(len(rows)-1): return [] row=rows[nth] description=['events_processed','id','priority','prod_mgr_url',\ 'workflow_spec_file','workflow_type'] return Session.convert(description,[row],True)
def getCollectedLogDetails(): """ return details of all collected logs """ sqlStr = """SELECT workflow, se_name, lfn FROM log_input WHERE status IN ('new', 'inprogress') ORDER BY workflow, se_name""" Session.execute(sqlStr) temp = Session.fetchall() result = {} for wf, se, log in temp: result.setdefault(wf, {}).setdefault(se, []).append(log) return result
def selecAllNodes(): """ _selecAllNodes_ Returns a list of all the nodes from jobEM_node_info table. (HostID, HostName, number_jobs) """ sqlStr = \ """ SELECT * FROM jobEM_node_info """ Session.execute(sqlStr) result = Session.fetchall() return result
def getCMSSoftFromProdMon(reverse=False): """ Returns a map between Worklow and CMSSoftware release if reverse, return the reverse """ sqlStr='select workflow_name,app_version from prodmon_Workflow;' Session.execute(sqlStr) rows=Session.fetchall() work={} for i in rows: if reverse: if not work.has_key(i[1]): work[i[1]]=[] work[i[1]].append(i[0]) else: work[i[0]]=i[1] return work
def flagSet(self, triggerId, jobSpecId, flagId): """ _flagSet_ -triggerId (string). Id of the trigger -flagId (string). Id of the flag -jobSpecId (string). Id of the job specification Returns true/false if the flag has been set or not, or an exception if the flag does not exists. """ sqlStr = """SELECT FlagValue FROM tr_Trigger WHERE TriggerID="%s" AND FlagID="%s" AND JobSpecID="%s"; """ % (triggerId, flagId, jobSpecId) Session.execute(sqlStr) rows = Session.fetchall() if (len(rows) == 0): return False if rows[0][0] != "finished": return False return True