# Copyright (C) University of Tennessee Health Science Center, Memphis, TN. # # This program is free software: you can redistribute it and/or modify it # under the terms of the GNU Affero General Public License # as published by the Free Software Foundation, either version 3 of the # License, or (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. # See the GNU Affero General Public License for more details. # # This program is available from Source Forge: at GeneNetwork Project # (sourceforge.net/projects/genenetwork/). # # Contact Drs. Robert W. Williams and Xiaodong Zhou (2010) # at rwilliams@uthsc.edu and xzhou15@uthsc.edu # # # # This module is used by GeneNetwork project (www.genenetwork.org) # # Created by GeneNetwork Core Team 2010/08/10 # # Last updated by GeneNetwork Core Team 2011/04/20 #DataUpdatePage.py # #Classes: #DataUpagePage #-KA import string from htmlgen import HTMLgen2 as HT import os import time from base.webqtlTrait import webqtlTrait from base.templatePage import templatePage from base import webqtlConfig from utility import webqtlUtil from dbFunction import webqtlDatabaseFunction ######################################### # Update Trait ######################################### class DataUpdatePage(templatePage): def __init__(self, fd): templatePage.__init__(self, fd) if not self.updMysql(): return if not fd.genotype: fd.readGenotype() fd.strainlist = fd.f1list + fd.strainlist fd.readData() self.formdata = fd.formdata self.dict['title'] = 'Data Updating' try: thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor) thisTrait.retrieveInfo() except: heading = "Updating Database" detail = ["The trait doesn't exist."] self.error(heading=heading,detail=detail,error="Error") return if webqtlConfig.USERDICT[self.privilege] >= webqtlConfig.USERDICT['user']: pass else: heading = "Updating Database" detail = ["You don't have the permission to modify this trait"] self.error(heading=heading,detail=detail,error="Error") return status = self.formdata.getvalue('curStatus') if status == 'updateCheck': #XZhou: Check the change self.updateCheckPage(fd, thisTrait) elif status == 'updateResult': #XZhou: make the changes to database self.updateResultPage(fd, thisTrait) else: #XZhou: show info retrieved from database self.dispTraitPage(fd, thisTrait) def dispTraitPage(self, fd, thisTrait): form = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), name='dataInput',submit=HT.Input(type='hidden')) #XZhou: This is to show trait info. recordInfoTable = HT.TableLite(border=0, cellspacing=1, cellpadding=5,align="left") for field in thisTrait.db.disfield: fieldValue = getattr(thisTrait, field) if not fieldValue: fieldValue = "" #fields to be ignored if field in ("chipid", "genbankid"): continue elif field == "comments": if fieldValue: comments = string.split(fieldValue, '\n') title0 = HT.Paragraph("Update History: ", Class="subtitle") form.append(title0) history = HT.Blockquote() for item in comments: if item: history.append(item, HT.BR()) form.append(history) continue else: pass if field == 'name' or field == 'units': form.append(HT.Input(type="hidden",name=field,value=fieldValue)) if field == 'name': inputBox = HT.Strong(fieldValue) else: continue elif field == 'pubmed_id': inputBox = HT.Span(HT.Input(type="text",name=field,size=60, maxlength=255,value=fieldValue)) warning = HT.Paragraph(Class="fs11 cr") warning.append('1. Please enter only the PubMed ID integer value into the field above.', HT.BR(), ' Don\'t enter', ' other characters.', HT.BR()) warning.append('2. If you modify an existing PubMed ID, changes will affect other records with', HT.BR(), \ ' the same ID but will NOT affect the phenotype description or trait data.', HT.BR()) warning.append('3. If your delete an existing PubMed ID, this will not affect any other traits,', HT.BR(), \ ' including those with the same PubMed ID.', HT.BR()) warning.append('4. If you enter publication data for a PubMed ID that is already in the database,', HT.BR(), \ ' then all fields except Phenotype and Trait Data will be ignored.') inputBox.append(warning) elif field == 'pre_publication_description' or field == 'post_publication_description' or field == 'original_description' or field == 'owner' or field == 'abstract' or field == 'blatseq' or field == 'targetseq' or field == 'description' or field == 'authors' or field == 'sequence' or field == 'alias' or field == 'probe_target_description': inputBox = HT.Textarea(name=field, cols=60, rows=4,text=fieldValue) elif field == 'post_publication_abbreviation' or field == 'pre_publication_abbreviation': inputBox = HT.Input(type="text",name=field,size=60, maxlength=30,value=fieldValue) elif field == 'geneid': inputBox = HT.Input(type="text",name=field,size=60, maxlength=255,value=fieldValue) recordInfoTable.append(HT.TR( HT.TD("%s :" % webqtlUtil.formatField(field), Class="fs12 fwb ff1", align="right"), HT.TD(width=20),HT.TD(inputBox))) #XZ: homologene is not in thisTrait.db.disfield, so have to do in this way field = 'homologeneid' inputBox = HT.Input(type="text",name=field,size=60, maxlength=255,value=thisTrait.homologeneid) else: inputBox = HT.Input(type="text",name=field,size=60, maxlength=255,value=fieldValue) #XZ: For existing non-confidential phenotype trait, pre_publication_description and pre_publication_abbreviation are not shown to anybody except submitter or admistrator to prevent the trait being set to confidential one. if thisTrait.db.type == 'Publish' and field == 'pre_publication_description' or field == 'pre_publication_abbreviation': if not thisTrait.confidential and webqtlConfig.USERDICT[self.privilege] < webqtlConfig.USERDICT['admin'] and self.userName != thisTrait.submitter: continue #XZ and Rob, April 20, 2011: This is to add field and inputBox to table. Note that the change of format to each field(Capitalize) by webqtlUtil.formatField function. recordInfoTable.append(HT.TR( HT.TD("%s :" % webqtlUtil.formatField(field), Class="fs12 fwb ff1", align="right", valign="top"), HT.TD(width=5),HT.TD(inputBox))) #XZhou: This is to show trait data. recordDataTable = HT.Text('Trait data updating is disabled') if thisTrait.db.type == 'Publish': thisTrait.retrieveData() recordDataTable = HT.TableLite(border=0, width = "90%",cellspacing=2, cellpadding=2) recordDataTable.append(HT.TR(HT.TD('Strain Name',Class="fs12 ffl fwb",align="Center"), HT.TD('TraitData',Class="fs12 ffl fwb",align="Center"), HT.TD('SE',Class="fs12 ffl fwb",align="Center"), HT.TD('N Per Strain',Class="fs12 ffl fwb",align="Center"), HT.TD('Strain Name',Class="fs12 ffl fwb",align="Center"), HT.TD('TraitData',Class="fs12 ffl fwb",align="Center"), HT.TD('SE',Class="fs12 ffl fwb",align="Center"), HT.TD('N Per Strain',Class="fs12 ffl fwb",align="Center"))) tempTR = HT.TR(align="Center") for i, strainName in enumerate(fd.strainlist): if thisTrait.data.has_key(strainName): tdata = thisTrait.data[strainName] traitVal, traitVar, traitNP = tdata.val, tdata.var, tdata.N else: traitVal, traitVar, traitNP = None, None, None if traitVal != None: traitVal = "%2.3f" % traitVal else: traitVal = 'x' if traitVar != None: traitVar = "%2.3f" % traitVar else: traitVar = 'x' if traitNP != None: traitNP = "%d" % traitNP else: traitNP = 'x' tempTR.append(HT.TD(HT.Paragraph(strainName),align='CENTER'), HT.TD(HT.Input(name=strainName, size=8, maxlength=8, value=traitVal),align='CENTER'), HT.TD(HT.Input(name='V'+strainName, size=8, maxlength=8, value=traitVar),align='CENTER'), HT.TD(HT.Input(name='N'+strainName, size=8, maxlength=8, value=traitNP),align='CENTER')) if i % 2: recordDataTable.append(tempTR) tempTR = HT.TR(align="Center") if (i+1) % 2: tempTR.append(HT.TD('')) tempTR.append(HT.TD('')) tempTR.append(HT.TD('')) recordDataTable.append(tempTR) updateButton = HT.Input(type='submit',name='submit', value='Submit Change',Class="button") resetButton = HT.Input(type='reset',Class="button") hddn = {'fullname':str(thisTrait), 'FormID':'updateRecord', 'curStatus':'updateCheck', 'RISet':fd.RISet, "incparentsf1":1} for key in hddn.keys(): form.append(HT.Input(name=key, value=hddn[key], type='hidden')) ############################# TD_LR = HT.TD(valign="top",colspan=2,bgcolor="#eeeeee") containerTable = HT.TableLite(border=0, width = "90%",cellspacing=0, cellpadding=0) mainTitle = HT.Paragraph("Update Info and Data", Class="title") title1 = HT.Paragraph("Trait Information: ", Class="subtitle") title2 = HT.Paragraph("Trait Data:", Class="subtitle") containerTable.append(HT.TR(HT.TD(title1)), HT.TR(HT.TD(HT.BR(),updateButton,resetButton,HT.BR(),HT.BR())), HT.TR(HT.TD(recordInfoTable)), HT.TR(HT.TD(title2)), HT.TR(HT.TD(HT.BR(),recordDataTable, HT.BR(), HT.BR())), HT.TR(HT.TD(updateButton,resetButton))) form.append(containerTable) TD_LR.append(mainTitle, form) self.dict['body'] = TD_LR def updateCheckPage(self, fd, thisTrait): form = HT.Form(cgi= os.path.join(webqtlConfig.CGIDIR, webqtlConfig.SCRIPTFILE), name='updateCheck',submit=HT.Input(type='hidden')) hddn = {'fullname':str(thisTrait), 'FormID':'updateRecord', 'curStatus':'updateResult', 'RISet':fd.RISet, "incparentsf1":1} recordInfoTable = HT.TableLite(border=0, cellspacing=1, cellpadding=5,align="left",width="90%") infoChanges = [] for field in thisTrait.db.disfield: #fields to be ignored #XZ: The stupid htmlgen can not set readonly for input and textarea. This is the only way I can prevent displayed items such as 'original_description', 'submitter' being changed. if field in ("chipid", "genbankid", "comments", "original_description", "submitter"): continue oldValue = getattr(thisTrait, field) if not oldValue: oldValue = '' oldValue = str(oldValue) modifiedValue = self.formdata.getvalue(field) if not modifiedValue: modifiedValue = "" modifiedValue.strip() oldValue.strip() if oldValue == modifiedValue: form.append(HT.Input(type="hidden",name=field,value=oldValue)) continue oldValue = HT.Paragraph(oldValue, Class="cr") warning = '' if field == 'PubMed_ID': if modifiedValue != "": try: modifiedValue = int(modifiedValue) except: continue #whether new PMID already exists newPMIDExist = None if modifiedValue: self.cursor.execute("SelecT Id from Publication where PubMed_ID = %d" % modifiedValue) results = self.cursor.fetchall() if results: newPMIDExist = results[0][0] if newPMIDExist: warning = HT.Paragraph(Class="fs11 cr") warning.append('This new PubMed_ID already exists in our database. If you still want to change to this very PubMed_ID, the publication information (title, author, journal, etc.) will be replaced by those linked to this new PubMed_ID. That means, all the fields below (if any, except phenotype info and trait value) will be ignored.') infoChanges.append(field) inputBox = HT.Textarea(name=field, cols=50, rows=3,text=modifiedValue, onChange = "Javascript:this.form.curStatus.value='updateCheck';") recordInfoTable.append( HT.TR(HT.TD("%s :" % webqtlUtil.formatField(field), Class="fs12 fwb ff1", colspan = 3, valign="top")), HT.TR(HT.TD(oldValue, valign="top"),HT.TD(width=20),HT.TD( inputBox, warning))) #XZ: homologeneid is not in thisTrait.db.disfield if thisTrait.db.type == "ProbeSet": field = 'homologeneid' oldValue = getattr(thisTrait, field) if not oldValue: oldValue = '' oldValue = str(oldValue) modifiedValue = self.formdata.getvalue(field) if not modifiedValue: modifiedValue = "" modifiedValue.strip() oldValue.strip() if oldValue == modifiedValue: form.append(HT.Input(type="hidden",name=field,value=oldValue)) else: oldValue = HT.Paragraph(oldValue, Class="cr") warning = '' infoChanges.append(field) inputBox = HT.Textarea(name=field, cols=50, rows=3,text=modifiedValue, onChange = "Javascript:this.form.curStatus.value='updateCheck';") recordInfoTable.append( HT.TR(HT.TD("%s :" % webqtlUtil.formatField(field), Class="fs12 fwb ff1", colspan = 3, valign="top")), HT.TR(HT.TD(oldValue, valign="top"),HT.TD(width=20),HT.TD( inputBox, warning))) if infoChanges == []: recordInfoTable = "" recordInfoChange = HT.Blockquote('No change has been made.') else: hddn['modifiedField'] = string.join(infoChanges, '::') recordInfoChange = '' recordDataChange = HT.Blockquote('Trait data updating is disabled') recordDataTable = "" modifiedVals = [] modifiedVars = [] modifiedNps = [] numDataChanges = 0 if thisTrait.db.type == 'Publish': thisTrait.retrieveData() recordDataTable = HT.TableLite(border=0, width = "90%",cellspacing=2, cellpadding=2) for i, strainName in enumerate(fd.strainlist): if thisTrait.data.has_key(strainName): tdata = thisTrait.data[strainName] traitVal, traitVar, traitNP = tdata.val, tdata.var, tdata.N else: traitVal, traitVar, traitNP = None, None, None if traitVal != None: traitVal = "%2.3f" % traitVal else: traitVal = 'x' if traitVar != None: traitVar = "%2.3f" % traitVar else: traitVar = 'x' if traitNP != None: traitNP = "%d" % traitNP else: traitNP = 'x' try: modifiedVal = "%2.3f" % fd.allTraitData[strainName].val except: modifiedVal = 'x' try: modifiedVar = "%2.3f" % fd.allTraitData[strainName].var except: modifiedVar = 'x' try: modifiedNp = "%d" % fd.allTraitData[strainName].N except: modifiedNp = 'x' if modifiedVal != traitVal: recordDataTable.append(HT.TR(HT.TD(HT.Paragraph(strainName + " Value")), HT.TD(HT.Paragraph(traitVal, Class="cr")), HT.TD(HT.Input(name=strainName, size=8, maxlength=8, value=modifiedVal, onChange = "Javascript:this.form.curStatus.value='updateCheck';")))) numDataChanges += 1 modifiedVals.append(modifiedVal) else: form.append(HT.Input(type="hidden",name=strainName,value=traitVal)) modifiedVals.append(traitVal) if modifiedVar != traitVar: recordDataTable.append(HT.TR(HT.TD(HT.Paragraph(strainName + " SE")), HT.TD(HT.Paragraph(traitVar, Class="cr")), HT.TD(HT.Input(name='V'+strainName, size=8, maxlength=8, value=modifiedVar, onChange = "Javascript:this.form.curStatus.value='updateCheck';")))) numDataChanges += 1 modifiedVars.append(modifiedVar) else: form.append(HT.Input(type="hidden",name='V'+strainName,value=traitVar)) modifiedVars.append(traitVar) if modifiedNp != traitNP: recordDataTable.append(HT.TR(HT.TD(HT.Paragraph(strainName + " N Per Strain")), HT.TD(HT.Paragraph(traitNP, Class="cr")), HT.TD(HT.Input(name='N'+strainName, size=8, maxlength=8, value=modifiedNp, onChange = "Javascript:this.form.curStatus.value='updateCheck';")))) numDataChanges += 1 modifiedNps.append(modifiedNp) else: form.append(HT.Input(type="hidden",name='N'+strainName,value=traitNP)) modifiedNps.append(traitNP) if numDataChanges == 0: recordDataChange = HT.Blockquote('No change has been made.') recordDataTable = "" else: hddn['modifiedDataField'] = 1 recordDataChange = "" #if numDataChanges: # hddn['val'] = string.join(modifiedVals, ',') # hddn['var'] = string.join(modifiedVars, ',') # hddn['np'] = string.join(modifiedNps, ',') for key in hddn.keys(): form.append(HT.Input(name=key, value=hddn[key], type='hidden')) ############################# TD_LR = HT.TD(valign="top",colspan=2,bgcolor="#eeeeee", height=200) mainTitle = HT.Paragraph("Update Info and Data", Class="title") title1 = HT.Paragraph("Trait Information:", Class="subtitle") title2 = HT.Paragraph("Trait Data:", Class="subtitle") if numDataChanges or infoChanges: recordChange = HT.Blockquote('The table below lists all the changes made. The texts in red are the original information stored on the server, the texts to the right are the modified version. ') updateButton = HT.Input(type='submit',name='submit', value='Update Data',Class="button") resetButton = HT.Input(type='reset',Class="button") form.append(title1, HT.Center(updateButton,resetButton), recordInfoChange, recordInfoTable,title2, recordDataChange, HT.Center(recordDataTable,HT.P(),updateButton,resetButton),HT.P()) TD_LR.append(mainTitle, recordChange, HT.Blockquote(form)) else: recordInfoChange = HT.Blockquote("No change has been made") TD_LR.append(mainTitle, recordInfoChange) self.dict['body'] = TD_LR #self.dict['js1'] = webqtlConfig.resetFieldScript return def updateResultPage(self, fd, thisTrait): comments = [] ctime = time.ctime() ##Start Updating dataID = -1 if thisTrait.db.type == 'Publish': self.cursor.execute("SelecT PublishXRef.InbredSetId, PublishXRef.DataId, PublishXRef.PublicationId, PublishXRef.PhenotypeId, PublishXRef.Sequence from PublishXRef, PublishFreeze where PublishXRef.InbredSetId= PublishFreeze.InbredSetId and PublishFreeze.Name = '%s' and PublishXRef.Id = %s" % (thisTrait.db.name, thisTrait.name)) PInbredSetId, dataID, PublicationId, PhenotypeId, Sequence = self.cursor.fetchall()[0] modifyField = self.formdata.getvalue('modifiedField') ###Modify Trait Informations if modifyField: modifyField = string.split(modifyField, '::') comments += modifyField updateHomologeneid = False if thisTrait.db.type == 'Publish': PhenotypeItemUpdate = [] PhenotypeItemValues = [] PublicationItemUpdate = [] PublicationItemValues = [] for item in modifyField: itemvalue = self.formdata.getvalue(item) #XZ: identify Phenotype items if item in ['pre_publication_description', 'post_publication_description', 'original_description', 'pre_publication_abbreviation', 'post_publication_abbreviation', 'lab_code', 'submitter', 'owner', 'authorized_users', 'units']: if itemvalue != None: #XZ: the problem is that the item value can not be deleted PhenotypeItemUpdate.append('%s=%%s' % item) PhenotypeItemValues.append(itemvalue) continue #XZ: this is important to distinguish Phenotype item and Publication item elif item == "pubmed_id": #Only integer allowed in this field try: itemvalue = int(itemvalue) except: itemvalue = None #whether old PMID exists self.cursor.execute("SelecT PubMed_ID from Publication where Id = %d" % PublicationId) oldPMID = self.cursor.fetchone() if oldPMID: oldPMID = oldPMID[0] #whether new PMID already exists newPMID = None self.cursor.execute("SelecT Id from Publication where PubMed_ID = %d" % itemvalue) newPMID = self.cursor.fetchone() if newPMID: newPMID = newPMID[0] ##the logic is still not very clear here if newPMID: #new PMID in record self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (newPMID, PInbredSetId, PhenotypeId, PublicationId, Sequence)) #no need to update other fields PublicationItemUpdate = [] break elif itemvalue: #have new PMID, but not in record or need to change self.cursor.execute("Update Publication set pubmed_id=%d where Id = %s" % (itemvalue,PublicationId)) else: #no new PMID if oldPMID: #remove a pubmed_id, don't know if this ever gonna happen self.cursor.execute("SelecT max(Id) from Publication") maxId = self.cursor.fetchone()[0] + 1 self.cursor.execute("SelecT * from Publication where Id = %d" % PublicationId) oldRecs = list(self.cursor.fetchone()) oldRecs[0] = maxId oldRecs[1] = None NFields = ['%s'] * len(oldRecs) query = "insert into Publication Values (%s)" % string.join(NFields, ',') self.cursor.execute(query, tuple(oldRecs)) self.cursor.execute("Update PublishXRef set PublicationId = %d where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (maxId, PInbredSetId, PhenotypeId, PublicationId, Sequence)) PublicationId = maxId pass else: pass continue else: pass if itemvalue: PublicationItemUpdate.append('%s=%%s' % item) PublicationItemValues.append(itemvalue) if PhenotypeItemUpdate: updateStr= string.join(PhenotypeItemUpdate,',') query = "Update Phenotype set %s where Id = %s" % (updateStr, PhenotypeId) self.cursor.execute(query,tuple(PhenotypeItemValues)) if PublicationItemUpdate: updateStr= string.join(PublicationItemUpdate,',') query = "Update Publication set %s where Id = %s" % (updateStr, PublicationId) self.cursor.execute(query,tuple(PublicationItemValues)) else: #ProbeSet or Genotype Data itemValues = [] itemUpdate = [] for item in modifyField: itemvalue = self.formdata.getvalue(item) if itemvalue != None: itemvalue = string.strip(itemvalue) else: pass if item == 'homologeneid': updateHomologeneid = True new_homologeneid = 0 if itemvalue and len(itemvalue) > 0: try: new_homologeneid = int(itemvalue) except: heading = "Record Updating Result" detail = ["Can't update database. Homologeneid must be integer!"] self.error(heading=heading,detail=detail,error="Error") return else: itemUpdate.append('%s=%%s' % item) #XZ: Use %% to put a % in the output string itemValues.append(itemvalue) if itemUpdate: updateStr= string.join(itemUpdate,', ') comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime) if thisTrait.db.type == "ProbeSet":#XZ, June 29, 2010: The algorithm is not good. Need to fix it later. if thisTrait.chipid in (2,4): if thisTrait.name[-2:] == '_A': thisTrait.name = string.replace(thisTrait.name, '_A', '') elif thisTrait.name[-2:] == '_B': thisTrait.name = string.replace(thisTrait.name, '_B', '') else: pass query = "Update %s set %s where Name like '%s%%%%'" % (thisTrait.db.type,updateStr,thisTrait.name) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name like '%s%%%%'" % (thisTrait.db.type, comments, thisTrait.name)) elif thisTrait.sequence: query = "Update %s set %s where BlatSeq='%s'" % (thisTrait.db.type,updateStr,thisTrait.sequence) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where BlatSeq='%s'" % (thisTrait.db.type, comments, thisTrait.sequence)) else: query = "Update %s set %s where Name='%s'" % (thisTrait.db.type,updateStr,thisTrait.name) self.cursor.execute(query,tuple(itemValues)) self.cursor.execute("Update %s set comments = CONCAT(comments,'%s') where Name='%s'" % (thisTrait.db.type, comments, thisTrait.name)) else: #XZ: Genotype query = "Update %s set %s where SpeciesId=%s and Name='%s'" % (thisTrait.db.type,updateStr, webqtlDatabaseFunction.retrieveSpeciesId(self.cursor, thisTrait.db.riset), thisTrait.name) self.cursor.execute(query,tuple(itemValues)) if updateHomologeneid: #XZ: to update homologene id must be after updating geneid. #XZ: In one species, one homologeneid can have multiple geneid. One geneid only can have one homologeneid. #XZ: In Homologene table, GeneId is unique. #XZ: Geneid might just being updated. thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor) thisTrait.retrieveInfo() if not thisTrait.geneid: heading = "Record Updating Result" detail = ["There is no geneid associated with this trait. Can't update homologeneid info"] self.error(heading=heading,detail=detail,error="Error") return else: query = """ SELECT Species.TaxonomyId FROM Species, InbredSet WHERE InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id """ % thisTrait.db.riset self.cursor.execute(query) taxonomyId = self.cursor.fetchone()[0] if not new_homologeneid: query = """DELETE FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid self.cursor.execute(query) else: query = """SELECT GeneId FROM Homologene WHERE GeneId=%s""" % thisTrait.geneid self.cursor.execute(query) result = self.cursor.fetchone() if not result: query = """INSERT into Homologene (HomologeneId, GeneId, TaxonomyId) VALUES (%s, %s, %s)""" % (new_homologeneid, thisTrait.geneid, taxonomyId) self.cursor.execute(query) else: query = """UPDATE Homologene SET HomologeneId=%s WHERE GeneId=%s""" % (new_homologeneid, thisTrait.geneid) self.cursor.execute(query) #XZ: It's critical to get lasted info first, then update gene level info across traits by geneid. #XZ: Need to build index on GeneId. Otherwise, it's too slow. if thisTrait.db.type == 'ProbeSet': thisTrait = webqtlTrait(fullname=self.formdata.getvalue('fullname'), cursor=self.cursor) thisTrait.retrieveInfo() if thisTrait.geneid: if 'symbol' in modifyField: if thisTrait.symbol: query = """UPDATE ProbeSet SET Symbol='%s' WHERE GeneId=%s""" % (thisTrait.symbol, thisTrait.geneid) else: query = """UPDATE ProbeSet SET Symbol=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'alias' in modifyField: if thisTrait.alias: query = """UPDATE ProbeSet SET alias='%s' WHERE GeneId=%s""" % (thisTrait.alias, thisTrait.geneid) else: query = """UPDATE ProbeSet SET alias=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'description' in modifyField: if thisTrait.description: #XZ: Attention, we must use "%s" instead of '%s'. Otherwise, to insert 3'UTR will generate error. query = """UPDATE ProbeSet SET description="%s" WHERE GeneId=%s""" % (thisTrait.description, thisTrait.geneid) else: query = """UPDATE ProbeSet SET description=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'strand_gene' in modifyField: if thisTrait.strand_gene: query = """UPDATE ProbeSet SET Strand_Gene='%s' WHERE GeneId=%s""" % (thisTrait.strand_gene, thisTrait.geneid) else: query = """UPDATE ProbeSet SET Strand_Gene=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'unigeneid' in modifyField: if thisTrait.unigeneid: query = """UPDATE ProbeSet SET UniGeneId='%s' WHERE GeneId=%s""" % (thisTrait.unigeneid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET UniGeneId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'refseq_transcriptid' in modifyField: if thisTrait.refseq_transcriptid: query = """UPDATE ProbeSet SET RefSeq_TranscriptId='%s' WHERE GeneId=%s""" % (thisTrait.refseq_transcriptid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET RefSeq_TranscriptId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'genbankid' in modifyField: if thisTrait.genbankid: query = """UPDATE ProbeSet SET GenbankId='%s' WHERE GeneId=%s""" % (thisTrait.genbankid, thisTrait.geneid) else: query = """UPDATE ProbeSet SET GenbankId=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) if 'omim' in modifyField: if thisTrait.omim: query = """UPDATE ProbeSet SET OMIM='%s' WHERE GeneId=%s""" % (thisTrait.omim, thisTrait.geneid) else: query = """UPDATE ProbeSet SET OMIM=NULL WHERE GeneId=%s""" % (thisTrait.geneid) self.cursor.execute(query) ###Modify Trait Data if thisTrait.db.type == 'Publish' and dataID > 0 and fd.formdata.getvalue("modifiedDataField"): StrainIds = [] for item in fd.strainlist: self.cursor.execute('SelecT Id from Strain where Name = "%s"' % item) StrainId = self.cursor.fetchone() if not StrainId: raise ValueError else: StrainIds.append(StrainId[0]) comments.append('Trait Value') #XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE self.cursor.execute('delete from PublishData where Id = %d' % dataID) self.cursor.execute('delete from PublishSE where DataId = %d' % dataID) self.cursor.execute('delete from NStrain where DataId = %d' % dataID) for i, strain in enumerate(fd.strainlist): sId = StrainIds[i] if fd.allTraitData.has_key(strain): tdata = fd.allTraitData[strain] _val, _var, _N = tdata.val, tdata.var, tdata.N if _val != None: #XZ, 03/05/2009: Xiaodong changed Data to PublishData, SE to PublishSE self.cursor.execute('insert into PublishData values(%d, %d, %s)' % (dataID, sId, _val)) if _var != None: self.cursor.execute('insert into PublishSE values(%d, %d, %s)' % (dataID, sId, _var)) if _N != None: self.cursor.execute('insert into NStrain values(%d, %d, %s)' % (dataID, sId, _N)) else: pass #end for else: pass TD_LR = HT.TD(valign="top", bgcolor="#eeeeee",height=200,width="100%") main_title = HT.Paragraph(" Record Updating Result", Class="title") TD_LR.append(main_title,HT.Blockquote('Successfully updated record %s in database ' % thisTrait.name, thisTrait.db.genHTML(), '.')) if thisTrait.db.type == 'Publish': comments = "%s modified %s at %s\n" % (self.userName, string.join(comments, ', '), ctime) self.cursor.execute("Update PublishXRef set comments = CONCAT(comments,'%s') where InbredSetId=%d and PhenotypeId=%d and PublicationId=%d and Sequence=%d" % (comments, PInbredSetId, PhenotypeId, PublicationId, Sequence)) if 0: heading = "Record Updating Result" detail = ["Can't update database. The server may be down at this time or you don't have the permission"] self.error(heading=heading,detail=detail,error="Error") return self.dict['body'] = str(TD_LR)