void Database::updatePlayer(Player *player)
{
if (!isConnected())
init();
if (!m_isConnected)
return;
PreparedStatement *statement = 0;
try
{
statement = m_database->prepareStatement("UPDATE accounts SET kills = ?, deaths = ? WHERE id = ?");
statement->setInt(1, player->kills());
statement->setInt(2, player->deaths());
statement->setInt(3, player->userId());
statement->executeQuery();
}
catch (SQLException &e)
{
emit writeToConsole("Query failed(updatePlayer): " + QLatin1String(e.getSQLStateCStr()));
}
delete statement;
}
/**
* Insert the stat into the database
*/
int StatController::AddStat(Stat& stat)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO stats (users, sheets, feeds, items, comments) VALUES (?,?,?,?,?)");
//Populate the query based on the passed stat
stmt->setInt(1, stat.users);
stmt->setInt(2, stat.sheets);
stmt->setInt(3, stat.feeds);
stmt->setInt(4, stat.items);
stmt->setInt(5, stat.comments);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted stat
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
unsigned int CallManager::storeCall(Connection *sqlCon, bool phone, unsigned int client, unsigned int translator)
{
if (!sqlCon)
return 0;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"INSERT INTO calls SET phone=(?), client=(?), translator=(?), request_time=NOW()");
pstmt->setInt(1, phone);
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
try {
pstmt->execute();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
pstmt = sqlCon->prepareStatement("SELECT LAST_INSERT_ID()");
ResultSet *res;
try {
res = pstmt->executeQuery();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
res->first();
unsigned int id = res->getInt(1);
delete res;
return id;
}
void MergedAuthorDao::add(vector<int>& froms, int to)
{
Connection* conn = NULL;
PreparedStatement* stat = NULL;
try
{
conn = ConnectionPool::getInstance()->getConnection();
conn->setAutoCommit(false);
stat = conn->prepareStatement("insert into na_author_map(from_naid,to_naid) values(?,?)");
stat->setInt(2, to);
for (int from : froms)
{
stat->setInt(1, from);
stat->executeUpdate();
}
conn->commit();
conn->setAutoCommit(true);
ConnectionPool::close(conn, stat, NULL);
}
catch (sql::SQLException &e)
{
LOG(ERROR) << boost::str(boost::format("# ERR: SQLException in %1% %2%") % __FILE__ %__FUNCTION__);
LOG(ERROR) << boost::str(boost::format("%1% error code %2%") %e.what() % e.getErrorCode());
}
}
/**
* Update the relevant sheet in the database
*/
void SheetController::UpdateSheet(Sheet& sheet)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE Sheets SET name = ?, username = ?, updated = ?, layoutid = ? WHERE id = ?");
//Populate the query with the values from the passed sheet
stmt->setString(1, sheet.name);
stmt->setString(2, sheet.username);
stmt->setString(3, sheet.updated.ExportToMySQL());
stmt->setInt(4, sheet.layoutId);
stmt->setInt(5, sheet.id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant content placeholder in the database
*/
void ContentPlaceholderController::UpdateContentPlaceholder(ContentPlaceholder& cph)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE cphs SET sheetid = ?, type = ?, column = ?, order = ? WHERE id = ?");
//Update with all parameters
stmt->setInt(1, cph.sheetId);
stmt->setInt(2, cph.type);
stmt->setInt(3, cph.column);
stmt->setInt(4, cph.order);
stmt->setInt(5, cph.id);
stmt->executeUpdate();
delete stmt;
}
int Call::DBwrite(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?) WHERE id=(?)");
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
pstmt->setInt(1, false);
pstmt->setString(4, COUNTRY_UNKNOWN);
pstmt->setString(5, COUNTRY_UNKNOWN);
pstmt->setString(6, translateLang.c_str());
pstmt->setInt(7, price);
char *time = asctime(localtime(&start_time));
if (start_time)
pstmt->setDateTime(8, time);
else
pstmt->setNull(8, 0);
pstmt->setInt(9, accounted);
pstmt->setInt(10, cost);
pstmt->setInt(11, getState() == ERROR);
pstmt->setInt(12, id);
int ret;
try {
ret = pstmt->executeUpdate();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
return ret == 1;
}
/**
* Update the relevant stat in the database
*/
void StatController::UpdateStat(Stat& stat)
{
PreparedStatement* stmt = conn->prepareStatement("UPDATE stats SET users = ?, sheets = ?, feeds = ?, items = ?, comments = ? WHERE id = ?");
//Populate the query values using the passed stat
stmt->setInt(1, stat.users);
stmt->setInt(2, stat.sheets);
stmt->setInt(3, stat.feeds);
stmt->setInt(4, stat.items);
stmt->setInt(5, stat.comments);
stmt->setInt(6, stat.id);
stmt->executeUpdate();
delete stmt;
}
void StatisticDAO::insert(const tableLogData& data)
{
PreparedStatement* insertRow;
PreparedStatement* queryID;
switch (data.playerNums)
{
case 6:
insertRow = connection->prepare("insert into TableLog values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
break;
case 4:
insertRow = connection->prepare("insert into TableLog values(null,?,?,?,?,?,?,?,?,?,?,?,?,null,null)");
break;
}
int lastID=0;
insertRow->setInt(TABLELOG_COL_TABLEMODE, data.tableMode);
insertRow->setInt(TABLELOG_COL_PLAYERNUMS, data.playerNums);
insertRow->setInt(TABLELOG_COL_WINNER, data.winner);
insertRow->setInt(TABLELOG_COL_REDSCORE, data.redScore);
insertRow->setInt(TABLELOG_COL_BLUESCORE, data.blueScore);
insertRow->setInt(TABLELOG_COL_REDCUPNUM, data.redCupNum);
insertRow->setInt(TABLELOG_COL_BLUECUPNUM, data.blueCupNum);
insertRow->setString(TABLELOG_COL_CREATETIME, data.createTime);
for (int i = 0; i < data.playerNums; i++)
insertRow->setString(TABLELOG_COL_PLAYERID + i, data.tableDetail[i].playerID); //Ö÷±íID¼Ç¼
connection->executeUpdate(insertRow);
queryID = connection->prepare("select LAST_INSERT_ID() from tableLog");
ResultSet* res=connection->executeQuery(queryID);
if (res->next()) { lastID=res->getInt(1); }
for (int i = 0; i < data.playerNums; i++)
{
insertRow = connection->prepare("insert into TableDetail values(null,?,?,?,?,?,?)");
insertRow->setInt(TABLEDETAIL_COL_TABLEID, lastID);
insertRow->setString(TABLEDETAIL_COL_PLAYERID, data.tableDetail[i].playerID);
insertRow->setInt(TABLEDETAIL_COL_PLAYERSERIAL, data.tableDetail[i].playerSerial);
insertRow->setInt(TABLEDETAIL_COL_TEAM, data.tableDetail[i].team);
insertRow->setInt(TABLEDETAIL_COL_ROLE, data.tableDetail[i].role);
insertRow->setInt(TABLEDETAIL_COL_RESULT, data.tableDetail[i].result);
connection->executeUpdate(insertRow);
}
};
/**
* Find the content placeholder with that id
*/
ContentPlaceholder* ContentPlaceholderController::GetContentPlaceholderById(int id)
{
PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM cph WHERE id = ?");
stmt->setInt(1, id);
ResultSet* rs = stmt->executeQuery();
delete stmt;
if(rs != NULL)
{
while(rs->next())
{
//Return the content placeholder retrieved by the query
ContentPlaceholder* c = GenerateContentPlaceholder(*rs);
delete rs;
return c;
}
}
else
{
return NULL;
}
return NULL;
}
/**
* Find all content placeholders that belong to the sheet of that id
*/
vector<ContentPlaceholder*> ContentPlaceholderController::GetContentPlaceholdersBySheetId(int sheetId)
{
PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM cph WHERE sheetid = ?");
stmt->setInt(1, sheetId);
ResultSet* rs = stmt->executeQuery();
delete stmt;
vector<ContentPlaceholder*> cphs;
if(rs != NULL)
{
while(rs->next())
{
//For all content placeholders matching the query, add them to the vector
cphs.push_back(GenerateContentPlaceholder(*rs));
}
delete rs;
}
return cphs;
}
/**
* Find the stat with that id
*/
Stat* StatController::GetStatById(int id)
{
PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM stats WHERE id = ?");
stmt->setInt(1, id);
ResultSet* rs = stmt->executeQuery();
delete stmt;
if(rs != NULL)
{
while(rs->next())
{
//If a stat matches this id, return it
Stat* s = GenerateStat(*rs);
delete rs;
return s;
}
}
else
{
return NULL;
}
return NULL;
}
void Database::updatePlayerCount(int count)
{
if (!isConnected())
init();
if (!m_isConnected)
return;
PreparedStatement *statement = 0;
try
{
statement = m_database->prepareStatement("UPDATE servers SET connectedPlayers = ? WHERE id = ?");
statement->setInt(1, count);
statement->setString(2, m_serverID.toStdString());
statement->executeQuery();
}
catch (SQLException &e)
{
emit writeToConsole("Query failed(updatePlayerCount): " + QLatin1String(e.getSQLStateCStr()));
}
delete statement;
}
/**
* Insert the sheet into the database
*/
int SheetController::AddSheet(Sheet& sheet)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO sheets (name, username, layoutid) VALUES (?,?,?)");
//Populate the query with the values from the passed sheet
stmt->setString(1, sheet.name);
stmt->setString(2, sheet.username);
stmt->setInt(3, sheet.layoutId);
//Insert
stmt->executeUpdate();
delete stmt;
//Create another query to get the ID of the inserted sheet
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
/**
* Find the layout with that id
*/
Layout LayoutController::GetLayoutById(int id)
{
PreparedStatement* stmt = conn->prepareStatement("SELECT * FROM layouts WHERE id = ?");
stmt->setInt(1, id);
ResultSet* rs = stmt->executeQuery();
delete stmt;
if(rs != NULL)
{
while(rs->next())
{
//If a layout matches that id, return it
return GenerateLayout(*rs);
}
}
else
{
Layout l;
return l;
}
Layout l;
return l;
}
int main()
{
try
{
clock_t tStart = clock();
readConfFile();
cout<<"Started the program\n";
//cout<<tabIndex;
DBConnection *squidLog = new DBConnection();
squidLog->dbConnOpen("127.0.0.1","3306","root","simple","squid");
string logReadQuery = "select * from access_log_new where id > ?;";
PreparedStatement *pstm = squidLog->conn->prepareStatement(logReadQuery);
pstm->setInt(1,tabIndex);
squidLog->res = pstm->executeQuery();
/*DBConnection *squidLog = new DBConnection();
squidLog->dbConnOpen("127.0.0.1","3306","root","simple","squid");
squidLog->tableName = "access_log";
squidLog->setReadPstmt(1,squidLog->tableName,"","");
squidLog->readTable();*/
statLog = new DBConnection();
createStatistics(squidLog,statLog);
/*grossStatisticsAcc(statLog->tableNameAcc);
grossStatisticsDen(statLog->tableNameDen);
createDomainStatisticsAcc(statLog->tableNameAcc);
createUserStatisticsAcc(statLog->tableNameAcc);
createDomainStatisticsDen(statLog->tableNameDen);
createUserStatisticsDen(statLog->tableNameDen);*/
writeConfFile();
printf("Time taken: %.2fs\n", (double)(clock() - tStart)/CLOCKS_PER_SEC);
cout<<"End Of program \n";
}
catch (sql::SQLException &e)
{
cout << "# ERR: SQLException in " << __FILE__;
cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}
catch (exception& e)
{
cout << "# ERR File: " << __FILE__;
cout << "(" << __FUNCTION__ << ") on line " << __LINE__ << endl;
cout << e.what() << '\n';
}
return 0;
}
/**
* Delete the layout from the database
*/
void LayoutController::RemoveLayout(Layout& layout)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM layouts WHERE id = ?");
stmt->setInt(1, layout.id);
//Delete by ID
stmt->executeUpdate();
}
/**
* Delete the stat from the database
*/
void StatController::RemoveStat(int id)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM stats WHERE id = ?");
stmt->setInt(1, id);
//Delete by ID
stmt->executeUpdate();
delete stmt;
}
/**
* Delete the content placeholder from the database
*/
void ContentPlaceholderController::RemoveContentPlaceholder(int id)
{
PreparedStatement* stmt = conn->prepareStatement("DELETE FROM cph WHERE id = ?");
stmt->setInt(1, id);
//Remove the record by ID
stmt->executeUpdate();
delete stmt;
}
vector<map<string, string>> retrievePendingMessages(Document & request)
{
int recvid= validateToken(request["usertoken"].GetString());
try
{
PreparedStatement * prepstmt;
prepstmt = conn->prepareStatement("select senderuid, receiveruid, msg, datesent from msgs where (receiveruid=? and status=0);");
prepstmt->setInt(1, recvid);
ResultSet * rs;
rs = prepstmt->executeQuery();
prepstmt = conn->prepareStatement("update msgs set status=1 where receiveruid=?;"); //ATTENTION! Threading error can occur!
prepstmt->setInt(1, recvid);
prepstmt->executeUpdate();
conn->commit();
ResultSet * namers;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, recvid);
namers = prepstmt->executeQuery();
namers->next();
string receiver = namers->getString("login");
vector<map<string, string>> msgs;
while(rs->next()){
map<string, string> onemsg;
prepstmt = conn->prepareStatement("select login from users where id=?;");
prepstmt->setInt(1, rs->getInt("senderuid"));
namers = prepstmt->executeQuery();
namers->next();
onemsg.insert(make_pair("sender", namers->getString("login")));
onemsg.insert(make_pair("receiver", receiver));
onemsg.insert(make_pair("message", rs->getString("msg")));
onemsg.insert(make_pair("datesent", rs->getString("datesent")));
//cout << namers->getString("login") << " " << rs->getString("msg") << endl;
msgs.push_back(onemsg);
}
return msgs;
}
catch(exception e)
{
throw 2;
}
}
/*
* Overload Function: setup_test_table
*/
void setup_test_table(Connection *con, int numOfPocs, int sensorsPerPoc) {
int i, j;
Statement *stmt;
PreparedStatement *pstmt;
try {
stmt = con->createStatement();
stmt->execute("DROP TABLE IF EXISTS ParkingSpot");
stmt->execute("CREATE TABLE ParkingSpot ("
"PocID int NOT NULL,"
"SensorID int NOT NULL,"
"SpotStatus int NOT NULL,"
"StartTime datetime DEFAULT NULL,"
"ExpireTime datetime DEFAULT NULL,"
"PRIMARY KEY (PocID, SensorID),"
"UNIQUE KEY LocationSensor_UNIQUE (PocID, SensorID))");
delete stmt;
pstmt = con->prepareStatement("INSERT INTO ParkingSpot (PocID, SensorID, SpotStatus) VALUES (?, ?, 0)");
for(i = 0; i < numOfPocs; i++)
{
pstmt->setInt(1, i);
for(j = 0; j < sensorsPerPoc; j++)
{
pstmt->setInt(2, j);
pstmt->execute();
}
}
} catch (sql::SQLException &e) {
cout << "# ERR: SQLException in " << __FILE__;
cout << " (function: " << __FUNCTION__ << ")" << endl;
cout << "# ERR: " << e.what();
cout << " (MySQL error code: " << e.getErrorCode();
cout << ", SQLState: " << e.getSQLState() << " )" << endl;
}
delete pstmt;
}
/**
* Insert the content placeholder into the database
*/
int ContentPlaceholderController::AddContentPlaceholder(ContentPlaceholder& cph)
{
PreparedStatement* stmt = conn->prepareStatement("INSERT INTO cphs (sheetid, type, column, order) VALUES (?,?,?,?)");
//Set provided parameters
stmt->setInt(1, cph.sheetId);
stmt->setInt(2, cph.type);
stmt->setInt(3, cph.column);
stmt->setInt(4, cph.order);
//Insert
stmt->executeUpdate();
delete stmt;
//Perform another query to get the index of the inserted content placeholder
Statement* lastStmt = conn->createStatement();
ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()");
if(rs != NULL)
{
while(rs->next())
{
int lastId = rs->getInt("LAST_INSERT_ID()");
delete rs;
delete lastStmt;
return lastId;
}
}
else
{
delete lastStmt;
return -1;
}
return -1;
}
int PhoneCall::DBwrite(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?), request_time=(?), confirm_time=(?), accepted=(?) WHERE id=(?)");
pstmt->setInt(1, true);
pstmt->setInt(2, client);
pstmt->setInt(3, translator);
pstmt->setString(4, ((PhoneCall *)this)->getClientCountry().c_str());
pstmt->setString(5, ((PhoneCall *)this)->getTranslatorCountry().c_str());
pstmt->setString(6, translateLang.c_str());
pstmt->setInt(7, price);
char time[512];
strftime(time, 512, SQLTIME_FMT, localtime(&start_time));
if (start_time)
pstmt->setDateTime(8, time);
else
pstmt->setNull(8, 0);
pstmt->setInt(9, getAccountedTime());
pstmt->setInt(10, cost);
pstmt->setInt(11, getState() == ERROR);
strftime(time, 512, SQLTIME_FMT, localtime(&request_time));
if (request_time)
pstmt->setString(12, time);
else
pstmt->setNull(12, 0);
strftime(time, 512, SQLTIME_FMT, localtime(&confirm_time));
if (confirm_time)
pstmt->setDateTime(13, time);
else
pstmt->setNull(13, 0);
pstmt->setBoolean(14, accepted);
pstmt->setInt(15, id);
int ret;
try {
ret = pstmt->executeUpdate();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
return ret == 1;
}
int PhoneCall::DBread(Connection *sqlCon)
{
if (!sqlCon || !id)
return -1;
PreparedStatement *pstmt = sqlCon->prepareStatement(
"SELECT phone, client, translator, client_country, translator_country, lang, price, start_time, accounted, cost, error, request_time, confirm_time, accepted FROM calls WHERE id=(?)");
pstmt->setInt(1, id);
ResultSet *res;
try {
res = pstmt->executeQuery();
} catch (SQLException &ex) {
log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what());
delete pstmt;
return 0;
}
delete pstmt;
if (res->rowsCount() != 1)
return -1;
res->first();
if (!res->getInt("phone")) {
delete res;
return -1;
}
client = res->getInt("client");
translator = res->getInt("translator");
translateLang = res->getString("lang").c_str();
price = res->getInt("price");
const char *time = res->getString("start_time").c_str();
if (strlen(time) > 0)
start_time = mktime(getdate(time));
accounted = res->getInt("accounted");
cost = res->getInt("cost");
if (res->getInt("error"))
state = ERROR;
time = res->getString("request_time").c_str();
if (strlen(time) > 0)
request_time = mktime(getdate(time));
time = res->getString("confirm_time").c_str();
if (strlen(time) > 0)
confirm_time = mktime(getdate(time));
accepted = res->getBoolean("accepted");
setClientCountry(res->getString("client_country").c_str());
setTranslatorCountry(res->getString("translator_country").c_str());
delete res;
return 0;
}
string generateToken(string login)
{
try
{
PreparedStatement * prepstmt;
prepstmt = conn -> prepareStatement("delete from tokens where userid=(select id from users where login=?);");
prepstmt->setString(1, login);
prepstmt->executeUpdate();
prepstmt = conn->prepareStatement("insert into tokens (userid, token, expiry) values ((select id from users where login=?), ?, now()+interval ? hour);");
string token = genRandomAlphanumericStr(TOKEN_SIZE);
prepstmt->setString(1, login);
prepstmt->setString(2, token);
prepstmt->setInt(3, TOKEN_VALID_TIME);
prepstmt->executeUpdate();
conn->commit();
return token;
}
catch(exception e)
{
throw 2;
}
}
/**
* Update the relevant sheet in the database, NULLs passed if that parameter is not to be updated
*/
void SheetController::UpdateSheet(int id, string* name, string* username, nfrd::misc::DateTime* updated, int* layoutId)
{
//Generate the update query
string query = "UPDATE Sheets SET ";
//If the value is null, set it to the existing value
if(name != NULL)
{
query += "name = ?";
}
else
{
query += "name = name";
}
if(username != NULL)
{
query += ", username = ?";
}
else
{
query += ", username = username";
}
if(updated != NULL)
{
query += ", updated = ?";
}
else
{
query += ", updated = updated";
}
if(layoutId != NULL)
{
query += ", layoutid = ?";
}
else
{
query += ", layoutid = layoutid";
}
query += " WHERE id = ?";
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
//Add the values to the query
if(name != NULL)
{
stmt->setString(param, *name);
param++;
}
if(username != NULL)
{
stmt->setString(param, *username);
param++;
}
if(updated != NULL)
{
stmt->setString(param, updated->ExportToMySQL());
param++;
}
if(layoutId != NULL)
{
stmt->setInt(param, *layoutId);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant content placeholder in the database, NULLs passed if that parameter is not to be updated
*/
void ContentPlaceholderController::UpdateContentPlaceholder(int id, int* sheetId, int* wid, bool* column, int* order)
{
string query = "UPDATE cph SET ";
//For each parameter, check if it is provided, if it is add to the UPDATE statement
if(sheetId != NULL)
{
query += "sheetid = ?";
}
else
{
query += "sheetid = sheetid";
}
if(wid != NULL)
{
query += ", wid = ?";
}
else
{
query += ", wid = wid";
}
if(column != NULL)
{
query += ", column = ?";
}
else
{
query += ", column = column";
}
if(order != NULL)
{
query += ", order = ?";
}
else
{
query += ", order = order";
}
query += " WHERE id = ?";
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
//Populate the parameters in the statement
if(sheetId != NULL)
{
stmt->setInt(param, *sheetId);
param++;
}
if(wid != NULL)
{
stmt->setInt(param, *wid);
param++;
}
if(column != NULL)
{
stmt->setInt(param, *column);
param++;
}
if(order != NULL)
{
stmt->setInt(param, *order);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
/**
* Update the relevant stat in the database, NULLs passed if that parameter is not to be updated
*/
void StatController::UpdateStat(int id, int* users, int* sheets, int* feeds, int* items, int* comments)
{
//Generate the update query
string query = "UPDATE stats SET ";
//If values are null, set the columns to equal their existing values
if(users != NULL)
{
query += "users = ?";
}
else
{
query += "users = users";
}
if(sheets != NULL)
{
query += ", sheets = ?";
}
else
{
query += ", sheets = sheets";
}
if(feeds != NULL)
{
query += ", feeds = ?";
}
else
{
query += ", feeds = feeds";
}
if(items != NULL)
{
query += ", items = ?";
}
else
{
query += ", items = items";
}
if(comments != NULL)
{
query += ", comments = ?";
}
else
{
query += ", comments = comments";
}
query += " WHERE id = ?";
//Add the values to the statement
PreparedStatement* stmt = conn->prepareStatement(query);
int param = 1;
if(users != NULL)
{
stmt->setInt(param, *users);
param++;
}
if(sheets != NULL)
{
stmt->setInt(param, *sheets);
param++;
}
if(feeds != NULL)
{
stmt->setInt(param, *feeds);
param++;
}
if(items != NULL)
{
stmt->setInt(param, *items);
param++;
}
if(comments != NULL)
{
stmt->setInt(param, *comments);
param++;
}
stmt->setInt(param, id);
stmt->executeUpdate();
delete stmt;
}
