-
Notifications
You must be signed in to change notification settings - Fork 0
db_interface.mjs
up2108272 edited this page May 9, 2024
·
2 revisions
- This file directly queries and interacts with the database
export function openDb(callback) {
if (!dbOpened) {
db = new sqlite3.Database("./server/learnforge.db", sqlite3.OPEN_READWRITE, (err) =>{
if (err) {
callback(false);
console.error(err.message);
}
else {
dbOpened = true;
callback(true);
}
});
}
else {
callback(true);
}
}- Opens the database if not already open so it can be read from and written to
- Success status returned through callback
export function createStudent(id, fname, mname, lname, email) {
openDb((res) => {
if(res) {
let sql = `INSERT INTO student_details VALUES (?, ?, ?, ?, ?)`
db.run(sql, [id, fname, mname, lname, email], (err) => {
if (err) return console.error(err.message);
})
}
});
}- Adds a student to the database
export function createLecturer(id, fname, mname, lname, email) {
openDb((res) =>{
if(res) {
let sql = `INSERT INTO lecturer_details VALUES (?, ?, ?, ?, ?)`
db.run(sql, [id, fname, mname, lname, email], (err) => {
if (err) return console.error(err.message);
})
}
})
}- Adds a lecturer to the database
export function displayStudentDetails() {
openDb((res) => {
if(res) {
let q = `SELECT * FROM student_details`;
db.all(q, [], (err, rows) =>{
if (err) return console.error(err.message);
rows.forEach((row) => {
console.log(row);
})
});
}
})
}- Displays the student details table in the console, used for debugging
export function displayTable(table) {
openDb((res) => {
if(res) {
let q = "SELECT * FROM " + table + ";";
db.all(q, [], (err, rows) =>{
if (err) return console.error(err.message);
rows.forEach((row) => {
console.log(row);
})
})
}
})
}- Used to display a given table from the database in the console, for debugging
export function getProfile(uid, callback) {
openDb((res) => {
if(res) {
let sql = "SELECT * FROM student_details WHERE student_id =" + "'" + uid + "'" + "UNION SELECT * FROM lecturer_details WHERE lecturer_id =" + "'" + uid + "'";
db.get(sql, [], (err, row) => {
if (err) return console.error(err.message);
if (row == undefined) {
callback(['ERROR']);
}
else {
callback([row.user_fname, row.user_mname, row.user_lname, row.user_emailaddress]);
}
});
}
});
}- Retrieves a user profile from the database, either lecturer or student
- Returned through callback
export function updateUserProfile(uid, column, newValue, callback) {
let sql1 = `UPDATE student_details SET ` + column + ` = '` + newValue + `' WHERE student_id = '` + uid + `';`;
let sql2 = `UPDATE lecturer_details SET ` + column + ` = '` + newValue + `' WHERE lecturer_id = '` + uid + `';`;
console.log(uid, column, newValue);
openDb((res) => {
if(res) {
db.run(sql1, (err) => {
if (err) {
console.log(err.message);
}
})
db.run(sql2, (err) => {
if (err) {
console.log(err.message);
}
})
}
});
}- updates the profile information of a given student or lecturer
export function createQuiz(uid, quizTitle, quizData) {
openDb((res) => {
if(res) {
let timeout = 5;
let validId = false
let tmpId;
var repeat = function(triesLeft) {
if(triesLeft > 0 && validId == false) {
tmpId = getRandomInt(9223372036854775807); // max sql integer value
let checkSql = `SELECT * FROM quiz_details WHERE quiz_id = ` + tmpId + `;`;
db.get(checkSql, [], (err, row) => {
if(err) {
return console.log(err.message);
}
if(row == undefined) {
validId = true;
let insertSql = `INSERT INTO quiz_details VALUES (?, ?, ?, ?)`
db.get(insertSql, [tmpId, uid, quizTitle, quizData], (err, row) => {
if(err) {
return console.log(err.message);
}
})
}
else {
repeat(triesLeft - 1)
}
})
}
else {
if(validId) {
console.log("Got unique ID: ", tmpId);
}
else {
console.log("Couldn't get unique ID");
}
}
}
repeat(timeout);
}
});
}- Adds a quiz to the database
- Generates a random id that isn't in use
export function getQuizzesFromLecturer(lecturerId, callback) {
openDb((res) => {
if(res) {
let sql = `SELECT * FROM quiz_details WHERE quiz_details.creator_id = (?);`;
db.all(sql, [lecturerId], (err, rows) => {
if(err) {
return console.log(err.message);
}
let data = [];
rows.forEach((row) => {
let str = '{"quiz_title": "' + row.quiz_title + '", "questions": ' + row.quiz_data + '}'
//console.log(row);
data.push(str);
});
console.log(data);
callback(data);
})
}
})
}- Retrieves all the quizzes made by a given lecturer
- Returned through callback
export function createClass(lecturerId, className, joinCode, callback) {
openDb((res) => {
if(res) {
let checkJoinCodeExists = `SELECT 1 FROM class_details WHERE join_code = '` + joinCode + `';`;
db.get(checkJoinCodeExists, [], (err, row) => {
if(err) {
return console.log(err.message);
}
else {
if(row == undefined) {
let addToDb = `INSERT INTO class_details (join_code, lecturer_id, class_name) VALUES (?, ?, ?);`;
db.run(addToDb, [joinCode, lecturerId, className], (err) => {
if(err) return console.log(err.message);
});
}
else {
console.log("That join code is unavailable");
callback("UNAVAILABLE");
}
}
});
}
});
}- Adds a class to the database, the join code doubles as a primary key
export function addStudentToClass(studentId, joinCode, callback) {
if(studentId != null && joinCode != null) {
openDb((res) => {
if(res) {
let sql = 'INSERT INTO class_student VALUES (?, ?);';
db.run(sql, [joinCode, studentId], (err) =>{
if(err) {
console.log(err.message);
callback("ERROR");
}
else {
callback("SUCCESS");
}
});
}
});
}
else {
console.log("Student or class ID null");
}
}- Adds an given student to a given class
export function getClassesFromLecturer(lecturerId, callback) {
openDb((res) => {
if(res) {
let sql = `SELECT * FROM class_details WHERE lecturer_id = '` + lecturerId + `';`;
db.all(sql, [], (err, rows) => {
if(err) {
return console.log(err.message);
}
let data = [];
rows.forEach((row) => {
data.push(row);
})
console.log(typeof(data));
console.log(data);
callback(data);
});
}
});
}- Retrieves all the classes made by a given lecturer
- Returned through callback
export function getClassesFromStudent(studentId, callback) {
openDb((res) => {
if(res) {
let sql = `SELECT class_details.join_code, class_details.lecturer_id, class_details.class_name FROM class_details JOIN class_student WHERE class_details.join_code = class_student.join_code AND class_student.student_id = '` + studentId + `';`;
db.all(sql, [], (err, rows) => {
if(err) {
callback(err.message);
return console.log(err.message);
}
let data = [];
rows.forEach((row) => {
data.push(row);
})
callback(data);
});
}
})
}- Retrieves all the classes a given student is apart of
- Returned through callback
export function getQuizzesFromClass(joinCode, callback) {
openDb((res) => {
if(res) {
let sql = `SELECT quiz_details.quiz_id, quiz_details.creator_id, quiz_details.quiz_title, quiz_details.quiz_data
FROM quiz_details
JOIN assignment_details
WHERE quiz_details.quiz_id = assignment_details.quiz_id
AND assignment_details.class_id = '` + joinCode + `';`;
db.all(sql, [], (err, rows) => {
if(err) {
callback(err.message);
return console.log(err.message);
}
let data = [];
rows.forEach((row) => {
data.push(row);
})
callback(data);
});
}
})
}- Gets all the quizzes assigned to a given class
- Returned through callback
export function addQuizToClass(quizId, joinCode, description, callback) {
openDb((res) => {
if(res) {
let checkSql = `SELECT * FROM quiz_details WHERE quiz_id = '` + quizId + `';`;
db.get(checkSql, [], (err, row) => {
if(err) {
callback(err.message);
return console.log(err.message);
}
else{
if (row == undefined) {
callback("Quiz ID doesn't exist");
return;
}
else {
let sql = `INSERT INTO assignment_details (class_id, quiz_id, issue_date, description) VALUES (?, ?, TIME('now'), ?)`;
db.run(sql, [joinCode, quizId, description], (err) => {
if(err){
callback(err.message);
return console.log(err.message);
}
else {
callback("SUCCESS");
}
});
}
}
});
}
})
}- Assigns a given quiz to a given class
learnforge wiki!