path: root/lib/class/dba.class.php

<?php
/* vim:set softtabstop=4 shiftwidth=4 expandtab: */
/**
 *
 * LICENSE: GNU General Public License, version 2 (GPLv2)
 * Copyright 2001 - 2013 Ampache.org
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License v2
 * as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 */

/* Make sure they aren't directly accessing it */
if (!defined('INIT_LOADED') || INIT_LOADED != '1') { exit; }

/**
 * Dba Class
 *
 * This is the database abstraction class
 * It duplicates the functionality of mysql_???
 * with a few exceptions, the row and assoc will always
 * return an array, simplifying checking on the far end
 * it will also auto-connect as needed, and has a default
 * database simplifying queries in most cases.
 *
 */
class Dba {

    public static $stats = array('query'=>0);

    private static $_sql;
    private static $config;

    /**
     * constructor
     * This does nothing with the DBA class
     */
    private function __construct() {

        // Rien a faire

    } // construct

    /**
     * query
     */
    public static function query($sql, $params) {
        // json_encode throws errors about UTF-8 cleanliness, which we don't
        // care about here.
        debug_event('Query', $sql . ' ' . @json_encode($params), 6);

        // Be aggressive, be strong, be dumb
        $tries = 0;
        do {
            $stmt = self::_query($sql, $params);
        } while (!$stmt && $tries++ < 3);

        return $stmt;
    }

    private static function _query($sql, $params) {
        $dbh = self::dbh();
        if (!$dbh) {
            debug_event('Dba', 'Error: failed to get database handle', 1);
            return false;
        }

        // Run the query
        if ($params) {
            $stmt = $dbh->prepare($sql);
            $stmt->execute($params);
        }
        else {
            $stmt = $dbh->query($sql);
        }

        // Save the query, to make debug easier
        self::$_sql = $sql;
        self::$stats['query']++;

        if (!$stmt) {
            debug_event('Dba', 'Error: ' . json_encode($dbh->errorInfo()), 1);
            self::disconnect();
        }
        else if ($stmt->errorCode() && $stmt->errorCode() != '00000') {
            debug_event('Dba', 'Error: ' . json_encode($stmt->errorInfo()), 1);
            self::disconnect();
            return false;
        }

        return $stmt;
    }

    /**
     * read
     */
    public static function read($sql, $params = null) {
        return self::query($sql, $params);
    }

    /**
     * write
     */
    public static function write($sql, $params = null) {
        return self::query($sql, $params);
    }

    /**
     * escape
     *
     * This runs a escape on a variable so that it can be safely inserted
     * into the sql
     */
    public static function escape($var) {
        $var = self::dbh()->quote($var);
        // This is slightly less ugly than it was, but still ugly
        return substr($var, 1, -1);
    }

    /**
     * fetch_assoc
     *
     * This emulates the mysql_fetch_assoc.
     * We force it to always return an array, albeit an empty one
     * The optional finish parameter affects whether we automatically clean
     * up the result set after the last row is read.
     */
    public static function fetch_assoc($resource, $finish = true) {
        if (!$resource) {
            return array();
        }

        $result = $resource->fetch(PDO::FETCH_ASSOC);

        if (!$result) {
            if ($finish) {
                self::finish($resource);
            }
            return array();
        }

        return $result;
    }

    /**
     * fetch_row
     *
     * This emulates the mysql_fetch_row
     * we force it to always return an array, albeit an empty one
     * The optional finish parameter affects whether we automatically clean
     * up the result set after the last row is read.
     */
    public static function fetch_row($resource, $finish = true) {
        if (!$resource) {
            return array();
        }

        $result = $resource->fetch(PDO::FETCH_NUM);;

        if (!$result) {
            if ($finish) {
                self::finish($resource);
            }
            return array();
        }

        return $result;
    }

    /**
     * num_rows
     *
     * This emulates the mysql_num_rows function which is really
     * just a count of rows returned by our select statement, this
     * doesn't work for updates or inserts.
     */
    public static function num_rows($resource) {
        if ($resource) {
            $result = $resource->rowCount();
            if ($result) {
                return $result;
            }
        }

        return 0;
    }

