tp5實作資料庫拷貝
龍行 PHP 2019-1-7 2051 0評論
一個資料庫拷貝實作功能,還是挺好的
backsql拷貝實作類
namespace expand;
class Baksql {
private $config=[];
private $handler;
private $tables = array();//需要備份的表
private $begin; //開始時間
private $error;//錯誤資訊
public function __construct($config) {
$config['path'] = WEB_PATH."/databakss/"; //拷貝位址預設目錄
$config["sqlbakname"]=date("YmdHis",time()).".sql";//預設儲存檔案名
$this->config = $config;
$this->begin = microtime(true);
header("Content-type: text/html;charset=utf-8");
$this->connect();
}
//首次進行pdo連接配接
private function connect() {
try{
$this->handler =new \PDO("{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']};",
$this->config['username'],
$this->config['password'],
array(
\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']};",
\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC
));
}catch (PDOException $e) {
die ("Error!: " . $e->getMessage() . "
");
}
}
private function query($sql = '')
{
$stmt = $this->handler->query($sql);
$stmt->setFetchMode(\PDO::FETCH_NUM);
$list = $stmt->fetchAll();
return $list;
}
private function get_dbname($dbName = '*') {
$sql = 'SHOW TABLES';
$list = $this->query($sql);
$tables = array();
foreach ($list as $value){
$tables[] = $value[0];
}
return $tables;
}
private function get_dbhead($table = '')
{
$sql = "SHOW CREATE TABLE `{$table}`";
$ddl = $this->query($sql)[0][1] . ';';
return $ddl;
}
private function get_dbdata($table = '')
{
$sql = "SHOW COLUMNS FROM `{$table}`";
$list = $this->query($sql);
//字段
$columns = '';
//需要傳回的SQL
$query = '';
foreach ($list as $value){
$columns .= "`{$value[0]}`,";
}
$columns = substr($columns, 0, -1);
$data = $this->query("SELECT * FROM `{$table}`");
foreach ($data as $value){
$dataSql = '';
foreach ($value as $v){
$dataSql .= "'{$v}',";
}
$dataSql = substr($dataSql, 0, -1);
$query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});\r\n";
}
return $query;
}
private function writeToFile($tables = array(), $ddl = array(), $data = array())
{
$str = "\r\n";
$str .= "SET FOREIGN_KEY_CHECKS=0;\r\n";
$i = 0;
foreach ($tables as $table)
{
$str .= "-- ----------------------------\r\n";
$str .= "-- Table structure for {$table}\r\n";
$str .= "-- ----------------------------\r\n";
$str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
$str .= $ddl[$i] . "\r\n";
$str .= "-- ----------------------------\r\n";
$str .= "-- Records of {$table}\r\n";
$str .= "-- ----------------------------\r\n";
$str .= $data[$i] . "\r\n";
$i++;
}
if(!file_exists($this->config['path'])){mkdir($this->config['path']);}
return file_put_contents($this->config['path'].$this->config['sqlbakname'], $str) ? '備份成功!花費時間' . round(microtime(true) - $this->begin,2) . 'ms' : '備份失敗!';
}
private function setTables($tables = array())
{
if (!empty($tables) && is_array($tables)){
//備份指定表
$this->tables = $tables;
}else{
//備份全部表
$this->tables = $this->get_dbname();
}
}
public function backup($tables = array())
{
//存儲表定義語句的數組
$ddl = array();
//存儲資料的數組
$data = array();
$this->setTables($tables);
if (!empty($this->tables)){
foreach ($this->tables as $table){
$ddl[] = $this->get_dbhead($table);
$data[] = $this->get_dbdata($table);
}
//開始寫入
return $this->writeToFile($this->tables, $ddl, $data);
}else{
$this->error = '資料庫中沒有表!';
return false;
}
}
public function getError()
{
return $this->error;
}
public function restore($filename = '')
{
$path=$this->config['path'].$filename;
if (!file_exists($path))
{
$this->error('SQL檔案不存在!');
return false;
}
else
{
$sql = $this->parseSQL($path);
//dump($sql);die;
try
{
$this->handler->exec($sql);
return '還原成功!花費時間'. round(microtime(true) - $this->begin,2) . 'ms';
}
catch (PDOException $e)
{
$this->error = $e->getMessage();
return false;
}
}
}
private function parseSQL($path = '')
{
$sql = file_get_contents($path);
$sql = explode("\r\n", $sql);
//先消除--注釋
$sql = array_filter($sql, function ($data)
{
if (empty($data) || preg_match('/^--.*/', $data))
{
return false;
}
else
{
return true;
}
});
$sql = implode('', $sql);
//删除注釋
$sql = preg_replace('/\/\*.*\*\//', '', $sql);
return $sql;
}
public function downloadFile($fileName) {
$fileName=$this->config['path'].$fileName;
if (file_exists($fileName)){
ob_end_clean();
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Length: ' . filesize($fileName));
header('Content-Disposition: attachment; filename=' . basename($fileName));
readfile($fileName);
}else{
$this->error="檔案有錯誤！";
}
}
private function getfiletime($file){
$path=$this->config['path'].$file;
$a = filemtime($path);
$time = date("Y-m-d H:i:s", $a);
return $time;
}
private function getfilesize($file){
$perms=stat($this->config['path'].$file);
$size = $perms['size'];
$a = ['B', 'KB', 'MB', 'GB', 'TB'];
$pos = 0;
while ($size >= 1024) {
$size /= 1024;
$pos++;
}
return round($size, 2). $a[$pos];
}
public function get_filelist($Order = 0) {
$FilePath=$this->config['path'];
$FilePath = opendir($FilePath);
$FileAndFolderAyy=array();
$i=1;
while (false !== ($filename = readdir($FilePath))) {
if ($filename!="." && $filename!=".."){
$i++;
$FileAndFolderAyy[$i]['name'] = $filename;
$FileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
$FileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
}
}
$Order == 0 ? sort($FileAndFolderAyy) : rsort($FileAndFolderAyy);
return $FileAndFolderAyy;
}
public function delfilename($filename){
$path=$this->config['path'].$filename;
if (@unlink($path)) {return '删除成功';}
}
}
?>
使用方法
namespace app\admin\controller;
use think\Controller;
use think\db\Query;
class Database extends Common
{
public function _initialize()
{
parent::_initialize();
}
public function index()
{
$dataList = db()->query("SHOW TABLE STATUS");
$this->assign('dataList', $dataList);
return $this->fetch();
}
public function backup()
{
if (request()->isPost()){
$id = input('id');
if (isset($id) && !empty($id)){
$table_arr = explode(',', $id); //備份資料表
$sql = new \expand\Baksql(\think\Config::get("database"));
$res = $sql->backup($table_arr);
return ajaxReturn($res, url('index'));
}
}
}
public function reduction()
{
$sql = new \expand\Baksql(\think\Config::get("database"));
$dataList = $sql->get_filelist();
$this->assign('dataList', $dataList);
return $this->fetch();
}
public function restore()
{
if (request()->isPost()){
$name = input('id');
$sql = new \expand\Baksql(\think\Config::get("database"));
$res = $sql->restore($name);
return ajaxReturn($res, url('reduction'));
}
}
public function dowonload()
{
$table = input('table');
$sql = new \expand\Baksql(\think\Config::get("database"));
$sql->downloadFile($table);
}
public function delete()
{
if (request()->isPost()){
$name = input('id');
$sql = new \expand\Baksql(\think\Config::get("database"));
$res = $sql->delfilename($name);
return ajaxReturn($res, url('reduction'));
}
}
}
評論一下
贊助站長
贊助站長X
版權申明：此文如未标注轉載均為本站原創，自由轉載請表明出處《龍行部落格》。
本文網址：https://www.liaotaoo.cn/149.html