Things are getting serious here!
The latest X DevAPI for PHP extension release brings into play a ton of new features, with this post I’m going to introduce you to the basic of how to setup the environment and use the extension within your PHP applications!
And yes, that’s a totally random fact that it just happen I’m one of the guys working on the PHP extension which allows to use MySQL as a Document Store, just in case you’re wondering why I’m writing about the PHP extension and not for example about.. the C++ connector!
MySQL as Document Store.
The X DevAPI for PHP is an extension which allows the user to access MySQL with installed the X Plugin as a document store via the X DevAPI API and the related underlying protocol.
A document store differs substantially from a traditional relational database organization where a schema needs to be provided in order to push data into the database, a document store permit to insert information in a non-uniform manner, thus without the requirement of defining and maintaining a specific set of schema’s –and their links– needed to properly store the object being recorded.
This database model became very popular with NoSQL and other similar products, the MySQL document store X Plugin has the purpose of allowing the MySQL users to retain their current MySQL configurations and being able to benefit from the new schema-less data organization.
Different language connectors are provided to access MySQL as Document Store, and in this post I’m going to focus on the powerful and widely used PHP language and it’s xdevapi extension which is the key use this MySQL functionality.
Long story short, now you can store random stuff in your database! There’s no need anymore to define and specify meticulously the content of the tables, just open a xdevapi session and push your data into the database!
The MySQL Setup.
MySQL is needed to do work with the MySQL Document Store!
If you don’t have an OS that support software installation from package repositories then you need to go to this page to download the latest released version, pick the one which fit your OS and install the package. One note about the download: Don’t be fooled by the page which ask you to sign up for the Oracle account, you don’t need any account! Click the button in the bottom part of the page and just download the thing.
Once you’ve your database is up and running enter a MySQL client shell and prepare the database to handle X DevAPI:
mysql> install plugin mysqlx soname 'mysqlx.so'; Query OK, 0 rows affected (0.01 sec) mysql>
That should be all for the database setup part, in truth there’s not that much work to do if you already are working with MySQL, just install the plugin and you’re ready to go!
The extension!
To work with the MySQL document store you need to install the X DevAPI for PHP extension mysql_xdevapi. You can download the extension from the pecl.php.net website, at the time of this post the last release is from 2/11/2017 with a release version 8.0.3.
Depending on your operating system you’ll have a source tree to build or a Windows DLL to include somewhere. Also, to build the code from the source you can clone the repo from our GitHUB here, detailed instruction for the various supported operating system are available in the readme page of the github repo.
If you’re running Linux then probably you might prefer to download and install the extension from a Linux repo –using dnf,yum,apt or any other diabolic mechanism-, in particular for Fedora-stylish Linux you can have a look at the Remi’s RPM repos which are up to date with the latest packages (for detailed instructions on how to setup the repos look here).
Of course I can’t close this installation topic without mentioning the official pecl tool for PHP, on my machine a query for the mysql_xdevapi package is showing the latest greatest extension release:
fjanisze pecl-database-mysql_xdevapi : pecl remote-info mysql_xdevapi Package details: ================ Latest 8.0.3 Installed - no - Package mysql_xdevapi License PHP Category Database Summary MySQL X DevAPI for PHP
Anyway, no matter which way choose to go for the installation of the extension, make sure that mysql_xdevapi is listed while calling php -m:
fjanisze pecl-database-mysql_xdevapi : php -m [PHP Modules] . . libxml mysql_xdevapi mysqli mysqlnd . . [Zend Modules]
From the output I’m cutting most of the stuff just to point out what you should look for when verifying if the extension is working.
During the installation or the compilation of the code some errors might popup, so be ready to install on your system those libraries:
- Boost and boost-devel
- Protoc, and the protobuf libraries (including the -devel one)
- The php-mysqlnd extension
- libssl and openssl (including -devel)
What you’ll actually need depends mostly on the way you’re going to install the extension, in this post I’ve include the links to all the pages with detailed instruction on how to proceed. If you encounter any problem please feel free to contact me!
Let’s write some PHP code
Now, if you’ve all your setup ready then it’s time to run a small script and check what basic operation are possible with MySQL document store and PHP.
In this post I’ll just show you some very basic operation, a new post will follow soonish with an enlarged example and more details, I don’t want this lecture to get too heavy.
First of all we need to create a connection to the database, in order to do so you need to access the mysql_xdevapi namespace and call the getSession function. getSession accepts as parameter the URI string with the credentials and address of the target server, in my example the URI is going to be: “mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled“, probably during your configuration of MySQL you’ve chosen a different password so please use yours instead of XXX!
The URI strings starts with the required “mysqlx” followed by your credentials and the address of the server, the port 33060 is the default one where MySQL is listening for X DevAPI connections. Also, by default the connection with the server is going to be over SSL and since here I’m going plain, I have to tell the server that SSL will not be enabled.
So, the connection code is:
$uri = "mysqlx://root:XXX@localhost:33060?ssl-mode=disabled"; $nodeSession = mysql_xdevapi\getSession( $uri );
$nodeSession is the object which handle the session for the current connection. Let’s see how to create a schema, a collection for documents and how to add a simple document:
$schema = $nodeSession->createSchema( "testx" );
$coll = $schema->createCollection( "store" );
$result = $coll->add( '{ "product" : "iPhone X", "price":1000, "stock" : 2 }' )->execute();
print 'The generated ID for the document is: ' . $result->getDocumentId() . PHP_EOL;
With the add you can trigger the insertion of a new document into the collection, each add has to be followed by the execute command. The same execute command is required by most of the DevAPI functions, before the execution of the request additional operations could be performed like adding more documents &c. The document I’m inserting in the code sample is an easy do understand JSON, if you are not familiar with JSON please have a look here.
The output you should see when running the example script should be something like:
fjanisze pecl-database-mysql_xdevapi : php example.php The generated ID for the document is: ABD24B17C294181AFE8394BAE2A1397F
And we can verify the content of the database as well:
mysql> select * from testx.store;
+-----------------------------------------------------------------------------------------------+----------------------------------+
| doc | _id |
+-----------------------------------------------------------------------------------------------+----------------------------------+
| {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B88", "price": 1000, "stock": 2, "product": "iPhone X"} | F5A1F292E55FBDEDFE8394BAF9B00B88 |
+-----------------------------------------------------------------------------------------------+----------------------------------+
1 row in set (0.00 sec)
mysql>
So, from this last shell output is clear what a collection is and how a document looks like! A collection is just a table with two columns, one representing the document itself which is a JSON and the other is the unique identifier of the document –which is a varchar-! Those ID’s are generate automatically for each inserted document, you can provide your own ID’s if you want, but don’ do that! I wrote the code which generate those ID’s, so just make me happy and use the generated one 🙂
mysql> describe testx.store; +-------+-------------+------+-----+---------+------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+------------------+ | doc | json | YES | | NULL | | | _id | varchar(32) | NO | PRI | NULL | STORED GENERATED | +-------+-------------+------+-----+---------+------------------+ 2 rows in set (0.00 sec) mysql>
Ok, I think we can stop here! In the next post I’ll provide a detailed example of how to use the extension and document store!
Thanks for reading!