    /**
     * finish
     *
     * This closes a result handle and clears the memory associated with it
     */
    public static function finish($resource) {
        if ($resource) {
            $resource->closeCursor();
        }
    }

    /**
     * affected_rows
     *
     * This emulates the mysql_affected_rows function
     */
    public static function affected_rows($resource) {
        if ($resource) {
            $result = $resource->rowCount();
            if ($result) {
                return $result;
            }
        }

        return 0;
    }

    /**
     * _connect
     *
     * This connects to the database, used by the DBH function
     */
    private static function _connect() {
        $username = Config::get('database_username');
        $hostname = Config::get('database_hostname');
        $password = Config::get('database_password');

        try {
            $dbh = new PDO('mysql:host=' . $hostname, $username, $password);
        }
        catch (PDOException $e) {
            debug_event('Dba', 'Connection failed: ' . $e->getMessage(), 1);
            return null;
        }

        return $dbh;
    }

    private static function _setup_dbh($dbh, $database) {
        if (!$dbh) {
            return false;
        }

        $charset = self::translate_to_mysqlcharset(Config::get('site_charset'));
        $charset = $charset['charset'];
        if ($dbh->exec('SET NAMES ' . $charset) === false) {
            debug_event('Dba', 'Unable to set connection charset to ' . $charset, 1);
        }

        if ($dbh->exec('USE `' . $database . '`') === false) {
            debug_event('Dba', 'Unable to select database ' . $database . ': ' . json_encode($dbh->errorInfo()), 1);
        }

        if (Config::get('sql_profiling')) {
            $dbh->exec('SET profiling=1');
            $dbh->exec('SET profiling_history_size=50');
            $dbh->exec('SET query_cache_type=0');
        }
    }

    /**
     * check_database
     *
     * Make sure that we can connect to the database
     */
    public static function check_database() {
        $dbh = self::_connect();

        if (!$dbh || $dbh->errorCode()) {
            return false;
        }

        return true;
    }

    /**
     * check_database_inserted
     *
     * Checks to make sure that you have inserted the database
     * and that the user you are using has access to it.
     */
    public static function check_database_inserted() {
        $sql = "DESCRIBE session";
        $db_results = Dba::read($sql);

        if (!$db_results) {
            return false;
        }

        // Make sure the whole table is there
        if (Dba::num_rows($db_results) != '7') {
            return false;
        }

        return true;
    }

    /**
     * show_profile
     *
     * This function is used for debug, helps with profiling
     */
    public static function show_profile() {
        if (Config::get('sql_profiling')) {
            print '<br/>Profiling data: <br/>';
            $res = Dba::read('SHOW PROFILES');
            print '<table>';
            while ($r = Dba::fetch_row($res)) {
                print '<tr><td>' . implode('</td><td>', $r) . '</td></tr>';
            }
            print '</table>';
        }
    }

    /**
     * dbh
     *
     * This is called by the class to return the database handle
     * for the specified database, if none is found it connects
     */
    public static function dbh($database='') {
        if (!$database) {
            $database = Config::get('database_name');
        }

        // Assign the Handle name that we are going to store
        $handle = 'dbh_' . $database;

        if (!is_object(Config::get($handle))) {
            $dbh = self::_connect();
            self::_setup_dbh($dbh, $database);
            Config::set($handle, $dbh, true);
            return $dbh;
        }
        else {
            return Config::get($handle);
        }
    }

    /**
     * disconnect
     *
     * This nukes the dbh connection, this isn't used very often...
     */
    public static function disconnect($database = '') {
        if (!$database) {
            $database = Config::get('database_name'); 
        }

        $handle = 'dbh_' . $database;

        // Nuke it
        Config::set($handle, false, true);

        return true;
    }

    /**
     * insert_id
     */
    public static function insert_id() {
        $dbh = self::dbh();
        if ($dbh) {
            return $dbh->lastInsertId();
        }
        return null;
    }

    /**
     * error
     * this returns the error of the db
     */
    public static function error() {
        $dbh = self::dbh();
        if ($dbh) {
            return $dbh->errorCode();
        }
        return false;
    }

