source: trunk/lib/DB.inc.php @ 122

<?php
/**
 * DB.inc.php
 * code by strangecode :: www.strangecode.com :: this document contains copyrighted information
 *
 * DB abstraction layer.
 *
 * @author  Quinn Comendant <quinn@strangecode.com>
 * @version 1.0.1
 */

class DB {

    // If DB::connect has successfully opened a db connection.
    var $_connected = false;

    // Database handler.
    var $dbh;

    // Hash of DB parameters.
    var $_params = array();

    // Default parameters.
    var $_param_defaults = array(

        // DB passwords should be set as apache environment variables in httpd.conf, readable only by root.
        'db_server' => 'localhost',
        'db_name' => null,
        'db_user' => null,
        'db_pass' => null,

        // Debugging.
        'db_always_debug' => false, // TRUE = display all SQL queries.
        'db_debug' => false, // TRUE = display db errors.
        'db_die_on_failure' => false, // TRUE = script stops on db error.
    );

    // Translate between HTML and MySQL character set names.
    var $mysql_character_sets = array(
        'utf-8' => 'utf8',
        'iso-8859-1' => 'latin1',
    );

    // Caches.
    var $existing_tables;
    var $table_columns;

    /**
     * This method enforces the singleton pattern for this class.
     *
     * @return  object  Reference to the global SessionCache object.
     * @access  public
     * @static
     */
    function &getInstance()
    {
        static $instance = null;

        if ($instance === null) {
            $instance = new DB();
        }

        return $instance;
    }

    /**
     * Constructor.
     */
    function DB()
    {
        // Initialize default params.
        if (isset($params) && is_array($params)) {
            // Merge new parameters with old overriding only those passed.
            $this->_params = array_merge($this->_params, $params);
        }
    }

