DEV Community

Cover image for PHP Design Patterns: Active Record with Layer Supertype
Antonio Silva
Antonio Silva

Posted on

2

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.

LayerSuperType

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
Enter fullscreen mode Exit fullscreen mode

Step 2 - Database Config File:

host = 
name = database/example.db
user = 
pass = 
type = sqlite
Enter fullscreen mode Exit fullscreen mode

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)
);
Enter fullscreen mode Exit fullscreen mode

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';
}
Enter fullscreen mode Exit fullscreen mode

Step 5 - Connection Class:

<?php

namespace api;

use Exception;
use PDO;

class Connection
{
    private function __construct()
    {
    }
}
Enter fullscreen mode Exit fullscreen mode
  • open method
/**
 * the open function receives the name of the configuration 
 * file and returns a PDO connection
 * */
 public static function open(string $name): PDO
 {
 }
Enter fullscreen mode Exit fullscreen mode
  • 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");
}
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
}
Enter fullscreen mode Exit fullscreen mode
  • 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}");
Enter fullscreen mode Exit fullscreen mode
  • 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}");
Enter fullscreen mode Exit fullscreen mode
  • SQLite connection
// open connection
$conn = new PDO("sqlite:{$name}");
Enter fullscreen mode Exit fullscreen mode
  • return connection
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

// return PDO connection
return $conn;
Enter fullscreen mode Exit fullscreen mode

Step 6 - Transaction Class:

<?php

namespace api;

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

    private function __construct()
    {
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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();
}
Enter fullscreen mode Exit fullscreen mode
  • 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;
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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;
    }
}
Enter fullscreen mode Exit fullscreen mode
  • get method
// Take the Connection
public static function get(): object
{
    // Return the Connection
    return self::$conn;
}
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode
  • __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];
        }
    }
Enter fullscreen mode Exit fullscreen mode
  • __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']);
    }
Enter fullscreen mode Exit fullscreen mode
  • 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;
    }
Enter fullscreen mode Exit fullscreen mode
  • 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");
    }
Enter fullscreen mode Exit fullscreen mode
  • 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;
    }
Enter fullscreen mode Exit fullscreen mode
  • 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";
        }
    }
Enter fullscreen mode Exit fullscreen mode
  • 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.');
        }
    }
Enter fullscreen mode Exit fullscreen mode
  • 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.');
        }
    }
Enter fullscreen mode Exit fullscreen mode
  • 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);
            }
        }
    }
Enter fullscreen mode Exit fullscreen mode

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

Enter fullscreen mode Exit fullscreen mode
  • 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();
Enter fullscreen mode Exit fullscreen mode
  • LOAD
    $p1 = new Product(1);
    print $p1->description;
Enter fullscreen mode Exit fullscreen mode
  • DELETE
    $p1 = new Product(1);
    if ($p1) {
        $p1->delete();
    }
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up