PHP Design Patterns: Active Record with Layer Supertype

What would Layer SuperType be?

Layer Supertype is a design pattern that involves creating a superclass for all types in a specific layer. It is a natural and selective evolution of common base types that live and breathe inside the confines of a specific layer. It is easy to hook up its functionality to a Domain Model.

Example

Next we will have an example of how Active Record with Layer Supertype works.

Step 1 - Directory System:

📦Layer_Supertype
 ┣ 📂api
 ┃ ┣ 📜Connection.php
 ┃ ┣ 📜Record.php
 ┃ ┗ 📜Transaction.php
 ┣ 📂classes
 ┃ ┗ 📜Product.php
 ┣ 📂config
 ┃ ┗ 📜config.ini
 ┣ 📂database
 ┃ ┗ 📜example.db
 ┗ 📜index.php

Step 2 - Database Config File:

host = 
name = database/example.db
user = 
pass = 
type = sqlite

Step 3 - Database:

CREATE TABLE product(
  id INTEGER PRIMARY KEY NOT NULL,
  description TEXT,
  stock FLOAT,
  cost_price FLOAT,
  sale_price FLOAT,
  bar_code TEXT,
  date_register DATE,
  origin CHAR(1)
);

Step 4 - Product Class:

<?php

namespace classes;

use api\Record;

class Product extends Record
{
    // constant to maintain the name of the database table.
    public const TABLENAME = 'product';
}

Step 5 - Connection Class:

<?php

namespace api;

use Exception;
use PDO;

class Connection
{
    private function __construct()
    {
    }
}

• open method

/**
 * the open function receives the name of the configuration 
 * file and returns a PDO connection
 * */
 public static function open(string $name): PDO
 {
 }

• check file

// Checks if the received file exists
if (file_exists("config/{$name}.ini")) {
    // Reads the file and returns a vector with the file data
    $db = parse_ini_file("config/{$name}.ini");
} else {
    // Throw an exception if the file is not found
    throw new Exception("Arquivo {$name} não encontrado");
}

• Read the file information

// Use isset() to check if information has been set
$host = isset($db['host']) ? $db['host'] : null;
$name = isset($db['name']) ? $db['name'] : null;
$user = isset($db['user']) ? $db['user'] : null;
$pass = isset($db['pass']) ? $db['pass'] : null;
$type = isset($db['type']) ? $db['type'] : null;

• Check database type

// Check database type
switch ($type) {
    // if the database is PostgreSQL
    case 'pgsql':
        break;

    // if the database is MySQL
    case 'mysql':
        break;

   // if the database is SQLite
   case 'sqlite':
       break;
}

• PostgreSQL connection

