objSQL.php

Go to the documentation of this file.

<?php

/**
 * Database access controller class
 *
 * @package objSQL
 * @version 2.1.0
 * @author MT Jordan <mtjo62@gmail.com>
 * @link http://objsql.sourceforge.net
 * @copyright 2004-2010 openSource Partners
 * @license LGPL
 * @revision $Id: objSQL.php,v 1.20 2010-07-11 13:22:21-05 mt_jordan Exp $
 */

class objSQL
{
    /**********************************************
     * Internal variables
     *********************************************/

    /**
     * Transaction flag - false or resource(Firebird/Interbase) if transaction is initiated
     *
     * @access private
     * @var    mixed
     */
    private $objAutoCommit = true;

    /**
     * Database connection object
     *
     * @access private
     * @var    mixed
     */
    private $objConnection = false;

    /**
     * Database connection class instance
     *
     * @access private
     * @var    mixed
     */
     private $objConnectClass;

    /**
     * Database type array contains all supported databases
     * Internal elements are used in checking extension availability and as method prefixes
     *
     * @access private
     * @var    array
     */
    private $objDbTypeArray = array( 'db2'      => array( 'db2','ibm_db2' ),
                                     'firebird' => array( 'ibase','interbase' ),
                                     'ingres'   => array( 'ingres','ingres' ),
                                     'maxdb'    => array( 'maxdb','maxdb' ),
                                     'mssql'    => array( 'mssql','mssql' ),
                                     'mysql'    => array( 'mysqli','mysqli' ),
                                     'oracle'   => array( 'oci','oci8' ),
                                     'pgsql'    => array( 'pg','pgsql' ),
                                     'sqlite'   => array( 'sqlite','sqlite' ),
                                     'sqlite3'  => array( 'sqlite3','sqlite3' ),
                                     'sybase'   => array( 'sybase','sybase_ct' ) );

    /**
     * Database type as set in datasource argument. See $objDbTypeArray for valid database types.
     *
     * @access private
     * @var    str
     */
    private $objDbType;

    /**
     * Array of database types that support sequences/generators
     *
     * @access private
     * @var    array
     */
    private $objSeqArray = array( 'db2',
                                  'firebird',
                                  'ingres',
                                  'maxdb',
                                  'oracle',
                                  'pgsql' );

    /**********************************************
     * Class methods
     *********************************************/

    /**
     * Constructor
     *
     * @access public
     * @param  array $datasource
     */
    public function __construct( $datasource )
    {
        if ( !is_array( $datasource ) )
            trigger_error( 'Datasource argument must be an array', E_USER_WARNING );
        else
            $this->datasource( $datasource );
    }

    /**
     * Close database connection - destroy connection object
     *
     * @access public
     * @return bool
     */
    public function close()
    {
        $objDbClose = $this->objConnectClass->objDbClose();
        $this->objConnection = false;

        return $objDbClose;
    }

    /**
     * Return database connection object
     *
     * @access public
     * @return mixed
     */
    public function connection()
    {
        return $this->objConnection;
    }

    /**
     * Create sequence/generator object
     *
     * @access public
     * @param  str $sequence
     * @param  int $max
     * @return mixed
     */
    public function create_sequence( $sequence, $max=false )
    {
        if ( in_array( $this->objDbType, $this->objSeqArray ) )
        {
            require_once 'drivers/' . $this->objDbType . '/' . $this->objDbType . 'Sequence.php';

            $objSequence = $this->objDbType . 'Sequence';
            $seq = new $objSequence( $sequence, $max, $this->objConnection );

            return $seq->setSequence();
        }
    }

