twelve-heroes/server/db/mariadb-connector.js

'use strict';
import mariadb from 'mariadb';

const JOJOAPPS_SERVER_DB='localhost';
// const JOJOAPPS_SERVER_DB_PORT= 1986;
const JOJOAPPS_SERVER_DB_SOCKET= '/run/mysqld/mysqld.sock';
const DATABASE_NAME='twelve_heroes';

export default class MariadbConnector {
  constructor() {
    let host = process.env.DB || JOJOAPPS_SERVER_DB;
    let port = process.env.DB_PORT || 0;
    let database = process.env.DB_NAME || DATABASE_NAME;
    console.log('DB server : ' + host);
    console.log('DB database : ' + database);

    let mariaDbConfig={
      host,
      user: 'node',
      password: 'nodejs1234',
      database,
      connectionLimit: 5
    };

    if (port === 0) {
      mariaDbConfig.socketPath = JOJOAPPS_SERVER_DB_SOCKET;
      console.log('Will use DB UNIX socket : ' + mariaDbConfig.socketPath);
    } else {
      mariaDbConfig.port=port;
      console.log('Will use DB port : ' + port);
    }

    this.pool = mariadb.createPool(mariaDbConfig);
  }

  async getUsernames() {

    let conn;
    let players = [];
    try {
      conn = await this.pool.getConnection();

      const rows_players = await conn.query("SELECT username FROM players");
      for (const { username: u } of rows_players) {
        players.push(u);
      }
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
    return players;
  }

  async addPlayer(username) {
    console.log('mariadb add player : ', username);
    let conn;
    try {
      conn = await this.pool.getConnection();
      const val = await conn.query("INSERT INTO players value (?, NOW(),NULL)", [username]);
      console.log('OK removing player : ', val);
      return val;
    } catch (err) {
      console.log('error adding player : ', err);
      throw err;
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
  }
  async removePlayer(username) {
    console.log('mariadb remove player : ', username);
    let conn;
    try {
      conn = await this.pool.getConnection();
      const val = await conn.query("DELETE FROM players WHERE username=?", [username]);
      console.log('OK removing player : ', val);
      return val;
    } catch (err) {
      console.log('error removing player : ', err);
      throw err;
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
  }

  async addNewGame(game) {
    console.log('mariadb add game : ', game);
    let conn;
    try {
      conn = await this.pool.getConnection();
      const val = await conn.query("INSERT INTO games values (NULL,?,?,?,?,?,?,NOW())",
        [game.player1,
        game.player2,
        game.deck,
        convertAdvRulesToString(game.advRules),
        game.status,
        game.data]);
      console.log('OK adding game : ', val);
      return val.insertId;
    } catch (err) {
      console.log('error adding game : ', err);
      throw err;
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
  }

  removeGameById(gameId) {
    return this.removeGame("DELETE FROM games WHERE id=?", [gameId]);
  }
  removeGamesByPlayer1(player1Name) {
    return this.removeGame("DELETE FROM games WHERE player1=?", [player1Name]);
  }
  removeGamesByPlayerAny(playerName) {
    return this.removeGame("DELETE FROM games WHERE player1=? OR player2=?", [playerName, playerName]);
  }
  removeGamesByStatus(status) {
    return this.removeGame("DELETE FROM games WHERE status=?", [status]);
  }
  removeGamesByDays(days) {
    return this.removeGame("DELETE FROM games WHERE  datediff(NOW(),last_played) > ?", [days]);
  }
  removeFinishedGamesByDays(days) {
    return this.removeGame("DELETE FROM games WHERE status='FINISHED' AND datediff(NOW(),last_played) > ?", [days]);
  }

  async removeGame(queryStr, queryArgs) {
    console.log('mariadb remove game  : ', queryStr);
    let conn;
    try {
      conn = await this.pool.getConnection();
      const val = await conn.query(queryStr, queryArgs);
      console.log('OK removing game : ', val);
      return val;
    } catch (err) {
      console.log('error removing game : ', err);
      throw err;
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
  }

  async getNonFinishedGamesForPlayer(username) {

    let conn;
    let games = [];
    try {
      conn = await this.pool.getConnection();
      const res = await conn.query(
        "SELECT * FROM`games` WHERE status != 'FINISHED' AND(player1 = ? OR player2 = '' OR player2 = ?)",
        [username,username]);
      for (const { player1,player2,deck,adv_rules } of res) {
        games.push({player1,player2,deck,adv_rules});
      }
      console.log(`returning for ${username} : `, games);
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
    return games;

  }
}
let convertAdvRulesToString = function (advRulesArray) {
  let advRulestStr = '';
  let last = advRulesArray.length - 1;
  advRulesArray.forEach((rule, index) => {
    advRulestStr += rule;
    if (index < last) {
      advRulestStr += ',';
    }
  });
  return advRulestStr;
};