Page 1 of 1

Transferring Data between tables in different databases

Posted: Thu Mar 08, 2007 9:37 am
by Kadanis
Hi

First time I've posted something for critique, but here goes. I'm pretty new to OO and using the comments in the style of phpDoc too so sorry if they're not what your used to

Be gentle :wink:

Any suggestions, bugs, comments would be great.

Specification: To be able to transfer data from a table in a database held on one server, to an indentical table in another database, potentially running on a totally different server within the network.
At the moment the largest table that will be transferred will be around 10'000 rows.

My Class:

Code: Select all

<?php

/**
 * TransferData Class
 * 
 * Transfers data from a specified mysql database table 
 * to an identical table in another mysql database
 *
 * @package V2
 * @author Dave Baker
 * @copyright Dave Baker 
 * @version 1.0
 * 
 */

class TransferData{
	
	/**
	 * Email address where errors will be sent
	 *
	 * @var string
	 * @access private
	 */
	private $errors_mail_to = 'xx@xx.com';
	
	/**
	 * Host name / IP Address for source mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $source_host = '';
	
	/**
	 * User name for source mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $source_user = '';
	
	/**
	 * Password for source mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $source_password = '';
	
	/**
	 * Source database name
	 *
	 * @var string
	 */
	private $source_database = '';
		
	/**
	 * Host name / IP Address for destination mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $destination_host = '';
	
	/**
	 * Username for destination mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $destination_user = '';
	
	/**
	 * Password for destination mysql database
	 *
	 * @var string
	 * @access private
	 */
	private $destination_password = '';
	
	/**
	 * Destination database name
	 *
	 * @var string
	 */
	private $destination_database = '';
	
	/**
	 * Create the destination database if it doesn't exist?
	 *
	 * @var boolean
	 */
	private $create_destination_database = false;
	
	/**
	 * Create the destination table if it doesn't exist?
	 *
	 * @var boolean
	 */
	private $create_destination_table = false;
	
	/**
	 * Append flag
	 * If true then data appended to table in destination database, if false table is emptied before data is inserted
	 *
	 * @var boolean
	 * @access private
	 */
	private $append = false;
	
	private $mysqli_type = array(
		0=>"DECIMAL",
		1=>"TINYINT",
		2=>"SMALLINT",
		3=>"INT",
		4=>"FLOAT",
		5=>"DOUBLE",
		7=>"TIMESTAMP",
		8=>"BIGINT",
		9=>"MEDIUMINT",
		10=>"DATE",
		11=>"TIME",
		12=>"DATETIME",
		13=>"YEAR",
		14=>"DATE",
		16=>"BIT",
		246=>"DECIMAL",
		247=>"ENUM",
		248=>"SET",
		249=>"TINYBLOB",
		250=>"MEDIUMBLOB",
		251=>"LONGBLOB",
		252=>"BLOB",
		253=>"VARCHAR",
		254=>"VARCHAR", 
		255=>"GEOMETRY"
	);
	
	
	/**
	 * Constructor 
	 * 
	 * @return void
	 */
	public function __construct(){
		require_once dirname(__FILE__) . '/class.errorhandler.php';
		$errors = new ErrorHandler();
		$errors->set_mail_flag(false);
		$errors->set_to($this->errors_mail_to);
		$errors->set_echo_errors(true);
	}
	
	/**
	 * Sets connection details for the source database
	 *
	 * @param string $hostname
	 * @param string $username
	 * @param string $password
	 * @param string $database_name
	 * @return void
	 */
	public function set_source_db($hostname, $username, $password,$database_name){
		$this->source_host = $hostname;
		$this->source_user = $username;
		$this->source_password = $password;
		$this->source_database = $database_name;
	}
		
	/**
	 * Sets connection details for the destination database
	 *
	 * @param string $hostname
	 * @param string $username
	 * @param string $password
	 * @param string $database_name
	 * @return void
	 */
	public function set_destination_db($hostname, $username, $password,$database_name){
		$this->destination_host = $hostname;
		$this->destination_user = $username;
		$this->destination_password = $password;
		$this->destination_database = $database_name;
	}
		
