gusucode.com > weenCompany闻名企业网站系统 4.0.0 繁体中英文 UTF8源码程序 > admin/database.php
<?php // +---------------------------------------------+ // | Copyright 2003 - 2005 weenCompany | // | http://www.weentech.com | // | This file may not be redistributed. | // +---------------------------------------------+ define('MOD_ACCESS', true); define('IN_ADMIN', true); define('IN_WEENCOMPANY', true); $rootpath = "./../"; include($rootpath . 'includes/core.php'); PrintHeader('數據庫維護'); // ######################### Test that the backup directory exists and is writable ########################## $backupDir = getcwd() . '/backup/'; $backupUrl = $weenurl . ADMIN_DIR.'/backup/'; $errors = array(); if (!is_dir($backupDir)) $errors[] ='數據庫備份文件夾 ('.$backupDir.') 不存在!'; if (!is_writable($backupDir)) $errors[] = '數據庫備份文件夾 ('.$backupDir.') 不可寫! - 屬性需改為: 0777'; if (!empty($errors)) { PrintErrors($errors, '初始化錯誤'); } else { $backupEnabled = true; } // ####################### FILE READ/WRITE USING GZIP ###################### function openFileWrite($filename) { if(function_exists('gzopen')) { $filename .= '.gz'; $handle = gzopen($filename, "w9"); } else { $handle = fopen($filename, "w"); } return $handle; } function openFileRead($filename) { if(function_exists('gzopen')) { $handle = gzopen($filename, "r"); } else { $handle = fopen($filename, "r"); } return $handle; } function writeFileData($handle, $data) { if(function_exists('gzwrite')) { gzwrite($handle, $data); } else { fwrite($handle, $data); } } function readFileData($handle, $size) { if(function_exists('gzread')) { $data = gzread($handle, $size); } else { $data = fread($handle, $size); } return $data; } function eof($handle) { if(function_exists('gzeof')) { return gzeof($handle); } else { return feof($handle); } } function closeFile($handle) { if(function_exists('gzclose')) { gzclose($handle); } else { fclose($handle); } } // ####################### END FILE READ FUNCTIONS ###################### function BackupTable($tablename, $fp) { global $DB; if(isset($fp)) { // Get the SQL to create the table $createTable = $DB->query_first("SHOW CREATE TABLE `$tablename`"); // Drop if it exists $tableDump = "DROP TABLE IF EXISTS `$tablename`;\n" . $createTable['Create Table'] . ";\n\n"; writeFileData($fp, $tableDump); // get data $getRows = $DB->query("SELECT * FROM `$tablename`"); $fieldCount = $DB->get_num_fields(); $rowCount = 0; while ($row = $DB->fetch_array($getRows)) { $tableDump = "INSERT INTO `$tablename` VALUES("; $fieldcounter = -1; $firstfield = 1; // get each field's data while (++$fieldcounter < $fieldCount) { if (!$firstfield) { $tableDump .= ', '; } else { $firstfield = 0; } if (!isset($row["$fieldcounter"])) { $tableDump .= 'NULL'; } elseif ($row["$fieldcounter"] != '') { $tableDump .= '\'' . addslashes($row["$fieldcounter"]) . '\''; } else { $tableDump .= '\'\''; } } $tableDump .= ");\n"; writeFileData($fp, $tableDump); $rowCount++; } writeFileData($fp, "\n\n\n"); $msg = "從表 '$tablename' 中備份了 $rowCount 行數據.<br/>"; } else { $msg = "備份數據庫表 '$tablename' 失敗!<br/>"; } return $msg; } function BackupSingleTable($tablename) { global $backupDir; $theverifycode = substr(md5(rand(0,9999)), 6, 12); $path = $backupDir . $tablename . '_' . $theverifycode .'_' . date("ymd") . '.sql'; $fp = openFileWrite($path); if($fp) { $msg = BackupTable($tablename, $fp); closeFile($fp); } $msg .= '<br/>數據庫表已備份到文件 ' . $path . '.gz<br/>'; return $msg; } function EmptyTable($tablename) { global $DB; $DB->query("DELETE FROM `$tablename`"); $msg .= '<br/>已完成清空數據庫表: ' . $tablename . '<br/>'; return $msg; } function BatchBackupTable($tablenames) { global $DB, $backupDir; $theverifycode = substr(md5(rand(0,9999)), 6, 12); $path = $backupDir . TABLE_PREFIX . $theverifycode . '_' . date("ymd") . '.sql'; $fp = openFileWrite($path); if($fp) { for($i = 0; $i < count($tablenames); $i++) { $msg = $msg . BackupTable($tablenames[$i], $fp); } closeFile($fp); } $msg .= '<br/>數據庫已備份到文件 ' . $path . '.gz<br/>'; return $msg; } function ParseQueries($sql, $delimiter) { $matches = array(); $output = array(); $queries = explode($delimiter, $sql); $sql = ""; $query_count = count($queries); for ($i = 0; $i < $query_count; $i++) { if (($i != ($query_count - 1)) || (strlen($queries[$i] > 0))) { $total_quotes = preg_match_all("/'/", $queries[$i], $matches); $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $queries[$i], $matches); $unescaped_quotes = $total_quotes - $escaped_quotes; if (($unescaped_quotes % 2) == 0) { $output[] = $queries[$i]; $queries[$i] = ""; } else { $temp = $queries[$i] . $delimiter; $queries[$i] = ""; $complete_stmt = false; for ($j = $i + 1; (!$complete_stmt && ($j < $query_count)); $j++) { $total_quotes = preg_match_all("/'/", $queries[$j], $matches); $escaped_quotes = preg_match_all("/(?<!\\\\)(\\\\\\\\)*\\\\'/", $queries[$j], $matches); $unescaped_quotes = $total_quotes - $escaped_quotes; if (($unescaped_quotes % 2) == 1) { $output[] = $temp . $queries[$j]; $queries[$j] = ""; $temp = ""; $complete_stmt = true; $i = $j; } else { $temp .= $queries[$j] . $delimiter; $queries[$j] = ""; } } } } } return $output; } function RestoreBackup($filename) { global $DB, $backupDir; // Read the file into memory and then execute it $fp = openFileRead($backupDir . $filename); while (!eof($fp)) { $query .= readFileData($fp, 10000); } closeFile($fp); // Split into discrete statements $queries = ParseQueries($query, ';'); for($i = 0; $i < count($queries); $i++) { $sql = trim($queries[$i]); if(!empty($sql)) { //echo $sql . '<br/>'; $DB->query($sql); } } PrintRedirect('database.php', 1); } function DeleteBackup($filename) { global $DB, $backupDir; @unlink($backupDir . $filename); PrintRedirect('database.php', 1); } // ####################### Display Action Results ###################### function PrintResults($title, $message) { PrintSection($title); echo '<table width="100%" border="0" cellpadding="5" cellspacing="0"> <tr> <td class="tdrow2" align="left"> <font class="ohblue">'.$title .'結果:</font><br/><br/>'. $message . ' <br/></td> </tr> </table>'; EndSection(); } // ####################### Perform OP on Table ###################### function TableOperation($tablename, $OP) { global $DB; $result = $DB->query_first("$OP TABLE `$tablename`"); return " '" . $tablename . "' : <strong>" . $result['Msg_text'] . "</strong><br/>"; } function BatchTableOperation($tablenames, $OP) { global $DB; $msg = ''; for($i = 0; $i < count($tablenames); $i++) { $msg = $msg . TableOperation($tablenames[$i], $OP); } return $msg; } // ######################### Instructions ########################## function PrintInstructions() { PrintSection('數據庫維護說明'); echo '<table width="100%" border="0" cellpadding="5" cellspacing="0"> <tr> <td class="tdrow2"> 通過此工具維護weenCompany系統數據庫, 操作說明如下:<br /> <strong>查錯</strong> - 檢查數據庫表是否存在錯誤.<br /> <strong>優化</strong> - 回收浪費的空間, 優化數據庫表.<br /> <strong>修復</strong> - 嘗試修復數據庫表中的錯誤.<br /> <strong>清空</strong> - '.TABLE_PREFIX.'sessions表用於記錄和驗證用戶登陸, '.TABLE_PREFIX.'vvc表用於記錄"驗證碼".<br /><br /> 提示:<br/> 1. 建議在進行數據維護時, 暫時關閉網站!<br/> 2. 定期備份數據庫, 當系統數據發生錯誤或丟失時可進行恢復. </td> </tr> </table>'; EndSection(); } // ####################### List Backup Files ######################### function DisplayBackups() { global $DB, $backupDir, $backupUrl, $backupEnabled; PrintSection('數據庫備份文件'); echo '<table width="100%" border="0" cellpadding="5" cellspacing="0"> <tr> <td class="tdrow1">文件名</td> <td class="tdrow1">大小</td> <td class="tdrow1">備份日期</td> <td class="tdrow1" colspan="3" align="center">操作</td></tr>'; if (is_dir($backupDir)) { $dir = opendir($backupDir); while (false !== ($file = readdir($dir))) { if(strpos(strtolower($file),'.sql') > 0) { $stats = stat($backupDir . $file); echo '<tr> <td class="tdrow3">' . $backupDir . $file . '</td> <td class="tdrow3">' . DisplayReadableFilesize($stats['size']) . '</td> <td class="tdrow3">' . DisplayDate($stats['mtime']) . '</td> <td class="tdrow3"><a href="database.php?dbaction=restorebackup&filename=' . $file . '" onclick="return confirm(\'確定恢復備份文件到數據庫嗎?\n\n注: 數據庫中對應的原數據將被刪除.\');">恢復</a></td>'; echo '<td class="tdrow3"><a href="' . $backupUrl . $file . '">下載</a></td> <td class="tdrow3"><a href="database.php?dbaction=deletebackup&filename=' . $file . '" onclick="return confirm(\'確定刪除選擇的備份文件嗎?\');">刪除</a></td> </tr>'; } } } echo '</table>'; EndSection(); } // ####################### List Database Tables ###################### function DisplayTables() { global $DB, $backupEnabled; PrintInstructions(); echo '<form method="post" action="database.php" name="tables"> <input type="hidden" name="dbaction" value=""/>'; PrintSection('數據庫列表'); echo '<table width="100%" border="0" cellpadding="5" cellspacing="0"> <tr> <td class="tdrow1">選擇</td> <td class="tdrow1">表名稱</td> <td class="tdrow1">記錄數</td> <td class="tdrow1">數據大小</td> <td class="tdrow1">索引大小</td> <td class="tdrow1">空閒</td> <td class="tdrow1" colspan="5" align="center">操作</td> </tr>'; $gettables = $DB->query("SHOW TABLES FROM `" . $DB->database . "` LIKE '" . substr(TABLE_PREFIX, 0, -1) ."\_%'"); while($table = $DB->fetch_array($gettables)) { $tableinfo = $DB->query_first("SHOW TABLE STATUS LIKE '" . $table[0] . "'"); echo '<tr> <td class="tdrow3"><input type="checkbox" name="tablenames[]" value="' . $tableinfo['Name'] . '" checkme="group" /></td> <td class="tdrow3">' . $tableinfo['Name'] . '</td> <td class="tdrow3">' . $tableinfo['Rows'] . '</td> <td class="tdrow3">' . DisplayReadableFilesize($tableinfo['Data_length']) . '</td> <td class="tdrow3">' . DisplayReadableFilesize($tableinfo['Index_length']) . '</td> <td class="tdrow3">' . iif($tableinfo['Data_free'] > 0, '<b>', '') . DisplayReadableFilesize($tableinfo['Data_free']) . iif($tableinfo['Data_free'] > 0, '</b>', '') . '</td> <td class="tdrow3"><a href="database.php?dbaction=checktable&tablename=' . $tableinfo['Name'] . '">查錯</a></td> <td class="tdrow3"><a href="database.php?dbaction=optimizetable&tablename=' . $tableinfo['Name'] . '">優化</a></td> <td class="tdrow3"><a href="database.php?dbaction=repairtable&tablename=' . $tableinfo['Name'] . '">修復</a></td> <td class="tdrow3">' . iif($backupEnabled, '<a href="database.php?dbaction=backuptable&tablename=' . $tableinfo['Name'] . '">備份</a>', '備份') . '</td>'; if($tableinfo['Name'] == TABLE_PREFIX . 'sessions') { echo '<td class="tdrow3"><a href="database.php?dbaction=emptytable&tablename=' . $tableinfo['Name'] . '" onclick="return confirm(\'確定清空此數據庫表嗎?\');">清空</a></td>'; } else if($tableinfo['Name'] == TABLE_PREFIX . 'vvc') { echo '<td class="tdrow3"><a href="database.php?dbaction=emptytable&tablename=' . $tableinfo['Name'] . '" onclick="return confirm(\'確定清空此數據庫表嗎?\');">清空</a></td>'; } else { echo '<td class="tdrow3">-</td>'; } echo '</tr>'; } echo '<tr> <td class="tdrow1" colspan="6"><input type="checkbox" checkall="group" onclick="javascript: return select_deselectAll (\'tables\', this, \'group\');"> 全選</td> <td class="tdrow1"><input type="submit" value="查錯" onclick="document.forms[\'tables\'].dbaction.value = \'checkall\';"/></td> <td class="tdrow1"><input type="submit" value="優化" onclick="document.forms[\'tables\'].dbaction.value = \'optimizeall\';"/></td> <td class="tdrow1"><input type="submit" value="修復" onclick="document.forms[\'tables\'].dbaction.value = \'repairall\';"/></td> <td class="tdrow1"><input type="submit" value="備份" onclick="document.forms[\'tables\'].dbaction.value = \'backupall\';" ' . iif($backupEnabled, '', 'disabled') . '/></td> <td class="tdrow1">- </td> </tr> </table> </form>'; EndSection(); DisplayBackups(); PrintFooter(); } // get the value of $action $action = isset($_POST['dbaction']) ? $_POST['dbaction'] : (isset($_GET['dbaction']) ? $_GET['dbaction'] : ''); $tablename = isset($_POST['tablename']) ? $_POST['tablename'] : (isset($_GET['tablename']) ? $_GET['tablename'] : ''); $filename = isset($_POST['filename']) ? $_POST['filename'] : (isset($_GET['filename']) ? $_GET['filename'] : ''); switch ($action) { case 'checktable': PrintResults('查錯數據庫表', TableOperation($tablename, 'CHECK')); break; case 'checkall': PrintResults('查錯數據庫表', BatchTableOperation($_POST['tablenames'], 'CHECK')); break; case 'optimizetable': PrintResults('優化數據庫表', TableOperation($tablename, 'OPTIMIZE')); break; case 'optimizeall': PrintResults('優化數據庫表',BatchTableOperation($_POST['tablenames'], 'OPTIMIZE')); break; case 'repairtable': PrintResults('修復數據庫表',TableOperation($tablename, 'REPAIR')); break; case 'repairall': PrintResults('修復數據庫表',BatchTableOperation($_POST['tablenames'], 'REPAIR')); break; case 'backuptable': PrintResults('備份數據庫表',BackupSingleTable($tablename)); break; case 'backupall': PrintResults('備份數據庫表', BatchBackupTable($_POST['tablenames'])); break; case 'emptytable': PrintResults('清空數據庫表', EmptyTable($tablename)); break; case 'restorebackup': RestoreBackup($filename); break; case 'deletebackup': DeleteBackup($filename); break; } DisplayTables(); ?>