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

              
 









                                                                
                              









































































                                                                                                          
                                             



                                                                
                                                          


                                                                     
                                                                
                
                                                         





                                               
                                                                                                                                       























                                                                                        
                                                                                                                                          



                                                            



                                                                      
 









                                                                                         
                                                                                                                                                                                                                                                            



                                                            



                                                                          
 
                                



                                                    










                                                                                                                        
 








                                                                                            

                                





















                                                                                                                                                       
        





                                                                                                                                                                                                                                                                                                                                                                                                                         
 


                                                                       
 



















                                                                                                                                                 
                                        


                                                    
 







                                                                             
                                
 












                                                                                 
             
                           



                                                

                                                  
                        
                        



                                                                 
 



                                               
                                                

                         
 
                        



                                                    



                                                          
                                                    



                                                                                    
                         
                                                                                         




                                                         
 
                                                                                 


                                                                                 

                                                                                    





                                                                 
 
                                                   

              
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() 
		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))
					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` FROM DeviceAddress where `deviceName`=%s", deviceName)
				#self.cur.execute()
				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
				deviceAddr = ''
                    		for record in output:
					columns = list()
                        		for entry in record:
                            			deviceAddr = str(entry)
                   		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 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))
                                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	 
user = 'root'
passw = 'randompasswordSQL'
host = 'localhost'
dbname = 'gsmselftesting'

x = DBMySQLConnection(user, passw, host, dbname)
print "connected to the DB ", x.connectDB()
print "do i have anything to do", x.anyTasksToDo()
for item in x.tasksList:
	taskID = item[0]
	taskNo = item[1]
	callFrom = item[2]
	callTo = item[3]
	print "call from to ", taskID , callFrom , callTo, taskNo

print "found", x.searchTaskList('S1IP2','GSM1')
#if variable == 0:
#	print x.insertTaskIn2('gsm4','sip8',2)

print "remove a task ",x.removeTaskFromList('2')

#print x.cleanTasksList()

for item in x.tasksList:
	device = item[1]
	print "device name", device 
	print "device's IP", x.deviceAddress(device)

print "update task result ", x.updateTaskResult(1,3)

print "update ping Table", x.updatePingResult(1,5,4,3,2,1)
print "delete a task from temp table", x.deleteTempTask(2)
print "add a result to the table", x.addResult(4,34)

#new task to do
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
#just made for Tri :)---------------------------------------------------------------
#print x.cleanTasksList()
#print "do i have any task from SIP1 to GSM1 to do", x.anyTasksFromTo('SIP2', 'GSMu1', 2)
#for item in x.tasksList:
#	taskID = item[0]
#	callFrom = item[1]
#	callTo = item[2]
#	print "call from to ", taskID , callFrom , callTo

print 'i am inserting into the table something', x.insertTaskIn2('gsm4','sip8',2)
print 'i am inserting into the table something', x.insertTaskIn2('gsm4','sip8',2)
print 'i am inserting into the table something', x.insertTaskIn2('gsm4','sip8',2)
print 'i am inserting into the table something', x.insertTaskIn2('gsm4','sip8',2)
#-----------------------------------------------------------------------------------
#$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$$
for item in x.tasksList:
	taskID = item[0]
	taskNo = item[1]
	callFrom = item[2]
	callTo = item[3]
	print "call from to ", taskID , callFrom , callTo, taskNo

print "close connection to the DB", x.closeDBConn()

del x #delete