dbdiff.php | searchcode

/php-tool/dbdiff.php

http://chenjin.googlecode.com/
PHP | 399 lines | 303 code | 51 blank | 45 comment | 57 complexity | 364ddd06a069f0a6f640a1cb128fc2f1 MD5 | raw file
Possible License(s): BSD-3-Clause

<?php

/**
 * ?????????
 * 
 * @author ??<54zhua@gmail.com>
 * @copyright www.joy999.com
 * @version v1.0
 */

?><html>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>??????????</title>
<body>
<form method="POST">
<h3>??????????????</h3>
???<input type="text" name="host1" value="<?php echo $_REQUEST['host1']?>"><br>
????<input type="text" name="user1" value="<?php echo $_REQUEST['user1']?>"><br>
???<input type="password" name="pwd1" value="<?php echo $_REQUEST['pwd1']?>"><br>
???<input type="text" name="db1" value="<?php echo $_REQUEST['db1']?>"><br>
<h3>??????????????</h3>
???<input type="text" name="host2" value="<?php echo $_REQUEST['host2']?>"><br>
????<input type="text" name="user2" value="<?php echo $_REQUEST['user2']?>"><br>
???<input type="password" name="pwd2" value="<?php echo $_REQUEST['pwd2']?>"><br>
???<input type="text" name="db2" value="<?php echo $_REQUEST['db2']?>"><br>
<input type="submit" value="??">
<input type="reset" value="??">
<p><input type="checkbox" name="exec" value="1" <?php echo ( $_REQUEST['exec'] == 1 ) ? 'checked' : '' ?> >??SQL????</p>
</form>


<?php
/**
 * ???????
 * 
 */

if ( ! isset( $_REQUEST['host1'] ) || ! isset( $_REQUEST['host2'] ) ) {
	echo '</form></body></html>';
    exit;
}

echo '<hr>';

set_time_limit( 0 );
ignore_user_abort( true );
ob_implicit_flush( true );

$link1 = mysql_connect(  $_REQUEST['host1'] , $_REQUEST['user1'] , $_REQUEST['pwd1'] ) 
			or die( '???1 ?????');
$link2 = mysql_connect( $_REQUEST['host2'] , $_REQUEST['user2'] , $_REQUEST['pwd2'] )
			or die( '???2 ?????');

//mysql_select_db( $_REQUEST['db1'] , $link1 )
//			or die( '???1 ?????');
//mysql_select_db( $_REQUEST['db2'] , $link2 )
//			or die( '???2 ?????');
$ts = array();
$tStruct = array();
$tCreate = array();
$tIndex = array();
foreach( array( $_REQUEST['db1'] => $link1 , $_REQUEST['db2'] => $link2 ) as $db => $link ) { 
	mysql_select_db( $db , $link )
		or die( '????' . $db . '?????');
 
    mysql_query( 'set names "utf8";' , $link )
    	or die( $db . ' set names "utf8" ?????');
    
    //?????
    $m = mysql_query( 'show tables;' , $link )
    	or die ( $db . 'show tables ?????');
    
    
    while( $r = mysql_fetch_array( $m ) ) {
        $tn = $r[0];
        
        $rm = mysql_query( 'show full columns from `' . $tn . '`' , $link );
        while( $v = mysql_fetch_assoc( $rm ) ) {
            $ts[$db][$tn][] = $v['Field'];
            $tStruct[$db][$tn][$v['Field']] = $v;
        }
        if ( $db == $_REQUEST['db1'] ) {
	        $rm = mysql_query( 'show create table `' . $tn . '`' , $link );
	        $v = mysql_fetch_array( $rm );
	        $tCreate[$v[0]] = $v[1] ;
        }
        
        //?????
        $rm = mysql_query( 'SHOW INDEX FROM `' . $tn . '`' , $link );
        $t = array();
        while( $v = mysql_fetch_assoc( $rm ) ) {        	 
        	if ( $v['Key_name'] == 'PRIMARY' ) {
        		//??
        		$t['key'][$v['Key_name']][] = $v['Column_name'];
        		continue;
        	}
        	if ( $v['Non_unique'] == 1 ) {
        		//??
        		if ( $v['Index_type'] == 'FULLTEXT' ) {
        			//????
        			$t['fulltext'][$v['Key_name']][] = $v['Column_name'];
        		} else {
        			//????
        			$t['index'][$v['Key_name']][] = $v['Column_name'];
        		}
        	} else {
        		//??
        		$t['uni'][$v['Key_name']][] = $v['Column_name'];
        	}
        }
//        if ( $tn == 'access_log') {
//        	var_dump( $t );
//        }
        foreach( $t as $type => $ta ) {
	        foreach( $ta as $kn => $ka ) {
	        	sort( $ka );
//	        	var_dump( $ka );
	        	$tmp = implode( '`,`' , $ka );
	        	if ( ! empty( $tmp ) ) {
	        		$tmp = '`' . $tmp . '`' ;
	        	}
	        	$ta[$kn] = $tmp;
	        }
	        $t[$type] = $ta;
        }
        $tIndex[$db][$tn] = $t;
    }
}

