Database Object Class Example

ExamDB.php

<?php
/**
 * $db = ExamDB::create('driver', [
 *          'dbuser' => 'user', ------. For Sqlite allowed empty
 *          'dbpass' => 'pass', ------.
 *          'port'   => 3306,   ------ // allowed empty to use default port
 *          'dbname' => 'database_name',
 *          'attributes' => [
 *              PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // PDO Attribute
 *          ]
 *      ]
 * );
 * // direct execution of PDO::query();
 * $db->query("SELECT * FROM {$db->quoteIdentifier('value')}");
 * // use nested multi quote
 * $db->quote(['a', 'b']) == ["'a'", "'b'"]
 * // quoting identifier
 * $db->quoteIdentifier('my.table') == "`my`.`table`" -> depending of driver (back tick use for sqlite & mysql)
 * 
 */
declare(strict_types=1);

namespace MyNameSpace;

use PDO;
use PDOException;

/**
 * Class ExamDB
 * @package MyNameSpace
 */
class ExamDB
{
    /**
     * @var bool
     */
    protected $debug = false;
    /**
     * @var PDO
     */
    protected $conn;
    /**
     * Dsn Connection
     */
    protected $dsn;
    /**
     * @var array
     */
    protected $options = [];
    /**
     * @var string Driver
     */
    protected $driver;
    /**
     * @var array parameter to use as reconnect
     */
    protected $params = [];
    /**
     * @var \Exception
     */
    protected $error;

    /**
     * @var string
     */
    protected $identifier = '"';

    /**
     * ExamDB Constructor
     * @access private Internal Class Use function to create as singleton
     */
    private function __construct()
    {
        
    }

    /**
     * @param string $driver
     * @param array  $options
     * @param bool   $debug boolean show exception debug
     *
     * @return ExamDB
     * @throws \InvalidArgumentException
     */
    public static function create($driver, array $options = [], $debug = false) : ExamDB
    {
        $exam =  new static(); // use static allowed to call into nested children
        $exam->debug = boolval($debug);
        if (!$exam->isDriverAvailable($driver)) {
            $exam->error = new \InvalidArgumentException(
                sprintf(
                    "Invalid or unsupported selected driver %s",
                    $exam->_sanitizeStringForDriver($driver)
                ),
                E_USER_ERROR
            );
            if ($exam->debug) {
                throw $exam->error;
            }

            return $exam;
        }

        $exam->driver  = $exam->_sanitizeStringForDriver($driver);
        $exam->options = $options;
        $exam->_createConnection();
        return $exam;
    }

    /**
     * Sanitize Driver
     *
     * @param string $driver selected driver
     * @purpose for checking avail drivers
     * @return string
     */
    private function _sanitizeStringForDriver($driver) : string
    {
        $driver = is_string($driver) ? \strtolower(\trim($driver)) : '';
        if (!$driver) {
            return '';
        }
        /**
         * Sanitize Of fallback driver
         */
        switch ($driver) {
            case 'mariadb': // mysqli
            case 'mysqli': // mysqli
            case 'pdo_mysqli': // mysqli
            case 'pdo_mysql': // mysqli
                $driver = 'mysql';
                break;
            case 'sqlite3': // sqlite3
            case 'pdo_sqlite3':
            case 'pdo_sqlite':
                $driver = 'sqlite';
                break;
            case 'oci8': // oracle OCI8
            case 'pdo_oci8': // oracle OCI8
            case 'pdo_oci': // oracle OCI8
                $driver = 'oci';
                break;
            case 'psql': // common mistake call as psql
            case 'postgre': // common mistake call as postgre
            case 'postgresql': // common mistake call as postgreSQL
            case 'pdo_pgsql': // common mistake call as pdo_pgsql
            case 'pdo_psql': // common mistake call as pdo_psql
                $driver = 'pgsql';
                break;
            case 'pdo_sqlsrv': // common mistake call as postgreSQL
                $driver = 'sqlsrv';
                break;
        }

        return $driver;
    }

    /**
     * is Driver available
     *
     * @param string $driver selected driver
     * @return boolean
     */
    public function isDriverAvailable($driver) : bool
    {
        $driver = $this->_sanitizeStringForDriver($driver);
        return $driver && in_array($driver, PDO::getAvailableDrivers());
    }

    /**
     * Initialize Param
     */
    private function _initConnection()
    {
        $class = new \ReflectionClass(($this->conn || 'PDO'));
        $this->conn = $class->newInstanceArgs($this->params);
    }

