Просмотр файла app/classes/DBM.php

<?php
 
class DBM {
 
    /**
     * Automatically add/update created/updated fields
     *
     * @var boolean
     */
    public static $timestamp_writes = false;
 
    /**
     * Dynamic config creds
     *
     * @var Array - representing config details
     */
    protected $config;
 
    /**
     * The PDO objects for the connection
     *
     * @var PDO - the Pear Data Object
     */
    protected $pdo;
 
    /**
     * A reference to the singleton instance
     *
     * @var DBM
     */
    protected static $instance = null;
 
    /**
     * method instance.
     *    - static, for singleton, for creating a global instance of this object
     * @return DBM - DBM Object
     */
    public static function run() {
        if (! isset(self::$instance)) {
            self::$instance = new DBM();
        }
        return self::$instance;
    }
 
    /**
     * Constructor.
     * 	- make protected so only subclasses and self can create this object (singleton)
     */
    protected function __construct() {}
 
    /**
     * method config
     *    - configure connection credentials to the db server
     *
     * @param $host
     * @param $name
     * @param $user
     * @param $password
     * @param null $port
     * @param string $driver
     * @throws Exception
     * @internal param $host - the host name of the db to connect to
     * @internal param $name - the database name
     * @internal param $user - the user name
     * @internal param $password - the users password
     * @internal param $port (optional) - the port to connect using, default to 3306
     * @internal param $driver - the dsn prefix
     */
    public function config($host, $name, $user, $password, $port=null, $driver='mysql') {
        if (!$this->validateDriver($driver)) {
            throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
        }
 
        if (isset($this->pdo)) {
            error_log('DATABASE WRAPPER::warning, attempting to config after connection exists');
        }
 
        $this->config = [
            'driver' => $driver,
            'host' => $host,
            'name' => $name,
            'user' => $user,
            'password' => $password,
            'port' => $port
        ];
    }
 
