summaryrefslogblamecommitdiffstats
path: root/Under-Testing/Server-Code-New/DbClass.py
blob: d937eb1ef85370a5e02ab844b3513a0884b8bc03 (plain) (tree)
1
2
3
4
5
6
7
8






                                                                
                               












                                          
                                       






















































































































































                                                                                                                                                                                       






                                                                                                                                            








































































































































































































































































                                                                                                                                                                                                                                                                                                                                                                                                          
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