    /**
     * Set database connection object, database type and load statement driver
     *
     * @access protected
     * @param  array $datasource
     * @return bool
     */
    protected function datasource( $datasource )
    {
        $this->objDbType = $this->objDetect( strtolower( $datasource[0] ) );

        if ( $this->objDbType )
        {
            require_once 'drivers/' . $this->objDbType . '/' . $this->objDbType . 'Connection.php';

            $objConnection = $this->objDbType . 'Connection';
            $this->objConnectClass = new $objConnection( $datasource );
            $this->objConnection = $this->objConnectClass->objDbConnection();

            if ( $this->objConnection )
                require_once 'drivers/' . $this->objDbType . '/' . $this->objDbType . 'Statement.php';
        }
    }

    /**
     * Return database type
     *
     * @access public
     * @return mixed
     */
    public function dbtype()
    {
        return $this->objDbType;
    }

    /**
     * Return delete query object
     *
     * @access public
     * @param  str   $table
     * @param  str   $where
     * @param  mixed $savepoint
     * @return mixed
     */
    public function delete( $table, $where=false, $savepoint=false )
    {
        $objWhere = ( !$where ) ? '' : ' WHERE ' . $where;
        $objQuery = 'DELETE FROM ' . $table . $objWhere;
        $objStatement = $this->objDbType . 'Statement';

        $stmt = new $objStatement( $objQuery, $this, $this->objAutoCommit, $savepoint );

        return $stmt->objExecuteNonPrepared();
    }

    /**
     * Return insert query result object
     *
     * @access public
     * @param  str   $table
     * @param  array $array
     * @param  mixed $savepoint
     * @return mixed
     */
    public function insert( $table, $array, $savepoint=false )
    {
        //$array MUST be a key value pair array
        $array_keys   = array_keys( $array );
        $array_values = array_values( $array );
        $objColumns   = '';
        $objValues    = '';

        for ( $i = 0; $i < count( $array ); $i++ )
        {
            if ( is_string( $array_values[$i] ) && !substr_count( strtolower( $array_values[$i] ), 'nextval' ) )
                $objValues .= "'" . $array_values[$i] . "',";
            else
                $objValues .= $array_values[$i] . ',';

            $objColumns .= $array_keys[$i] . ',';
        }

        $objValues  = ' VALUES ( ' . rtrim( $objValues, ',' ) . ')';
        $objColumns = ' (' . rtrim( $objColumns, ',' ) . ')';
        $objQuery   = 'INSERT INTO ' . $table . $objColumns . $objValues;
        $objStatement = $this->objDbType . 'Statement';

        $stmt = new $objStatement( $objQuery, $this, $this->objAutoCommit, $savepoint );

        return $stmt->objExecuteNonPrepared();
    }

    /**
     * Return valid database type and verify enabled extension/module
     *
     * @access private
     * @return mixed
     */
    private function objDetect( $dbtype )
    {
        $objDbType = ( $dbtype == 'interbase' ) ? 'firebird' : $dbtype;

        if ( array_key_exists( $objDbType, $this->objDbTypeArray ) )
        {
            if ( !extension_loaded( $this->objDbTypeArray[$objDbType][1] ) )
            {
                trigger_error( $this->objDbTypeArray[$objDbType][1] . ' extension not enabled', E_USER_WARNING );
                return false;
            }
            else
            {
                return $objDbType;
            }
        }

        trigger_error( 'Unsupported database type: ' . $dbtype, E_USER_WARNING );
        return false;
    }

    /**
     * Execute non-prepared query and return resultset object
     *
     * @access public
     * @param  str   $query
     * @param  mixed $savepoint
     * @return mixed
     */
    public function query( $query, $savepoint=false )
    {
        $objStatement = $this->objDbType . 'Statement';
        $stmt = new $objStatement( $query, $this, $this->objAutoCommit, $savepoint );

        return $stmt->objExecuteNonPrepared();
    }

    /**
     * Return prepared statement
     *
     * @access public
     * @param  str   $query
     * @param  mixed $savepoint
     * @return mixed
     */
    public function prepare( $query, $savepoint=false )
    {
        $objStatement = $this->objDbType . 'Statement';

        return new $objStatement( $query, $this, $this->objAutoCommit, $savepoint );
    }