    /**
     * Creating PDO Connection
     *
     * @return PDO | null
     * @throws \ErrorException
     * @throws PDOException
     */
    private function _createConnection()
    {
        if (empty($this->options)) {
            $this->error = new \ErrorException('Empty Configuration', E_USER_ERROR);
            if ($this->debug) {
                throw $this->error;
            }
            return null;
        }
        if ($this->driver !='sqlite') {
            if (empty($this->options['dbuser'])) {
                $this->error = new \ErrorException('Empty Database For Connection', E_USER_ERROR);
                if ($this->debug) {
                    throw $this->error;
                }
                return null;
            }
            // fallback database password
            if (empty($this->options['dbpass'])) {
                $this->options['dbuser'] = '';
            }
        }
        $attr_default = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        ];
        if (empty($this->options['attributes'])) {
            $this->options['attributes'] = $attr_default;
        }

        if (!is_array($this->options['attributes'])) {
            $this->error = new \ErrorException('Invalid PDO Attributes, Options atributes must be as array', E_USER_ERROR);
            if ($this->debug) {
                throw $this->error;
            }
            return null;
        }

        // make exceptions
        $this->options['attributes'] =  array_merge($this->options['attributes'], $attr_default);
        $dsn = $this->constructPDODsn($this->driver);
        $this->params = [
            'dsn'    => $dsn,
            'dbuser' => (isset($this->options['dbuser']) ? $this->options['dbuser'] : null),
            'dbpass' => (isset($this->options['dbpass']) ? $this->options['dbuser'] : null),
            'attributes' => $this->options['attributes']
        ];
        // set Identifier for SQlite & MySQL
        $this->identifier = in_array($this->driver, ['mysql', 'sqlite'])
            ? '`' : $this->identifier;
        try {
            $this->conn = new PDO(
                $dsn,
                $this->params['dbuser'],
                $this->params['dbpass'],
                $this->params['attributes']
            );
        } catch (PDOException $e) {
            $this->error = $e;
            if ($this->debug) {
                throw $this->error;
            }

            return null;
        }

