summaryrefslogblamecommitdiffstats
path: root/notFinishedCode/dbClass.py
blob: 552d1406ac17f6f5689fa9dec0a974cd7d4f79df (plain) (tree)
1
2
3
4
5
6
7
8
9
10
11
12

              
 








                                                                
                                
                                
                              






















































                                                                                                          
                                                              


















                                                                         
                                             



                                                                
                                                          


                                                                     
                                                                
                
                                                         





                                               
                                                                                                                                                     

                                                                                    



                                                            


                                                                          









                                                                         





















                                                                                                                                                     



                                                   
                                                                                                                                          



                                                            



                                                                      
 









                                                                                         
                                                                                                                                                                                                                                                            



                                                            



                                                                          
 
                                



                                                    










                                                                                                                        
 








                                                                                            

                                





















                                                                                                                                                       
        





                                                                                                                                                                                                                                                                                                                                                                                                                         
 


                                                                       
 

















                                                                                                                                                 
                                                      

                                                                                        
                                        


                                                    
 







                                                                             
                                
 












                                                                                 












































































                                                                                                                                                                                                                   
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.lockSet = 0
		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)
                   		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 removeTaskFromList(self, taskID):
		#remove only one task from the task list
		if self.connectionCreated == 1:
			for index in range(len(self.tasksList)):
        			item = self.tasksList[index]
				if item[0] == str(taskID):
					#self.tasksList.remove(index)
					#print 'found it'
					del self.tasksList[index]
					return 1 #deleted taskID
		
			return 2 #didn't find that taskID
		else:
			return 0
			
	def deviceAddress(self,deviceName):
		if self.connectionCreated == 1:
			try:
				successful = self.cur.execute("SELECT `deviceIP`,`number` FROM DeviceAddressTable where `deviceName`=%s", deviceName)
				#self.cur.execute()
				output = self.cur.fetchall() #get the mysql response
				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 GSMPrefix(self,deviceName):
		if self.connectionCreated == 1:
			try:
				successful = self.cur.execute("SELECT `deviceIP`,`number` FROM DeviceAddressTable where `deviceName`=%s", deviceName)
				output = self.cur.fetchall() #get the mysql response
				deviceAddr = ''
                    		for record in output:
					columns = list()
                        		for entry in record:
						columns.append(str(entry))
                            		GSMListPrefix = 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 updateTaskResult(self, taskID, status):
		if self.connectionCreated == 1:
			try:
				successful = self.cur.execute("UPDATE TaskTable SET status=%i WHERE taskID=%i"%(int(status), int(taskID)))
				output = self.cur.fetchone()

				if debugMode == 1:
					print output
				if successful == 0:
					return 1 #update successful
				else:
					return 4 #taskID doesn't exist

			except MySQLdb.Error, e:
				if debugMode == 1:
					print str(e)
				return 3
		else:
			return 0

	def updatePingResult(self, taskNo, sipServer, sipGate, sipLoc, gsmBox1, gsmBox2):
		if self.connectionCreated == 1:
			try:
                                successful = self.cur.execute("UPDATE PingResultTable SET sipServer=%i, sipGate=%i, sipLoc=%i, gsmBox1=%i, gsmBox2=%i WHERE taskNo=%i"%(int(sipServer), int(sipGate), int(sipLoc), 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 insertTaskIn2(self, fromDevice, toDevice, taskNo):
		if self.connectionCreated == 1:
			try:
				#I 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`, `status`) VALUES ('" + str(taskNo) + "', '" + str(fromDevice) + "', '" + str(toDevice) +"', '0'); 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()


				#without closing the cursos we get a MySQL error, the mistake is an internal mistak of the MySQLdb python library
	#			self.cur.close()
	#			self.cur = self.datBaseConn.cursor()

				while self.cur.nextset() is not None: pass

				newQuery1 = 'SELECT taskID FROM `TempTaskTable` ORDER BY taskID DESC LIMIT 1';
				successful1 = self.cur.execute(newQuery1)
                                record = self.cur.fetchone()

				columns = list()
                                for entry in record:
                                	columns.append(str(entry))
                                
				columns.append(str(taskNo))
				columns.append(str(fromDevice))
				columns.append(str(toDevice))
				columns.append(str(0))
                                self.tasksList.append(columns)				

				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 searchTaskList(self, fromDevice, toDevice):
		if self.connectionCreated == 1:
			for item in self.tasksList:
				taskID = item[0]
				taskNo = item[1]
				callFrom = item[2]
				callTo = item [3]
				if callFrom == fromDevice and callTo == toDevice:
					return 1 #that task was found
			return 2 #that task wasn't found
		else:
			return 0	 

	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()
						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