	/**
	 * Transfers the data (uses the mysqli library). 
	 *
	 * @param string $source_table
	 * @param string $destination_table
	 *
	 */
	public function transfer_data($source_table, $destination_table = ''){
		//set destination table if no user intput
		if ($destination_table == ''){$destination_table = $source_table;}
		
		//check and create destination database
		if ($this->create_destination_database){$this->create_destination_dbs();}
			
		//connect to source
		$source_conn = new mysqli($this->source_host, $this->source_user, $this->source_password, $this->source_database) 
			or trigger_error(mysqli_connect_error(),E_USER_WARNING);
		
		//get source data
		$source_table_data = $source_conn->query("SELECT * FROM $source_table;") 
			or trigger_error(mysqli_error($source_conn), E_USER_WARNING);

		//create destination table
		if ($this->create_destination_table){
			//generate field list for the create table function
			$field_list = mysqli_fetch_fields($source_table_data);
			$fields = '';
			foreach($field_list as $field){
				$fields .=  sprintf("`%s` %s (%s),\n",$field->name,$this->mysqli_type[$field->type],$field->length);
			}
			$fields = substr($fields,0,-2);
			$this->create_destination_tbl($destination_table,$fields);
		}
			
		//connect to destination
		$destination_conn = new mysqli($this->destination_host, $this->destination_user, $this->destination_password, $this->destination_database) 
				or trigger_error(mysqli_connect_error(), E_USER_WARNING);
		
		//if append is false, clear out destination table
		if (!$this->append){
			$destination_conn->query("DELETE FROM $destination_table;") 
				or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
		}
		
		//get the first row and field names. 
		$row = $source_table_data->fetch_assoc();
		$columns = '(';
		$data = '(';
		//create column and data strings
		foreach ($row as $name => $value){
			$columns .= "$name,";
			$data .= "'$value',";
		}
		$columns = substr($columns,0,-1) . ')';
		$data = substr($data,0,-1) . ')';
		
		//transfer first row
		$destination_conn->query("INSERT INTO $destination_table $columns VALUES $data;") 
			or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
		
		//rinse and repeat for remaining data
		while ($row =  $source_table_data->fetch_assoc()){
			$data = '(';
			foreach ($row as $value){
				$data .= "'$value',";
			}
			$data = substr($data,0,-1) . ')';
			
			$destination_conn->query("INSERT INTO $destination_table $columns VALUES $data;") 
				or trigger_error(mysqli_error($destination_conn), E_USER_WARNING);
		}
		
		//close down
		$source_table_data->close();
		$source_conn->close();
		$destination_conn->close();
		
	}

	/**
	 * Creates the database (if required) in the destination MYSQL Server
	 *
	 */
	private function create_destination_dbs(){
		$conn = new mysqli($this->destination_host,$this->destination_user, $this->destination_password)
			or trigger_error(mysqli_connect_error(), E_USER_ERROR);
		$conn->query("CREATE DATABASE IF NOT EXISTS {$this->destination_database};") 
			or trigger_error(mysqli_error($conn), E_USER_ERROR);
		$conn->close();
	}
	
	/**
	 * Creates the destination table in the destination database
	 *
	 * @param string $destination_table
	 * @param string $fields
	 */
	private function create_destination_tbl($destination_table, $fields){
		$conn = new mysqli($this->destination_host,$this->destination_user, $this->destination_password,$this->destination_database)
			or trigger_error(mysqli_connect_error(), E_USER_ERROR);
		$conn->query("CREATE TABLE IF NOT EXISTS $destination_table ($fields);") 
			or trigger_error(mysqli_error($conn), E_USER_ERROR);
		$conn->close();
	}
	
	/**
	 * Flags whether the destination database needs to be created
	 *
	 * @param string $create_database
	 */
	public function set_create_database($create_database){
		$this->create_destination_database = $create_database;
	}
	
	/**
	 * Flags whether the destination table needs to be created
	 *
	 * @param string $create_table
	 */
	public function set_create_table($create_table){
		$this->create_destination_table = $create_table;
	}
	
