Created
October 23, 2020 20:38
-
-
Save glpzzz/90433c21a345b6965931177c78412df3 to your computer and use it in GitHub Desktop.
yii-dump-db
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
namespace common\models; | |
/** | |
* Creates DB dump. | |
* | |
* @version 0.1 | |
* @author fadeevms. Fork of https://bitbucket.org/rodzadra/yii-dump-db Rodrigo Zadra Armond <rodzadra@gmail.com> | |
* | |
*/ | |
use yii; | |
class DumpDb | |
{ | |
private $constraints; | |
private $db = NULL; | |
private $db_connected = NULL; | |
//max size of the query in megabytes - this value will be multiplied by 1024. | |
private $max_query_size = 50; | |
//this can be useful when a view is defined by/for a different user or | |
//that doesn't exists on the DB which this SQL will be imported | |
private $removeViewDefiner = FALSE; | |
private $excludeTables = []; | |
/** | |
* construct class | |
* | |
* @param string $dsn - 'mysql:host=[REMOTE_HOST];dbname=[DATABASE]' | |
* @param string $username - username | |
* @param string $password - password | |
*/ | |
public function __construct($dsn = NULL, $username = NULL, $password = NULL) | |
{ | |
if (is_null($dsn) || is_null($username) || is_null($password)) { | |
$this->db = \Yii::$app->db; | |
} else { | |
$this->db = new \yii\db\Connection([ | |
'dsn' => $dsn, | |
'username' => $username, | |
'password' => $password, | |
]); | |
$this->db->open(); | |
} | |
$this->max_query_size = $this->max_query_size * 1024; | |
$this->db_connected = $this->db->createCommand('SELECT DATABASE() AS db')->queryAll()[0]['db']; | |
} | |
/** | |
* set/unset the parameter that remove the DEFINER option (USER) from | |
* generated "create view" params | |
* | |
* @param bool $b - default FALSE | |
* @return bool - the value of $this->removeViewdefiner | |
*/ | |
public function setRemoveViewDefinerSecurity($b = FALSE) | |
{ | |
$this->removeViewDefiner = ($b === true) ? $b : FALSE; | |
return $this->removeViewDefiner; | |
} | |
/** | |
* set the max kilobyte the generated query can have | |
* | |
* @param integet $size - default 50(Mb) | |
* @return void | |
*/ | |
public function setMaxQuerySize($size = 50) | |
{ | |
if (!isset($size)) | |
return; | |
$this->max_query_size = (int)$size * 1024; | |
return; | |
} | |
/** | |
* return the max size (Kb) the generated query can have | |
* | |
* | |
* @return int | |
*/ | |
public function getMaxQuerySize() | |
{ | |
return (int)$this->max_query_size . "Mb"; | |
} | |
/** | |
* Dump all tables | |
* @param boolean $download - if the generated data is to be sent to browser | |
* @param string $params - additional params | |
* @return file|strings | |
*/ | |
public function getDump($download = TRUE, $params = []) | |
{ | |
if (isset($params['exclude_tables'])) { | |
$this->excludeTables = array_map("trim", explode(',', $params['exclude_tables'])); | |
} | |
ob_start(); | |
foreach ($this->getTables() as $val) { | |
$this->dumpTable($val['name']); | |
} | |
$result = $this->setHeader(); | |
$result .= ob_get_contents(); | |
$result .= $this->getViews(); | |
$result .= $this->getConstraints(); | |
$result .= $this->setFooter(); | |
ob_end_clean(); | |
if ($download) { | |
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); | |
header("Cache-Control: no-cache"); | |
header("Pragma: no-cache"); | |
header("Content-type:application/sql"); | |
header("Content-Disposition:attachment;filename=downloaded.sql"); | |
} | |
return $result; | |
} | |
/** | |
* Generate constraints to all tables | |
* @return string | |
*/ | |
private function getConstraints() | |
{ | |
$sql = "--\r\n-- Constraints for dumped tables\r\n--" . PHP_EOL . PHP_EOL; | |
$first = TRUE; | |
foreach ($this->constraints as $key => $value) { | |
if (!in_array($key, $this->excludeTables)) { | |
if ($first && count($value[0]) > 0) { | |
$sql .= "--\r\n-- Constraints for table $key\r\n--" . PHP_EOL . PHP_EOL; | |
$sql .= "ALTER TABLE $key" . PHP_EOL; | |
} | |
if (count($value[0]) > 0) { | |
for ($i = 0; $i < count($value[0]); $i++) { | |
if (strpos($value[0][$i], 'CONSTRAINT') === FALSE) | |
$sql .= preg_replace('/(FOREIGN[\s]+KEY)/', " ADD $1", $value[0][$i]); | |
else | |
$sql .= preg_replace('/(CONSTRAINT)/', " ADD $1", $value[0][$i]); | |
if ($i == count($value[0]) - 1) | |
$sql .= ";" . PHP_EOL; | |
if ($i < count($value[0]) - 1) | |
$sql .= PHP_EOL; | |
} | |
} | |
} | |
} | |
return $sql; | |
} | |
/** | |
* Set sql file header | |
* @return string | |
*/ | |
private function setHeader() | |
{ | |
$header = PHP_EOL . "--\n--lDate: " . date('Y-m-d H:i:s') . "\n--" . PHP_EOL . PHP_EOL; | |
$header .= PHP_EOL . "--\n-- DATABASE: " . $this->db_connected . PHP_EOL . PHP_EOL; | |
$header .= PHP_EOL . "--\n-- Disable foreign key checks, autocommit and start a transaction\n--" . PHP_EOL . PHP_EOL; | |
$header .= "SET FOREIGN_KEY_CHECKS=0;" . PHP_EOL; | |
$header .= "SET SQL_MODE=\"NO_AUTO_VALUE_ON_ZERO\";" . PHP_EOL; | |
$header .= "SET AUTOCOMMIT=0;" . PHP_EOL; | |
$header .= "START TRANSACTION;" . PHP_EOL . PHP_EOL; | |
$header .= "/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;" . PHP_EOL; | |
$header .= "/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;" . PHP_EOL; | |
$header .= "/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;" . PHP_EOL; | |
$header .= "/*!40101 SET NAMES utf8 */;" . PHP_EOL; | |
return $header; | |
} | |
/** | |
* Set sql file footer | |
* @return string | |
*/ | |
private function setFooter() | |
{ | |
$footer = PHP_EOL . "SET FOREIGN_KEY_CHECKS=1;" . PHP_EOL; | |
$footer .= "COMMIT;" . PHP_EOL . PHP_EOL; | |
$footer .= "/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;" . PHP_EOL; | |
$footer .= "/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;" . PHP_EOL; | |
$footer .= "/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;" . PHP_EOL; | |
return $footer; | |
} | |
/** | |
* Create table dump | |
* @param $tableName | |
* @return mixed | |
*/ | |
private function dumpTable($tableName) | |
{ | |
$pdo = $this->db->pdo; | |
echo PHP_EOL . "--\n-- Table structure for table `$tableName`\n--" . PHP_EOL; | |
echo PHP_EOL . 'DROP TABLE IF EXISTS ' . $this->db->quoteTableName($tableName) . ';' . PHP_EOL . PHP_EOL; | |
$q = $this->db->createCommand('SHOW CREATE TABLE ' . $this->db->quoteTableName($tableName) . ';')->queryOne(); | |
if (!isset($q['Create Table'])) | |
return; | |
$create_query = $q['Create Table']; | |
$pattern = '/CONSTRAINT.*|FOREIGN[\s]+KEY/'; | |
// constraints to $tablename | |
preg_match_all($pattern, $create_query, $this->constraints[$this->db->quoteTableName($tableName)]); | |
$create_query = preg_replace('/CREATE TABLE/', 'CREATE TABLE IF NOT EXISTS', $create_query); | |
$create_query = explode("\n", $create_query); | |
$create_query_size = count($create_query); | |
for ($i = 0; $i < $create_query_size - 1; $i++) { | |
if (preg_match($pattern, $create_query[$i + 1])) { | |
echo preg_replace('/\,$/', '', $create_query[$i]) . PHP_EOL; | |
break; | |
} else | |
echo $create_query[$i] . PHP_EOL; | |
} | |
echo trim($create_query[$create_query_size - 1]) . ";" . PHP_EOL; | |
$rows = $this->db->createCommand('SELECT * FROM ' . $this->db->quoteTableName($tableName) . ';')->queryAll(); | |
if (empty($rows)) { | |
echo PHP_EOL; | |
echo "-- --------------------------------------------------------" . PHP_EOL; | |
return; | |
} | |
echo PHP_EOL . "--\n-- Dumping data for table `$tableName`\n--" . PHP_EOL . PHP_EOL; | |
$attrs = array_map(array($this->db, 'quoteColumnName'), array_keys($rows[0])); | |
$insert_head = 'INSERT INTO ' . $this->db->quoteTableName($tableName) . '' . " (" . implode(', ', $attrs) . ') VALUES'; | |
echo $insert_head . PHP_EOL; | |
$i = 0; | |
$query_size = 0; | |
$rowsCount = count($rows); | |
foreach ($rows as $row) { | |
// Process row | |
foreach ($row as $key => $value) { | |
if ($value === null) | |
$row[$key] = 'NULL'; | |
else | |
$row[$key] = $pdo->quote($value); | |
} | |
$imploded_row = " (" . implode(', ', $row) . ')'; | |
echo $imploded_row; | |
$query_size += strlen($imploded_row); | |
if ($i < $rowsCount - 1) { | |
if ($query_size <= $this->max_query_size) { | |
echo ','; | |
} else { | |
echo ';' . PHP_EOL; | |
echo $insert_head; | |
$query_size = 0; | |
} | |
} else { | |
echo ';'; | |
} | |
echo PHP_EOL; | |
$i++; | |
} | |
echo PHP_EOL; | |
echo "-- --------------------------------------------------------"; | |
echo PHP_EOL; | |
} | |
/** | |
* creates the views schema | |
* | |
* @return string | |
*/ | |
private function getViews() | |
{ | |
$result = NULL; | |
$db_views = $this->db->createCommand('SHOW FULL TABLES IN `' . $this->db_connected . '` WHERE TABLE_TYPE LIKE "VIEW";')->queryAll(); | |
foreach ($db_views as $view) { | |
$the_view = $view['Tables_in_' . $this->db_connected]; | |
$result .= PHP_EOL . "--\n-- Structure for view `" . $the_view . "`\n--" . PHP_EOL; | |
$create_view = $this->db->createCommand('SHOW CREATE VIEW `' . $the_view . '`')->queryAll(); | |
foreach ($create_view as $create) { | |
$result .= PHP_EOL . 'DROP TABLE IF EXISTS ' . $this->db->quoteTableName($the_view) . ';' . PHP_EOL . PHP_EOL; | |
if ($this->removeViewDefiner) | |
$result .= preg_replace("/DEFINER=`\w+`@`.` /", '', $create['Create View']); | |
else | |
$result .= $create['Create View']; | |
$result .= ";" . PHP_EOL; | |
$result .= PHP_EOL; | |
$result .= "-- --------------------------------------------------------"; | |
$result .= PHP_EOL; | |
} | |
} | |
return $result; | |
} | |
/** | |
* Get mysql tables list | |
* @return array | |
*/ | |
private function getTables() | |
{ | |
$tables = $this->db->createCommand('SHOW FULL TABLES IN `' . $this->db_connected . '` WHERE TABLE_TYPE LIKE "BASE TABLE";')->queryAll(); | |
$result = array(); | |
foreach ($tables as $table) { | |
if (!in_array($table['Tables_in_' . $this->db_connected], $this->excludeTables)) { | |
$result[]['name'] = $table['Tables_in_' . $this->db_connected]; | |
} | |
} | |
return $result; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment