Hi Folks,
Last year, I published a package in PHPClasses to synchronize the records of two versions of the same database schema and also to synchronize the schema. You can check out the link here – http://www.phpclasses.org/package/7448-PHP-Synchronize-MySQL-tables-with-another-database.html.
The purpose of this package is simple. Many a times, there is problem while development in a team that a few things are working on a developer’s machine and the same things are not working on another developer’s machine or showing exceptions/errors. After hours of debugging we find that the data entered in database are different and therefore is the reason
of inconsistent behaviour of code (if code is meeting the business requirements). So, in this case, this package will help to migrate records between two versions of the same database schema and synchronize different schema too and hence to debug easily.
Here is the old schema whose data needs to be changed: oldschema.sql
-- phpMyAdmin SQL Dump -- version 3.3.10deb1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 04, 2012 at 09:43 PM -- Server version: 5.1.61 -- PHP Version: 5.3.5-1ubuntu7.4 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `oldschema` -- -- -------------------------------------------------------- -- -- Table structure for table `user_table` -- CREATE TABLE IF NOT EXISTS `user_table` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_uname` varchar(255) COLLATE utf8_bin NOT NULL, `user_email` varchar(255) COLLATE utf8_bin NOT NULL, `user_passwd` blob NOT NULL, `user_fname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_lname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_token` varchar(255) COLLATE utf8_bin NOT NULL, `user_gender` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '', `user_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_city` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_postal` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_country` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_contact_no` varchar(255) COLLATE utf8_bin NOT NULL, `user_status` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'Inactive', `user_online` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'No', `user_last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_logout_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`user_id`), UNIQUE KEY `user_uname` (`user_uname`), UNIQUE KEY `user_email` (`user_email`), UNIQUE KEY `user_token` (`user_token`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=24 ; -- -- Dumping data for table `user_table` -- INSERT INTO `user_table` (`user_id`, `user_uname`, `user_email`, `user_passwd`, `user_fname`, `user_lname`, `user_token`, `user_gender`, `user_address`, `user_city`, `user_postal`, `user_country`, `user_contact_no`, `user_status`, `user_online`, `user_last_login`, `user_logout_time`) VALUES (1, 'admin', '[email protected]', 0x9df9yuyyb96e442b, 'Super', 'Administrator', 'none', 'Male', '', '', '', '', '', 'Active', 'No', '2012-02-16 15:52:01', '2011-12-13 15:57:10'), (2, 'guser', '[email protected]', 0fgdx9df9b96e442b, 'Nitesh', 'Apte', 'wsse4d', 'Male', '', '', '', '', '', 'Inactive', 'No', '2011-10-19 13:47:50', '0000-00-00 00:00:00'), (8, '32c786bf', '[email protected]', 0x3332633738366266, 'F1', 'L1', '32c786bf', '', '', '', '', '', '', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (9, '9cae8262', '[email protected]', 0x3963616538323632, 'F2', 'L2', '9cae8262', '', '', '', '', '', '', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (10, '2b15e984', '[email protected]', 0x3262313565393834, 'F3', 'L3', '2b15e984', '', '', '', '', '', '', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (11, '233618a0', '[email protected]', 0x3233333631386130, 'F4', 'L4', '233618a0', '', '', '', '', '', '', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (14, '7330895f', '[email protected]', 0x0f98f8ad04fc638d, 'Promotional', 'Admin', '7330895f', '', '', '', '', '', '', 'Active', 'No', '2011-11-25 22:15:01', '2011-11-25 22:19:14'), (15, 'cbdbb6f8', '[email protected]', 0xd0c5aa7bbaf038b7, 'Event', 'Admin', 'cbdbb6f8', '', '', '', '', '', '', 'Active', 'No', '2011-12-09 17:21:49', '2011-11-25 16:38:08'), (16, '4b8c38a0', '[email protected]', 0xa0921d3ab950c3df, 'News', 'Admin', '4b8c38a0', '', '', '', '', '', '', 'Active', 'No', '2011-12-09 16:52:45', '2011-11-23 12:32:45'), (17, '705d0284', '[email protected]', 0x0f157e726f84036a, 'Question', 'Admin', '705d0284', '', '', '', '', '', '', 'Active', 'No', '2011-12-15 11:50:40', '2011-11-25 17:29:27'), (18, '6276efda', '[email protected]', 0xdfa26468c432b172, 'Nitesh1', 'Apte', '6276efda', '', '', '', '', '', '9922939828', 'Active', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (20, '21dd5f14', '[email protected]', 0xa868a82732644567, 'SomeFirstname', 'SomeLAstName', '21dd5f14', '', '', '', '', '', '3442344545251244', 'Active', 'No', '2011-11-11 19:44:54', '2011-11-01 00:00:00'), (21, 'cb950bf8', '[email protected]', 0xd0c5e7aa34ed34bb, 'Radha', 'Mohan', 'cb950bf8', '', '', '', '', '', '98978989989', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (22, '738e2bc6', '[email protected]', 0x0f984f5ed6bbaa13, 'Nitesh', 'Apte', '738e2bc6', '', '', '', '', '', '99999999999', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00'), (23, '4eb8e6b2', '[email protected]', 0xa0eea493cafbb134, 'nitesh', 'apye', '4eb8e6b2', '', '', '', '', '', '808534085034', 'Inactive', 'No', '0000-00-00 00:00:00', '0000-00-00 00:00:00');
And the new schema is below: newschema.sql
-- phpMyAdmin SQL Dump -- version 3.3.10deb1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Apr 04, 2012 at 09:41 PM -- Server version: 5.1.61 -- PHP Version: 5.3.5-1ubuntu7.4 SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; -- -- Database: `newschema` -- -- -------------------------------------------------------- -- -- Table structure for table `user_table` -- CREATE TABLE IF NOT EXISTS `user_table` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `user_uname` varchar(255) COLLATE utf8_bin NOT NULL, `user_email` varchar(255) COLLATE utf8_bin NOT NULL, `user_passwd` blob NOT NULL, `user_fname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_lname` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_token` varchar(255) COLLATE utf8_bin NOT NULL, `user_gender` varchar(20) COLLATE utf8_bin NOT NULL DEFAULT '', `user_address` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_city` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_postal` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_country` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '', `user_contact_no` varchar(255) COLLATE utf8_bin NOT NULL, `user_status` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'Inactive', `user_online` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT 'No', `user_created_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_last_login` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `user_logout_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`user_id`), UNIQUE KEY `user_uname` (`user_uname`), UNIQUE KEY `user_email` (`user_email`), UNIQUE KEY `user_token` (`user_token`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ; -- -- Dumping data for table `user_table` --
Below is the class that will do the trick: class.DataMigratorMerger.php
<?php
/**
* Data Migrator Mreger is for merging the data/records of two database of completely same or similar schema.
*
* @author Nitesh Apte
*/
class DataMigratorMreger {
private $conn;
private $sourceDB;
private $destinationDB;
public function __construct() {
$this->conn = mysql_connect("localhost", "root", "");
}
public function mergeData($oldSchema, $newSchema, $reverse = FALSE) {
mysql_query("SET FOREIGN_KEY_CHECKS = 0");
if($reverse) {
$this->destinationDB = $oldSchema;
$this->sourceDB = $newSchema;
} else {
$this->destinationDB = $newSchema;
$this->sourceDB = $oldSchema;
}
mysql_query("use ".$oldSchema);
$tables = $this->getAssoc("show tables");
for($i=0;$i<count($tables);$i++) {
/* Disable this in case you want to merge the data */
$trun = "TRUNCATE $this->destinationDB.".$tables[$i]['Tables_in_'.$this->sourceDB];
mysql_query($trun);
$values = $this->getAssoc("describe ".$tables[$i]['Tables_in_'.$oldSchema]);
// put $j=1 in case of merging. Generally, first column is auto incremented
id
for($j=0;$j<count($values);$j++) {
$val[$i][] = $values[$j]['Field'];
}
$field = implode(',', $val[$i]);
$sql = "INSERT INTO $this->destinationDB.".$tables[$i]['Tables_in_'.$oldSchema]." ($field) SELECT ".$field." FROM $this->sourceDB.".$tables[$i]['Tables_in_'.$oldSchema];
mysql_query($sql);
}
mysql_query("SET FOREIGN_KEY_CHECKS = 1");
}
public function getAssoc($queryString) {
$rs = mysql_query($queryString, $this->conn);
while($rw = @mysql_fetch_array($rs, MYSQL_ASSOC)){
$values[] = $rw;
}
return $values;
}
}
?>
And below is the test script describing the usage of this class: test.php
<?php
ini_set("display_errors", 1);
error_reporting("E_ALL ~ E_NOTICE");
include_once 'class.DataMigratorMerger.php';
$obj = new DatabaseMergerMigrator();
//migrate/merge from oldschema to newschema
$obj->mergeData("oldschema","newschema");
// Put third parameter as TRUE if you want to migrate/merge from newschema to old schema.
$obj->mergeData("oldschema","newschema", TRUE);
?>
Quite a handy tool, isn’t it? Hope this will help to save a lot of time in debugging and development.
Please feel free to improve and share it.
Github: You can clone it from https://github.com/niteshapte/mysql-data-merger-migrator
That’s all folks for today.
Critics/suggestion are very much welcome.
Have a nice day ahead.
Leave a Reply