» » » How to export database from Yii2 application

How to export database from Yii2 application

Facebook
Google+
https://applerinquest.com/how-to-export-database-from-yii2-application">
LinkedIn
Follow by Email
9 min read

The export database feature is nice to have for backup and restore the database from either website or web application. I added the export database in my application using the PHP based MySQL dump library. I did the search and found more two ways to achieve my goal but they have the issue with the null value and text data type. One of the two ways are to generate the insert script with PHP which can not generate the insert script with null value.

 

Here what I did to make it works in Yii2

  • Download the file from here
  • In controllers folder, create the ShutterExport folder. You can name the folder or change the structure directory you want.
  • Copy the dumper.php which comes with the downloaded file and add under the ShutterExport folder.
  • For Yii2, you must add the namespace at the top of the dumper.php. I add the code below.
<?php
    namespace frontend\controllers\ShuttleExport;
?>
  • In the dumper.php, it uses the PHP exception. So in order to make it works in Yii2, we need to import the Exception from Yii2. I add the code below.
<?php
    namespace frontend\controllers\ShuttleExport;  
    use yii\base\Exception;
?>
  • If you use the dumper.php now, you may have the PHP syntax error with class initializing. The error comes from create static function. You just change the origin code to new code below.
<?php
	static function create($options) {
		if (class_exists('mysqli')) {
          // # original from source code
          // $class_name = "Shuttle_DBConn_Mysqli";
          $dbConn = new Shuttle_DBConn_Mysqli($options);
		} else {
          // # original from source code<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>
          // $class_name = "Shuttle_DBConn_Mysql";
          $dbConn = new Shuttle_DBConn_Mysql($options);
		}

        // # original from source code
        // return new $class_name($options);
        return $dbConn;
	}
?>

 

Here is the final code in the dumper.php

<?php 
    namespace frontend\controllers\ShuttleExport;  
    use yii\base\Exception;

/**
 * Abstract dump file: provides common interface for writing
 * data to dump files. 
 */
abstract class Shuttle_Dump_File {
	/**
	 * File Handle
	 */
	protected $fh;

	/**
	 * Location of the dump file on the disk
	 */
	protected $file_location;

	abstract function write($string);
	abstract function end();

	static function create($filename) {
		if (self::is_gzip($filename)) {
			return new Shuttle_Dump_File_Gzip($filename);
		}
		return new Shuttle_Dump_File_Plaintext($filename);
	}
	function __construct($file) {
		$this->file_location = $file;
		$this->fh = $this->open();

		if (!$this->fh) {
			throw new Shuttle_Exception("Couldn't create gz file");
		}
	}

	public static function is_gzip($filename) {
		return preg_match('~gz$~i', $filename);
	}	
}

/**
 * Plain text implementation. Uses standard file functions in PHP. 
 */
class Shuttle_Dump_File_Plaintext extends Shuttle_Dump_File {
	function open() {
		return fopen($this->file_location, 'w');
	}
	function write($string) {
		return fwrite($this->fh, $string);
	}
	function end() {
		return fclose($this->fh);
	}
}

/**
 * Gzip implementation. Uses gz* functions. 
 */
class Shuttle_Dump_File_Gzip extends Shuttle_Dump_File {
	function open() {
		return gzopen($this->file_location, 'wb9');
	}
	function write($string) {
		return gzwrite($this->fh, $string);
	}
	function end() {
		return gzclose($this->fh);
	}
}

/**
 * MySQL insert statement builder. 
 */
class Shuttle_Insert_Statement {
	private $rows = array();
	private $length = 0;
	private $table;

	function __construct($table) {
		$this->table = $table;
	}

	function reset() {
		$this->rows = array();
		$this->length = 0;
	}

	function add_row($row) {
		$row = '(' . implode(",", $row) . ')';
		$this->rows[] = $row;
		$this->length += strlen($row);
	}

