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`
`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](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
See: Making a Decentralized Game on Hive - Part 3
by
@mahdiyari
