kristuff / patabase

lib/Driver/Mysql/MysqlDriver.php

<?php declare(strict_types=1);

/** 
 *  ___      _        _
 * | _ \__ _| |_ __ _| |__  __ _ ___ ___
 * |  _/ _` |  _/ _` | '_ \/ _` (_-</ -_)
 * |_| \__,_|\__\__,_|_.__/\__,_/__/\___|
 * 
 * This file is part of Kristuff\Patabase.
 * (c) Kristuff <kristuff@kristuff.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @version    1.0.1
 * @copyright  2017-2022 Christophe Buliard
 */

namespace Kristuff\Patabase\Driver\Mysql;

use Kristuff\Patabase\Driver\ServerDriver;

/**
 * Class MysqlDriver
 *
 * Data types:
 *  CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
 *  INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
 *  DECIMAL, NUMERIC
 *  FLOAT, DOUBLE
 *  BIT(n)
 *   
 */
class MysqlDriver extends ServerDriver
{
    /**
     * List of DSN attributes
     *
     * @access protected
     * @var array
     */
    protected $dsnAttributes = array(
        'hostname',
        'username',
        'password',
        'database'
    );

    /**
     * Escape identifier
     *
     * @access public
     * @param string  $identifier
     *
     * @return string
     */
    public function escapeIdentifier(string $identifier): string
    {
        return '`' . $identifier .'`';
    }

    /**
     * Escape value
     *
     * @access public
     * @param string  $value
     *
     * @return string
     */
    public function escapeValue(string $value): string
    {
        return "'".$value."'";
    }

    /**
     * Create a new PDO connection
     *
     * @access public
     * @param array   $settings
     *
     * @return void
     */
    public function createConnection(array $settings): void
    {
        $charset = !empty($settings['charset'])  ?  ';charset='.$settings['charset']  : ';charset=utf8';
        $port    = !empty($settings['port'])     ?  ';port='.$settings['port']        : '';
        $dbname  = !empty($settings['database']) ?  ';dbname='.$settings['database']  : '';
        $options = [
          //  \PDO::ATTR_EMULATE_PREPARES => false,
        ];

        $this->pdo = new \PDO(
            'mysql:host='.$settings['hostname'] .$port .$dbname .$charset,
            $settings['username'],
            $settings['password'],
            $options
        );

        // emulate prepare is true by default in mysql
        // TODO 
        //  $this->pdo->setAttribute(\PDO::ATTR_EMULATE_PREPARES, false);
        //  $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
    }

    /**
     * Get last inserted id
     *
     * @access public
     * @return string
     */
    public function lastInsertedId(): string
    {
        return $this->pdo->lastInsertId();
    }

    /**
     * Enable foreign keys
     *
     * @access public
     * @return void
     */
    public function enableForeignKeys(): void
    {
        $this->pdo->exec('SET FOREIGN_KEY_CHECKS=1');
    }

    /**
     * Disable foreign keys
     *
     * @access public
     * @return void
     */
    public function disableForeignKeys(): void
    {
        $this->pdo->exec('SET FOREIGN_KEY_CHECKS=0');
    }

    /**
     * Get whether foreign keys are enabled or not
     * For compatibility with Sqlite, not implemented in that driver (allways enabled), return true
     * 
     * @access public
     * @return bool     true if foreign keys are enabled, otherwise false
     */
    public function isForeignKeyEnabled(): bool
    {
        return true;
    }
    