$sql = array();
$msg = array();

if ( is_array( $ts[$_REQUEST['db1']] ) ) foreach( $ts[$_REQUEST['db1']] as $tn => $tbs ) {
    if ( ! isset( $ts[$_REQUEST['db2']][$tn] ) ) {
        //????       
        $sql[] = $tCreate[$tn];
    }
    $last = '?';
    foreach( $tbs as $v ) {
        if ( ! isset( $ts[$_REQUEST['db2']][$tn] ) ) {
             $msg[$tn][] = '??????? ' . $v . ' ? ' . $last . ' ??';
             $last = $v;
             continue;
        }
        
        $fieldUpdateMethod = '' ;
        if ( ! in_array( $v , $ts[$_REQUEST['db2']][$tn] ) ) {
            //????
            $msg[$tn][] = '?? ' . $v . ' ? ' . $last . ' ??';
            $fieldUpdateMethod = 'ADD';
        } else {
            //????
            
            if ( $tStruct[$_REQUEST['db2']][$tn][$v] != $tStruct[$_REQUEST['db1']][$tn][$v] ) {
            	
                $msg[$tn][] = '?? ' . $v . ' ? ' . $last . ' ??';
                $fieldUpdateMethod = 'CHANGE `' . $v . '` ' ;
            }
        }
        
        if ( $fieldUpdateMethod ) {
            $T = $tStruct[$_REQUEST['db1']][$tn];
            $tt = $T[$v];
            
            $tSql = 'alter table `' . $tn .'` ' . $fieldUpdateMethod . ' `' . $v .'` ' . $tt['Type'] . ' ';
            if ( $tt['Null'] == 'NO' ) {
                $tSql .= ' NOT NULL ';
            }
            
            $tSql .= ' ' . $tt['Extra'] . ' ';
           
            if ( $tt['Default'] != '' ) {
            	if ( $tt['Default'] == 'CURRENT_TIMESTAMP' ) {
            		$tSql .= ' default CURRENT_TIMESTAMP ';
            	} else {
                	$tSql .= ' default \'' . addslashes( $tt['Default'] ) . '\' ';
            	}
            }
            $tSql .= ' COMMENT \'' . mysql_escape_string( $tt['Comment'] ) .'\' ';
            if ( $last == '?' ) {
                $tSql .= ' FIRST ';
            } else {
                if ( in_array( $last , $ts[$_REQUEST['db2']][$tn] ) ) {
                    $tSql .= ' AFTER `' . $last . '`';
                }
            }
            
            //?????????????????????
            
            if ( ! empty( $tt['Extra'] ) ) {
	            
	            if ( $tt['Key'] == 'PRI' ) {
	                //??,????????????????
	                $tSql .= ' , DROP PRIMARY KEY ';
	                //?????????????
	                $tSql .= ' , ADD PRIMARY KEY ( ';
	                foreach( $T as $tName => $vv ) {
	                    $priKey = array();
	                    if ( $vv['Key'] == 'PRI' ) {
	                        $priKey[] = '`' . $tName . '`';
	                    }
	                    $tSql .= implode( ',' , $priKey );
	                }
	                $tSql .= ' ) ';
	            }
	            
	            
	            if ( $tt['Key'] == 'MUL' ) {
	                //??
	                $tSql .= ' , ADD INDEX ( ' . $v . ' )';
	            }
	            
	            if ( $tt['Key'] == 'UNI' ) {
	                //??
	                $tSql .= ' , ADD UNIQUE ( ' . $v . ' )';
	            }
            }
            $sql[] = $tSql;
        }
        $last = $v;
    }
   
}

