sqlrelay.php

适合于Unix/Linux下的一个持久数据库连接池

<?php
/* vim: set expandtab tabstop=4 shiftwidth=4: */
// +----------------------------------------------------------------------+
// | PHP Version 4                                                        |
// +----------------------------------------------------------------------+
// | Copyright (c) 1997-2003 The PHP Group                                |
// +----------------------------------------------------------------------+
// | This source file is subject to version 2.02 of the PHP license,      |
// | that is bundled with this package in the file COPYING, and is        |
// | available at through the world-wide-web at                           |
// | http://www.php.net/license/2_02.txt.                                 |
// | If you did not receive a copy of the PHP license and are unable to   |
// | obtain it through the world-wide-web, please send a note to          |
// | [e-mail] so we can mail you a copy immediately.               |
// +----------------------------------------------------------------------+
// | Author: David Muse <[e-mail]>                       |
// +----------------------------------------------------------------------+
//
// $Id: sqlrelay.php,v 1.26 2007/05/03 02:56:51 mused Exp $
//
// Database independent query interface definition for PHP's SQLRelay
// extension.
//

require_once "DB/common.php";

class DB_sqlrelay_cursor
{
    var $cursor;
    var $connection;
    var $rownum = 0;

    function DB_sqlrelay_cursor($cur,$connection)
    {
        $this->cursor = $cur;
        $this->connection = $connection;
        $this->rownun = 0;
    }
}

class DB_sqlrelay extends DB_common
{
    // {{{ properties

    var $connection;
    var $identity = "";
    var $bindformat = "";
    var $phptype = "sqlrelay";
    var $dbsyntax = "sqlrelay";
    var $autocommit = false;
    var $fetchmode = DB_FETCHMODE_ORDERED; /* Default fetch mode */
    var $affectedrows = 0;
    var $prepare_types = array();
    var $is_select = array();
    var $features = array(
            'prepare' => true,
            'pconnect' => true,
            'transactions' => true,
            'limit' => 'emulate'
        );


    // }}}
    // {{{ constructor

    /**
     * DB_sqlrelay constructor.
     *
     * @access public
     */

    function DB_sqlrelay()
    {
        $this->DB_common();
    }

    // }}}
    // {{{ connect()

    /**
     * Connect to a database and log in as the specified user.
     *
     * @param $dsn the data source name (see DB::parseDSN for syntax)
     * @param $persistent (optional) whether the connection should
     *        be persistent
     * @access public
     * @return int DB_OK on success, a DB error on failure
     */

    function connect($dsninfo, $persistent = false)
    {

        if (method_exists($this,'loadExtension') || 
                            method_exists($this,'loadextension')) {
            if (!PEAR::loadExtension('sql_relay')) {
                return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
            }
        } else {
            if (!DB::assertExtension('sql_relay')) {
                return $this->raiseError(DB_ERROR_EXTENSION_NOT_FOUND);
            }
        }

        $this->dsn = $dsninfo;

        $this->connection = sqlrcon_alloc($dsninfo['hostspec'],
                                            $dsninfo['port'],
                                            $dsninfo['socket'],
                                            $dsninfo['username'],
                                            $dsninfo['password'],
                                            0,1);
#sqlrcon_debugOn($this->connection);
        return DB_OK;
    }

    // }}}
    // {{{ disconnect()

    /**
     * Log out and disconnect from the database.
     *
     * @access public
     *
     * @return bool TRUE on success, FALSE if not connected.
     */
    function disconnect()
    {
        sqlrcon_free($this->connection);
        $this->connection = null;
        return true;
    }

    // }}}
    // {{{ isSelect()

    function isSelect($query)
    {
        return ((preg_match('/^\s*SHOW TABLES\s*/si', $query) ||
                    preg_match('/^\s*\(?\s*SELECT\s+/si', $query)) &&
                    !preg_match('/^\s*\(?\s*SELECT\s+INTO\s+/si', $query));
    }

    // }}}
    // {{{ simpleQuery()

