create_tables.cpp

#include <iostream> 
#include <sqlite3.h> //using sqlite3 library for database integration
  
int main() 
{ 
    sqlite3* db; 
    std::string sql = "CREATE TABLE Rooms("
        "room_id CHAR(10) PRIMARY KEY NOT NULL,"
        "room_type CHAR(10)"
        ");"
        "CREATE TABLE Lecturers("
        "lecturer_id INT PRIMARY KEY NOT NULL,"
        "lecturer_name CHAR(50) NOT NULL"
        ");"
        "CREATE TABLE Students("
        "student_id INT PRIMARY KEY NOT NULL,"
        "student_name CHAR(50) NOT NULL"
        ");"
        "CREATE TABLE Modules("
        "module_code CHAR(10) PRIMARY KEY NOT NULL,"
        "module_name CHAR(50) NOT NULL"
        ");"
        "CREATE TABLE Classes("
        "class_id INT PRIMARY KEY NOT NULL,"
        "room_id CHAR(10) NOT NULL,"
        "lecturer_id INT NOT NULL,"
        "module_code CHAR(10) NOT NULL,"
        "class_type CHAR(10),"
        "time_24hrs INT NOT NULL,"
        "length_hrs INT NOT NULL,"
        "FOREIGN KEY (room_id) REFERENCES Rooms(room_id),"
        "FOREIGN KEY (lecturer_id) REFERENCES Lecturers(lecturer_id),"
        "FOREIGN KEY (module_code) REFERENCES Modules(module_code)"
        ");"
        "CREATE TABLE Class_students("
        "student_id INT NOT NULL,"
        "class_id INT NOT NULL,"
        "PRIMARY KEY (student_id, class_id),"
        "FOREIGN KEY (student_id) REFERENCES Students(student_id),"
        "FOREIGN KEY (class_id) REFERENCES Classes(class_id)"
        ");";
    int exit = 0; 
    char* sqlErrorMessage;

    //open connection to database
    exit = sqlite3_open("data.db", &db); //will return 0 if succeeded
    //check connection was successful before continuing
    //example code taken from https://www.geeksforgeeks.org/sql-using-c-c-and-sqlite/
    if (exit) { 
        std::cerr << "Error opening database: " << sqlite3_errmsg(db) << std::endl; 
        return (-1); 
    } 
    else
        std::cout << "Opened Database Successfully!" << std::endl; 
    //end of example code
    
    //execute sql commands
    //sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void *data, char **errmsg)
    //arguments taken from https://www.geeksforgeeks.org/sql-using-c-c-and-sqlite/
    exit = sqlite3_exec(db, sql.c_str(), NULL, 0, &sqlErrorMessage );
    //report back to user if execution was successful, and if not why
    if (exit != SQLITE_OK) {
        std::cerr << "Some errors occured when creating tables:" << std::endl;
        std::cerr << sqlErrorMessage << std::endl;
    }
    else
        std::cout << "Tables created successfully." << std::endl;

    sqlite3_close(db); //close connection to the database once finished
    return (0); 
}