    /**
     * method createConnection.
     *    - create a PDO connection using the credentials provided
     *
     * @param $driver
     * @param $host
     * @param $name
     * @param $user
     * @param $password
     * @param null $port
     * @return PDO object with a connection to the database specified
     * @throws Exception
     * @internal param $driver - the dsn prefix
     * @internal param $host - the host name of the db to connect to
     * @internal param $name - the database name
     * @internal param $user - the user name
     * @internal param $password - the users password
     * @internal param $port (optional) - the port to connect using, default to 3306
     */
    protected function createConnection($driver, $host, $name, $user, $password, $port=null) {
        if (!$this->validateDriver($driver)) {
            throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
        }
 
        // attempt to create pdo object and connect to the database
        try {
            //@TODO the following drivers are NOT supported yet: odbc, ibm, informix, 4D
            // build the connection string from static constants based on the selected PDO Driver.
            if ($driver == "sqlite" || $driver == "sqlite2") {
                $connection_string = $driver.':'.$host;
            } elseif ($driver == "sqlsrv") {
                $connection_string = "sqlsrv:Server=".$host.";Database=".$name;
            } elseif ($driver == "firebird" || $driver == "oci") {
                $connection_string = $driver.":dbname=".$name;
            } else {
                $connection_string = $driver.':host='.$host.';dbname='.$name;
            }
 
            // add the port if one was specified
            if (!empty($port)) {
                $connection_string .= ";port=$port";
            }
 
            // initialize the PDO object
            $new_connection = new PDO($connection_string, $user, $password);
 
            // set the error mode
            $new_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $new_connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $new_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
 
            $new_connection->exec('SET CHARACTER SET utf8');
            $new_connection->exec('SET NAMES utf8mb4');
 
            // return the new connection
            return $new_connection;
        }
 
        // handle any exceptions by catching them and returning false
        catch (PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method get.
     * 	- grab the PDO connection to the DB
     */
    protected function get() {
 
        // if we have not created the db connection yet, create it now
        if (!isset($this->pdo)) {
            $this->pdo = $this->createConnection(
                $this->config['driver'],
                $this->config['host'],
                $this->config['name'],
                $this->config['user'],
                $this->config['password'],
                $this->config['port']
            );
        }
 
        return $this->pdo;
    }
 
    /**
     * Получение количества элементов в таблице
     * @param  string $table имя таблицы
     * @param  array $params массив условий
     * @return int количество записей
     * @throws Exception
     */
    public function count($table, $params = null) {
        $sql_str = "SELECT count(*) FROM $table";
 
        $sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
 
        $add_and = false;
        // add each clause using parameter array
        if (empty($params)) {
            $params = [];
        }
        foreach ($params as $key=>$val) {
            // only add AND after the first clause item has been appended
            if ($add_and) {
                $sql_str .= ' AND ';
            } else {
                $add_and = true;
            }
 
            // append clause item
            if (is_array($val)) {
                $sql_str .= $key.current($val).":$key";
            } else {
                $sql_str .= "$key = :$key";
            }
        }
 
        // now we attempt to retrieve the row using the sql string
        try {
 
            $pstmt = $this->get()->prepare($sql_str);
 
            // bind each parameter in the array
            foreach ($params as $key=>$val) {
                if (is_array($val)) {
                    $val = end($val);
                }
                $pstmt->bindValue(':'.$key, $val);
            }
 
            $pstmt->execute();
 
            return $pstmt->fetchColumn(0);
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method select.
     *    - retrieve information from the database, as an array
     *
     * @param string $table - the name of the db table we are retreiving the rows from
     * @param array $params - associative array representing the WHERE clause filters
     * @param int $limit (optional) - the amount of rows to return
     * @param int $start (optional) - the row to start on, indexed by zero
     * @param array $order_by (optional) - an array with order by clause
     * @return mixed - associate representing the fetched table row, false on failure
     * @throws Exception
     */
    public function select($table, $params = null, $limit = null, $start = null, $order_by = null) {
        // building query string
        $sql_str = "SELECT * FROM $table";
        // append WHERE if necessary
        $sql_str .= ( count($params)>0 ? ' WHERE ' : '' );
 
        $add_and = false;
        // add each clause using parameter array
        if (empty($params)) {
            $params = [];
        }
        foreach ($params as $key=>$val) {
            // only add AND after the first clause item has been appended
            if ($add_and) {
                $sql_str .= ' AND ';
            } else {
                $add_and = true;
            }
 
            // append clause item
            if (is_array($val)) {
                $sql_str .= $key.current($val).":$key";
            } else {
                $sql_str .= "$key=:$key";
            }
        }
 
        // add the order by clause if we have one
        if (!empty($order_by)) {
            $sql_str .= ' ORDER BY';
            $add_comma = false;
            foreach ($order_by as $column => $order) {
                if ($add_comma) {
                    $sql_str .= ', ';
                }
                else {
                    $add_comma = true;
                }
                $sql_str .= " $column $order";
            }
        }
 
        // now we attempt to retrieve the row using the sql string
        try {
            // decide which database we are selecting from
            $pdoDriver = $this->get()->getAttribute(PDO::ATTR_DRIVER_NAME);
 
            //@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
            $disableLimit = ["sqlsrv", "mssql", "oci"];
 
            // add the limit clause if we have one
            if (!is_null($limit) && !in_array($pdoDriver, $disableLimit)) {
                $sql_str .= ' LIMIT '.(!is_null($start) ? "$start, ": '')."$limit";
            }
 
            $pstmt = $this->get()->prepare($sql_str);
 
            // bind each parameter in the array
            foreach ($params as $key=>$val) {
                if (is_array($val)) {
                    $val = end($val);
                }
                $pstmt->bindValue(':'.$key, $val);
            }
 
            $pstmt->execute();
 
            // now return the results, depending on if we want all or first row only
            if (! is_null($limit) && $limit == 1) {
                return $pstmt->fetch(PDO::FETCH_ASSOC);
            } else {
                return $pstmt->fetchAll(PDO::FETCH_ASSOC);
            }
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method selectFirst.
     *    - retrieve the first row returned from a select statement
     *
     * @param table - the name of the db table we are retreiving the rows from
     * @param array $params
     * @param array $order_by (optional) - an array with order by clause
     * @return mixed - associate representing the fetched table row, false on failure
     */
    public function selectFirst($table, $params = [], $order_by = null) {
        return $this->select($table, $params, 1, null, $order_by);
    }
 
    /**
     * method delete.
     *    - deletes rows from a table based on the parameters
     *
     * @param table - the name of the db table we are deleting the rows from
     * @param array $params
     * @return bool - associate representing the fetched table row, false on failure
     * @throws Exception
     */
    public function delete($table, $params = []) {
        // building query string
        $sql_str = "DELETE FROM $table";
        // append WHERE if necessary
        $sql_str .= count($params)>0 ? ' WHERE ' : '';
 
        $add_and = false;
        // add each clause using parameter array
        foreach ($params as $key=>$val) {
            // only add AND after the first clause item has been appended
            if ($add_and) {
                $sql_str .= ' AND ';
            } else {
                $add_and = true;
            }
 
            // append clause item
            if (is_array($val)) {
                $sql_str .= "$key".current($val).":$key";
            } else {
                $sql_str .= "$key=:$key";
            }
        }
 
        // now we attempt to retrieve the row using the sql string
        try {
            $pstmt = $this->get()->prepare($sql_str);
 
            // bind each parameter in the array
            foreach ($params as $key=>$val) {
                if (is_array($val)) {
                    $val = end($val);
                }
                $pstmt->bindValue(':'.$key, $val);
            }
 
            // execute the delete query
            $successful_delete = $pstmt->execute();
 
            // if we were successful, return the amount of rows updated, otherwise return false
            return ($successful_delete == true) ? $pstmt->rowCount() : false;
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method update.
     *    - updates a row to the specified table
     *
     * @param string $table - the name of the db table we are adding row to
     * @param array $params - associative array representing the columns and their respective values to update
     * @param array $wheres (Optional) - the where clause of the query
     * @param bool $timestamp_this (Optional) - if true we set created and modified values to now
     * @return bool|int - the amount of rows updated, false on failure
     * @throws Exception
     */
    public function update($table, $params, $wheres = [], $timestamp_this = false) {
        if (! $timestamp_this) {
            $timestamp_this = self::$timestamp_writes;
        }
        // build the set part of the update query by
        // adding each parameter into the set query string
        $add_comma = false;
        $set_string = '';
        foreach ($params as $key=>$val) {
            // only add comma after the first parameter has been appended
            if ($add_comma) {
                $set_string .= ', ';
            } else {
                $add_comma = true;
            }
 
            // now append the parameter
            if (is_array($val)) {
                $set_string .= "$key=$key".implode($val);
            } else {
                $set_string .= "$key=:param_$key";
            }
        }
 
        // add the timestamp columns if neccessary
        if ($timestamp_this) {
            $set_string .= ($add_comma ? ', ' : '') . 'modified='.time();
        }
 
        // lets add our where clause if we have one
        $where_string = '';
        if (!empty($wheres)) {
            // load each key value pair, and implode them with an AND
            $where_array = [];
            foreach($wheres as $key => $val) {
 
                // append clause item
                if (is_array($val)) {
                    $where_array[] = $key.current($val).":where_$key";
                } else {
                    $where_array[] = "$key=:where_$key";
                }
            }
            // build the final where string
            $where_string = 'WHERE '.implode(' AND ', $where_array);
        }
 
        // build final update string
        $sql_str = "UPDATE $table SET $set_string $where_string";
 
        // now we attempt to write this row into the database
        try {
            $pstmt = $this->get()->prepare($sql_str);
 
            // bind each parameter in the array
            foreach ($params as $key=>$val) {
                if (is_array($val)) continue;
                $pstmt->bindValue(':param_'.$key, $val);
            }
 
            // bind each where item in the array
            foreach ($wheres as $key=>$val) {
                if (is_array($val)) {
                    $val = end($val);
                }
                $pstmt->bindValue(':where_'.$key, $val);
            }
 
            // execute the update query
            $successful_update = $pstmt->execute();
 
            // if we were successful, return the amount of rows updated, otherwise return false
            return ($successful_update == true) ? $pstmt->rowCount() : false;
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method insert.
     *    - adds a row to the specified table
     *
     * @param string $table - the name of the db table we are adding row to
     * @param array $params - associative array representing the columns and their respective values
     * @param bool $timestamp_this (Optional), if true we set created and modified values to now
     * @return mixed - new primary key of inserted table, false on failure
     * @throws Exception
     */
    public function insert($table, $params = [], $timestamp_this = false) {
        if (! $timestamp_this) {
            $timestamp_this = self::$timestamp_writes;
        }
 
        // first we build the sql query string
        $columns_str = '(';
        $values_str = 'VALUES (';
        $add_comma = false;
 
        // add each parameter into the query string
        foreach ($params as $key=>$val) {
            // only add comma after the first parameter has been appended
            if ($add_comma) {
                $columns_str .= ', ';
                $values_str .= ', ';
            } else {
                $add_comma = true;
            }
 
            // now append the parameter
            $columns_str .= "$key";
            $values_str .= ":$key";
        }
 
        // add the timestamp columns if neccessary
        if ($timestamp_this) {
            $columns_str .= ($add_comma ? ', ' : '') . 'created, modified';
            $values_str .= ($add_comma ? ', ' : '') . time().', '.time();
        }
 
        // close the builder strings
        $columns_str .= ') ';
        $values_str .= ')';
 
        // build final insert string
        $sql_str = "INSERT INTO $table $columns_str $values_str";
 
        // now we attempt to write this row into the database
        try {
            $pstmt = $this->get()->prepare($sql_str);
 
            // bind each parameter in the array
            foreach ($params as $key=>$val) {
                $pstmt->bindValue(':'.$key, $val);
            }
 
            $pstmt->execute();
            $newID = $this->get()->lastInsertId();
 
            // return the new id
            return $newID;
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method insertMultiple.
     *    - adds multiple rows to a table with a single query
     *
     * @param string $table - the name of the db table we are adding row to
     * @param array $columns - contains the column names
     * @param array $rows
     * @param bool $timestamp_these (Optional), if true we set created and modified values to NOW() for each row
     * @return mixed - new primary key of inserted table, false on failure
     * @throws Exception
     */
    public function insertMultiple($table, $columns = [], $rows = [], $timestamp_these = false) {
        if (! $timestamp_these) {
            $timestamp_these = self::$timestamp_writes;
        }
 
        // generate the columns portion of the insert statment
        // adding the timestamp fields if needs be
        if ($timestamp_these) {
            $columns[] = 'created';
            $columns[] = 'modified';
        }
        $columns_str = '(' . implode(',', $columns) . ') ';
 
        // generate the values portions of the string
        $values_str = 'VALUES ';
        $add_comma = false;
 
        foreach ($rows as $row_index => $row_values) {
            // only add comma after the first row has been added
            if ($add_comma) {
                $values_str .= ', ';
            } else {
                $add_comma = true;
            }
 
            // here we will create the values string for a single row
            $values_str .= '(';
            $add_comma_forvalue = false;
            foreach ($row_values as $value_index => $value) {
                if ($add_comma_forvalue) {
                    $values_str .= ', ';
                } else {
                    $add_comma_forvalue = true;
                }
                // generate the bind variable name based on the row and column index
                $values_str .= ':'.$row_index.'_'.$value_index;
            }
            // append timestamps if necessary
            if ($timestamp_these) {
                $values_str .= ($add_comma_forvalue ? ', ' : '') . time().', '.time();
            }
            $values_str .= ')';
        }
 
        // build final insert string
        $sql_str = "INSERT INTO $table $columns_str $values_str";
 
        // now we attempt to write this multi inster query to the database using a transaction
        try {
            $this->get()->beginTransaction();
            $pstmt = $this->get()->prepare($sql_str);
 
            // traverse the 2d array of rows and values to bind all parameters
            foreach ($rows as $row_index => $row_values) {
                foreach ($row_values as $value_index => $value) {
                    $pstmt->bindValue(':'.$row_index.'_'.$value_index, $value);
                }
            }
 
            // now lets execute the statement, commit the transaction and return
            $pstmt->execute();
            $this->get()->commit();
            return true;
        }
        catch(PDOException $e) {
            $this->get()->rollBack();
            throw $e;
        }
        catch(Exception $e) {
            $this->get()->rollBack();
            throw $e;
        }
    }
 
    /**
     * method execute.
     *    - executes a query that modifies the database
     *
     * @param string $query - the SQL query we are executing
     * @param array $params
     * @return mixed - the affected rows, false on failure
     * @throws Exception
     */
    public function execute($query, $params = []) {
        try {
            // prepare the statement
            $pstmt = $this->get()->prepare($query);
 
            // bind each parameter in the array
            foreach ((array)$params as $key=>$val) {
                $pstmt->bindValue(':'.$key, $val, is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
 
            // execute the query
            $result = $pstmt->execute();
 
            // only if return value is false did this query fail
            return ($result == true) ? $pstmt->rowCount() : false;
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method query.
     *    - returns data from a free form select query
     *
     * @param string $query - the SQL query we are executing
     * @param array $params - a list of bind parameters
     * @return mixed - the affected rows, false on failure
     * @throws Exception
     */
    public function query($query, $params = []) {
        try {
 
            $pstmt = $this->get()->prepare($query);
 
            // bind each parameter in the array
            foreach ((array)$params as $key=>$val) {
                $pstmt->bindValue(':'.$key, $val, is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
            }
 
            // execute the query
            $pstmt->execute();
 
            // now return the results
            return $pstmt->fetchAll(PDO::FETCH_ASSOC);
        }
        catch(PDOException $e) {
            throw $e;
        }
        catch(Exception $e) {
            throw $e;
        }
    }
 
    /**
     * method queryFirst.
     * 	- returns the first record from a free form select query
     *
     * @param string $query - the SQL query we are executing
     * @param array $params - a list of bind parameters
     * @return mixed - the affected rows, false on failure
     */
    public function queryFirst($query, $params = []) {
        $result = $this->query($query, $params);
        if (empty($result)) {
            return false;
        }
        else {
            return $result[0];
        }
    }
 
    /**
     * Validate the database in question is supported by your installation of PHP.
     * @param string $driver The DSN prefix
     * @return boolean true, the database is supported; false, the database is not supported.
     */
    private function validateDriver($driver) {
        if (!in_array($driver, PDO::getAvailableDrivers())) {
            return false;
        } else {
            return true;
        }
    }
 
    /**
     * Destructor.
     * 	- release the PDO db connections
     */
    function __destruct() {
        unset($this->pdo);
    }
}