// set the port
$port = isset($db['port']) ? $db['port'] : '5432';
// open connection
$conn = new PDO("pgsql:dbname={$name}; user={$user};
password={$pass}; host={$host}; port={$port}");

• MySQL connection

// set the port
$port = isset($db['port']) ? $db['port'] : '3306';
// open connection
$conn = new PDO("mysql:host={$host}; dbname={$name}; 
port={$port}", "{$user}", "{$pass}");

• SQLite connection

// open connection
$conn = new PDO("sqlite:{$name}");

• return connection

// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// return PDO connection
return $conn;

Step 6 - Transaction Class:

<?php

namespace api;

class Transaction
{
    // Property for storing connection status
    private static $conn;

    private function __construct()
    {
    }
}

• open method

// Open Transaction
public static function open(string $database): void
{
    // Open Connection with DataBase
    self::$conn = Connection::open($database);

    /**
     * Start a transaction in PDO
     * The beginTransaction() method turns off
     * the autocommit mode
     */
    self::$conn->beginTransaction();
}

• close method

// Close Transaction
public static function close(): void
{
    // If there is a connection
    if (self::$conn) {
        // commit a transaction
        self::$conn->commit();
        // Clear the connection
        self::$conn = null;
    }
}

• rollback method

// Rollback Transaction
public static function rollback(): void
{
    // If there is a connection
    if (self::$conn) {
        /**
         * The PDO::rollback() method rolls back all
         * changes made to the database.
         * Also, it returns the connection to the autocommit mode.
         */
        self::$conn->rollback();
        // Clear the connection
        self::$conn = null;
    }
}

• get method

// Take the Connection
public static function get(): object
{
    // Return the Connection
    return self::$conn;
}

Step 7 - Record Class:

<?php

namespace api;

use PDO;

/**
 * Record will only be a reading class,
 * so cannot instance an object
 * directly from her.
 */
abstract class Record
{
    /**
     * @var mixed[]
     */
    protected array $data;

    public function __construct(int $id = null)
    {
        // If an id is passed.
        if ($id) {
            // Metodo load call.
            $object = $this->load($id);

            if ($object) {
                // Feed the object itself with these attributes.
                $this->fromArray($object->toArray());
            }
        }
    }
}

• __get and __set methods

    /**
     * receive the name and value
     * of the property respectively.
     * */
    public function __set(string $prop, mixed $value): void
    {
        // If the value is null.
        if ($value === null) {
            // performs unset in the vector in the specific position.
            unset($this->data[$prop]);
        } else {
            $this->data[$prop] = $value;
        }
    }

    /**
     * receive the name of the property and pass it
     * to the data attribute as the key.
     * */
    public function __get(string $prop): mixed
    {
        // Check if the attribute exists.
        if (isset($this->data[$prop])) {
            return $this->data[$prop];
        }
    }

• __isset and __clone methods

    // Determine if a variable is declared and is different than null.
    public function __isset(string $prop): bool
    {
        return isset($this->data[$prop]);
    }

    // Method used to clone an entire object.
    public function __clone(): void
    {
        // Eliminate id from cloned object.
        unset($this->data['id']);
    }

• fromArray and toArray methods

    // Method used for popular an object from a vector.
    public function fromArray(array $data): void
    {
        $this->data = $data;
    }

    // Method used to export the object in the form of a vector.
    public function toArray(): array
    {
        return $this->data;
    }

• getEntity method

    // Method used to read the table name.
    protected function getEntity(): string
    {
        // Return the name of the daughter class used at the time.
        $class = get_class($this);

        // Return the value of the accountant of the daughter class.
        return constant("{$class}::TABLENAME");
    }

• prepare method

    // Travel the data vector.
    protected function prepare(array $data): array
    {
        $prepared = [];
        // Follow with a Foreach.
        foreach ($data as $key => $value) {
            // If it is a scalar.
            if (is_scalar($value)) {
                // Set up a new vector with the result of the escape method.
                $prepared[$key] = $this->escape($value);
            }
        }

        // return new vector.
        return $prepare;
    }

• escape method

    // For each value of the vector.
    protected function escape(mixed $value): mixed
    {
        /**
         * For each value of the vector
         * AND
         * Not empty
         * */
        if (is_string($value) && (!empty($value))) {
            // adds \ in quotes
            $value = addslashes($value);
            // Return the value between simple quotes.
            return "'$value'";
        // If it's a boolean.
        } else if (is_bool($value)) {
            // Return converted to True or False.
            return $value ? 'TRUE' : 'FALSE';
        } else if ($value !== '') {
            return $value;
        } else {
            // No content returns as NULL.
            return "NULL";
        }
    }

• load method

    // Upload data
    public function load(int $id): object
    {
        /**
         * SQL statement
         * $this->getEntity() = table name.
         * */
        $sql = "SELECT * FROM {$this->getEntity()} WHERE id = :id";
        // Retonate active connection
        if ($conn = Transaction::get()) {
            // perform the prepare method.
            $result = $conn->prepare($sql);
            /*
             * Binds a parameter to the specified variable name.
             * PDO::PARAM_INT Represents the SQL INTEGER data type.
             */
            $result->bindParam(':id', $id, PDO::PARAM_INT);
            // execute sql statement.
            $result->execute();

            // If there is a return.
            if ($result) {
                // Fetches the next row and returns it as an object.
                return $result->fetchObject(get_class($this));
            }
        } else {
            throw new Exception('There is no active connection.');
        }
    }

• store method

    public function store(): bool
    {
        /*
         * If you do not have data in id position
         * OR
         * that object does not exist in the database
         * We proceed to the insert
         */
        if (empty($this->data['id']) || (!$this->load($this->data['id']))) {
            // Data processing.
            $prepared = $this->prepare($this->data);
            // SQL statement.
            $sql = "INSERT INTO {$this->getEntity()}" .
                    // Implode data names of the columns.
                    '(' . implode(', ', array_keys($prepared)) . ')' .
                    ' VALUES ' .
                    // Implode data columns values.
                    '(' . implode(', ', array_values($prepared)) . ')';
        } else {
            // Data processing.
            $prepared = $this->prepare($this->data);
            // Vector set.
            $set = [];
            // Travel the data.
            foreach ($prepared as $column => $value) {
                // Each pair of information is placed in the vector to be.
                $set[] = "$column = $value";
            }

            // SQL statement.
            $sql = "UPDATE {$this->getEntity()}";
            // implode vector set.
            $sql .= " SET " . implode(' , ', $set);
            // Only for that id..
            $sql .= " WHERE id = " . (int) $this->data['id'];
        }

        // Retonate active connection
        if ($conn = Transaction::get()) {
            // execute sql statement
            return $conn->exec($sql);
        } else {
            throw new Exception('There is no active connection.');
        }
    }

• delete method

    public function delete(int $id = null): bool
    {
        $id = $id ? $id : $this->data['id'];
        // SQL statement.
        $sql = "DELETE FROM {$this->getEntity()} WHERE id = :id";

        // Retonate active connection
        if ($conn = Transaction::get()) {
            // perform the prepare method.
            $result = self::$conn->prepare($sql);
             /*
             * Binds a parameter to the specified variable name.
             * PDO::PARAM_INT Represents the SQL INTEGER data type.
             */
            $result->bindParam(':id', $id, PDO::PARAM_INT);
            // execute sql statement.
            if ($result->execute()) {
                // returns an array indexed by column name as returned in your result set
                return $result->fetch(PDO::FETCH_ASSOC);
            }
        }
    }

Testing

<?php

require_once 'api/Connection.php';
require_once 'api/Record.php';
require_once 'api/Transaction.php';
require_once 'classes/Product.php';

use api\Connection;
use api\Record;
use api\Transaction;
use classes\Product;

try {
     /**
     * open Transaction
     * Passive the name of the connection
     * file with the database.
     */
    Transaction::open('config');
    // close Transaction
    Transaction::close();
} catch (Exception $e) {
    // Undo the command package if there is any error
    Transaction::rollback();
    echo $e->getMessage();
}

• STORE

    $product = new Product();
    $product->description = 'Juice';
    $product->stock = 8;
    $product->cost_price = 12;
    $product->sale_price = 18;
    $product->bar_code = '123123123';
    $product->origin = 'S';
    $product->date_register = date('Y-m-d');
    $product->store();

• LOAD

    $p1 = new Product(1);
    print $p1->description;

• DELETE

    $p1 = new Product(1);
    if ($p1) {
        $p1->delete();
    }