summaryrefslogtreecommitdiffstats
path: root/Under-Testing/Server-Code-New/DbClass.py
blob: d937eb1ef85370a5e02ab844b3513a0884b8bc03 (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
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
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