db.inc.php

Discuz功能源码（开源）

		$sqlerror ? cpmsg('database_run_query_invalid', '', 'error') : cpmsg('database_run_query_succeed', '', 'succeed');

	}

} elseif($operation == 'optimize') {

	$optimizetable = '';
	$totalsize = 0;
	$tablearray = array( 0 =>$tablepre);

	shownav('tools', 'nav_db', 'nav_db_optimize');
	showsubmenu('nav_db', array(
		array('nav_db_export', 'db&operation=export', 0),
		array('nav_db_import', 'db&operation=import', 0),
		array('nav_db_runquery', 'db&operation=runquery', 0),
		array('nav_db_optimize', 'db&operation=optimize', 1),
		array('nav_db_dbcheck', 'db&operation=dbcheck', 0)

	));
	showtips('db_optimize_tips');
	showformheader('db&operation=optimize');
	showtableheader('db_optimize_tables');
	showsubtitle(array('', 'db_optimize_table_name', 'type', 'db_optimize_rows', 'db_optimize_data', 'db_optimize_index', 'db_optimize_frag'));

	if(!submitcheck('optimizesubmit')) {

		foreach($tablearray as $tp) {
			$query = $db->query("SHOW TABLE STATUS LIKE '$tp%'", 'SILENT');
			while($table = $db->fetch_array($query)) {
				if($table['Data_free'] && $table[$tabletype] == 'MyISAM') {
					$checked = $table[$tabletype] == 'MyISAM' ? 'checked' : 'disabled';
					showtablerow('', '', array(
						"<input class=\"checkbox\" type=\"checkbox\" name=\"optimizetables[]\" value=\"$table[Name]\" $checked>",
						$table[Name],
						$table[$tabletype],
						$table[Rows],
						$table[Data_length],
						$table[Index_length],
						$table[Data_free],
					));
					$totalsize += $table['Data_length'] + $table['Index_length'];
				}
			}
		}
		if(empty($totalsize)) {
			showtablerow('', 'colspan="6"', $lang['db_optimize_done']);
		} else {
			showtablerow('', 'colspan="6"', $lang['db_optimize_used'].' '.sizecount($totalsize));
			showsubmit('optimizesubmit', 'submit', '<input name="chkall" id="chkall" class="checkbox" onclick="checkAll(\'prefix\', this.form)" checked="checked" type="checkbox" /><label for="chkall">'.$lang[db_optimize_opt].'</label>');
		}

	} else {

		//$db->query("DELETE FROM {$tablepre}subscriptions", 'UNBUFFERED');
		//$db->query("UPDATE {$tablepre}memberfields SET authstr=''", 'UNBUFFERED');

		foreach($tablearray as $tp) {
			$query = $db->query("SHOW TABLE STATUS LIKE '$tp%'", 'SILENT');
			while($table = $db->fetch_array($query)) {
				if(is_array($optimizetables) && in_array($table['Name'], $optimizetables)) {
					$db->query("OPTIMIZE TABLE $table[Name]");
				}
				showtablerow('', '', array(
					$table[Name],
					$db->version() > '4.1' ?  $table['Engine'] : $table['Type'],
					$table[Rows],
					$table[Data_length],
					$table[Index_length],
					0
				));
				$totalsize += $table['Data_length'] + $table['Index_length'];
			}
		}
		showtablerow('', 'colspan="6"', $lang['db_optimize_used'].' '.sizecount($totalsize));
	}

	showtablefooter();
	showformfooter();

} elseif($operation == 'dbcheck') {

	if(!$db->query("SHOW FIELDS FROM {$tablepre}settings", 'SILENT')) {
		cpmsg('dbcheck_permissions_invalid', '', 'error');
	}

	$step = max(1, intval($step));
	if($step == 3) {

		if(!file_exists('admin/discuzdb.md5')) {
			cpmsg('dbcheck_nofound_md5file', '', 'error');
		}

		include DISCUZ_ROOT.'./config.inc.php';
		$dbcharset = empty($dbcharset) ? $charset : $dbcharset;
		unset($dbuser, $dbpw, $dbname);

		$fp = fopen(DISCUZ_ROOT.'./admin/discuzdb.md5', "rb");
		$discuzdb = fread($fp, filesize(DISCUZ_ROOT.'./admin/discuzdb.md5'));
		fclose($fp);
		$dbmd5 = substr($discuzdb, 0, 32);
		$discuzdb = unserialize(substr($discuzdb, 34));
		$settingsdata = $discuzdb[1];
		$discuzdb = $discuzdb[0][0];
		$repair = !empty($repair) ? $repair : array();
		$setting = !empty($setting) ? $setting : array();
		$missingtable = !empty($missingtable) ? $missingtable : array();
		$repairtable = is_array($repairtable) && !empty($repairtable) ? $repairtable : array();

		$except = array('threads' => array('sgid'));
		$query = $db->query("SELECT fieldid FROM {$tablepre}profilefields");
		while($profilefields = $db->fetch_array($query)) {
			$except['memberfields'][] = 'field_'.$profilefields[$fieldid];
		}

		if(submitcheck('repairsubmit') && (!empty($repair) || !empty($setting) || !empty($repairtable) || !empty($missingtable))) {
			$error = '';$errorcount = 0;
			$alter = $fielddefault = array();

			foreach($missingtable as $value) {
				if(!isset($installdata)) {
					$fp = fopen(DISCUZ_ROOT.'./install/discuz.sql', "rb");
					$installdata = fread($fp, filesize(DISCUZ_ROOT.'./install/discuz.sql'));
					fclose($fp);
				}
				preg_match("/CREATE TABLE ".$tablepre.$value."\s+\(.+?;/is", $installdata, $a);
				$db->query(createtable($a[0], $dbcharset));
			}

			foreach($repair as $value) {
				if(!in_array($r_table, $repairtable)) {
					list($r_table, $r_field, $option) = explode('|', $value);
					if(!isset($repairrtable[$r_table]) && $fieldsquery = $db->query("SHOW FIELDS FROM $tablepre$r_table", 'SILENT')) {
						while($fields = $db->fetch_array($fieldsquery)) {
							$fielddefault[$r_table][$fields['Field']] = $fields['Default'];
						}
					}

					$field = $discuzdb[$r_table][$r_field];
					$altersql = '`'.$field['Field'].'` '.$field['Type'];
					$altersql .= $field['Null'] == 'NO' ? ' NOT NULL' : '';
					$altersql .= in_array($fielddefault[$r_table][$field['Field']], array('', '0')) && in_array($field['Default'], array('', '0')) ||
						$field['Null'] == 'NO' && $field['Default'] == '' ||
						preg_match('/text/i', $field['Type']) || preg_match('/auto_increment/i', $field['Extra']) ?
						'' : ' default \''.$field['Default'].'\'';
					$altersql .= $field['Extra'] != '' ? ' '.$field['Extra'] : '';
					$altersql = $option == 'modify' ? "MODIFY COLUMN ".$altersql : "ADD COLUMN ".$altersql;
					$alter[$r_table][] = $altersql;
				}
			}

			foreach($alter as $r_table => $sqls) {
				$db->query("ALTER TABLE `$tablepre$r_table` ".implode(',', $sqls), 'SILENT');
				if($sqlerror = $db->error()) {
					$errorcount += count($sqls);
					$error .= $sqlerror.'<br /><br />';
				}
			}
			$alter = array();

			foreach($repairtable as $value) {
				foreach($discuzdb[$value] as $field) {
					if(!isset($fielddefault[$value]) && $fieldsquery = $db->query("SHOW FIELDS FROM $tablepre$value", 'SILENT')) {
						while($fields = $db->fetch_array($fieldsquery)) {
							$fielddefault[$value][$fields['Field']] = $fields['Default'];
						}
					}
					$altersql = '`'.$field['Field'].'` '.$field['Type'];
					$altersql .= $field['Null'] == 'NO' ? ' NOT NULL' : '';
					$altersql .= in_array($fielddefault[$value][$field['Field']], array('', '0')) && in_array($field['Default'], array('', '0')) ||
						$field['Null'] == 'NO' && $field['Default'] == '' ||
						preg_match('/text/i', $field['Type']) || preg_match('/auto_increment/i', $field['Extra']) ?
						'' : ' default \''.$field['Default'].'\'';
					$altersql .= $field['Extra'] != '' ? ' '.$field['Extra'] : '';
					$altersql = "MODIFY COLUMN ".$altersql;
					$alter[$value][] = $altersql;
				}
			}

			foreach($alter as $r_table => $sqls) {
				$db->query("ALTER TABLE `$tablepre$r_table` ".implode(',', $sqls), 'SILENT');
				if($sqlerror = $db->error()) {
					$errorcount += count($sqls);
					$error .= $sqlerror.'<br /><br />';
				}
			}

			if(!empty($setting)) {
				$settingsdatanow = array();
				$settingsquery = $db->query("SELECT variable FROM {$tablepre}settings WHERE SUBSTRING(variable, 1, 9)<>'jswizard_' ORDER BY variable");
				while($settings = $db->fetch_array($settingsquery)) {
					$settingsdatanew[] = $settings['variable'];
				}
				$settingsdellist = @array_diff($settingsdata, $settingsdatanew);
				if($setting['del'] && is_array($settingsdellist)) {
					foreach($settingsdellist as $variable) {
						$db->query("INSERT INTO {$tablepre}settings (variable, value) VALUES ('$variable', '')", 'SILENT');
					}
				}
				updatecache('settings');
			}

			if($errorcount) {
				cpmsg('dbcheck_repair_error', '', 'error');
			} else {
				cpmsg('dbcheck_repair_completed', $BASESCRIPT.'?action=db&operation=dbcheck&step=3', 'succeed');
			}
		}

		$installexists = file_exists(DISCUZ_ROOT.'./install/discuz.sql');
		$discuzdbnew = $deltables = $excepttables = $missingtables = $charseterror = array();
		foreach($discuzdb as $dbtable => $fields) {
			if($fieldsquery = $db->query("SHOW FIELDS FROM $tablepre$dbtable", 'SILENT')) {
				while($fields = $db->fetch_array($fieldsquery)) {
					$r = '/^'.$tablepre.'/';
					$cuttable = preg_replace($r, '', $dbtable);
					if($db->version() < '4.1' && $cuttable == 'sessions' && $fields['Field'] == 'sid') {
						$fields['Type'] = str_replace(' binary', '', $fields['Type']);
					}
					if($cuttable == 'memberfields' && preg_match('/^field\_\d+$/', $fields['Field'])) {
						unset($discuzdbnew[$cuttable][$fields['Field']]);
						continue;
					}
					$discuzdbnew[$cuttable][$fields['Field']]['Field'] = $fields['Field'];
					$discuzdbnew[$cuttable][$fields['Field']]['Type'] = $fields['Type'];
					$discuzdbnew[$cuttable][$fields['Field']]['Null'] = $fields['Null'] == '' ? 'NO' : $fields['Null'];
					$discuzdbnew[$cuttable][$fields['Field']]['Extra'] = $fields['Extra'];
					$discuzdbnew[$cuttable][$fields['Field']]['Default'] = $fields['Default'] == '' || $fields['Default'] == '0' ? '' : $fields['Default'];
				}
				ksort($discuzdbnew[$cuttable]);
			} else {
				$missingtables[] = '<span style="float:left;width:33%">'.(($installexists ? '<input name="missingtable[]" type="checkbox" class="checkbox" value="'.$dbtable.'">' : '').$tablepre.$dbtable).'</span>';
				$excepttables[] = $dbtable;
			}
		}

		if($db->version() > '4.1') {
			$dbcharset = strtoupper($dbcharset) == 'UTF-8' ? 'UTF8' : strtoupper($dbcharset);
			$query = $db->query("SHOW TABLE STATUS LIKE '$tablepre%'");
			while($tables = $db->fetch_array($query)) {
				$r = '/^'.$tablepre.'/';
				$cuttable = preg_replace($r, '', $tables['Name']);
				$tabledbcharset = substr($tables['Collation'], 0, strpos($tables['Collation'], '_'));
				if($dbcharset != strtoupper($tabledbcharset)) {
					$charseterror[] = '<span style="float:left;width:33%">'.$tablepre.$cuttable.'('.$tabledbcharset.')</span>';
				}
			}
		}

		$dbmd5new = md5(serialize($discuzdbnew));

		$settingsdatanow = array();
		$settingsquery = $db->query("SELECT variable FROM {$tablepre}settings WHERE SUBSTRING(variable, 1, 9)<>'jswizard_' ORDER BY variable");
		while($settings = $db->fetch_array($settingsquery)) {
			$settingsdatanew[] = $settings['variable'];
		}
		$settingsdellist = @array_diff($settingsdata, $settingsdatanew);

		if($dbmd5 == $dbmd5new && empty($charseterror) && empty($settingsdellist)) {
			cpmsg('dbcheck_ok', '', 'succeed');
		}

		$showlist = $addlists = '';
		foreach($discuzdb as $dbtable => $fields) {
			$addlist = $modifylist = $dellist = array();
			if($fields != $discuzdbnew[$dbtable]) {
				foreach($discuzdb[$dbtable] as $key => $value) {
					if(is_array($missingtables) && in_array($tablepre.$dbtable, $missingtables)) {
					} elseif(!isset($discuzdbnew[$dbtable][$key])) {
						$dellist[] = $value;
					} elseif($value != $discuzdbnew[$dbtable][$key]) {
						$modifylist[] = $value;
					}
				}
				if(is_array($discuzdbnew[$dbtable])) {
					foreach($discuzdbnew[$dbtable] as $key => $value) {
						if(!isset($discuzdb[$dbtable][$key]) && !@in_array($value['Field'], $except[$dbtable])) {
							$addlist[] = $value;
						}
					}
				}
			}

			if(($modifylist || $dellist) && !in_array($dbtable, $excepttables)) {

				$showlist .= showtablerow('', '', array("<span class=\"diffcolor3\">$tablepre$dbtable</span> $lang[dbcheck_field]", $lang[dbcheck_org_field], $lang[dbcheck_status]), TRUE);

				foreach($modifylist as $value) {
					$slowstatus = slowcheck($discuzdbnew[$dbtable][$value['Field']]['Type'], $value['Type']);

					$showlist .= "<tr><td><input name=\"repair[]\" class=\"checkbox\" type=\"checkbox\" value=\"$dbtable|$value[Field]|modify\"> <b>".$value['Field']."</b> ".
						$discuzdbnew[$dbtable][$value['Field']]['Type'].
						($discuzdbnew[$dbtable][$value['Field']]['Null'] == 'NO' ? ' NOT NULL' : '').
						(!preg_match('/auto_increment/i', $discuzdbnew[$dbtable][$value['Field']]['Extra']) && !preg_match('/text/i', $discuzdbnew[$dbtable][$value['Field']]['Type']) ? ' default \''.$discuzdbnew[$dbtable][$value['Field']]['Default'].'\'' : '').
						' '.$discuzdbnew[$dbtable][$value['Field']]['Extra'].
						"</td><td><b>".$value['Field']."</b> ".$value['Type'].
						($value['Null'] == 'NO' ? ' NOT NULL' : '').
						(!preg_match('/auto_increment/i', $value['Extra']) && !preg_match('/text/i', $value['Type']) ? ' default \''.$value['Default'].'\'' : '').
						' '.$value['Extra']."</td><td>".
						(!$slowstatus ? "<em class=\"edited\">$lang[dbcheck_modify]</em></td></tr>" : "<em class=\"unknown\">$lang[dbcheck_slow]</em>")."</td></tr>";
				}

				if($modifylist) {
					$showlist .= showtablerow('', 'colspan="3"', "<input onclick=\"setrepaircheck(this, this.form, '$dbtable')\" name=\"repairtable[]\" class=\"checkbox\" type=\"checkbox\" value=\"$dbtable\"> <b>$lang[dbcheck_repairtable]</b>", TRUE);
				}

				foreach($dellist as $value) {
					$showlist .= "<tr><td><input name=\"repair[]\" class=\"checkbox\" type=\"checkbox\" value=\"$dbtable|$value[Field]|add\"> <strike><b>".$value['Field']."</b></strike></td><td> <b>".$value['Field']."</b> ".$value['Type'].($value['Null'] == 'NO' ? ' NOT NULL' : '')."</td><td>".
						"<em class=\"del\">$lang[dbcheck_delete]</em></td></tr>";
				}
			}

			if($addlist) {
				$addlists .= "<tr><td colspan=\"3\"><b>$tablepre$dbtable</b> $lang[dbcheck_new_field]</td></tr>";

				foreach($addlist as $value) {
					$addlists .= "<tr><td colspan=\"3\">&nbsp;&nbsp;&nbsp;&nbsp;<b>".$value['Field']."</b> ".$discuzdbnew[$dbtable][$value['Field']]['Type'].($discuzdbnew[$dbtable][$value['Field']]['Null'] == 'NO' ? ' NOT NULL' : '')."</td></tr>";
				}
			}