<center>![coding-1853305_1280.jpg](https://images.hive.blog/DQmeBMvQ9RAne3j2qH1RrtDaWEyQ2bZo81ik3wsNXYGbp3E/coding-1853305_1280.jpg)</center>

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](https://hive.blog/hive-139531/@mahdiyari/making-a-decentralized-game-on-hive-tic-tac-toe-part-1) and [Part2](https://hive.blog/hive-139531/@mahdiyari/making-a-decentralized-game-on-hive-part-2))
## MySQL Setup
You can use apps like [AMPPS](https://www.ampps.com/) 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 the`my.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](https://github.com/mysqljs/mysql) 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`
<center>![tablesqlgames.png](https://images.hive.blog/DQmVCMyNZax6xd6kCc2VZijGjMa9Qbt8Myeev7jF8KmcqXe/image.png)</center>

`moves`
<center>![tablesqlmoves.png](https://images.hive.blog/DQmVwyDZg1N3KEteDgfA5NdMHeUkjpngHZEmizBXxhh3XPs/image.png)</center>

`requests`
<center>![image.png](https://images.hive.blog/DQmQLxfwKEfWSQ614vWb6qfUNVMSyiFgnKEh5nSWcKYFQQv/image.png)</center>
***
#### 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](https://gitlab.com/mahdiyari/decentralized-game-on-hive)
[Part1](https://hive.blog/hive-139531/@mahdiyari/making-a-decentralized-game-on-hive-tic-tac-toe-part-1)
[Part2](https://hive.blog/hive-139531/@mahdiyari/making-a-decentralized-game-on-hive-part-2)

[Next part >>](https://hive.blog/hive-169321/@mahdiyari/making-a-decentralized-game-on-hive-part-4)
***
**Vote for my witness:**
- https://wallet.hive.blog/~witnesses
- https://peakd.com/witnesses
- https://ecency.com/witnesses