File: vendor/jackbooted/db/DB.php

Recommend this page to a friend!
  Classes of Brett Dutton  >  JackBooted PHP Framework  >  vendor/jackbooted/db/DB.php  >  Download  
File: vendor/jackbooted/db/DB.php
Role: Class source
Content type: text/plain
Description: Class source
Class: JackBooted PHP Framework
Web application framework using simplified MVC
Author: By
Last change:
Date: 4 years ago
Size: 21,827 bytes
 

Contents

Class file image Download
<?php
namespace Jackbooted\DB;

use \Jackbooted\Config\Cfg;
use \Jackbooted\Util\Log4PHP;
use \Jackbooted\Util\PHPExt;
/**
 * @copyright Confidential and copyright (c) 2016 Jackbooted Software. All rights reserved.
 *
 * Written by Brett Dutton of Jackbooted Software
 * brett at brettdutton dot com
 *
 * This software is written and distributed under the GNU General Public
 * License which means that its source code is freely-distributed and
 * available to the general public.
 */

/**
 * DB - Database abstraction for PDO.
 * The DB Strings are created from the configuration
 * @see addDB
*/
class DB extends \Jackbooted\Util\JB {
    /**
     * Default DB is Local
     */
    const DEF = 'local';

    /**
     * Wrap up the PDO Constants.
     */
    const FETCH_ASSOC = \PDO::FETCH_ASSOC;

    /**
     * Wrap up the PDO Constants.
     */
    const FETCH_NUM = \PDO::FETCH_NUM;

    /**
     * Wrap up the PDO Constants.
     */
    const FETCH_BOTH = \PDO::FETCH_BOTH;

    /**
     * Default SQL Table Engine type.
     */
    const SQL_ENGINE = 'InnoDB';

    /**
     * Default SQL Table Charset.
     */
    const SQL_CHARSET = 'utf8';

    const SQLITE    = 'sqlite';
    const MYSQL     = 'mysql';
    const SQLSERVER = 'dblib';
    const ORACLE    = 'oracle';

    // Keep a cache of the connections
    private static $connections = [];

    // Thelast accessed database
    private static $lastDB = null;

    // Keep a log of the number of calls
    private static $callNumber = 0;

    // Logging
    private static $log;
    private static $queryLoggingFunction;
    private static $queryLoggingLevel;
    private static $queryLogFlag;
    private static $errorLoggingFunction;
    private static $errorLoggingLevel;

    /**
     * Controls whether to use direct queries or prepared statements
     */
    private static $directQuery = true;

    /**
     * Set up the statics, sets up the logging level in one place.
     *
     * If you want to change this throughout this class, you can adjust here.
     * @since 1.0
     * @return void
     */
    public static function init() {
        self::$log = Log4PHP::logFactory ( __CLASS__ );
        self::$log->setClassErrorLevel( Log4PHP::INFO );

        // Sets up the logging level in one place
        // If you want to change this throughout this class, you can adjust here
        self::$queryLoggingFunction =  [ self::$log, 'info' ];
        self::$queryLoggingLevel = Log4PHP::INFO;
        self::$queryLogFlag = self::$log->isDisplayed ( self::$queryLoggingLevel );

        self::$errorLoggingFunction =  [ self::$log, 'error' ];
        self::$errorLoggingLevel = Log4PHP::ERROR;
    }

    /**
     * Returns the log object so that you can selectively turn on log flags.
     *
     * Eg:
     * <pre>
     *  $dbLogger = DB::getLogger ();
     *  $dbLogger->setClassOutputDevice ( Log4PHP::SCREEN );
     *  $dbLogger->setClassErrorLevel ( Log4PHP::ALL );
     *  DB::init();
     * </pre>
     * @since 1.0
     * @return Log4PHP - returns the log object.
     */
    public static function getLogger () {
        return self::$log;
    }

    /**
     * Allows a database to be created and saved in the $config.
     *
     * This allows compatibility with fab_query
     *
     * @param string $name     Name of the database connection eg: 'reg'.
     * @param string $host     Host name of the database server.
     * @param string $user     Username to access the server.
     * @param string $password Password for this account.
     * @param string $database Database name.
     * @param string $driver   Database driver - Supported drivers are 'mysql' or 'pgsql'.
     *
     * @since 1.0
     * @return void
     */
    public static function addDB($name, $host, $user, $password, $database, $driver = 'mysql') {
        Cfg::set($name.'-host', $host);
        Cfg::set($name.'-db', $database);
        Cfg::set($name.'-user', $user);
        Cfg::set($name.'-pass', $password);
        Cfg::set($name.'-driver', $driver);
    }

