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

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

const JOJOAPPS_SERVER_DB = 'localhost';
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]
    );
  }
  removeCreatedGamesByPlayer1(player1Name) {
    return this.removeGame(
      "DELETE FROM games WHERE player1=? AND status='CREATED'",
      [player1Name]
    );
  }

  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 getJoinableGamesForPlayer(username) {
    let conn;
    let games = [];
    try {
      conn = await this.pool.getConnection();
      const res = await conn.query(
        "SELECT * FROM games WHERE (status = 'CREATED' OR status = 'PAUSED')  AND(player1 = ? OR player2 = '' OR player2 = ?)",
        [username, username]
      );
      for (const { id, player1, player2, deck, adv_rules } of res) {
        games.push({ id, player1, player2, deck, adv_rules });
      }
      console.log(`returning for ${username} : `, games);
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
    return games;
  }
  async getGameById(gameId) {
    let conn;
    let game = {};
    try {
      conn = await this.pool.getConnection();
      game = await conn.query('SELECT * FROM games WHERE id=?', gameId);
    } finally {
      if (conn && conn !== null) {
        conn.end();
      }
    }
    return game;
  }
}
let convertAdvRulesToString = function(advRulesArray) {
  let advRulestStr = '';
  let last = advRulesArray.length - 1;
  advRulesArray.forEach((rule, index) => {
    advRulestStr += rule;
    if (index < last) {
      advRulestStr += ',';
    }
  });
  return advRulestStr;
};