db.php

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

<?php
defined('WikyBlog') or die("Not an entry point...");


global $page,$jsNum,$dbObject,$pageOwner;
$dbObject->links['Database'] = '/Admin/'.$pageOwner['username'].'/Database';
$page->scripts[] = '/include/'.$jsNum.'/rows.js';


if( $_SESSION['userlevel'] !== 4){
	$page->contentA['Admin Only'] = 'You must be an administrator to access this page.';
	return;
}


switch( $page->userCmd ){
	
	case 'repair':
		repairTable();
	break;
	
	case 'analyze':
		analyzeTable();
	break;
	
	case 'optimize':
		optimizeTable();
	break;
	
	case 'check':
		checkTable();
	break;
	
	default:
		getTableStatus();
	break;
	
	
}


function getTableNames(){
	global $dbInfo,$page;
	if( isset($_POST['list']) && is_array($_POST['list']) ){
		foreach( $_POST['list'] as $table){
			$newname = checkTableName($table);
			if( $newname ){
				$name[] = $newname;
			}
		}
	}
	
	if( !empty($name) ){
		$name = array_diff($name,array(''));
		return implode(' ,',$name);
	}
	message('Please select table(s).');
	return false;
}

function checkTableName($name,$warn=true){
	global $dbInfo,$wbTables;
	static $tables = array();
	
	if( empty($tables) ){
		
		foreach($wbTables as $table){
			$tables[trim($table)] = true;
		}
		
		foreach($dbInfo as $info){
			if( !isset($info['dbTable']) ){
				continue;
			}
			$tables[$info['dbTable']] = true;
		}
	}

	if( isset($tables[$name]) ){
		return '`'.$name.'`';
	}
	if( isset($tables['`'.$name.'`']) ){
		return '`'.$name.'`';
	}
	
	
	if( $warn ){
		trigger_error($name.' is not in the package list of tables.');
	}
	return false;
}



function analyzeTable(){
	global $page;
	if( !($tables = getTableNames()) ){
		return;
	}
	$t =& $page->contentA['Analyze'];
	$query = 'ANALYZE TABLE '.$tables;
	$result = wbDB::runQuery($query);
	$t .= '<table class="tableRows">';
	$i = false;
	while( $row = mysql_fetch_assoc($result) ){
		if( !$i){
			$t .= resultHeading($row);
			$i = true;
		}
		$t .= resultRow($row);
	}
	$t .= '</table>';
	$t .= '<p><b>Query:</b> '.$query.'</p>';
}

function optimizeTable(){
	global $page;
	if( !($tables = getTableNames()) ){
		return;
	}
	$t =& $page->contentA['Optimize'];
	$query = 'OPTIMIZE TABLE '.$tables;
	$result = wbDB::runQuery($query);
	$t .= '<table class="tableRows">';
	$i = false;
	while( $row = mysql_fetch_assoc($result) ){
		if( !$i){
			$t .= resultHeading($row);
			$i = true;
		}
		$t .= resultRow($row);
	}
	$t .= '</table>';
	$t .= '<p><b>Query:</b> '.$query.'</p>';	
}	
function repairTable(){
	global $page;
	if( !($tables = getTableNames()) ){
		return;
	}
	$t =& $page->contentA['Repair'];
	$query = 'REPAIR TABLE '.$tables;
	$result = wbDB::runQuery($query);
	$t .= '<table class="tableRows">';
	$i = false;
	while( $row = mysql_fetch_assoc($result) ){
		if( !$i){
			$t .= resultHeading($row);
			$i = true;
		}
		$t .= resultRow($row);
	}
	$t .= '</table>';
	$t .= '<p><b>Query:</b> '.$query.'</p>';	
}	


function checkTable(){
	global $page;
	if( !($tables = getTableNames()) ){
		//message('Did not assign table value');
		return;
	}
	$t =& $page->contentA['Check'];
	$query = 'CHECK TABLE '.$tables;
	$result = wbDB::runQuery($query);
	$t .= '<table class="tableRows">';
	$i = false;
	while( $row = mysql_fetch_assoc($result) ){
		if( !$i){
			$t .= resultHeading($row);
			$i = true;
		}
		$t .= resultRow($row);
	}
	$t .= '</table>';
	$t .= '<p><b>Query:</b> '.$query.'</p>';	
}
	
function resultHeading(&$row){
	$t = '<tr>';
	foreach( $row as $key => $value){
		$t .= '<th>'.$key.'</th>';
	}
	$t .= '</tr>';
	return $t;
}
function resultRow(&$row){
	$t = '<tr>';
	foreach( $row as $key => $value){
		$t .= '<td>'.$value.'</td>';
	}
	$t .= '</tr>';
	return $t;	
}


