summaryrefslogtreecommitdiffstats
path: root/Code/Server-Code/DbClass.py
diff options
context:
space:
mode:
Diffstat (limited to 'Code/Server-Code/DbClass.py')
-rwxr-xr-xCode/Server-Code/DbClass.py438
1 files changed, 438 insertions, 0 deletions
diff --git a/Code/Server-Code/DbClass.py b/Code/Server-Code/DbClass.py
new file mode 100755
index 0000000..cc3a083
--- /dev/null
+++ b/Code/Server-Code/DbClass.py
@@ -0,0 +1,438 @@
+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.deviceUpdate = None
+ 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)
+
+ 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 07667929147--Fitri
+ 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:
+ 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)))
+ #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 `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
+
+