From b99193ba68f947802245a288940e54904ecbee61 Mon Sep 17 00:00:00 2001 From: Triatmoko Date: Fri, 18 Nov 2011 13:23:49 +0100 Subject: final version --- Under-Testing/Server-Code-New/DbClass.py | 445 ------------------------------- 1 file changed, 445 deletions(-) delete mode 100644 Under-Testing/Server-Code-New/DbClass.py (limited to 'Under-Testing/Server-Code-New/DbClass.py') diff --git a/Under-Testing/Server-Code-New/DbClass.py b/Under-Testing/Server-Code-New/DbClass.py deleted file mode 100644 index d937eb1..0000000 --- a/Under-Testing/Server-Code-New/DbClass.py +++ /dev/null @@ -1,445 +0,0 @@ -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 - - -- cgit v1.2.3-55-g7522