    /**
     * This is a standard normalizing function that can be called throughout the system.
     *
     * And will always return the PDO object that is necessary for the calls.
     * <ul>
     * <li>If the $db variable is a string it is assumed to be the index of the db connection
     * that is needed. If the connection exists already it is returned, otherwise it sets
     * up the connection using the $config variables</li>
     * <li>If $db is an object then it is assumed to be a PDO object and passed straight thru</li>
     * <li>If $db is an array then it must contain the items array ( 'hostname' => $host,
     * 'username' => $user, 'password' => $password, 'dbname'  => $database, 'driver' => 'mysql' ) If they don't exist
     * in the $config, then they are set up</li>
     * </ul>
     * @param mixed $db - This is either null, and array of the the database connection
     *                    information or the connection handle itself.
     * @since 1.0
     * @return PDO Object connection to the database.
     */
    private static function connectionFactory ( $db=null, $key=null ) {

        if ( is_string ( $db ) ) {
            // If this is a string then a key has been passed.
            // The key may have been set up as PDO object or
            // it might be a key from legacy config
            return self::connectionFactoryFromString ( $db );
        }
        else if ( is_object ( $db ) ) {
            // If this is an objecct then it is likely a PDO object
            self::$lastDB = $db;
            return self::$lastDB;
        }
        else if ( is_array ( $db ) ) {
            // If this is an array then it might be a database information
            return self::connectionFactoryFromArray ( $db, $key );
        }
        else {
            return self::$lastDB;
        }
    }

    private static function connectionFactoryFromString ( $db ) {
        if ( isset ( self::$connections[$db] ) ) {
            self::$lastDB = self::$connections[$db];
            return self::$lastDB;
        }
        else {
            $dbConnection =  [ 'hostname' => Cfg::get ( $db . '-host' ),
                               'dbname'   => Cfg::get ( $db . '-db' ),
                               'username' => Cfg::get ( $db . '-user' ),
                               'password' => Cfg::get ( $db . '-pass' ),
                               'driver'   => Cfg::get ( $db . '-driver', DB::MYSQL ) ];

            if ( $dbConnection['hostname'] != '' ) {
                return self::connectionFactoryFromArray ( $dbConnection, $db );
            }
            else {
                self::logErrorMessage ( 'Unknown DB: ' . $db );
                return false;
            }
        }
    }

    private static function connectionFactoryFromArray ( $db, $key=null ) {
        if ( ! isset ( $db['driver'] ) ) {
            $db['driver'] = self::MYSQL;
        }

        if ( preg_match ( '/^java:comp\/env\/jdbc\/.*$/', $db['dbname'] ) ) {
            $connectionString = $db['dbname'];
        }
        else if ( $db['driver'] == self::SQLITE ) {
            $connectionString = $db['driver'] . ':' . $db['hostname'];
        }
        else {
            $connectionString = $db['driver'] . ':host=' . $db['hostname'] . ';dbname=' . $db['dbname'];
        }

        if ( $key == null ) {
            $key = hash ( 'md4', $connectionString . $db['username'] . $db['password'] );
        }

        if ( ! isset ( self::$connections[$key] ) ) {
            self::dbg ( 'Setting up new DB conn: ' . $connectionString . ' - ' . $db['username'] );
            try {
                if ( $db['driver'] == self::SQLITE ) {
                    self::$connections[$key] = new \PDO ( $connectionString );
                }
                else {
                    self::$connections[$key] = new \PDO ( $connectionString, $db['username'], $db['password'] );
                }
            }
            catch ( Exception $ex ) {
                self::logErrorMessage ( 'Error Setting up new DB conn: ' . $connectionString . ' - ' .
                                        $db['username'] . ' - ' . $ex->getMessage() );
                return false;
            }
        }

        self::$lastDB = self::$connections[$key];
        return self::$lastDB;
    }