	function get_sql() {
		if (empty($this->rows)) {
			return false;
		}

		return 'INSERT INTO `' . $this->table . '` VALUES ' . 
			implode(",\n", $this->rows) . '; ';
	}

	function get_length() {
		return $this->length;
	}
}

/**
 * Main facade
 */
abstract class Shuttle_Dumper {
	/**
	 * Maximum length of single insert statement
	 */
	const INSERT_THRESHOLD = 838860;
	
	/**
	 * @var Shuttle_DBConn
	 */	
	public $db;

	/**
	 * @var Shuttle_Dump_File
	 */
	public $dump_file;

	/**
	 * End of line style used in the dump
	 */
	public $eol = "\r\n";

	/**
	 * Specificed tables to include
	 */
	public $include_tables;

	/**
	 * Specified tables to exclude
	 */
	public $exclude_tables = array();

	/**
	 * Factory method for dumper on current hosts's configuration. 
	 */
	static function create($db_options) {
		$db = Shuttle_DBConn::create($db_options);

		$db->connect();

		if (self::has_shell_access() 
				&& self::is_shell_command_available('mysqldump')
				&& self::is_shell_command_available('gzip')
			) {
			$dumper = new Shuttle_Dumper_ShellCommand($db);
		} else {
			$dumper = new Shuttle_Dumper_Native($db);
		}

		if (isset($db_options['include_tables'])) {
			$dumper->include_tables = $db_options['include_tables'];
		}
		if (isset($db_options['exclude_tables'])) {
			$dumper->exclude_tables = $db_options['exclude_tables'];
		}

		return $dumper;
	}

	function __construct(Shuttle_DBConn $db) {
		$this->db = $db;
	}

	public static function has_shell_access() {
		if (!is_callable('shell_exec')) {
			return false;
		}
		$disabled_functions = ini_get('disable_functions');
		return stripos($disabled_functions, 'shell_exec') === false;
	}

	public static function is_shell_command_available($command) {
		if (preg_match('~win~i', PHP_OS)) {
			/*
			On Windows, the `where` command checks for availabilty in PATH. According
			to the manual(`where /?`), there is quiet mode: 
			....
			    /Q       Returns only the exit code, without displaying the list
			             of matched files. (Quiet mode)
			....
			*/
			$output = array();
			exec('where /Q ' . $command, $output, $return_val);

			if (intval($return_val) === 1) {
				return false;
			} else {
				return true;
			}

		} else {
			$last_line = exec('which ' . $command);
			$last_line = trim($last_line);

			// Whenever there is at least one line in the output, 
			// it should be the path to the executable
			if (empty($last_line)) {
				return false;
			} else {
				return true;
			}
		}
		
	}

	/**
	 * Create an export file from the tables with that prefix.
	 * @param string $export_file_location the file to put the dump to.
	 *		Note that whenever the file has .gz extension the dump will be comporessed with gzip
	 * @param string $table_prefix Allow to export only tables with particular prefix
	 * @return void
	 */
	abstract public function dump($export_file_location, $table_prefix='');

