mysql.php

开源邮件管理系统

            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

    // }}}
    // {{{ listViews()

    /**
     * list all views in the current database
     *
     * @param string database, the current is default
     * @return mixed array of view names on success, a MDB2 error on failure
     * @access public
     */
    function listViews($database = null)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $query = 'SHOW FULL TABLES';
        if (!is_null($database)) {
            $query.= " FROM $database";
        }
        $query.= " WHERE Table_type = 'VIEW'";

        $result = $db->queryCol($query);
        if (PEAR::isError($result)) {
            return $result;
        }

        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

    // }}}
    // {{{ listTableFields()

    /**
     * list all fields in a table in the current database
     *
     * @param string $table name of table that should be used in method
     * @return mixed array of field names on success, a MDB2 error on failure
     * @access public
     */
    function listTableFields($table)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $table = $db->quoteIdentifier($table, true);
        $result = $db->queryCol("SHOW COLUMNS FROM $table");
        if (PEAR::isError($result)) {
            return $result;
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

    // }}}
    // {{{ createIndex()

    /**
     * Get the stucture of a field into an array
     *
     * @author Leoncx
     * @param string $table      name of the table on which the index is to be created
     * @param string $name       name of the index to be created
     * @param array  $definition associative array that defines properties of the index to be created.
     *                           Currently, only one property named FIELDS is supported. This property
     *                           is also an associative with the names of the index fields as array
     *                           indexes. Each entry of this array is set to another type of associative
     *                           array that specifies properties of the index that are specific to
     *                           each field.
     *
     *                           Currently, only the sorting property is supported. It should be used
     *                           to define the sorting direction of the index. It may be set to either
     *                           ascending or descending.
     *
     *                           Not all DBMS support index sorting direction configuration. The DBMS
     *                           drivers of those that do not support it ignore this property. Use the
     *                           function supports() to determine whether the DBMS driver can manage indexes.
     *
     *                           Example
     *                               array(
     *                                   'fields' => array(
     *                                       'user_name' => array(
     *                                           'sorting' => 'ascending'
     *                                           'length' => 10
     *                                       ),
     *                                       'last_login' => array()
     *                                    )
     *                                )
     *
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function createIndex($table, $name, $definition)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $table = $db->quoteIdentifier($table, true);
        $name = $db->quoteIdentifier($db->getIndexName($name), true);
        $query = "CREATE INDEX $name ON $table";
        $fields = array();
        foreach ($definition['fields'] as $field => $fieldinfo) {
            if (!empty($fieldinfo['length'])) {
                $fields[] = $db->quoteIdentifier($field, true) . '(' . $fieldinfo['length'] . ')';
            } else {
                $fields[] = $db->quoteIdentifier($field, true);
            }
        }
        $query .= ' ('. implode(', ', $fields) . ')';
        return $db->exec($query);
    }

    // }}}
    // {{{ dropIndex()

    /**
     * drop existing index
     *
     * @param string    $table         name of table that should be used in method
     * @param string    $name         name of the index to be dropped
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function dropIndex($table, $name)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $table = $db->quoteIdentifier($table, true);
        $name = $db->quoteIdentifier($db->getIndexName($name), true);
        return $db->exec("DROP INDEX $name ON $table");
    }

    // }}}
    // {{{ listTableIndexes()

    /**
     * list all indexes in a table
     *
     * @param string $table name of table that should be used in method
     * @return mixed array of index names on success, a MDB2 error on failure
     * @access public
     */
    function listTableIndexes($table)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $key_name = 'Key_name';
        $non_unique = 'Non_unique';
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            if ($db->options['field_case'] == CASE_LOWER) {
                $key_name = strtolower($key_name);
                $non_unique = strtolower($non_unique);
            } else {
                $key_name = strtoupper($key_name);
                $non_unique = strtoupper($non_unique);
            }
        }

        $table = $db->quoteIdentifier($table, true);
        $query = "SHOW INDEX FROM $table";
        $indexes = $db->queryAll($query, null, MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($indexes)) {
            return $indexes;
        }

        $result = array();
        foreach ($indexes as $index_data) {
            if ($index_data[$non_unique] && ($index = $this->_fixIndexName($index_data[$key_name]))) {
                $result[$index] = true;
            }
        }

        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }
        return array_keys($result);
    }

    // }}}
    // {{{ createConstraint()

    /**
     * create a constraint on a table
     *
     * @param string    $table        name of the table on which the constraint is to be created
     * @param string    $name         name of the constraint to be created
     * @param array     $definition   associative array that defines properties of the constraint to be created.
     *                                Currently, only one property named FIELDS is supported. This property
     *                                is also an associative with the names of the constraint fields as array
     *                                constraints. Each entry of this array is set to another type of associative
     *                                array that specifies properties of the constraint that are specific to
     *                                each field.
     *
     *                                Example
     *                                   array(
     *                                       'fields' => array(
     *                                           'user_name' => array(),
     *                                           'last_login' => array()
     *                                       )
     *                                   )
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function createConstraint($table, $name, $definition)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $type = '';
        $name = $db->quoteIdentifier($db->getIndexName($name), true);
        if (!empty($definition['primary'])) {
            $type = 'PRIMARY';
            $name = 'KEY';
        } elseif (!empty($definition['unique'])) {
            $type = 'UNIQUE';
        } elseif (!empty($definition['foreign'])) {
            $type = 'CONSTRAINT';
        }
        if (empty($type)) {
            return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
                'invalid definition, could not create constraint', __FUNCTION__);
        }

        $table_quoted = $db->quoteIdentifier($table, true);
        $query = "ALTER TABLE $table_quoted ADD $type $name";
        if (!empty($definition['foreign'])) {
            $query .= ' FOREIGN KEY';
        }
        $fields = array();
        foreach (array_keys($definition['fields']) as $field) {
            $fields[] = $db->quoteIdentifier($field, true);
        }
        $query .= ' ('. implode(', ', $fields) . ')';
        if (!empty($definition['foreign'])) {
            $query.= ' REFERENCES ' . $db->quoteIdentifier($definition['references']['table'], true);
            $referenced_fields = array();
            foreach (array_keys($definition['references']['fields']) as $field) {
                $referenced_fields[] = $db->quoteIdentifier($field, true);
            }
            $query .= ' ('. implode(', ', $referenced_fields) . ')';
            $query .= $this->_getAdvancedFKOptions($definition);
        }
        $res = $db->exec($query);
        if (PEAR::isError($res)) {
            return $res;
        }
        if (!empty($definition['foreign'])) {
            return $this->_createFKTriggers($table, array($name => $definition));
        }
        return MDB2_OK;
    }

    // }}}
    // {{{ dropConstraint()

    /**
     * drop existing constraint
     *
     * @param string    $table        name of table that should be used in method
     * @param string    $name         name of the constraint to be dropped
     * @param string    $primary      hint if the constraint is primary
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function dropConstraint($table, $name, $primary = false)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        if ($primary || strtolower($name) == 'primary') {
            $query = 'ALTER TABLE '. $db->quoteIdentifier($table, true) .' DROP PRIMARY KEY';
            return $db->exec($query);
        }

        //is it a FK constraint? If so, also delete the associated triggers
        $db->loadModule('Reverse', null, true);
        $definition = $db->reverse->getTableConstraintDefinition($table, $name);
        if (!PEAR::isError($definition) && !empty($definition['foreign'])) {
            //first drop the FK enforcing triggers
            $result = $this->_dropFKTriggers($table, $name, $definition['references']['table']);
            if (PEAR::isError($result)) {
                return $result;
            }
            //then drop the constraint itself
            $table = $db->quoteIdentifier($table, true);
            $name = $db->quoteIdentifier($db->getIndexName($name), true);
            $query = "ALTER TABLE $table DROP FOREIGN KEY $name";
            return $db->exec($query);
        }

        $table = $db->quoteIdentifier($table, true);
        $name = $db->quoteIdentifier($db->getIndexName($name), true);
        $query = "ALTER TABLE $table DROP INDEX $name";
        return $db->exec($query);
    }

    // }}}
    // {{{ _createFKTriggers()

    /**
     * Create triggers to enforce the FOREIGN KEY constraint on the table
     *
     * NB: since there's no RAISE_APPLICATION_ERROR facility in mysql,
     * we call a non-existent procedure to raise the FK violation message.
     * @see http://forums.mysql.com/read.php?99,55108,71877#msg-71877
     *
     * @param string $table        table name
     * @param array  $foreign_keys FOREIGN KEY definitions
     *
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access private
     */
    function _createFKTriggers($table, $foreign_keys)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }
        // create triggers to enforce FOREIGN KEY constraints
        if ($db->supports('triggers') && !empty($foreign_keys)) {
            $table = $db->quoteIdentifier($table, true);
            foreach ($foreign_keys as $fkname => $fkdef) {
                if (empty($fkdef)) {
                    continue;
                }
                //set actions to 'RESTRICT' if not set
                $fkdef['onupdate'] = empty($fkdef['onupdate']) ? 'RESTRICT' : strtoupper($fkdef['onupdate']);
                $fkdef['ondelete'] = empty($fkdef['ondelete']) ? 'RESTRICT' : strtoupper($fkdef['ondelete']);

                $trigger_names = array(