Ping API DB

Code

├── Dockerfile
├── api
│   ├── database
│   │   ├── config.php
│   │   ├── database.php
│   │   ├── schema.mwb
│   │   ├── schema.png
│   │   └── schema.sql
│   ├── install
│   │   └── index.php
│   ├── model
│   │   ├── host.php
│   │   ├── icmp.php
│   │   └── packet.php
│   ├── util
│   │   └── Ping.php
│   └── v1
│       └── index.php
└── docker-compose.yml

docker-compose.yml:

version: "3"

services:
  web:
    container_name: web
    image: ifpb/php:7.3-apache-pdo-ping
    build: .
    networks:
      - app-network
    ports:
      - 8080:80
    volumes:
      - ./:/var/www/html/
    depends_on:
      - mysql

  mysql:
    container_name: mysql
    image: mysql:8.0
    command: --default-authentication-plugin=mysql_native_password
    restart: unless-stopped
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-network

networks:
  app-network:
    driver: bridge

Dockerfile:

FROM php:7.3-apache

RUN apt -y update \
  && apt install -y \
  mariadb-client \
  inetutils-ping

# pdo_mysql package
RUN docker-php-ext-install pdo_mysql

.env:

MYSQL_ROOT_PASSWORD=secret
MYSQL_DATABASE=monitor
MYSQL_USER=devuser
MYSQL_PASSWORD=devpass

Database

Schema

api/database/schema.png:

api/database/schema.sql:

DROP DATABASE IF EXISTS monitor;
CREATE DATABASE monitor;
USE monitor;

