Skip to main content

MySQL: Moving table from one db to another

To move one table from one db to another, you can create new table (create table foo_new like foo) in db where you want to move table and then copy data with insert into/select query. However there is much easier way which is especially handy when you deal with big tables.

As you probably aready know, there is easy way to rename MySQL table just by issuing rename clause in alter statement:

ALTER TABLE foo RENAME TO new_foo;

You can also use RENAME TABLE syntax like this:

RENAME TABLE foo TO new_foo;

Now, when you need to move table from one db to another, all you have to do is to specify it’s current db and new db name as table prefix. For example if you want to move table foo from current db to new db you can issue queries like these:

ALTER TABLE currentdb.foo RENAME TO newdb.foo;

or

RENAME TABLE currentdb.foo TO newdb.foo;

Btw there is important difference between ALTER and RENAME statements in a way that with RENAME you can rename more than one tables at once. This comes handy if you want for example to swap names of two tables:

RENAME TABLE table1 TO temp, table2 TO table1, temp TO table2;

PHP: Callback functions and OOP

Recently, I had to change default behavior of storing session data into files and use MySQL DB instead. In practice, that means writing whole bunch of callback functions and setting callbacks with session_set_save_handler function. Since I use OOP, what really bothered me was the fact that (according to my PHP CHM Manual sitting on my desktop) for session_set_save_handler, all functions has to exist in global scope, since all callback arguments are strings?

bool session_set_save_handler ( string open, string close, string read, string write, string destroy, string gc )

Doing that in non OOP way with 6 functions on global scope is not something I really liked, so I googled for solution and found that you can easily assign an array like array(‘class_name’, ‘method’) for all callbacks in PHP. Cool stuff which allows you to create session handler class with bunch of static methods for those callbacks, but why the hell that is not documented in PHP Manual???

I went to online manual at least to see if someone submitted comment about this, and find out that session_set_save_handler definition there is completely different:

bool session_set_save_handler ( callback $open, callback $close, callback $read, callback $write, callback $destroy, callback $gc )

Obviously, since last time I browsed online manual, a lot of thing has changed, one among them is introducing “callback” type in those “pseudo types” used only for documentation purposes. And there, manual for callback says following:

callback

Some functions like call_user_func() or usort() accept user defined callback functions as a parameter. Callback functions can not only be simple functions but also object methods including static class methods.

A method of an instantiated object is passed as an array containing an object as the element with index 0 and a method name as the element with index 1.

Static class methods can also be passed without instantiating an object of that class by passing the class name instead of an object as the element with index 0.

which basically allows you to pass an array with class name and method as callback, and that method will be called.

Let me give you and example with sessions:

<?php

/**
 * Sessin_Handlers class
 * contains dummy methods needed for session stuff
 * Replace content with some real stuff like db conn etc.
 *
 */
class Session_Handlers
{
	function open($save_path, $session_name)
	{
		echo "Open Method Called<br>";
		return true;
	}

	function close()
	{
		echo "Close Method Called<br>";
		return true;
	}

	function read($id)
	{
		echo "Read Method Called<br>";
		return true;
	}

	function write($id, $sess_data)
	{
		echo "Write Method Called<br>";
		return true;

	}

	function destroy($id)
	{
		echo "Destroy Method Called<br>";
		return true;
	}

	function gc($maxlifetime)
	{
		echo "GC Method Called<br>";
		return true;
	}
}

//call all method from Session_Handlers statically
session_set_save_handler(array('Session_Handlers', 'open'), array('Session_Handlers', 'close'), array('Session_Handlers', 'read'), array('Session_Handlers', 'write'), array('Session_Handlers', 'destroy'), array('Session_Handlers', 'gc'));

session_start();

// proceed to use sessions normally
?>

As you see, we’ve created simple methods which only echo when they are called (in real life, you should either save session data into file or db). As you can see, we simple passed arrays to session_set_save_handler, which served us to connect class methods with session callbacks.

Method Overloading in PHP5

Although with release of PHP5 we finaly got some long awaited OOP features, sometimes I really miss overloading capability which exists in languages like Java. I am talking about something like this:

class Overloading_Test
{
  public void hello()
  {
    System.out.println("Hello Anonymous");
  }

  public void hello(String name)
  {
    System.out.println("Hello " + name);
  }


  public void hello(String firstName, String lastName)
  {
    System.out.println("Hello " + firstName + " " + lastName);
  }
}

This way you can call either hello with no arguments at all, or with one or two arguments, and proper method would always be called. Unfortunately, if you try something like this in PHP, it would give you fatal error, because basically, methods cannot be redeclared, since support for overloading is not part of core language like in Java.

However, there is still a way to achieve this Java like overloading functionality by using “magic” methods that are described in PHP Manual. Although it is not clear from manual how could you achieve exact functionality like in Java, I played a little bit with __call function, and get interesting workaround.

<?php

class Overloading_Test
{
  function __call($method_name, $arguments)
  {
    //list of supported methods
    //only 'hello' for this test
    $accepted_methods = array("hello");

    //in case of unexistant method we trigger fatal error
    if(!in_array($method_name, $accepted_methods))
    {
      trigger_error("Method <strong>$method_name</strong> doesn't exist", E_USER_ERROR);
    }

    //we inspect number of arguments
    if(count($arguments) == 0)
    {
      $this->hello1();
    }
    elseif(count($arguments) == 1)
    {
      $this->hello2($arguments[0]);
    }
    elseif(count($arguments) == 2)
    {
      $this->hello3($arguments[0], $arguments[1]);
    }
    else
    {
      return false;
    }
  }

  function hello1()
  {
    echo "Hello Anonymous<br>";
  }

  function hello2($name)
  {
    echo "Hello $name<br>";
  }

  function hello3($first_name, $last_name)
  {
    echo "Hello $first_name, $last_name<br>";
  }
}


$ot = new Overloading_Test();
$ot->hello();
$ot->hello("John");
$ot->hello("John", "Smith");
//this one will produce fatal error
//$ot->test();
?>

If you run this code, you will get something like:

Hello Anonymous
Hello John
Hello John, Smith

So, what is going on here? Whenever we call some undeclared method (which is the case with ‘hello’ method here), magic method __call is called, and two arguments (method name and arguments) are passed to it. For this simple test, we only support overloading of ‘hello’ method, so in case you try any other, we trigger fatal error.

What’s going on further is, we simple check number of argumens passed (by counting $arguments array), and call proper method. For the sake of clarity, I only used simple overloading based on number of arguments, but you could also check for argument type (ie string, integer etc.) and call proper method.

So, as you see, method overloading in PHP5 is not as elegant as in Java, but you can still make it. For more information about ‘magic’ fucntions (there are quite a few for member overloading as well), please visit PHP Manual.