Skip to content

/ 4007CEM-Resit-2

Permalink
master
Switch branches/tags

4007CEM-Resit-2/DBClass.h

Go to file
 
 
Cannot retrieve contributors at this time
273 lines (268 sloc) 12.1 KB
Raw Blame
#include <iostream>
#include <string>
#include <sqlite3.h>
using namespace std;
class TimeTable {
private:
sqlite3 *db;
char *zErrMsg;
int rc;
const char* sql;
sqlite3_stmt *stmt;
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
int i;
for(i = 0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[1] : "NULL");
}
printf("\n");
return 0;
}
void DBErrors() {
if (rc) {
cout << "Data Base Error:" << sqlite3_errmsg(db) << endl;
closeDB();
}
}//Returns errors given by SQLite
//The following section is used to create the tables
void ModulesTable(){ //To be run after courses table
sql = "CREATE TABLE Modules("\
"MODULE_ID VARCHAR PRIMARY KEY NOT NULL,"\
"MODULE_NAME TEXT NOT NULL,"\
"Course VARCHAR ,"\
"FOREIGN KEY (Course) REFERENCES Courses(Course_ID));";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void CoursesTable(){ //To be run first in the program
sql = "CREATE TABLE Courses("\
"Course_ID VARCHAR PRIMARY KEY NOT NULL,"\
"Course_Name VARCHAR NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ClassesTable(){ //To be run after both the courses table and the modules table
sql = "CREATE TABLE Classes("\
"ID NOT NULL ,"\
"NAME NOT NULL ,"\
"ROOM NOT NULL ,"\
"Time NOT NULL,"\
"Location NOT NULL,"\
"Course ,"\
"Lecturer NOT NULL,"\
"Student_ID NOT NULL,"\
"MODULE NOT NULL);";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void StudentsTable(){ // To be run after courses table
sql = "CREATE TABLE Students("\
"STUDENT_ID INTEGER NOT NULL ,"\
"STUDENT_NAME VARCHAR NOT NULL ,"\
"Course VARCHAR,"\
"FOREIGN KEY (Course) REFERENCES Courses(Course_ID));";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ChecksNSubmission(char *query){
sqlite3_prepare(db, query, strlen(query), &stmt, nullptr);
rc = sqlite3_step(stmt);
sqlite3_finalize(stmt);
free(query);
DBErrors();
}//Prepares the sql stmt provided then runs it through SQLite
//This following section uses placeholders in order to take input and insert it into
//the relevant tables
void IntoModules(const char* ID, const char* NOM, const char* CID){ // To Be inserted second after courses
char *query = nullptr;
asprintf(&query, "INSERT INTO Modules (MODULE_ID, MODULE_NAME, Course) "
"VALUES ('%s','%s', '%s');",ID,NOM,CID);
ChecksNSubmission(query);
DBErrors();
}
void IntoClasses(const char* ID,const char* NOM,const char* RMNUM,const char* TIM,const char* LOC,
const char* MID,const char* CID,const char* LEC,const char* SID){ //To be inserted last
char *query = nullptr;
asprintf(&query, "INSERT INTO Classes (ID, NAME, ROOM, Time, Location, MODULE, Course, Lecturer, Student_ID) "
"VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s');",ID,NOM,RMNUM,TIM,LOC,MID,CID,LEC,SID);
ChecksNSubmission(query);
DBErrors();
}
void IntoStudents(const char* ID, const char* SN, const char* CS){ //To be run second or after courses table is filled
char *query = nullptr;
asprintf(&query, "INSERT INTO Students (STUDENT_ID, STUDENT_NAME, Course) "
"VALUES ('%s','%s','%s');",ID,SN,CS);
ChecksNSubmission(query);
}
void IntoCourses(const char* CID, const char* CN){ //To be inserted first
char *query = nullptr;
asprintf(&query, "INSERT INTO Courses (Course_ID, Course_Name) "
"VALUES ('%s','%s');",CID,CN);
ChecksNSubmission(query);
}
public:
TimeTable(){//This function is to automatically create the tables when initiating
//This Portion allows user to name the database
cout<<"What would you like to name the Database? : "<<endl;
string FileName ;
cin >> FileName;
rc = sqlite3_open( FileName.c_str(), &db);
DBErrors();
cout<<"Creating Courses Table"<<endl;
CoursesTable();
DBErrors();
cout<<"Creating Modules Table"<<endl;
ModulesTable();
DBErrors();
cout<<"Creating Students Table"<<endl;
StudentsTable();
DBErrors();
cout<<"Creating Classes Table"<<endl;
ClassesTable();
DBErrors();
}
//The following section has functions that ask the user for the inputs
//into the tables and then converts those inputs into a format that could
//be used by the function connected it in the private domain all of them will
//have something in common and that is that they all convert from input
//string to const char* which is needed in order to use the pointers set earlier
void FillModulesTable() {
int NumM;
int i = 0;
cout << "How many modules would you like to add to the record today? "<<endl;
cin >> NumM;
while (i != NumM) {
i++;
string I_D, N_OM, C_ID;
cout << "Please enter the module ID number for module number " << i << endl;
cin >>I_D ;
cout << "Please enter the name for module number " << i << endl;
cin >>N_OM ;
cout << "Please enter the Course ID in which a student would undertake module number " << i << endl;
cin >>C_ID;
const char* ID = I_D.c_str();
const char* NOM = N_OM.c_str();
const char* CID = C_ID.c_str();
IntoModules(ID,NOM,CID);
}
}
void FillClassesTable(){
int NumC;
int i = 0;
cout<<"How many classes would you like to add to the record today? "<<endl;
cin>>NumC;
while (i != NumC){
i++;
string id,nom,rmnum,tim,loc,mid,cid,lec,sid;
cout<<"Please enter the ID for class number "<<i<<endl;
cin>>id;
cout<<"Please enter the name of class number "<<i<<endl;
cin>>nom;
cout<<"Please enter the room number for class number "<<i<<endl;
cin>>rmnum;
cout<<"Please enter the time for class number "<<i<<endl;
cin>>tim;
cout<<"Please enter the module ID for class number "<<i<<endl;
cin>>mid;
cout<<"Please enter the course ID for class number "<<i<<endl;
cin>>cid;
cout<<"Please enter the lecturer's name for class number "<<i<<endl;
cin>>lec;
cout<<"Please enter the student ID to attend class number "<<i<<endl;
cin>>sid;
cout<<"Please enter the location of class number "<<i<<endl;
cin>>loc;
const char* LOC = loc.c_str();
const char* ID = id.c_str();
const char* NOM = nom.c_str();
const char* RMNUM = rmnum.c_str();
const char* TIM = tim.c_str();
const char* MID = mid.c_str();
const char* CID = cid.c_str();
const char* LEC = lec.c_str();
const char* SID = sid.c_str();
IntoClasses(ID,NOM,RMNUM,TIM,LOC,MID,CID,LEC,SID);
}
}
void FillStudentsTable(){
int NumS;
int i = 0;
cout<<"How many students would you like to add to the record today? "<<endl;
cin>>NumS;
while (i != NumS){
i++;
string id,sn,cs;
cout<<"Please enter the ID number for student number "<<i<<endl;
cin>> id;
cout<<"Please enter only the name of student number "<<i<<endl;
cin>> sn;
cout<<"Please enter the course ID of student number "<<i<<endl;
cin>> cs;
const char* ID = id.c_str();
const char* SN = sn.c_str();
const char* CS = cs.c_str();
IntoStudents(ID,SN,CS);
}
}
void FillCoursesTable(){
int NumC;
int i = 0;
cout <<"How many courses would you like to add to the registry today? "<<endl;
cin>>NumC;
while (i != NumC){
i++;
string cid,cn;
cout<<"Please enter the course ID for course number "<<i<<endl;
cin >> cid;
cout<<"Please enter the course name for course number "<<i<<endl;
cin >> cn;
const char* CID = cid.c_str();
const char* CN = cn.c_str();
IntoCourses(CID,CN);
}
}
//The following section includes functions that are solely asked to query
//data from the database and present it
void EnrolledStudents(){
sql = "SELECT * FROM Students";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void CoursesAvailable(){
sql = "SELECT * FROM Courses";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ModulesAvailable(){
sql = "SELECT * FROM Modules";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void ClassesAvailable(){
sql = "SELECT * FROM Classes";
rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
}
void StudentTimeTable(){
string sid;
cout<<"Please enter the ID of the student you would like to see the schedule of: "<<endl;
cin>>sid;
const char* SID = sid.c_str();
char *query = nullptr;
asprintf(&query, "SELECT NAME as Class_Name,"\
"Time as Class_Time,"\
"Location as Class_Location,"\
"MODULE_NAME as M_Name,"\
"Lecturer as Class_Lecturer,"\
"Course_Name as Course,"\
"Classes.Student_ID as Student_ID,"\
"S.STUDENT_NAME as STUDENT_NAME"\
"FROM Classes"\
"INNER JOIN Modules M on M.MODULE_ID = Classes.MODULE"\
"INNER JOIN Courses C on C.Course_ID = M.Course"\
"INNER JOIN Students S on S.STUDENT_ID = Classes.Student_ID"\
"WHERE Classes.Student_ID = '%s'",SID);
ChecksNSubmission(query);
}//This is the most important function as it queries the data
//that is only relevant to a certain student's ID
//in other words it is a certain student's timetable
void DeleteRow(const char* TableName, const char* ColoumnName, const char* ID){
char *query = nullptr;
asprintf(&query, "DELETE FROM ''%s'' WHERE '%s' = '%s';",TableName,ColoumnName,ID);
ChecksNSubmission(query);
}
void closeDB(){
sqlite3_close(db);
}
};