<?php
use application\ESPCMS_AdminAuthority;
use application\ESPCMS_FileTool;
use admin_application\link\SettingLink;
class Database {
private static $database_baksize = 0;
private static $database_makesqlText;
private static $database_sql_num = 0;
private static $database_offset;
private static $database_vol_info = array();
public function __construct() {
$exclude_method = array('saveDatabase', 'db_import', 'splitsql', 'syntablestruct', 'creat_sql', 'outsqllink', 'fileArrayet',
'make_sqlhead', 'get_create_sql', 'get_insert_sql', 'getRow', 'getOne', 'getAll');
ESPCMS_AdminAuthority::authorityAdminVerify($exclude_method);
}
public static function listDatabase() {
global $espcms_admin_templates, $espcms_link_db;
$out_type = $_REQUEST['out_type'];
if ($out_type == 'list') {
$db_sql = "SHOW TABLE STATUS LIKE '" . ESPCMS_DB_PREFIX . "%'";
$db_query = $espcms_link_db->db_query($db_sql);
while ($fetch_row = $espcms_link_db->db_array_list($db_query)) {
$fetch_row['optlink'] = SettingLink::Database_link_array('opt', $fetch_row);
$array[] = $fetch_row;
}
$espcms_admin_templates->into('array', $array);
$templates = 'admin/database_list';
} else {
$espcms_admin_templates->into('link', SettingLink::Database_link_array());
$templates = 'admin/database_index';
}
$espcms_admin_templates->output($templates);
}
public static function backDatabase() {
global $espcms_admin_templates, $espcms_link_db;
$db_sql = "SHOW TABLE STATUS LIKE '" . ESPCMS_DB_PREFIX . "%'";
$db_query = $espcms_link_db->db_query($db_sql);
while ($fetch_row = $espcms_link_db->db_array_list($db_query)) {
$array[] = $fetch_row;
}
$espcms_admin_templates->into('array', $array);
$espcms_admin_templates->into('link', SettingLink::Database_link_array());
$upload_max_filesize = intval(ini_get('upload_max_filesize'));
$espcms_admin_templates->into('maxfilesize', $upload_max_filesize * 1024);
$espcms_admin_templates->into('token', token());
$espcms_admin_templates->output('admin/database_bak');
}
public static function recoverDatabase() {
global $espcms_admin_templates;
$bakFileDir = ESPCMS_FILE_ROOT . 'espcms_datacache/backup/';
if (!is_dir($bakFileDir)) {
espcms_message_err('database_pack-espcms_lable_database_backfile_err', 'false');
}
$sql_array = ESPCMS_FileTool::list_dir($bakFileDir, array('info'), 'file', false);
if (!is_array($sql_array)) {
espcms_message_err('database_pack-espcms_lable_database_nobackfile', 'false');
}
$sqlbak_info = array();
foreach ($sql_array AS $key => $file_read) {
$file = $file_read['filepath'];
if (!is_file($file)) {
continue;
}
$bak_file_info = file_get_contents($file);
if (!$bak_file_info) {
continue;
}
$bak_file_array = explode(',', $bak_file_info);
if (!is_array($bak_file_array)) {
continue;
}
$file_info = pathinfo($file);
if (!$file_info['basename']) {
continue;
}
$sqlbak_info[$key]['filename'] = $file_info['filename'];
$sqlbak_info[$key]['baknum'] = count($bak_file_array);
$sqlbak_info[$key]['addtime'] = filemtime($file);
}
rsort($sqlbak_info);
$espcms_admin_templates->into('sqlbak_info', $sqlbak_info);
$espcms_admin_templates->into('type', $_GET['type']);
$espcms_admin_templates->into('link', SettingLink::Database_link_array(false, $_GET));
$espcms_admin_templates->into('open_menu', 'setting');
$espcms_admin_templates->into('token', token());
$espcms_admin_templates->output('admin/database_recover');
}
public static function saveDatabase() {
global $espcms_link_db;
$saveType = $_POST['savetype'];
if (empty($saveType)) {
espcms_public_dialog('espcms_public_dialog', 'public_pack-espcms_input_parameter_fail', 'false', array($saveType));
}
if ($saveType == 'bak') {
$bakFileDir = ESPCMS_FILE_ROOT . 'espcms_datacache/backup/';
if (!is_dir($bakFileDir)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_backfile_err', 'false', array('espcms_datacache/backup/'));
}
$sql_runlog_file = $bakFileDir . 'databack_name.log';
$sql_filekey = 'espcms_dbbak_' . espcms_random('[Y][M][D][H][I][S]');
$sql_filename = $bakFileDir . $sql_filekey;
$sql_filename_info_path = $sql_filename . '.info';
$upload_max_filesize = intval(ini_get('upload_max_filesize'));
$upload_filesize = $upload_max_filesize * 1024;
$database_savesize = $_POST['database_savesize'];
if (empty($database_savesize) || !espcms_ismatches($database_savesize)) {
espcms_public_dialog('espcms_public_dialog', 'public_pack-espcms_input_parameter_fail', 'false', array($database_savesize));
}
$database_savesize = $database_savesize > $upload_filesize ? $upload_filesize : $database_savesize;
self::$database_baksize = $database_savesize * 1024;
$vol = 1;
$tables = array();
if ($vol <= 1) {
$str = '';
$db_sql = "SHOW TABLE STATUS LIKE '" . ESPCMS_DB_PREFIX . "%'";
$db_query = $espcms_link_db->db_query($db_sql);
while ($fetch_row = $espcms_link_db->db_array_list($db_query)) {
$tables[$fetch_row['Name']] = -1;
}
foreach ($tables as $key => $val) {
$str .= $key . ':' . $val . ";\r\n";
}
if (!ESPCMS_FileTool::writeFile($sql_runlog_file, $str)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_backfile_err2', 'false', array($sql_runlog_file));
}
}
$tables = self::outsqllink($sql_runlog_file, $vol);
if ($tables === false) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_backfile_err3', 'false');
}
if (empty($tables)) {
$sql_filename_path = $sql_filename . '_' . $vol . '.sql';
self::$database_vol_info[] = $sql_filekey . '_' . $vol . '.sql';
ESPCMS_FileTool::writeFile($sql_filename_path, self::$database_makesqlText);
if (is_array(self::$database_vol_info)) {
$database_vol_info = implode(',', self::$database_vol_info);
ESPCMS_FileTool::writeFile($sql_filename_info_path, $database_vol_info);
}
@unlink($sql_runlog_file);
espcms_log_install('database_pack-espcms_lable_database_bak_botton');
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_bak_save_ok', 'true');
} else {
$sql_filename_path = $sql_filename . '_' . $vol . '.sql';
self::$database_vol_info[] = $sql_filekey . '_' . $vol . '.sql';
ESPCMS_FileTool::writeFile($sql_filename_path, self::$database_makesqlText);
$vol++;
$is_sql_creat = self::creat_sql($sql_filename, $sql_runlog_file, $sql_filename_info_path, $sql_filekey, $database_savesize, $vol);
espcms_log_install('database_pack-espcms_lable_database_bak_botton');
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_bak_save_ok', 'true');
}
} elseif ($saveType == 'recover') {
$bakFileDir = ESPCMS_FILE_ROOT . 'espcms_datacache/backup/';
if (!is_dir($bakFileDir)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_backfile_err', 'false', array('espcms_datacache/backup/'));
}
$recover_file = $_POST['recover_file'];
$sql_bak_infofile = $bakFileDir . $recover_file . '.info';
if (!is_file($sql_bak_infofile)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_recover_file_err', 'false');
}
$bak_file_info = file_get_contents($sql_bak_infofile);
if (!$bak_file_info) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_recover_file_err', 'false');
}
$bak_file_array = explode(',', $bak_file_info);
if (!is_array($bak_file_array)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_recover_file_err', 'false');
}
foreach ($bak_file_array as $sql_filename) {
$sql_filename_path = $bakFileDir . $sql_filename;
$runid = self::db_import($sql_filename_path);
if (!$runid) {
continue;
}
}
espcms_log_install('database_pack-espcms_lable_database_recover_botton');
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_recover_save_ok', 'true');
}
}
public static function delDatabase() {
$fileArray = $_POST['select_id'];
if (!is_array($fileArray)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_del_err', 'false');
}
$bakFileDir = ESPCMS_FILE_ROOT . 'espcms_datacache/backup/';
if (!is_dir($bakFileDir)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_backfile_err', 'false', array('espcms_datacache/backup/'));
}
$del_file_array = array();
foreach ($fileArray as $key => $filename) {
$sql_bak_infofile = $bakFileDir . $filename . '.info';
if (!is_file($sql_bak_infofile)) {
continue;
}
$del_file_array[] = $sql_bak_infofile;
$bak_file_info = file_get_contents($sql_bak_infofile);
if (!$bak_file_info) {
continue;
}
$bak_file_array = explode(',', $bak_file_info);
if (!is_array($bak_file_array)) {
continue;
}
foreach ($bak_file_array as $sql_filename) {
$sql_filename_path = $bakFileDir . $sql_filename;
$del_file_array[] = $sql_filename_path;
}
}
if (!is_array($del_file_array)) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_del_err', 'false');
}
foreach ($del_file_array as $delfile) {
if (!is_file($delfile)) {
continue;
}
ESPCMS_FileTool::delFile($delfile);
}
$delFileStr = implode(',', $fileArray);
espcms_log_install('database_pack-espcms_lable_database_del_botton', $delFileStr);
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_del_ok', 'true');
}
public static function optimizeDatabase() {
global $espcms_link_db;
$dbtable = $_GET['dbtable'];
if ($dbtable) {
$row = self::getRow('OPTIMIZE TABLE ' . $dbtable);
if (!$row) {
espcms_public_dialog('espcms_public_dialog', 'database_pack-espcms_lable_database_opt_err', 'false');
}
if ($row['Msg_type'] == 'error' && strpos($row['Msg_text'], 'repair') !== false) {
$espcms_link_db->db_query('REPAIR TABLE ' . $dbtable);
}
espcms_log_install('database_pack-espcms_lable_database_optword_botton', $dbtable);
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_opt_save_ok', 'true');
} else {
$db_sql = "SHOW TABLE STATUS LIKE '" . ESPCMS_DB_PREFIX . "%'";
$db_query = $espcms_link_db->db_query($db_sql);
while ($fetch_row = $espcms_link_db->db_array_list($db_query)) {
$dbtable = $fetch_row['Name'];
$row = self::getRow('OPTIMIZE TABLE ' . $dbtable);
if (!$row) {
continue;
}
if ($row['Msg_type'] == 'error' && strpos($row['Msg_text'], 'repair') !== false) {
$espcms_link_db->db_query('REPAIR TABLE ' . $dbtable);
}
}
espcms_log_install('database_pack-espcms_lable_database_opt_botton');
espcms_public_dialog('save_ok', 'database_pack-espcms_lable_database_opt_save_ok', 'true');
}
}
private static function db_import($sql_filename_path) {
global $espcms_link_db;
$sqldump = array_filter(file($sql_filename_path), 'remove_comment');
$sqldump = str_replace("\r", '', implode('', $sqldump));
$sqlquery = self::splitsql($sqldump);
if (!is_array($sqlquery)) {
return false;
}
unset($sqldump);
foreach ($sqlquery as $sql) {
$sql = self::syntablestruct(trim($sql), $espcms_link_db->db_version() > '4.1', ESPCMS_DB_CHARSET);
if ($sql) {
$espcms_link_db->db_query($sql, 'SILENT');
if (($sqlerror = $espcms_link_db->error()) && $espcms_link_db->errno() != 1062) {
return false;
}
}
}
return true;
}
private static function splitsql($sql) {
$sql = str_replace("\r", "\n", $sql);
$ret = array();
$num = 0;
$queriesarray = explode(";\n", trim($sql));
unset($sql);
foreach ($queriesarray as $query) {
$queries = explode("\n", trim($query));
foreach ($queries as $query) {
$ret[$num] .= $query[0] == "--" ? NULL : $query;
}
$num++;
}
return ($ret);
}
private static function syntablestruct($sql, $version, $dbcharset) {
if (strpos(trim(substr($sql, 0, 18)), 'CREATE TABLE') === FALSE) {
return $sql;
}
$sqlversion = strpos($sql, 'ENGINE=') === FALSE ? FALSE : TRUE;
if ($sqlversion === $version) {
return $sqlversion && $dbcharset ? preg_replace(array('/ character set \w+/i', '/ collate \w+/i', "/DEFAULT CHARSET=\w+/is"), array('', '', "DEFAULT CHARSET=$dbcharset"), $sql) : $sql;
}
if ($version) {
return preg_replace(array('/TYPE=HEAP/i', '/TYPE=(\w+)/is'), array("ENGINE=MEMORY DEFAULT CHARSET=$dbcharset", "ENGINE=\\1 DEFAULT CHARSET=$dbcharset"), $sql);
} else {
return preg_replace(array('/character set \w+/i', '/collate \w+/i', '/ENGINE=MEMORY/i', '/\s*DEFAULT CHARSET=\w+/is', '/\s*COLLATE=\w+/is', '/ENGINE=(\w+)(.*)/is'), array('', '', 'ENGINE=HEAP', '', '', 'TYPE=\\1\\2'), $sql);
}
}
private static function creat_sql($sql_filename, $sql_runlog_file, $sql_filename_info_path, $sql_filekey, $database_savesize = 0, $vol = 2) {
if (!$database_savesize || $vol < 2) {
return false;
}
$tables = self::outsqllink($sql_runlog_file, $vol);
if ($tables === false) {
return false;
}
if (empty($tables)) {
$sql_filename_path = $sql_filename . '_' . $vol . '.sql';
self::$database_vol_info[] = $sql_filekey . '_' . $vol . '.sql';
ESPCMS_FileTool::writeFile($sql_filename_path, self::$database_makesqlText);
@unlink($sql_runlog_file);
if (is_array(self::$database_vol_info)) {
$database_vol_info = implode(',', self::$database_vol_info);
ESPCMS_FileTool::writeFile($sql_filename_info_path, $database_vol_info);
}
return true;
} else {
$sql_filename_path = $sql_filename . '_' . $vol . '.sql';
self::$database_vol_info[] = $sql_filekey . '_' . $vol . '.sql';
ESPCMS_FileTool::writeFile($sql_filename_path, self::$database_makesqlText);
$vol++;
self::creat_sql($sql_filename, $sql_runlog_file, $sql_filename_info_path, $sql_filekey, $database_savesize, $vol);
}
}
private static function outsqllink($sql_runlog_file, $vol) {
$tables = self::fileArrayet($sql_runlog_file);
if ($tables === false) {
return false;
}
if (empty($tables)) {
return $tables;
}
self::$database_makesqlText = self::make_sqlhead($vol);
foreach ($tables as $table => $pos) {
if ($pos == -1) {
$table_df = self::get_create_sql($table, true);
if (strlen(self::$database_makesqlText) + strlen($table_df) > self::$database_baksize - 32) {
if (self::$database_sql_num == 0) {
self::$database_makesqlText .= $table_df;
self::$database_sql_num += 2;
$tables[$table] = 0;
}
break;
} else {
self::$database_makesqlText .= $table_df;
self::$database_sql_num += 2;
$pos = 0;
}
}
$post_pos = self::get_insert_sql($table, $pos);
if ($post_pos == -1) {
unset($tables[$table]);
} else {
$tables[$table] = $post_pos;
break;
}
}
self::$database_makesqlText .= '-- END EasySitePM SQL Dump Program ';
if (is_array($tables)) {
$str = '';
foreach ($tables as $key => $val) {
$str .= $key . ':' . $val . ";\r\n";
}
ESPCMS_FileTool::writeFile($sql_runlog_file, $str);
}
return $tables;
}
private static function fileArrayet($path) {
if (!file_exists($path)) {
return false;
}
$arr = array();
$str = file_get_contents($path);
if (!empty($str)) {
$tmp_arr = explode("\n", $str);
foreach ($tmp_arr as $val) {
$val = trim($val, "\r;");
if (!empty($val)) {
list($table, $count) = explode(':', $val);
$arr[$table] = $count;
}
}
}
return $arr;
}
private static function make_sqlhead() {
global $espcms_link_db;
$head = "-- SEAPOA SQL Dump\r\n" .
"-- HOST:" . ESPCMS_URL . "\r\n" .
"-- DATE:" . espcms_timeformat(time(), 3) . "\r\n" .
"-- SQLVER:" . $espcms_link_db->db_version() . "\r\n" .
"-- PHPVER:" . phpversion() . "\r\n" .
"-- Vol:" . ESPCMS_VERSION . "\r\n";
return $head;
}
private static function get_create_sql($table, $add_drop = false) {
if ($add_drop) {
$table_df = "DROP TABLE IF EXISTS `$table`;\r\n";
} else {
$table_df = '';
}
$tmp_arr = self::getRow("SHOW CREATE TABLE `$table`");
$tmp_sql = $tmp_arr['Create Table'];
$tmp_sql = substr($tmp_sql, 0, strrpos($tmp_sql, ")") + 1); $table_df .= $tmp_sql . " TYPE=MyISAM;\r\n";
return $table_df;
}
private static function get_insert_sql($table, $pos) {
$post_pos = $pos;
self::$database_offset = 300;
$total = self::getOne("SELECT COUNT(*) FROM $table");
if ($total == 0 || $pos >= $total) {
return -1;
}
$cycle_time = ceil(($total - $pos) / self::$database_offset);
for ($i = 0; $i < $cycle_time; $i++) {
$array = array();
$data = self::getAll("SELECT * FROM $table LIMIT " . (self::$database_offset * $i + $pos) . ', ' . self::$database_offset);
$data_count = count($data);
$fields = array_keys($data[0]);
$start_sql = "INSERT INTO `$table` ( `" . implode("`, `", $fields) . "` ) VALUES ";
for ($j = 0; $j < $data_count; $j++) {
$record = array_map("dump_escape_string", $data[$j]);
$record = array_map("dump_null_string", $record);
$tmp_dump_sql = $start_sql . " ('" . implode("', '", $record) . "');\r\n";
$tmp_str_pos = strpos($tmp_dump_sql, 'NULL');
$tmp_dump_sql = empty($tmp_str_pos) ? $tmp_dump_sql : substr($tmp_dump_sql, 0, $tmp_str_pos - 1) . 'NULL' . substr($tmp_dump_sql, $tmp_str_pos + 5);
if (strlen(self::$database_makesqlText) + strlen($tmp_dump_sql) > self::$database_baksize - 32) {
if (self::$database_sql_num == 0) {
self::$database_makesqlText .= $tmp_dump_sql;
self::$database_sql_num++;
$post_pos++;
if ($post_pos == $total) {
return -1;
}
}
return $post_pos;
} else {
self::$database_makesqlText .= $tmp_dump_sql;
self::$database_sql_num++;
$post_pos++;
}
}
}
return -1;
}
private static function getRow($sql, $limited = false) {
global $espcms_link_db;
if ($limited == true) {
$sql = trim($sql . ' LIMIT 1');
}
$res = $espcms_link_db->db_query($sql);
if ($res !== false) {
return mysql_fetch_assoc($res);
} else {
return false;
}
}
private static function getOne($sql, $limited = false) {
global $espcms_link_db;
if ($limited == true) {
$sql = trim($sql . ' LIMIT 1');
}
$res = $espcms_link_db->db_query($sql);
if ($res !== false) {
$row = mysql_fetch_row($res);
if ($row !== false) {
return $row[0];
} else {
return '';
}
} else {
return false;
}
}
private static function getAll($sql) {
global $espcms_link_db;
$res = $espcms_link_db->db_query($sql);
if ($res !== false) {
$arr = array();
while ($row = mysql_fetch_assoc($res)) {
$arr[] = $row;
}
return $arr;
} else {
return false;
}
}
}