def removeFile(self, RunNumber, **options):
""" Add a new file to an existing run.
The run is identified by its run number.
The file is identified by its name and is
additionally characterized by its stream.
Optionally a set of named options may be passed,
which will be used to directly populate the
file parameters table
@author M.Frank
"""
run = str(RunNumber)
if (options.has_key('Name')):
opt = DbCore.sqlOpts(**options)
stmt = "SELECT FileID FROM " + FilesTable + " WHERE RunNumber=" + run
if (len(opt) > 0):
stmt = stmt + " AND " + opt
cur = DbCore.Cursor(self.db()).select(stmt)
fids = ''
while (cur.next().isSuccess()):
fids = fids + str(cur.result()[0]) + ', '
if (len(fids) > 0):
fids = fids[:-2]
stm = "DELETE FROM " + FileParamsTable + " WHERE FileID IN (" + fids + ")"
result = self._exec(stmt, 1)
if (result[0] != SUCCESS):
return fail('Cannot delete file parameters for file IDs=',
fids)
stmt = "DELETE FROM " + FilesTable + " WHERE RunNumber=" + run
result = self._exec(stmt, 1)
if (result[0] != SUCCESS):
return fail('Cannot delete files with IDs=', fids)
return (SUCCESS, )
return fail(
'Cannot remove file. At least the file name must be specified.')
def runParams(self, RunNumber, **options):
""" Retrieve all parameters of a given run.
The run is identified by its run number.
The parameters are returned in a tuple of
tuples: (status, ((name,value,type),...))
@author M.Frank
"""
try:
res = []
opt = DbCore.sqlOpts(**options)
stmt = "SELECT RunNumber, Name, Val, Typ from " + RunParamsTable + " WHERE RunNumber=" + str(
RunNumber)
if (len(opt) > 0):
stmt = stmt + " AND " + opt
# print 'runParams:',stmt
cur = DbCore.Cursor(self.db()).select(stmt)
while (cur.next().isSuccess()):
res.append(cur.result())
if (len(res) == 0):
res = self.existsRun(RunNumber=RunNumber)
if (res[0] == SUCCESS):
return (SUCCESS, [])
return fail('[No such run]: Run ', RunNumber,
' does not exist!')
return (SUCCESS, res)
except Exception, X:
return fail('[Internal Error (runParams)] ', X, ' Statement=',
stmt)
def files(self, **options):
""" Access files according to some selection criteria.
Calling sequence:
files(<CriteriaName>=<Value>,[<CriteriaName>=<Value>]...)
Returns:
Tuple containing status code and result (or error description)
@author M.Frank
"""
try:
stmt = "SELECT FileID, RunNumber, FileName, FileStatus, StartDate, EndDate, Stream,\
MD5Sum, LogicalName, LogicalStatus, EventStat, FileSize\
FROM " + FilesTable + " WHERE " + DbCore.sqlOpts(**options)
if (len(options) > 0):
res = []
cur = DbCore.Cursor(self.db()).select(stmt)
while (cur.next().isSuccess()):
res.append(cur.result())
return (SUCCESS, res)
return fail(
'[Invalid arguments] You need to specify some selection criteria!'
)
except Exception, X:
return fail(X)
def runParam(self, RunNumber, Name):
""" Access a parameter of an existing run.
The run is identified by its run number.
The parameter is identified by its name.
@author M.Frank
"""
try:
stmt = "SELECT RunNumber,Name,Val,Typ from " + RunParamsTable + " \
WHERE RunNumber=" + str(RunNumber) + " \
AND Name='" + Name + "'"
res = []
cur = DbCore.Cursor(self.db()).select(stmt)
while (cur.next().isSuccess()):
res.append(cur.result())
if (len(res) == 1):
return (SUCCESS, res[0])
elif (len(res) == 0):
return fail('Run parameter:', Name, ' for run ', RunNumber,
' does not exist!')
elif (len(res) > 1):
return fail(
'[Internal Error] Multiple entries for run parameter:',
Name, ' [run:', RunNumber, '] found!')
except Exception, X:
return fail(X)
def __init__(self, login):
""" Constructor
@author M.Frank
"""
self.login = login
self.core = DbCore.DbCore(self.login)
def selectByYearInfo(self):
b = """<H2>LHCb Run Database: Access runs by year</H2>
<TABLE border="1" cellpadding="5" cellspacing="0">
<TR><TD align="left" bgcolor="#9DFF9D"><B>Year</B></TD>
<TD align="left" bgcolor="#9DFF9D"> </TD>
<TD align="left" bgcolor="#9DFF9D"> </TD>
<TD align="left" bgcolor="#9DFF9D"><B>Number of runs</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>First run</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>Last run</B></TD>
</TR>
"""
tmp = ''
stmt = "SELECT COUNT(*), SUBSTRING(StartDate,1,4), MIN(RunNumber), MAX(RunNumber) FROM RunDbruns GROUP BY SUBSTRING(StartDate,1,4)"
cur = DbCore.Cursor(self.service.db()).select(stmt)
result = []
while ( cur.next().isSuccess() ):
r = cur.result();
year = str(r[1])
ref = '/RunDbSummary?-show=year&-year='+year
tmp = tmp + '<TR><TD align="left">'+year+'</TD>'+\
'<TD align="left"><A href="'+ref+'&-fillsonly=YES" target="">Show fills</A></TD>'+\
'<TD align="left"><A href="'+ref+'" target="">Show runs</A><sup>(1)</sup></TD>'+\
'<TD align="left">'+str(r[0])+'</TD>'+\
'<TD align="left">'+str(r[2])+'</TD>'+\
'<TD align="left">'+str(r[3])+'</TD></TR>'
b = b + tmp
b = b + """</TABLE>
<sup>(1)</sup> This operation may require some time.
"""
return b
def removeFileParam(self, **options):
""" Add a new parameter to the FileParameters table.
@author M.Frank
"""
stmt = ""
try:
name = options['Name']
fid = None
if (options.has_key('FileID')):
fid = str(options['FileID'])
else:
stmt = "SELECT f.FileID from " + FilesTable + " f \
WHERE f.Name='" + options['File'] + "' \
AND f.RunNumber=" + str(options['RunNumber'])
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
fid = str(cur.result()[0])
else:
return fail('Cannot add file Parameter '+name+\
' [File does not exist]\n'+result[1])
stmt = "DELETE FROM " + FileParamsTable + " WHERE Name='" + Name + " AND FileID=" + fid
result = self._exec(stmt, 1)
if (result[0] != SUCCESS):
return fail('Cannot add file Parameter ', name, '\n',
result[1])
return (SUCCESS, )
except KeyError, X:
return fail('[Insufficient arguments supplied]: ', options, '\n',
X)
def exists(self, resnam):
count = 0
stmt = "SELECT COUNT(*) FROM " + ResourceTable + " WHERE Name='" + resnam + "'"
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
count = self.db().executeQuery(stmt)[0]
return (SUCCESS, count > 0)
def get(self, resnam):
stmt = "SELECT Name, Size, Initial, Val, Field FROM " + ResourceTable + " WHERE Name='" + resnam + "'"
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
res = cur.result()
if (res is not None):
return (SUCCESS, res)
return fail('Resource ', resnam, ' does not exist')
def runs(self, **options):
""" Retrieve all runs according to SQL restrictions defined in the
options dictionary.
@author M.Frank
"""
stmt = ''
try:
opt = DbCore.sqlOpts(**options)
stmt = "SELECT RunNumber,FillNumber,Partition,Activity,StartDate,EndDate,ProgramName,ProgramVersion,IntegratedLumi,Keep,Migrate \
FROM " + RunsTable + " WHERE " + opt
cur = DbCore.Cursor(self.db()).select(stmt)
res = []
while (cur.next().isSuccess()):
res.append(cur.result())
return (SUCCESS, res)
except KeyError, X:
return fail(X, '\nStatement=', stmt)
def fileParams(self, **options):
"""
@author M.Frank
"""
stmt = ''
try:
fid = ''
if (options.has_key('FileID')):
opt = DbCore.sqlOpts(FileID=options['FileID'])
stmt = str(opt)
elif (options.has_key('FileName')):
stmt = "SELECT DISTINCT FileID from " + FilesTable + " WHERE " + DbCore.sqlOpts(
FileName=options['FileName'])
# print '0 - fileParams:', stmt
cur = DbCore.Cursor(self.db()).select(stmt)
while (cur.next().isSuccess()):
fid = fid + str(cur.result()[0]) + ', '
if (len(fid) == 0):
return fail('Cannot access file parameters.\n')
fid = fid[:-2]
stmt = ' FileID IN (' + fid + ')'
elif (options.has_key('File')):
fileopts = options['File']
if (fileopts.has_key('FileID')):
fid = str(fileopts['FileID'])
else:
stmt = "SELECT DISTINCT FileID from " + FilesTable + " WHERE " + DbCore.sqlOpts(
**fileopts)
# print '1 - fileParams:', stmt
cur = DbCore.Cursor(self.db()).select(stmt)
while (cur.next().isSuccess()):
fid = fid + str(cur.result()[0]) + ', '
if (len(fid) == 0):
return fail('Cannot access file parameters.\n')
fid = fid[:-2]
stmt = ' FileID IN (' + fid + ')'
if (options.has_key('Param')):
paropt = options['Param']
par = DbCore.sqlOpts(**paropt)
if (len(par) > 0):
if (len(stmt) > 0): stmt = stmt + " AND " + par
else: stmt = par
stm = "SELECT FileID,Name,Val,Typ FROM " + FileParamsTable + " WHERE " + stmt
# print 'fileParams:', stm
res = []
cur = DbCore.Cursor(self.db()).select(stm)
while (cur.next().isSuccess()):
res.append(cur.result())
return (SUCCESS, res)
except KeyError, X:
return fail('[Insufficient arguments supplied]: ', options, '\n',
X)
def selectByFillInfo(self, restriction=None):
b = """<H2>LHCb Run Database: Access runs by fill</H2>
<TABLE border="1" cellpadding="5" cellspacing="0">
<TR><TD align="left" bgcolor="#9DFF9D"><B>Select fill</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>Number of runs</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>First run</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>Last run</B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>Start date<sup>(1)</sup></B></TD>
<TD align="left" bgcolor="#9DFF9D"><B>End date<sup>(1)</sup></B></TD>
</TR>
"""
tmp = ''
stmt = """SELECT COUNT(*),
FillNumber,
MIN(RunNumber),
MAX(RunNumber),
MIN(StartDate),
MAX(EndDate)
FROM RunDbRuns """
if ( restriction ):
stmt = stmt + restriction
stmt = stmt + """
GROUP BY FillNumber
ORDER BY MIN(StartDate)"""
cur = DbCore.Cursor(self.service.db()).select(stmt)
cnt = 0
result = []
while ( cur.next().isSuccess() ):
cnt = cnt + 1
r = cur.result();
fill = str(r[RUN_FILLNUMBER])
ref = '/RunDbSummary?-show=fill&-fill='+fill
tmp = tmp + '<TR><TD align="left"><A href="'+ref+'" target="">'+fill+'</A></TD>'+\
'<TD align="left">'+str(r[0])+'</TD>'+\
'<TD align="left">'+str(r[2])+'</TD>'+\
'<TD align="left">'+str(r[3])+'</TD>'+\
'<TD align="left">'+db_time(r[4])+'</TD>'+\
'<TD align="left">'+db_time(r[5])+'</TD></TR>'
b = b + tmp
b = b + """</TABLE>
<sup>(1)</sup> Date format: yyyy-mm-dd H24:MM:SS
"""
if ( cnt == 0 ):
b = b + errstr('[No fills found] for condition:',restriction)
return b
def existsRun(self, **options):
""" Check the existence of a given run according to SQL restrictions
defined in the options dictionary.
@author M.Frank
"""
try:
stmt = 'SELECT r.RunNumber FROM ' + RunsTable + ' r WHERE '
for o in options:
stmt = stmt + str(o) + '=' + str(options[o]) + ' AND '
stmt = stmt[:len(stmt) - 5]
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
return (SUCCESS, )
return fail('Unknown run number')
except KeyError, X:
return fail(X)
def nextSequenceID(self, name):
""" Retrieve the next available identifier of a named sequence.
The sequence is identified by its name.
@author M.Frank
"""
try:
stmt = 'UPDATE '+SequencesTable+' s '\
'SET s.Name=s.Name, s.Entry=s.Entry+1 '\
'WHERE s.Name=\''+name+'\''
result = self._exec(stmt, 0)
if (result[0] != SUCCESS):
return fail('Cannot get next sequence ID: ' + name + '\n' +
result[1])
stmt = "SELECT s.Entry FROM " + SequencesTable + " s WHERE s.Name='" + name + "'"
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
return (SUCCESS, cur.result()[0])
return fail(strtime() + ' Cannot access sequence ID: ' + name)
except Exception, X:
return fail(X)
def addFileParam(self, Name, Val, Typ, **options):
""" Add a new parameter to the FileParameters table.
@author M.Frank
"""
stmt = ''
try:
name = Name # options['Name']
val = str(Val) # str(options['Val'])
typ = str(Typ) # options['Typ']
fid = None
if (options.has_key('FileID')):
fid = str(options['FileID'])
else:
stmt = "SELECT FileID from " + FilesTable + "\
WHERE FileName='" + options['FileName'] + "' \
AND RunNumber=" + str(options['RunNumber'])
cur = DbCore.Cursor(self.db()).select(stmt)
if (cur.next().isSuccess()):
fid = str(cur.result()[0])
if (cur.next().isSuccess()):
return fail('Cannot add file Parameter ', name,
' [Ambiguous file ', options['FileName'],
']')
else:
return fail('Cannot add file Parameter '+name+\
' [File does not exist]\n'+result[1])
stmt = "INSERT INTO " + FileParamsTable + " (FileID, Name, Val, Typ) \
VALUES (" + fid + ",'" + name + "','" + val + "','" + typ + "')"
result = self._exec(stmt, 1)
if (result[0] != SUCCESS):
return fail('Cannot add file Parameter ', name, '\n',
result[1])
return (SUCCESS, )
except KeyError, X:
return fail('[Insufficient arguments supplied]: ', options, '\n',
X)
def all_intra_mut():
mypath = '/home/fede/Dropbox/projects/ig_davide/new_seqs/new_samples'
input_files = [os.path.join(mypath, f) for f in os.listdir(mypath)
if os.path.isfile(os.path.join(mypath, f)) and f[0] != '.']
input_files.append('/home/fede/Dropbox/projects/ig_davide/new_seqs/B4_db-pass.tab_CON-FUN-N.tab')
input_files.append('/home/fede/Dropbox/projects/ig_davide/new_seqs/B5_db-pass.tab_CON-FUN-N.tab')
print input_files
out_files, mut_levels = [], []
for f in input_files:
## donor B4
igs = [i for i in DbCore.readDbFile(f)]
# 1. Naive
naive = [i for i in igs if (i.subset.lower() == 'n' and i.mut == 0)]
naive_filtered, _ = remove_duplicate_junctions(naive)
if naive_filtered:
o, mut = intra_donor_distance(naive_filtered, type_ig='Naive', donor=f.split('/')[-1], bins=100)
out_files.append(o)
mut_levels.append(mut)
# 2. Mem
mem = [i for i in igs if (i.subset.lower()[3] == 'mem')]
mem_filtered, _ = remove_duplicate_junctions(mem)
if mem_filtered:
# sets1 = [(x, i+1) for i in range(int(max([m.mut for m in mem_filtered if m.mut < 24])))] + [(24,29)]
max_mut = int(max([m.mut for m in mem_filtered]))
sets = [(i-1, i) for i in range(1, 24)] + [(23,max_mut+1)]
combinations = [x for x in zip(sets, sets)]
for i, j in combinations:
o, mut = intra_donor_distance(mem_filtered, i, j,
type_ig='Mem', donor=f.split('/')[-1], bins=100)
out_files.append(o)
mut_levels.append(mut)
return out_files, mut_levels
def dump(self, **options):
""" Dump database content.
@author M.Frank
"""
try:
result = ''
opt = DbCore.sqlOpts(**options)
if (len(opt) == 0): opt = "RunNumber>0"
stmt = "SELECT RunNumber,FillNumber,Partition,Activity,StartDate,EndDate,ProgramName,ProgramVersion,IntegratedLumi \
FROM " + RunsTable + " WHERE " + opt
cur = DbCore.Cursor(self.db()).select(stmt)
result = result + '\n\n\n%-8s %-8s %-8s %-12s %-24s %-24s %8s %8s %11s\n' % (
'Run', 'Fill', 'PID', 'Activity', 'Start Date', 'End Date',
'Pgm', 'Vsn', 'Lumi')
while (cur.next().isSuccess()):
(RunNumber, FillNumber, Partition, Activity, StartDate,
EndDate, ProgramName, ProgramVersion,
IntegratedLumi) = cur.result()
result = result + '%-8d %-8d %-8s %-12s %-24s %-24s %8s %8s %11.2f\n' % (
RunNumber, FillNumber, str(Partition), Activity,
db_time(StartDate), db_time(EndDate), ProgramName,
ProgramVersion, IntegratedLumi)
pars = self.runParams(RunNumber)
if (pars[0] == SUCCESS):
context = 'RunParameters:'
pars = pars[1]
for p in pars:
(ID, Name, Value, Type) = p
result = result + ' %-19s %-24s %-s\n' % (
context, Name + '[' + Type + ']', Value)
context = ''
files = self.files(RunNumber=RunNumber,
FileName=(
"LIKE",
"'%'",
))
if (files[0] != SUCCESS):
result = result + 'Failed to retrieve files for run:' + str(
RunNumber) + ' Err=' + str(files[1]) + '\n'
else:
files = files[1]
context = 'Files:'
for f in files:
(ID, RunNumber, FileName, FileStatus, StartDate,
EndDate, Stream, MD5Sum, LogicalName, LogicalStatus,
EventStat, FileSize) = f
result = result + ' %-19s %-8d %-50s %-15s %-8s %d %d Bytes\n' % (
context, ID, '"' + FileName + '"', '"' + Stream +
'"', FileStatus, EventStat, FileSize)
result = result + ' %-19s %-8s %-50s %-12s %-32s\n' % (
'', '', '"' + LogicalName + '"',
'"' + LogicalStatus + '"', MD5Sum)
pars = self.fileParams(File={
'RunNumber': RunNumber,
'FileName': FileName
},
Param={'Name': ('LIKE', "'%'")})
if (pars[0] != SUCCESS):
result = result + 'Failed to retrieve fileparameters for run:' + str(
RunNumber
) + ' File:' + FileName + ' Err=' + str(
pars[1]) + '\n'
else:
pars = pars[1]
context = 'Parameters:'
for p in pars:
(FID, Name, Value, Type) = p
result = result + ' %25s %-16s %-24s = %-15s\n' % (
'', context, Name + '[' + Type + ']',
Value)
context = ''
if (context == ''): result = result + '\n'
context = ''
result = result + '\n\n\n'
print result
return (SUCCESS, result)
except Exception, X:
import sys, traceback
result = result + 'Exception:\n' + str(X)
info = sys.exc_info()
lns = traceback.format_exception(info[0], info[1], info[2])
for line in lns:
print 'Error:', line
return fail(X)
def __init__(self):
if (ResourceDatabase.Conn is None):
ResourceDatabase.Conn = DbCore.DbCore(ResourceDatabase.Source)
self.core = ResourceDatabase.Conn
import newDefineClones as cl
import DbCore
import os, re, sys
import numpy as np
import matplotlib.pyplot as plt
import scipy
import seaborn
import itertools
from sklearn import mixture
from icing.core import parallel_distance
ham_model = DbCore.getModelMatrix('ham')
sym = cl.default_sym
def remove_duplicate_junctions(igs_list):
igs, juncs = [], []
for ig in igs_list:
junc = re.sub('[\.-]','N', str(ig.junction))
if not junc in juncs:
igs.append(ig)
juncs.append(junc)
return igs, juncs
def calcDist(el1, el2, mut=[]):
#consider ham model
return DbCore.calcDistances([el1, el2], 1, ham_model, 'min', sym, mut)[0,1]
def intra_donor_distance(igs, lim_mut1=None, lim_mut2=None, type_ig='Mem', donor='B4', bins=100):
def calcDist(el1, el2, mut=[]):
#consider ham model
return DbCore.calcDistances([el1, el2], 1, ham_model, 'min', sym, mut)[0,1]
def _core(self):
if (self.core is None):
self.core = DbCore.DbCore(self.login)
return self.core
def db(self):
return DbCore.DbCore(self.core)
