twistofreality / dilmun

src/Enki/MySql.php

<?php

namespace Subreality\Dilmun\Enki;

use Subreality\Dilmun\LoggedClassTrait;

/**
 * Class MySql
 * @package Subreality\Dilmun\Enki
 */
class MySql implements EnkiInterface
{
    use LoggedClassTrait;

    /**
     * @var \PDO
     */
    private $pdo;

    /**
     * Provides a MySQL connection interface that catches a PDOException on failure.
     *
     * @param string $host  The host name for the MySQL connection
     * @param string $user  The username for the MySQL connection
     * @param string $pass  The password for the MySQL connection
     * @param string $name  The database name for the MySQL connection
     *
     * @return bool         Returns true if the connection was successful; returns false otherwise
     */
    public function connect($host, $user, $pass, $name)
    {
        $dsn = "mysql:host={$host};dbname={$name}";

        try {
            $this->pdo = new \PDO($dsn, $user, $pass);
            $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

            $connected = true;
        } catch (\PDOException $pdo_e) {
            $context = array();

            $context["exception"] = $pdo_e;

            $this->updateLog("error", "MySQL connection failed with the following message: {message}", $context);
            
            $connected = false;
        }

        return $connected;
    }

    /**
     * Destroys an existing PDO connection
     *
     * @return bool Returns true if the connection existed and was destroyed; returns false otherwise
     */
    public function disconnect()
    {
        if ($this->pdo instanceof \PDO) {
            $this->pdo = null;

            $disconnected = true;
        } else {
            $disconnected = false;
        }

        return $disconnected;
    }

    /**
     * Gets the column names for all primary keys for a table or returns false if no keys exist
     *
     * @param string $table The table from which keys will be retrieved
     *
     * @return string|false Returns a string of column names representing primary keys;
     *                      returns false if the table has no primary keys
     */
    public function getPrimaryKeys($table)
    {
        $keys_query = "SHOW KEYS FROM {$table} WHERE Key_name='PRIMARY'";

        $statement = $this->pdo->prepare($keys_query);

        $result = $this->statementWrapper($statement, "Trying to get keys failed with the following message: ");

        if ($result) {
            $keys = $this->processPrimaryKeyResult($result);
        } else {
            $keys = false;
        }

        return $keys;
    }

    /**
     * Retrieves a row represented by a single primary key
     *
     * @param int|string $id    A unique id for a table
     * @param string $table     The table from which a row will be retrieved
     *
     * @return array|false      Returns an associative array representing the row corresponding with the primary key;
     *                          returns false if the table does not exist, if the provided id does not correspond with a
     *                          record, or if the table has a compound primary key
     */
    public function getRowById($id, $table)
    {
        $id  = $this->quoteQueryStringVariable($id);
        $key = $this->getPrimaryKeys($table);

        $query = "SELECT * FROM {$table}
                    WHERE {$key} = {$id}";

        $statement = $this->pdo->prepare($query);

        $result = $this->statementWrapper($statement);

        if (empty($result)) {
            $row = false;
        } else {
            $row = $result[0];
        }

        return $row;
    }

    /**
     * Determines whether a given table exists
     *
     * @param string $table The table that will be checked for existence
     * @return bool         Returns true if the table exists; returns false otherwise
     */
    public function tableExists($table)
    {
        $table_query = "SHOW TABLES LIKE '{$table}'";

        $statement = $this->pdo->prepare($table_query);

        $statement->execute();

        $row_count = $statement->rowCount();

        if ($row_count == 0) {

The if-else statement can be simplified to return !($row_count == 0);.

            return false;
        } else {
            return true;
        }
    }

    /**
     * Processes the result set from a primary key query
     *
     * @see MySql::getPrimaryKeys()
     *
     * @param array $result An array representing the result of a primary key query
     *
     * @return string|false Returns a string column name if the result set contains only one primary key;
     *                      returns false otherwise
     */
    private function processPrimaryKeyResult($result)
    {
        $row_count = count($result);
        $this->updateLog("debug", "{$row_count} keys found");

        if ($row_count == 1) {
            $this->updateLog("debug", "Single key found; setting up key string");

            $keys = $result[0]['Column_name'];
        } else {
            $this->updateLog("debug", "No key or multiple keys found; returning false");

            $keys = false;
        }

        return $keys;
    }

    /**
     * Prepares a variable for a query string by surrounding it with single quotes if it's a string
     *
     * @param mixed $var    The variable to be assessed for quoting
     *
     * @return mixed        Returns the single-quoted variable if the variable is a string; returns the variable
     *                      otherwise
     */
    private function quoteQueryStringVariable($var)
    {
        if (is_string($var)) {
            $query_var = "'{$var}'";
        } else {
            $query_var = $var;
        }

        return $query_var;
    }

    /**
     * A wrapper for executing PDOStatement objects that either returns the statement execution result or
     * catches any PDOException thrown and updates the class logger with the exception message.
     *
     * @param \PDOStatement $statement  The statement to be executed
     * @param string|null $log_message  OPTIONAL: The message to be logged, in addition to the PDOException message
     *
     * @return array|false              Returns an associative array of the statement execution result of execution
     *                                  was successful; returns false otherwise
     */
    private function statementWrapper(\PDOStatement $statement, $log_message = null)
    {
        if ($log_message === null) {
            $log_message = "Statement execution error: ";
        }
        
        try {
            $statement->execute();
        } catch (\PDOException $pdo_e) {
            $context = array();

            $context["exception"] = $pdo_e;

            $this->updateLog("error", "{$log_message}: {message}", $context);

            return false;
        }

        $result = $statement->fetchAll(\PDO::FETCH_ASSOC);
        
        return $result;
    }
}