    /**
     * Return select query result object
     *
     * @access public
     * @param  str $table
     * @param  str $columns
     * @param  str $where
     * @param  str $order_by
     * @param  str $sort_order
     * @return mixed
     */
    public function select( $table, $columns=false, $where=false, $order_by=false, $sort_order=false )
    {
        $objColumns   = ( !$columns ) ? '*' : $columns;
        $objWhere     = ( !$where ) ? '' : ' WHERE ' . $where;
        $objOrderBy   = ( !$order_by ) ? '' : ' ORDER BY ' . $order_by;
        $objSortOrder = ( !$sort_order || strtolower( $sort_order ) != 'desc' || strtolower( $sort_order ) != 'asc' ) ? '' : ' ' . $sort_order;

        $objQuery = 'SELECT ' . $objColumns . ' FROM ' . $table . $objWhere . $objOrderBy . $objSortOrder;
        $objStatement = $this->objDbType . 'Statement';

        $stmt = new $objStatement( $objQuery, $this, false, false );

        return $stmt->objExecuteNonPrepared();
    }

    /**
     * Return sequence value/property for named sequence - use with non-prepared insert queries
     *
     * @access public
     * @param  str $sequence
     * @return mixed
     */
    public function sequence( $sequence )
    {
        if ( in_array( $this->objDbType, $this->objSeqArray ) )
        {
            require_once 'drivers/' . $this->objDbType . '/' . $this->objDbType . 'Sequence.php';

            $objSequence = $this->objDbType . 'Sequence';
            $seq = new $objSequence( $sequence, false, $this->objConnection );

            return $seq->getSequence();
        }
    }

    /**
     * Return database server version
     *
     * @access public
     * @return str
     */
    public function server_version()
    {
        return $this->objConnectClass->objServerVersion();
    }

    /**
     * Begin transaction
     *
     * @access public
     * @return mixed
     */
    public function transaction()
    {
        require_once 'drivers/' . $this->objDbType . '/' . $this->objDbType . 'Transaction.php';

        $this->objAutoCommit = ( $this->objDbType == 'firebird' ) ? ibase_trans( $this->objConnection ) : false;
        $objTransaction = $this->objDbType . 'Transaction';

        return new $objTransaction( $this->objConnection, $this->objAutoCommit );
    }

    /**
     * Return update query result object
     *
     * @access public
     * @param  str   $table
     * @param  array $array
     * @param  str   $where
     * @param  mixed $savepoint
     * @return mixed
     */
    public function update( $table, $array, $where=false, $savepoint=false )
    {
        //$array MUST be a key value pair array
        $objColumns = array_keys( $array );
        $objValues  = array_values( $array );
        $objUpdate  = '';

        for ( $i = 0; $i < count( $array ); $i++ )
        {
            if ( !$objValues[$i] )
            {
                if ( is_numeric( $objValues[$i] ) )
                    $objValues[$i] = 0;
                elseif ( is_bool( $objValues[$i] ) )
                    $objValues[$i] = false;
                else
                    $objValues[$i] = '';
            }

            if ( is_string( $objValues[$i] ) && !is_numeric( $objValues[$i] ) )
                $objUpdate .= $objColumns[$i] . "='" . $objValues[$i] . "',";
            else
                $objUpdate .= $objColumns[$i] . '=' . $objValues[$i] . ',';
        }

        $objUpdate = rtrim( $objUpdate, ',' );
        $objWhere  = ( !$where ) ? '' : ' WHERE ' . $where;
        $objQuery  = 'UPDATE ' . $table . ' SET ' . $objUpdate . $objWhere;

        $objStatement = $this->objDbType . 'Statement';
        $stmt = new $objStatement( $objQuery, $this, $this->objAutoCommit, $savepoint );

        return $stmt->objExecuteNonPrepared();
    }
}

?>