Making a Decentralized Game on Hive - Part 3

This part took a little longer because it was actually time-consuming (also I took a little break). The process is getting harder so it might take a few days longer than expected for the coming parts but I will do my best.

In the previous parts, we made a simple front-end with login functionality and initialized our API and back-end application with streaming. (Part1 and Part2)

MySQL Setup

You can use apps like AMPPS which comes with MySQL and other tools like PHPMyAdmin (one of the best MySQL management apps) or install MySQL directly. I have AMMPS on windows and use MySQL docker on Linux.

MySQL docker installation:

docker pull mysql/mysql-server:latest

I create a folder /root/mysql-docker1 and put the MySQL config file there my.cnf and another folder data for holding the database files.
Running on port 127.0.0.1:3306:

docker run --name=mysql1 \
--mount type=bind,src=/root/mysql-docker1/my.cnf,dst=/etc/my.cnf \
--mount type=bind,src=/root/mysql-docker1/data,dst=/var/lib/mysql \
-p 127.0.0.1:3306:3306 -d mysql/mysql-server:latest

There are different ways to tune your MySQL server based on your hardware which I'm not going to talk about. The following config is for medium-range hardware (32GB ram).
my.cnf:

[mysqld]
skip_name_resolve
user=mysql
default_authentication_plugin = mysql_native_password

symbolic-links=0
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_flush_neighbors = 0
innodb_undo_log_truncate=off
max_connect_errors = 1000000

# InnoDB Settings
innodb_file_per_table
innodb_log_files_in_group       = 2
innodb_open_files               = 4000
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 8     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 16G    # Use up to 70-80% of RAM
innodb_flush_method             = O_DIRECT_NO_FSYNC
innodb_log_buffer_size          = 64M
innodb_log_file_size            = 10G
innodb_stats_on_metadata        = 0

# tune
innodb_doublewrite= 1
innodb_thread_concurrency       = 0
innodb_flush_log_at_trx_commit  = 0
innodb_lru_scan_depth           = 2048
innodb_page_cleaners            = 4
join_buffer_size                = 256K
sort_buffer_size                = 256K
innodb_use_native_aio           = 1
innodb_stats_persistent         = 1

innodb_adaptive_flushing        = 1
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16
innodb_io_capacity              = 1500
innodb_io_capacity_max          = 2500
innodb_purge_threads            = 4
innodb_adaptive_hash_index      = 0
max_prepared_stmt_count         = 1000000
innodb_monitor_enable           = '%'
performance_schema              = ON
key_buffer_size = 512M
# Connection Settings
max_connections                 = 2000   # UPD
back_log                        = 3000
interactive_timeout             = 180
wait_timeout                    = 10
table_open_cache                = 200000 # UPD
table_open_cache_instances      = 64
open_files_limit                = 100000 # UPD

Note: restart MySQL server after updating themy.cnf file.

MySQL password on AMMPS is mysql and on docker setup, I think you get the password from docker logs mysql1. Anyway, there are many documentations about MySQL already on the internet.

Create a database tictactoe. It's easier with tools like PHPMyAdmin if you are using AMPPS. Or try HeidiSQL.
Here is the SQL command for creating the database:

CREATE DATABASE `tictactoe`;

---

Development

Let's create a config file for holding MySQL login information for our app. I will put this file as config.example.js in repository and you have to rename it manually.
config.js:

const config = {
  dbName: 'tictactoe',
  dbUser: 'root',
  dbPassword: 'password',
  dbHost: '127.0.0.1',
  dbPort: 3306
}

module.exports = config

---

I have a personal code for MySQL connection pooling. It simply makes a custom async function just like the original connect function of mysqljs library but for pooling connections.
helpers/mysql.js:

const mysql = require('mysql')
const config = require('../config')
const pool = mysql.createPool({
  connectionLimit: 5,
  host: config.dbHost,
  port: config.dbPort,
  user: config.dbUser,
  password: config.dbPassword,
  database: config.dbName,
  charset: 'utf8mb4'
})

// Rewriting MySQL query method as a promise
const con = {}
con.query = async (query, val) => {
  if (val) {
    const qu = await new Promise((resolve, reject) => {
      pool.query(query, val, (error, results) => {
        if (error) reject(new Error(error))
        resolve(results)
      })
    })
    return qu
  } else {
    const qu = await new Promise((resolve, reject) => {
      pool.query(query, (error, results) => {
        if (error) reject(new Error(error))
        resolve(results)
      })
    })
    return qu
  }
}

module.exports = con

It creates a pool of 5 connections which is more than enough for our game.
And of course:

npm install mysql

---

Initializing database

We make a function to create necessary tables if they don't exist already.
helpers/initDatabase.js:

const mysql = require('./mysql')

/**
 * id, game_id, player1, player2, starting_player, status, winner
 */
const tableGames =
  'CREATE TABLE IF NOT EXISTS `tictactoe`.`games` ( `id` INT NOT NULL AUTO_INCREMENT , ' +
  '`game_id` TINYTEXT NOT NULL , ' +
  '`player1` TINYTEXT NOT NULL , `player2` TINYTEXT NULL DEFAULT NULL , ' +
  '`starting_player` TINYTEXT NOT NULL , `status` TINYTEXT NULL , ' +
  '`winner` TINYTEXT NULL DEFAULT NULL , PRIMARY KEY  (`id`)) ' +
  'ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;'

/**
 * id, game_id, player, col, row
 */
