def initialize():
global logTestFile, htmpFile
global fpLogTest, fpHTMP
logTestFile = os.getenv('LOG_TEST')
htmpFile = os.getenv('HTMPUNIQ_INPUT_FILE')
rc = 0
#
# Make sure the environment variables are set.
#
if not logTestFile:
print 'Environment variable not set: LOG_TEST'
rc = 1
#
# Make sure the environment variables are set.
#
if not htmpFile:
print 'Environment variable not set: HTMPUNIQ_INPUT_FILE'
rc = 1
#
# Initialize file pointers.
#
fpLogTest = None
fpHTMP = None
db.useOneConnection(1)
return rc
def updateDatabase(cmds):
dbServer = os.environ['MGD_DBSERVER']
dbName = os.environ['MGD_DBNAME']
dbUser = os.environ['MGD_DBUSER']
dbPasswordFile = os.environ['MGD_DBPASSWORDFILE']
dbPassword = string.strip(open(dbPasswordFile, 'r').readline())
db.set_sqlLogin(dbUser, dbPassword, dbServer, dbName)
# process in batches of 100
total = len(cmds)
try:
db.useOneConnection(1)
while cmds:
print('Current running time (secs): %s' %
(time.time() - STARTTIME))
db.sql(cmds[:100], 'auto')
cmds = cmds[100:]
db.useOneConnection(0)
except:
bailout('Failed during database updates')
print('Processed %d updates to SEQ_Sequence._SequenceStatus_key' % total)
print('Total running time (secs): %s' % (time.time() - STARTTIME))
return
def init():
# Purpose: initialize file descriptors, create connection to
# the database and load lookups
# Returns: nothing
# Assumes: nothing
# Effects: gives SQL commands to the database
# Throws: nothing
global inFile, outFile, nextKey
print '%s' % mgi_utils.date()
print 'Initializing'
db.useOneConnection(1)
db.set_sqlLogin(user, password, mgdServer, mgdDB)
results = db.sql('''select max(_Assoc_key) + 1 as nextKey from %s''' % table, 'auto')
nextKey = results[0]['nextKey']
if nextKey == None:
nextKey = 1001
inFilePath = os.environ['INFILE_NAME']
try:
inFile = open(inFilePath, 'r')
except:
exit('Could not open file for reading %s\n' % inFilePath)
outFilePath = '%s/%s.bcp' % (os.environ['OUTPUTDIR'], table)
try:
outFile = open(outFilePath, 'w')
except:
exit('Could not open file for writing %s\n' % outFilePath)
loadLookups()
def exit(
status, # numeric exit status (integer)
message = None # exit message (string)
):
# Purpose:
# Returns: nothing
# Assumes: nothing
# Effects: nothing
# Throws: nothing
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
except:
pass
try:
reportlib.finish_nonps(reportFile)
except:
pass
db.useOneConnection(0)
sys.exit(status)
def exit(status, message=None):
#
# requires: status, the numeric exit status (integer)
# message (string)
#
# effects:
# Print message to stderr and exits
#
# returns:
#
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
inputerrorFile.close()
ens1errorFile.close()
ens2errorFile.close()
mgierrorFile.close()
except:
pass
try:
db.useOneConnection()
except:
pass
sys.exit(status)
def closeFiles():
if fpLogDiag:
fpLogDiag.close()
if fpLogCur:
fpLogCur.close()
if fpHTMPInput:
fpHTMPInput.close()
if fpHTMPDup:
fpHTMPDup.close()
if fpHTMPError:
fpHTMPError.close()
if fpHTMP:
fpHTMP.close()
if fpGenotype:
fpGenotype.close()
db.useOneConnection(0)
return 0
def init():
print('DB Server:' + db.get_sqlServer())
print('DB Name: ' + db.get_sqlDatabase())
sys.stdout.flush()
db.useOneConnection(1)
openFiles()
loadTempTable()
results = db.sql(
'''select distinct a1.accid as secondary, a2.accid as primary
from ACC_Accession a1, ACC_Accession a2
where a1._MGIType_key = 19
and a1._LogicalDB_key in (9, 27)
and a1.preferred = 0
and a1._object_key = a2._object_key
and a2._MGIType_key = 19
and a2._LogicalDB_key in (9, 27)
and a2.preferred = 1''', 'auto')
for r in results:
s = r['secondary']
p = r['primary']
if s not in secondaryToPrimaryDict:
secondaryToPrimaryDict[s] = []
secondaryToPrimaryDict[s].append(p)
def doDeletes(refsKeys):
# Purpose: deletes all MGI_Relationships created by this load
# Returns: 1 if error, else 0
# Assumes: database connection exists
# Effects: queries a database, writes number deleted to curation log
# Throws: Nothing
db.sql(
'''select _StrainMarker_key
into temporary table toDelete
from MRK_StrainMarker
where _Refs_key in (%s)''' % refsKeys, None)
db.sql('''create index idx1 on toDelete(_StrainMarker_key)''', 'auto')
results = db.sql('''select count(*) as deleteCt
from toDelete''', 'auto')
deleteCt = 0
for r in results:
deleteCt = r['deleteCt']
fpLogCur.write('\nDeleting %s Strain Markers\n\n' % deleteCt)
db.sql(
'''delete from MRK_StrainMarker sm
using toDelete d
where d._StrainMarker_key = sm._StrainMarker_key''', None)
db.commit()
db.useOneConnection(0)
return 0
def exit(
# Purpose: prints error 'message' if it is not None
# writes to log files and exits with 'status'
# Returns: nothing
# Assumes: Nothing
# Effects: Exits with 'status'
status, # numeric exit status (integer)
message=None # exit message (str.
):
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
fpDiagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
fpErrorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
fpDiagFile.close()
fpErrorFile.close()
fpInputFile.close()
except:
pass
db.useOneConnection(0)
sys.exit(status)
def exit(status, message = None):
'''
# requires: status, the numeric exit status (integer)
# message (string)
#
# effects:
# Print message to stderr and exits
#
# returns:
#
'''
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
inputFile.close()
outputFile.close()
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
errorFile.close()
except:
pass
db.useOneConnection()
sys.exit(status)
def initialize():
global mitMapFile, fpMITMap
global mitDiffFile, fpMITDiff
mitMapFile = os.getenv('MIT_MAP_FILE')
mitDiffFile = os.getenv('MIT_DIFF_FILE')
rc = 0
#
# Make sure the environment variables are set.
#
if not mitMapFile:
print 'Environment variable not set: MIT_MAP_FILE'
rc = 1
if not mitDiffFile:
print 'Environment variable not set: MIT_DIFF_FILE'
rc = 1
#
# Initialize file pointers.
#
fpMITMap = None
fpMITDiff = None
db.useOneConnection(1)
return rc
def closefiles():
fpCur.close()
fpDiag.close()
#fpStudentRpt.close()
db.useOneConnection(0)
return 0
def init():
# Purpose: Initialization of database connection and file descriptors,
# create database lookup dictionaries; create dictionary from
# input file
# Returns: 1 if file descriptors cannot be initialized
# Assumes: Nothing
# Effects: opens a database connection
# Throws: Nothing
global egToMarkerDict, mgiToMarkerDict
global fpInFile, fpClustererFile, fpLoadFile, fpQcRpt
user = os.environ['MGD_DBUSER']
passwordFileName = os.environ['MGD_DBPASSWORDFILE']
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
try:
fpInFile = open(inFilePath, 'r')
except:
exit('Could not open file for reading %s\n' % inFilePath)
try:
fpLoadFile = open(loadFilePath, 'w')
except:
exit('Could not open file for writing %s\n' % loadFilePath)
try:
fpQcRpt = open(qcRptPath, 'w')
except:
exit('Could not open file for writing %s\n' % qcRptPath)
# Create lookup of homology IDs to their marker keys
results = db.sql(
'''select a.accid, a._object_key as markerKey, m._organism_key
from acc_accession a, mrk_marker m
where a._mgitype_key = 2
and a._logicalDB_key in (47, 64, 172)
and a._object_key = m._marker_key
and m._marker_status_key = 1''', 'auto')
for r in results:
#print('hMrkID: %s orgKey: %s hMrkKey: %s' % (r['accid'], int(r['_organism_key']), int(r['markerKey']) ))
homologyLookup[r['accid']] = [
int(r['_organism_key']),
int(r['markerKey'])
]
# Create lookup of mouse MGI IDs to their marker keys
results = db.sql(
'''select a.accid, a._object_key as markerKey
from acc_accession a, mrk_marker m
where a._mgitype_key = 2
and a._logicalDB_key = 1
and a.prefixPart = 'MGI:'
and a._object_key = m._marker_key
and m._marker_status_key = 1''', 'auto')
for r in results:
mouseLookup[r['accid']] = r['markerKey']
return
def bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
db.commit()
db.useOneConnection()
noteFile.close()
noteChunkFile.close()
sqlFile.close()
if DEBUG:
return
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
bcpNote = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteTable, currentDir, noteFileName)
diagFile.write('%s\n' % bcpNote)
os.system(bcpNote)
bcpNote = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteChunkTable, currentDir, noteChunkFileName)
diagFile.write('%s\n' % bcpNote)
os.system(bcpNote)
def init():
#
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes global file descriptors/file names
#
# returns:
#
global diagFile, errorFile
db.useOneConnection(1)
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def initialize():
global mgiMapFile
global fpMap
global user
global passwordFile
mgiMapFile = os.getenv('MGI_MAP_FILE')
user = os.getenv('MGD_DBUSER')
passwordFile = os.getenv('MGD_DBPASSWORDFILE')
rc = 0
#
# Make sure the environment variables are set.
#
if not mgiMapFile:
print 'Environment variable not set: MGI_MAP_FILE'
rc = 1
#
# Initialize file pointers.
#
fpMap = None
#
# Use one connection to the database
#
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFile)
db.useOneConnection(1)
return rc
def init():
# Purpose: initialize file descriptors, create connection to
# the database and load lookups
# Returns: nothing
# Assumes: nothing
# Effects: gives SQL commands to the database
# Throws: nothing
global inFile, outFile, nextKey
print('%s' % mgi_utils.date())
print('Initializing')
db.useOneConnection(1)
db.set_sqlLogin(user, password, mgdServer, mgdDB)
results = db.sql(
'''select max(_Assoc_key) + 1 as nextKey from %s''' % table, 'auto')
nextKey = results[0]['nextKey']
if nextKey == None:
nextKey = 1001
inFilePath = os.environ['INFILE_NAME']
try:
inFile = open(inFilePath, 'r')
except:
exit('Could not open file for reading %s\n' % inFilePath)
outFilePath = '%s/%s.bcp' % (os.environ['OUTPUTDIR'], table)
try:
outFile = open(outFilePath, 'w')
except:
exit('Could not open file for writing %s\n' % outFilePath)
loadLookups()
def exit(
status, # numeric exit status (integer)
message=None # exit message (string)
):
# Purpose: writes message to error log and exits
# Returns: nothing
# Assumes: nothing
# Effects: nothing
# Throws: nothing
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
inputFile.close()
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
errorFile.close()
except:
pass
db.useOneConnection(0)
sys.exit(status)
def init():
global fpB6BcpFile, fpMgpBcpFile, sourceLookup, nextAssocKey
try:
fpB6BcpFile = open(b6BcpFile, 'w')
except:
'Could not open file for writing %s\n' % b6BcpFile
sys.exit(1)
if B6_ONLY == 'false':
try:
fpMgpBcpFile = open(mgpBcpFile, 'w')
except:
'Could not open file for writing %s\n' % mgpBcpFile
sys.exit(1)
loadSourceLookup()
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
db.useOneConnection(1)
#
# get next _Assoc_key
#
results = db.sql(''' select nextval('seq_source_assoc_seq') as nextAssocKey ''', 'auto')
nextAssocKey = results[0]['nextAssocKey']
return 0
def init():
# Purpose: Initialization of database connection and file descriptors
# Returns: 1 if file descriptors cannot be initialized
# Assumes: Nothing
# Effects: opens a database connection
# Throws: Nothing
global fpCC, fpH, fpRptFile, fpLoadFile
user = os.environ['MGD_DBUSER']
passwordFileName = os.environ['MGD_DBPASSWORDFILE']
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
# temp report
fpCC = open('connComp.rpt', 'w')
# temp report
fpH = open('hybridCluster.rpt', 'w')
# Sue's temp report
fpRptFile = open(os.environ['HYBRID_RPT'], 'w')
# Load file in homologyload format
try:
fpLoadFile = open(loadFilePath, 'w')
except:
exit('Could not open file for writing %s\n' % loadFilePath)
return
def initialize():
global mgi_to_uniprotFile
global uniprotFile
global markerIPFile
global markerIPRef
global annotEvidence, annotEditor, annotDate
#
# initialize caches
#
db.useOneConnection(1)
db.set_sqlLogFunction(db.sqlLogAll)
mgi_to_uniprotFile = os.getenv('MGI_UNIPROT_LOAD_FILE')
uniprotFile = os.getenv('UNIPROT_ACC_ASSOC_FILE')
markerIPFile = os.getenv('MARKER_IP_ASSOC_FILE')
markerIPRef = os.environ['MARKER_IP_ANNOT_REF']
annotEvidence = os.environ['ANNOT_EVIDENCECODE']
annotEditor = os.environ['ANNOT_EDITOR']
annotDate = os.environ['ANNOT_DATE']
rc = 0
#
# Make sure the required environment variables are set.
#
if not mgi_to_uniprotFile:
print 'Environment variable not set: MGI_UNIPROT_LOAD_FILE'
rc = 1
if not uniprotFile:
print 'Environment variable not set: UNIPROT_ACC_ASSOC'
rc = 1
if not markerIPFile:
print 'Environment variable not set: MARKER_IP_ASSOC_FILE'
rc = 1
if not markerIPRef:
print 'Environment variable not set: MARKER_IP_ANNOT_REF'
rc = 1
if not annotEvidence:
print 'Environment variable not set: ANNOT_EVIDENCECODE'
rc = 1
if not annotEditor:
print 'Environment variable not set: ANNOT_EDITOR'
rc = 1
if not annotDate:
print 'Environment variable not set: ANNOT_DATE'
rc = 1
return rc
def closeFiles():
if fpAssoc:
fpAssoc.close()
db.useOneConnection(0)
return 0
def closeFiles():
inputFile.close();
outputFile.close();
db.useOneConnection(0)
return 0
def init():
'''
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors
#
# returns:
#
'''
global annotFile1, diagFile
global omimToDOLookup
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
annotFile1 = open(annotFileName1, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName1)
db.useOneConnection(1)
#
# omimToDOLookup
# omim id -> do id
# only translate OMIM->DO where OMIM translates to at most one DO id
#
results = db.sql('''
WITH includeOMIM AS (
select a2.accID
from ACC_Accession a1, ACC_Accession a2
where a1._MGIType_key = 13
and a1._LogicalDB_key = 191
and a1._Object_key = a2._Object_key
and a1.preferred = 1
and a2._LogicalDB_key = 15
group by a2.accID having count(*) = 1
)
select distinct a1.accID as doID, a2.accID as omimID
from includeOMIM d, ACC_Accession a1, VOC_Term t, ACC_Accession a2
where d.accID = a2.accID
and t._Vocab_key = 125
and t._Term_key = a1._Object_key
and a1._LogicalDB_key = 191
and a1._Object_key = a2._Object_key
and a2._LogicalDB_key = 15
''', 'auto')
for r in results:
key = r['omimID']
value = r['doID']
omimToDOLookup[key] = []
omimToDOLookup[key].append(value)
def init():
'''
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors
#
# returns:
#
'''
global annotFile1, diagFile
global omimToDOLookup
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
annotFile1 = open(annotFileName1, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName1)
db.useOneConnection(1)
#
# omimToDOLookup
# omim id -> do id
# only translate OMIM->DO where OMIM translates to at most one DO id
#
results = db.sql('''
WITH includeOMIM AS (
select a2.accID
from ACC_Accession a1, ACC_Accession a2
where a1._MGIType_key = 13
and a1._LogicalDB_key = 191
and a1._Object_key = a2._Object_key
and a1.preferred = 1
and a2._LogicalDB_key = 15
group by a2.accID having count(*) = 1
)
select distinct a1.accID as doID, a2.accID as omimID
from includeOMIM d, ACC_Accession a1, VOC_Term t, ACC_Accession a2
where d.accID = a2.accID
and t._Vocab_key = 125
and t._Term_key = a1._Object_key
and a1._LogicalDB_key = 191
and a1._Object_key = a2._Object_key
and a2._LogicalDB_key = 15
''', 'auto')
for r in results:
key = r['omimID']
value = r['doID']
omimToDOLookup[key] = []
omimToDOLookup[key].append(value)
def init():
# Purpose: Initialization of database connection and file descriptors,
# and next available database keys
# Returns: 1 if file descriptors cannot be initialized
# Assumes: Nothing
# Effects: opens a database connection
# Throws: Nothing
global fpInFile, fpClusterBCP, fpMemberBCP, fpAccessionBCP
global nextClusterKey, nextMemberKey, nextAccessionKey
# create file descriptors for input/output files
try:
fpInFile = open(inFile, 'r')
except:
exit(1, 'Could not open file %s\n' % inFile)
try:
fpClusterBCP = open(clusterBCP, 'w')
except:
exit(1, 'Could not open file %s\n' % clusterBCP)
try:
fpMemberBCP = open(memberBCP, 'w')
except:
exit(1, 'Could not open file %s\n' % memberBCP)
try:
fpAccessionBCP = open(accessionBCP, 'w')
except:
exit(1, 'Could not open file %s\n' % accessionBCP)
# get next ACC_Accession, MRK_Cluster and MRK_ClusterMember key
user = os.environ['MGD_DBUSER']
passwordFileName = os.environ['MGD_DBPASSWORDFILE']
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
results = db.sql('''select max(_Cluster_key) + 1 as nextKey
from MRK_Cluster''', 'auto')
if results[0]['nextKey'] is None:
nextClusterKey = 1000
else:
nextClusterKey = results[0]['nextKey']
results = db.sql('''select max(_ClusterMember_key) + 1 as nextKey
from MRK_ClusterMember''', 'auto')
if results[0]['nextKey'] is None:
nextMemberKey = 1000
else:
nextMemberKey = results[0]['nextKey']
results = db.sql('''select max(_Accession_key) + 1 as nextKey
from ACC_Accession''', 'auto')
nextAccessionKey = results[0]['nextKey']
return
def init ():
global accKey, gensatLogicalDBKey, egLogicalDBKey
global markerMGITypeKey, createdByKey
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFile)
db.useOneConnection(1)
#
# Get the keys from the database.
#
cmds = []
cmds.append('select max(_Accession_key) + 1 as _Accession_key from ACC_Accession')
cmds.append('select _LogicalDB_key from ACC_LogicalDB where name = \'%s\'' % (gensatLogicalDB))
cmds.append('select _LogicalDB_key from ACC_LogicalDB where name = \'%s\'' % (egLogicalDB))
cmds.append('select _MGIType_key from ACC_MGIType where name = \'%s\'' % (markerMGIType))
cmds.append('select _User_key from MGI_User where name = \'%s\'' % (createdBy))
results = db.sql(cmds,'auto')
#
# If any of the keys cannot be found, stop the load.
#
if len(results[0]) == 1:
accKey = results[0][0]['_Accession_key']
else:
print 'Cannot determine the next Accession key'
sys.exit(1)
if len(results[1]) == 1:
gensatLogicalDBKey = results[1][0]['_LogicalDB_key']
else:
print 'Cannot determine the Logical DB key for "' + gensatLogicalDB + '"'
sys.exit(1)
if len(results[2]) == 1:
egLogicalDBKey = results[2][0]['_LogicalDB_key']
else:
print 'Cannot determine the Logical DB key for "' + egLogicalDB + '"'
sys.exit(1)
if len(results[3]) == 1:
markerMGITypeKey = results[3][0]['_MGIType_key']
else:
print 'Cannot determine the MGI Type key for "' + markerMGIType + '"'
sys.exit(1)
if len(results[4]) == 1:
createdByKey = results[4][0]['_User_key']
else:
print 'Cannot determine the User key for "' + createdBy + '"'
sys.exit(1)
return
def closeFiles():
fpInFile.close()
fpClusterBCP.close()
fpMemberBCP.close()
fpAccessionBCP.close()
db.useOneConnection(0)
return
def init():
'''
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors/file names
# 4. Initializes global keys
#
# returns:
#
'''
global inputFile, outputFile, diagFile, errorFile, errorFileName, diagFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
diagFileName = inputFileName + '.diagnostics'
errorFileName = inputFileName + '.error'
outputFileName = inputFileName + '.trans'
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
outputFile = open(outputFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outputFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
diagFile.write('Input File: %s\n' % (inputFileName))
diagFile.write('Output File: %s\n' % (outputFileName))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def unsetupSql ():
# Purpose: un-initialize the 'db' module
# Returns: nothing
# Assumes: nothing
# Effects: resets the db.useOneConnection() value
# Throws: nothing
db.useOneConnection(0)
return
def closeFiles():
global fpMap
if fpMap:
fpMap.close()
db.useOneConnection(0)
return 0
def init():
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors/file names
# 4. Initializes global keys
#
# returns:
#
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global strainFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
fdate = mgi_utils.date('%m%d%Y') # current date
head, tail = os.path.split(inputFileName)
diagFileName = tail + '.' + fdate + '.diagnostics'
errorFileName = tail + '.' + fdate + '.error'
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
strainFile = open(strainFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % strainFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
return
def init():
'''
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors/file names
# 4. Initializes global keys
#
# returns:
#
'''
global inputFile, outputFile, diagFile, errorFile, errorFileName, diagFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
diagFileName = inputFileName + '.diagnostics'
errorFileName = inputFileName + '.error'
outputFileName = inputFileName + '.trans'
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
outputFile = open(outputFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outputFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
diagFile.write('Input File: %s\n' % (inputFileName))
diagFile.write('Output File: %s\n' % (outputFileName))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
def init():
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors/file names
# 4. Initializes global keys
#
# returns:
#
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global strainFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
fdate = mgi_utils.date('%m%d%Y') # current date
head, tail = os.path.split(inputFileName)
diagFileName = tail + '.' + fdate + '.diagnostics'
errorFileName = tail + '.' + fdate + '.error'
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
strainFile = open(strainFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % strainFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
return
def init():
global bcpCommand
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global markerFile, refFile, aliasFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
bcpCommand = bcpCommand + db.get_sqlServer() + ' ' + db.get_sqlDatabase(
) + ' %s ' + currentDir + ' %s "\\t" "\\n" mgd'
head, tail = os.path.split(inputFileName)
diagFileName = outputDir + '/' + tail + '.diagnostics'
errorFileName = outputDir + '/' + tail + '.error'
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
markerFile = open(markerFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % markerFileName)
try:
refFile = open(refFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % refFileName)
try:
aliasFile = open(aliasFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % aliasFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
return
def init():
global bcpCommand
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global markerFile, refFile, aliasFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
bcpCommand = bcpCommand + db.get_sqlServer() + ' ' + db.get_sqlDatabase() + ' %s ' + currentDir + ' %s "\\t" "\\n" mgd'
head, tail = os.path.split(inputFileName)
diagFileName = outputDir + '/' + tail + '.diagnostics'
errorFileName = outputDir + '/' + tail + '.error'
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
markerFile = open(markerFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % markerFileName)
try:
refFile = open(refFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % refFileName)
try:
aliasFile = open(aliasFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % aliasFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
return
def finalize():
# Purpose: closes file descriptors and connection to the db
# Returns: nothing
# Assumes: nothing
# Effects: nothing
# Throws: nothing
global inFile, outFile
db.useOneConnection(0)
inFile.close()
outFile.close()
def closeFiles():
if fpLogTest:
fpLogTest.close()
if fpHTMP:
fpHTMP.close()
db.useOneConnection(0)
return 0
def finalize():
# Purpose: closes file descriptors and connection to the db
# Returns: nothing
# Assumes: nothing
# Effects: nothing
# Throws: nothing
global inFile, outFile
db.useOneConnection(0)
inFile.close()
outFile.close()
def finalize():
global fpSnpMrk
db.useOneConnection(0)
#
# Close the bcp file.
#
fpSnpMrk.close()
return
def closeFiles():
# Purpose: closes file descriptors and database connection
# Returns: 0
# Assumes: file descriptors have been initialized
# Effects: None
# Throws: Nothing
db.useOneConnection(0)
fpClusterBCP.close()
fpMemberBCP.close()
return
def closeFiles():
if fpLogDiag:
fpLogDiag.close()
if fpLogCur:
fpLogCur.close()
db.useOneConnection(0)
return 0
def closeFiles():
global fpMITMap, fpMITDiff
if fpMITMap:
fpMITMap.close()
if fpMITDiff:
fpMITDiff.close()
db.useOneConnection(0)
return 0
def processCommandLine():
if len(sys.argv) != 3:
bailout('Incorrect command-line; need two parameters.')
db.set_sqlServer(sys.argv[1])
db.set_sqlDatabase(sys.argv[2])
db.useOneConnection(1)
try:
db.sql('select count(1) from MGI_dbInfo', 'auto')
except:
bailout('Cannot query database %s..%s' % (sys.argv[1], sys.argv[2]))
return
def closeFiles():
# Purpose: closes file descriptors and database connection
# Returns: 0
# Assumes: file descriptors have been initialized
# Effects: None
# Throws: Nothing
fpLoadFile.close()
fpRptFile.close()
# close the database connection
db.useOneConnection(0)
return
def initialize():
global fpQcFile, fpInFile, pubMedByExptDict, geoIdToKeyDict
# create file descriptors
try:
fpInFile = open(inFileName, 'r')
except:
print('Cannot create %s' % inFileName)
# create file descriptors
try:
fpQcFile = open(qcFileName, 'w')
except:
print('Cannot create %s' % qcFileName)
db.useOneConnection(1)
# create the pubmed ID property lookup by GEO experiment
# get all experiments with 2ndary GEO IDs, and their pubMed IDs if they
# have them
db.sql('''select p._Object_key, p.value
into temporary table pubMedIds
from MGI_Property p
where p._PropertyTerm_key = %s
and p._PropertyType_key = %s''' % (pubmedPropKey, propTypeKey), None)
db.sql('''create index idx1 on pubMedIds(_Object_key)''', None)
db.sql('''select e._Experiment_key, a.accid
into temporary table exp
from GXD_HTExperiment e, ACC_Accession a
where e._Experiment_key = a._Object_key
and a._MGIType_key = %s
and a._LogicalDB_key = %s''' % (mgiTypeKey, geoLdbKey), None)
db.sql('''create index idx2 on exp(_Experiment_key)''', None)
results = db.sql('''select e._Experiment_key, e.accid, p.value
from exp e
left outer join pubMedIds p on (e._Experiment_key = p._Object_key)''', 'auto')
for r in results:
key = r['_Experiment_key']
accid = r['accid']
value = r['value']
geoIdToKeyDict[accid] = key
if accid not in pubMedByExptDict:
pubMedByExptDict[accid] = []
if value != None:
pubMedByExptDict[accid].append(value)
db.useOneConnection(0)
return
def closeFiles():
# Purpose: closes file descriptors and database connection
# Returns: 0
# Assumes: file descriptors have been initialized
# Effects: None
# Throws: Nothing
fpInFile.close()
fpLoadFile.close()
fpQcRpt.close()
# close the database connection
db.useOneConnection(0)
return
def process(assayKey): """ processes cache based on assayKey argument if assayKey == 0 we create full BCP File else we live update the cache for one assay """ db.useOneConnection(1) # determine type of load if assayKey == 0: createFullBCPFile() else: updateSingleAssay(assayKey) db.useOneConnection(0)
def init():
# Purpose: open file descriptors
# Returns: Nothing
# Assumes: Nothing
# Effects: creates files in the file system, opens db connection
#
# Open input and output files
#
openFiles()
#
# create database connection
#
db.useOneConnection(1)
def init():
# Purpose: process command line options
# Returns: nothing
# Assumes: nothing
# Effects: initializes global variables
# exits if files cannot be opened
# Throws: nothing
global inputFile, diagFile, errorFile, errorFileName, diagFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
fdate = mgi_utils.date('%m%d%Y') # current date
head, tail = os.path.split(inputFileName)
diagFileName = tail + '.' + fdate + '.diagnostics'
errorFileName = tail + '.' + fdate + '.error'
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
diagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
errorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
# Set Log File Descriptor
db.set_sqlLogFD(diagFile)
diagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
diagFile.write('Server: %s\n' % (db.get_sqlServer()))
diagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
diagFile.write('Input File: %s\n' % (inputFileName))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
return
def closeFiles():
# Purpose: closes file descriptors and database connection
# Returns: 0
# Assumes: file descriptors have been initialized
# Effects: None
# Throws: Nothing
db.useOneConnection(0)
fpClusterBCP.close()
fpMemberBCP.close()
if accessionBCP != '':
fpAccessionBCP.close()
if propertyBCP != '':
fpPropertyBCP.close()
return
def init():
"""
Initialize database connection
Open output files
"""
global seqFile, rawFile, sourceFile, accFile
db.useOneConnection(1)
seqFile = open(seqFileName, 'w')
rawFile = open(rawFileName, 'w')
sourceFile = open(sourceFileName, 'w')
accFile = open(accFileName, 'w')
def runBCP ():
sys.stdout.flush()
db.commit()
db.useOneConnection(0)
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assocTable, currentDir, bcpFile)
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
return
def bcpFiles(
recordsProcessed # number of records processed (integer)
):
outPrepFile.close()
outAssayFile.close()
outAssayNoteFile.close()
outGelLaneFile.close()
outGelLaneStFile.close()
outGelRowFile.close()
outGelBandFile.close()
outAccFile.close()
# update the max Accession ID value
db.sql('select * from ACC_setMax (%d)' % (recordsProcessed), None)
db.commit()
db.useOneConnection(0)
if DEBUG or not bcpon:
return
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), probeprepTable, currentDir, outPrepFileName)
bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assayTable, currentDir, outAssayFileName)
bcp3 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), assaynoteTable, currentDir, outAssayNoteFileName)
bcp4 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelLaneTable, currentDir, outGelLaneFileName)
bcp5 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelLaneStTable, currentDir, outGelLaneStFileName)
bcp6 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelRowTable, currentDir, outGelRowFileName)
bcp7 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), gelBandTable, currentDir, outGelBandFileName)
bcp8 = '%s %s %s %s %s %s "\\t" "\\n" mgd' \
% (bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), accTable, currentDir, outAccFileName)
for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
return
def setupSql (server, # str. name of database server
database, # str. name of database
username, # str. user with full permissions on database
password # str. password for 'username'
):
# Purpose: initialize the 'db' module to use the desired database
# login information
# Returns: nothing
# Assumes: the parameters are all valid
# Effects: initializes the 'db' module with the given login info, and
# tells it to use one connection (rather than a separate
# connection for each db.sql() call)
# Throws: nothing
db.set_sqlLogin (username, password, server, database)
db.useOneConnection(1)
return
def init():
# Purpose: Initialization of database connection and file descriptors,
# create database lookup dictionaries; create dictionary from
# input file
# Returns: 1 if file descriptors cannot be initialized
# Assumes: Nothing
# Effects: opens a database connection
# Throws: Nothing
global fpInFile, fpClusterBCP, fpMemberBCP
global createdByKey, nextClusterKey, nextMemberKey
# create file descriptors for input/output files
try:
fpInFile = open(inFile, 'r')
except:
exit(1, 'Could not open file %s\n' % inFile)
try:
fpClusterBCP = open(clusterBCP, 'w')
except:
exit(1, 'Could not open file %s\n' % clusterBCP)
try:
fpMemberBCP = open(memberBCP, 'w')
except:
exit(1, 'Could not open file %s\n' % memberBCP)
# get next MRK_Cluster and MRK_ClusterMember key
user = os.environ['MGD_DBUSER']
passwordFileName = os.environ['MGD_DBPASSWORDFILE']
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
results = db.sql('''select _User_key from MGI_User where login = '******' ''' % createdBy, 'auto')
createdByKey = results[0]['_User_key']
#print 'createdByKey: %s' % createdByKey
results = db.sql(''' select nextval('mrk_cluster_seq') as nextKey ''', 'auto')
nextClusterKey = results[0]['nextKey']
results = db.sql(''' select nextval('mrk_clustermember_seq') as nextKey ''', 'auto')
nextMemberKey = results[0]['nextKey']
return
def exit(
status, # numeric exit status (integer)
message=None # exit message (str.
):
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
errorFile.close()
except:
pass
db.useOneConnection(0)
sys.exit(status)
def exit(
status, # numeric exit status (integer)
message = None # exit message (string)
):
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
diagFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
errorFile.write('\n\nEnd Date/Time: %s\n' % (mgi_utils.date()))
diagFile.close()
errorFile.close()
except:
pass
db.useOneConnection(0)
sys.exit(status)
def finish_nonps(fp, isHTML = 0):
'''
# requires: fp, the output file descriptor
#
# effects:
# 1. Closes the output file
#
# returns:
#
'''
if isHTML:
fp.write("</PRE>")
fp.write("</BODY>")
fp.write("</HTML>")
fp.close()
db.useOneConnection(0)
def exit(status, message=None):
#
# requires: status, the numeric exit status (integer)
# message (string)
#
# effects:
# Print message to stderr and exits
#
# returns:
#
if message is not None:
sys.stderr.write('\n' + str(message) + '\n')
try:
db.useOneConnection()
except:
pass
sys.exit(status)
