<?php
/**
* Database backup Restore class
* @author xialeistudio<admin@xialeistudio.net>
* Class Databasetool
*/
Class Databasetool
{
Private $handler;
Private $config = Array (
' Host ' => ' localhost ',
' Port ' => 3306,
' User ' => ' root ',
' Password ' => ',
' Database ' => ' test ',
' CharSet ' => ' Utf-8 ',
' Target ' => ' sql.sql '
);
Private $tables = Array ();
Private $error;
Private $begin; Start time
/**
* Schema method
* @param array $config
*/
Public function __construct ($config = Array ())
{
$this->begin = Microtime (true);
$config = Is_array ($config)? $config: Array ();
$this->config = Array_merge ($this->config, $config);
Start PDO Connection
if (! $this->handler instanceof PDO)
{
Try
{
$this->handler = new PDO ("mysql:host={$this->config[' host ']}:{$this->config[' Port '};d bname={$this-> config[' database '} ', $this->config[' user ', $this->config[' password ']);
}
catch (Pdoexception $e)
{
$this->error = $e->getmessage ();
return false;
}
catch (Exception $e)
{
$this->error = $e->getmessage ();
return false;
}
}
}
/**
* Backup
* @param array $tables
* @return BOOL
*/
public Functi On backup ($tables = Array ())
{
//Storage table definition statement array
$DDL = Array ();
//the array of stored data
$data = Array ();
$this->settables ($tables);
if (!empty ($this->tables))
{
foreach ($this->tables as $ Table)
{
$ddl [] = $this->getddl ($table);
$data [] = $this->getdata ($table);
}
//start writing
$this->writetofile ($this->tables, $DDL, $data);
&NBSP;&NBSP}
else
{
$this->error = ' database does not have a table! '
return false;
}
}
/**
* Set up the tables to be backed up
* @param array $tables
*/
Private Function settables ($tables = Array ())
{
if (!empty ($tables) && Is_array ($tables))
{
Backing up specified tables
$this->tables = $tables;
}
Else
{
Back up all Tables
$this->tables = $this->gettables ();
}
}
/**
* Inquiry
* @param string $sql
* @return Mixed
*/
Private Function query ($sql = ')
{
$stmt = $this->handler->query ($sql);
$stmt->setfetchmode (Pdo::fetch_num);
$list = $stmt->fetchall ();
return $list;
}
/**
* Get all Tables
* @return Array
*/
Private Function Gettables ()
{
$sql = ' show TABLES ';
$list = $this->query ($sql);
$tables = Array ();
foreach ($list as $value)
{
$tables [] = $value [0];
}
return $tables;
}
/**
* Get Table definition statement
* @param string $table
* @return Mixed
*/
Private Function getddl ($table = ')
{
$sql = "Show CREATE TABLE ' {$table} '";
$DDL = $this->query ($sql) [0][1]. ';';
return $DDL;
}
/**
* Get Table data
* @param string $table
* @return Mixed
*/
Private Function getData ($table = ')
{
$sql = "Show COLUMNS from ' {$table} '";
$list = $this->query ($sql);
Field
$columns = ';
The SQL that needs to be returned
$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;
}
/**
* Write to File
* @param array $tables
* @param array $ddl
* @param array $data
*/
Private Function WriteToFile ($tables = Array (), $ddl = Array (), $data = Array ())
{
$str = "/*\r\nmysql Database Backup tools\r\n";
$str. = "server:{$this->config[' host ']}:{$this->config[' Port ']}\r\n";
$str. = "database:{$this->config[' Database ']}\r\n";
$str. = "Data:". Date (' y-m-d h:i:s ', Time ()). "\r\n*/\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 + +;
}
Echo file_put_contents ($this->config[' target '), $STR)? ' Backup successful! ' (Microtime (True)-$this->begin). ' MS ': ' Backup failed! '
}
/**
* Error message
* @return Mixed
*/
Public Function GetError ()
{
return $this->error;
}
Public Function Restore ($path = ')
{
if (!file_exists ($path))
{
$this->error (' SQL file does not exist! ');
return false;
}
Else
{
$sql = $this->parsesql ($path);
Try
{
$this->handler->exec ($sql);
Echo ' Restore succeeded! Take time ', (Microtime (True)-$this->begin). ' MS ';
}
catch (Pdoexception $e)
{
$this->error = $e->getmessage ();
return false;
}
}
}
/**
* Parse SQL file as an array of SQL statements
* @param string $path
* @return array|mixed|string
*/
Private Function parsesql ($path = ')
{
$sql = file_get_contents ($path);
$sql = Explode ("\ r \ n", $sql);
Eliminate first--note
$sql = Array_filter ($sql, function ($data)
{
if (Empty ($data) | | | preg_match ('/^--. * *, $data))
{
return false;
}
Else
{
return true;
}
});
$sql = Implode (", $sql);
Delete/**/Comments
$sql = preg_replace ('/\/\*.*\*\//', ', ', $sql);
return $sql;
}
}