    /**
     * Converts the passed array to the necessary for prepared statement.
     *
     * If the array is associative then
     * it creates a :x,:y style string. If the array is numeric then creates a string with ? eg:
     * <pre>
     * $arr = array ( 'domain' => 0, 'length' => 1, 'tld' => 5, 'pricescore' => 2, 'intprice' => 4 );
     * $params = array ( 'foo' => 'dummy1', 'bar' => 'dummy2' );
     * echo 'INSERT INTO domain_info VALUES(' . DB::in ( $arr, $params ) . ')<br>';
     * echo '<pre>'; print_r ( $params ); echo '</pre>';
     *
     * $arr = array_values ( $arr );
     * $params = array ( 'dummy1', 'dummy2' );
     * echo 'INSERT INTO domain_info VALUES(' . DB::in ( $arr, $params ) . ')<br>';
     * echo '<pre>'; print_r ( $params ); echo '</pre>';
     * </pre>
     * @param mixed $values  Values that will be in the IN.
     * @param mixed &$params Adds to the parameters.
     *
     * @since 1.0
     * @return string
     */
    public static function in ( $values, &$params=null ) {
        if ( ! is_array ( $params ) ) {
            $params =  [];
        }

        if ( PHPExt::is_assoc ( $values ) ) {
            foreach ( $values as $key => $val ) {
                $params[$key] = $val;
            }

            return ':' . join ( ',:', array_keys ( $values ) );
        }
        else {
            if ( ! is_array ( $values ) ) {
                $values =  [ $values ];
            }

            foreach ( $values as $val ) $params[] = $val;

            return join ( ',', array_fill( 0, count ( $values ), '?' ) );
        }
    }

    /**
     * Sets the buffering mode for mysql.
     *
     * @param mixed   $dbh  Database handle.
     * @param boolean $flag Set buffered on or off.
     *
     * @since 1.0
     * @return boolean The old value.
     */
    public static function setBuffered ( $dbh, $flag=true ) {
        if ( ( $dbh = self::connectionFactory ( $dbh ) ) === false ) {
            return false;
        }

        $oldAttribute = self::$directQuery;
        if ( $oldAttribute != $flag ) {
            self::$directQuery = $flag;
        }

        return $oldAttribute;
    }

    /**
     * Sets the prepared emulation mode for mysql.
     *
     * @param mixed   $dbh  Database handle.
     * @param boolean $flag Set emulated prepared statements on or off.
     *
     * @since 1.0
     * @return boolean The old value.
     */
    public static function setPreparedMode ( $dbh, $flag=true ) {
        if ( ( $dbh = self::connectionFactory ( $dbh ) ) === false ) {
            return false;
        }

        $oldAttribute = self::$directQuery;
        self::$directQuery = $flag;

        return $oldAttribute;
    }

    /**
     * Returns a single value.
     *
     * Useful for count(*) sql calls.
     *
     * @param mixed  $dbh    Database handle.
     * @param string $qry    Query String.
     * @param mixed  $params Paraneters (array) or single parameter.
     *
     * @since 1.0
     * @return mixed
     */
    public static function oneValue ( $dbh, $qry, $params=null ) {
        $row = self::oneRow ( $dbh, $qry, $params, self::FETCH_NUM );
        if ( $row === false || ! is_array ( $row ) || $row[0] === null ) {
            return false;
        }
        else {
            return $row[0];
        }
    }

    /**
     * Returns a single row.
     *
     * @param mixed   $dbh       Database handle.
     * @param string  $qry       Query String.
     * @param mixed   $params    Paraneters (array) or single parameter.
     * @param integer $fetchType Fetch Type.
     *
     * @since 1.0
     * @return array
     */
    public static function oneRow ( $dbh, $qry, $params=null, $fetchType=self::FETCH_ASSOC ) {
        $result = self::query ( $dbh, $qry, $params );
        return ( $result === false ) ? false : $result->fetch ( $fetchType );
    }

    /**
     * Returns a single column.
     *
     * @param mixed   $dbh       Database handle.
     * @param string  $qry       Query String.
     * @param mixed   $params    Paraneters (array) or single parameter.
     * @param integer $fetchType Fetch Type.
     *
     * @since 1.0
     * @return array
     */
    public static function oneColumn ( $dbh, $qry, $params=null ) {
        $result = self::query ( $dbh, $qry, $params );
        if ( $result === false ) return false;

        $col = [];
        while ( ( $row = $result->fetch ( self::FETCH_NUM ) ) !== false ) {
            $col[] = $row[0];
        }

        if ( count( $col ) == 0 ) return false;

        return $col;
    }

    /**
     * Sets a database handle to unbuffered mode then makes a call then resets it back to buffered mode.
     *
     * This is good for one off calls, but if you are doing many updated you should call @see setBuffered
     * at the beginning of the loop and the again at the end.
     *
     * @param mixed  $dbh    Database handle.
     * @param string $qry    Query String.
     * @param mixed  $params Paraneters (array) or single parameter.
     *
     * @since 1.0
     * @return object Database resource
     */
    public static function unbuffered ( $dbh, $qry, $params=null ) {
        $oldAttribute = self::setBuffered ( $dbh, false );
        self::exec ( $dbh, $qry, $params );
        self::setBuffered ( $dbh, $oldAttribute );
        return $dbh;
    }

    /**
     * Executes a query and returns a PDOStatement that you can iterate over.
     *
     * @param mixed   $dbh    Database handle.
     * @param string  $qry    Query String.
     * @param mixed   $params Paraneters (array) or single parameter.
     * @param boolean $log    Force not log this call.
     *
     * @since 1.0
     * @return object Result set of the query
     */
    public static function query ( $dbh, $qry, $params=null, $log=false ) {
        $qry = self::doReplacements( $qry );

        if ( self::$queryLogFlag || $log ) {
            self::dbg ( $qry, $params );
        }

        if ( ( $dbResource = self::connectionFactory ( $dbh ) ) === false ) {
            return false;
        }

        try {
            if ( $params == null ) {
                // turn on emulating prepared statements because mysql gets confused on some statements
                if ( ! Cfg::get ( 'quercus', false ) &&
                     ! in_array ( self::driver( $dbh ), [ self::SQLSERVER, self::SQLITE ] ) ) {
                    $dbResource->setAttribute( \PDO::ATTR_EMULATE_PREPARES, true );
                }
                $result = $dbResource->query ( $qry );
                if ( ! Cfg::get ( 'quercus', false ) &&
                     ! in_array ( self::driver( $dbh ), [ self::SQLSERVER, self::SQLITE ] ) ) {
                    $dbResource->setAttribute( \PDO::ATTR_EMULATE_PREPARES, false );
                }
                return ( $result === FALSE ) ? self::logError ( $qry, $dbResource ) : $result;
            }
            else {
                $prepareParams = [];
                if ( self::$directQuery === true &&
                     ! Cfg::get ( 'quercus', false ) &&
                     ! in_array ( self::driver( $dbh ), [ self::SQLSERVER, self::SQLITE ] ) ) {
                    $prepareParams[\PDO::MYSQL_ATTR_DIRECT_QUERY] = true;
                }
                if ( ( $dbResource = $dbResource->prepare ( $qry, $prepareParams ) ) === false ) {
                    return self::logErrorMessage ( 'Problem SQL: ' . $qry );
                }

                if ( ! is_array ( $params ) ) {
                    $params =  [ $params ];
                }

                $result = $dbResource->execute ( $params );

                return ( $result === FALSE ) ? self::logError ( $qry, $dbResource ) : $dbResource;
            }
        }
        catch ( Exception $ex ) {
            return self::logError ( 'E: ' . $ex->getMessage() . ': ' . $qry, $dbResource );
        }
    }

    /**
     * Executes an update, insert of delete.
     *
     * Returns the number of rows affected
     *
     * @param mixed   $dbh    Database handle.
     * @param string  $qry    Query String.
     * @param mixed   $params Paraneters (array) or single parameter.
     * @param boolean $log    Force not log this call.
     *
     * @since 1.0
     * @return integer
     */
    public static function exec ( $dbh, $qry, $params=null, $log=false ) {
        $qry = self::doReplacements($qry);
        if ( self::$queryLogFlag || $log ) {
            self::dbg ( $qry, $params );
        }

        if ( ( $dbResource = self::connectionFactory ( $dbh ) ) === false ) return false;
        try {
            if ( $params == null ) {
                $result = $dbResource->exec ( $qry );
            }
            else {
                if ( ( $newResource = $dbResource->prepare ( $qry ) ) === false ) {
                    return self::logError ( $qry, $dbResource );
                }
                else {
                    $dbResource = $newResource;
                }

                if ( ! is_array ( $params ) ) {
                    $params =  [ $params ];
                }

                $result = $dbResource->execute ( $params );
            }

            if ( Cfg::get ( 'quercus', false ) ) {
                if ( $result < 0 ) {
                    return self::logError ( $qry, $dbResource );
                }
                else {
                    return (int)$result;
                }
            }
            else {
                if ( $result === false ) {
                    return self::logError ( $qry, $dbResource );
                }
                else if ( is_int ( $result ) ) {
                    return $result;
                }
                else {
                    return $dbResource->rowCount();
                }
            }
        }
        catch ( Exception $ex ) {
            return self::logError ( 'E: ' . $ex->getMessage() . ': ' . $qry, $dbResource );
        }
    }