DROP TABLE IF EXISTS host;
CREATE TABLE host (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(100) NOT NULL,
  address varchar(100) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO host
  (name, address)
VALUES
  ('www.google.com', '216.58.222.100');

DROP TABLE IF EXISTS icmp;
CREATE TABLE icmp(
  id int NOT NULL AUTO_INCREMENT,
  transmitted int NOT NULL,
  received int NOT NULL,
  created_at datetime NOT NULL,
  host_id int NOT NULL,
  PRIMARY KEY (id),
  KEY host_id (host_id),
  CONSTRAINT fk_host_id FOREIGN KEY (host_id) REFERENCES host (id)
);

INSERT INTO icmp
  (transmitted, received, created_at, host_id)
VALUES
  (2, 2, NOW(), 1);

DROP TABLE IF EXISTS packet;
CREATE TABLE packet(
  id int NOT NULL AUTO_INCREMENT,
  seq int NOT NULL,
  ttl int NOT NULL,
  time float NOT NULL,
  icmp_id int NOT NULL,
  PRIMARY KEY (id),
  KEY icmp_id (icmp_id),
  CONSTRAINT fk_icmp_id FOREIGN KEY (icmp_id) REFERENCES icmp(id)
);

INSERT INTO packet
  (seq, ttl, time, icmp_id)
VALUES
  (0, 53, 83.023, 1),
  (1, 53, 104.898, 1);

Install

api/install/index.php:

<?php

require_once(__DIR__ . '/../database/config.php');

try {
  $schema = file_get_contents('../database/schema.sql');
  $connection = new PDO(DB . ":host=" . DBHOST, DBUSER, DBPWD);
  $connection->exec($schema);
  // echo "Database installed!";
  header('Location: /public');
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}

api/database/config.php:

<?php

const DB = 'mysql';
const DBHOST = 'mysql';
const DBNAME = 'monitor';
const DBUSER = 'root';
const DBPWD = 'secret';

Model

api/database/database.php:

<?php
namespace Database;

use \PDO;

require_once('config.php');

class Database {

  protected $connection;

  function __construct(){
    $dsn = DB.":dbname=".DBNAME.";host=".DBHOST;
    try {
      $this->connection = new PDO($dsn, DBUSER, DBPWD);
    } catch (PDOException $e) {
      echo 'Connection failed: ' . $e->getMessage();
    }
  }

}

api/model/host.php:

<?php
namespace Model;

use Database\Database;
use \PDO;

require_once __DIR__."/../database/database.php";

class Host extends Database {

  public function create($name, $address) {
    $sql = "INSERT INTO host (name, address) VALUES ('${name}', '${address}');";
    try {
      $this->connection->exec($sql);
      return $this->connection->lastInsertId();
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
      return null;
    } 
  }

  public function read($id) {
    $sql = "SELECT * FROM host WHERE id = ${id}";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetch(PDO::FETCH_ASSOC) : null;
  }

  public function readByNameAddress($name, $address) {
    $sql = "SELECT * FROM host WHERE name='${name}' AND address='${address}'";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetch(PDO::FETCH_ASSOC) : null;
  }

  public function readAll() {
    $sql = "SELECT * FROM host";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetchAll(PDO::FETCH_ASSOC) : null;
  }

  public function readOrCreate($name, $address) {
    $result = $this->readByNameAddress($name, $address);

    if ($result) {
      return $result;
    } else {
      return $this->create($name, $address);
    }
  }
  
  public function update($id, $name, $address) {
    $sql = "UPDATE host
            SET name='${name}', address='${address}'
            WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }
  
  public function remove($id) {
    $sql = "DELETE FROM host WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

api/model/icmp.php:

<?php
namespace Model;

use Database\Database;
use \PDO;

require_once __DIR__."/../database/database.php";

class Icmp extends Database {

  public function create($transmitted, $received, $host_id) {
    $sql = "INSERT INTO icmp (transmitted, received, created_at, host_id)
            VALUES (${transmitted}, ${received}, NOW(), ${host_id});";
    try {
      $this->connection->exec($sql);
      return $this->connection->lastInsertId();
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
      return null;
    } 
  }

  public function read($id) {
    $sql = "SELECT * FROM icmp WHERE id = ${id}";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm->fetch(PDO::FETCH_ASSOC);
  }

  public function readAll() {
    $sql = "SELECT * FROM icmp";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm->fetchAll(PDO::FETCH_ASSOC);
  }

  public function readJoinAll() {
    $sql = "SELECT name, address, transmitted, received, created_at, seq, ttl, time
            FROM icmp INNER JOIN host INNER JOIN packet
            WHERE host.id = icmp.host_id AND icmp.id = packet.icmp_id";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm->fetchAll(PDO::FETCH_ASSOC);
  }
  
  public function update($id, $transmitted, $received, $created_at, $host_id) {
    $sql = "UPDATE icmp
            SET transmitted=${transmitted}, received=${received}, created_at=DATE(${created_at}), host_id=${host_id}
            WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }
  
  public function remove($id) {
    $sql = "DELETE FROM icmp WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

api/model/packet.php:

<?php
namespace Model;

use Database\Database;
use \PDO;

require_once __DIR__."/../database/database.php";

class Packet extends Database {

  public function create($seq, $ttl, $time, $icmp_id) {
    $sql = "INSERT INTO packet (seq, ttl, time, icmp_id)
            VALUES (${seq}, ${ttl}, ${time}, ${icmp_id});";
    try {
      $this->connection->exec($sql);
      return $this->connection->lastInsertId();
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
      return null;
    } 
  }

  public function read($id) {
    $sql = "SELECT * FROM packet WHERE id = ${id}";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm->fetch(PDO::FETCH_ASSOC);
  }

  public function readAll() {
    $sql = "SELECT * FROM packet";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm->fetchAll(PDO::FETCH_ASSOC);
  }
  
  public function update($id, $seq, $ttl, $time, $icmp_id) {
    $sql = "UPDATE packet
            SET seq=${seq}, ttl=${ttl}, time=${time}, icmp_id=${icmp_id}
            WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }
  
  public function remove($id) {
    $sql = "DELETE FROM packet WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

API

api/v1/index.php:

<?php

namespace Api;

require_once __DIR__ . "/../util/ping.php";

use Util\Ping;

$host = $_GET["host"] ?? null;
$count = $_GET["count"] ?? 1;

$json = '';

if ($host && $count) {
  $ping = new Ping();
  $pingInfo = $ping->exec($host, $count);
  $json = $pingInfo;
} else {
  http_response_code(500);
  $json = ["error" => "Unknown host"];
}

header("Content-type: application/json; charset=UTF-8");
header("Access-Control-Allow-Origin: *");

echo json_encode($json);

api/util/Ping.php:

<?php

namespace Util;

use Model\Icmp;
use Model\Packet;
use Model\Host;

require_once __DIR__ . "/../model/icmp.php";
require_once __DIR__ . "/../model/packet.php";
require_once __DIR__ . "/../model/host.php";

class Ping
{

  function exec($host, $count)
  {
    $this->count = $count;
    $this->host = $host;

    $command = "ping -c{$this->count} {$this->host}";
    $this->result = shell_exec($command);

    $this->pingInfo = $this->parse();
    $this->loadDB();

    return $this->pingInfo;
  }

  function loadDB()
  {
    $icmp = new Icmp();
    $packet = new Packet();
    $host = new Host();

    $hostId = $host->readOrCreate($this->pingInfo["host"], $this->pingInfo["ip"]);
    $icmpId = $icmp->create($this->pingInfo["statistics"]["transmitted"], $this->pingInfo["statistics"]["received"], $hostId);
    foreach ($this->pingInfo["packets"] as $packetInfo) {
      $packet->create($packetInfo["seq"], $packetInfo["ttl"], $packetInfo["time"], $icmpId);
    }
  }

  function parse()
  {
    $json = [];
    $json["host"] = $this->host;
    $json["ip"] = "";
    $json["packets"] = [];
    $json["statistics"] = [];

    $regex = "/\(([\d\.]+)\)/";
    preg_match($regex, $this->result, $matches);
    $json["ip"] = $matches[1];

    $regex = "/icmp_seq=(\d+) ttl=(\d+) time=([\d\.]+)/";
    preg_match_all($regex, $this->result, $matches);
    foreach ($matches[1] as $key => $sequence) {
      $json["packets"][] = [
        "seq" => (int) $matches[1][$key],
        "ttl" => (int) $matches[2][$key],
        "time" => (float) $matches[3][$key]
      ];
    }

    $regex = "/(\d+) packets transmitted, (\d+) (packets received|received)/";
    preg_match($regex, $this->result, $matches);
    $json["statistics"]["transmitted"] = (int) $matches[1];
    $json["statistics"]["received"] = (int) $matches[2];
    $json["statistics"]["losted"] = $matches[1] - $matches[2];

    $regex = "/min\/avg\/max\/(stddev|mdev) = ([\d\.]+)\/([\d\.]+)\/([\d\.]+)\/([\d\.]+)/";
    preg_match($regex, $this->result, $matches);
    $json["statistics"]["min"] = (float) $matches[1];
    $json["statistics"]["avg"] = (float) $matches[2];
    $json["statistics"]["max"] = (float) $matches[3];
    $json["statistics"]["stddev"] = (float) $matches[4];

    return $json;
  }
}