summaryrefslogtreecommitdiffstats
path: root/For Weekly Test/20-07-2011/classDB.py
blob: 11a109f152e4c2940e8902fd1daccd5c07942d8f (plain) (blame)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
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