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
