resit.cpp

#include <iostream>
#include <string>
#include <sqlite3.h>
#include <string.h>

using namespace std;

class DB {

    private:
                     
        sqlite3 *db;        
        char *zErrMsg;  
        int rc;     
        char* sql;        
        sqlite3_stmt *stmt;        

        static int callback(void *NotUsed, int argc/*holds the number of results*/, char **argv/*holds each value */, char **azColName/*holds each column returned*/) { 
            for(int i = 0; i < argc; i++) {             
                cout << azColName[i] << ": " << argv[i] << endl;            
            }
            cout << endl;     
            return 0; 
        }

        void checkDBErrors() {
            if( rc ){          
                cout << "DB Error: " << sqlite3_errmsg(db) << endl; //Error message        
                closeDB();        
            }
        }


    public:

        DB() {      
            rc = sqlite3_open("timetable.db", &db); 
            checkDBErrors();        
        }

        void createStudent() {     
            sql = "CREATE TABLE STUDENTS ( STUDENT_ID INT(7) PRIMARY KEY, STUDENT_NAME VARCHAR(50), STUDENT_PHONE_NUMBER INT(11), STUDENT_ADDRESS VARCHAR(50), COURSE_ID INT(1), FOREIGN KEY (COURSE_ID) REFERENCES COURSE (COURSE_ID));";                     
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);   // Execute the SQL        
        }

        void createCourse(){
			sql = " CREATE TABLE COURSE ( COURSE_ID INT(1) PRIMARY KEY, COURSE_NAME VARCHAR(50), MODULE_ID VARCHAR(7), FOREIGN KEY (MODULE_ID) REFERENCES MODULES (MODULES_ID))";
			rc = sqlite3_exec(db,sql,callback,0,&zErrMsg);
		}

        void createModules() {
            sql = "CREATE TABLE MODULES ( MODULE_ID VARCHAR(7) PRIMARY KEY, MODULE_NAME VARCHAR(50), CLASSES_ID INT, ROOMS_ID VARCHAR, LECTURER_ID INT(7), COURSE_ID INT(1), FOREIGN KEY (CLASSES_ID) REFERENCES CLASSES(CLASSES_ID), FOREIGN KEY (ROOMS_ID) REFERENCES ROOMS(ROOMS_ID), FOREIGN KEY (LECTURER_ID) REFERENCES LECTURER(LECTURER_ID), FOREIGN KEY (COURSE_ID) REFERENCES COURSE(COURSE_ID));";           
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); 
        }

        void createClasses(){
            sql = "CREATE TABLE CLASSES ( CLASSES_ID INT PRIMARY KEY, CLASSES_DESCRIPTION VARCHAR(100), MODULE_ID VARCHAR(7), FOREIGN KEY (MODULE_ID) REFERENCES MODULES(MODULE_ID))";
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
        }

        void createLecturer(){
            sql = "CREATE TABLE LECTURER (LECTURER_ID INT PRIMARY KEY, LECTURER_NAME VARCHAR(20), MODULE_ID VARCHAR(7), FOREIGN KEY (MODULE_ID) REFERENCES MODULES(MODULE_ID));";                 
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); 
        }

        void createRoom(){
            sql = "CREATE TABLE ROOMS (ROOMS_ID VARCHAR PRIMARY KEY, ROOMS_NAME VARCHAR(20), MODULE_ID VARCHAR(7), FOREIGN KEY (MODULE_ID) REFERENCES MODULES(MODULE_ID));";                 
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg); 
        }

        void insertStudents(char* ID, char* NAME, char* PHONE_NUMBER, char* address, char* course) {
            char *query = NULL;         
            asprintf(&query, "INSERT INTO STUDENTS ('STUDENT_ID', 'STUDENT_NAME', 'STUDENT_PHONE_NUMBER', 'STUDENT_ADDRESS', 'COURSE_ID') VALUES  ('%s', '%s' , '%s', '%s', '%s');", ID,NAME,PHONE_NUMBER,address, course);          
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);    
            sqlite3_finalize(stmt);         
            free(query); 
        }

        void insertCourse (char* ID, char* name, char* module){
            char *query = NULL;
            asprintf(&query, "INSERT INTO COURSE ('COURSE_ID', 'COURSE_NAME', 'MODULE_ID') VALUES ('%s', '%s', '%s');", ID,name,module);
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);     
            sqlite3_finalize(stmt);          
            free(query);     
        }

        void insertModules (char* ID, char* name, char* classes, char* rooms, char* lecturer, char* course){
            char *query = NULL;
            asprintf(&query, "INSERT INTO MODULES ('MODULE_ID', 'MODULE_NAME', 'CLASSES_ID', 'ROOMS_ID', 'LECTURER_ID', 'COURSE_ID') VALUES ('%s', '%s', '%s','%s', '%s', '%s');", ID,name,classes,rooms,lecturer,course);
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);     
            sqlite3_finalize(stmt);          
            free(query);     
        }

        void insertClasses (char* ID, char* description, char* module){
            char *query = NULL;
            asprintf(&query, "INSERT INTO CLASSES ('CLASSES_ID', 'CLASSES_DESCRIPTION', 'MODULE_ID') VALUES ('%s', '%s', '%s');", ID,description,module);
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);     
            sqlite3_finalize(stmt);          
            free(query);     
        }

        void insertLecturer (char* ID, char* name, char* module){
            char *query = NULL;
            asprintf(&query, "INSERT INTO LECTURER ('LECTURER_ID', 'LECTURER_NAME', 'MODULE_ID') VALUES ('%s', '%s', '%s');", ID,name,module);
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);     
            sqlite3_finalize(stmt);          
            free(query);     
        }

        void insertRoom (char* ID, char* name, char* module){
            char *query = NULL;
            asprintf(&query, "INSERT INTO ROOMS ('ROOMS_ID', 'ROOMS_NAME', 'MODULE_ID') VALUES ('%s', '%s', '%s');", ID,name,module);
            sqlite3_prepare(db, query, strlen(query), &stmt, NULL);   
            rc = sqlite3_step(stmt);     
            sqlite3_finalize(stmt);          
            free(query);     
        }

        void showTable() {          
            sql = "SELECT * FROM 'STUDENTS' ;";             
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);   
            sql = "SELECT * FROM 'COURSE';";        
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
            sql = "SELECT * FROM 'MODULES';";             
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
            sql = "SELECT * FROM 'CLASSES';";              
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
            sql = "SELECT * FROM 'LECTURER';";              
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
            sql = "SELECT * FROM 'ROOMS';";             
            rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);
        }

        void closeDB() {            
            sqlite3_close(db);    // Close the SQL connection
        }
};

int main() {   
    DB sqldb;
    sqldb.createStudent();
    sqldb.insertStudents("8898445", "Bruno Rodrigues", "07446675783", "25 gulson road", "1");
    sqldb.insertStudents("7463748", "Madalena Costa", "07654362781", "25 london road", "2");
    sqldb.createCourse();
    sqldb.insertCourse("1", "Computer Science", "1");
    sqldb.insertCourse("2", "Nutrition and Health", "2");
    sqldb.createModules();
    sqldb.insertModules("1", "5002CEM Theory of Computation", "1", "ASG31", "1", "1");
    sqldb.insertModules("2", "Introduction to Food Law and Policy", "2", "WM209", "2", "2");
    sqldb.createClasses();
    sqldb.insertClasses("1","Thursday","1");
    sqldb.insertClasses("2", "Mondays","2");
    sqldb.createLecturer();
    sqldb.insertLecturer("1","Kevin Garnett","1");
    sqldb.insertLecturer("2","Ray Allen","2");
    sqldb.createRoom();
    sqldb.insertRoom("ASG31", "Armstrong","1");
    sqldb.insertRoom("WM209","William Morris","2");
    sqldb.showTable();
    sqldb.closeDB();  
    return 0;
}