<?php
namespace admin_application;
use application\ESPCMS_FileTool;
class DBSql {
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 static function sqlBak() {
global $espcms_link_db;
$database_savesize = intval(ini_get('upload_max_filesize')) * 1024;
$bakFileDir = ESPCMS_FILE_ROOT . 'espcms_datacache/backup/';
if (!is_dir($bakFileDir)) {
return false;
}
$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';
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)) {
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);
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);
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);
return 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 = "-- ESPCMS 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;
}
}
}