Source for file lib_db.php

<?php
/*
 * Lokorin.com
 * Copyright 2004-2006
 * Licensed under the GNU LGPL. See COPYING for full terms.
 */
/**
 * A library designed to abstract the database interaction. The library
 * provides functions to the most common operations, cutting down on the
 * repetitiveness of building queries. It also improves readability and avoids
 * unnecessary code.
 * @author Andreas Launila
 * @version $Revision: 1.16 $
 * @package com.lokorin.lokorin.lib
 * @license http://www.gnu.org/copyleft/lesser.html GNU Lesser General Public License
 */
 
$tablePrefix = 'lok_';    
/**#@+
 * Site specific table name constants.
 * @access public
 * @var string 
 */
define('TABLE_NEWS', $tablePrefix.'news');
define('TABLE_MENU', $tablePrefix.'menu');
define('TABLE_ERRORS', $tablePrefix.'errors');
define('TABLE_PROJECTS', $tablePrefix.'projects');
define('TABLE_ADMIN_LOGS', $tablePrefix.'admin_logs');
define('TABLE_PROGRAM_LANGS', $tablePrefix.'program_languages');
define('TABLE_DOWNLOADS', $tablePrefix.'downloads');
define('TABLE_COUNTERS', $tablePrefix.'counters');
define('TABLE_EVENTS', $tablePrefix.'events');
define('TABLE_DKPBANK', $tablePrefix.'dkpbank');
define('TABLE_DKPBANK_MEMBERS', $tablePrefix.'dkpbank_members');
define('TABLE_DKPBANK_IMAGES', $tablePrefix.'dkpbank_images');
define('TABLE_LINK_CATEGORIES', $tablePrefix.'link_categories');
define('TABLE_LINKS', $tablePrefix.'links');
define('TABLE_COMMENTS', $tablePrefix.'comments');
define('TABLE_PROJECT_IMAGES', $tablePrefix.'project_images');
define('TABLE_PROJECT_SUBMENUS', $tablePrefix.'project_submenus');
define('TABLE_PROJECT_STAGES', $tablePrefix.'project_stages');
define('TABLE_MAPPED_URLS', $tablePrefix.'mapped_urls');
define('TABLE_CUSTOM_ALIASES', $tablePrefix.'custom_aliases');
define('TABLE_ALIASES', $tablePrefix.'aliases');
define('TABLE_CUSTOM_REDIRECTS', $tablePrefix.'custom_redirects');
define('TABLE_REDIRECTS', $tablePrefix.'redirects');
define('TABLE_ABBR', $tablePrefix.'abbreviations');
define('TABLE_PERFORMANCE', $tablePrefix.'performance');
define('TABLE_SITEMAP', $tablePrefix.'sitemap');
define('TABLE_DOWNLOAD_CATEGORIES', $tablePrefix.'download_categories');
/**#@-*/ = 'mplf_';
/**#@+
 * Table name constants for the MPLF archive.
 * @access public
 * @var string 
 */
define('TABLE_MPLF_FILTERS', $mplfPrefix.'filters');
define('TABLE_MPLF_CATEGORIES', $mplfPrefix.'categories');
define('TABLE_MPLF_EDITS', $mplfPrefix.'edits');
define('TABLE_MPLF_MODERATORS', $mplfPrefix.'moderators');
define('TABLE_MPLF_CHANGES', $mplfPrefix.'changes');
/**#@-*/ * Describes a database abstraction. The abstraction maintains a connection
 * which is instance specific. Hence it is recommended that only one global
 * instance is used.
 * @author Andreas Launila
 * @package com.lokorin.lokorin.lib
 */
class DataBase {
    /**
     * The database connection to use.
     * @var mysqlconnection 
     */
    private $connection;
    /**
     * Contains any queued queries.
     * @var array 
     */
    private $queue = array();
    /**
     * The number of queries that have been performed by the database. 0 means
     * that no queries have been performed.
     * @var integer 
     */
    private $queryCount;
    /**
     * The number of milliseconds that have elapsed while waiting for the
     * database to complete a query. This is the sum of all query times.
     * @var integer 
     */
    private $queryDuration;
    /**
     * The one and only instance of the class.
     * @var DB 
     */
    private static $instance;
 
