def add(self, task):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "INSERT INTO task (taskName, taskParameter, taskPath, taskState, userID, assignedNodeId) VALUES (%s, %s, %s, %s, %s, %s)"
val = (task._taskName, task._taskParameters, task._taskExePath,
task._taskState, task._taskUserId, None)
# val = ("1111111", "John", "21", 3, '997', None)
# val = (hostName, userName)
# nodeid is foreign key of assignedNodeId
# clientId is foreign key of userID
cursor.execute(sql, val)
connection.commit()
if cursor._last_insert_id:
logging.info(str(cursor.rowcount) + " task inserted.")
return cursor._last_insert_id
else:
logging.warnings('Task is not added to DB')
logging.error('Task is not added to DB')
return
except Exception as e:
logging.exception(e)
raise
finally:
DB_Manager.closeConnection()
def setTaskStatus(self, taskState, taskId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "UPDATE task SET taskState = " + taskState + " WHERE taskId = " + str(
taskId)
# sql = "UPDATE task SET taskState = 'PENDING' WHERE taskId = 4"
print sql
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
print(cursor.rowcount, "record updated.")
logging.info("1 record updated.")
else:
print('no record updated')
logging.warnings('no record updated')
return
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while setTaskStatus into task'
logging.error(error_msg)
raise
finally:
DB_Manager.closeConnection()
def getTaskOutcomeByTaskId(self, taskId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM taskresult WHERE taskId = " + str(taskId)
cursor.execute(sql)
myresult = cursor.fetchall()
return myresult
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTaskById in task'
finally:
DB_Manager.closeConnection()
def delete(self, taskResultId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "DELETE FROM taskresult WHERE resultId=" + str(taskResultId)
cursor.execute(sql)
connection.commit()
print cursor.rowcount, "task deleted."
return cursor._last_insert_id
except Exception as e:
logging.exception(e)
logging.exception('Error occured while deleting data in task')
raise
finally:
DB_Manager.closeConnection()
def getTaskStateById(self, taskId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task WHERE taskId = " + str(taskId)
# sql = "SELECT * FROM task WHERE userID='1111113'";
cursor.execute(sql)
myresult = cursor.fetchall()
return unicodeToStringObj.unicodeToString(myresult[0][4])
except Exception as e:
logging.exception(e)
error_msg = 'Error occured in getTasksByClientId in coordinator/database/taskRepository.py'
finally:
DB_Manager.closeConnection()
def getTasksByStatus(self, outcome):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task WHERE outcome = " + str(outcome)
cursor.execute(sql)
myresult = cursor.fetchall()
for x in myresult:
print(x)
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTaskById in task'
finally:
DB_Manager.closeConnection()
def getAllRecords(self):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM taskresult"
cursor.execute(sql)
myresult = cursor.fetchall()
for x in myresult:
print(x)
except Exception as e:
logging.exception('Error!-> Code: {c} && Message, {m}'.format(
c=type(e).__name__, m=str(e)))
raise Exception(str(e))
finally:
DB_Manager.closeConnection()
def getTasksByNodeId(self, assignedNodeId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task WHERE assignedNodeId = " + str(
assignedNodeId)
cursor.execute(sql)
myresult = cursor.fetchall()
for x in myresult:
print(x)
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTasksByNodeId in task'
finally:
DB_Manager.closeConnection()
def delete(self, clientId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "DELETE FROM client WHERE clientId=" + str(clientId)
cursor.execute(sql)
connection.commit()
print cursor.rowcount, " client deleted."
return cursor._last_insert_id
except Exception as e:
# logging.exception(e)
# logging.exception('Error occured while deleting data in task')
print "Error Occured"
return e
finally:
DB_Manager.closeConnection()
def getAvailableNodes(self):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM node WHERE nodeStatus='AVAILABLE'"
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
logging.info('There are more than zero available nodes')
return myresult
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
raise
finally:
DB_Manager.closeConnection()
def modifyNodeStatus(self, nodeId, nodeStatus):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "UPDATE node SET nodeStatus = " + str(
nodeStatus) + " WHERE nodeId = " + str(nodeId)
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
logging.info("1 node modified.")
return cursor.rowcount
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
raise
finally:
DB_Manager.closeConnection()
def getAllNodes(self):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM node"
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
logging.info('There are some nodes')
return myresult
except Exception as e:
logging.exception(e)
logging.exception('Error occured while fetching all the nodes: ' +
e.message)
raise
finally:
DB_Manager.closeConnection()
def delete(self, nodeId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
# sql = "DELETE FROM node WHERE nodeId = "+ str(nodeId)
sql = "DELETE FROM node"
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
print(cursor.rowcount, "record deleted.")
else:
print "OOPS !No record deleted---------"
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
finally:
DB_Manager.closeConnection()
def getNodeStatusByNodeId(self, nodeId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM node WHERE nodeId = " + str(nodeId)
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
logging.info('Node status has found successfully')
return unicodeToStringObj.unicodeToString(myresult[0][3])
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
raise
finally:
DB_Manager.closeConnection()
def getClients(self):
try:
connection = DB_Manager.getConnection()
print connection
cursor = connection.cursor()
sql = "SELECT * FROM client"
cursor.execute(sql)
myresult = cursor.fetchall()
for x in myresult:
print(x)
except Exception as e:
logging.exception(e)
logging.exception('Error occured while getting Clients in task')
finally:
DB_Manager.closeConnection()
# getClients("a")
def getAlltasks(self):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task"
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
return myresult
else:
return 0
except Exception as e:
error_msg = 'Error occured while getting getAllRecords in task'
logging.exception(str(error_msg) + ": " + str(e.message))
raise
finally:
DB_Manager.closeConnection()
def modify(self, hostName, userName, clientId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "UPDATE client SET hostName = " + hostName + ", userName = "******" WHERE clientId = " + str(
clientId)
# sql = "UPDATE client SET userName='******' WHERE " + str(clientIdd)
# "UPDATE node SET nodeStatus='hell' WHERE nodeId =382"
# sql = "UPDATE task SET taskState = 'PENDING' WHERE taskId = 5"
cursor.execute(sql)
connection.commit()
print(cursor.rowcount, "client modified.")
except Exception as e:
logging.exception(e)
logging.exception('Error occured while modifying data into task')
finally:
DB_Manager.closeConnection()
def getTasksByStatus(self, taskState):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task WHERE taskState = " + str(taskState)
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
logging.info(
'Task according to the status has fetched sucessfully')
return myresult
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTaskById in task'
raise
finally:
DB_Manager.closeConnection()
def getTasksByClientId(self, userID):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "SELECT * FROM task WHERE userID = " + str(userID)
# sql = "SELECT * FROM task WHERE userID='1111113'";
print sql
cursor.execute(sql)
myresult = cursor.fetchall()
if myresult:
for x in myresult:
print(x)
else:
print "no result found for the given clientId"
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTasksByClientId in task'
finally:
DB_Manager.closeConnection()
def updateTaskState(self, taskId, taskState):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "UPDATE task SET taskState = " + str(
taskState) + " WHERE taskId = " + str(taskId)
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
print(cursor.rowcount, "task modified.")
return cursor.rowcount
else:
print "Task State is not modified."
return "Task State is not modified."
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while getting in getTaskById in task'
finally:
DB_Manager.closeConnection()
def add(self, nodeObj):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "INSERT INTO node (nodeIp, nodePort, nodeStatus) VALUES (%s, %s, %s)"
val = (nodeObj._ip, nodeObj._port, nodeObj._status)
# val = ("192.168.6.137", 3000, 1)
# val = ("127.0.0.1", 5000, 1)
cursor.execute(sql, val)
connection.commit()
if cursor.rowcount:
logging.info('1 node inserted.')
return cursor._last_insert_id
else:
logging.warning("No record inserted.")
return 'No record inserted.'
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into node')
finally:
DB_Manager.closeConnection()
def assignNode(self, taskState, assignedNodeId, taskId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
# sql = "UPDATE task SET taskState = 'PENDING' WHERE taskId = 5"
# sql = "UPDATE task SET taskState = ? , assignedNodeId = ? WHERE taskId = ?"
sql = "UPDATE task SET taskState = " + taskState + ", assignedNodeId = " + str(
assignedNodeId) + " WHERE taskId = " + str(taskId)
print sql
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
print(cursor.rowcount, "taskResult modified.")
else:
print "No record Modified of Task Result"
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while assigning in assignNode in task'
finally:
DB_Manager.closeConnection()
def modify(self, taskName, taskParameter, taskPath, taskState, userID,
assignedNodeId, taskId):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
# assignedNodeId is foreign key
sql = "UPDATE task SET taskName = " + taskName + ", taskParameter = " + taskParameter + ", taskPath = " + taskPath + ", taskState = " + str(
taskState) + ", userID = " + str(
userID) + ", assignedNodeId = " + str(
assignedNodeId) + " WHERE taskId = " + str(taskId)
# sql = "UPDATE task SET taskName ='helll', taskParameter = '111', taskPath = '1111' , taskState = 1 , userID = 1111113, assignedNodeId = 3 WHERE taskId = 4"
# sql = "UPDATE client SET hostName = " + hostName + ", userName = "******" WHERE clientId = " + str(clientId)
cursor.execute(sql)
connection.commit()
print(cursor.rowcount, "task modified.")
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while modifying data into task'
finally:
DB_Manager.closeConnection()
def add(self, clientObj):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "INSERT INTO client (hostName, userName) VALUES (%s, %s)"
val = (clientObj._hostName, clientObj._userName)
# val = ("111111111", "John")
cursor.execute(sql, val)
connection.commit()
if cursor._last_insert_id:
logging.info(str(cursor.rowcount) +" client inserted")
print str(cursor.rowcount) +" client inserted"
return cursor._last_insert_id
else:
logging.warning('Task is not added')
return
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
raise
finally:
DB_Manager.closeConnection()
def updateAssignedNode(self, taskId, assignedNodeID):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "UPDATE task SET assignedNodeId = " + str(
assignedNodeID) + " WHERE taskId = " + str(taskId)
cursor.execute(sql)
connection.commit()
if cursor.rowcount:
print('assignedNodeId of a task is modified.')
logging.info('assignedNodeId of a task is modified.')
else:
print 'No record Modified of Task'
logging.error('No record Modified of Task')
return cursor.rowcount
except Exception as e:
logging.exception(e)
error_msg = 'Error occured while assigning in assignNode in task'
logging.error(error_msg)
raise
finally:
DB_Manager.closeConnection()
def add(self, taskResult):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "INSERT INTO taskresult (taskId, Outcome, ErrorCode, ErrorMsg, ResultBuffer) VALUES (%s, %s, %s, %s, %s)"
val = (taskResult._taskId, taskResult._status,
taskResult._errorCode, taskResult._errorMessage,
taskResult._resultBuffer)
# val = (8, 2, 404, "400", "MEDIUMBLOB")
cursor.execute(sql, val)
connection.commit()
if cursor.rowcount:
logging.log(cursor.rowcount, "record inserted.")
return cursor._last_insert_id
else:
logging.error('No task result inserted')
return
except Exception as e:
logging.exception(e)
raise
finally:
DB_Manager.closeConnection()
import logging
from coordinator.database.DBManager import DB_Manager
DB_Manager = DB_Manager()
class clientRepository:
def add(self, clientObj):
try:
connection = DB_Manager.getConnection()
cursor = connection.cursor()
sql = "INSERT INTO client (hostName, userName) VALUES (%s, %s)"
val = (clientObj._hostName, clientObj._userName)
# val = ("111111111", "John")
cursor.execute(sql, val)
connection.commit()
if cursor._last_insert_id:
logging.info(str(cursor.rowcount) +" client inserted")
print str(cursor.rowcount) +" client inserted"
return cursor._last_insert_id
else:
logging.warning('Task is not added')
return
except Exception as e:
logging.exception(e)
logging.exception('Error occured while adding data into task')
raise
finally:
