Dump A MySQL Database To YAML Using PHP

The Problem

I was recently working on a project that is built on WordPress / BuddyPress. I built up some content using the CMS features of WordPress in my development server and then I transfered the WordPress content to a beta server using the export/import functionality. That kind of stinks having to do that every time I want to push updates to my beta server. So I looked into using Doctrine to generate data fixtures which I could repeatedly run against the different database. Doctrine supports YAML files, which are extremely easy to edit by hand, so it seemed like a good idea.

So what’s the problem? Well, I didn’t currently have any of my data in YAML files. I didn’t want to had edit them or do some sort of copy and paste madness. So, I wrote a script in PHP to dump my data into a YAML file.

The Solution

I’m not going to do much explanation because the code is fairly well commented. You can download the file here or you could just copy and paste it from below, but you may get some weird character conversions. The script can generate YAML for all the tables in your database, for a specific list of tables or you can specify an SQL query. Just execute the script without any arguments to see the usage statement (or just read it at the top of the file). Without further ado, here’s the script.

#!/usr/bin/env php

        dumpyaml.php [options] -d  -t table1,table2
        dumpyaml.php [options] -d  -q \"SELECT * FROM `mytable`\" -qn custom_name

  -h        MySQL database host
  -u        MySQL username
  -p        MySQL password
  -d    Name of the MySQL database to dump
  -t    Comma delimited list of tables to dump from database
  -q \"\"    SQL query to dump
  -qn   Name of the \"table\" when using the -q option
  -n              Convert table names to class names

";
}

/**
 * Reads/parses the command-line arguments
 */
function read_args() {

  global $argc, $argv, $DBHOST, $DBUSER, $DBPASS, $DBNAME, $TABLES, $DBQUERY, $QUERYNAME, $CONVERT_NAMES;

  while(!empty($argv)) {

    $arg = array_shift($argv);
    switch($arg) {
      case '-h':
        $DBHOST = array_shift($argv);
        break;
      case '-u':
        $DBUSER = array_shift($argv);
        break;
      case '-p':
        $DBPASS = array_shift($argv);
        break;
      case '-d':
        $DBNAME = array_shift($argv);
        break;
      case '-t':
        $TABLES = explode(",", array_shift($argv));
        break;
      case '-q':
        $DBQUERY = array_shift($argv);
        break;
      case '-qn':
        $QUERYNAME = array_shift($argv);
        break;
      case '-n':
        $CONVERT_NAMES = true;
        break;
    }
  }

  // Validate Command-Line Arguments
  $retVal = true;
  if (empty($DBHOST)) {
    echo "-h  is required\n";
    $retVal = false;
  }
  if (empty($DBUSER)) {
    echo "-u  is required\n";
    $retVal = false;
  }
  if (empty($DBPASS)) {
    echo "-p  is required\n";
    $retVal = false;
  }
  if (empty($DBNAME)) {
    echo "-d  is required\n";
    $retVal = false;
  }
  if (!empty($TABLES) && !empty($DBQUERY)) {
    echo "-t  and -q \"\" are mutually exclusive options\n";
    $retVal = false;
  }
  if (!empty($DBQUERY) && empty($QUERYNAME)) {
    echo "-qn  is required when using -q \"\"\n";
    $retVal = false;
  }
  return $retVal;
}

/**
 * Takes a table name with underscores and converts it to something
 * like a class name (CAML case and remove the underscores).
 */
function convert_name_to_class($string) {

  // Return Value
  $retVal = '';

  $tokens = explode('_', $string);
  foreach($tokens as $token) {
    $retVal .= strtoupper(substr($token, 0, 1));
    if (strlen($token) > 1) {
      $retVal .= substr($token, 1);
    }
  }

  // Return Value
  return $retVal;
}

/**
 * Takes a SQL query and dumps YAML
 */
function sql_to_yaml($link, $sql, $table) {

  global $CONVERT_NAMES;

  // Run the query
  $result = mysql_query($sql, $link);

  if ($result) {

    // Output the table name
    echo ($CONVERT_NAMES === true) ? convert_name_to_class($table) . ":\n" : "{$table}:\n";

    // Loop over the result set
    while ($row = mysql_fetch_assoc($result)) {

      // Output the row/collection indicator
      echo "  -\n";

      // Loop over the columns output names and values
      foreach ($row as $key => $value) {

        // Do have any newlines or line feeds?
        $literalFlag = (strpos($value, "\r") !== FALSE || strpos($value, "\n") !== FALSE) ? "| " : "";

        // Output the key/value pair
        echo "    {$key}: {$literalFlag}{$value}\n";
      }
    }
  }

  // Free the result resources
  mysql_free_result($result);
}

/**
 * Retrieves the database tables from the database and puts them into the $TABLES array
 */
function get_database_tables($link) {

  global $TABLES;

  // Run the query
  $result = mysql_query('SHOW TABLES', $link);

  if ($result) {

    while ($row = mysql_fetch_row($result)) {
      $TABLES[] = $row[0];
    }
  }

  // Free the result resources
  mysql_free_result($result);
}

/**
 * Main program loop
 */
if (read_args()) {

  // Open database connection
  $link = mysql_connect($DBHOST, $DBUSER, $DBPASS);
  if (!$link) {
    die("Could not connect to DB [{$DBUSER}:{$DBPASS}@{$DBHOST}] :: " . mysql_error()) . "\n";
  }

  // Select the database
  if (mysql_select_db($DBNAME)) {

    // Output header
    echo "---\n";

    // Query Mode
    if (!empty($DBQUERY)) {
      sql_to_yaml($link, $DBQUERY, $QUERYNAME);
    }

    else {

      // If the user has not specified tables then query for them
      if (empty($TABLES)) {
        get_database_tables($link);
      }

      // Loop over tables and output YAML
      foreach ($TABLES as $tbl) {
        sql_to_yaml($link, "SELECT * FROM {$tbl}", $tbl);
        echo "\n";
      }
    }

    // Output footer
    echo "...\n";
  }
  else {
    echo "Could not select database [{$DBNAME}] :: " . mysql_error() . "\n";
  }

  // Close Database
  mysql_close($link);
}
else
{
  usage();
}
?>