Tuesday, November 20, 2012

Auto-Constructing DDL for a MySQL federated table from and existing table

For anyone looking for a tool to automatically generate a DDL statement for a federated table from the DDL statement of an existing table, here is the PHP script (note: this was only tested with MySQL 5.5)

Please see http://forums.mysql.com/read.php?10,512901,513020#msg-513020

 parse_str(implode('&', array_slice($argv, 1)), $_GET);  
 $mysqli = new mysqli('target_host', 'login_id', 'password', 'db_name', '3306' );  
 if(!$mysqli) die("Connection error. Aborting\n");  
 // this is the name of the server used in the CREATE SERVER statement  
 $fedlink_server = $_GET['fedlink_server'] = "my_preconfigured_fedlink";  
 $source_table = $_GET['source_table']   ;//= "accounts";  
 $target_table = $_GET['target_table']   ;//= "fedlinked_accounts";  
 if ((strlen($source_table) ==0) OR (strlen($target_table) ==0))  
   die("Required values: source_table=?, target_table=?\n\n");  
 echo "source_table=$source_table\n";  
 echo "target_table=$target_table\n";  
 $sql = "SHOW CREATE TABLE `$source_table`;";  
 $mysqli_result = $mysqli->query($sql);  
 if (!$mysqli_result) die("The query failed to return results\n");  
 $row = $mysqli_result->fetch_all();  
 $ddl = $row[0][1];  
 $ddl = str_replace("''", "'", $ddl);  
 $ddl_arr = preg_split("/\n/", $ddl);  
 $ddl_end = $ddl_arr[sizeof($ddl_arr) -1];  
 $ddl_arr[0] = "CREATE TABLE `$target_table` (";  
 $charset= (substr_count($ddl_end, "DEFAULT CHARSET=utf8") == 1)  
     ? $charset = " CHARSET=utf8"  
     : "";  
 $ddl_arr[sizeof($ddl_arr) -1]  
   = ") ENGINE=FEDERATED $charset CONNECTION='$fedlink_server/$source_table';";  
 echo join("\n", $ddl_arr);  
Simply execute the script as follows (for example):

php -f the-script-in-a-file.php source_table=accounts target_table=linkedtable_account

NOTE: This script was written to suite MY needs and won't necessarily work for every situation.