Database class design

问题:

I'm creating a web app with various classes for things like the user, Smarty template control, etc.

I already have a database class which is all well and good, but I'm concerned about the performance of it.

Currently, in another class, I'm doing $this->db = new DB() to create a local database instance, however the database class's __construct() function creates a new connection to the MySQL server every time I make a new DB() instance, which is obviously less than sensible. This means that each instance of all my different classes that uses the database class makes a connection to the server. I don't have a vast amount of classes, but I only want one per page load.

This is a stripped down sample of what I have at the moment:

// Database class used by multiple other classes
class DB {
    private $dbh;

    function __construct() {
        $this->dbh = // PDO connection here
    }

    public function query($str) {
        // Do a query
    }
}

// Example class User
class User {
    private $db;    // Stores local instance of DB class.

    function __construct() {
        $this->db = new DB();    // Makes a new connection in DB::__construct()
    }

    public function login() {
        $this->db->query('SELECT * FROM users');
    }
}

I'm looking for the "best" or most common practice of doing this. I don't want to make 10-ish separate connections for each page load.

I want to know what the best way of using and managing a DB class in my application. My four thoughts are these:

1. Would using a persistent connection to the MySQL server solve this multiple connection issue for me?
2. Should I use a static factory class and return a DB instance instead of using new DB()?
3. Is the proper solution to use an entirely static class and just do DB::query() (for example) every time I reference it?
4. I often use multiple classes in another (so we might have class Folders which requires classes User, DB and Smarty). Is it general practice to extend each class somehow?

回答1:

If you make the variable holding the connection static, then you can check if you already established a connection. Static variables are the same across all instances of the class, so you can create 100 instances that all use the same connection. You just need to reference it statically: self::$dbh instead of $this->dbh.

class DB {
    private static $dbh = null;

    function __construct() {
        if ( is_null(self::$dbh) ) {
            self::$dbh = // PDO connection here
        }
    }
 }

回答2:

I would suggest you to check the $this -> db at first and then only create it.

function __construct() {
        if(!isset($this -> db) || !is_a("DB", $this -> db)) {
         $this->db = new DB();    // Makes a new connection in DB::__construct()
    }
}

回答3:

You need to inject db connection to your class instead of creating a new connection.

// In a bootstrap file
$db = new DB(); 


// User.php
class User {
    private $db;

    function __construct($db=null) {
       if (!is_null($db)) {
          $this->setConnection($db);
       }
    }

    function setConnection($db) {
       $this->db = $db;
    }

    public function login() {
        $this->db->query('SELECT * FROM users');
    }
}

BTW, Zend_Registry is a good solution if you prefer it http://framework.zend.com/manual/en/zend.registry.using.html

回答4:

<?php

class DBLayer {

    public $prefix;
    public $link_id;
    public $query_result;
    public $saved_queries = array();
    public $num_queries = 0;

    public function DBLayer() {

        $db_prefix = '';
        $this->prefix = $db_prefix;
        if (isset($this->link_id)) {
            return $this->link_id;
        }
        $this->link_id = @mysql_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, true);

        if ($this->link_id) {
            if (@mysql_select_db(DATABASE_NAME, $this->link_id)) {
                return $this->link_id;
            } else {
                $this->wplog("Unable to select database. Host:". DATABASE_HOST. "Database:" . DATABASE_NAME   . " Error: " . mysql_error(), 'ERROR', __FILE__, __LINE__);
            }
        } else {
            $this->wplog("Unable to connect to MySQL server. Host: " . DATABASE_HOST . " Error: " . mysql_error(), 'ERROR', __FILE__, __LINE__);
        }
    }

    public function query($sql, $unbuffered = false) {

        if(LOG){echo "<hr>$sql";}

        $this->query_result = @mysql_query($sql, $this->link_id);

        if ($this->query_result) {
            return $this->query_result;
        } else {
            $msg= $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error();
            $this->wplog($msg);
        }
    }

    public function result($query_id = 0, $row = 0) {
        return ($query_id) ? @mysql_result($query_id, $row) : false;
    }

    public function fetch_assoc($query_id = 0) {
        return ($query_id) ? @mysql_fetch_assoc($query_id) : false;
    }

    public function fetch_row($query_id = 0) {
        return ($query_id) ? @mysql_fetch_row($query_id) : false;
    }

    public function num_rows($query_id = 0) {
        return ($query_id) ? @mysql_num_rows($query_id) : false;
    }

    public function affected_rows() {
        return ($this->link_id) ? @mysql_affected_rows($this->link_id) : false;
    }

    public function insert_id() {
        return ($this->link_id) ? @mysql_insert_id($this->link_id) : false;
    }

    public function get_num_queries() {
        return $this->num_queries;
    }

    public function get_saved_queries() {
        return $this->saved_queries;
    }

    public function free_result($query_id = false) {
        return ($query_id) ? @mysql_free_result($query_id) : false;
    }

    public function escape($str) {
        if (function_exists('mysql_real_escape_string'))
        return mysql_real_escape_string($str, $this->link_id);
        else
        return mysql_escape_string($str);
    }

    public function get_select($q, $onlyone=false) {

        $results = array();

        $r = $this->query($q);

        if ($onlyone) {
            return $this->fetch_assoc($r);
        }
        while ($l = $this->fetch_assoc($r)) {

            $results[] = $l;
        }

        return $results;
    }

    public function get_error() {
        return mysql_error();
    }

    public function close() {
        if ($this->link_id) {
            if ($this->query_result)
            @mysql_free_result($this->query_result);

            return @mysql_close($this->link_id);
        }
        else
        return false;
    }

    public function auto_execute($table, $data, $type, $criteria='') {

        $result = $this->get_select("desc " . $table);


        if ($type == "INSERT")
        $start = "insert into " . $table . " set ";
        elseif ($type == "UPDATE")
        $start = "update " . $table . " set ";
        $sql = $start;
        foreach ($result as $rst) {
            foreach ($data as $key => $value) {
                if ($key == $rst['Field'] and $key !== 0) {
                    if ((@ereg('date', $rst['Type'])) && $value == '') {
                        $sql = $sql . "`".$key."`" . "=NULL, ";
                    } elseif ((!@ereg('int', $rst['Type']))) {
                        $sql = $sql . "`".$key."`" . "='" . $value . "', ";
                    } else {
                        if (trim($value) != "") {
                            $sql = $sql . "`".$key."`" . "=" . $value . ", ";
                        }
                    }
                }
            }
        }

        if ($sql == $start)
        return 0;
        else {
            $sql = substr($sql, 0, strlen($sql) - 2);
            if ($type == "UPDATE" and !empty($criteria))
            $sql = $sql . " where " . $criteria;
        }
        //echo $sql;exit;
        if ($this->query($sql)) {
            $return = $this->insert_id();
        } else {
            $return = 0;
        }
        return $return;
    }
    private function wplog($message) {
        if(LOG==true){
            $lineBreak = "\n"; // this function will NOT work on a windows server without further modification

            $contents = date('Y-m-d H:i:s') . ' ' . $message. $lineBreak;

            $myFile = SERVER_PATH.'/log.txt';
$fh = fopen($myFile, 'a') ;
fwrite($fh, $contents);
fclose($fh);

            //SetFileContents(SERVER_PATH.'/log.txt',$contents,'a');
        }
    }

}