to1.0.php

一款基于PHP的网络日记程序。WikyBlog支持：多用户的 BLOG

<?php

//	Can I just update the database?
//		..no! because the primary key for `pages` won't be right .. we'll get a duplicate entry for `file_id`
//



class to10 extends fromSkeleton{
	var $MySQL41;

	function to10($updateObj=null,$versionNum=null){
		$this->updateObj = $updateObj;
		$this->versionNum = $versionNum;
	}

	
	function go($MySQL41){
		$this->MySQL41 = $MySQL41;
		
		$_GET += array('cmd'=>'');
		switch( $_GET['cmd']){
			case 'start':
				$this->alterUserTable();
				message('<a href="?cmd=second">Second</a>');
			break;
			
			case 'second':			
				$this->addOwnerIdColumn();
				message('<a href="?cmd=third">Third</a>');
			break;
			
			case 'third':
				$this->recreateTables();
				$this->done = true;
			break;

			default:
				message('<a href="?cmd=start">Start</a>');
			break;
		}
	}

	
	
	function alterUserTable(){
		global $wbTables;
		
		//change primary to unique
		$query = 'ALTER TABLE '.$wbTables['users'].' ';
		$query .= ' DROP PRIMARY KEY, ';
		$query .= ' ADD UNIQUE( `username` )';
		$result = wbDB::runQuery($query);
		
		//add user_id
		$query = 'ALTER TABLE '.$wbTables['users'].' ';
		$query .= ' ADD `user_id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST';
		$result = wbDB::runQuery($query);
	}
	
	function addOwnerIdColumn(){
		global $dbInfo,$wbTables;
		
		$query = 'CREATE TABLE ';
		$query .= $wbTables['all_files'];
		$query .= ' ( 	`uniqlink` VARCHAR(255) NOT NULL,
						`owner_id` INT(20) NOT NULL default \'\',
						`modified` timestamp NOT NULL,
						`posted` timestamp NOT NULL default \'0000-00-00 00:00:00\',
						`created` timestamp NOT NULL default \'0000-00-00 00:00:00\',
						`keywords` varchar(200) default NULL,
						`flags` set(\'locked\',\'hidden\',\'restricted\',\'nocomments\',\'registered\',\'deleted\',\'safe\',\'admin\') NOT NULL default \'\',
						`username` varchar(20) default NULL,
						`ip` varchar(15) NOT NULL default \'\',
						UNIQUE KEY `uniqlink` (`uniqlink`) )';
						//, `keywords`, `username`, `ip`
						
		if( $this->MySQL41 ){
			$query .= ' DEFAULT CHARSET=utf8';
		}
		$result = wbDB::runQuery($query);
				
		foreach($dbInfo as $space => $info){
			if( !isset($info['dbTable']) ){
				continue;
			}
			$users = $wbTables['users'];
			$selectA = array('modified','posted','created','keywords','flags','username','ip');
			$select = ' ';
			foreach($selectA as $field){
				$select .= ', '.$info['dbTable'].'.`'.$field.'`';
			}
			$query = ' INSERT INTO '.$wbTables['all_files'];
			$query .= ' SELECT '.wbData::dbInfo($space,'uniqLink').', `user_id` '.$select;
			$query .= ' FROM '.$info['dbTable'].' LEFT JOIN '.$users.' on '.$users.'.`username` = '.$info['dbTable'].'.owner';
						// `owner`, `modified`, `posted`, 
						//, `keywords`, `username`, `ip` 
			
			if(! wbDB::runQuery($query) ){
				echo mysql_error();
			}
			
		}
		
		//Reorder all_files according to `created`
		$query = 'ALTER TABLE '.$wbTables['all_files'];
		$query .= ' ORDER BY `created`';
		$result = wbDB::runQuery($query);
		
		//Add the auto_increment column
		$query = 'ALTER TABLE '.$wbTables['all_files'];
		$query .= ' ADD `file_id` INT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST';
		$result = wbDB::runQuery($query);
		
	}
	
	function recreateTables(){
		global $dbInfo,$wbTables;


		//DO A 
		//		SHOW CREATE TABLE
		//		ALTER TABLE `pages` RENAME `wikyblog_temp` ;
		//		CREATE TABLE..
		//		ALTER TABLE .. ADD `file_id`
		//		insert .. select
		
		$i = 1;
		//Copy the `file_id` back to each table
		foreach($dbInfo as $space => $info){
			if( !isset($info['dbTable']) ){
				continue;
			}
			//A
			$query = 'SHOW CREATE TABLE '.$info['dbTable'];
			$result = wbDB::runQuery($query);
			$row = mysql_fetch_assoc($result);
			$createTable = $row['Create Table'];
			
			//B
			$query = 'ALTER TABLE '.$info['dbTable'].' RENAME `wikyblog_temp` ';
			wbDB::runQuery($query);
			
			//C
			wbDB::runQuery($createTable);
			
			//D
			$query = 'ALTER TABLE '.$info['dbTable'];
			$query .= ' ADD `file_id` INT( 8 ) UNSIGNED NOT NULL FIRST, ADD INDEX ( `file_id` )'; //can't be unique yet
			wbDB::runQuery($query);
			
			//E
			//	insert ... select
			//
			$query = '';
			$query .= 'INSERT INTO '.$info['dbTable'];
			$query .= ' SELECT '.$wbTables['all_files'].'.`file_id`, '.$info['dbTable'].'.* FROM `wikyblog_temp` as '.$info['dbTable'].' LEFT JOIN '.$wbTables['all_files'];
			$query .= ' FORCE KEY(`uniqlink`) on '.$wbTables['all_files'].'.`uniqlink` = '.wbData::dbInfo($space,'uniqLink');
			
			//debugQuery($query);
			$result = wbDB::runQuery($query);
			if( !$result ){
				echo mysql_error();
			}
			
			//F
			$query = 'ALTER TABLE '.$info['dbTable'];
			$query .= ' DROP INDEX `file_id` ,ADD UNIQUE `file_id` ( `file_id` ) ';
			wbDB::runQuery($query);

			
			//G
			$query = 'DROP TABLE `wikyblog_temp`';
			wbDB::runQuery($query);
			//$i++;
		}


	}
	
}

function debugQuery($query){
	($result = mysql_query('EXPLAIN '. $query )) or trigger_error('EXPLAIN '.$query);
	
	$t = '<table class="tableRows" cellspacing="9" width="100%" style="background-color:white;">';
	
	$i = 0;
	while( $line = mysql_fetch_assoc($result) ){
		$t .= "\n";
		if( $i === 0){
			$t .= '<tr>';
			foreach ($line as $key => $value) {
				$t .= '<th>'.toDisplay($key).'</th>';
			}
			$t .= '</tr>';
			$i++;
		}
		
		
		$t .= '<tr>';
		foreach ($line as $value) {
			$t .= '<td style="white-space:nowrap;">'.$value.'</td>';
		}
		$t .= '</tr>';
	}
	
	$t .= '<tr><td colspan="10" style="padding:2em 0 2em 0;">';
	$t .= nl2br($query);
	//$t .= str_replace('(','(<br>&nbsp;',$query);
	$t .= '</td></tr>';
	$t .= '</table>';
	$t .= "\n";
	echo $t;
	
}