Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save jordanbrauer/599d450ad9a22e6913ede1c216e22f26 to your computer and use it in GitHub Desktop.
Save jordanbrauer/599d450ad9a22e6913ede1c216e22f26 to your computer and use it in GitHub Desktop.
PHP class to duplicate database
<?php
namespace C3\Utility;
use Monolog\Logger;
/**
* Class DuplicateDatabase
*
* @package C3\Utility
*/
class DuplicateDatabase
{
/**
* @var null|\Monolog\Logger $_log
*/
protected $_log = null;
/**
* @var \PDO Connection for duplication
*/
protected $_pdo;
/**
* @param \Monolog\Logger $log
* @param \PDO $pdo
*/
public function __construct($log, $pdo)
{
$this->_log = $log;
$this->_pdo = $pdo;
}
/**
* Duplicate oldDb to newDb
* Assumes that \PDO is set to use exceptions
*
* $flags array can contain the following arrays:
* exclude_tables - Exclude these tables
* exclude_data - Exclude data from these tables
*
* $flags can also set
* drop_database - boolean to determine whether to recreate database (default = true)
*
* @param string $oldDb
* @param string $newDb
* @param array $flags
*/
public function duplicateDatabase($oldDb, $newDb, $flags=array())
{
// Get include/exclude options from flags
$excludeTables = array();
$excludeDataFromTables = array();
$dropDatabase = true;
if (isset($flags['exclude_tables'])) {
$excludeTables = $flags['exclude_tables'];
}
if (isset($flags['exclude_data'])) {
$excludeDataFromTables = $flags['exclude_data'];
}
if (isset($flags['drop_database'])) {
$dropDatabase = ($flags['drop_database'] == true);
}
// Setup options to make copying possible (turning off checks and disabling auto-increment on 0)
$this->_pdo->query("SET UNIQUE_CHECKS=0");
$this->_pdo->query("SET FOREIGN_KEY_CHECKS=0");
$this->_pdo->query("/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */");
$this->_pdo->query("/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */");
try {
$getTables = "SHOW FULL TABLES IN `{$oldDb}` WHERE Table_Type = 'BASE TABLE'";
//echo "$getTables\n";
$tables = array();
foreach ($this->_pdo->query($getTables) as $row){
$tables[] = $row[0];
}
// Drop/recreate database if required
if ($dropDatabase) {
$this->_pdo->query("DROP DATABASE IF EXISTS `{$newDb}`");
$this->_pdo->query("CREATE DATABASE `{$newDb}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
}
// Loop to create/populate all tables from $oldDb
foreach ($tables as $table) {
// Skip creating table at all if in $excludeTables
if (array_search($table,$excludeTables)) {
continue;
}
$createCmd = "CREATE TABLE `{$newDb}`.`{$table}` LIKE `{$oldDb}`.`{$table}`";
//echo "{$createCmd}\n";
$this->_pdo->query($createCmd);
// Skip adding data to table if in $excludeDataFromTables
if (array_search($table,$excludeDataFromTables)) {
continue;
}
$alterCmd = "/*!40000 ALTER TABLE `{$newDb}`.`{$table}` DISABLE KEYS */";
//echo "{$alterCmd}\n";
$this->_pdo->query($alterCmd);
$insertCmd = "INSERT INTO `{$newDb}`.`{$table}` SELECT * FROM `{$oldDb}`.`{$table}`";
//echo "{$insertCmd}\n";
$this->_pdo->query($insertCmd);
$alterCmd2 = "/*!40000 ALTER TABLE `{$newDb}`.`{$table}` ENABLE KEYS */";
//echo "{$alterCmd2}\n";
$this->_pdo->query($alterCmd2);
}
} finally {
$this->_pdo->query("SET FOREIGN_KEY_CHECKS=1");
$this->_pdo->query("SET UNIQUE_CHECKS=1");
$this->_pdo->query("/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */");
$this->_pdo->query("/*!40101 SET SQL_MODE=@OLD_SQL_MODE */");
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment