MySQL to VoltDB: Experiences from the field

written by Andrew Wilson on September 4, 2012 with no comments

Francis Pelland is the Technical Lead for Social Game Universe developing the next generation of The Lightning Platform. The Lightning Platform lets developers build high performance, scalable data platform and provides the management, analytics and the tools to promote and grow social games. He wrote a short guide to help his team get through the transition for PHP developers moving from MySQL to VoltDB.   Here’s an excerpt:

Is there PhpMyAdmin or similar?

Yes there is!  This tool is called Web Studio.  This will allow you to navigate the tables (but not browse its content).  You can see in real time all the processes and queries running through the database.  You will have the ability to run ad hoc queries and see what’s in the database, by clicking on the button at the top, to the left of the exclamation point (to execute the query you will click the exclamation point). Minus some features like sub-queries, the query format is identical to MySQL.  Always use single quotes when sending strings.

I need to change some queries or fix bugs

Every query or stored procedure is defined or declared in an xml file called project.xml.  Most of the SQL statements are defined in the project.xml, while some more complicated ones are in java stored procedures.   If you need to fix bugs, change the queries or do anything else, you can change the project.xml rebuild the catalog and redeploy it. This process is a bit manual.

How do I execute a procedure in PHP?

Queries

If you use the PDO extension for MySQL, you may be sanitizing your results by adding a question mark “?” wherever you intend to have a variable like this “SELECT * FROM items WHERE itemID = ?” otherwise, you will process the query with the results in line.

In VoltDB, you will put the name of the procedure as it is defined in the project.xml file or using the @adhoc system procedures.

Parameters

If you use the PDO extension in MySQL but aren’t adding question marks to your queries or are using the native PHP functions for communicating with MySQL, you won’t be passing parameters, as the values are already embedded within the query.  Otherwise, in order to associate the question marks with a value, you will pass an array of parameters in the same order of all your question marks (“?”) found within your query.

Like MySQL, VoltDB returns an associative array, where the keys are the column names.  Note that the column names will be in uppercase rather what you may have specified in your table definitions.  A solution for ensuring the case of the column name matches what you expect is to specify the column name in the query – ex: whether “SELECT age as “age” FROM user WHERE userID = 1” will result in the array key being “age” rather than “AGE”.

Results

VoltDB returns an array of VoltTable objects. You move through the rows through an iterator. You get a column from a row through an associative array.

This is a short example application that reports on the VoltDB Voter example application:

connect($hostname, '', '');
  echo "Connected\n";
/*
* All params are strings. If the procedure was not called before, this will
* also create a procedure with strings as param types.
*/
$resp = $voltClient->invoke("Vote", array(3, 4, 5));
if ($resp === null) {
  echo "invoke had an error\n";
  } else {
  echo "invoke returned a response\n";
}
if ($resp->statusCode() != VoltInvocationResponse::SUCCESS) {
  echo "status " . $resp->statusCode() . " " . $resp->statusString() . "\n";
  } else {
  echo "Success!!!\n";
  /* Iterate through all returned tables */
  while ($resp->hasMoreResults()) {
    $t = $resp->nextResult();
    echo "Next result" . $t->statusCode() . " row count " . $t->rowCount() . "\n";
    /* Iterate through all rows in the table */
      while ($t->hasMoreRows()) {
      $r = $t->nextRow();
      /* The returned row is a PHP array, print it */
      print_r($r);
      }
    }
  }
} catch (Exception $e) {
  echo"Exception " . $e->getCode() . "\n";
  return;
}
?>

The code demonstrates how to connect to a VoltDB instance, run a series of queries and display the outcomes and any error messages.