	protected function get_tables($table_prefix) {
		if (!empty($this->include_tables)) {
			return $this->include_tables;
		}
		
		// $tables will only include the tables and not views.
		// TODO - Handle views also, edits to be made in function 'get_create_table_sql' line 336
		$tables = $this->db->fetch_numeric('
			SHOW FULL TABLES WHERE Table_Type = "BASE TABLE" AND Tables_in_'.$this->db->name.' LIKE "' . $this->db->escape_like($table_prefix) . '%"
		');

		$tables_list = array();
		foreach ($tables as $table_row) {
			$table_name = $table_row[0];
			if (!in_array($table_name, $this->exclude_tables)) {
				$tables_list[] = $table_name;
			}
		}
		return $tables_list;
	}
}

class Shuttle_Dumper_ShellCommand extends Shuttle_Dumper {
	function dump($export_file_location, $table_prefix='') {
		$command = 'mysqldump -h ' . escapeshellarg($this->db->host) .
			' -u ' . escapeshellarg($this->db->username) . 
			' --password=' . escapeshellarg($this->db->password) . 
			' ' . escapeshellarg($this->db->name);

		$include_all_tables = empty($table_prefix) &&
			empty($this->include_tables) &&
			empty($this->exclude_tables);

		if (!$include_all_tables) {
			$tables = $this->get_tables($table_prefix);
			$command .= ' ' . implode(' ', array_map('escapeshellarg', $tables));
		}

		$error_file = tempnam(sys_get_temp_dir(), 'err');

		$command .= ' 2> ' . escapeshellarg($error_file);

		if (Shuttle_Dump_File::is_gzip($export_file_location)) {
			$command .= ' | gzip';
		}

		$command .= ' > ' . escapeshellarg($export_file_location);

		exec($command, $output, $return_val);

		if ($return_val !== 0) {
			$error_text = file_get_contents($error_file);
			unlink($error_file);
			throw new Shuttle_Exception('Couldn\'t export database: ' . $error_text);
		}

		unlink($error_file);
	}
}

class Shuttle_Dumper_Native extends Shuttle_Dumper {
	public function dump($export_file_location, $table_prefix='') {
		$eol = $this->eol;

		$this->dump_file = Shuttle_Dump_File::create($export_file_location);

		$this->dump_file->write("-- Generation time: " . date('r') . $eol);
		$this->dump_file->write("-- Host: " . $this->db->host . $eol);
		$this->dump_file->write("-- DB name: " . $this->db->name . $eol);
		$this->dump_file->write("/*!40030 SET NAMES UTF8 */;$eol");
		
		$this->dump_file->write("/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;$eol");
		$this->dump_file->write("/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;$eol");
		$this->dump_file->write("/*!40101 SET @[email protected]@COLLATION_CONNECTION */;$eol");
		$this->dump_file->write("/*!40103 SET @OLD_TI[email protected]@TIME_ZONE */;$eol");
		$this->dump_file->write("/*!40103 SET TIME_ZONE='+00:00' */;$eol");
		$this->dump_file->write("/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;$eol");
		$this->dump_file->write("/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;$eol");
		$this->dump_file->write("/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;$eol");
		$this->dump_file->write("/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;$eol$eol");


		$tables = $this->get_tables($table_prefix);
		foreach ($tables as $table) {
			$this->dump_table($table);
		}
		
		$this->dump_file->write("$eol$eol");
		$this->dump_file->write("/*!40101 SET [email protected]_SQL_MODE */;$eol");
		$this->dump_file->write("/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;$eol");
		$this->dump_file->write("/*!40014 SET [email protected]_UNIQUE_CHECKS */;$eol");
		$this->dump_file->write("/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;$eol");
		$this->dump_file->write("/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;$eol");
		$this->dump_file->write("/*!40101 SET [email protected]_COLLATION_CONNECTION */;$eol");
		$this->dump_file->write("/*!40111 SET [email protected]_SQL_NOTES */;$eol$eol");

		unset($this->dump_file);
	}

	protected function dump_table($table) {
		$eol = $this->eol;

		$this->dump_file->write("DROP TABLE IF EXISTS `$table`;$eol");

		$create_table_sql = $this->get_create_table_sql($table);
		$this->dump_file->write($create_table_sql . $eol . $eol);

		$data = $this->db->query("SELECT * FROM `$table`");

		$insert = new Shuttle_Insert_Statement($table);

		while ($row = $this->db->fetch_row($data)) {
			$row_values = array();
			foreach ($row as $value) {
				$row_values[] = $this->db->escape($value);
			}
			$insert->add_row( $row_values );

			if ($insert->get_length() > self::INSERT_THRESHOLD) {
				// The insert got too big: write the SQL and create
				// new insert statement
				$this->dump_file->write($insert->get_sql() . $eol);
				$insert->reset();
			}
		}

		$sql = $insert->get_sql();
		if ($sql) {
			$this->dump_file->write($insert->get_sql() . $eol);
		}
		$this->dump_file->write($eol . $eol);
	}
	
	public function get_create_table_sql($table) {
		$create_table_sql = $this->db->fetch('SHOW CREATE TABLE `' . $table . '`');
		return $create_table_sql[0]['Create Table'] . ';';
	}
}

class Shuttle_DBConn {
	public $host;
	public $username;
	public $password;
	public $name;

	protected $connection;

	function __construct($options) {
		$this->host = $options['host'];
		if (empty($this->host)) {
			$this->host = '127.0.0.1';
		}
		$this->username = $options['username'];
		$this->password = $options['password'];
		$this->name = $options['db_name'];
	}

	static function create($options) {
		if (class_exists('mysqli')) {
      // # original from source code
      // $class_name = "Shuttle_DBConn_Mysqli";
      $dbConn = new Shuttle_DBConn_Mysqli($options);
		} else {
      // # original from source code
      // $class_name = "Shuttle_DBConn_Mysql";
      $dbConn = new Shuttle_DBConn_Mysql($options);
		}

    // # original from source code
    // return new $class_name($options);
    return $dbConn;
	}
}

class Shuttle_DBConn_Mysql extends Shuttle_DBConn {
	function connect() {
		$this->connection = @mysql_connect($this->host, $this->username, $this->password);
		if (!$this->connection) {
			throw new Shuttle_Exception("Couldn't connect to the database: " . mysql_error());
		}

		$select_db_res = mysql_select_db($this->name, $this->connection);
		if (!$select_db_res) {
			throw new Shuttle_Exception("Couldn't select database: " . mysql_error($this->connection));
		}

		return true;
	}

	function query($q) {
		if (!$this->connection) {
			$this->connect();
		}
		$res = mysql_query($q);
		if (!$res) {
			throw new Shuttle_Exception("SQL error: " . mysql_error($this->connection));
		}
		return $res;
	}

	function fetch_numeric($query) {
		return $this->fetch($query, MYSQL_NUM);
	}

	function fetch($query, $result_type=MYSQL_ASSOC) {
		$result = $this->query($query, $this->connection);
		$return = array();
		while ( $row = mysql_fetch_array($result, $result_type) ) {
			$return[] = $row;
		}
		return $return;
	}

	function escape($value) {
		if (is_null($value)) {
			return "NULL";
		}
		return "'" . mysql_real_escape_string($value) . "'";
	}

	function escape_like($search) {
		return str_replace(array('_', '%'), array('\_', '\%'), $search);
	}

	function get_var($sql) {
		$result = $this->query($sql);
		$row = mysql_fetch_array($result);
		return $row[0];
	}

	function fetch_row($data) {
		return mysql_fetch_assoc($data);
	}
}


class Shuttle_DBConn_Mysqli extends Shuttle_DBConn {
	function connect() {
    // # original from source code
    //$this->connection = @new MySQLi($this->host, $this->username, $this->password, $this->name);
    
    // # apple adds this connection for Yii2
    try {
      $this->connection = new \yii\db\Connection([
        'dsn' => 'mysqli:host='.$this->host.';dbname='.$this->name,
        'username' => $this->username,
        'password' => $this->password,
      ]);
    } catch (Exception $e) {
      throw new Shuttle_Exception("Couldn't connect to the database: " . $e);
    }

    // # original from source code
		//if ($this->connection->connect_error) {
		//	throw new Shuttle_Exception("Couldn't connect to the database: " . $this->connection->connect_error);
		//}

		return true;
	}

	function query($q) {
		if (!$this->connection) {
			$this->connect();
		}
		$res = $this->connection->query($q);
		
		if (!$res) {
			throw new Shuttle_Exception("SQL error: " . $this->connection->error);
		}
		
		return $res;
	}

	function fetch_numeric($query) {
		return $this->fetch($query, MYSQLI_NUM);
	}