	/**
	 * Flags whether data is appended to destination
	 *
	 * @param boolean $append
	 */
	public function append_to_table($append){
		$this->append = $append;
	}
}
?>

Posted: Thu Mar 08, 2007 10:04 am
by Chris Corbyn
I have to wonder why you'd not just use mysqldump ;)

Code: Select all

mysqldump some_db -h remote_host -u remote_user --password=remote_password | mysql local_db -u local_user --password=local_password

Posted: Thu Mar 08, 2007 10:11 am
by Kadanis
d11wtq wrote:I have to wonder why you'd not just use mysqldump ;)

Code: Select all

mysqldump some_db -h remote_host -u remote_user --password=remote_password | mysql local_db -u local_user --password=local_password
I guess my honest first answer is because I didn't know I could. :oops:

I'll have to research that, but for speeds sake I'll ask here too::

Can you use that function to do SELECT DELETE LIMIT etc on the data you are dumping?

In order to fill the whole specification (which I didn't write up here) I need to transfer data from 2 tables from a remote server to the local server. For the 1st its just a case of dumping the contents, but in the case of the other, only data with fields set to a certain status can be transfered.

Also in the second instance the transfered records must be deleted after transfer and can only be moved in blocks of 1000. (Hence the DELETE and LIMIT question).

To manage this I was going to use this class for the full dump, then extend it to cover the second more fiddlely transfer.

If there is a faster and easier way to do this, then I'm all for it :)

Posted: Thu Mar 08, 2007 12:03 pm
by Chris Corbyn
No, you can't filter the SQL with mysqldump. You also need command line access (or the ability to run shell commands from php). mysqldump just generates SQL output to STDOUT. Typically people redirect it into a file for backup purposes but you can do what I showed above and pipe it from one database to another.

Sounds like it was worth spending the time to write your class if you need to filter the SQL :)

Some notes:

1. I'd perhaps be tempted to write a custom error handler and allow it to be attached to the class as an observer (a chance to get your head in some design patterns there!)

Code: Select all

$transfer->addErrorHandler(new WhateverErrorHandler());
2. I haven't looked that closely, but you appear to create new connections all over. You may want to register these inside the class when they are first created:

Code: Select all

public function getConnection($host, $user, $pass)
{
    if ($this->connections[$host] === null)
    {
        $this->connections[$host] = new mysqli($host, $user, $pass);
    }
    return $this->connections[$host];
}
3. Personal preference entirely, but I find "protected" is more than enough. "private" is too restrictive if ever you want to extend the class. You can always add "final" to any methods you really don't want to be overridden without preventing access.

From the brief skim over it I had though, not bad at all :D

Posted: Thu Mar 08, 2007 12:40 pm
by Kadanis
Thanks for the comments.

1. I have got a custom error handler class, I didn't post it with this class. However, I'm not sure what you mean by "attach it as an observer", I'll have to do some research there. At the moment I just set it up in the constructor.

Code: Select all

public function __construct(){
                require_once dirname(__FILE__) . '/class.errorhandler.php';
                $errors = new ErrorHandler();
                $errors->set_mail_flag(false);
                $errors->set_to($this->errors_mail_to);
                $errors->set_echo_errors(true);
        }

2. That's something I was worried about. Wasn't sure how to approach it, thank's for the suggestion. I'll look into implementing something like this in the class asap.

3. I realised the problems with using private when I came to start writing the sub-class for the second transfer. I've now switched all the "private" for "protected", but thanks for the pick-up :) Didn't know about the "final" key word though so will research that too.

Thanks again for taking the time to read/comment. Very helpful

Posted: Thu Mar 08, 2007 3:42 pm
by Christopher
d11wtq wrote:No, you can't filter the SQL with mysqldump. You also need command line access (or the ability to run shell commands from php). mysqldump just generates SQL output to STDOUT. Typically people redirect it into a file for backup purposes but you can do what I showed above and pipe it from one database to another.

Sounds like it was worth spending the time to write your class if you need to filter the SQL :)
You could also export the first table with "SELECT INTO OUTFILE" and then filter the file (or not) and then "LOAD DATA INFILE" into the second table. That would probably be faster than record by record as those two commands are very efficient.