RuneOptimizer/src/cli/runeoptimizer/db/db.c

/*
 * This file is part of RuneOptimizer.
 *
 * RuneOptimizer is free software: you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the Free
 * Software Foundation, either version 3 of the License, or (at your option)
 * any later version.
 *
 * RuneOptimizer is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for
 * more details.
 *
 * You should have received a copy of the GNU General Public License along with
 * RuneOptimizer. If not, see <https://www.gnu.org/licenses/>.
 */

/**
 * @file db.c
 *
 * Implementation of the database functions.
 *
 * This file implements all the database related functions defined in
 * {@link db.h}. It also defines and implements some static functions used by
 * them.
 */

#include <stdio.h>
#include <sqlite3.h>
#include <string.h>
#include <sys/stat.h>
#include <unistd.h>
#include "../runeoptimizer.h"
#include "../error/error.h"
#include "db.h"

/**
 * Ensures that the directory structure for the database exist.
 *
 * Creates the required directories for the file in the glovan variable
 * {@link db_location}. It doesn't create the database file itself. It doesn't
 * have return values, so the caller won't know if the folder structure was
 * succesfully created, but {@link db_open} will throw an error when called
 * if they werent created.
 */
static void db_create_directories();

/**
 * Creates all database tables.
 *
 * Runs CREATE TABLE stetements against the database to set up all the tables
 * the application uses. It is called from {@link db_open} if it detects that
 * the database has just been created. All the statements contains the IF NOT
 * EXIST clause, so there will be no errors if they actually exists.
 *
 * In case of error, it will print a message to stderr.
 *
 * @return {@link SUCCESS} if all the tables were created, or an error defined
 * in {@link error.h} if one or more tables could not be created.
 */
static int db_create_tables();

extern int db_close(){
    if (db != NULL){
        int result = sqlite3_close_v2(db);
        if (result != SQLITE_OK){
            fprintf(
              stderr,
              "Error closing database connection: %s", sqlite3_errmsg(db)
            );
            return(result);
        }
    }
    db = NULL;
    return(SUCCESS);
}

extern int db_open(char *path){
    db_close();
    char exists = TRUE;
    if (access( db_location, F_OK ) != 0) {
        exists = FALSE;
        db_create_directories();
    }

    // Get the path, relative to the program
    char location[sizeof(db_location)];

    // If db specified as argument, use it, else use the default location.
    if (path == NULL) strcpy(location, db_location);
    else strcpy(location, path);
    if (
        SUCCESS !=
        sqlite3_open_v2(
          location, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL
        )
    ){
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
        db_close();
        return(ERROR_DB_CANT_OPEN);
    }

    // If the database was just created, create the tables
    if (exists == FALSE && access( db_location, F_OK ) == 0){
        if (SUCCESS != db_create_tables()){
            return ERROR_DB_CANT_OPEN;
        }
    }

    return(SUCCESS);
}

extern int db_query(sqlite3_stmt **stmt, char query[], char *parameters[]){

    // If the connection has not been initialized, do it now
    if (NULL == db && SUCCESS != db_open(NULL)){
        return ERROR_DB_CANT_OPEN;
    }


    if (SQLITE_OK != sqlite3_prepare_v2(db, query, -1, stmt, 0)) {
        fprintf(
          stderr, "ERROR executing query '%s': %s\n", query, sqlite3_errmsg(db)
        );
        sqlite3_finalize(*stmt);
        return(ERROR_DB_QUERY);
    }

    // Loop and bind parameters
    char total_parameters = sqlite3_bind_parameter_count(*stmt);
    if (total_parameters > 0 && parameters == NULL){
        return(ERROR_DB_QUERY_NO_PARAMETERS);
    }
    for (int i = 0; i < total_parameters; i ++){
        sqlite3_bind_text(
          *stmt, i + 1, parameters[i], strlen(parameters[i]), NULL
        );
    }

    //printf("PREP QUERY: %s\n", sqlite3_expanded_sql(*stmt));

    return(SUCCESS);
}

extern int db_execute(char query[], char *parameters[]){

    // If the connection has not been initialized, do it now
    if (NULL == db && SUCCESS != db_open(NULL)){
        return ERROR_DB_CANT_OPEN;
    }

    sqlite3_stmt *stmt;
    if (SQLITE_OK != sqlite3_prepare_v2(db, query, -1, &stmt, 0)) {
        fprintf(
          stderr, "ERROR executing statement '%s': %s\n",
          query, sqlite3_errmsg(db)
        );
        sqlite3_finalize(stmt);
        return(ERROR_DB_EXECUTE);
    }

    // Loop and bind parameters
    char total_parameters = sqlite3_bind_parameter_count(stmt);
    if (total_parameters > 0 && parameters == NULL){
        return(ERROR_DB_EXECUTE_NO_PARAMETERS);
    }
    for (int i = 0; i < total_parameters; i ++){
        sqlite3_bind_text(
          stmt, i + 1, parameters[i], strlen(parameters[i]), NULL
        );
    }
    //printf("PREP QUERY: %s\n", sqlite3_expanded_sql(stmt));
    if (SQLITE_DONE != sqlite3_step(stmt)){
        fprintf(
          stderr, "ERROR Executing statement '%s': %s\n",
          query, sqlite3_errmsg(db)
        );
        sqlite3_finalize(stmt);
        return(ERROR_DB_EXECUTE);
    }

    sqlite3_finalize(stmt);
    return(SUCCESS);
}

