Wednesday, June 20, 2018

Building the PHP MySQL XDevAPI PECL Extension on MySQL 8.0.11 and PHP 7.2 for the MySQL Document Store

The MySQL Document Store is a NoSQL JSON document store built upon well known MySQL database technology.  PHP runs about eight percent of the Internet.  So putting the two together is a big priority for me. So this blog post is about getting all this together on a Ubuntu 18.04 system.

Note that I will be teaching PHP and the X DevAPI at Oracle Code One and hopefully in some tutorials/workshops this year.  These session will feature the X DevAPI installed on Virtual Box images and I probably will not have time to cover these steps in detail but I will point to this as reference material.


PHP 7.2 

PHP's performance has really skyrocketed with the seven series and the newer betas are looking very impressive.  But to use the new X Devapi you will need to get the shared object for it into your PHP server. 

The MySQL X DevAPI PECL Extension


You can find the MySQL X DevAPI among the many PECL extensions and you can get the latest tarball of source code and also a link to the homepage.  And on that home page are directions for installing/configure the extension. The docs say to do the followings and assume you already have MySQL 8.0.11 installed (or go to https://dev.mysql.com/downloads for the MySQL apt repo software; Install it and then run  sudo apt-get install mysql-shell mysql-server).

$ apt install build-essential libprotobuf-dev libboost-dev openssl protobuf-compiler
$ add-apt-repository ppa:ondrej/php
$ apt install php7.2-cli php7.2-dev php7.2-mysql php7.2-pdo php7.2-xml
$ pecl install mysql_xdevapi

And a quick program to make sure PHP could use the X Devapi.

<?php

$session = mysql_xdevapi\getSession("mysqlx://root:oracle@localhost:33060");
if ($session === NULL ) {
  die("Connection not established!\n");
}

echo "Connection established!\n");

?>

Pretty simple, eh?  Well, I had problems. A call to an undefined function mysql_xdevapi\getSession error.  For some reason the X DevAPI shared object was not being found. 

A Fix

Now there is a way to get things to work but it takes a little work.
1. cd /etc/php/7.2/mods-available
2. cp mysqli.ini mysql_xdevapi.ini
3. edit mysql_xdevapi.ini and change mysqli to mysql_xdevapi on the last line.
4. cd /etc/php/7.2/cli/conf.d
5. ln -s /etc/php/7.2/mods-available/mysql_xdevapi.ini 20-mysql_xdevapi.ini

Now the first test program runs and the Connection established message is displayed!

A Bigger Test


Here is a bigger test program:

 #!/bin/php
<?php

$session = mysql_xdevapi\getSession("mysqlx://root:hidave@localhost:33060");
if ($session === NULL) {
  die("Connection could not be established");
}

$dave = [
  "name" => "Dave",
  "state"  => "TX",
  "category" => 1,
  "job"  => "Community Manager"
];
$alex = [
  "name" => "Alex",
  "age"  => 28,
  "category" => 2,
  "job"  => "House Flipper"
];

$schema = $session->getSchema("test");
$collection = $schema->createCollection("stuff");
$collection = $schema->getCollection("stuff");

$collection->add($alex, $dave)->execute();
var_dump($collection->find("name = 'Dave'")->execute()->fetchOne());
?>


So now we have a working PHP 7.2 with the MySQL XDevAPI PECL extension.  Later we will look into more uses.