    /**
     * Set (or overwrite existing) parameters by passing an array of new parameters.
     *
     * @access public
     *
     * @param  array    $params     Array of parameters (key => val pairs).
     */
    function setParam($params)
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (isset($params) && is_array($params)) {
            // Merge new parameters with old overriding only those passed.
            $_this->_params = array_merge($_this->_params, $params);
        } else {
            App::logMsg(sprintf('Parameters are not an array: %s', $params), LOG_ERR, __FILE__, __LINE__);
        }
    }

    /**
     * Return the value of a parameter.
     *
     * @access  public
     *
     * @param   string  $param      The key of the parameter to return.
     *
     * @return  mixed               Parameter value.
     */
    function getParam($param)
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (isset($_this->_params[$param])) {
            return $_this->_params[$param];
        } else {
            App::logMsg(sprintf('Parameter is not set: %s', $param), LOG_DEBUG, __FILE__, __LINE__);
            return null;
        }
    }

    /**
     * Connect to database with credentials in params.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 14:02:49
     */
    function connect()
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->getParam('db_name') || !$_this->getParam('db_user') || !$_this->getParam('db_pass')) {
            App::logMsg('Database credentials missing.', LOG_EMERG, __FILE__, __LINE__);
            return false;
        }

        // Connect to database. Always create a new link to the server.
        if ($_this->dbh = mysql_connect($_this->getParam('db_server'), $_this->getParam('db_user'), $_this->getParam('db_pass'), true)) {
            // Select database
            mysql_select_db($_this->getParam('db_name'), $_this->dbh);
        }

        // Test for connection errors.
        if (!$_this->dbh || mysql_error($_this->dbh)) {
            $mysql_error_msg = $_this->dbh ? 'Codebase MySQL error: (' . mysql_errno($_this->dbh) . ') ' . mysql_error($_this->dbh) : 'Codebase MySQL error: Could not connect to server.';
            App::logMsg($mysql_error_msg, LOG_EMERG, __FILE__, __LINE__);

            // Print helpful or pretty error?
            if ($_this->getParam('db_debug')) {
                echo $mysql_error_msg . "\n";
            } else {
                echo _("This page is temporarily unavailable. It should be back up in a few minutes.");
            }

            // Die or continue without connection?
            if ($_this->getParam('db_die_on_failure')) {
                echo "\n\n<!-- Script execution stopped out of embarrassment. -->";
                die;
            } else {
                return false;
            }
        }

        // DB connection success!
        $_this->_connected = true;

        // Tell MySQL what character set we're useing. Available only on MySQL verions > 4.01.01.
        $_this->query("/*!40101 SET NAMES '" . $_this->mysql_character_sets[strtolower(App::getParam('character_set'))] . "' */");

        return true;
    }

    /**
     * Close db connection.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 14:32:01
     */
    function close()
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->_connected) {
            return false;
        }

        mysql_close($_this->dbh);
    }

    /**
     * Return the current database handler.
     *
     * @access  public
     * @return  resource Current value of $this->dbh.
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   20 Aug 2005 13:50:36
     */
    function getDBH()
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->_connected) {
            return false;
        }

        return $_this->dbh;
    }

    /**
     * Returns connection status
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 14:58:09
     */
    function isConnected()
    {
        return $this->_connected;
    }
    
    /**
     * Returns a properly escaped string using mysql_real_escape_string() with the current connection's charset.
     *
     * @access  public
     * @param   string  $string     Input string to be sent as SQL query.
     * @return  string              Escaped string from mysql_real_escape_string()
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   06 Mar 2006 16:41:32
     */
    function escapeString($string)
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }
        return mysql_real_escape_string($string, $_this->dbh);
    }

    /**
     * A wrapper for mysql_query. Allows us to set the database link_identifier,
     * to trap errors and ease debugging.
     *
     * @param  string  $query   The SQL query to execute
     * @param  bool    $debug   If true, prints debugging info
     * @return resource         Query identifier
     */
    function query($query, $debug=false)
    {
        static $_query_count = 0;

        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->_connected) {
           return false;
        }

        $_query_count++;
        $debugqry = preg_replace("/\n[\t ]+/", "\n", $query);
        if ($_this->getParam('db_always_debug') || $debug) {
            echo "<!-- ----------------- Query $_query_count ---------------------\n$debugqry\n-->\n";
        }

        // Execute!
        $qid = mysql_query($query, $_this->dbh);

        // Error checking.
        if (!$qid || mysql_error($_this->dbh)) {
            if ($_this->getParam('db_debug')) {
                echo '<pre style="padding:2em; background:#ddd; font:9px monaco;">' . wordwrap(mysql_error($_this->dbh)) . '<hr>' . htmlspecialchars($debugqry) . '</pre>';
            } else {
                echo _("This page is temporarily unavailable. It should be back up in a few minutes.");
            }
            App::logMsg(sprintf('MySQL error %s: %s in query: %s', mysql_errno($_this->dbh), mysql_error($_this->dbh), $debugqry), LOG_EMERG, __FILE__, __LINE__);
            if ($_this->getParam('db_die_on_failure')) {
                echo "\n\n<!-- Script execution stopped out of embarrassment. -->";
                die;
            }
        }

        return $qid;
    }

    /**
     * Loads a list of tables in the current database into an array, and returns
     * true if the requested table is found. Use this function to enable/disable
     * funtionality based upon the current available db tables or to dynamically
     * create tables if missing.
     *
     * @param  string $table    The name of the table to search.
     * @param  bool   $strict   Get fresh table info (in case DB changed).
     * @return bool    true if given $table exists.
     */
    function tableExists($table, $use_cached_results=true)
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->_connected) {
            return false;
        }

        if (!isset($_this->existing_tables) || !$use_cached_results) {
            $_this->existing_tables = array();
            $qid = $_this->query("SHOW TABLES");
            while (list($row) = mysql_fetch_row($qid)) {
                $_this->existing_tables[] = $row;
            }
        }
        if (in_array($table, $_this->existing_tables)) {
            return true;
        } else {
            App::logMsg(sprintf('nonexistent DB table: %s.%s', $_this->getParam('db_name'), $table), LOG_ALERT, __FILE__, __LINE__);
            return false;
        }
    }

    /**
     * Tests if the given array of columns exists in the specified table.
     *
     * @param  string $table    The name of the table to search.
     * @param  array  $columns  An array of column names.
     * @param  bool   $strict   Exact schema match, or are additional fields in the table okay?
     * @param  bool   $strict   Get fresh table info (in case DB changed).
     * @return bool    true if given $table exists.
     */
    function columnExists($table, $columns, $strict=true, $use_cached_results=true)
    {
        if (!isset($_this) || !is_a($_this, 'DB') && !is_subclass_of($_this, 'DB')) {
            $_this =& DB::getInstance();
        }

        if (!$_this->_connected) {
            return false;
        }

        // Ensure the table exists.
        if (!$_this->tableExists($table, $use_cached_results)) {
            return false;
        }

        // For single-value columns.
        if (!is_array($columns)) {
            $columns = array($columns);
        }

        if (!isset($_this->table_columns[$table]) || !$use_cached_results) {
            // Populate and cache array of current columns for this table.
            $_this->table_columns[$table] = array();
            $qid = $_this->query("DESCRIBE $table");
            while ($row = mysql_fetch_row($qid)) {
                $_this->table_columns[$table][] = $row[0];
            }
        }

        if ($strict) {
            // Do an exact comparison of table schemas.
            sort($columns);
            sort($_this->table_columns[$table]);
            return $_this->table_columns[$table] == $columns;
        } else {
            // Only check that the specified columns are available in the table.
            $match_columns = array_intersect($_this->table_columns[$table], $columns);
            sort($columns);
            sort($match_columns);
            return $match_columns == $columns;
        }
    }

    /**
     * Reset cached items.
     *
     * @access  public
     * @author  Quinn Comendant <quinn@strangecode.com>
     * @since   28 Aug 2005 22:10:50
     */
    function resetCache()
    {
        $this->existing_tables = null;
        $this->table_columns = null;
    }

} // End.

?>