static void db_create_directories(){
    char dir_to_make[sizeof(db_location)];
    int last_path_separator = -1;
    for(int i = 0; i < (int) strlen(db_location); i ++){
        if(db_location[i] == '/' || db_location[i] == '\\'){
            last_path_separator = i + 1;
        }
    }
    if (last_path_separator != -1){
        strncpy(dir_to_make, db_location, last_path_separator);
        dir_to_make[last_path_separator] = '\0';

    }
    #ifdef _WIN32
    mkdir(dir_to_make);
    #else
    mkdir(dir_to_make, 0777);
    #endif
    return;
}

static int db_create_tables(){
    // Table rune_stats
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS rune_stats("
        "  rune CHAR(12), slot INT, stat INT, value INT, grind INT, enchant INT"
        ");",
        NULL
      )
    ){
        fprintf(
          stderr,
          "Unable to create table rune_stats: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_RUNE_STATS;
    }

    // Table runes
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS runes("
        "  id CHAR(12),"
        "  unit CHAR(12),"
        "  type INT,"
        "  slot INT,"
        "  stars INT,"
        "  level INT,"
        "  quality INT,"
        "  efficiency FLOAT,"
        "  max_efficiency FLOAT,"
        "  main_stat INT,"
        "  current_hp_percent INT,"
        "  current_atk_percent INT,"
        "  current_def_percent INT,"
        "  current_hp_flat INT,"
        "  current_atk_flat INT,"
        "  current_def_flat INT,"
        "  current_spd INT,"
        "  current_crr INT,"
        "  current_crd INT,"
        "  current_acc INT,"
        "  current_res INT,"
        "  lv12_hp_percent INT,"
        "  lv12_atk_percent INT,"
        "  lv12_def_percent INT,"
        "  lv12_hp_flat INT,"
        "  lv12_atk_flat INT,"
        "  lv12_def_flat INT,"
        "  lv12_spd INT,"
        "  lv12_crr INT,"
        "  lv12_crd INT,"
        "  lv12_acc INT,"
        "  lv12_res INT,"
        "  lv15_hp_percent INT,"
        "  lv15_atk_percent INT,"
        "  lv15_def_percent INT,"
        "  lv15_hp_flat INT,"
        "  lv15_atk_flat INT,"
        "  lv15_def_flat INT,"
        "  lv15_spd INT,"
        "  lv15_crr INT,"
        "  lv15_crd INT,"
        "  lv15_acc INT,"
        "  lv15_res INT"
        ");",
        NULL
      )
    ){
        fprintf(
          stderr, "Unable to create table runes: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_RUNES;
    }

    // Table units
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS units("
        "  id CHAR(12),"
        "  monster INT,"
        "  name CHAR(128),"
        "  stars INT,"
        "  level INT,"
        "  storage INT,"
        "  base_hp INT,"
        "  base_atk INT,"
        "  base_def INT,"
        "  base_spd INT,"
        "  base_crr INT,"
        "  base_crd INT,"
        "  base_res INT,"
        "  base_acc INT,"
        "  current_hp INT,"
        "  current_atk INT,"
        "  current_def INT,"
        "  current_spd INT,"
        "  current_crr INT,"
        "  current_crd INT,"
        "  current_res INT,"
        "  current_acc INT"
        ");",
        NULL
      )
    ){
        fprintf(
          stderr,
          "Unable to create table units: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_UNITS;
    }

    // Table info
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS info("
        "  player_id CHAR(12),"
        "  player_name CHAR(64),"
        "  player_level INT,"
        "  ts DATETIME,"
        "  modified INT"
        ");",
        NULL
      )
    ){
        fprintf(
          stderr,
          "Unable to create table info: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_INFO;
    }

    // Tables teams and units_teams
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS "
        "units_teams(unit CHAR(12), team CHAR(12));",
        NULL
      )
    ){
        fprintf(
          stderr,
          "Unable to create table units_teams: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_UNITS_TEAMS;
    }
    if (
      SUCCESS !=
      db_execute(
        "CREATE TABLE IF NOT EXISTS "
        "teams(id CHAR(12), name CHAR(50), priority INT);",
        NULL
      )
    ){
        fprintf(
          stderr,
          "Unable to create table teams: %s\n", sqlite3_errmsg(db)
        );
        return ERROR_DB_CREATE_TEAMS;
    }

    return SUCCESS;
}