summaryrefslogblamecommitdiffstats
path: root/notFinishedCode/tricode/DbClass.py
blob: 697a2e4295c3c29ca43e12bade56031e9cde53f3 (plain) (tree)





































































































































































































































































































                                                                                                                                                                                                                                                            
import MySQLdb
import string

class DBMySQLConnection:
	def __init__(self, username, password, host, dbname):
		#initialize at the start all the user parameters
		self.usern = username
		self.passw = password
		self.host = host
		self.db = dbname
		self.connectionCreated = 0
		self.tasksList = list()
		self.callerLists = list()
		self.errCode = None
		self.lockSet = 0
		self.deviceUpdate = None
		global debugMode
		debugMode = 0 

	def connectDB(self):
		try:
			#try the connection
			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 updateGSMDevice(self, deviceName, newPortName, newNumber):
		if self.connectionCreated == 1:
			try:
                                stmt = "UPDATE DeviceAddressTable SET  portName = '"+ newPortName + "', number = '"+ newNumber+ "' WHERE  deviceName = '" + deviceName+ "'"
				self.cur.execute(stmt)
                                
                        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("UPDATE PingResultTable SET sipServer=%i, sipGate=%i, unisip=%i, gsmBox1=%i, gsmBox2=%i WHERE taskNo=%i"%(int(sipServer), int(sipGate), int(unisip), int(gsmBox1), int(gsmBox2), int(taskNo)))
                                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 `errorcode`=%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 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