if ( is_array( $ts[$_REQUEST['db2']] ) ) foreach( $ts[$_REQUEST['db2']] as $tn => $tbs ) {    
    foreach( $tbs as $v ) {
        
        if ( ! in_array( $v , $ts[$_REQUEST['db1']][$tn] ) ) {
            $msg[$tn][] = '?? ' . $v;
            $sql[] = 'alter table `' . $tn . '` drop column `' . $v . '`';      
        }
    } 
}

$typeMap = array(
	'key' => '??' ,
	'index' => '??' ,
	'uni' => '??' ,
	'fulltext' => '????' ,
);

//??????????
echo '<hr><h2>????????</h2>';
$indexMsg = array();
foreach( $tIndex[$_REQUEST['db2']] as $tableName => $indexs ) {	
	$str = '';
	foreach( $indexs as $type => $arr ) {
		foreach( $arr as $iName => $iVal ) {
			if ( isset( $tIndex[$_REQUEST['db1']][$tableName][$type][$iName] ) ) {
				$srcVal = $tIndex[$_REQUEST['db1']][$tableName][$type][$iName];
				if ( $srcVal == $iVal ) {
					//?????
					continue;
				} else {
					//????????
					$msg[$tableName][] = '<li>' . $typeMap[$type] .' [' . $iName .' : ' . $iVal . '] ??';
					
					//???????
					switch( $type ) {
						case 'key' :
							$sql[] = 'ALTER TABLE `'. $tableName . '` DROP PRIMARY KEY ';
							$sql[] = 'ALTER TABLE `' . $tableName . '` ADD PRIMARY KEY ( ' . $srcVal . ' )';
							break;
						case 'index' :
							$sql[] = 'ALTER TABLE `'. $tableName . '` DROP INDEX `' . $iName .'`';
							$sql[] = 'ALTER TABLE `' . $tableName . '` ADD INDEX `' . $iName .'` ( ' . $srcVal . ' )';
							break;
						case 'uni' :
							$sql[] = 'ALTER TABLE `'. $tableName . '` DROP INDEX `' . $iName .'`';
							$sql[] = 'ALTER TABLE `' . $tableName . '` ADD UNIQUE `' . $iName .'` ( ' . $srcVal . ' )';
							break;
						case 'fulltext' :
							$sql[] = 'ALTER TABLE `'. $tableName . '` DROP INDEX `' . $iName .'`';
							$sql[] = 'ALTER TABLE `' . $tableName . '` ADD FULLTEXT `' . $iName .'` ( ' . $srcVal . ' )';
							break;
					}
					
				}
			} else {
				//????????
				$msg[$tableName][] = '<li>'. $typeMap[$type] .' [' . $iName .' : ' . $iVal . '] ????';
				switch( $type ) {
					case 'key' :
						$sql[] = 'ALTER TABLE `'. $tableName . '` DROP PRIMARY KEY ';						
						break;
					case 'index' :
					case 'uni' :
					case 'fulltext' :
						$sql[] = 'ALTER TABLE `'. $tableName . '` DROP INDEX `' . $iName .'`';						
						break;					
				}
			}
		}
	}
	
}