////	Table Status
function getTableStatus(){
	global $page,$dbname,$dbObject,$lang;
	
	$page->formAction = $dbObject->links['Database'];
	
	ob_start();
	
	
	echo '<table class="tableRows">';
	echo '<tr><th>Table</th><th>Entries</th><th>Data Size</th><th>Index Size</th><th>Overhead</th><th>Last Modified</th><th>&nbsp;</th></tr>';

	$query = 'SHOW TABLE STATUS FROM `'.$dbname.'`';
	$result = wbDB::runQuery($query);
	$totals['Data_length'] = 0;
	$totals['Index_length'] = 0;
	$totals['Data_free'] = 0;
	$totals['Rows'] = 0;
	$i = 0;
	$classes[] = 'class="tableRowOdd" ';
	$classes[] = 'class="tableRowEven" ';
	
//	ob_start();
	while( $row = mysql_fetch_assoc($result) ){
	
		if( !checkTableName($row['Name'],false)){
			continue;
		}
		
		$totals['Data_length'] += $row['Data_free'];
		$totals['Data_length'] += $row['Data_length'];
		$totals['Index_length'] += $row['Index_length'];
		$totals['Data_free'] += $row['Data_free'];
		$totals['Rows'] += $row['Rows'];
	
		if( empty($row['Data_free']) ){
			$row['Data_free'] = ' - ';
		}else{
			$row['Data_free'] = number_format($row['Data_free']).' Bytes ';
		}
			
		echo '<tr '.$classes[($i%2)].' onClick="selectCheckBox(event,\''.addslashes($row['Name']).'\')" id="row_'.$row['Name'].'" style="cursor:pointer"><td><b>'.$row['Name'].'</b></td>';
		echo '<td>'.number_format($row['Rows']).'</td>';
		echo '<td>'.number_format($row['Data_length']/1000,($row['Data_length'] > 0) ? 1 : 0).' KB </td>';
		echo '<td>'.number_format($row['Index_length']/1000,($row['Index_length'] > 0) ? 1 : 0).' KB </td>';
		echo '<td>'.$row['Data_free'].'</td>';
		echo '<td>'.dbFromDate($row['Update_time'],3).'</td>';
		echo '<td class="sm">';
 			echo '<input type="checkbox" onClick="return true" id="checkbox_'.$row['Name'].'" value="'.$row['Name'].'" name="list[]" />';
		echo '</td>';
		echo '</tr>';
		$percentMax = '';
		$i++;

	}
	
	echo '<tr onClick="setCheckboxes(this)" style="cursor:pointer" ><td><b>Totals: </b></td>';
	echo '<td>'.number_format($totals['Rows']).'</td>';
	echo '<td>'.number_format($totals['Data_length']/1000,($totals['Data_length'] > 0) ? 1 : 0).' KB </td>';
	echo '<td>'.number_format($totals['Index_length']/1000,($totals['Index_length'] > 0) ? 1 : 0).' KB </td>';
	echo '<td>'. number_format($totals['Data_free']).' Bytes </td>';
	echo '<td colspan="2" class="sm" style="text-align:right">';
		echo '<a href="javascript:void(0)">'.$lang['check_uncheck'].'</a>';
	echo '</td>';
	echo '</tr>';
	echo '<tr><td colspan="7" class="sm" style="text-align:right">';
		echo '<input type="submit" name="cmd" value="Check" title="Check Tables" />';
		echo '<input type="submit" name="cmd" value="Analyze" title="Analyze Tables" />';
		echo '<input type="submit" name="cmd" value="Repair" title="Repair Tables"/>';
		echo '<input type="submit" name="cmd" value="Optimize" title="Optimize Tables"/>';
	echo '</tr>';
	echo '</table>';
	
	
	echo '<blockquote style="border:1px solid #d4d0c8;padding:7px;">';
		echo '"We recommend that to start with, you execute <i>myisamchk -s</i> [Check] each night on all tables that have been updated during the last 24 hours, until you come to trust MySQL as much as we do ... once a week is more than enough for us."<br/> <div style="text-align:right">-- MySQL Documentation</div>';
		
		echo '<blockquote>As of WikyBlog V1.4, CHECK and OPTIMIZE are regularly executed automatically.</blockquote>';
		echo '</blockquote>';
	
	
	$page->contentA['Database'] = wb::get_clean();
	
}