    /**
     * translate_to_mysqlcharset
     *
     * This translates the specified charset to a mysql charset.
     */
    public static function translate_to_mysqlcharset($charset) {
        // Translate real charset names into fancy MySQL land names
        switch (strtoupper($charset)) {
            case 'CP1250':
            case 'WINDOWS-1250':
                $target_charset = 'cp1250';
                $target_collation = 'cp1250_general_ci';
                break;
            case 'ISO-8859':
            case 'ISO-8859-2':
                $target_charset = 'latin2';
                $target_collation = 'latin2_general_ci';
                break;
            case 'ISO-8859-1':
            case 'CP1252':
            case 'WINDOWS-1252':
                $target_charset = 'latin1';
                $target_collation = 'latin1_general_ci';
                break;
            case 'EUC-KR':
                $target_charset = 'euckr';
                $target_collation = 'euckr_korean_ci';
                break;
            case 'CP932':
                $target_charset = 'sjis';
                $target_collation = 'sjis_japanese_ci';
                break;
            case 'KOI8-U':
                $target_charset = 'koi8u';
                $target_collation = 'koi8u_general_ci';
                break;
            case 'KOI8-R':
                $target_charset = 'koi8r';
                $target_collation = 'koi8r_general_ci';
                break;
            default;
            case 'UTF-8':
                $target_charset = 'utf8';
                $target_collation = 'utf8_unicode_ci';
                break;
        }

        return array(
            'charset' => $target_charset,
            'collation' => $target_collation
        );
    }

    /**
     * reset_db_charset
     *
     * This cruises through the database and trys to set the charset to the
     * current site charset. This is an admin function that can be run by an
     * administrator only. This can mess up data if you switch between charsets
     * that are not overlapping.
     */
    public static function reset_db_charset() {
        $translated_charset = self::translate_to_mysqlcharset(Config::get('site_charset'));
        $target_charset = $translated_charset['charset'];
        $target_collation = $translated_charset['collation'];

        // Alter the charset for the entire database
        $sql = "ALTER DATABASE `" . Config::get('database_name') . "` DEFAULT CHARACTER SET $target_charset COLLATE $target_collation";
        $db_results = Dba::write($sql);

        $sql = "SHOW TABLES";
        $db_results = Dba::read($sql);

        // Go through the tables!
        while ($row = Dba::fetch_row($db_results)) {
            $sql = "DESCRIBE `" . $row['0'] . "`";
            $describe_results = Dba::read($sql);

            // Change the tables default charset and colliation
            $sql = "ALTER TABLE `" . $row['0'] . "`  DEFAULT CHARACTER SET $target_charset COLLATE $target_collation";
            $alter_table = Dba::write($sql);

            // Iterate through the columns of the table
            while ($table = Dba::fetch_assoc($describe_results)) {
                if (
                (strpos($table['Type'], 'varchar') !== false) ||
                (strpos($table['Type'], 'enum') !== false) ||
                (strpos($table['Table'],'text') !== false)) {
                    $sql = "ALTER TABLE `" . $row['0'] . "` MODIFY `" . $table['Field'] . "` " . $table['Type'] . " CHARACTER SET " . $target_charset;
                    $charset_results = Dba::write($sql);
                    if (!$charset_results) {
                        debug_event('CHARSET','Unable to update the charset of ' . $table['Field'] . '.' . $table['Type'] . ' to ' . $target_charset,'3');
                    } // if it fails
                }
            }
        } 
    }

    /**
     * optimize_tables
     *
     * This runs an optimize on the tables and updates the stats to improve
     * join speed.
     * This can be slow, but is a good idea to do from time to time. We do 
     * it in case the dba isn't doing it... which we're going to assume they
     * aren't.
     */
    public static function optimize_tables() {
        $sql = "SHOW TABLES";
        $db_results = Dba::read($sql);

        while($row = Dba::fetch_row($db_results)) {
            $sql = "OPTIMIZE TABLE `" . $row[0] . "`";
            $db_results_inner = Dba::write($sql);

            $sql = "ANALYZE TABLE `" . $row[0] . "`";
            $db_results_inner = Dba::write($sql);
        }
    }
}
?>