    /**
     * Generates the Limit sql on the passed in query
     * @param string $sql
     * @param int $start
     * @param int $cnt
     * @return string the sql with the limit added
     */
    static function limit ( $sql, $start, $cnt ) {

        // Check if we are already doing the limiting
        if ( strpos ( strtoupper ( $sql ), 'LIMIT' ) !== FALSE ) return ( $sql );

        return ( $sql . " LIMIT $start,$cnt" );
    }

    /**
     * Replaces any special strings in the query with the appropriate values.
     *
     * @param string $query
     *
     * @since 1.0
     * @return string
     **/
    private static function doReplacements( $query ) {
        return strtr ( $query,  [ '%%PRE%%'        => Cfg::get( 'prefix', 'w_' ),
                                  '%%SQLENGINE%%'  => Cfg::get( 'sql_tabletype', self::SQL_ENGINE ),
                                  '%%SQLCHARSET%%' => Cfg::get( 'sql_charset', self::SQL_CHARSET ) ] );
    }

    private static function logError( $qry, $resource ) {
        self::logErrorMessage ( join ( ':', $resource->errorInfo () ) . ':' . $qry );
        //echo ( join ( ':', $resource->errorInfo () ) . ':' . $qry );
        return false;
    }

    private static function logErrorMessage( $message ) {
        //echo $message . self::calculateCallLocation();
        self::$log->error ( $message, self::calculateCallLocation() );
        return false;
    }

    private static function dbg ( $qry, &$params=null ) {
        $msg = self::$callNumber . ':"' . $qry . '"';
        self::$callNumber ++;
        if ( $params != null ) {
            $msg .= ( is_array ( $params ) ) ? join ( ':', $params ) : $params;
        }
        self::$log->debug ( $msg, self::calculateCallLocation() );
    }

    private static function calculateCallLocation ( ) {
        $stack = debug_backtrace ();
        $stackLength = count ( $stack );
        for ( $origin = 1; $origin<$stackLength; $origin++ ) {
            if ( __FILE__ != $stack[$origin]['file'] ) break;
        }

        $fileLocation = basename ( $stack[$origin]['file'] );
        $lineNumber = '(L:' . $stack[$origin]['line'] . ')';
        $origin ++;
        $calledFrom = ( ( isset ( $stack[$origin]['class'] ) ) ? $stack[$origin]['class'] : '' ) .
                      ( ( isset ( $stack[$origin]['type'] ) ) ? $stack[$origin]['type'] : '' ) .
                      ( ( isset ( $stack[$origin]['function'] ) ) ? $stack[$origin]['function'] : '' );
        if ( $calledFrom == '' ) {
            $calledFrom = $fileLocation;
        }

        return $lineNumber . $calledFrom;
    }

    /**
     * Returns last_insert_id().
     *
     * @param mixed $dbh Database resource.
     *
     * @since 1.0
     * @return integer Last inset id
     */
    public static function lastInsertId ( $dbh ) {
        if ( ( $dbResource = self::connectionFactory ( $dbh ) ) === false ) {
            return false;
        }

        return $dbResource->lastInsertId();
    }

    /**
     * Resets the connections etc back to a fresh state.
     *
     * @since 1.0
     * @return void
     */
    public static function reset() {
        if ( isset( self::$connections ) AND is_array( self::$connections ) AND count( self::$connections ) > 0) {
            foreach ( self::$connections as $db => $connection ) {
                unset( self::$connections[$db] );
            }
        }
        else {
            self::$connections = [];
        }
    }

    public static function driver ( $dbh=self::DEF ) {
        return Cfg::get ( $dbh . '-driver' );
    }
}

For more information send a message to info at phpclasses dot org.