1Cd102aE

1void dbCreateTable(sqlite3* db, const std::string tableName) {
2    char* errormsg;
3    std::string createTable = "CREATE TABLE IF NOT EXISTS "+ tableName +"("
4                              "UserId INT PRIMARY KEY NOT NULL,"
5                              "UserName TEXT NOT NULL,"
6                              "Points INT NOT NULL,"
7                              "GamePlayed INT NOT NULL,"
8                              "GamesWon INT NOT NULL);";
9
10    int createTableStatus = sqlite3_exec(db, createTable.c_str(), NULL, 0, &errormsg);
11    if (createTableStatus != SQLITE_OK){
12        std::cout << "Table could not be created" <<sqlite3_errmsg(db) << std::endl;
13    }
14}
15
16void dbInsertPlayer(sqlite3* db, const std::string table, const std::string playerName, const std::string id){
17    std::string insertStatement = "INSERT INTO " + table + " VALUES (" + id + ", '" + playerName + "', 0, 0, 0);";
18    char* errormsg;
19    int insertStatus = sqlite3_exec(db, insertStatement.c_str(), NULL, 0, &errormsg);
20    if (insertStatus != SQLITE_OK){
21        std::cout << "DB unable to insert:  " << sqlite3_errmsg(db) << std::endl;
22    }
23}
24
25static int callback(void* data, int rows, char** argv, char** azColName){
26    if (data != NULL) {
27        //dbSelectValues called
28        std::vector<std::string>& dataVect = *reinterpret_cast<std::vector<std::string>*>(data);
29        for (int i = 0; i < rows; i++) {
30            dataVect.push_back(argv[i]);
31        }
32    }
33    else {
34        //stdout print called
35        for (int i = 0; i < rows; i++) {
36            printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
37        }
38        printf("\n");
39    }      
40    return 0;
41}
42
43void dbDeletePlayer(sqlite3* db, const std::string table, const std::string id){
44    std::string deleteStatement = "DELETE FROM " + table + " WHERE UserID = " + id + ";";
45    char* errorMsg;
46    int deleteStatus = sqlite3_exec(db, deleteStatement.c_str(), callback, NULL, &errorMsg);
47    if (deleteStatus != SQLITE_OK){
48        std::cout << "delete unsucessful: " << errorMsg << std::endl;
49    }
50}
51
52/*
53accepts pointer to database (db), table name in db (table), vector of columns and respective values to populate SQL WHERE (where_col, where_list), column (set_col) and a vector 
54of values (set_list) ordered as where_list to populate SQL SET
55dbUpdatePlayer(DB, "PLAYERS", "PlayerName", {"billy","bob"}, "Points", "2")
56= SQL: UPDATE PLAYERS SET Points = 2 WHERE PlayerName = billy
57= SQL: UPDATE PLAYERS SET Points = 2 WHERE PlayerName = bob
58*/  
59void dbUpdatePlayer(sqlite3* db, const std::string table, const std::string where_col, const std::vector<std::string>& where_list, const std::string set_col, const std::string set_val) {
60    int i = 0;
61    char* errorMsg;
62    for(std::string n : where_list) {
63        std::string updateStatement = "UPDATE " + table + " SET " + set_col + " = " + set_val + " WHERE " + where_col + " = " + n + ";";
64        i++;
65        int updateStatus = sqlite3_exec(db, updateStatement.c_str(), callback, NULL, &errorMsg);
66        if (updateStatus != SQLITE_OK){
67            std::cout << "update unsucessful: " << errorMsg << std::endl;
68        }
69    }
70}
71
72/*
73accepts pointer to database (db), vector to store SELECT return values (data), vector of columns and respective values to populate SQL WHERE (where_col, where_list), and vector 
74of columns (select_list) whose values are to return to the caller (pushed into data)
75dbUpdatePlayer(DB, "PLAYERS", {"billy","bob"}, Points, "2", {"PlayerName","PlayerID"})
76= data Vector populated with: {"billy","1"} 
77*/ 
78void dbSelectValues(sqlite3* db, const std::string table, std::vector<std::string>& data, const std::string where_col, const std::string where_val, const std::vector<std::string>& select_list) {
79    char* errormsg;
80    std::string selectCols;
81    //take list of columns to select and concatenate them for SQL SELECT statement
82    for(auto n : select_list) {
83        selectCols += n + ", ";
84    }
85    selectCols.pop_back(); //remove last " " from string
86    selectCols.pop_back(); //remove last "," from string
87    //std::cout << "dbSelectValues: selectCols = " << selectCols << std::endl;
88    std::string selectStatement = "SELECT " + selectCols + " FROM " + table + " WHERE " + where_col + " = " + where_val + ";";
89    int selectStatus = sqlite3_exec(db, selectStatement.c_str(), callback, static_cast<void*>(&data), &errormsg);
90        if (selectStatus != SQLITE_OK){
91            std::cout << "Select could not be performed: " <<sqlite3_errmsg(db) << std::endl;
92        }
93}