MySQL 8.0 is now finally GA, bringing into play the powerful Document Store set of feature along with Connectors for many of the most popular languages! Also PHP is coming with it’s own extension designed to support all of the new exciting feature coming with this latest MySQL milestone.
The complete web documentation for the MySQL X DevAPI Extension for PHP is available here.
About 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 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.
What does that mean in short? It means that now you can store non homogeneous data in your database without the need to define and specify meticulously the content of the tables, just open a xdevapi session and push your data into the database!
Installing the PHP extension
The easier way to install the extension is by using pecl tool or is possible to download the tarball file directly from this link. There are some dependencies to fulfill in order to use the extension, the most relevant is certainly boost and the protobufs libraries.
PHP Extension for MySQL 8.0 and Document Store.
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, is possible to change this default behavior by providing the proper ssl-mode, like in the example below:
$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 }'
'{ "product" : "Nokia Y", "price":900, "stock" : 3,
"description": "A good mobile phone" }' )->execute();
In this code example the first two lines are for the purpose of creating a schema and a collection within the schema, in all the following samples I’ll use the variable $coll as a reference to the Collection obtained by createCollection
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, in the example code I’m submitting two documents with one add operation, you can add as many documents as you want with a single operation, each document have to be separated by a comma.
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, manipulating the fields &c. The documents I’m inserting in the code sample are easy to understand JSON, if you are not familiar with JSON please have a look here.
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 |
| {"_id": "F5A1F292E55FBDEDFE8394BAF9B00B89", "price": 900, "stock": 3, "product": "Nokia Y", "description": "A good mobile phone"} | F5A1F292E55FBDEDFE8394BAF9B00B89 |
+-----------------------------------------------------------------------------------------------+----------------------------------
2 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.
The $result object returned by the add operation can be used to verify what changes has been applied to the collection or to obtain a list of the ID’s generated by the server while adding the documents.
$item_count = $result->getAffectedItemsCount();
print($item_count." documents has been added to the collection, printing ID's");
$ids = $result->getGeneratedIds();
for( $i = 0 ; $i < $item_count ; $i++ ) {
print("The document ID number ".$i." is ".$ids[$i].PHP_EOL);
}
Manipulating the documents
Let’s see how easily those Collections can be manipulated, for example removing documents can be done by using the straightforward removeOne API, which is a function that expect one single argument, the ID of the document to remove:
//Add some documents, note that I'm providing the IDs myself
$res = $coll->add(
["_id" => "1", "name" => "Carlotta", "age" => 34, "job" => "Dentista"],
["_id" => "2", "name" => "Antonello", "age" => 45, "job" => "Tassinaro"],
["_id" => "3", "name" => "Mariangela", "age" => 32, "job" => "Attrice"],
["_id" => "4", "name" => "Antonio", "age" => 42, "job" => "Urologo"]
)->execute();
//Remove the document with ID 4
$coll->removeOne("4");
Is possible to look for documents using the `find` operation, in it’s most basic implementation the `find` function will require an expression that can be used to match the document to extract from the collection:
//Find all the entries for which the 'age' field is greater than 30
$res = $coll->find("age > 30")->execute();
//Fetch the entries
$data = $res->fetchAll();
//Print the results
for( $i = 0 ; $i < count( $data ) ; $i++ ) {
print($data[$i]["name"]." have more than 30 years!");
}
As last example here’s how is possible to modify the content of a document using the powerful `modify` operation. The only argument to `modify` is an expression that can be used to identify the documents that have to be modified, it’s then followed by one or more operation that define the modification:
//Fill the collection with some documents
$coll->add('{"name": "Sakila", "age": 15, "job": "Programmer"}',
'{"name": "Sakila", "age": 17, "job": "Singer"}',
'{"name": "Sakila", "age": 18, "job": "Student"}',
'{"name": "Arnold", "age": 24, "job": "Plumber"}',
'{"name": "Robert", "age": 39, "job": "Manager"}')->execute();
//This modify operation will change the 'job' to 'Unemployed' for all
//the three Sakila in the collection
$coll->modify("name like 'Sakila'")->set("job", "Unemployed")->execute();
//Add a second job to Arnold, the field 'job' will now on be an array
//of two elements: 'Plumber' and 'Nursey'
$coll->modify("name like 'Arnold'")->arrayAppend('job','Nursey')->execute();
Conclusion
There’s a strong feeling of excitement around MySQL 8.0 and his features, in particular Document Store is going to be a game changes in the industry by providing a powerful and flexible tool into the most popular and recognized DB.