foreach( $tIndex[$_REQUEST['db1']] as $tableName => $indexs ) {		
	foreach( $indexs as $type => $arr ) {
		foreach( $arr as $iName => $iVal ) {
			if ( isset( $tIndex[$_REQUEST['db2']][$tableName][$type][$iName] ) ) {
				
			} else {
				//??????????
				$msg[$tableName][] = '<li>'. $typeMap[$type] .' [' . $iName .' : ' . $iVal . '] ????';
				switch( $type ) {
					case 'key' :						
						$sql[] = 'ALTER TABLE `' . $tableName . '` ADD PRIMARY KEY ( ' . $iVal . ' )';
						break;
					case 'index' :
						$sql[] = 'ALTER TABLE `' . $tableName . '` ADD INDEX `' . $iName .'` ( ' . $iVal . ' )';
						break;
					case 'uni' :
						$sql[] = 'ALTER TABLE `' . $tableName . '` ADD UNIQUE `' . $iName .'` ( ' . $iVal . ' )';
						break;
					case 'fulltext' :
						$sql[] = 'ALTER TABLE `' . $tableName . '` ADD FULLTEXT `' . $iName .'` ( ' . $iVal . ' )';
						break;
				}
			}
		}
	}
	
}


//??????


foreach( $msg as $tn => $arr ) {
    echo '<hr><p>???' . $tn . '</p><ul>';
    if ( !empty( $arr ) ) foreach( $arr as $v ) {
        echo '<li>' . $v . '</li>';
    } else {
        echo '<li>????</li>';
    }
    echo '</ul>';
}

if ( ! empty( $sql ) ) {

echo '<textarea style="width:100%;height:500px">';

$result = '';
foreach( $sql as $s ) {
    echo htmlspecialchars( $s . ";\n\n\n" );
//    if ( $_REQUEST['exec'] == 1 ) {
//    	echo '<p><b>???</b>' . $s . ' ' .  ( mysql_query( $s , $link2 ) ? '<font color=green>??</font>' : '<font color=red>??</font>' );
//    	$result .= "</p>\r\n";
//    }
}
echo '</textarea>';
if ( $_REQUEST['exec'] == 1 ) {
	$totalSqls = count( $sql );
	foreach( $sql as $i => $s ) {
		echo '===========================????' . ( $i + 1 ) . ' / ' . $totalSqls .'=================================<br />';
    	echo '<p><b>???</b>' . $s . ' ' .  ( mysql_query( $s , $link2 ) ? '<font color=green>??</font>' : '<font color=red>??</font>' );
    	echo "</p>\r\n";
    }
}

?>
<table><tr><td>
<form method="POST">
<input type="hidden" name="exec" value="1">
<input type="hidden" name="host1" value="<?php echo $_REQUEST['host1']?>"><br>
<input type="hidden" name="user1" value="<?php echo $_REQUEST['user1']?>"><br>
<input type="hidden" name="pwd1" value="<?php echo $_REQUEST['pwd1']?>"><br>
<input type="hidden" name="db1" value="<?php echo $_REQUEST['db1']?>"><br>
<input type="hidden" name="host2" value="<?php echo $_REQUEST['host2']?>"><br>
<input type="hidden" name="user2" value="<?php echo $_REQUEST['user2']?>"><br>
<input type="hidden" name="pwd2" value="<?php echo $_REQUEST['pwd2']?>"><br>
<input type="hidden" name="db2" value="<?php echo $_REQUEST['db2']?>"><br>
<input type="submit" value="??SQL???????">
</form>
</td><td>
<form method="POST">
<input type="hidden" name="host1" value="<?php echo $_REQUEST['host1']?>"><br>
<input type="hidden" name="user1" value="<?php echo $_REQUEST['user1']?>"><br>
<input type="hidden" name="pwd1" value="<?php echo $_REQUEST['pwd1']?>"><br>
<input type="hidden" name="db1" value="<?php echo $_REQUEST['db1']?>"><br>
<input type="hidden" name="host2" value="<?php echo $_REQUEST['host2']?>"><br>
<input type="hidden" name="user2" value="<?php echo $_REQUEST['user2']?>"><br>
<input type="hidden" name="pwd2" value="<?php echo $_REQUEST['pwd2']?>"><br>
<input type="hidden" name="db2" value="<?php echo $_REQUEST['db2']?>"><br>
<input type="submit" value="????????SQL?">
</form>
</td></tr></table>
<?php 
} else {
	echo '<font color=green>??????????</font>';
}

mysql_close( $link1 );
mysql_close( $link2 );

?>
</body>
</html>