Merging two MySql databases
From LeonWiki!
Contents |
Introduction
- This tutorial will attempt to show how to merge two MySql databases using PHP and SQL.
Purpose
- Most software projects have the need to have various servers running different versions of an application. Some examples are a development server, staging server and production server.
- We recently moved our website to a CMS environment. The CMS framework we chose is [joomla]. Joomla is database driven and also has extensions (plugins that private parties have written for joomla) called components, modules and plugins. Hence, when doing new development (in joomla that's adding/configuring/hacking extensions) both the file system and the database are changed. The file system can be easily versioned using subversion or some other version management system. However, the database presents more of a challenge. So keeping a joomla site updated means getting the latest versioned code AND merging the databases.
Rules
- Should never replace existing records in target (these could be updated content records)
- NOTE: We need a regex inclusion rule for updated component records. Content can be isolated very easily in the database, so we should have an exclusion capability that excludes content and nothing else and then do an insert replace sql operation.
- Should always create new tables in target
Inputs
- Schema name
Design
- SQL
- Merge command
INSERT IGNORE INTO tbl_name [(col_name,...)]
- Get table names for a schema
SELECT table_name FROM information_schema.tables WHERE table_schema = '<schema_name>'
- Get columns for a table
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table_name>'
- PHP
- See new code.
Implementation
- The code below was lifted from this [post].
- Create 2 db connection scripts and one script to merge the data
- connect.php
<?php
// connect to the 33 member database
$host = "localhost"; // Database server
$user = "Database username"; // Database username
$pass = "Database password"; // Database password
$db = "Database name"; // Database name
$db1=mysql_connect("$host","$user","$pass");
mysql_select_db("$db");
$ok = mysql_select_db("$db");
if (!ok)
{
die("<br>" . mysql_errno().":".mysql_errno()."<br>");
}
?>
- dbconnect.php
<?php
// connect to the 1665 member database
$host = "localhost"; // Database server
$user = "Database username"; // Database username
$pass = "Database password"; // Database password
$db = "Database name"; // Database name
$db1=mysql_connect("$host","$user","$pass");
mysql_select_db("$db");
$ok = mysql_select_db("$db");
if (!ok)
{
die("<br>" . mysql_errno().":".mysql_errno()."<br>");
}
?>
- create-new.php
<?php
// 33 members database
include("connect.php);
// for this demo we'll use customer as the table name
// replace the field names with the correct fields from your database table
$sql = "SELECT * FROM customer";
$sql1=mysql_query($sql) or die("Couldn't select customer!");
while($row = mysql_fetch_array($sql1))
{
$ID = stripslashes($row['ID']); // replace the field names only
$MembersID = stripslashes($row['MembersID']); // replace the field names only
$fname = stripslashes($row['fname']); // replace the field names only
$lname = stripslashes($row['lname']); // replace the field names only
$email = stripslashes($row['email']); // replace the field names only
// 1665 members database
include("dbconnect.php);
$insertmember="insert into customer (ID, MembersID, fname, lname, email) values ('$ID', '$MembersID', '$fname', '$lname', '$email')";
//registering member in database
$insertmember2=mysql_query($insertmember) or die("<br><br>Could not insert customer");
// do not uncomment code below.
//$ID = mysql_insert_id();
echo "Insert $fname $lname Completed Successfully";
}// Closes While statement
?>
- Basically you are connecting to the database with the 33 members and as long as the table and field structure is identical you can then just open a connection to the database containing the 1665 members and append the 33 members to that db.
- When completed you will have 1 db with 1698 members.
Updated implementation to get table names and fields dynamically using php scripts above as starting point
- Updated create-new.php (connect.php => source_connect.php...dbconnect.php => target_connect.php
<?php
// 33 members database
include("source_connect.php");
//sql to get the table names for the schema
$sql_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema = '<schema_name>'";
$table_names = mysql_query($sql_table_names) or die("Couldn't get table names!");
include("target_connect.php");
foreach ($table_names as $table_name) {
$sql_field_names = "SELECT column_name FROM information_schema.columns WHERE table_name = '$table_name'";
$field_names = mysql_query($sql_field_names) or die("Couldn't get field name for ".$table_name);
$field_array = array();
foreach ($field_names as $field_name) {
$field_array[$field_name] = $field_name;
}
$sql_insert_ignore = "INSERT IGNORE INTO $table_name $field_array";
$insert_items = mysql_query($sql_insert_ignore) or die("Couldn't insert field names: $field_array into $table_name");
echo "Insert into $table_name successful";
?>
- Issues to resolve
- How to pass the $field_array correctly so it can be resolved in updated code.
- Sql code will need to be able to create and populate new tables in the target database that exist only in the source database.
- When we've updated a table remove that table name from the source database list and if we have any left at the end then those are the tables that need to be created.
NOTE: This is a php test that works and can be adapted: [| phpApp]
<?php
$dbhost = 'localhost';
$dbuser = 'rsie';
$dbpass = 'leon7382';
$schema = $_GET['schema'];
$conn = mysql_connect($dbhost,$dbuser,$dbpass) or die('Error connecting to mysql');
mysql_select_db('information_schema');
//$sql = 'select userFirstName,userLastName,userOrganization from jos_dtregister_user';
//$myquery_result = mysql_query($sql);
//$num=mysql_numrows($myquery_result);
$header = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>
<html xmlns='http://www.w3.org/1999/xhtml'>
<head>
<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1' />
<title>Display</title>
</head>
<body>";
$output = 'Here I am';
/*
TODO: Show a form that has gone and gotten all the schema from the localhost database. Take that data and populate a dropdown. The user will select
a schema and hit submit... the following code will be called to populate the results page.
*/
//sql to get the table names for the schema
$sql_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema = '$schema'";
$table_names = mysql_query($sql_table_names) or die("Couldn't get table names!");
$count = mysql_numrows($table_names);
echo "count = $count<br/>";
echo "schema = $schema<br/>";
while ($table_name=mysql_fetch_array($table_names)) {
$name = $table_name['table_name'];
echo "table name = $name<br/>";
$sql_field_names = "SELECT column_name FROM information_schema.columns WHERE table_name = '$name' and table_schema = '$schema'";
$field_names = mysql_query($sql_field_names) or die("Couldn't get field name for ".$table_name);
$fcount = mysql_numrows($field_names);
echo "field name count = $fcount<br/>";
//$field_array = array();
while ($field_name=mysql_fetch_array($field_names)) {
$fieldName = $field_name['column_name'];
//$field_array[$field_name] = $field_name;
echo "field name = $fieldName<br/>";
}
//$sql_insert_ignore = "INSERT IGNORE INTO $table_name $field_array";
//$insert_items = mysql_query($sql_insert_ignore) or die("Couldn't insert field names: $field_array into $table_name");
}
// echo "Insert into $table_name successful";
mysql_close();
echo $header . $output . $footer;
?>
Next version as objectified code
$dbhost = 'localhost';
$dbuser = 'rsie';
$dbpass = 'leon7382';
$schema = '';
$source = '';
$target = '';
$mode = '';
$schema = $_GET['schema'];
$source = $_GET['source'];
$target = $_GET['target'];
$mode = $_GET['mode'];
$tarGlobal = null;
$srcGlobal = null;
//echo "mode = " .$mode;
if($source != null) $srcGlobal = $source; else $srcGlobal = null;
if($target != null) $tarGlobal = $target; else $tarGlobal = null;
$conn = mysql_connect($dbhost,$dbuser,$dbpass) or die('Error connecting to mysql');
mysql_select_db('information_schema');
$headr = new Header();
echo $headr->buildHeader();
$decider = new controller();
$decider->decide();
mysql_close();
/*===========================Class Interface==========================*/
class controller
{
function decide() {
global $schema,$source,$target,$mode;
if($source != null || $target != null) {
//sql to get the table names for the schema
// echo "schema = ".$schema;
// echo "source = ".$source;
// echo "target = ".$target;
if($schema!=null) $where_constraint = $schema;
if($source!=null) $where_constraint = $source;
if($target!=null) $where_constraint = $target;
$sql_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema = '$where_constraint'";
$table_names = mysql_query($sql_table_names) or die("Couldn't get table names!");
$count = mysql_numrows($table_names);
if($mode == 'List Tables/Fields') {
$obj = new ListObject();
echo "<div style='float:left'><ul>";
echo $obj->ListSchema($source);
echo "</ul></div>";
echo "<div style='float:left'><ul>";
if(mysql_numrows($table_names) > 0)
mysql_data_seek($table_names,0);
echo $obj->ListSchema($target);
echo "</ul></div>";
} elseif($mode == "Merge Ignore") {
if($source != null && $target != null) {
if($source == $target)
echo "<h2><span style='color:red;'>I will not merge the same database...waste of my time</span></h2>";
else {
$so = new SourceObject($source);
$to = new TargetObject($target);
$merger = new Merger();
$merger->mergeIgnore($so,$to);
}
} elseif($source != null || $target != null) {
$st = ($source != null) ? $source : $target;
echo "<h3><span style='color:red;'>how could this happen? You only selected: <b>$st</b></span></h3>";
}
} elseif($mode == "Diff Schema") {
if($source != null && $target != null) {
$so = new SourceObject($source);
$to = new TargetObject($target);
$diff = new Differencer($so,$to);
echo "<div style='float:left'><ul>";
$diff->doDiff();
echo "</ul></div>";
}
else
echo "<h3>Please select source AND target for a diff process</h3>";
} elseif($mode == "Merge Replace") {
//here for merge replace
if($source != null && $target != null) {
if($source == $target)
echo "<h2><span style='color:red;'>I will not merge the same database...waste of my time</span></h2>";
else {
$so = new SourceObject($source);
$to = new TargetObject($target);
$merger = new Merger();
$merger->mergeReplace($so,$to);
}
} elseif($source != null || $target != null) {
$st = ($source != null) ? $source : $target;
echo "<h3><span style='color:red;'>how could this happen? You only selected: <b>$st</b></span></h3>";
}
} else
echo "<h3>Please select source AND target for a merge process</h3>";
} else {
echo "<h3>Please choose either a source or target schema to list or both source and target for merging/diffing</h3>";
}
}
}
class ListObject
{
function ListSchema($schema) {
$sql_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema = '$schema'";
$table_names = mysql_query($sql_table_names) or die("Couldn't get table names!");
$count = mysql_numrows($table_names);
if($schema != null) {
$output = "<h1>Schema: $schema</h1>";
$output .= "<h3>Table Count: $count</h3>";
}
while ($table_name=mysql_fetch_array($table_names)) {
$name = $table_name['table_name'];
$output .= "<li><b><span style='color:red;'>table name = $name</span></b></li>";
$sql_field_names = "SELECT column_name FROM information_schema.columns WHERE table_name = '$name' and table_schema = '$schema'";
$field_names = mysql_query($sql_field_names) or die("Couldn't get field name for ".$table_name);
$fcount = mysql_numrows($field_names);
$output .= "<li><b>field name count = $fcount</b></li>";
//$field_array = array();
while ($field_name=mysql_fetch_array($field_names)) {
$fieldName = $field_name['column_name'];
//$field_array[$field_name] = $field_name;
$output .= "<li><span style='color:green;'>field name = $fieldName</span></li>";
}
}
return $output;
}
function ListSchemaDiff($tables,$schema)
{
$output = "";
if(count($tables)) {
foreach($tables as $table_name => $field_names) {
// echo "$table_name";
$output .= "<li><b><span style='color:red;'>table name = $table_name</span></b></li>";
$fcount = count($field_names);
$output .= "<li><b>field name count = $fcount</b></li>";
// echo $field_names;
foreach($field_names as $fieldName) {
$output .= "<li><span style='color:green;'>field name = $fieldName</span></li>";
}
}
return $output;
}
else
echo "Houston, we have database synch...";
return "";
}
function getSchema($schema) {
$table = array();
$fields = array();
$sql_table_names = "SELECT table_name FROM information_schema.tables WHERE table_schema = '$schema'";
$table_names = mysql_query($sql_table_names) or die("getSchema: Couldn't get table names!");
while ($table_name=mysql_fetch_array($table_names)) {
$name = $table_name['table_name'];
$sql_field_names = "SELECT column_name FROM information_schema.columns WHERE table_name = '$name' and table_schema = '$schema'";
$field_names = mysql_query($sql_field_names) or die("getSchema: Couldn't get field name for ".$table_name);
while ($field_name=mysql_fetch_array($field_names)) {
$fieldName = $field_name['column_name'];
$fields[$fieldName] = $fieldName;
}
$table[$name] = $fields;
unset($fields);
}
return $table;
}
}
class MySelects
{
function buildSelects($selectName,$result,$fieldName)
{
global $srcGlobal,$tarGlobal;
$output = "<label name='Select_$selectName'><b>Select $selectName</b></label>";
$output .= "<select name='$selectName'>";
switch($selectName) {
case "source":
if($srcGlobal == null)
$output .= "<option value='' selected='true'>Select one</option>";
else {
$hasValue = $srcGlobal;
$output .= "<option value=''>Select one</option>";
}
break;
case "target":
if($tarGlobal == null)
$output .= "<option value='' selected='true'>Select one</option>";
else {
$hasValue = $tarGlobal;
$output .= "<option value=''>Select one</option>";
break;
}
}
while($row=mysql_fetch_array($result)) {
$name = $row[$fieldName];
if($hasValue == null || $name != $hasValue)
$output .= "<option value='$name'>$name</option>";
else
$output .= "<option value='$name' selected='true'>$name</option>";
}
$output .= "</select>";
return $output;
}
}
class Header
{
function buildHeader() {
//sql to get the schema names
$schema_names = "select schema_name from information_schema.schemata";
$schemaResult = mysql_query($schema_names);
$header = "<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'>";
$header .= "<html xmlns='http://www.w3.org/1999/xhtml'>";
$header .= "<head><meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'/><title>Merge App</title></head>";
$header .= "<body>";
$header .= "<form action='index.php'>";
$header .= "<table width='50%' border='0'>";
$header .= "<tr><td>";
$myselects = new MySelects();
$header .= $myselects->buildSelects("source",$schemaResult,"schema_name");
$header .= "</td><td>";
mysql_data_seek($schemaResult,0);
$header .= $myselects->buildSelects("target",$schemaResult,"schema_name");
$header .= "</td>";
$header .= "<td><input type='submit' name='mode' value='List Tables/Fields'/></td>";
$header .= "<td><input type='submit' name='mode' value='Diff Schema'/></td><td> </td><td> </td>";
$header .= "<td><input type='submit' name='mode' value='Merge Ignore'/></td>";
$header .= "<td><input type='submit' name='mode' value='Merge Replace'/></td>";
$header .= "</tr></table>";
$header .= "</form>";
return $header;
}
}
class SchemaType
{
var $schema;
var $tables;
function __construct($schema,$tables)
{
$this->schema = $schema;
$this->tables = $tables;
}
function getSchema()
{
return $this->schema;
}
}
class TargetObject extends SchemaType
{
function __construct($schema) {
$lister = new ListObject();
$tables = $lister->getSchema($schema);
parent::__construct($schema,$tables);
}
function getTables()
{
return $this->tables;
}
function getName()
{
return $this->schema;
}
}
class SourceObject extends SchemaType
{
function __construct($schema) {
$lister = new ListObject();
$tables = $lister->getSchema($schema);
parent::__construct($schema,$tables);
}
function getTables()
{
return $this->tables;
}
function getName()
{
return $this->schema;
}
}
class Differencer
{
var $sObj;
var $tObj;
function __construct($sourceObj,$targetObj)
{
$this->sObj = $sourceObj;
$this->tObj = $targetObj;
}
function doDiff()
{
$arr = array();
$s_schema = $this->sObj->getName();
$t_schema = $this->tObj->getName();
echo "<h2>Tables in '$s_schema' that are NOT in '$t_schema'</h2>";
$st = $this->sObj->getTables();
$tt = $this->tObj->getTables();
foreach($st as $tablename => $fields) {
if($tt[$tablename] == null) {
//list the tablename and fields in a list like when we do the lister thingy
$arr[$tablename] = $st[$tablename];
}
}
$lister = new ListObject();
$schema = $this->sObj->getName();
//echo "<h3>Schema: $schema</h3>";
$count = count($arr);
echo "<h3>Number of Different Tables: $count</h3>";
echo $lister->ListSchemaDiff($arr,$this->sObj->getName());
}
}
class Merger
{
function mergeIgnore($sourceObj,$targetObj)
{
$src = $sourceObj->getName();
$tar = $targetObj->getName();
$sourceTables = $sourceObj->getTables();
$targetTables = $targetObj->getTables();
mysql_select_db($src);
echo "<h3>Merging....$src -> $tar</h3>";
//loop through source getting the corresponding name in target
foreach($sourceTables as $tablename => $sourcefields) {
$targetfields = $targetTables[$tablename];
if($targetfields != null) {
if($this->tableCheck($sourcefields,$targetfields)) {
$insertSql = "insert ignore into $tar.$tablename select * from $src.$tablename";
mysql_query($insertSql);
}
}
else {
echo "$tablename in source but not in target<br/>";
$createSql = "create table $tar.$tablename like $tablename";
$result = mysql_query($createSql);
if($result) echo "<h3>Success creating $tar.$tablename...</h3>";
$populateSql = "insert into $tar.$tablename select * from $tablename";
$result = mysql_query($populateSql);
if($result) echo "<h3>Success populating table $tar.$tablename...</h3>";
}
}
}
function mergeReplace($sourceObj,$targetObj)
{
$src = $sourceObj->getName();
$tar = $targetObj->getName();
$sourceTables = $sourceObj->getTables();
$targetTables = $targetObj->getTables();
mysql_select_db($src);
echo "<h3>Merging....$src -> $tar</h3>";
//loop through source getting the corresponding name in target
foreach($sourceTables as $tablename => $sourcefields) {
$targetfields = $targetTables[$tablename];
if($targetfields != null) {
if($this->tableCheck($sourcefields,$targetfields)) {
$insertSql = "replace into $tar.$tablename select * from $src.$tablename";
mysql_query($insertSql);
}
}
else {
echo "$tablename in source but not in target<br/>";
$createSql = "create table $tar.$tablename like $tablename";
$result = mysql_query($createSql);
if($result) echo "<h3>Success creating $tar.$tablename...</h3>";
$populateSql = "insert into $tar.$tablename select * from $tablename";
$result = mysql_query($populateSql);
if($result) echo "<h3>Success populating table $tar.$tablename...</h3>";
}
}
}
function tableCheck($table1,$table2)
{
//check that each table has the same number and name for each field (ie. that db structures should be the same)
$result = true;
foreach($table1 as $key => $value) {
if($value != $table2[$key])
$result = false;
}
return result;
}
}