    /**
     * Gets the one and only instance of the class.
     * @return DB The one and only instance.
     */
    public static function getInstance() {
        if(!isset(DataBase::$instance)) {
            DataBase::$instance =  new DataBase();
        }
        return DataBase::$instance;
    }
 
    /**
     * Constructor for DB.
     */
    private function __construct() {
        $this->queryCount = 0;
        $this->queryDuration = 0;
    }
 
    /**
     * Connects to mysql and selects the correct databse.
     */
    private function connectToDb() {
        $this->connection = mysql_connect('localhost', 'root', 'starstorm');
        if(!$this->connection) {
            logFatalException(new Exception('Connection failed: '.mysql_error()));
        }
        mysql_select_db('lokorin_com',$this->connection) or die("Failed to select the database.");
    }
 
    /**
     * Ensures that there is a active valid connection the the database. Id
     * there is an active and valid connection then nothing is done, otherwise
     * a connection is created.
     */
    private function ensureConnected() {
        if(!isset($this->connection)) {
            $this->connectToDB();
        }
    }
 
    /**
     * Performs a specified query on the database.
     * @param string $query The query that should be performed.
     * @return mysqlresult The result resource of the query.
     */
    public function query($query) {
        $this->ensureConnected();
        
        //Start the query timing.
        $start = microtime(true);
        
        $result = mysql_query($query, $this->connection);
        if(!$result) {
            logFatalException(new Exception('Invalid MySQL query: '.
                    mysql_error()."\n Query: ".$query));
        }
        
        //Log the result of the timing.
        $elapsedTime = round((microtime(true) - $start)*pow(10, 6));
        $this->queryDuration += $elapsedTime;
        $this->queryCount++;
        
        return $result;
    }
    
    /**
     * Escapes any special characters in the specified value. The result is
     * also quoted if it is not numeric. All field values should be run
     * through this function to ensure security.
     * @param mixed $value The value that should be escaped.
     * @return mixed The escaped version of the value, with appropriate quotes
     *          if it is not numeric.
     */
    public function escape($value) {
        $this->ensureConnected();
           //Stripslashes
           if(get_magic_quotes_gpc()) {
               $value = stripslashes($value);
           }
           
        if(!is_int($value) && $value == '') {
               //Handle empty non integer values.
               return "''";
           } if(ctype_digit((string)$value)) {
            //Do nothing if digits.
            return $value;
        } else {
            //Quote if not digits.
            return "'".mysql_real_escape_string($value)."'";
        }
    }
 
    /**
     * Builds and executes an insert query based on the specified table and
     * values.
     * @param string $tableName The name of the table that the query should
     *          be executed on.
     * @param array $insertFields An array containing the values that should
     *          be used to build the insert query. The key should be the field
     *          name and the value should be the connected value for that field.
     * @return boolean True if the query was executed successfully, false
     *          otherwise.
     */
    public function queryInsert($tableName, $insertFields = array()) {
        return $this->query($this->buildInsertQuery($tableName, $insertFields));
    }
 
    /**
     * Builds an insert query based on the specified table and values.
     * @param string $tableName The name of the table that the values should
     *          be inserted in.
     * @param array $insertFields An array containing the values that should
     *          be used to build the insert query. The key should be the field
     *          name and the value should be the connected value for that field.
     * @return string The resulting query.
     */
    public function buildInsertQuery($tableName, $insertFields = array()) {
        $query = "INSERT INTO `".$tableName."` (";
        $fields = '';
        $values = '';
        foreach($insertFields as $key => $value) {
            if($fields != '') {
                $fields .= ', ';
            }
            $fields .= '`'.$key.'`';
            if($values != '') {
                $values .= ', ';
            }
            $values .= $this->escape($value);
        }
        $query .= $fields.") VALUES (".$values.")";
        return $query;
    }
 
