pdo.php

PhpWiki是sourceforge的一个开源项目

<?php // -*-php-*-
rcs_id('$Id: PDO.php,v 1.2 2005/02/11 14:45:45 rurban Exp $');

/*
 Copyright 2005 $ThePhpWikiProgrammingTeam

 This file is part of PhpWiki.

 PhpWiki is free software; you can redistribute it and/or modify
 it under the terms of the GNU General Public License as published by
 the Free Software Foundation; either version 2 of the License, or
 (at your option) any later version.

 PhpWiki 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 PhpWiki; if not, write to the Free Software
 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
*/ 

/**
 * @author: Reini Urban
 */

require_once('lib/WikiDB/backend.php');

class WikiDB_backend_PDO
extends WikiDB_backend
{

    function WikiDB_backend_PDO ($dbparams) {
        $this->_dbparams = $dbparams;
        if (strstr($dbparams['dsn'], "://")) { // pear DB syntax
            $parsed = parseDSN($dbparams['dsn']);
            $this->_parsedDSN = $parsed;
            /* Need to convert the DSN
             *    dbtype(dbsyntax)://username:password@protocol+hostspec/database?option=value&option2=value2
             * to dbtype:option1=value1;option2=value2
             * PDO passes the DSN verbatim to the driver. But we need to extract the username + password
             * and cannot rely that the parseDSN keys have the same name as needed by the driver.
             * e.g: odbc:DSN=SAMPLE;UID=db2inst1;PWD=ibmdb2, mysql:host=127.0.0.1;dbname=testdb
             */
            $driver = $parsed['phptype'];
            unset($parsed['phptype']);
            unset($parsed['dbsyntax']);
            $dbparams['dsn'] = $driver . ":";
            $this->_dbh->database = $parsed['database'];
            // mysql needs to map database=>dbname, hostspec=>host. TODO for the others.
            $dsnmap = array('mysql' => array('database'=>'dbname', 'hostspec' => 'host')
                            );
            foreach (array('protocol','hostspec','port','socket','database') as $option) {
                if (!empty($parsed[$option])) {
                    $optionname = (isset($dsnmap[$driver][$option]) and !isset($parsed[$optionname]))
                        ? $dsnmap[$driver][$option]
                        : $option;
                    $dbparams['dsn'] .= ($optionname . "=" . $parsed[$option] . ";");
                    unset($parsed[$optionname]);
                }
                unset($parsed[$option]);
            }
            unset($parsed['username']);
            unset($parsed['password']);
            // pass the rest verbatim to the driver.
            foreach ($parsed as $option => $value) {
                $dbparams['dsn'] .= ($option . "=" . $value . ";");
            }
        } else {
            list($driver, $dsn) = explode(":", $dbparams['dsn'], 2);
            foreach (explode(";", trim($dsn)) as $pair) {
                if ($pair) {
                    list($option, $value) = explode("=", $pair, 2);
                    $this->_parsedDSN[$option] = $value;
                }
            }
            $this->_dbh->database = isset($this->_parsedDSN['database']) 
                ? $this->_parsedDSN['database'] 
                : $this->_parsedDSN['dbname'];
        }
        if (empty($this->_parsedDSN['password'])) $this->_parsedDSN['password'] = '';

        try {
            // try to load it dynamically (unix only)
            if (!loadPhpExtension("pdo_$driver")) {
                echo $GLOBALS['php_errormsg'], "<br>\n";
                trigger_error(sprintf("dl() problem: Required extension '%s' could not be loaded!", 
                                      "pdo_$driver"),
                              E_USER_WARNING);
            }

            // persistent is defined as DSN option, or with a config value.
            //   phptype://username:password@hostspec/database?persistent=false
            $this->_dbh = new PDO($dbparams['dsn'], 
                                  $this->_parsedDSN['username'], 
                                  $this->_parsedDSN['password'],
                                  array(PDO_ATTR_AUTOCOMMIT => true,
                                        PDO_ATTR_TIMEOUT    => DATABASE_TIMEOUT,
                                        PDO_ATTR_PERSISTENT => !empty($parsed['persistent'])
                                                               or DATABASE_PERSISTENT
                                        ));
        }
        catch (PDOException $e) {
            echo "<br>\nDB Connection failed: " . $e->getMessage();
            if (DEBUG & _DEBUG_VERBOSE or DEBUG & _DEBUG_SQL) {
                echo "<br>\nDSN: '", $dbparams['dsn'], "'";
                echo "<br>\n_parsedDSN: '", print_r($this->_parsedDSN), "'";
                echo "<br>\nparsed: '", print_r($parsed), "'";
            }
            exit();
        }
        if (DEBUG & _DEBUG_SQL) { // not yet implemented
            $this->_dbh->debug = true;
        }
        $this->_dsn = $dbparams['dsn'];
        $this->_dbh->databaseType = $driver;
        $this->_dbh->setAttribute(PDO_ATTR_CASE, PDO_CASE_NATURAL);
        // Use the faster FETCH_NUM, with some special ASSOC based exceptions.

        $this->_hasTransactions = true;
        try {
            $this->_dbh->beginTransaction();
        }
        catch (PDOException $e) {
            $this->_hasTransactions = false;
        }
        $sth = $this->_dbh->prepare("SELECT version()");
        $sth->execute();
        $this->_serverinfo['version'] = $sth->fetchSingle();
        $this->commit(); // required to match the try catch block above!

        $prefix = isset($dbparams['prefix']) ? $dbparams['prefix'] : '';
        $this->_table_names
            = array('page_tbl'     => $prefix . 'page',
                    'version_tbl'  => $prefix . 'version',
                    'link_tbl'     => $prefix . 'link',
                    'recent_tbl'   => $prefix . 'recent',
                    'nonempty_tbl' => $prefix . 'nonempty');
        $page_tbl = $this->_table_names['page_tbl'];
        $version_tbl = $this->_table_names['version_tbl'];
        $this->page_tbl_fields = "$page_tbl.id AS id, $page_tbl.pagename AS pagename, "
            . "$page_tbl.hits hits";
        $this->page_tbl_field_list = array('id', 'pagename', 'hits');
        $this->version_tbl_fields = "$version_tbl.version AS version, "
            . "$version_tbl.mtime AS mtime, "
            . "$version_tbl.minor_edit AS minor_edit, $version_tbl.content AS content, "
            . "$version_tbl.versiondata AS versiondata";
        $this->version_tbl_field_list = array('version', 'mtime', 'minor_edit', 'content',
            'versiondata');

        $this->_expressions
            = array('maxmajor'     => "MAX(CASE WHEN minor_edit=0 THEN version END)",
                    'maxminor'     => "MAX(CASE WHEN minor_edit<>0 THEN version END)",
                    'maxversion'   => "MAX(version)",
                    'notempty'     => "<>''",
                    'iscontent'    => "$version_tbl.content<>''");
        $this->_lock_count = 0;
    }

    function beginTransaction() {
        if ($this->_hasTransactions)
            $this->_dbh->beginTransaction();
    }
    function commit() {
        if ($this->_hasTransactions)
            $this->_dbh->commit();
    }
    function rollback() {
        if ($this->_hasTransactions)
            $this->_dbh->rollback();
    }
    /* no result */
    function query($sql) {
        $sth = $this->_dbh->prepare($sql);
        return $sth->execute();
    }
    /* with one result row */
    function getRow($sql) {
        $sth = $this->_dbh->prepare($sql);
        if ($sth->execute())
            return $sth->fetch(PDO_FETCH_BOTH);
        else 
            return false;
    }

    /**
     * Close database connection.
     */
    function close () {
        if (!$this->_dbh)
            return;
        if ($this->_lock_count) {
            trigger_error("WARNING: database still locked " . 
                          '(lock_count = $this->_lock_count)' . "\n<br />",
                          E_USER_WARNING);
        }
        $this->unlock(false, 'force');

        unset($this->_dbh);
    }

    /*
     * Fast test for wikipage.
     */
    function is_wiki_page($pagename) {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        $sth = $dbh->prepare("SELECT $page_tbl.id AS id"
                             . " FROM $nonempty_tbl, $page_tbl"
                             . " WHERE $nonempty_tbl.id=$page_tbl.id"
                             . "   AND pagename=?");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        if ($sth->execute())
            return $sth->fetchSingle();
        else
            return false;
    }
        
    function get_all_pagenames() {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        $sth = $dbh->exec("SELECT pagename"
                          . " FROM $nonempty_tbl, $page_tbl"
                          . " WHERE $nonempty_tbl.id=$page_tbl.id");
        return $sth->fetchAll(PDO_FETCH_NUM);
    }

    function numPages($filter=false, $exclude='') {
        $dbh = &$this->_dbh;
        extract($this->_table_names);
        $sth = $dbh->exec("SELECT count(*)"
                          . " FROM $nonempty_tbl, $page_tbl"
                          . " WHERE $nonempty_tbl.id=$page_tbl.id");
        return $sth->fetchSingle();
    }

    function increaseHitCount($pagename) {
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];
        $sth = $dbh->prepare("UPDATE $page_tbl SET hits=hits+1"
                             ." WHERE pagename=?");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $sth->execute();
    }

    /**
     * Read page information from database.
     */
    function get_pagedata($pagename) {
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];
        $sth = $dbh->prepare("SELECT id,pagename,hits,pagedata FROM $page_tbl"
                             ." WHERE pagename=?");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $sth->execute();
        $row = $sth->fetch(PDO_FETCH_NUM);
        return $row ? $this->_extract_page_data($row[3], $row[2]) : false;
    }

    function  _extract_page_data($data, $hits) {
        if (empty($data))
            return array('hits' => $hits);
        else 
            return array_merge(array('hits' => $hits), $this->_unserialize($data));
    }

    function update_pagedata($pagename, $newdata) {
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];

        // Hits is the only thing we can update in a fast manner.
        if (count($newdata) == 1 && isset($newdata['hits'])) {
            // Note that this will fail silently if the page does not
            // have a record in the page table.  Since it's just the
            // hit count, who cares?
            $sth = $dbh->prepare("UPDATE $page_tbl SET hits=? WHERE pagename=?");
            $sth->bindParam(1, $newdata['hits'], PDO_PARAM_INT);
            $sth->bindParam(2, $pagename, PDO_PARAM_STR, 100);
            $sth->execute();
            return;
        }
        $this->beginTransaction();
        $data = $this->get_pagedata($pagename);
        if (!$data) {
            $data = array();
            $this->_get_pageid($pagename, true); // Creates page record
        }
        
        $hits = (empty($data['hits'])) ? 0 : (int)$data['hits'];
        unset($data['hits']);

        foreach ($newdata as $key => $val) {
            if ($key == 'hits')
                $hits = (int)$val;
            else if (empty($val))
                unset($data[$key]);
            else
                $data[$key] = $val;
        }
        $sth = $dbh->prepare("UPDATE $page_tbl"
                             . " SET hits=?, pagedata=?"
                             . " WHERE pagename=?");
        $sth->bindParam(1, $hits, PDO_PARAM_INT);
        $sth->bindParam(2, $this->_serialize($data), PDO_PARAM_LOB);
        $sth->bindParam(3, $pagename, PDO_PARAM_STR, 100);
        if ($sth->execute()) {
            $this->commit();
            return true;
        } else {
            $this->rollBack();
            return false;
        }
    }

    function get_cached_html($pagename) {
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];
        $sth = $dbh->prepare("SELECT cached_html FROM $page_tbl WHERE pagename=?");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $sth->execute();
        return $sth->fetchSingle(PDO_FETCH_NUM);
    }

    function set_cached_html($pagename, $data) {
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];
        if (empty($data)) $data = '';
        $sth = $dbh->prepare("UPDATE $page_tbl"
                            . " SET cached_html=?"
                             . " WHERE pagename=?");
        $sth->bindParam(1, $data, PDO_PARAM_STR);
        $sth->bindParam(2, $pagename, PDO_PARAM_STR, 100);
        $sth->execute();
    }

    function _get_pageid($pagename, $create_if_missing = false) {
        // check id_cache
        global $request;
        $cache =& $request->_dbi->_cache->_id_cache;
        if (isset($cache[$pagename])) {
            if ($cache[$pagename] or !$create_if_missing) {
                return $cache[$pagename];
            }
        }
        
        $dbh = &$this->_dbh;
        $page_tbl = $this->_table_names['page_tbl'];
        $sth = $dbh->prepare("SELECT id FROM $page_tbl WHERE pagename=?");
        $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
        $id = $sth->fetchSingle();
        if (! $create_if_missing ) {
            return $id;
        }
        if (! $id ) {
            //mysql, mysqli or mysqlt
            if (substr($dbh->databaseType,0,5) == 'mysql') {
                // have auto-incrementing, atomic version
                $sth = $dbh->prepare("INSERT INTO $page_tbl"
                                     . " (id,pagename)"
                                     . " VALUES (NULL,?)");
                $sth->bindParam(1, $pagename, PDO_PARAM_STR, 100);
                $sth->execute();
                $id = $dbh->lastInsertId();
            } else {
                $this->beginTransaction();
                $sth = $dbh->prepare("SELECT MAX(id) FROM $page_tbl");