import MySQLdb import string class DBMySQLConnection: def __init__(self): #initialize at the start all the user parameters self.usern = "root" self.passw = "" self.host = "localhost" self.db = "gsmselftesting" self.connectionCreated = 0 self.tasksList = list() self.callerLists = list() self.errCode = None self.deviceUpdate = None self.lockSet = 0 global debugMode debugMode = 0 def connectDB(self): try: #try to connect self.datBaseConn=MySQLdb.connect(self.host,self.usern, self.passw,self.db) self.datBaseConn.paramstyle = 'format' self.cur = self.datBaseConn.cursor() #make the cursor, used for sending queries self.connectionCreated = 1 #use it as an indicator that the connection was created return 1 except MySQLdb.Error, e: #if we have an error then try to catch it error=str(e) if error[1:5] == '1045': #wrong username or password return 0 elif error[1:5] == '2002': #can't connect to mysql, mysql shutdown or wrong host return 2 else: if debugMode == 1: print error return 3 def closeDBConn(self): #close the connection to the database here if self.connectionCreated == 1: try: #close the cursor and then the connection to the DB self.cur.close() self.datBaseConn.close() return 1 except MySQLdb.Error, e: #in case of an error if debugMode == 1: error = str(e) print error return 3 else: #I never really had a connection return 0 def anyTasksToDo(self): #see are there any jobs to be executed and make a list out of it if self.connectionCreated == 1: try: self.cur.execute("SELECT * FROM TempTaskTable") output = self.cur.fetchall() #get the mysql response #parse the output from the mysql by creating a list #with lists where each attribue(column) gets independent #element of the list for record in output: columns = list() for entry in record: columns.append(str(entry)) #columns.append(str(0)) self.tasksList.append(columns) if not self.tasksList: return 0 else: return 1 except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 else: return 0 def cleanTasksList(self): if self.connectionCreated == 1: del self.tasksList[:] return 1 else: return 0 def deviceAddress(self,deviceName): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT `portName`,`number`, `username`, `password`, `server` FROM DeviceAddressTable where `deviceName`=%s", deviceName) output = self.cur.fetchall() deviceAddr = '' for record in output: columns = list() for entry in record: columns.append(str(entry)) deviceAddr = columns return deviceAddr except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 #some error happened else: return 0 #I am not connected def deviceList(self): # taking all device list and put it in the list deviceLists = list() if self.connectionCreated == 1: try: tuple = self.cur.execute("SELECT `deviceName` FROM DeviceAddressTable") output = self.cur.fetchall() for record in output: columns = list() for entry in record: columns.append(str(entry)) deviceLists.append(columns) return deviceLists #return deviceAddr except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 #some error happened else: print 'not conn' return 0 #I am not connected def GSMPrefix(self): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT * FROM GSMPrefix") output = self.cur.fetchall() #get the mysql response GSMListPrefix = list() for record in output: columns = list() for entry in record: columns.append(str(entry)) GSMListPrefix.append(columns) return GSMListPrefix except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 #some error happened else: return 0 #I am not connected def updateGSMDevice(self, deviceName, newPortName, newNumber): if self.connectionCreated == 1: try: try: #delete old device portName which assign to this port, to prevent double port address in the table. stmt = "UPDATE DeviceAddressTable SET portName = 'missing' WHERE portName = '"+newPortName+"'" self.cur.execute(stmt) except ValueError: print "Error execute query" stmt = "UPDATE DeviceAddressTable SET portName = '"+ newPortName + "', number = '"+ newNumber+ "' WHERE deviceName = '" + deviceName+ "'" self.cur.execute(stmt) return 1 except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def updatePingResult(self, taskNo, sipServer, sipGate, unisip, gsmBox1, gsmBox2): if self.connectionCreated == 1: try: successful = self.cur.execute("INSERT INTO PingResultTable(taskNo, sipServer, sipGate, unisip, gsmBox1, gsmBox2) VALUES ('%i', '%i','%i', '%i','%i','%i')"%( int(taskNo),int(sipServer), int(sipGate), int(unisip), int(gsmBox1), int(gsmBox2))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 0: return 1 #ping table updated else: return 4 #the taskNo didn't exist except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def deleteTempTask(self, taskID): if self.connectionCreated == 1: try: successful = self.cur.execute("DELETE FROM TempTaskTable WHERE taskID=%i"%(int(taskID))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 1: return 1 #deleted it else: return 4 #that taskID didn't exist or something else except MySQLdb.Error, e: if debugMode == 1: print str(e) return 3 else: return 0 def addResult(self, taskID, result): if self.connectionCreated == 1: try: successful = self.cur.execute("INSERT INTO ResultTable(taskID, result) VALUES ('%i', '%i')"%(int(taskID), int(result))) output = self.cur.fetchone() if debugMode == 1: print output if successful == 1: return 1 #successfully added the result else: return 4 #hmmm except MySQLdb.Error, e: error = str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key if debugMode == 1: print str(e) return 3 else: return 0 def errorCode(self,code): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT description FROM ErrorCodeTable where `respondCode`=%s", code) data = self.cur.fetchone() self.errCode = data[0] return self.errCode except MySQLdb.Error, e: error = str(e) if error[1:5] == '1146': return 2 #the table doesn't exist if debugMode == 1: print str(e) return 3 #some error happened else: return 0 #I am not connected def lockMutex(self, seconds): if self.connectionCreated == 1: try: successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") output = self.cur.fetchone() if output[0] == 1: #resource is free which means software is not started yet successful = self.cur.execute("SELECT GET_LOCK('SoftwareStarted', %i)"%(int(seconds))) output1 = self.cur.fetchone() if output1[0] == 1: #I got the lock now self.lockSet = 1 return 1 elif output1[0] == 0: return 7 #if the attempt timed out (for example, because another client has previously locked the name) else: return 6 # if an error occurred (such as running out of memory or the thread was killed with elif output[0] ==0: return 4 #software is already running and somebody has allocated the mutex else: #means some not so good bug return 5 # if an error occurs (such as an incorrect argument). except MySQLdb.Error, e: error = str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key if debugMode == 1: print str(e) return 3 else: return 0 def maxTaskNo(self): if self.connectionCreated == 1: try: succ = self.cur.execute("SELECT max(taskNo) FROM TaskTable") taskNo = self.cur.fetchone() self.taskNo = taskNo[0] if self.taskNo == None or self.taskNo == '0' or self.taskNo == '' or self.taskNo == 0: self.taskNo = 1 else: self.taskNo = int(self.taskNo) +1 return self.taskNo except MySQLdb.Error, e: error = str(e) message = 'error' if debugMode == 1: print str(e) if error[1:5] == '1062': return message #duplicate entry for the key return message else: return 0 def maxTaskID(self): if self.connectionCreated == 1: try: succ = self.cur.execute("SELECT max(taskID) FROM TaskTable") taskID = self.cur.fetchone() self.taskID = taskID[0] if self.taskID == None or self.taskID == '0' or self.taskID == '' or self.taskID == 0: self.taskID = 1 return self.taskID except MySQLdb.Error, e: error = str(e) message = 'error' if debugMode == 1: print str(e) if error[1:5] == '1062': return message #duplicate entry for the key return message else: return 0 def insertTask(self, taskNo, fromDevice, toDevice): if self.connectionCreated == 1: try: newQuery = ("INSERT INTO `TaskTable` (`taskNo`,`from`,`to`) VALUES ('"+str(taskNo)+"','" +str(fromDevice)+ "', '"+str(toDevice)+"')") successful = self.cur.execute(newQuery) output = self.cur.fetchone() return 1 if debugMode == 1: print output except MySQLdb.Error, e: error = str(e) if debugMode == 1: print str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key return 3 else: return 0 def insertTaskIn2(self, fromDevice, toDevice, taskNo): if self.connectionCreated == 1: try: #we used here a transaction since I want the mysql to execute a few commands and tell me was it successful rather than to execute some and there happens a mistake and one part is updated and the other isn't newQuery = "START TRANSACTION; INSERT INTO `TaskTable` (`taskNo`, `from`, `to`) VALUES ('" + str(taskNo) + "', '" + str(fromDevice) + "', '" + str(toDevice) +"'); SELECT @taskID := LAST_INSERT_ID(); INSERT INTO `TempTaskTable` (`taskID`, `taskNo`, `from`, `to`) VALUES (@taskID, '" + str(taskNo) + "', '" + str(fromDevice) + "', '"+ str(toDevice) + "'); COMMIT;" successful = self.cur.execute(newQuery) output = self.cur.fetchone() while self.cur.nextset() is not None: pass return 1 if debugMode == 1: print output except MySQLdb.Error, e: error = str(e) if debugMode == 1: print str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key return 3 else: return 0 def releaseMutex(self): if self.connectionCreated == 1: try: if self.lockSet == 1: successful = self.cur.execute("SELECT IS_FREE_LOCK('SoftwareStarted')") output = self.cur.fetchone() if output[0] == 1: #the lock seems to be free self.lockSet = 0 return 4 elif output[0] == 0: #try to release the lock successful = self.cur.execute("SELECT RELEASE_LOCK('SoftwareStarted')") output1 = self.cur.fetchone() self.lockSet = 0 if output1[0] == 1: #the lock was released successfully return 1 elif output1[0] == 0: # if the lock was not established by this thread (in which case the lock is not released) return 5 else: # and NULL if the named lock did not exist. The lock does not exist if it was never obtained by a call to GET_LOCK() or if it has previously been released. return 6 else: #some serious problem #and NULL if an error occurs (such as an incorrect argument). return 5 else: return 7 #the lock wasn't set except MySQLdb.Error, e: error = str(e) if error[1:5] == '1062': return 2 #duplicate entry for the key if debugMode == 1: print str(e) return 3 else: return 0