    /**
     * Builds and executes a delete query on the specified table using the
     * specified values.
     * @param string $tableName The name of the table to delete rows from.
     * @param string $sqlSelector An sql selector to tell the query what rows
     *          should be affected by the query.
     * @return boolean True if the query was executed successfully, false
     *          otherwise.
     */
    public function queryDelete($tableName, $sqlSelector = '') {
        if(strlen($sqlSelector) == 0) {
            //Because of the possible consequences of letting this through as
            //a default it will be logged as a fatal exception instead.
            logFatalException(new IllegalArgumentException("Empty sqlSelectors" .
                    "are not allowed, use TRUNCATE instead."));
        }
        return $this->query($this->buildDeleteQuery($tableName, $sqlSelector));
    }
    
    /**
     * Builds and executes a delete query for the specified table using the
     * specified values.
     * @param string $tableName The name of the table to delete rows from.
     * @param string $sqlSelector An sql selector to tell the query what rows
     *          should be affected by the query.
     * @return string The resulting query.
     */
    public function buildDeleteQuery($tableName, $sqlSelector) {
        return "DELETE FROM `".$tableName."` ".$sqlSelector;
    }
 
    /**
     * Builds and executes an update query on the specified table using the
     * specified values and selector.
     * @param string $tableName The name of the table to update.
     * @param array $updateFields The fields that should be updated for all
     *          affected rows. The key is the field's name and the value is the
     *          new value that the field should have.
     * @param string $sqlSelector An optional selector to tell the query what
     *          rows should be affected by the query. The default is that all rows
     *          are affected.
     * @return boolean True if the query was executed successfully, false
     *          otherwise.
     */
    public function queryUpdate($tableName, $updateFields = array(), $sqlSelector = '') {
        return($this->query(
            $this->buildUpdateQuery($tableName, $updateFields, $sqlSelector)));
    }
    
    /**
     * Builds an update query for the specified table using the specified
     * values.
     * @param string $tableName The name of the table to update.
     * @param array $updateFields The fields that should be updated for all
     *          affected rows. The key is the field's name and the value is the
     *          new value that the field should have.
     * @param string $sqlSelector An optional selector to specify the resulting
     *          selector even more. The parameter is appended onto the end of the
     *          constructed selector.
     * @return string The resulting query.
     */
    public function buildUpdateQuery($tableName, $updateFields = array(), $sqlSelector = '') {
        $query = "UPDATE `".$tableName."` SET ";
        $variables = '';
        foreach($updateFields as $k => $value) {
            if($variables != '') {
                $variables .= ', ';
            }
            $variables .= '`'.$k."`=".$this->escape($value);
        }
        $query .= $variables." ".$sqlSelector;
        return $query;
    }
 
    /**
     * Selects specific table fields from a specific table. The select
     * operation can also specify an optional sql selector.
     * @param string $tableName The name of the table to select from.
     * @param array $fields An array with the names of the fields that should
     *          be selected.
     * @param string $sqlSelector Optional sql selector for the query.
     * @return array The selected rows and fields. The array contains one
     *          array per row. Each of those array rows contain keys with
     *          the specified field names along with the connected values.
     *          If no values were found then an empty array will be returned.
     */
    public function querySelect($tableName, $fields, $sqlSelector = '') {
        if(!is_array($fields)) {
            return false;
        }
 
        //Build the query.
        $query = "SELECT ";
        foreach($fields as $field) {
            if(strlen($query) > 7) {
                $query .= ", ";
            }
            $query .= '`'.$field.'`';
        }
        $query .= " FROM `".$tableName.'`';
        if($sqlSelector != '') {
            $query .= " ".$sqlSelector;
        }
 
        //Run the query
        $result = $this->query($query);
        $rows = array();
        while($row = mysql_fetch_array($result)) {
            $currentRow = array();
            //Extract all the row values.
            foreach($fields as $field) {
                $currentRow[$field] = $row[$field];
            }
            $rows[] = $currentRow;
        }
        return $rows;
    }
    
    /**
     * Selects all table fields from a specific table. The select
     * operation can also specify an optional sql selector.
     * @param string $tableName The name of the table to select from.
     * @param string $sqlSelector Optional sql selector for the query.
     * @return array The selected rows and fields. The array contains one
     *          array per row. Each of those array rows contain keys with
     *          the field names along with the connected values. If no values
     *          were found then an empty array will be returned.
     */
    public function querySelectAll($tableName, $sqlSelector = '') {
        //Build the query.
        $query = "SELECT * FROM `".$tableName.'`';
        if($sqlSelector != '') {
            $query .= " ".$sqlSelector;
        }
        
        //Run the query
        $result = $this->query($query);
        $rows = array();
        while($row = mysql_fetch_array($result)) {
            $rows[] = $row;
        }
        return $rows;
    }
    
