def bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
transTypeFile.close()
transFile.close()
cmd1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_TranslationType', transTypeFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
cmd2 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_Translation', transFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
diagFile.write('%s\n' % cmd1)
diagFile.write('%s\n' % cmd2)
if DEBUG:
return
os.system(cmd1)
os.system(cmd2)
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 bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
transTypeFile.close()
transFile.close()
cmd1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_TranslationType', transTypeFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
cmd2 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_Translation', transFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
diagFile.write('%s\n' % cmd1)
diagFile.write('%s\n' % cmd2)
if DEBUG:
return
os.system(cmd1)
os.system(cmd2)
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 bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
exptFile.close()
exptMarkerFile.close()
accFile.close()
noteFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
cmd1 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expts', currentDir, exptFileName, bcpdelim)
cmd2 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expt_Marker', currentDir, exptMarkerFileName, bcpdelim)
cmd3 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName, bcpdelim)
cmd4 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Notes', currentDir, noteFileName, bcpdelim)
diagFile.write('%s\n' % cmd1)
diagFile.write('%s\n' % cmd2)
diagFile.write('%s\n' % cmd3)
diagFile.write('%s\n' % cmd4)
os.system(cmd1)
os.system(cmd2)
os.system(cmd3)
os.system(cmd4)
# update mld_expts_seq auto-sequence
db.sql(
''' select setval('mld_expts_seq', (select max(_Expt_key) from MLD_Expts)) ''',
None)
db.commit()
# update mld_expt_marker_seq auto-sequence
db.sql(
''' select setval('mld_expt_marker_seq', (select max(_Assoc_key) from MLD_Expt_Marker)) ''',
None)
db.commit()
def header(fp, headerType = "JAX"):
'''
# requires: fp, output file descriptor pointing to an open file
# headerType, string that denotes which header to use
# effects: writes the specified header to the file
'''
jaxheaderfile = '# The Jackson Laboratory - Mouse Genome Informatics (MGI)\n# Copyright 1996, 1999, 2002, 2005, 2008 The Jackson Laboratory\n# All Rights Reserved\n'
#
# always write the JAX header
#
fp.write(jaxheaderfile)
fp.write('# Date Generated: %s\n' % (mgi_utils.date()))
#
# special case
#
if headerType == 'JAX':
fp.write('#\n')
else:
fp.write('# (server = %s, database = %s)\n#\n\n' % (db.get_sqlServer(), db.get_sqlDatabase()))
def executeBCP():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
synFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Synonym', datadir, 'MGI_Synonym.bcp')
diagFile.write('%s\n' % bcp1)
os.system(bcp1)
# update mgi_synonym_seq auto-sequence
db.sql(''' select setval('mgi_synonym_seq', (select max(_synonym_key) from MGI_Synonym)) ''', None)
db.commit()
def bcpFiles():
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
bcpdelim = "|"
if DEBUG or not bcpon:
return
refFile.close()
bcp1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_Reference_Assoc', refFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
diagFile.write('%s\n' % bcp1)
os.system(bcp1)
# update mgi_reference_assoc_seq auto-sequence
db.sql(
''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''',
None)
def executeBCP():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
synFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Synonym', datadir, 'MGI_Synonym.bcp')
diagFile.write('%s\n' % bcp1)
os.system(bcp1)
# update mgi_synonym_seq auto-sequence
db.sql(''' select setval('mgi_synonym_seq', (select max(_synonym_key) from MGI_Synonym)) ''', None)
db.commit()
def bcpFiles():
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
bcpdelim = "|"
if DEBUG or not bcpon:
return
refFile.close()
bcp1 = 'cat %s | bcp %s..%s in %s -c -t\"%s" -S%s -U%s' \
% (passwordFileName, db.get_sqlDatabase(), \
'MGI_Reference_Assoc', refFileName, bcpdelim, db.get_sqlServer(), db.get_sqlUser())
diagFile.write('%s\n' % bcp1)
os.system(bcp1)
# update mgi_reference_assoc_seq auto-sequence
db.sql(''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''', None)
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 bcpFiles():
bcpdelim = "|"
if DEBUG or not bcpon:
return
closeFiles()
bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase())
bcpII = '"|" "\\n" mgd'
bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII)
bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII)
bcp3 = '%s %s "/" %s %s' % (bcpI, mutantTable, mutantFileName, bcpII)
bcp4 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII)
bcp5 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII)
bcp6 = '%s %s "/" %s %s' % (bcpI, accRefTable, accRefFileName, bcpII)
bcp7 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII)
bcp8 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII)
db.commit()
for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
if len(ikmcSQLs) > 0:
print(ikmcSQLs)
db.sql(ikmcSQLs, None)
db.commit()
# update all_allele_seq auto-sequence
db.sql(
''' select setval('all_allele_seq', (select max(_Allele_key) from ALL_Allele)) ''',
None)
# update mgi_reference_assoc_seq auto-sequence
db.sql(
''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) from MGI_Reference_Assoc)) ''',
None)
# update mgi_note_seq auto-sequence
db.sql(
''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''',
None)
# update all_allele_mutation_seq auto-sequence
db.sql(
''' select setval('all_allele_mutation_seq', (select max(_Assoc_key) from ALL_Allele_Mutation)) ''',
None)
# update all_allele_cellline_seq auto-sequence
db.sql(
''' select setval('all_allele_cellline_seq', (select max(_Assoc_key) from ALL_Allele_CellLine)) ''',
None)
# update voc_annot_seq auto-sequence
db.sql(
''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''',
None)
db.commit()
def init ():
print 'DB Server:' + db.get_sqlServer()
print 'DB Name: ' + db.get_sqlDatabase()
sys.stdout.flush()
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
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 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 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():
print('DB Server:' + db.get_sqlServer())
print('DB Name: ' + db.get_sqlDatabase())
sys.stdout.flush()
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
return
def bcpFiles():
# Purpose: BCPs the data into the database
# Returns: 1 if error, else 0
# Assumes: connection to the database
# Effects: copies data into the db
# Throws: Nothing
if DEBUG == 'true':
return 0
closeFiles()
bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase())
bcpII = '"|" "\\n" mgd'
bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII)
bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII)
bcp3 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII)
bcp4 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII)
bcp5 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII)
bcp6 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII)
db.commit()
for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6]:
fpDiagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
# update all_allele_mutation_seq auto-sequence
db.sql(
''' select setval('all_allele_mutation_seq', (select max(_Assoc_key) from ALL_Allele_Mutation)) ''',
None)
# update all_allele_seq auto-sequence
db.sql(
''' select setval('all_allele_seq', (select max(_Allele_key) from ALL_Allele)) ''',
None)
# update mgi_reference_assoc auto-sequence
db.sql(
''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) + 1 from MGI_Reference_Assoc)) ''',
None)
# update mgi_note_seq auto-sequence
db.sql(
''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''',
None)
# update voc_annot_seq auto-sequence
db.sql(
''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''',
None)
db.commit()
return 0
def bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
exptFile.close()
exptMarkerFile.close()
accFile.close()
noteFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
cmd1 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expts', currentDir, exptFileName, bcpdelim)
cmd2 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Expt_Marker', currentDir, exptMarkerFileName, bcpdelim)
cmd3 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName, bcpdelim)
cmd4 = '%s %s %s %s %s %s "%s" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MLD_Notes', currentDir, noteFileName, bcpdelim)
diagFile.write('%s\n' % cmd1)
diagFile.write('%s\n' % cmd2)
diagFile.write('%s\n' % cmd3)
diagFile.write('%s\n' % cmd4)
os.system(cmd1)
os.system(cmd2)
os.system(cmd3)
os.system(cmd4)
def executeBCP():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
accFile.close()
accrefFile.close()
markerFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MRK_Marker', datadir, 'MRK_Marker.bcp')
bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', datadir, 'ACC_Accession.bcp')
bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_AccessionReference', datadir, 'ACC_AccessionReference.bcp')
diagFile.write('%s\n' % bcp1)
diagFile.write('%s\n' % bcp2)
diagFile.write('%s\n' % bcp3)
os.system(bcp1)
os.system(bcp2)
os.system(bcp3)
# update mrk_marker_seq auto-sequence
db.sql(
''' select setval('mrk_marker_seq', (select max(_Marker_key) from MRK_Marker)) ''',
None)
db.commit()
def executeBCP():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
accFile.close()
accrefFile.close()
markerFile.close()
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MRK_Marker', datadir, 'MRK_Marker.bcp')
bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', datadir, 'ACC_Accession.bcp')
bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_AccessionReference', datadir, 'ACC_AccessionReference.bcp')
diagFile.write('%s\n' % bcp1)
diagFile.write('%s\n' % bcp2)
diagFile.write('%s\n' % bcp3)
os.system(bcp1)
os.system(bcp2)
os.system(bcp3)
# update mrk_marker_seq auto-sequence
db.sql(''' select setval('mrk_marker_seq', (select max(_Marker_key) from MRK_Marker)) ''', None)
db.commit()
def execBCP():
bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, setBcp)
fpDiag.write('%s\n' % bcpCmd)
os.system(bcpCmd)
bcpCmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, memberBcp)
fpDiag.write('%s\n' % bcpCmd)
os.system(bcpCmd)
# reset the rnaseq primary key sequence
db.sql(
''' select setval('gxd_htsample_rnaseq_seq', (select max(_rnaseq_key) from gxd_htsample_rnaseq)) ''',
None)
db.commit()
db.useOneConnection(0)
return 0
def bcpFiles():
outSetFile.close()
outMemberFile.close()
db.commit()
if DEBUG or not bcpon:
return
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, outSetFileName)
bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, outMemberFileName)
for bcpCmd in [bcp1, bcp2]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
return
def bcpFiles():
outSetFile.close()
outMemberFile.close()
db.commit()
if DEBUG or not bcpon:
return
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), setTable, outputDir, outSetFileName)
bcp2 = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), memberTable, outputDir, outMemberFileName)
for bcpCmd in [bcp1, bcp2]:
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 init():
'''
# requires:
#
# effects:
# 1. Processes command line options
# 2. Initializes local DBMS parameters
# 3. Initializes global file descriptors/file names
#
# returns:
#
'''
global inputFile, outputFile, diagFile, errorFile
global inputFileName, errorFileName, diagFileName, outputFileName
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_commandLogFile(diagFileName)
# Set Log File Descriptor
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('\nStart file: %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
#
# returns:
#
'''
global inputFile, outputFile, diagFile, errorFile
global inputFileName, errorFileName, diagFileName, outputFileName
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_commandLogFile(diagFileName)
# Set Log File Descriptor
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('\nStart file: %s\n\n' % (mgi_utils.date()))
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 bcpFiles():
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcpCmd = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Relationship', outputDir, bcpFile)
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(2)
# update mgi_relationship auto-sequence
db.sql(''' select setval('mgi_relationship_seq', (select max(_Relationship_key) from MGI_Relationship)) ''', None)
return
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 ():
bcpScript = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
db.commit()
fpImageAccFile.close()
bcpI = '%s %s %s' % (bcpScript, db.get_sqlServer(), db.get_sqlDatabase())
bcpII = '"\\t" "\\n" mgd'
accTable = 'ACC_Accession'
bcpCmd = '%s %s "/" %s %s' % (bcpI, accTable, imageAccFile, bcpII)
os.system(bcpCmd)
return
def bcpFiles():
bcpdelim = "|"
if DEBUG or not bcpon:
return
closeFiles()
bcpI = '%s %s %s' % (BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase())
bcpII = '"|" "\\n" mgd'
bcp1 = '%s %s "/" %s %s' % (bcpI, alleleTable, alleleFileName, bcpII)
bcp2 = '%s %s "/" %s %s' % (bcpI, mutationTable, mutationFileName, bcpII)
bcp3 = '%s %s "/" %s %s' % (bcpI, mutantTable, mutantFileName, bcpII)
bcp4 = '%s %s "/" %s %s' % (bcpI, refTable, refFileName, bcpII)
bcp5 = '%s %s "/" %s %s' % (bcpI, accTable, accFileName, bcpII)
bcp6 = '%s %s "/" %s %s' % (bcpI, accRefTable, accRefFileName, bcpII)
bcp7 = '%s %s "/" %s %s' % (bcpI, noteTable, noteFileName, bcpII)
bcp8 = '%s %s "/" %s %s' % (bcpI, noteChunkTable, noteChunkFileName, bcpII)
bcp9 = '%s %s "/" %s %s' % (bcpI, annotTable, annotFileName, bcpII)
db.commit()
for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6, bcp7, bcp8, bcp9]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
if len(ikmcSQLs) > 0:
print ikmcSQLs
db.sql(ikmcSQLs, None)
db.commit()
# update mgi_reference_assoc_seq auto-sequence
db.sql(''' select setval('mgi_reference_assoc_seq', (select max(_Assoc_key) from MGI_Reference_Assoc)) ''', None)
# update all_allele_cellline_seq auto-sequence
db.sql(''' select setval('all_allele_cellline_seq', (select max(_Assoc_key) from ALL_Allele_CellLine)) ''', None)
# update voc_annot_seq auto-sequence
db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None)
db.commit()
def bcpFiles():
outResultFile.close()
outResultStFile.close()
outResultImageFile.close()
db.commit()
db.useOneConnection(0)
bcpScript = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcpI = '%s %s %s' % (bcpScript, db.get_sqlServer(), db.get_sqlDatabase())
bcpII = '"|" "\\n" mgd'
bcp1 = '%s %s "/" %s %s' % (bcpI, resultTable, outResultFileName, bcpII)
bcp2 = '%s %s "/" %s %s' % (bcpI, resultStTable, outResultStFileName, bcpII)
bcp3 = '%s %s "/" %s %s' % (bcpI, resultImageTable, outResultImageFileName, bcpII)
for bcpCmd in [bcp1, bcp2, bcp3]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
return
def init():
global diagFile, errorFile, inputFile, errorFileName, diagFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
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)
# 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 diagFile, errorFile, inputFile, errorFileName, diagFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
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)
# 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 loadTempTable():
print('Create a bcp file from the input file')
sys.stdout.flush()
#
# Read each record from the input file, perform validation checks and
# write them to a bcp file.
#
line = fpInput.readline()
while line:
#for line in fpInput.readlines():
seqID = str.strip(line)
# write out to the bcp file:
fpBCP.write('%s%s' % (seqID, CRT))
line = fpInput.readline()
#
# Close the bcp file.
#
fpBCP.close()
#
# Load the input data into the temp table.
#
print('Load the input data into the temp table: ' + tempTable)
sys.stdout.flush()
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable,
bcpFile)
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(1)
def bcpFiles():
strainFile.close()
markerFile.close()
accFile.close()
annotFile.close()
noteFile.close()
noteChunkFile.close()
if DEBUG or not bcpon:
return
db.commit()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
bcp1 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), strainTable, outputDir, strainTableBCP)
bcp2 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), markerTable, outputDir, markerTableBCP)
bcp3 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), accTable, outputDir, accTableBCP)
bcp4 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), annotTable, outputDir, annotTableBCP)
bcp5 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteTable, outputDir, noteTableBCP)
bcp6 = '%s %s %s %s %s %s "\|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), noteChunkTable, outputDir, noteChunkTableBCP)
for bcpCmd in [bcp1, bcp2, bcp3, bcp4, bcp5, bcp6]:
diagFile.write('%s\n' % bcpCmd)
os.system(bcpCmd)
# update prb_strain_marker_seq auto-sequence
db.sql(''' select setval('prb_strain_marker_seq', (select max(_StrainMarker_key) from PRB_Strain_Marker)) ''', None)
db.commit()
# update voc_annot_seq auto-sequence
db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None)
db.commit()
return
def loadTempTables():
global assoc
print('Create a bcp file from the gene model input file')
sys.stdout.flush()
#
# Read each record from the gene model input file, perform validation
# checks and write them to a bcp file.
#
line = fpGM.readline()
count = 1
while line:
tokens = re.split(TAB, line[:-1])
gmID = tokens[0]
chromosome = tokens[1]
startCoordinate = tokens[2]
endCoordinate = tokens[3]
strand = tokens[4]
description = tokens[5]
if len(re.findall('[^0-9]', startCoordinate)) > 0:
print('Invalid start coordinate (line ' + str(count) + ')')
fpGMBCP.close()
closeFiles()
sys.exit(1)
if len(re.findall('[^0-9]', endCoordinate)) > 0:
print('Invalid end coordinate (line ' + str(count) + ')')
fpGMBCP.close()
closeFiles()
sys.exit(1)
if strand != '-' and strand != '+' and strand != '.':
print('Invalid strand (line ' + str(count) + ')')
fpGMBCP.close()
closeFiles()
sys.exit(1)
fpGMBCP.write(gmID + TAB + chromosome + TAB + startCoordinate + TAB +
endCoordinate + TAB + strand + TAB + description + NL)
line = fpGM.readline()
count += 1
#
# Close the bcp file.
#
fpGMBCP.close()
print('Create a bcp file from the association input file')
sys.stdout.flush()
#
# Read each record from the association input file, perform validation
# checks and write them to a bcp file.
#
line = fpAssoc.readline()
count = 1
while line:
tokens = re.split(TAB, line[:-1])
mgiID = tokens[0]
gmID = tokens[1]
if re.match('MGI:[0-9]+', mgiID) == None:
print('Invalid MGI ID (line ' + str(count) + ')')
fpAssocBCP.close()
closeFiles()
sys.exit(1)
fpAssocBCP.write(mgiID + TAB + gmID + NL)
#
# Maintain a dictionary of the MGI IDs that are in the association
# input file. The value for each dictionary entry is a list of the
# gene model IDs that are associated with the MGI ID key.
#
if mgiID in assoc:
list = assoc[mgiID]
list.append(gmID)
assoc[mgiID] = list
else:
list = [gmID]
assoc[mgiID] = list
line = fpAssoc.readline()
count += 1
#
# Close the bcp file.
#
fpAssocBCP.close()
#
# Load the temp tables with the input data.
#
print('Load the gene model data into the temp table: ' + gmTempTable)
sys.stdout.flush()
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),gmTempTable,
gmBCPFile)
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(1)
print('Load the association data into the temp table: ' + assocTempTable)
sys.stdout.flush()
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),assocTempTable,
assocBCPFile)
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(1)
return
def initialize():
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global alleleFile, mutationFile, mutantFile, refFile
global accFile, accRefFile, noteFile, noteChunkFile, annotFile
global newAlleleFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
head, tail = os.path.split(inputFileName)
diagFileName = outputDir + '/' + tail + '.diagnostics'
errorFileName = outputDir + '/' + tail + '.error'
newAlleleFileName = outputDir + '/' + tail + '.new'
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:
newAlleleFile = open(newAlleleFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % newAlleleFileName)
try:
inputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
alleleFile = open(alleleFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % alleleFileName)
try:
mutationFile = open(mutationFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % mutationFileName)
try:
mutantFile = open(mutantFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % mutantFileName)
try:
refFile = open(refFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % refFileName)
try:
accFile = open(accFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % accFileName)
try:
accRefFile = open(accRefFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % accRefFileName)
try:
noteFile = open(noteFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteFileName)
try:
noteChunkFile = open(noteChunkFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteChunkFileName)
try:
annotFile = open(annotFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName)
# 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()))
def init():
global bcpCommand
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global outImageFile, outPaneFile, outAccFile
global outCopyrightFile, outCaptionFile
global inImageFile, inPaneFile
global createdByKey
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'
diagFileName = currentDir + '/gxdimageload.diagnostics'
errorFileName = currentDir + '/gxdimageload.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)
# Input Files
try:
inImageFile = open(inImageFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inImageFileName)
try:
inPaneFile = open(inPaneFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inPaneFileName)
# Output Files
try:
outImageFile = open(outImageFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outImageFileName)
try:
outPaneFile = open(outPaneFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outPaneFileName)
try:
outAccFile = open(outAccFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outAccFileName)
try:
outCaptionFile = open(outCaptionFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outCaptionFileName)
try:
outCopyrightFile = open(outCopyrightFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outCopyrightFileName)
db.setTrace(True)
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()))
createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
return
def writeBCP(expID, matrixList):
global rnaSeqKey, combinedKey, bcpCommandList
#print 'creating bcp files for expID: %s' % expID
start_time = time.time()
combinedFile = '%s.%s' % (combinedBcpFile, expID)
combinedFilePath = '%s/%s' % (outputDir, combinedFile)
initCombinedBcp(combinedFilePath)
rnaSeqFile = '%s.%s' % (rnaSeqBcpFile, expID)
rnaSeqFilePath = '%s/%s' % (outputDir, rnaSeqFile)
initRnaSeqBcp(rnaSeqFilePath)
# counts in the bcp file
combinedLineCt = 0
rnaSeqLineCt = 0
for matrix in matrixList:
for row in matrix:
#print row
rowLen = len(row)
# position 0 is always markerKey
# position rowLen -1 is # Biological Replicates
# position rowLen - 2 is ave QN TPM (all samples)
markerKey = row[0]
numBioRepl = row[-1]
aveQnTpm = row[-2]
#print 'avQnTpm: %s' % aveQnTpm
levelKey = calcLevel(aveQnTpm)
#print 'levelKey: %s' % levelKey
line = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s' % (
combinedKey, TAB, markerKey, TAB, levelKey, TAB, numBioRepl,
TAB, aveQnTpm, TAB, createdByKey, TAB, createdByKey, TAB,
loaddate, TAB, loaddate, CRT)
#print 'combined: %s' %line
fpCombinedBcp.write(line)
combinedLineCt += 1
# positions 1 to rowLen-2 are samples: sampleKey|aveTPM|qnTpm
# to get samples we iterate over rowLen-3 starting with i+1
for i in range(rowLen - 3):
#print 'next sample: %s' % row[i+1]
sampleKey, aveTpm, qnTpm = string.split(row[i + 1], PIPE)
line = '%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s' % (
rnaSeqKey, TAB, sampleKey, TAB, combinedKey, TAB,
markerKey, TAB, aveTpm, TAB, qnTpm, TAB, createdByKey, TAB,
createdByKey, TAB, loaddate, TAB, loaddate, CRT)
#print 'rnaseq: %s' % line
fpRnaSeqBcp.write(line)
rnaSeqLineCt += 1
rnaSeqKey += 1
combinedKey += 1
closeCombinedBcp()
closeRnaSeqBcp()
# gather bcp commands in a List so we can execute them together at the
# end of processing
#
cmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), combinedTable, outputDir, combinedFile)
bcpCommandList.append(cmd)
#print 'Num combined bcp lines for expID %s: %s' % (expID, combinedLineCt)
cmd = '%s %s %s %s %s %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), rnaSeqTable, outputDir, rnaSeqFile)
bcpCommandList.append(cmd)
#print 'Num rnaSeq bcp lines for expID %s: %s' % (expID, rnaSeqLineCt)
elapsed_time = time.time() - start_time
elapsed_time = time.time() - start_time
print '%sTIME: Creating bcp files %s %s%s' % (
CRT, expID, time.strftime("%H:%M:%S", time.gmtime(elapsed_time)), CRT)
return 0
def init():
global diagFile, errorFile, errorFileName, diagFileName
global outResultStFile, outResultFile, outResultImageFile
global inResultsFile, inStructureFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
diagFileName = 'resultsload.diagnostics'
errorFileName = 'resultsload.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)
# Input Files
try:
inResultsFile = open(inResultsFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inResultsFileName)
try:
inStructureFile = open(inStructureFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inStructureFileName)
# Output Files
try:
outResultStFile = open(outResultStFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outResultStFileName)
try:
outResultFile = open(outResultFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outResultFileName)
try:
outResultImageFile = open(outResultImageFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outResultImageFileName)
# 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 mgiIDToSymbolDict, termIDToTermDict
global mgdMgiIdToTermIdDict, inputTermIdLookupByMgiId
global mcvTermToParentMkrTypeTermDict
global mcvKeyToTermDict, mkrTypeToAssocMCVTermDict
global mcvTermToMkrTypeDict, mkrKeyToMkrTypeKeyDict, mkrTypeToKeyDict
global updatedBy, updatedByKey
print 'DB Server:' + db.get_sqlServer()
print 'DB Name: ' + db.get_sqlDatabase()
sys.stdout.flush()
db.useOneConnection(1)
#db.set_sqlLogFunction(db.sqlLogAll)
openFiles()
loadTempTable()
# get user key for updates
results = db.sql('''select _User_key from MGI_User where login = '******' ''' % updatedBy)
updatedByKey = results[0]['_User_key']
#
# Load global lookup dictionaries
#
# create lookup of all official markers in the database
# mapped to their symbols
results = db.sql('''select a.accid, m.symbol
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''', 'auto')
for r in results:
mgiIDToSymbolDict[r['accid']] = r['symbol']
# create lookup of all mcv and so ids mapped to their terms
results = db.sql('''select a.accID, t.term
from ACC_Accession a, VOC_Term t
where a._LogicalDB_key in (145,146)
and a._MGIType_key = 13
and a._Object_key = t._Term_key''', 'auto')
for r in results:
termIDToTermDict[r['accID']] = r['term']
# create lookup of markers mapped to their SO/MCV IDs
results = db.sql('''select a1.accID as termID, a2.accID as mgiID
from VOC_Annot v, ACC_Accession a1, ACC_Accession a2
where v._AnnotType_key = 1011
and v._Term_key = a1._Object_key
and a1._MGIType_key = 13
and a1._LogicalDB_key in (145, 146)
and a1.preferred = 1
and a1.prefixPart = 'MCV:'
and v._Object_key = a2._Object_key
and a2._MGIType_key = 2
and a2._LogicalDB_key = 1
and a2.prefixPart = 'MGI:' ''', 'auto')
for r in results:
mgiID = r['mgiID']
termID = r['termID']
if not mgdMgiIdToTermIdDict.has_key(mgiID):
mgdMgiIdToTermIdDict[mgiID] = []
mgdMgiIdToTermIdDict[mgiID].append(termID)
#
# get all SO/MCV annotations to markers from temp table
# loaded from the input file
#
results = db.sql('select tmp.termID, tmp.mgiID from %s tmp ' % (tempTable), 'auto')
#'where tmp.mgiID is not null ' + \
#'and tmp.termID is not null', 'auto')
# load lookup
for r in results:
mgiID = r['mgiID']
termID = r['termID']
if not inputTermIdLookupByMgiId.has_key(mgiID):
inputTermIdLookupByMgiId[mgiID] = [] # default
if termID != None: # this case when only mgiID in file for delete
inputTermIdLookupByMgiId[mgiID].append(termID)
#
# get marker types from the database
#
results = db.sql('''select a.accId as mgiID, t.name
from MRK_Marker m, ACC_Accession a, MRK_Types t
where m._Marker_Status_key = 1
and m._Organism_key = 1
and m._Marker_key = a._Object_key
and a._MGIType_key = 2
and a._LogicalDB_key = 1
and a.preferred = 1
and a.prefixPart = 'MGI:'
and m._Marker_Type_key = t._Marker_Type_key''', 'auto')
for r in results:
mgiIdToMkrTypeDict[r['mgiID']] = r['name']
results = db.sql('''select name, _Marker_Type_key
from MRK_Types''', 'auto')
for r in results:
mkrTypeKeyToMkrTypeDict[ r['_Marker_Type_key'] ] = r['name']
mkrTypeToKeyDict[r['name']] = r['_Marker_Type_key']
# parse the MCV Note and load lookups
# we store the association of a marker type to a MCV
# term in the term Note. Only MCV terms which correspond to
# marker types have these notes
# note looks like:
# Marker_Type=N
#
# _Vocab_key = 79 = Marker Category Vocab
# _NoteType_key = 1001 = Private Vocab Term Comment'
# Get the MCV vocab terms and their notes from the database
# Notes tell us the term's MGI marker type if term maps directly to a
# marker type
cmds = []
cmds.append('''
select n._Object_key, rtrim(nc.note) as chunk, nc.sequenceNum
into temp notes
from MGI_Note n, MGI_NoteChunk nc
where n._MGIType_key = 13
and n._NoteType_key = 1001
and n._Note_key = nc._Note_key
''')
cmds.append('create index notes_idx1 on notes(_Object_key)')
cmds.append('''
select t._Term_key, t.term, n.chunk
from VOC_Term t
left outer join notes n on n._object_key = t._term_key
where t._Vocab_key = 79
and t._Term_key = n._Object_key
order by t._Term_key, n.sequenceNum
''')
results = db.sql(cmds, 'auto')
notes = {} # map the terms to their note chunks
for r in results[2]:
term = r['term']
chunk = r['chunk']
# if there is a note chunk add it to the notes dictionary
# we'll pull all the chunks together later
if chunk != None:
if not notes.has_key(term):
notes[term] = []
notes[term].append(chunk)
# parse the marker type from the note, if there is one
for term in notes.keys():
note = string.join(notes[term], '')
if not note[0:11] == 'Marker_Type':
continue
# parse the note
tokens = string.split(note, ';')
mType = tokens[0]
tokens = string.split(mType, '=')
# 2nd token is the marker type key
mkrTypeKey = int(string.strip(tokens[1]))
mkrType = mkrTypeKeyToMkrTypeDict[mkrTypeKey]
# There is only 1 MCV term per MGI Mkr type
mkrTypeToAssocMCVTermDict[mkrType]= term
mcvTermToMkrTypeDict[term] = mkrType
#
# now map all mcv terms to their parent term representing a marker type
# for all children in the closure table - find the parent
# which is a marker type parent
#
cmds = []
cmds.append('''
select _AncestorObject_key, _DescendentObject_key
into temp clos
from DAG_Closure
where _DAG_key = 9
and _MGIType_key = 13
''')
cmds.append('create index clos_idx1 on clos(_AncestorObject_key)')
cmds.append('create index clos_idx2 on clos(_DescendentObject_key)')
cmds.append('''
select t1.term as ancestorTerm, t2.term as descendentTerm
from clos c, VOC_Term t1, VOC_Term t2
where c._AncestorObject_key = t1._Term_key
and c._DescendentObject_key = t2._Term_key
order by t2.term
''')
results = db.sql(cmds, 'auto')
# the mcv terms that represent marker types
mcvMarkerTypeValues = mkrTypeToAssocMCVTermDict.values()
for r in results[3]:
aTerm = r['ancestorTerm']
dTerm = r['descendentTerm']
if mcvTermToParentMkrTypeTermDict.has_key(dTerm):
# we've already mapped this descendent to its marker type parent
continue
# dTerm may be a marker type term
elif dTerm in mcvMarkerTypeValues:
mcvTermToParentMkrTypeTermDict[dTerm] = dTerm
# if the ancestor of this descendent term is a
# marker type term load it into the dict
elif aTerm in mcvMarkerTypeValues:
mcvTermToParentMkrTypeTermDict[dTerm] = aTerm
# map marker keys to their marker type
cmd = '''
select _Marker_Type_key, _Marker_key
from MRK_Marker
where _Marker_Status_key = 1
'''
results = db.sql(cmd, 'auto')
for r in results:
mkrTypeKey = r['_Marker_Type_key']
mkrKey = r['_Marker_key']
mkrKeyToMkrTypeKeyDict[mkrKey] = mkrTypeKey
def init():
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global strainFile, markerFile, accFile, annotFile
global noteFile, noteChunkFile
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)
try:
markerFile = open(markerFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % markerFileName)
try:
accFile = open(accFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % accFileName)
try:
noteFile = open(noteFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteFileName)
try:
noteChunkFile = open(noteChunkFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteChunkFileName)
try:
annotFile = open(annotFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName)
# 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 diagFile, errorFile, inputFile, errorFileName, diagFileName
global outSetFile, outMemberFile
global setKey, setMemberKey, createdByKey, mgiTypeKey, useSetKey
global DEBUG
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
diagFileName = '%s/setload.diagnostics' % (outputDir)
errorFileName = '%s/setload.error' % (outputDir)
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)
# Output Files
try:
fullPathSetFile = '%s/%s' % (outputDir, outSetFileName)
outSetFile = open(fullPathSetFile, 'w')
except:
exit(1, 'Could not open file %s\n' % fullPathSetFile)
try:
fullPathMemberFile = '%s/%s' % (outputDir, outMemberFileName)
outMemberFile = open(fullPathMemberFile, 'w')
except:
exit(1, 'Could not open file %s\n' % fullPathMemberFile)
# 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()))
if mode == 'preview':
DEBUG = 1
bcpon = 0
elif mode != 'load':
exit(1, 'Invalid Processing Mode: %s\n' % (mode))
results = db.sql('select max(_Set_key) + 1 as maxKey from MGI_Set', 'auto')
setKey = results[0]['maxKey']
createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
mgiTypeKey = loadlib.verifyMGIType(setType, 0, errorFile)
#
# use existing MGI_Set, or create a new one
#
results = db.sql('select _Set_key from MGI_Set where _MGIType_key = %s and name = \'%s\''
% (mgiTypeKey, setName), 'auto')
if len(results) > 0:
for r in results:
setKey = r['_Set_key']
# delete/reload
db.sql('delete from MGI_SetMember where _Set_key = %s' % (setKey), None)
else:
outSetFile.write(str(setKey) + TAB + \
str(mgiTypeKey) + TAB + \
str(setName) + TAB + \
'1' + TAB + \
str(createdByKey) + TAB + str(createdByKey) + TAB + \
loaddate + TAB + loaddate + CRT)
results = db.sql('select max(_SetMember_key) + 1 as maxKey from MGI_SetMember', 'auto')
setMemberKey = results[0]['maxKey']
return
def init():
global mgiIDToSymbolDict, termIDToTermDict
global mgdMgiIdToTermIdDict, inputTermIdLookupByMgiId
global mcvTermToParentMkrTypeTermDict
global mcvKeyToTermDict, mkrTypeToAssocMCVTermDict
global mcvTermToMkrTypeDict, mkrKeyToMkrTypeKeyDict, mkrTypeToKeyDict
global updatedBy, updatedByKey
print('DB Server:' + db.get_sqlServer())
print('DB Name: ' + db.get_sqlDatabase())
sys.stdout.flush()
db.useOneConnection(1)
#db.set_sqlLogFunction(db.sqlLogAll)
openFiles()
loadTempTable()
# get user key for updates
results = db.sql('''select _User_key from MGI_User where login = '******' ''' %
updatedBy)
updatedByKey = results[0]['_User_key']
#
# Load global lookup dictionaries
#
# create lookup of all official markers in the database
# mapped to their symbols
results = db.sql(
'''select a.accid, m.symbol
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''', 'auto')
for r in results:
mgiIDToSymbolDict[r['accid']] = r['symbol']
# create lookup of all mcv and so ids mapped to their terms
results = db.sql(
'''select a.accID, t.term
from ACC_Accession a, VOC_Term t
where a._LogicalDB_key in (145,146)
and a._MGIType_key = 13
and a._Object_key = t._Term_key''', 'auto')
for r in results:
termIDToTermDict[r['accID']] = r['term']
# create lookup of markers mapped to their SO/MCV IDs
results = db.sql(
'''select a1.accID as termID, a2.accID as mgiID
from VOC_Annot v, ACC_Accession a1, ACC_Accession a2
where v._AnnotType_key = 1011
and v._Term_key = a1._Object_key
and a1._MGIType_key = 13
and a1._LogicalDB_key in (145, 146)
and a1.preferred = 1
and a1.prefixPart = 'MCV:'
and v._Object_key = a2._Object_key
and a2._MGIType_key = 2
and a2._LogicalDB_key = 1
and a2.prefixPart = 'MGI:' ''', 'auto')
for r in results:
mgiID = r['mgiID']
termID = r['termID']
if mgiID not in mgdMgiIdToTermIdDict:
mgdMgiIdToTermIdDict[mgiID] = []
mgdMgiIdToTermIdDict[mgiID].append(termID)
#
# get all SO/MCV annotations to markers from temp table
# loaded from the input file
#
results = db.sql('select tmp.termID, tmp.mgiID from %s tmp ' % (tempTable),
'auto')
#'where tmp.mgiID is not null ' + \
#'and tmp.termID is not null', 'auto')
# load lookup
for r in results:
mgiID = r['mgiID']
termID = r['termID']
if mgiID not in inputTermIdLookupByMgiId:
inputTermIdLookupByMgiId[mgiID] = [] # default
if termID != None: # this case when only mgiID in file for delete
inputTermIdLookupByMgiId[mgiID].append(termID)
#
# get marker types from the database
#
results = db.sql(
'''select a.accId as mgiID, t.name
from MRK_Marker m, ACC_Accession a, MRK_Types t
where m._Marker_Status_key = 1
and m._Organism_key = 1
and m._Marker_key = a._Object_key
and a._MGIType_key = 2
and a._LogicalDB_key = 1
and a.preferred = 1
and a.prefixPart = 'MGI:'
and m._Marker_Type_key = t._Marker_Type_key''', 'auto')
for r in results:
mgiIdToMkrTypeDict[r['mgiID']] = r['name']
results = db.sql(
'''select name, _Marker_Type_key
from MRK_Types''', 'auto')
for r in results:
mkrTypeKeyToMkrTypeDict[r['_Marker_Type_key']] = r['name']
mkrTypeToKeyDict[r['name']] = r['_Marker_Type_key']
# parse the MCV Note and load lookups
# we store the association of a marker type to a MCV
# term in the term Note. Only MCV terms which correspond to
# marker types have these notes
# note looks like:
# Marker_Type=N
#
# _Vocab_key = 79 = Marker Category Vocab
# _NoteType_key = 1001 = Private Vocab Term Comment'
# Get the MCV vocab terms and their notes from the database
# Notes tell us the term's MGI marker type if term maps directly to a
# marker type
cmds = []
cmds.append('''
select n._Object_key, rtrim(n.note) as chunk
into temp notes
from MGI_Note n
where n._MGIType_key = 13
and n._NoteType_key = 1001
''')
cmds.append('create index notes_idx1 on notes(_Object_key)')
cmds.append('''
select t._Term_key, t.term, n.chunk
from VOC_Term t
left outer join notes n on n._object_key = t._term_key
where t._Vocab_key = 79
and t._Term_key = n._Object_key
order by t._Term_key
''')
results = db.sql(cmds, 'auto')
notes = {} # map the terms to their note chunks
for r in results[2]:
term = r['term']
chunk = r['chunk']
# if there is a note chunk add it to the notes dictionary
# we'll pull all the chunks together later
if chunk != None:
if term not in notes:
notes[term] = []
notes[term].append(chunk)
# parse the marker type from the note, if there is one
for term in list(notes.keys()):
note = ''.join(notes[term])
if not note[0:11] == 'Marker_Type':
continue
# parse the note
tokens = str.split(note, ';')
mType = tokens[0]
tokens = str.split(mType, '=')
# 2nd token is the marker type key
mkrTypeKey = int(str.strip(tokens[1]))
mkrType = mkrTypeKeyToMkrTypeDict[mkrTypeKey]
# There is only 1 MCV term per MGI Mkr type
mkrTypeToAssocMCVTermDict[mkrType] = term
mcvTermToMkrTypeDict[term] = mkrType
#
# now map all mcv terms to their parent term representing a marker type
# for all children in the closure table - find the parent
# which is a marker type parent
#
cmds = []
cmds.append('''
select _AncestorObject_key, _DescendentObject_key
into temp clos
from DAG_Closure
where _DAG_key = 9
and _MGIType_key = 13
''')
cmds.append('create index clos_idx1 on clos(_AncestorObject_key)')
cmds.append('create index clos_idx2 on clos(_DescendentObject_key)')
cmds.append('''
select t1.term as ancestorTerm, t2.term as descendentTerm
from clos c, VOC_Term t1, VOC_Term t2
where c._AncestorObject_key = t1._Term_key
and c._DescendentObject_key = t2._Term_key
order by t2.term
''')
results = db.sql(cmds, 'auto')
# the mcv terms that represent marker types
mcvMarkerTypeValues = list(mkrTypeToAssocMCVTermDict.values())
for r in results[3]:
aTerm = r['ancestorTerm']
dTerm = r['descendentTerm']
if dTerm in mcvTermToParentMkrTypeTermDict:
# we've already mapped this descendent to its marker type parent
continue
# dTerm may be a marker type term
elif dTerm in mcvMarkerTypeValues:
mcvTermToParentMkrTypeTermDict[dTerm] = dTerm
# if the ancestor of this descendent term is a
# marker type term load it into the dict
elif aTerm in mcvMarkerTypeValues:
mcvTermToParentMkrTypeTermDict[dTerm] = aTerm
# map marker keys to their marker type
cmd = '''
select _Marker_Type_key, _Marker_key
from MRK_Marker
where _Marker_Status_key = 1
'''
results = db.sql(cmd, 'auto')
for r in results:
mkrTypeKey = r['_Marker_Type_key']
mkrKey = r['_Marker_key']
mkrKeyToMkrTypeKeyDict[mkrKey] = mkrTypeKey
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, diagFile, errorFile, errorFileName, diagFileName
global transTypeFile, transFile
global transTypeFileName, transFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
# the default output file names are bases on 'inputFileName'
head, fileName = os.path.split(inputFileName)
# rename 'head'
head = outputFileDir
fdate = mgi_utils.date('%m%d%Y') # current date
diagFileName = head + '/' + fileName + '.' + fdate + '.diagnostics'
print diagFileName
errorFileName = head + '/' + fileName + '.' + fdate + '.error'
print errorFileName
transTypeFileName = head + '/' + fileName + '.' + fdate + '.MGI_TranslationType.bcp'
print transTypeFileName
transFileName = head + '/' + fileName + '.' + fdate + '.MGI_Translation.bcp'
print transFileName
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:
transTypeFile = open(transTypeFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % transTypeFileName)
try:
transFile = open(transFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % transFileName)
# 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()))
def init():
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global outSetFile, outMemberFile
global setKey, setMemberKey, createdByKey, mgiTypeKey, useSetKey
global DEBUG
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
diagFileName = '%s/setload.diagnostics' % (outputDir)
errorFileName = '%s/setload.error' % (outputDir)
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)
# Output Files
try:
fullPathSetFile = '%s/%s' % (outputDir, outSetFileName)
outSetFile = open(fullPathSetFile, 'w')
except:
exit(1, 'Could not open file %s\n' % fullPathSetFile)
try:
fullPathMemberFile = '%s/%s' % (outputDir, outMemberFileName)
outMemberFile = open(fullPathMemberFile, 'w')
except:
exit(1, 'Could not open file %s\n' % fullPathMemberFile)
# 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()))
if mode == 'preview':
DEBUG = 1
bcpon = 0
elif mode != 'load':
exit(1, 'Invalid Processing Mode: %s\n' % (mode))
results = db.sql('select max(_Set_key) + 1 as maxKey from MGI_Set', 'auto')
setKey = results[0]['maxKey']
createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
mgiTypeKey = loadlib.verifyMGIType(setType, 0, errorFile)
#
# use existing MGI_Set, or create a new one
#
results = db.sql(
'select _Set_key from MGI_Set where _MGIType_key = %s and name = \'%s\''
% (mgiTypeKey, setName), 'auto')
if len(results) > 0:
for r in results:
setKey = r['_Set_key']
# delete/reload
db.sql('delete from MGI_SetMember where _Set_key = %s' % (setKey),
None)
else:
outSetFile.write(str(setKey) + TAB + \
str(mgiTypeKey) + TAB + \
str(setName) + TAB + \
'1' + TAB + \
str(createdByKey) + TAB + str(createdByKey) + TAB + \
loaddate + TAB + loaddate + CRT)
results = db.sql(
'select max(_SetMember_key) + 1 as maxKey from MGI_SetMember', 'auto')
setMemberKey = results[0]['maxKey']
return
def loadTempTables():
global build, mb2mgiInInputDict, header, fpMirbaseInvalidIdRpt, fatalErrorCount
print('Create a bcp file from the coordinate input file')
sys.stdout.flush()
#
# Read each record from the coordinate input file, perform validation
# checks and write them to a bcp file.
#
# set the global header value; remove any tabs, preserve newline
header = '%s\n' % fpCoord.readline().strip()
tokens = header.split(';')
for t in tokens:
a = t.split('=')
if a[0].strip().lower() == 'build':
build = a[1].strip()
count = 1
writeInvcoordStrandHeader()
for line in fpCoord.readlines():
tokens = re.split(TAB, line[:-1])
mgiID = tokens[0].strip()
chromosome = tokens[1].strip()
startCoordinate = tokens[2].strip()
endCoordinate = tokens[3].strip()
strand = tokens[4].strip()
source = tokens[5].strip()
display = tokens[6].strip()
miRBaseID = tokens[7].strip()
badIdList = []
if miRBaseID != '':
for id in str.split(miRBaseID, ','):
id = str.strip(id)
if str.find(str.strip(id), 'MI') != 0:
print('bad mirbase id: %s' % id)
badIdList.append(id)
if badIdList:
# report, go to next line
print('bad MiRBase Ids: %s' % ', '.join(badIdList))
fpMirbaseInvalidIdRpt.write(
'%s%s%s%s' % (mgiID, TAB, ', '.join(badIdList, ), NL))
fatalErrorCount += 1
continue
sourceDisplay = '%s/%s' % (source, display)
#print 'sourceDisplay: %s' % sourceDisplay
if not sourceDisplay in sourceDisplayList:
sourceDisplayList.append(sourceDisplay)
errors = createInvCoordStrandReport(mgiID, startCoordinate,
endCoordinate, strand, source)
if errors != 0:
continue
fpCoordBCP.write(mgiID + TAB + chromosome + TAB + startCoordinate +
TAB + endCoordinate + TAB + strand + TAB + source +
TAB + display + TAB + miRBaseID + TAB + build + NL)
count += 1
writeInvcoordStrandFooter()
#
# Close the bcp file.
#
fpCoordBCP.close()
#
# Load the temp tables with the input data.
#
print('Load the coordinate data into the temp table: ' + coordTempTable)
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(),coordTempTable,
coordBCPFile)
print('bcp cmd = %s' % bcpCmd)
sys.stdout.flush()
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(1)
return
def init():
global diagFile, errorFile, inputFile, errorFileName, diagFileName
global strainFile, markerFile, accFile, annotFile
global noteFile
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', encoding="latin-1")
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)
try:
markerFile = open(markerFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % markerFileName)
try:
accFile = open(accFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % accFileName)
try:
noteFile = open(noteFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteFileName)
try:
annotFile = open(annotFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName)
# 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 loadTempTable():
global annot, updatedBy
print('Create a bcp file from the input file')
sys.stdout.flush()
#
# Read each record from the input file, perform validation checks and
# write them to a bcp file.
#
line = fpInput.readline()
updatedBy = re.split(TAB, line[:-1])[6]
count = 1
while line:
tokens = re.split(TAB, line[:-1])
termID = tokens[0]
mgiID = tokens[1]
jNum = tokens[2]
evidCode = tokens[3]
inferFrom = tokens[4]
qual = tokens[5]
if qual == None:
qual = ''
editor = tokens[6]
date = tokens[7]
if date == None:
date = ''
notes = tokens[8]
if notes == None:
notes = ''
ldb = '' # not used for mcvload
termIDExists = len(re.findall('[a-zA-Z0-9]', termID))
mgiIDExists = len(re.findall('[a-zA-Z0-9]', mgiID))
jNumExists = len(re.findall('[a-zA-Z0-9]', jNum))
evidCodeExists = len(re.findall('[a-zA-Z0-9]', evidCode))
inferFromExists = len(re.findall('[a-zA-Z0-9]', inferFrom))
qualExists = len(re.findall('[a-zA-Z0-9]', qual))
editorExists = len(re.findall('[a-zA-Z0-9]', editor))
dateExists = len(re.findall('[a-zA-Z0-9]', date))
#
# Special Case
# If there is only an mgiID this is ok, it means
# curator intends to delete all annotations
#
if termIDExists == 0 and jNumExists == 0 and \
evidCodeExists == 0 and inferFromExists == 0 \
and editorExists == 0 and mgiIDExists > 0:
# check the MGI ID for format
if re.match('MGI:[0-9]+', mgiID) == None:
print('Invalid MGI ID (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
# write out to the bcp file:
fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \
TAB + editor + NL)
# add to the annotation dictionary so it gets written to the
# annotation file
annotList = [termID, mgiID, jNum, evidCode, inferFrom, qual, \
editor, date, notes, ldb]
if mgiID not in annot:
annot[mgiID] = []
annot[mgiID].append(annotList)
# get next input line and continue
line = fpInput.readline()
continue
# There must be a term ID in proper format
# SO:nnnnnnn or MCV:nnnnnnn
if termIDExists == 0:
print('Missing Term ID (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('MCV:[0-9]+',termID) == None and \
re.match('SO:[0-9]+',termID) == None:
print('Invalid Term ID (line ' + str(count) + ')' + ' ' + \
termID)
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an MGI ID in proper format (MGI:nnnnnnn).
#
if mgiIDExists == 0:
print('Missing MGI ID (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('MGI:[0-9]+', mgiID) == None:
print('Invalid MGI ID (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an J Number in proper format (J:nnnnnnn).
#
if jNumExists == 0:
print('Missing J Number (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('J:[0-9]+', jNum) == None:
print('Invalid J Number (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an evidence code
#
if evidCodeExists == 0:
print('Missing Evidence Code(line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an editor login
#
if editorExists == 0:
print('Missing Editor login (line ' + str(count) + ')')
fpBCP.close()
closeFiles()
sys.exit(1)
fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \
TAB + editor + NL)
#
# Maintain a dictionary of the MGI IDs that are in the input file.
# The key for each entry is the MGI ID and the value is a list of
# the annotation attributes for that the MGI ID.
#
if mgiID != '':
annotList = [termID, mgiID, jNum, evidCode, inferFrom, \
qual, editor, date, notes, ldb]
if mgiID not in annot:
annot[mgiID] = []
annot[mgiID].append(annotList)
line = fpInput.readline()
count += 1
#
# Close the bcp file.
#
fpBCP.close()
#
# Load the input data into the temp table.
#
print('Load the input data into the temp table: ' + tempTable)
sys.stdout.flush()
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable,
bcpFile)
rc = os.system(bcpCmd)
if rc != 0:
closeFiles()
sys.exit(1)
def initialize():
# Purpose: open file descriptors; write timestamps to log files
# Returns: 1 if error, else 0
# Assumes: Nothing
# Effects: Sets global variables, exits if a file can't be opened,
# creates files in the file system
global fpDiagFile, fpErrorFile, fpInputFile, errorFileName, diagFileName
global fpAlleleFile, fpMutationFile, fpRefFile
global fpAccFile, fpNoteFile, fpAnnotFile
global fpNewAlleleRptFile
db.useOneConnection(1)
#db.set_sqlUser(user)
#db.set_sqlPasswordFromFile(passwordFileName)
head, tail = os.path.split(inputFileName)
diagFileName = outputDir + '/' + tail + '.diagnostics'
errorFileName = outputDir + '/' + tail + '.error'
try:
fpDiagFile = open(diagFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % diagFileName)
try:
fpErrorFile = open(errorFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % errorFileName)
try:
fpNewAlleleRptFile = open(newAlleleRptFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % newAlleleRptFileName)
try:
fpInputFile = open(inputFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inputFileName)
try:
fpAlleleFile = open(alleleFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % alleleFileName)
try:
fpMutationFile = open(mutationFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % mutationFileName)
try:
fpRefFile = open(refFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % refFileName)
try:
fpAccFile = open(accFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % accFileName)
try:
fpNoteFile = open(noteFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % noteFileName)
try:
fpAnnotFile = open(annotFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % annotFileName)
# Log all SQL
db.set_sqlLogFunction(db.sqlLogAll)
fpDiagFile.write('Start Date/Time: %s\n' % (mgi_utils.date()))
fpDiagFile.write('Server: %s\n' % (db.get_sqlServer()))
fpDiagFile.write('Database: %s\n' % (db.get_sqlDatabase()))
fpErrorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
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 inputFile, diagFile, errorFile, errorFileName, diagFileName
global refFileName, refFile
global mgiTypeKey
global refAssocKey, createdByKey
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'
refFileName = tail + '.MGI_Reference_Assoc.bcp'
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:
refFile = open(refFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % refFileName)
# 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('Object Type: %s\n' % (mgiType))
diagFile.write('Input File: %s\n' % (inputFileName))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
mgiTypeKey = loadlib.verifyMGIType(mgiType, 0, errorFile)
createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
def bcpFiles():
'''
# requires:
#
# effects:
# BCPs the data into the database
#
# returns:
# nothing
#
'''
db.commit()
strainFile.flush()
markerFile.flush()
accFile.flush()
annotFile.flush()
noteFile.flush()
bcpCommand = os.environ['PG_DBUTILS'] + '/bin/bcpin.csh'
currentDir = os.getcwd()
bcp1 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'PRB_Strain', currentDir, strainFileName)
bcp2 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'PRB_Strain_Marker', currentDir, markerFileName)
bcp3 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'ACC_Accession', currentDir, accFileName)
bcp4 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'VOC_Annot', currentDir, annotFileName)
bcp5 = '%s %s %s %s %s %s "|" "\\n" mgd' % \
(bcpCommand, db.get_sqlServer(), db.get_sqlDatabase(), 'MGI_Note', currentDir, noteFileName)
diagFile.write('%s\n' % bcp1)
diagFile.write('%s\n' % bcp2)
diagFile.write('%s\n' % bcp3)
diagFile.write('%s\n' % bcp4)
diagFile.write('%s\n' % bcp5)
os.system(bcp1)
os.system(bcp2)
os.system(bcp3)
os.system(bcp4)
os.system(bcp5)
# update the AccessionMax value
db.sql('select * from ACC_setMax (%d)' % (lineNum), None)
db.commit()
# update prb_strain_seq auto-sequence
db.sql(''' select setval('prb_strain_seq', (select max(_Strain_key) from PRB_Strain)) ''', None)
db.commit()
# update prb_strain_marker_seq auto-sequence
db.sql(''' select setval('prb_strain_marker_seq', (select max(_StrainMarker_key) from PRB_Strain_Marker)) ''', None)
db.commit()
# update voc_annot_seq auto-sequence
db.sql(''' select setval('voc_annot_seq', (select max(_Annot_key) from VOC_Annot)) ''', None)
db.commit()
# update mgi_note_seq auto-sequence
db.sql(''' select setval('mgi_note_seq', (select max(_Note_key) from MGI_Note)) ''', None)
db.commit()
def loadTempTable ():
global annot, updatedBy
print 'Create a bcp file from the input file'
sys.stdout.flush()
#
# Read each record from the input file, perform validation checks and
# write them to a bcp file.
#
line = fpInput.readline()
updatedBy = re.split(TAB, line[:-1])[6]
count = 1
while line:
tokens = re.split(TAB, line[:-1])
termID = tokens[0]
mgiID = tokens[1]
jNum = tokens[2]
evidCode = tokens[3]
inferFrom = tokens[4]
qual = tokens[5]
if qual == None:
qual = ''
editor = tokens[6]
date = tokens[7]
if date == None:
date = ''
notes = tokens[8]
if notes == None:
notes = ''
ldb = '' # not used for mcvload
termIDExists = len(re.findall('[a-zA-Z0-9]',termID))
mgiIDExists = len(re.findall('[a-zA-Z0-9]',mgiID))
jNumExists = len(re.findall('[a-zA-Z0-9]',jNum))
evidCodeExists = len(re.findall('[a-zA-Z0-9]',evidCode))
inferFromExists = len(re.findall('[a-zA-Z0-9]',inferFrom))
qualExists = len(re.findall('[a-zA-Z0-9]',qual))
editorExists = len(re.findall('[a-zA-Z0-9]',editor))
dateExists = len(re.findall('[a-zA-Z0-9]',date))
#
# Special Case
# If there is only an mgiID this is ok, it means
# curator intends to delete all annotations
#
if termIDExists == 0 and jNumExists == 0 and \
evidCodeExists == 0 and inferFromExists == 0 \
and editorExists == 0 and mgiIDExists > 0:
# check the MGI ID for format
if re.match('MGI:[0-9]+',mgiID) == None:
print 'Invalid MGI ID (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
# write out to the bcp file:
fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \
TAB + editor + NL)
# add to the annotation dictionary so it gets written to the
# annotation file
annotList = [termID, mgiID, jNum, evidCode, inferFrom, qual, \
editor, date, notes, ldb]
if not annot.has_key(mgiID):
annot[mgiID] = []
annot[mgiID].append(annotList)
# get next input line and continue
line = fpInput.readline()
continue
# There must be a term ID in proper format
# SO:nnnnnnn or MCV:nnnnnnn
if termIDExists == 0:
print 'Missing Term ID (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('MCV:[0-9]+',termID) == None and \
re.match('SO:[0-9]+',termID) == None:
print 'Invalid Term ID (line ' + str(count) + ')' + ' ' + \
termID
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an MGI ID in proper format (MGI:nnnnnnn).
#
if mgiIDExists == 0:
print 'Missing MGI ID (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('MGI:[0-9]+',mgiID) == None:
print 'Invalid MGI ID (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an J Number in proper format (J:nnnnnnn).
#
if jNumExists == 0:
print 'Missing J Number (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
else:
if re.match('J:[0-9]+',jNum) == None:
print 'Invalid J Number (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an evidence code
#
if evidCodeExists == 0:
print 'Missing Evidence Code(line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
#
# There must be an editor login
#
if editorExists == 0:
print 'Missing Editor login (line ' + str(count) + ')'
fpBCP.close()
closeFiles()
sys.exit(1)
fpBCP.write(termID + TAB + mgiID + TAB + jNum + TAB + evidCode + \
TAB + editor + NL)
#
# Maintain a dictionary of the MGI IDs that are in the input file.
# The key for each entry is the MGI ID and the value is a list of
# the annotation attributes for that the MGI ID.
#
if mgiID != '':
annotList = [termID, mgiID, jNum, evidCode, inferFrom, \
qual, editor, date, notes, ldb]
if not annot.has_key(mgiID):
annot[mgiID] = []
annot[mgiID].append(annotList)
line = fpInput.readline()
count += 1
#
# Close the bcp file.
#
fpBCP.close()
#
# Load the input data into the temp table.
#
print 'Load the input data into the temp table: ' + tempTable
sys.stdout.flush()
bcpCmd = '%s %s %s %s "/" %s "\\t" "\\n" mgd' % \
(BCP_COMMAND, db.get_sqlServer(), db.get_sqlDatabase(),tempTable,
bcpFile)
rc = os.system(bcpCmd)
if rc <> 0:
closeFiles()
sys.exit(1)
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, diagFile, errorFile, errorFileName, diagFileName
global transTypeFile, transFile
global transTypeFileName, transFileName
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
# the default output file names are bases on 'inputFileName'
head, fileName = os.path.split(inputFileName)
# rename 'head'
head = outputFileDir
fdate = mgi_utils.date('%m%d%Y') # current date
diagFileName = head + '/' + fileName + '.' + fdate + '.diagnostics'
print diagFileName
errorFileName = head + '/' + fileName + '.' + fdate + '.error'
print errorFileName
transTypeFileName = head + '/' + fileName + '.' + fdate + '.MGI_TranslationType.bcp'
print transTypeFileName
transFileName = head + '/' + fileName + '.' + fdate + '.MGI_Translation.bcp'
print transFileName
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:
transTypeFile = open(transTypeFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % transTypeFileName)
try:
transFile = open(transFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % transFileName)
# 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()))
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 diagFileName, errorFileName, synFileName
global inputFile, diagFile, errorFile, synFile
global mgiTypeKey, createdByKey, referenceKey
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
head, tail = os.path.split(inputFileName)
diagFileName = logDir + '/' + tail + '.diagnostics'
errorFileName = logDir + '/' + tail + '.error'
synFileName = 'MGI_Synonym.bcp'
print inputFileName
print logDir
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:
synFile = open(outputDir + '/' + synFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % synFileName)
# 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()))
diagFile.write('Object Type: %s\n' % (mgiType))
diagFile.write('Input File: %s\n' % (inputFileName))
errorFile.write('Start Date/Time: %s\n\n' % (mgi_utils.date()))
mgiTypeKey = loadlib.verifyMGIType(mgiType, 0, errorFile)
createdByKey = loadlib.verifyUser(createdBy, 0, errorFile)
# if reference is J:0, then no reference is given
if jnum == 'J:0':
referenceKey = ''
else:
referenceKey = loadlib.verifyReference(jnum, 0, errorFile)
# exit if we can't resolve mgiType, createdBy or jnum
if mgiTypeKey == 0 or \
createdByKey == 0 or \
referenceKey == 0:
exit(1)
if mode == 'reload':
print 'mode is: %s, deleting synonyms' % mode
sys.stdout.flush()
db.sql('delete from MGI_Synonym ' + \
'where _MGIType_key = %d ' % (mgiTypeKey) + \
'and _CreatedBy_key = %d ' % (createdByKey), None)
def init():
global diagFile, errorFile, errorFileName, diagFileName
global outAccFile, outPrepFile, outAssayFile, outAssayNoteFile
global outGelLaneFile, outGelLaneStFile, outGelRowFile, outGelBandFile
global inPrimerFile, inPrepFile, inAssayFile, inGelLaneFile, inGelBandFile
db.useOneConnection(1)
db.set_sqlUser(user)
db.set_sqlPasswordFromFile(passwordFileName)
fdate = mgi_utils.date('%m%d%Y') # current date
diagFileName = sys.argv[0] + '.' + fdate + '.diagnostics'
errorFileName = sys.argv[0] + '.' + 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)
# Input Files
try:
inPrepFile = open(inPrepFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inPrepFileName)
try:
inAssayFile = open(inAssayFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inAssayFileName)
try:
inGelLaneFile = open(inGelLaneFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inGelLaneFileName)
try:
inGelBandFile = open(inGelBandFileName, 'r')
except:
exit(1, 'Could not open file %s\n' % inGelBandFileName)
# Output Files
try:
outPrepFile = open(outPrepFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outPrepFileName)
try:
outAssayFile = open(outAssayFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outAssayFileName)
try:
outAssayNoteFile = open(outAssayNoteFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outAssayNoteFileName)
try:
outGelLaneFile = open(outGelLaneFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outGelLaneFileName)
try:
outGelLaneStFile = open(outGelLaneStFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outGelLaneStFileName)
try:
outGelRowFile = open(outGelRowFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outGelRowFileName)
try:
outGelBandFile = open(outGelBandFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outGelBandFileName)
try:
outAccFile = open(outAccFileName, 'w')
except:
exit(1, 'Could not open file %s\n' % outAccFileName)
# 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
