space-ace/db.cpp

#include "db.h"
#include "utils.h"

#include <SQLiteCpp/VariadicBind.h>

#include <chrono>
#include <iomanip>
#include <iostream>
#include <sstream>
#include <thread>

/*
database is locked

This happens when more then one node plays the game.

The database access is slow.

So, make sure you set it up so that you do your writes right
before you collect user input.  That way, the user won't see
the lags.

This might be an issue on rPI systems!
Change the strategy so we only update when the game ends.
*/

DBData::DBData(void)
    : db("space-data.db", SQLite::OPEN_READWRITE | SQLite::OPEN_CREATE) {
  locked_retries = 50;
  create_tables();
}

DBData::~DBData() {}

void DBData::retry_wait(void) {
  std::this_thread::sleep_for(std::chrono::milliseconds(100));
}

#define DBLOCK "database is locked"

/**
 * @brief create tables if they don't exist.
 */
void DBData::create_tables(void) {
  int tries = 0;

retry:
  try {
    db.exec("CREATE TABLE IF NOT EXISTS settings(username TEXT, setting TEXT, "
            "value TEXT, PRIMARY KEY(username, setting));");
    db.exec(
        "CREATE TABLE IF NOT EXISTS scores ( \"username\" TEXT, \"when\" "
        "INTEGER, \"date\" INTEGER, \"hand\" INTEGER, \"won\" INTEGER, "
        "\"score\" INTEGER, PRIMARY KEY(\"username\", \"date\", \"hand\"));");
    db.exec("CREATE TABLE IF NOT EXISTS monthly (	\"month\"	"
            "INTEGER, \"username\"	TEXT, \"days\"	INTEGER, 	"
            "\"score\"	INTEGER, PRIMARY KEY(\"month\",\"username\") );");
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "create_tables():" << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0) {
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  }
}

/**
 * @brief get setting from the settings table
 *
 * We use user and setting.
 * Return isMissing if not found.
 *
 * @param setting
 * @param ifMissing
 * @return std::string
 */
std::string DBData::getSetting(const std::string &setting,
                               std::string ifMissing) {
  SQLite::Statement stmt_getSet = SQLite::Statement(
      db, "SELECT value FROM settings WHERE username=? AND setting=?");
  int tries = 0;

retry:
  try {

    stmt_getSet.bind(1, user);
    stmt_getSet.bind(2, setting);
    if (stmt_getSet.executeStep()) {
      std::string value = stmt_getSet.getColumn(0);
      return value;
    };
    return ifMissing;
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "getSettings( " << setting << "," << ifMissing
                   << " ): " << user << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0) {
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  }

  return ifMissing;
}

/**
 * @brief save setting in the settings table
 *
 * We save user setting in the settings table.
 * We use SQLite's REPLACE INTO so it does an update if it exists, or an insert
 * if it is missing.
 * @param setting
 * @param value
 */