    /**
     * Selects the first value found in a specified field in a specified table
     * that satisfies an sql selector.
     * @param string $tableName The name of the table to select from.
     * @param string $field The name of the table field from which the value
     *          should be selected.
     * @param string $sqlSelector An sql selector for the query. The selector
     *          specifies which rows that  should be selected and which should be
     *          ignored.
     * @return mixed The first value found that specifies all paramaters.
     * @throws NoSuchElementException If nor row matches the sql selector.
     */
    public function querySelectFirst($tableName, $field, $sqlSelector) {
        $rows = $this->querySelect($tableName, array($field), $sqlSelector);
        if(sizeof($rows) == 0) {
            throw new NoSuchElementException("Nothing matched the selector (" .
                    $sqlSelector . ") in the table (" . $tableName . ")");
        }
        return $rows[0][$field];
    }
    
    /**
     * Gets the number of rows that matches specified conditions.
     * @param string $tableName The table name to count from.
     * @param string $sqlSelector An optional sql selector for the query. This
     *          can be used to count a subset of the rows. The default is that all
     *          rows are counted.
     * @return integer The number of rows that satisfy the specified
     *          conditions.
     */
    public function queryCount($tableName, $sqlSelector = '') {
        $result = $this->query("SELECT COUNT(*) FROM `".$tableName."` ".$sqlSelector);
        list($count) = mysql_fetch_row($result);
        return $count;
    }
    
    /**
     * Builds a sql selector from specified field values.
     * @param array $fieldValues An array containing the specified values. The
     *          key is the field name, the value is the value that the field should
     *          have.
     * @return string A sqlSelector that specifies rows whos field have the
     *          specified values. Returns a boolean false if the argument is not
     *          an array.
     */
    public function buildSqlSelector($fieldValues) {
        if(!is_array($fieldValues)) {
            return false;
        }
        if(sizeof($fieldValues) == 0) {
            return '';
        }
        
        $sqlSelector = '';
        foreach($fieldValues as $key => $value) {
            if(strlen($sqlSelector) != 0) {
                $sqlSelector .= ' AND ';
            }
            $sqlSelector .= "(`".$key."`=".$this->escape($value).")";
        }
        return "WHERE ".$sqlSelector;
    }
 
    /**
     * Gets the last generated identifier on a per-connection basis.
     * @return integer The last generated identifier.
     */
    public function getLastInsertId() {
        $this->ensureConnected();
        return mysql_insert_id();
    }
 
    /**
     * Puts a specified query at the end of the queue.
     * @param string $query The query that should be added to the queue.
     */
    public function queueQuery($query) {
        $this->queue[] = $query;
    }
    
    /**
     * Flushes the queue, executing all queries in it in the order that they
     * were inserted and then clearing it.
     */
    public function flushQueue() {
        foreach($this->queue as $query) {
            $this->query($query);
        }
        $this->clearQueue;
    }
 
    /**
     * Gets the number of queries in the queue.
     * @return integer A positive number.
     */
    public function getQueueSize() {
        return count($this->queue);
    }
 
    /**
     * Clears the queue, removing all currently queued queries wihtout
     * executing them.
     */
    public function clearQueue() {
        $this->queue = array();
    }
    
    /**
     * Gets the number of queries that have been performed by the database
     * during the page request.
     * @return integer A non-negative number, 0 means that no queries have been
     *          performed.
     */
    public function getQueryCount() {
        return $this->queryCount;
    }
    
    /**
     * Gets the number of milliseconds that the database has spent processing
     * queries during the page request.
     * @return integer A non-negative number, e.g. 10 means that 10
     *          milliseconds have been spent on processing queries.
     */
    public function getElapsedQueryTime() {
        return $this->queryDuration;
    }
}
 
?>