        return $this->conn;
    }

    /**
     * Constructs the MySql PDO DSN.
     *
     * @param string $driver
     *
     * @return string The DSN.
     */
    protected function constructPDODsn($driver) : string
    {
        $params = $this->options;
        $dsn = $driver . ':';
        if ($dsn == 'sqlite:') {
            if (isset($params['path'])) {
                $dsn .= $params['path'];
            } elseif (isset($params['memory'])) {
                $dsn .= ':memory:';
            }
            return $dsn;
        } elseif ($dsn == 'mysql:') {
            // add default
            if (!isset($params['host'])) {
                $params['host'] = 'localhost';
            }
            if ($params['host'] != '') {
                $dsn .= 'host=' . $params['host'] . ';';
            }
            if (isset($params['port'])) {
                $dsn .= 'port=' . $params['port'] . ';';
            }
            if (isset($params['dbname'])) {
                $dsn .= 'dbname=' . $params['dbname'] . ';';
            }
            if (isset($params['unix_socket'])) {
                $dsn .= 'unix_socket=' . $params['unix_socket'] . ';';
            }
            if (isset($params['charset'])) {
                $dsn .= 'charset=' . $params['charset'] . ';';
            }
        } elseif ($dsn == 'pgsql:') {
            if (isset($params['host']) && $params['host'] != '') {
                $dsn .= 'host=' . $params['host'] . ' ';
            }

            if (isset($params['port']) && $params['port'] != '') {
                $dsn .= 'port=' . $params['port'] . ' ';
            }

            if (isset($params['dbname'])) {
                $dsn .= 'dbname=' . $params['dbname'] . ' ';
            } else {
                // Used for temporary connections to allow operations like dropping the database currently connected to.
                // Connecting without an explicit database does not work, therefore "template1" database is used
                // as it is certainly present in every server setup.
                $dsn .= 'dbname=template1' . ' ';
            }
            if (isset($params['sslmode'])) {
                $dsn .= 'sslmode=' . $params['sslmode'] . ' ';
            }
        } elseif ($dsn == 'sqlsrv:') {
            $dsn .= 'server=';
            if (isset($params['host'])) {
                $dsn .= $params['host'];
            }
            if (isset($params['port']) && !empty($params['port'])) {
                $dsn .= ',' . $params['port'];
            }
            if (isset($params['dbname'])) {
                $dsn .= ';Database=' .  $params['dbname'];
            }
            if (isset($params['MultipleActiveResultSets'])) {
                $dsn .= '; MultipleActiveResultSets=' . ($params['MultipleActiveResultSets'] ? 'true' : 'false');
            }
        } elseif ($dsn == 'oci:') {
            $dsn .= 'dbname=';
            if ( ! empty($params['host'])) {
                if ( ! isset($params['port'])) {
                    $params['port'] = 1521;
                }

                $serviceName = $params['dbname'];

                if ( ! empty($params['servicename'])) {
                    $serviceName = $params['servicename'];
                }

                $service = 'SID=' . $serviceName;
                $pooled  = '';
                $instance = '';

                if (isset($params['service']) && $params['service'] == true) {
                    $service = 'SERVICE_NAME=' . $serviceName;
                }

                if (isset($params['instancename']) && ! empty($params['instancename'])) {
                    $instance = '(INSTANCE_NAME = ' . $params['instancename'] . ')';
                }

                if (isset($params['pooled']) && $params['pooled'] == true) {
                    $pooled = '(SERVER=POOLED)';
                }

                $dsn .= '(DESCRIPTION=' .
                '(ADDRESS=(PROTOCOL=TCP)(HOST=' . $params['host'] . ')(PORT=' . $params['port'] . '))' .
                '(CONNECT_DATA=(' . $service . ')' . $instance . $pooled . '))';
            }

            $dsn .= isset($params['dbname']) ? $params['dbname'] : '';
        }

        return $dsn;
    }

    /**
     * @return bool
     */
    public function isConnected() : bool
    {
        if ($this->conn instanceof PDO) {
            try {
                $this->conn->query("SELECT 1");
                return true;
            } catch(PDOException $err) {
                return false;
            }
        }

        return false;
    }

    /**
     * Reconnect The Connection
     */
    public function ping()
    {
        if ($this->conn instanceof PDO && ! $this->isConnected()) {
            $this->_initConnection();
        }
    }

    /**
     * @return \Exception
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * Trimming table for safe usage
     *
     * @param mixed $table
     * @return mixed
     */
    public function trimSelector($table)
    {
        if (! $this->isConnected()) {
            return false;
        }

        if (is_array($table)) {
            return array_map(array($this, 'trimSelector'), $table);
        } elseif (is_object($table)) {
            foreach (get_object_vars($table) as $key => $value) {
                $table->{$key} = $this->trimSelector($value);
            }
            return $table;
        }
        if (is_string($table)) {
            $tableArray = explode('.', $table);
            $tableArray = array_filter($tableArray);
            foreach ($tableArray as $key => $value) {
                $tableArray[$key] = trim(
                    trim(
                        trim($value),
                        $this->identifier
                    )
                );
            }
            $table = implode('.', $tableArray);
        }
        return $table;
    }

    /**
     * Quote string for insert into SQL execution
     *  Allowed nested
     *
     * @param mixed $quoteStr value to quote
     * @param mixed $param_type
     * @return mixed
     */
    public function quote($quoteStr, $param_type = null)
    {
        if (!$this->isConnected()) {
            return null;
        }
        if (is_resource($quoteStr)) {
            throw new \InvalidArgumentException("Could not quoting resource values", E_USER_ERROR);
        }
        if (is_array($quoteStr)) {
            foreach ($quoteStr as $key => $value) {
                $quoteStr[$key] = $this->quote($value, $param_type);
            }
            return $quoteStr;
        } elseif (is_object($quoteStr)) {
            foreach (get_object_vars($quoteStr) as $key => $value) {
                $quoteStr->{$key} = $this->quote($value, $param_type);
            }
            return $quoteStr;
        }

        return $this->conn->quote($quoteStr, $param_type);
    }

    /**
     * Alternative multi variable type quoted identifier
     *
     * @param mixed $quoteStr
     * @return mixed
     */
    public function quoteIdentifier($quoteStr)
    {
        $quoteStr = $this->trimSelector($quoteStr);
        if (is_resource($quoteStr)) {
            throw new \InvalidArgumentException("Could not quoting resource values", E_USER_ERROR);
        }
        if (is_array($quoteStr)) {
            foreach ($quoteStr as $key => $value) {
                $quoteStr[$key] = $this->quoteIdentifier($value);
            }
            return $quoteStr;
        } elseif (is_object($quoteStr)) {
            foreach (get_object_vars($quoteStr) as $key => $value) {
                $quoteStr->{$key} = $this->quoteIdentifier($value);
            }
            return $quoteStr;
        }

        $tableArray = explode('.', trim($quoteStr));
        return $this->identifier
            . implode("{$this->identifier}.{$this->identifier}", $tableArray)
            . $this->identifier;
    }

    /**
     * Getting PDO Connection if available
     *
     * @return PDO
     */
    public function getPDO()
    {
        return $this->conn;
    }

    /**
     * Getting parameter
     *
     * @return array
     */
    public function getParams() : array
    {
        return $this->params;
    }

    /**
     * Magic Method By pass method Arguments
     *
     * @param string $name Method Name
     * @param array  $arguments
     * @return mixed
     * @throw \BadMethodCallException
     */
    public function __call($name, $arguments)
    {
        if ($this->conn && method_exists($this->conn, $name)) {
            return call_user_func_array([$this->conn, $name], $arguments);
        }
        throw new \BadMethodCallException(
            sprintf("Call to undefined Method %s", $name),
            E_USER_ERROR
        );
    }
}