const tableMoves =
  'CREATE TABLE IF NOT EXISTS `tictactoe`.`moves` ( `id` INT NOT NULL AUTO_INCREMENT , ' +
  '`game_id` TINYTEXT NOT NULL , `player` TINYTEXT NOT NULL , ' +
  '`col` INT(1) NOT NULL , `row` INT(1) NOT NULL , ' +
  'PRIMARY KEY  (`id`)) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;'

const tableRquests =
  'CREATE TABLE IF NOT EXISTS `tictactoe`.`requests` ( `id` INT NOT NULL AUTO_INCREMENT , ' +
  '`game_id` TINYTEXT NOT NULL , `player` TINYTEXT NOT NULL , `status` TINYTEXT NOT NULL , ' +
  'PRIMARY KEY  (`id`)) ENGINE = InnoDB CHARSET=utf8mb4 COLLATE utf8mb4_general_ci;'

const initDatabase = async () => {
  await mysql.query(tableGames)
  await mysql.query(tableMoves)
  await mysql.query(tableRquests)
}

module.exports = initDatabase

Created tables

games

moves

requests

---

Updating main application

Now we can complete the game methods in index.js:
createGame

const createGame = async (data, user) => {
  if (!data || !data.id || !data.starting_player) {
    return
  }
  // validating
  if (
    data.id.length !== 20 ||
    (data.starting_player !== 'first' && data.starting_player !== 'second')
  ) {
    return
  }
  // Check already existing games
  const duplicate = await mysql.query(
    'SELECT `id` FROM `games` WHERE `game_id`= ?',
    [data.id]
  )
  if (duplicate && Array.isArray(duplicate) && duplicate.length > 0) {
    return
  }
  // Add game to database
  await mysql.query(
    'INSERT INTO `games`(`game_id`, `player1`, `starting_player`, `status`) VALUES (?, ?, ?, ?)',
    [data.id, user, data.starting_player, 'waiting']
  )
}

---

requestJoin

const requestJoin = async (data, user) => {
  if (!data || !data.id || !data.id.length !== 20) {
    return
  }
  // Check game id in database
  const game = await mysql.query(
    'SELECT `player1` FROM `games` WHERE `game_id`= ? AND `status`= ?',
    [data.id, 'waiting']
  )
  if (!game || !Array.isArray(game) || game.length < 1) {
    return
  }
  // Players can not play with themselves
  if (game[0].player1 === user) {
    return
  }
  // Check already open requests
  const requests = await mysql.query(
    'SELECT `id` FROM `requests` WHERE `game_id`= ? AND (`player`= ? OR `status`= ?)',
    [data.id, user, 'accepted']
  )
  if (requests && Array.isArray(requests) && requests.length > 0) {
    return
  }
  // Request join game
  await mysql.query(
    'INSERT INTO `requests`(`game_id`, `player`, `status`) VALUES (?, ?, ?)',
    [data.id, user, 'waiting']
  )
}

---

acceptRequest

const acceptRequest = async (data, user) => {
  if (!data || !data.id || !data.player || !data.id.length !== 20) {
    return
  }
  // Validate game in database
  const game = await mysql.query(
    'SELECT `player1` FROM `games` WHERE `game_id`= ? AND `status`= ?',
    [data.id, 'waiting']
  )
  if (!game || !Array.isArray(game) || game.length < 1) {
    return
  }
  const requests = await mysql.query(
    'SELECT `id` FROM `requests` WHERE `game_id`= ? AND `player`= ? AND `status`= ?',
    [data.id, data.player, 'waiting']
  )
  if (!requests || !Array.isArray(requests) || requests.length < 1) {
    return
  }
  // Accept the join request and update game status
  await mysql.query(
    'UPDATE `games` SET `player2`=?,`status`=? WHERE `game_id`=?',
    [data.player, 'running', data.id]
  )
  await mysql.query(
    'UPDATE `requests` SET `status`=? WHERE `game_id`=? AND `player`=?',
    ['accepted', data.id, data.player]
  )
}

---

Some updates to processData:

const processData = (jsonData, postingAuths) => {
  try {
    if (!jsonData) {
      return
    }
    const data = JSON.parse(jsonData)
    if (!data || !data.action || !data.app) {
      return
    }
    if (
      !postingAuths ||
      !Array.isArray(postingAuths) ||
      postingAuths.length < 1
    ) {
      return
    }
    const user = postingAuths[0]
    if (data.action === 'create_game') {
      createGame(data, user)
    } else if (data.action === 'request_join') {
      requestJoin(data, user)
    } else if (data.action === 'accept_request') {
      acceptRequest(data, user)
    } else if (data.action === 'play') {
      play(data, user)
    }
  } catch (e) {
    // error might be on JSON.parse and wrong json format
    return null
  }
}

And streaming function:

try {
  stream.streamBlockOperations((ops) => {
    if (ops) {
      const op = ops[0]
      if (op && op[0] === 'custom_json' && op[1].id === 'tictactoe') {
        processData(op[1].json, op[1].required_posting_auths)
      }
    }
  })
} catch (e) {
  throw new Error(e)
}

---

I think it's enough for this part. Let's finish before I sleep on the keyboard.

We set up the MySQL server and made a script to create 3 tables. Our back-end is now processing data into the database and 3 main functions are working as expected. Creating a game, Requesting to join a game, and accepting the requests.

We have to create the front-end for these functions in the next part. I think the hard part is going to be the play function which holds the game rules.

Upvote if you like and leave a comment. Make sure to follow me and share the post.

Thanks for reading.

---

GitLab
Part1
Part2

Next part >>

---

Vote for my witness:

• https://wallet.hive.blog/~witnesses
• https://peakd.com/witnesses
• https://ecency.com/witnesses