Host DNS Model

Code

host-dns
├── .env
├── Dockerfile
├── database
│   ├── config.php
│   ├── database.php
│   ├── schema.mwb
│   └── schema.sql
├── docker-compose.yml
├── install
│   └── index.php
├── model
│   ├── dns.php
│   └── host.php
└── test.php

docker-compose.yml:

version: "3"

services:
  web:
    container_name: web
    image: ifpb/php:7.3-apache-pdo
    build: .
    networks:
      - app-tier
    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
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE: ${MYSQL_DATABASE}
      MYSQL_USER: ${MYSQL_USER}
      MYSQL_PASSWORD: ${MYSQL_PASSWORD}
    networks:
      - app-tier
    ports:
      - 3306:3306

  adminer:
    container_name: adminer
    image: adminer:4.7.5
    restart: always
    networks:
      - app-tier
    ports:
      - 8081:8080
    depends_on:
      - mysql

networks:
  app-tier:
    driver: bridge

Dockerfile:

FROM php:7.3-apache

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

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

.env:

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

Database

database/schema.sql:

DROP DATABASE IF EXISTS `computer`;
CREATE DATABASE `computer`;
USE `computer`;

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

INSERT INTO `host`
  (`name`)
VALUES
  ('pc01'),
  ('pc02');

DROP TABLE IF EXISTS `dns`;
CREATE TABLE `dns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ip` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `dns`
  (`ip`)
VALUES
  ('8.8.8.8'),
  ('8.8.4.4');

DROP TABLE IF EXISTS `host_dns`;
CREATE TABLE `host_dns` (
  `id` int NOT NULL AUTO_INCREMENT,
  `host_id` int NOT NULL,
  `dns_id` int NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `host_id` (`host_id`),
  INDEX `dns_id` (`dns_id`),
  CONSTRAINT `host_id_fk` 
    FOREIGN KEY (`host_id`) 
    REFERENCES `host` (`id`),
  CONSTRAINT `dns_id_fk` 
    FOREIGN KEY (`dns_id`) 
    REFERENCES `dns` (`id`)
);

INSERT INTO `host_dns`
  (`host_id`, `dns_id`)
VALUES
  (1, 1),
  (1, 2),
  (2, 1);

Install

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: home.php');;
} catch (PDOException $e) {
  echo 'Connection failed: ' . $e->getMessage();
}

database/config.php:

<?php

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

http://localhost:8080/install/

PDO Model

Database Model

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();
    }
  }

}

Host Model

model/host.php:

<?php
namespace Model;

use Database\Database;
use Model\Dns;
use \PDO;

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

class Host extends Database {

  public function create($name) {
    $sql = "INSERT INTO host (name) VALUES ('${name}')";
    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 readByName($name) {
    $sql = "SELECT * FROM host WHERE name='${name}'";
    $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) {
    $result = $this->readByName($name);

    if ($result) {
      return $result;
    } else {
      return $this->create($name);
    }
  }
  
  public function update($id, $name) {
    $sql = "UPDATE host
            SET name='${name}'
            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(); 
    }
  }

  public function getDns($host_id) {
    $allDns = [];
    $dns = new Dns();
    $sql = "SELECT * FROM host_dns INNER JOIN dns WHERE host_id = ${host_id} and dns_id = dns.id";
    $pdoStm = $this->connection->query($sql);
    if ($pdoStm) {
      $matches = $pdoStm->fetchAll(PDO::FETCH_ASSOC);
      foreach ($matches as $match) {
        $allDns[] = $dns->read($match["id"]);
      }
      return $allDns;
    } else {
      return null;
    };
  }

  public function addDns($host_id, $dns_id) {
    $sql = "INSERT INTO host_dns (host_id, dns_id) VALUES (${host_id}, ${dns_id})";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

  public function rmDns($host_id, $dns_id) {
    $sql = "DELETE FROM host_dns WHERE host_id = ${host_id} and dns_id = ${dns_id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

Dns Model

model/dns.php:

<?php
namespace Model;

use Database\Database;
use \PDO;

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

class Dns extends Database {

  public function create($ip) {
    $sql = "INSERT INTO dns (ip) VALUES ('${ip}')";
    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 dns WHERE id = ${id}";
    $pdoStm = $this->connection->query($sql);
    return $pdoStm ? $pdoStm->fetch(PDO::FETCH_ASSOC) : null;
  }

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

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

  public function readOrCreate($ip) {
    $result = $this->readByIp($ip);

    if ($result) {
      return $result;
    } else {
      return $this->create($ip);
    }
  }
  
  public function update($id, $ip) {
    $sql = "UPDATE dns
            SET ip='${ip}'
            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 dns WHERE id=${id}";
    try {
      return $this->connection->exec($sql);
    } catch(PDOExecption $e) { 
      $this->connection->rollback(); 
      print "Error!: " . $e->getMessage(); 
    }
  }

}

How to CRUD

test.php:

<pre>
<?php

use Model\Host;
use Model\Dns;

require_once('model/host.php');
require_once('model/dns.php');

$host = new Host();
$dns = new Dns();

// Create  
$hostId = $host->create('pc3');
var_dump($hostId); //=> string(1) "3"
$dnsId = $dns->create('1.1.1.1');
var_dump($dnsId);  //=> string(1) "3"

// Read
var_dump($host->read($hostId));
//=>
// array(2) {
//   ["id"]=>string(1) "3"
//   ["name"]=>string(3) "pc3"
// }

// Update
$host->update($hostId, 'pc03');
var_dump($host->read($hostId));
//=>
// array(2) {
//   ["id"]=>string(1) "3"
//   ["name"]=>string(4) "pc03"
// }

// Delete
var_dump($host->remove($hostId)); //=> int(1)
var_dump($host->read($hostId));   //=> bool(false)
var_dump($host->read($dnsId));    //=> bool(false)

$host2 = $host->readOrCreate('pc02');
$allDns = $host->getDns($host2["id"]);
var_dump($allDns);
//=>
// array(1) {
//   [0]=>array(2) {
//     ["id"]=>string(1) "1"
//     ["ip"]=>string(7) "8.8.8.8"
//   }
// }

$host->addDns($host2["id"], 2);
$host->addDns($host2["id"], 3);
$allDns = $host->getDns($host2["id"]);
var_dump($allDns);
// =>
// array(3) {
//   [0]=>array(2) {
//     ["id"]=>  string(1) "1"
//     ["ip"]=>  string(7) "8.8.8.8"
//   }
//   [1]=>array(2) {
//     ["id"]=>  string(1) "2"
//     ["ip"]=>  string(7) "8.8.4.4"
//   }
//   [2]=>array(2) {
//     ["id"]=>  string(1) "3"
//     ["ip"]=>  string(7) "1.1.1.1"
//   }
// }

$host->rmDns($host2["id"], 3);
$allDns = $host->getDns($host2["id"]);
var_dump($allDns);
// =>
// array(2) {
//   [0]=>array(2) {
//     ["id"]=>  string(1) "1"
//     ["ip"]=>  string(7) "8.8.8.8"
//   }
//   [1]=>array(2) {
//     ["id"]=>  string(1) "2"
//     ["ip"]=>  string(7) "8.8.4.4"
//   }
// }
?>
</pre>

http://localhost:8080/test.php