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\');">&nbsp;全選</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">-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</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();

?>