pgsql.php

开源邮件管理系统

            $from  = $match[2];
            $what  = (count($matches) == 6) ? $match[5] : $match[3];
            return $manip.' '.$from.' '.$what.' WHERE ctid=(SELECT ctid FROM '.$from.' '.$where.' LIMIT '.$limit.')';
        }
        //return error?
        return $query;
    }

    // }}}
    // {{{ getServerVersion()

    /**
     * return version information about the server
     *
     * @param bool   $native  determines if the raw version string should be returned
     * @return mixed array/string with version information or MDB2 error object
     * @access public
     */
    function getServerVersion($native = false)
    {
        $query = 'SHOW SERVER_VERSION';
        if ($this->connected_server_info) {
            $server_info = $this->connected_server_info;
        } else {
            $server_info = $this->queryOne($query, 'text');
            if (PEAR::isError($server_info)) {
                return $server_info;
            }
        }
        // cache server_info
        $this->connected_server_info = $server_info;
        if (!$native && !PEAR::isError($server_info)) {
            $tmp = explode('.', $server_info, 3);
            if (empty($tmp[2])
                && isset($tmp[1])
                && preg_match('/(\d+)(.*)/', $tmp[1], $tmp2)
            ) {
                $server_info = array(
                    'major' => $tmp[0],
                    'minor' => $tmp2[1],
                    'patch' => null,
                    'extra' => $tmp2[2],
                    'native' => $server_info,
                );
            } else {
                $server_info = array(
                    'major' => isset($tmp[0]) ? $tmp[0] : null,
                    'minor' => isset($tmp[1]) ? $tmp[1] : null,
                    'patch' => isset($tmp[2]) ? $tmp[2] : null,
                    'extra' => null,
                    'native' => $server_info,
                );
            }
        }
        return $server_info;
    }

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

    /**
     * Prepares a query for multiple execution with execute().
     * With some database backends, this is emulated.
     * prepare() requires a generic query as string like
     * 'INSERT INTO numbers VALUES(?,?)' or
     * 'INSERT INTO numbers VALUES(:foo,:bar)'.
     * The ? and :name and are placeholders which can be set using
     * bindParam() and the query can be sent off using the execute() method.
     * The allowed format for :name can be set with the 'bindname_format' option.
     *
     * @param string $query the query to prepare
     * @param mixed   $types  array that contains the types of the placeholders
     * @param mixed   $result_types  array that contains the types of the columns in
     *                        the result set or MDB2_PREPARE_RESULT, if set to
     *                        MDB2_PREPARE_MANIP the query is handled as a manipulation query
     * @param mixed   $lobs   key (field) value (parameter) pair for all lob placeholders
     * @return mixed resource handle for the prepared query on success, a MDB2
     *        error on failure
     * @access public
     * @see bindParam, execute
     */
    function &prepare($query, $types = null, $result_types = null, $lobs = array())
    {
        if ($this->options['emulate_prepared']) {
            $obj =& parent::prepare($query, $types, $result_types, $lobs);
            return $obj;
        }
        $is_manip = ($result_types === MDB2_PREPARE_MANIP);
        $offset = $this->offset;
        $limit = $this->limit;
        $this->offset = $this->limit = 0;
        $result = $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'pre'));
        if ($result) {
            if (PEAR::isError($result)) {
                return $result;
            }
            $query = $result;
        }
        $pgtypes = function_exists('pg_prepare') ? false : array();
        if ($pgtypes !== false && !empty($types)) {
            $this->loadModule('Datatype', null, true);
        }
        $query = $this->_modifyQuery($query, $is_manip, $limit, $offset);
        $placeholder_type_guess = $placeholder_type = null;
        $question = '?';
        $colon = ':';
        $positions = array();
        $position = $parameter = 0;
        while ($position < strlen($query)) {
            $q_position = strpos($query, $question, $position);
            $c_position = strpos($query, $colon, $position);
            //skip "::type" cast ("select id::varchar(20) from sometable where name=?")
            $doublecolon_position = strpos($query, '::', $position);
            if ($doublecolon_position !== false && $doublecolon_position == $c_position) {
                $c_position = strpos($query, $colon, $position+2);
            }
            if ($q_position && $c_position) {
                $p_position = min($q_position, $c_position);
            } elseif ($q_position) {
                $p_position = $q_position;
            } elseif ($c_position) {
                $p_position = $c_position;
            } else {
                break;
            }
            if (is_null($placeholder_type)) {
                $placeholder_type_guess = $query[$p_position];
            }
            
            $new_pos = $this->_skipDelimitedStrings($query, $position, $p_position);
            if (PEAR::isError($new_pos)) {
                return $new_pos;
            }
            if ($new_pos != $position) {
                $position = $new_pos;
                continue; //evaluate again starting from the new position
            }

            if ($query[$position] == $placeholder_type_guess) {
                if (is_null($placeholder_type)) {
                    $placeholder_type = $query[$p_position];
                    $question = $colon = $placeholder_type;
                    if (!empty($types) && is_array($types)) {
                        if ($placeholder_type == ':') {
                        } else {
                            $types = array_values($types);
                        }
                    }
                }
                if ($placeholder_type_guess == '?') {
                    $length = 1;
                    $name = $parameter;
                } else {
                    $regexp = '/^.{'.($position+1).'}('.$this->options['bindname_format'].').*$/s';
                    $param = preg_replace($regexp, '\\1', $query);
                    if ($param === '') {
                        $err =& $this->raiseError(MDB2_ERROR_SYNTAX, null, null,
                            'named parameter name must match "bindname_format" option', __FUNCTION__);
                        return $err;
                    }
                    $length = strlen($param) + 1;
                    $name = $param;
                }
                if ($pgtypes !== false) {
                    if (is_array($types) && array_key_exists($name, $types)) {
                        $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$name]);
                    } elseif (is_array($types) && array_key_exists($parameter, $types)) {
                        $pgtypes[] = $this->datatype->mapPrepareDatatype($types[$parameter]);
                    } else {
                        $pgtypes[] = 'text';
                    }
                }
                if (($key_parameter = array_search($name, $positions))) {
                    $next_parameter = 1;
                    foreach ($positions as $key => $value) {
                        if ($key_parameter == $key) {
                            break;
                        }
                        ++$next_parameter;
                    }
                } else {
                    ++$parameter;
                    $next_parameter = $parameter;
                    $positions[] = $name;
                }
                $query = substr_replace($query, '$'.$parameter, $position, $length);
                $position = $p_position + strlen($parameter);
            } else {
                $position = $p_position;
            }
        }
        $connection = $this->getConnection();
        if (PEAR::isError($connection)) {
            return $connection;
        }
        static $prep_statement_counter = 1;
        $statement_name = sprintf($this->options['statement_format'], $this->phptype, $prep_statement_counter++ . sha1(microtime() + mt_rand()));
        $statement_name = substr(strtolower($statement_name), 0, $this->options['max_identifiers_length']);
        if ($pgtypes === false) {
            $result = @pg_prepare($connection, $statement_name, $query);
            if (!$result) {
                $err =& $this->raiseError(null, null, null,
                    'Unable to create prepared statement handle', __FUNCTION__);
                return $err;
            }
        } else {
            $types_string = '';
            if ($pgtypes) {
                $types_string = ' ('.implode(', ', $pgtypes).') ';
            }
            $query = 'PREPARE '.$statement_name.$types_string.' AS '.$query;
            $statement =& $this->_doQuery($query, true, $connection);
            if (PEAR::isError($statement)) {
                return $statement;
            }
        }

        $class_name = 'MDB2_Statement_'.$this->phptype;
        $obj = new $class_name($this, $statement_name, $positions, $query, $types, $result_types, $is_manip, $limit, $offset);
        $this->debug($query, __FUNCTION__, array('is_manip' => $is_manip, 'when' => 'post', 'result' => $obj));
        return $obj;
    }

    // }}}
    // {{{ function getSequenceName($sqn)

    /**
     * adds sequence name formatting to a sequence name
     *
     * @param   string  name of the sequence
     *
     * @return  string  formatted sequence name
     *
     * @access  public
     */
    function getSequenceName($sqn)
    {
        if (false === $this->options['disable_smart_seqname']) {
            if (strpos($sqn, '_') !== false) {
                list($table, $field) = explode('_', $sqn, 2);
            }
            $schema_list = $this->queryOne("SELECT array_to_string(current_schemas(false), ',')");
            if (PEAR::isError($schema_list) || empty($schema_list) || count($schema_list) < 2) {
                $order_by = ' a.attnum';
                $schema_clause = ' AND n.nspname=current_schema()';
            } else {
                $schemas = explode(',', $schema_list);
                $schema_clause = ' AND n.nspname IN ('.$schema_list.')';
                $counter = 1;
                $order_by = ' CASE ';
                foreach ($schemas as $schema) {
                    $order_by .= ' WHEN n.nspname='.$schema.' THEN '.$counter++;
                }
                $order_by .= ' ELSE '.$counter.' END, a.attnum';
            }

            $query = "SELECT substring((SELECT substring(pg_get_expr(d.adbin, d.adrelid) for 128)
                    	    FROM pg_attrdef d
                    	   WHERE d.adrelid = a.attrelid
                    	     AND d.adnum = a.attnum
                    	     AND a.atthasdef
                    	 ) FROM 'nextval[^'']*''([^'']*)')
                        FROM pg_attribute a
                    LEFT JOIN pg_class c ON c.oid = a.attrelid
                    LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef
                    LEFT JOIN pg_namespace n ON c.relnamespace = n.oid
                       WHERE (c.relname = ".$this->quote($sqn, 'text');
            if (!empty($field)) {
                $query .= " OR (c.relname = ".$this->quote($table, 'text')." AND a.attname = ".$this->quote($field, 'text').")";
            }
            $query .= "      )"
                         .$schema_clause."
                         AND NOT a.attisdropped
                         AND a.attnum > 0
                         AND pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval%'
                    ORDER BY ".$order_by;
            $seqname = $this->queryOne($query);
            if (!PEAR::isError($seqname) && !empty($seqname) && is_string($seqname)) {
                return $seqname;
            }
        }

        return sprintf($this->options['seqname_format'],
            preg_replace('/[^\w\$.]/i', '_', $sqn));
    }

    // }}}
    // {{{ nextID()

    /**
     * Returns the next free id of a sequence
     *
     * @param string $seq_name name of the sequence
     * @param boolean $ondemand when true the sequence is
     *                          automatic created, if it
     *                          not exists
     * @return mixed MDB2 Error Object or id
     * @access public
     */
    function nextID($seq_name, $ondemand = true)
    {
        $sequence_name = $this->quoteIdentifier($this->getSequenceName($seq_name), true);
        $query = "SELECT NEXTVAL('$sequence_name')";
        $this->pushErrorHandling(PEAR_ERROR_RETURN);
        $this->expectError(MDB2_ERROR_NOSUCHTABLE);
        $result = $this->queryOne($query, 'integer');
        $this->popExpect();
        $this->popErrorHandling();
        if (PEAR::isError($result)) {
            if ($ondemand && $result->getCode() == MDB2_ERROR_NOSUCHTABLE) {
                $this->loadModule('Manager', null, true);
                $result = $this->manager->createSequence($seq_name);
                if (PEAR::isError($result)) {
                    return $this->raiseError($result, null, null,
                        'on demand sequence could not be created', __FUNCTION__);
                }
                return $this->nextId($seq_name, false);
            }
        }
        return $result;
    }

    // }}}
    // {{{ lastInsertID()

    /**
     * Returns the autoincrement ID if supported or $id or fetches the current
     * ID in a sequence called: $table.(empty($field) ? '' : '_'.$field)
     *
     * @param string $table name of the table into which a new row was inserted
     * @param string $field name of the field into which a new row was inserted
     * @return mixed MDB2 Error Object or id
     * @access public
     */
    function lastInsertID($table = null, $field = null)
    {
        if (empty($table) && empty($field)) {
            return $this->queryOne('SELECT lastval()', 'integer');
        }
        $seq = $table.(empty($field) ? '' : '_'.$field);
        $sequence_name = $this->quoteIdentifier($this->getSequenceName($seq), true);
        return $this->queryOne("SELECT currval('$sequence_name')", 'integer');
    }

    // }}}
    // {{{ currID()

    /**
     * Returns the current id of a sequence
     *
     * @param string $seq_name name of the sequence
     * @return mixed MDB2 Error Object or id
     * @access public
     */
    function currID($seq_name)
    {
        $sequence_name = $this->quoteIdentifier($this->getSequenceName($seq_name), true);
        return $this->queryOne("SELECT last_value FROM $sequence_name", 'integer');
    }
}

/**
 * MDB2 PostGreSQL result driver
 *
 * @package MDB2
 * @category Database
 * @author  Paul Cooper <[e-mail]>
 */
class MDB2_Result_pgsql extends MDB2_Result_Common
{
    // }}}
    // {{{ fetchRow()

    /**
     * Fetch a row and insert the data into an existing array.
     *
     * @param int       $fetchmode  how the array data should be indexed
     * @param int    $rownum    number of the row where the data can be found