    /**
     * Add a foreign key
     * 
     * @access public
     * @param string   $fkName         The constraint name
     * @param string   $srcTable       The source table
     * @param string   $srcColumn      The source column 
     * @param string   $refTable       The referenced table
     * @param string   $refColumn      The referenced column
     *
     * @return bool    True if the foreign key has been created, otherwise false
     */
    public function addForeignKey(string $fkName, string $srcTable, string $srcColumn, string $refTable, string $refColumn): bool
    {
        $sql = sprintf('ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s(%s)',
                       $this->escape($srcTable),
                       $fkName,
                       $this->escape($srcColumn),
                       $this->escape($refTable),
                       $this->escape($refColumn)
        );
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Drop a foreign key
     * 
     * @access public
     * @param string   $fkName         The constraint name
     * @param string   $tableName      The source table
     *
     * @return bool    True if the foreign key has been dropped, otherwise false
     */
    public function dropForeignKey(string $fkName, string $tableName): bool
    {
        $sql = sprintf('ALTER TABLE %s DROP FOREIGN KEY %s',
                       $this->escape($tableName),
                       $fkName
        );
        return $this->prepareAndExecuteSql($sql);
    }
    
    /**
     * Checks if a database exists
     *
     * @access public
     * @param string   $databaseName   The database name
     *
     * @return bool     True if the given database exists, otherwise false.
     */
    public function databaseExists(string $databaseName) : bool
    {
        $sql = 'SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = :dbName'; 
        $query = $this->pdo->prepare($sql);
        $query->bindValue(':dbName',  $databaseName, \PDO::PARAM_STR);
        $query->execute();
        return (bool) $query->fetchColumn();
    }

    /**
     * Create a database
     *
     * @access public
     * @param string   $databaseName   The database name
     * @param string   $owner          The database owner. This parameter is not honored in Mysql. Default is null
     * @param string   $template       The template to use. This parameter is not honored in Mysql. Default is null
     *
     * @return bool     True if the database has been created, otherwise false.
     */
    public function createDatabase(string $databaseName, ?string $owner = null, ?string $template = null): bool
    {
        $sql = trim(sprintf('CREATE DATABASE %s',  $this->escape($databaseName)));
        return $this->prepareAndExecuteSql($sql);
    }
    
    /**
     * Create a user
     *
     * @access public
     * @param string   $userName         The user name
     * @param string   $userpassword     The user password
     *
     * @return bool     True if the user has been created, otherwise false. 
     */
    public function createUser(string $userName, string $userPassword) : bool
    {
        $sql = trim(sprintf('CREATE USER %s@%s IDENTIFIED BY %s', 
                    $this->escape($userName),
                    $this->escape($this->getHostName()),
                    "'" . $userPassword ."'"
        ));
        return $this->prepareAndExecuteSql($sql);
   }

    /**
     * Drop a user
     *
     * @access public
     * @param string   $userName         The user name
     * @param bool     $ifExists         (optional) True if the user must be deleted only when exists. Default is false.
     *
     * @return bool     True if the user has been dropped or does not exist when $ifExists is set to True, otherwise false. 
     */
    public function dropUser(string $userName, bool $ifExists = false): bool
    {
        $sql = trim(sprintf('DROP USER %s %s@%s', 
                    $ifExists === true ? 'IF EXISTS': '',
                    $this->escape($userName),
                    $this->escape($this->getHostName())
        ));
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Grant user permissions on given database
     *
     * @access public
     * @param string   $databaseName     The database name
     * @param string   $userName         The user name
     *
     * @return bool     True if the user has been granted, otherwise false. 
     */
     public function grantUser(string $databaseName, string $userName): bool
    {
        $sql = trim(sprintf('GRANT ALL ON %s.* TO %s@%s; FLUSH PRIVILEGES;', 
            $this->escape($databaseName),
            $this->escape($userName),
            $this->escape($this->getHostName())
        ));
        return $this->prepareAndExecuteSql($sql);
    }

    /**
     * Get the SQL for show databases
     *
     * @access public
     * @return string
     */
    public function sqlShowDatabases(): string
    {
        return 'SHOW DATABASES';
    }

    /**
     * Get the SQL for show tables
     *
     * @access public
     * @return string
     */
    public function sqlShowTables(): string
    {
        return 'SHOW TABLES';
    }

    /**
     * Get the SQL for show users
     *
     * @access public
     * @return string
     */
    public function sqlShowUsers(): string
    {
        return 'SELECT DISTINCT user FROM mysql.user';
    }

    /**
     * Get the options for CREATE TABLE query
     *
     * @access protected
     * @return string
     */
    public function sqlCreateTableOptions(): string 
    {
        $engine =  !empty($settings['engine'])  ? $settings['engine']  : 'InnoDB';

The variable $settings seems to never exist and therefore empty should always be true.

        $charset = !empty($settings['charset']) ? $settings['charset'] : 'utf8';
        $collate = !empty($settings['collate']) ? $settings['collate'] : 'utf8_unicode_ci';
        return sprintf('ENGINE=%s DEFAULT CHARSET=%s COLLATE=%s;', $engine, $charset, $collate);
    } 
    
    /**
     * Get the SQL for random function 
     *
     * @access public
     * @param  int      $seed    The random seed. Default is null.
     *
     * @return string         
     */
    public function sqlRandom($seed = null): string
    {
        return sprintf('rand(%s)', !empty($seed) ? $seed : '');   
    }

    /**
     * Gets/returns the SQL for auto increment column.
     *
     * @access public
     * @param string   $type   The sql column type
     * 
     * @return string
     */
    public function sqlColumnAutoIncrement(string $type): string
    {
        return $type .' AUTO_INCREMENT';
    }
    
}