def createTable():
'''
creates an empty SQL table for scripts
@param None.
@return errorCode: Error
'''
command = """CREATE TABLE IF NOT EXISTS sl (
id INTEGER PRIMARY KEY AUTOINCREMENT,
scriptId INTEGER,
userId INTEGER,
compId INTEGER,
startTime DATETIME,
endTime DATETIME,
returnVal INTEGER,
errorCode INTEGER,
stdoutFile CHAR(256),
stderrFile CHAR(256),
asAdmin BOOL,
FOREIGN KEY (scriptId)
REFERENCES s(id)
ON DELETE CASCADE,
FOREIGN KEY (userId)
REFERENCES u(id)
ON DELETE CASCADE,
FOREIGN KEY (compId)
REFERENCES c(id)
ON DELETE CASCADE
);"""
# e = sqlFuncs.createTable(command, "ScriptLog")
e = sqlFuncs.exeCommand(command, "createTable", "ScriptLog")
return e
def delete(ID: int):
'''
Removes a scriptLog entry from the database based on it's ID.
Also removes the corresponding files (stdout/stderr) from directory.
@param
ID: int - primary key of scriptLog
@return
e - most recent error when executing function or Success if no error occurs
'''
e, stdoutFile = ScriptLogTable.getAttrByID("stdoutFile", ID)
e, stderrFile = ScriptLogTable.getAttrByID("stderrFile", ID)
if (e == pref.getError(pref.ERROR_SUCCESS)):
command = """DELETE FROM sl WHERE ID = """ + str(ID) + """;"""
e = sqlFuncs.exeCommand(command, "delete", "ScriptLog")
if (
e == pref.getError(pref.ERROR_SUCCESS)
): # If deleted from db successfully, remove corresponding stdout/stderr files
path = pref.getNoCheck(pref.CONFIG_SCRIPT_LOG_PATH)
try:
os.remove(path + stdoutFile)
os.remove(path + stderrFile)
except OSError as err:
e = pref.getError(pref.ERROR_FILE_NOT_FOUND,
args=(str(stdoutFile) + "/" +
str(stderrFile)))
return e
def deleteTable():
'''
Removes the computer SQL table from the database. Used for testing principally.
@param None.
@return e - Error code, returns success if no error occurs.
'''
command = """DROP TABLE c;
"""
e = sqlFuncs.exeCommand(command, "deleteTable", "Computer")
return e
def delete(ID: int):
'''
Removes a computer entry from the database based on it's ID.
@param
ID: int - primary key of computer
@return
e - most recent error when executing function or Success if no error occurs
'''
command = """DELETE FROM c WHERE ID = """ + str(ID) + """;"""
e = sqlFuncs.exeCommand(command, "delete", "Computer")
return e
def add(entry: ScriptLog):
'''
Takes a scriptLog object (which has not yet been added to the scriptLog SQL table),
adds it to the table and updates scriptLog object's ID (ID is automatically
generated using sqlite AUTOINCREMENT)
This function is meant to take a scriptLog object generated from a call to the
createEntry function.
@param
entry - object of class ScriptLog
@return
e - most recent error when executing function or Success if no error occurs
'''
ID = None
command = """ INSERT INTO sl (id, scriptID, userID, compID, startTime, endTime, returnVal, errorCode, stdoutFile, stderrFile, asAdmin) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""
data = entry.paramToList()
e, ID = sqlFuncs.insert(command, data, "add", "ScriptLog")
entry.ID = ID # access ID through entry object after executing this function
######### create names/files for stdoutFile, stderrFile - {STDOUT/STDERR}_SCRIPT_ID.log #########
# (1) Add names to entry object
if (e == pref.getError(pref.ERROR_SUCCESS)):
e, scriptName = tos.ScriptTable().getAttrByID(
"name", entry.scriptID)
if e == pref.getError(pref.ERROR_SUCCESS):
entry.stdoutFile = "STDOUT_" + str(scriptName) + "_" + str(
ID
) + ".log" # access stdoutFile through entry object after executing this function
entry.stderrFile = "STDERR_" + str(scriptName) + "_" + str(
ID
) + ".log" # access stderrFile through entry object after executing this function
# (2) Write names to sql entry
command2 = """UPDATE sl SET stdoutFile = \"""" + str(
entry.stdoutFile) + """\", stderrFile = \"""" + str(
entry.stderrFile) + """\" WHERE id = """ + str(
ID) + """;"""
e = sqlFuncs.exeCommand(command2, "add", "ScriptLog")
# (3) Create files
if (e == pref.getError(pref.ERROR_SUCCESS)):
e = createFile(
e, pref.getNoCheck(pref.CONFIG_SCRIPT_LOG_PATH),
entry.stdoutFile)
if (e == pref.getError(pref.ERROR_SUCCESS)):
e = createFile(
e, pref.getNoCheck(pref.CONFIG_SCRIPT_LOG_PATH),
entry.stderrFile)
if (
e != pref.getError(pref.ERROR_SUCCESS)
): # if any errors occured along the way, revert any potential changes (catch all)
entry.stdoutFile = None
entry.stderrFile = None
return e #FIXME - should actions be undone if any errors occur along the way *thinking* - for loop
def editEntry(entry: ScriptLog):
'''
Updates a row in the scriptLog SQL table based on the entry object passed.
Overwrites all attributes of the row with the values of the entry object.
Overwrites row based on the ID of the entry object.
@param
entry: ScriptLog - ScriptLog object, must have ID != None or error will be thrown
@return
e - most recent error when executing function or Success if no error occurs
sl - ScriptLog object corresponding to row updated in SQL table. Should be the
same as entry passed to function if no error occured
'''
sl = entry
if (entry.ID == None):
e = pref.getError(pref.ERROR_NO_ID_PROVIDED,
args=("editEntry", "ScriptLog"))
else:
command = """UPDATE sl SET """
for attr, value in entry.__dict__.items():
if (attr == "ID"):
pass
else:
command = command + str(attr)
if (value == None):
command = command + """ = NULL"""
elif attr[-4:] == "Time":
command = command + """ = """ + """\"""" + str(
value.strftime('%Y-%m-%d %H:%M:%S.%f')) + """\""""
elif isinstance(value, str):
command = command + """ = """ + """\"""" + str(
value) + """\""""
else:
command = command + """ = """ + str(value)
command = command + """, """
command = command[:-2] #remove last ' ,'
command = command + """ WHERE ID = """ + str(entry.ID) + """;"""
e = sqlFuncs.exeCommand(command, "editEntry", "ScriptLog")
if (e == pref.getError(pref.ERROR_SUCCESS)):
command2 = """SELECT * FROM sl WHERE ID = """ + str(
entry.ID) + """;"""
e, row = sqlFuncs.getRow(command2, "editEntry", "ScriptLog")
if (e == pref.getError(pref.ERROR_SUCCESS)):
e, sl = tupleToScriptLog(row, "editEntry")
return e, sl
def createTable():
'''
creates an empty SQL table for scripts
@param None.
@return errorCode: Error
'''
command = """CREATE TABLE IF NOT EXISTS u (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
username CHAR(256),
password CHAR(256),
dtCreated DATETIME,
dtModified DATETIME,
admin BOOL
);"""
e = sqlFuncs.exeCommand(command, "createTable", "User")
# e = sqlFuncs.createTable(command, "User")
return e
def createTable():
'''
creates an empty SQL table for scripts
@param None.
@return errorCode: Error
'''
command = """CREATE TABLE IF NOT EXISTS s (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name CHAR(256),
fileName CHAR(256),
author INTEGER,
desc CHAR(1024),
dtCreated DATETIME,
dtModified DATETIME,
size FLOAT(5, 3),
isAdmin BOOL,
FOREIGN KEY (author)
REFERENCES u(id)
ON DELETE CASCADE
);"""
e = sqlFuncs.exeCommand(command, "createTable", "Script")
return e
def createTable():
'''
creates an empty SQL table for scripts
@param None.
@return errorCode: Error
'''
command = """CREATE TABLE IF NOT EXISTS c (
id INTEGER PRIMARY KEY AUTOINCREMENT,
userId INTEGER,
name CHAR(256),
nickName CHAR(256),
desc CHAR(1024),
username CHAR(256),
IP CHAR(256),
dtCreated DATETIME,
dtModified DATETIME,
asAdmin BOOL,
FOREIGN KEY (userId)
REFERENCES u(id)
ON DELETE CASCADE
);"""
e = sqlFuncs.exeCommand(command, "createTable", "Computer")
# e = sqlFuncs.createTable(command, "Computer")
return e