	function fetch($query, $result_type=MYSQLI_ASSOC) {
		$result = $this->query($query, $this->connection);
		$return = array();
		while ( $row = $result->fetch_array($result_type) ) {
			$return[] = $row;
		}
		return $return;
	}

	function escape($value) {
		if (is_null($value)) {
			return "NULL";
		}
		return "'" . $this->connection->real_escape_string($value) . "'";
	}

	function escape_like($search) {
		return str_replace(array('_', '%'), array('\_', '\%'), $search);
	}

	function get_var($sql) {
		$result = $this->query($sql);
		$row = $result->fetch_array($result, MYSQLI_NUM);
		return $row[0];
	}

	function fetch_row($data) {
		return $data->fetch_array(MYSQLI_ASSOC);
	}
}

class Shuttle_Exception extends Exception {};

?>

 

Now in order to call the action, I need to create the the new Utilities controller class and add the Export action.

Controllers

  • UtilitiesController.php
    • actionExport()

Here is my code in my UtilitiesController.php

<?php

namespace frontend\controllers;

use yii\web\Controller;
use yii\filters\VerbFilter;
use yii\filters\AccessControl;
use yii; 

// # Export the database from dump DB
use frontend\controllers\ShuttleExport\Dumper;

class UtilitiesController extends Controller
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            'access' => [
                'class' => AccessControl::className(),
                'only' => ['index', 'create', 'delete', 'update', 'view'], 
                'rules' => [
                    [
                        'actions' => ['index', 'create', 'delete', 'update', 'view'], 
                        'allow' => true,
                        'roles' => ['@'], 
                    ],
                ],
                'denyCallback' => function ($rule, $action) {
            //-- You are not allowed to access this page. denyCallback function is executed if no rules are mathced.
            $this->goHome();
        }
            ],               
            'verbs' => [
                'class' => VerbFilter::className(),
                'actions' => [
                    'delete' => ['POST'],
                ],
            ],
        ];
    }


    /**
     * @return mixed
     */
    public function actionIndex()
    {
       return $this->render('index');
    }    


    /**
     * Export the dump database 
     * @link https://github.com/2createStudio/shuttle-export
     */
    public function actionExport() 
    {
      
      // how to access the DB connection?
      // @link http://www.bsourcecode.com/yiiframework2/yii2-0-database-connection/
      $db_connection_yii2 = \Yii::$app->db;
      // extract the db config
      $db_config = explode( ';', str_replace('mysql:','',$db_connection_yii2->dsn) );
      $host = str_replace('host=','',$db_config[0]);
      $db_name = str_replace('dbname=','',$db_config[1]);

      // call the custom class
      $db_dumper = Shuttle_Dumper::create(array(
        'host' => $host,
        'username' => $db_connection_yii2->username,
        'password' => $db_connection_yii2->password,
        'db_name' => $db_name,
      ));
    
      // option 1: dump the database to gzipped file      
      $db_dumper->dump( $db_name . date('Y-m-d') . '.sql.gz');

      // option 2: dump the database to plain text file
      // $db_dumper->dump(  $db_name . date('Y-m-d') . '.sql');    

      // Process download
      $filepath = Yii::getAlias('@frontend') . '/web/' . $db_name . date('Y-m-d') . '.sql.gz';
      if(file_exists($filepath)) {
        header('Content-Description: File Transfer');
        header('Content-Type: application/octet-stream');
        header('Content-Disposition: attachment; filename="'.basename($filepath).'"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        header('Content-Length: ' . filesize($filepath));
        flush(); // Flush system output buffer
        readfile($filepath);
        
        // delete the dump file
        unlink($filepath);

        exit;
      }
    
    }
}

That’s it. In view file, you can call the Export action when the dump process is done, you will get the prompt for downloading the file to your computer.

 

Facebook
Google+
https://applerinquest.com/how-to-export-database-from-yii2-application">
LinkedIn
Follow by Email