mysql.php

开源邮件管理系统

                    'insert'    => $fkname.'_insert_trg',
                    'update'    => $fkname.'_update_trg',
                    'pk_update' => $fkname.'_pk_update_trg',
                    'pk_delete' => $fkname.'_pk_delete_trg',
                );
                $table_fields = array_keys($fkdef['fields']);
                $referenced_fields = array_keys($fkdef['references']['fields']);

                //create the ON [UPDATE|DELETE] triggers on the primary table
                $restrict_action = ' IF (SELECT ';
                $aliased_fields = array();
                foreach ($table_fields as $field) {
                    $aliased_fields[] = $table .'.'.$field .' AS '.$field;
                }
                $restrict_action .= implode(',', $aliased_fields)
                       .' FROM '.$table
                       .' WHERE ';
                $conditions  = array();
                $new_values  = array();
                $null_values = array();
                for ($i=0; $i<count($table_fields); $i++) {
                    $conditions[]  = $table_fields[$i] .' = OLD.'.$referenced_fields[$i];
                    $new_values[]  = $table_fields[$i] .' = NEW.'.$referenced_fields[$i];
                    $null_values[] = $table_fields[$i] .' = NULL';
                }
                $restrict_action .= implode(' AND ', $conditions).') IS NOT NULL'
                                .' THEN CALL %s_ON_TABLE_'.$table.'_VIOLATES_FOREIGN_KEY_CONSTRAINT();'
                                .' END IF;';

                $cascade_action_update = 'UPDATE '.$table.' SET '.implode(', ', $new_values) .' WHERE '.implode(' AND ', $conditions). ';';
                $cascade_action_delete = 'DELETE FROM '.$table.' WHERE '.implode(' AND ', $conditions). ';';
                $setnull_action        = 'UPDATE '.$table.' SET '.implode(', ', $null_values).' WHERE '.implode(' AND ', $conditions). ';';

                if ('SET DEFAULT' == $fkdef['onupdate'] || 'SET DEFAULT' == $fkdef['ondelete']) {
                    $db->loadModule('Reverse', null, true);
                    $default_values = array();
                    foreach ($table_fields as $table_field) {
                        $field_definition = $db->reverse->getTableFieldDefinition($table, $field);
                        if (PEAR::isError($field_definition)) {
                            return $field_definition;
                        }
                        $default_values[] = $table_field .' = '. $field_definition[0]['default'];
                    }
                    $setdefault_action = 'UPDATE '.$table.' SET '.implode(', ', $default_values).' WHERE '.implode(' AND ', $conditions). ';';
                }

                $query = 'CREATE TRIGGER %s'
                        .' %s ON '.$fkdef['references']['table']
                        .' FOR EACH ROW BEGIN '
                        .' SET FOREIGN_KEY_CHECKS = 0; ';  //only really needed for ON UPDATE CASCADE

                if ('CASCADE' == $fkdef['onupdate']) {
                    $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE',  'update') . $cascade_action_update;
                } elseif ('SET NULL' == $fkdef['onupdate']) {
                    $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setnull_action;
                } elseif ('SET DEFAULT' == $fkdef['onupdate']) {
                    $sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setdefault_action;
                } elseif ('NO ACTION' == $fkdef['onupdate']) {
                    $sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'AFTER UPDATE', 'update');
                } else {
                    //'RESTRICT'
                    $sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update');
                }
                if ('CASCADE' == $fkdef['ondelete']) {
                    $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE',  'delete') . $cascade_action_delete;
                } elseif ('SET NULL' == $fkdef['ondelete']) {
                    $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setnull_action;
                } elseif ('SET DEFAULT' == $fkdef['ondelete']) {
                    $sql_delete = sprintf($query, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete') . $setdefault_action;
                } elseif ('NO ACTION' == $fkdef['ondelete']) {
                    $sql_delete = sprintf($query.$restrict_action, $trigger_names['pk_delete'], 'AFTER DELETE', 'delete');
                } else {
                    //'RESTRICT'
                    $sql_delete = sprintf($query.$restrict_action, $trigger_names['pk_delete'], 'BEFORE DELETE', 'delete');
                }
                $sql_update .= ' SET FOREIGN_KEY_CHECKS = 1; END;';
                $sql_delete .= ' SET FOREIGN_KEY_CHECKS = 1; END;';

                $db->pushErrorHandling(PEAR_ERROR_RETURN);
                $db->expectError(MDB2_ERROR_CANNOT_CREATE);
                $result = $db->exec($sql_delete);
                $expected_errmsg = 'This MySQL version doesn\'t support multiple triggers with the same action time and event for one table';
                $db->popExpect();
                $db->popErrorHandling();
                if (PEAR::isError($result)) {
                    if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
                        return $result;
                    }
                    $db->warnings[] = $expected_errmsg;
                }
                $db->pushErrorHandling(PEAR_ERROR_RETURN);
                $db->expectError(MDB2_ERROR_CANNOT_CREATE);
                $result = $db->exec($sql_update);
                $db->popExpect();
                $db->popErrorHandling();
                if (PEAR::isError($result) && $result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
                    if ($result->getCode() != MDB2_ERROR_CANNOT_CREATE) {
                        return $result;
                    }
                    $db->warnings[] = $expected_errmsg;
                }
            }
        }
        return MDB2_OK;
    }

    // }}}
    // {{{ _dropFKTriggers()

    /**
     * Drop the triggers created to enforce the FOREIGN KEY constraint on the table
     *
     * @param string $table            table name
     * @param string $fkname           FOREIGN KEY constraint name
     * @param string $referenced_table referenced table name
     *
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access private
     */
    function _dropFKTriggers($table, $fkname, $referenced_table)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $triggers  = $this->listTableTriggers($table);
        $triggers2 = $this->listTableTriggers($referenced_table);
        if (!PEAR::isError($triggers2) && !PEAR::isError($triggers)) {
            $triggers = array_merge($triggers, $triggers2);
            $pattern = '/^'.$fkname.'(_pk)?_(insert|update|delete)_trg$/i';
            foreach ($triggers as $trigger) {
                if (preg_match($pattern, $trigger)) {
                    $result = $db->exec('DROP TRIGGER '.$trigger);
                    if (PEAR::isError($result)) {
                        return $result;
                    }
                }
            }
        }
        return MDB2_OK;
    }

    // }}}
    // {{{ listTableConstraints()

    /**
     * list all constraints in a table
     *
     * @param string $table name of table that should be used in method
     * @return mixed array of constraint names on success, a MDB2 error on failure
     * @access public
     */
    function listTableConstraints($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);
            }
        }

        $query = 'SHOW INDEX FROM ' . $db->quoteIdentifier($table, true);
        $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]) {
                if ($index_data[$key_name] !== 'PRIMARY') {
                    $index = $this->_fixIndexName($index_data[$key_name]);
                } else {
                    $index = 'PRIMARY';
                }
                if (!empty($index)) {
                    $result[$index] = true;
                }
            }
        }
        
        //list FOREIGN KEY constraints...
        $query = 'SHOW CREATE TABLE '. $db->escape($table);
        $definition = $db->queryOne($query, 'text', 1);
        if (!PEAR::isError($definition) && !empty($definition)) {
            $pattern = '/\bCONSTRAINT\b\s+([^\s]+)\s+\bFOREIGN KEY\b/Uims';
            if (preg_match_all($pattern, str_replace('`', '', $definition), $matches) > 0) {
                foreach ($matches[1] as $constraint) {
                    $result[$constraint] = true;
                }
            }
        }

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

    // }}}
    // {{{ createSequence()

    /**
     * create sequence
     *
     * @param string    $seq_name name of the sequence to be created
     * @param string    $start    start value of the sequence; default is 1
     * @param array     $options  An associative array of table options:
     *                          array(
     *                              'comment' => 'Foo',
     *                              'charset' => 'utf8',
     *                              'collate' => 'utf8_unicode_ci',
     *                              'type'    => 'innodb',
     *                          );
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function createSequence($seq_name, $start = 1, $options = array())
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        $seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);
        
        $options_strings = array();

        if (!empty($options['comment'])) {
            $options_strings['comment'] = 'COMMENT = '.$db->quote($options['comment'], 'text');
        }

        if (!empty($options['charset'])) {
            $options_strings['charset'] = 'DEFAULT CHARACTER SET '.$options['charset'];
            if (!empty($options['collate'])) {
                $options_strings['charset'].= ' COLLATE '.$options['collate'];
            }
        }

        $type = false;
        if (!empty($options['type'])) {
            $type = $options['type'];
        } elseif ($db->options['default_table_type']) {
            $type = $db->options['default_table_type'];
        }
        if ($type) {
            $options_strings[] = "ENGINE = $type";
        }

        $query = "CREATE TABLE $sequence_name ($seqcol_name INT NOT NULL AUTO_INCREMENT, PRIMARY KEY ($seqcol_name))";
        if (!empty($options_strings)) {
            $query .= ' '.implode(' ', $options_strings);
        }
        $res = $db->exec($query);
        if (PEAR::isError($res)) {
            return $res;
        }

        if ($start == 1) {
            return MDB2_OK;
        }

        $query = "INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')';
        $res = $db->exec($query);
        if (!PEAR::isError($res)) {
            return MDB2_OK;
        }

        // Handle error
        $result = $db->exec("DROP TABLE $sequence_name");
        if (PEAR::isError($result)) {
            return $db->raiseError($result, null, null,
                'could not drop inconsistent sequence table', __FUNCTION__);
        }

        return $db->raiseError($res, null, null,
            'could not create sequence table', __FUNCTION__);
    }

    // }}}
    // {{{ dropSequence()

    /**
     * drop existing sequence
     *
     * @param string    $seq_name     name of the sequence to be dropped
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function dropSequence($seq_name)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        return $db->exec("DROP TABLE $sequence_name");
    }

    // }}}
    // {{{ listSequences()

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

        $query = "SHOW TABLES";
        if (!is_null($database)) {
            $query .= " FROM $database";
        }
        $table_names = $db->queryCol($query);
        if (PEAR::isError($table_names)) {
            return $table_names;
        }

        $result = array();
        foreach ($table_names as $table_name) {
            if ($sqn = $this->_fixSequenceName($table_name, true)) {
                $result[] = $sqn;
            }
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

    // }}}
}
?>