void DBData::setSetting(const std::string &setting, const std::string &value) {
  SQLite::Statement stmt_setSet = SQLite::Statement(
      db, "REPLACE INTO settings(username, setting, value) VALUES(?,?,?);");
  int tries = 0;

retry:
  try {
    stmt_setSet.bind(1, user);
    stmt_setSet.bind(2, setting);
    stmt_setSet.bind(3, value);
    stmt_setSet.exec();
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "setSettings( " << setting << "," << value
                   << " ): " << user << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
}

/**
 * @brief save the user's score
 *
 * @param when now()
 * @param date what day they played
 * @param hand which hand they played
 * @param won did they win? 1/0
 * @param score
 */
void DBData::saveScore(time_t when, time_t date, int hand, int won, int score) {
  int tries = 0;

retry:
  try {
    SQLite::Statement stmt(
        db, "INSERT INTO scores( \"username\", \"when\", "
            "\"date\", \"hand\", \"won\", \"score\") VALUES(?,?,?,?,?,?);");
    stmt.bind(1, user);
    stmt.bind(2, when);
    stmt.bind(3, date);
    stmt.bind(4, hand);
    stmt.bind(5, won);
    stmt.bind(6, score);
    stmt.exec();
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "saveScore( " << when << "," << date << "," << hand << ","
                   << won << "," << score << " ): " << user << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
}

/**
 * @brief Returns number of hands played on given day
 *
 * returns number of hands they played, or 0
 * @param day
 * @return int
 */
int DBData::handsPlayedOnDay(time_t day) {
  int tries = 0;

retry:
  try {
    SQLite::Statement stmt(
        db, "SELECT COUNT(*) FROM scores WHERE \"username\"=? AND \"DATE\"=?;");
    stmt.bind(1, user);
    stmt.bind(2, day);
    int count = 0;
    if (stmt.executeStep()) {
      count = stmt.getColumn(0);
    };
    return count;
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "handsPlayedOnDay( " << day << " ): " << user
                   << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  return 0;
}

/*
 * If you're looking at scores, you're not really looking for all the details.
 * I think using the group/SUM would be better, and it sorts scores from highest
 * to lowest.  Let SQL do the work for me.
 */
// select date,username,SUM(score),SUM(won) FROM scores group by date,username
// ORDER BY SUM(score) DESC;

std::vector<scores_details> DBData::getScoresOnDay(time_t date) {
  std::vector<scores_details> scores;
  int tries = 0;

retry:
  try {
    // \"when\",
    SQLite::Statement stmt(db, "SELECT \"username\", \"date\", \"hand\", "
                               "\"won\", \"score\" FROM SCORES WHERE "
                               "\"date\"=? ORDER BY \"username\", \"hand\";");
    stmt.bind(1, date);
    while (stmt.executeStep()) {
      scores_details sd;
      sd.user = (const char *)stmt.getColumn(0);
      sd.date = stmt.getColumn(1);
      sd.hand = stmt.getColumn(2);
      sd.won = stmt.getColumn(3);
      sd.score = stmt.getColumn(4);
      scores.push_back(sd);
    }
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "getScoresOnDay( " << date << " ): " << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    scores.clear();
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  return scores;
}

/**
 * @brief Gets scores, time_t is day, vector has user and scores sorted highest
 * to lowest.
 *
 * @return std::map<time_t, std::vector<scores_data>>
 */
std::map<time_t, std::vector<scores_data>> DBData::getScores(void) {
  std::map<time_t, std::vector<scores_data>> scores;
  int tries = 0;

retry:
  try {
    SQLite::Statement stmt(
        db, "SELECT `date`,username,SUM(score),SUM(won) FROM scores "
            "GROUP BY `date`,username ORDER BY `date`,SUM(score) DESC;");
    time_t current = 0;
    std::vector<scores_data> vsd;

    while (stmt.executeStep()) {
      time_t the_date = stmt.getColumn(0);

      if (current == 0) {
        // ok, we've got the first one!
        current = the_date;
      } else {
        // Ok, are we on another date now?
        if (the_date != current) {
          scores[current] = std::move(vsd);
          vsd.clear();
          current = the_date;
        }
      }
      scores_data sd;
      sd.user = (const char *)stmt.getColumn(1);
      sd.date = the_date;
      sd.score = stmt.getColumn(2);
      sd.won = stmt.getColumn(3);
      vsd.push_back(sd);
    }
    if (!vsd.empty()) {
      scores[current] = std::move(vsd);
    }
    vsd.clear();
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "getScores(): " << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    scores.clear();
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << locked_retries << " retries."
                     << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  return scores;
}

/**
 * @brief Get hands played per day
 *
 * Uses the user value.
 *
 * @return std::map<time_t, int>
 */
std::map<time_t, int> DBData::getPlayed(void) {
  std::map<time_t, int> hands;
  int tries = 0;

retry:
  try {
    SQLite::Statement stmt(
        // select date, count(hand) from scores where username='grinder' group
        // by date;
        db, "SELECT `date`,COUNT(hand) FROM scores "
            "WHERE username=? GROUP BY `date`;");
    stmt.bind(1, user);
    while (stmt.executeStep()) {
      time_t the_date = stmt.getColumn(0);
      hands[the_date] = stmt.getColumn(1);
    }
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "getPlayed(): " << user << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    hands.clear();
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  return hands;
}

/**
 * @brief When has the user played?
 *
 * This returns a map of date (time_t), and number of hands played on that date.
 *
 * @return std::map<time_t, long>
 */
std::map<time_t, int> DBData::whenPlayed(void) {
  // select "date", count(hand) from scores where username='?' group by
  // "date";
  std::map<time_t, int> plays;
  int tries = 0;

retry:
  try {
    SQLite::Statement stmt(db, "SELECT `date`, COUNT(hand) FROM scores WHERE "
                               "username=? GROUP BY `date`;");
    stmt.bind(1, user);

    while (stmt.executeStep()) {
      time_t d = (long)stmt.getColumn(0);
      plays[d] = stmt.getColumn(1);
    }
  } catch (std::exception &e) {
    if (get_logger) {
      get_logger() << "whenPlayed(): " << std::endl;
      get_logger() << "SQLite exception: " << e.what() << std::endl;
    }
    plays.clear();
    if (strcmp(e.what(), DBLOCK) == 0) {
      ++tries;
      if (tries < locked_retries) {
        retry_wait();
        goto retry;
      }
      if (get_logger)
        get_logger() << "giving up! " << tries << " retries." << std::endl;
    }
  }
  if (tries > 0)
    if (get_logger)
      get_logger() << "success after " << tries << std::endl;
  return plays;
}

/**
 * @brief This will expire out old scores
 *
 * @todo implement, but don't throw away high scores.
 *
 */
void DBData::expireScores(void) {}

/**
 * @brief Format date to string.
 *
 * We use default "%0m/%0d/%Y", but can be configured by SysOp via
 * config["date_score"] setting.  "%Y/%0m/%0d" for non-US?
 *
 * @param tt
 * @return std::string
 */
std::string convertDateToDateScoreFormat(time_t tt) {
  std::stringstream ss;
  if (config["date_score"]) {
    std::string custom_format = config["date_score"].as<std::string>();
    ss << std::put_time(std::localtime(&tt), custom_format.c_str());
  } else {
    ss << std::put_time(std::localtime(&tt), "%0m/%0d/%Y");
  }

  std::string date = ss.str();
  return date;
}

void normalizeDate(std::chrono::_V2::system_clock::time_point &date) {
  time_t date_t = std::chrono::system_clock::to_time_t(date);
  normalizeDate(date_t);
  date = std::chrono::system_clock::from_time_t(date_t);
}

/**
 * @brief change datetime to have consistent time
 *
 * This converts the time part to hour:00
 *
 * @param tt
 * @param hour
 */
void normalizeDate(time_t &tt, int hour) {
  std::tm *local_tm = localtime(&tt);
  // adjust date to 2:00:00 AM

  tt -= (local_tm->tm_min * 60) + local_tm->tm_sec;
  while (local_tm->tm_hour < hour) {
    ++local_tm->tm_hour;
    tt += 60 * 60;
  }
  if (local_tm->tm_hour > hour) {
    tt -= (60 * 60) * (local_tm->tm_hour - hour);
  }
  /* // possible DST adjustment. LMTATSM
  if (local_tm->tm_isdst) {
    // DST in effect
    tt -= (60*60);
  }
  */
}

void firstOfMonthDate(std::chrono::_V2::system_clock::time_point &date) {
  using namespace std::literals;
  time_t date_t = std::chrono::system_clock::to_time_t(date);
  // adjust to first day of the month
  std::tm date_tm;
  localtime_r(&date_t, &date_tm);

  if (date_tm.tm_mday > 1) {
    date -= 24h * (date_tm.tm_mday - 1);
  }
  normalizeDate(date);
}