    /**
     * Send a query to SQLRelay and return the results as a SQLRelay cursor.
     *
     * @param the SQL query
     *
     * @access public
     *
     * @return mixed returns a valid SQLRelay cursor for successful SELECT
     * queries, DB_OK for other successful queries.  A DB error is
     * returned on failure.
     */
    function simpleQuery($query)
    {

        $cursor = sqlrcur_alloc($this->connection);

        if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
            sqlrcur_lowerCaseColumnNames ($cursor);
        }
        if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
            sqlrcur_getNullsAsEmptyStrings ($cursor);
        } else {
            sqlrcur_getNullsAsNulls ($cursor);
        }

        sqlrcur_setResultSetBufferSize($cursor,100);
        if (!sqlrcur_sendQuery($cursor, $query)) {
            $this->affectedrows = 0;
            $error = sqlrcur_errorMessage($cursor);
            sqlrcur_free($cursor);
            return $this->raiseError(DB_ERROR, null, null, null, $error);
        }

        $this->affectedrows = sqlrcur_affectedRows($cursor);

        /* If the query was a select, return a cursor, otherwise return DB_OK.
        If there are any affected rows, then the query was definitely not a
        select, otherwise there's no good way to know what kind of query it was
        except by parsing it. */
        if ($this->isSelect($query)) {
            return new DB_sqlrelay_cursor($cursor,$this->connection);
        }

        sqlrcur_free($cursor);
        return DB_OK;
    }

    // }}}
    // {{{ prepare()

    /**
    * Prepares a query for multiple execution with execute().
    *
    * @param string the query to prepare
    *
    * @return SQLRelay cursor
    *
    * @access public
    * @see execute
    */

    function prepare($query)
    {
        if ($this->identity == "") {
            $this->identity = sqlrcon_identify($this->connection);
        }

        if ($this->bindformat == "") {
echo("bindformat is ".$this->bindformat."\n");
            $this->bindformat = sqlrcon_bindFormat($this->connection);
echo("bindformat is ".$this->bindformat."\n");
        }

        $cursor = sqlrcur_alloc($this->connection);

        if ($this->options['portability'] & DB_PORTABILITY_LOWERCASE) {
            sqlrcur_lowerCaseColumnNames ($cursor);
        }
        if ($this->options['portability'] & DB_PORTABILITY_NULL_TO_EMPTY) {
            sqlrcur_getNullsAsEmptyStrings ($cursor);
        } else {
            sqlrcur_getNullsAsNulls ($cursor);
        }

        sqlrcur_setResultSetBufferSize($cursor,100);

        # handle ?-delimited bind variables by rewriting the query and creating
        # : or @ delimited variables
        # for db's which already uses ?-delimited variables, we don't need
        # to do this...
        if ($this->bindformat == "?") {
            $newquery = $query;
        } else {
            $paramindex = 0;
            $newquery = '';
            $types = array();
            $inquotes = false;
            for ($i = 0; $i < strlen($query); $i++) {
                if ($query[$i] == '\'') {
                    $inquotes = !$inquotes;
                } else if (($query[$i] == '?' || $query[$i] == '&') &&
                                                            !$inquotes) {
                    if ($query[$i] == '?') {
                        $types[$paramindex] = DB_PARAM_SCALAR;
                    } else {
                        $types[$paramindex] = DB_PARAM_OPAQUE;
                    }
                    if ($this->bindformat == "@*") {
                        $newquery .= "@bind" . $paramindex;
                    } else if ($this->bindformat == "$1") {
                        $newquery .= "$" . ($paramindex+1);
                    } else {
                        $newquery .= ":bind" . $paramindex;
                    }
                    $paramindex++;
                    continue;
                }
                $newquery .= $query[$i];
            }        
            $this->prepare_types[(int)$cursor] = $types;
        }

        $this->is_select[(int)$cursor] = $this->isSelect($newquery);

        sqlrcur_prepareQuery($cursor, $newquery);
        return new DB_sqlrelay_cursor($cursor,$this->connection);
    }

    // }}}
    // {{{ autoExecute()

    /**
     * Automaticaly generates an insert or update query and call prepare()
     * and execute() with it
     *
     * @param string $table         the table name
     * @param array  $fields_values the associative array where $key is a
     *                               field name and $value its value
     * @param int    $mode          a type of query to make:
     *                               DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE
     * @param string $where         for update queries: the WHERE clause to
     *                               append to the SQL statement.  Don't
     *                               include the "WHERE" keyword.
     *
     * @return mixed  a new DB_result object for successful SELECT queries
     *                 or DB_OK for successul data manipulation queries.
     *                 A DB_Error object on failure.
     *
     * @uses DB_common::autoPrepare(), DB_common::execute()
     */
    function autoExecute($table, $fields_values, $mode = DB_AUTOQUERY_INSERT,
                         $where = false)
    {
        $sth = $this->autoPrepare($table, array_keys($fields_values), $mode,
                                  $where);
        if (DB::isError($sth)) {
            return $sth;
        }
        $ret =& $this->execute($sth, $fields_values);
        $this->freePrepared($sth);
        return $ret;

    }

    // }}}
    // {{{ buildManipSQL()

    /**
     * Produces an SQL query string for autoPrepare()
     *
     * Example:
     * <pre>
     * buildManipSQL('table_sql', array('field1', 'field2', 'field3'),
     *               DB_AUTOQUERY_INSERT);
     * </pre>
     *
     * That returns
     * <samp>
     * INSERT INTO table_sql (field1,field2,field3) VALUES (?,?,?)
     * </samp>
     *
     * NOTES:
     *   - This belongs more to a SQL Builder class, but this is a simple
     *     facility.
     *   - Be carefull! If you don't give a $where param with an UPDATE
     *     query, all the records of the table will be updated!
     *
     * @param string $table         the table name
     * @param array  $table_fields  the array of field names
     * @param int    $mode          a type of query to make:
     *                               DB_AUTOQUERY_INSERT or DB_AUTOQUERY_UPDATE
     * @param string $where         for update queries: the WHERE clause to
     *                               append to the SQL statement.  Don't
     *                               include the "WHERE" keyword.
     *
     * @return string  the sql query for autoPrepare()
     */
    function buildManipSQL($table, $table_fields, $mode, $where = false)
    {
        if ($this->identity == "") {
            $this->identity = sqlrcon_identify($this->connection);
        }
        if (count($table_fields) == 0) {
            return $this->raiseError(DB_ERROR_NEED_MORE_DATA);
        }
        $first = true;
        $pgbindindex = 1;
        switch ($mode) {
            case DB_AUTOQUERY_INSERT:
                $values = '';
                $names = '';
                foreach ($table_fields as $value) {
                    if ($first) {
                        $first = false;
                    } else {
                        $names .= ',';
                        $values .= ',';
                    }
                    $names .= $value;
                    if ($this->bindformat == "@*") {
                        $values .= "@$value";
                    } else if ($this->bindformat == "$1") {
                        $values .= "$" . $pgbindindex;
                        $pgbindindex++;
                    } else {
                        $values .= ":$value";
                    }
                }
                return "INSERT INTO $table ($names) VALUES ($values)";
            case DB_AUTOQUERY_UPDATE:
                $set = '';
                foreach ($table_fields as $value) {
                    if ($first) {
                        $first = false;
                    } else {
                        $set .= ',';
                    }
                    # FIXME: handle other db formats too
                    if ($this->bindformat == "@*") {
                        $set .= "$value = @$value";
                    } else if ($this->bindformat == "$1") {
                        $set .= "$value = $" . $pgbindindex;
                        $pgbindindex++;
                    } else {
                        $set .= "$value = :$value";
                    }
                }
                $sql = "UPDATE $table SET $set";
                if ($where) {
                    $sql .= " WHERE $where";
                }
                return $sql;
            default:
                return $this->raiseError(DB_ERROR_SYNTAX);
        }
    }

    // }}}
    // {{{ execute()
    /**
    * Executes a prepared SQL query
    * With execute() the generic query of prepare is
    * assigned with the given data array. The values
    * of the array inserted into the query in the same
    * order like the array order
    *
    * @param resource $cursor query handle from prepare()
    * @param array    $data numeric array containing the
    *                       data to insert into the query
    *
    * @return mixed  a new DB_Result or a DB_Error when fail
    *
    * @access public
    * @see prepare()
    */

    function &execute(&$sqlrcursor, $data = false)
    {

        sqlrcur_clearBinds($sqlrcursor->cursor);

        # handle ?/& binds...
        $types=&$this->prepare_types[(int)$sqlrcursor->cursor];
        $typessize = sizeof($types);
        if ($typessize > 0 && $typessize != sizeof($data)) {
            return $this->raiseError(DB_ERROR_MISMATCH);
        }

        for ($i = 0; $i < $typessize; $i++) {
            if (is_array($data)) {
                $pdata[$i] = &$data[$i];
            } else {
                $pdata[$i] = &$data;
            }
            if ($types[$i] == DB_PARAM_OPAQUE) {
                $fp = fopen($pdata[$i], "r");
                $pdata[$i] = '';