Today we’re going to have a look at some more detailed examples on how to use the MySQL X DevAPI extension for PHP, if you read carefully my previous post about the topic –link here– then you should have already installed a MySQL server with the Document Store plugin and PHP with it’s mysqlx extension, if not then please go back to my first post about the topic and install all this stuff.
We ended up my last post with a simple script like this:
<?php
$uri = "mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled";
$nodeSession = mysql_xdevapi\getSession( $uri );
$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;
?>
Assuming that your MySQL server is running and has the xdevapi plugin installed then once the script is executed then the content of your database should look like this:
mysql> select * from store;
+-----------------------------------------------------------------------------------------------+----------------------------------+
| doc | _id |
+-----------------------------------------------------------------------------------------------+----------------------------------+
| {"_id": "AE26968177A780EDFE83996D3AE80223", "price": 1000, "stock": 2, "product": "iPhone X"} | AE26968177A780EDFE83996D3AE80223 |
+-----------------------------------------------------------------------------------------------+----------------------------------+
1 row in set (0.00 sec)
Today, we’re going to have a deeper look at the Document Store with PHP!
The URI.
First things first, you can’t do anything if you’re not connected with a server.
The way this connection is established is by calling getSession –see later– with the proper URI. The URI is a string containing the credential information, the address of the target server running the xdevapi plugin and some optional parameters which might be provided. Please note that the URI begins with the prefix ‘mysqlx://‘ followed by the remaining stuff, this is a required standard confirmation prefix and the server will refuse the connection if it’s not included in the URI.
By default the server will require a secure connection so in case you’re willing to go without any encryption the option “ssl-mode=disabled” must be provided. The other supported options related to the configuration of SSL are the following: ssl-key, ssl-cert, ssl-ca, ssl-capath, ssl-ciphers, ssl-crl, ssl-crlpath and ssl-no-defaults. The details on how to use all these SSL options is a bit over the point of this post, but I guess someday I’ll prepare a full fledged explanation on how to establish secure connections using X DevAPI.
By default the mysqlx plugin on the server is listening the port 33060, make sure to tune these address/port details to fit your server configuration. The day I’m writing this post beside the SSL settings there are no more optional fields that can be provided within the URI string.
Connection, Schema and Collection creation.
getSession( string URI ) is the function you’re going to use in order to establish a connection with the server, the only argument accepted is the URI string. If the connection is successful getSession will return a NodeSession object which contains a pretty long list of methods that can be used to operate on the Document Store. Here I’m not going to cover all them up since it would take too long, but perhaps in a future post I’ll write introductions to all the magic stuff coming with the extension.
From the NodeSession object we can call createSchema( string name ) which as the name suggest create a schema with the provided name. If the schema already exist in the DB then an error is raised. createSchema returns a NodeSchema object which has the methods for collection and table creation, where a collection is for the Document Store the counterpart of a table in canonical relational database.
This comparison of documents and tables might not be the most accurate ever (conceptually those are two different things that work in different ways etc), but assuming that you’ve no idea what a collection is and that most of the time you’ve been dealing with tables then i think make a bit of sense to do some informal comparison between the two things.
Now to be clear here that a collection is just a table within the database, the Document Store is a technology built over the existing framework of MySQL functionalities and was added on top of that is a set of abstractions and a protocol (and plugins, and extensions or connectors…) to implement those abstractions.
Anyway, all our documents are going to be store inside a collection, to create one we have to call createCollection( string name ) from the NodeSchema object. The method will either raise an error if the collection already exist within the schema or will return a NodeCollection object that could be used to manipulate the collection.
NodeCollection is what will allow us to do the heavy duty work on our collections of documents, we can create,delete or modify documents, replace or search for documents and so on and so fort.
Operating with documents.
Let’s have a look at an extended example of our initial script and see what can we do with those documents, the script cover basic addition of documents on some modification examples:
<?php
$uri = "mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled";
$nodeSession = mysql_xdevapi\getSession( $uri );
$schema = $nodeSession->createSchema( "testx" );
$coll = $schema->createCollection( "store" );
#Adding methods can be done by calling the 'add' function from
#the NodeCollection object. There are three variants of the function,
#for each of them you have to provide one or more JSONs
$result = $coll->add( '{ "product" : "iPhone X", "price":1000, "stock" : 2 }' )->execute();
print 'The generated ID for the document is: ' . $result->getDocumentId() . PHP_EOL;
#This is a second variant of 'add', where multiple ducuments can be inserted
#with a single call.
$result = $coll->add( '{ "product" : "Samsung Y", "price":999, "stock" : 10 } ',
'{ "product" : "Nokia W", "price":399, "stock" : 40 } ',
'{ "product" : "iPhone 8", "price":649, "stock" : 20 } ' )->execute();
#the getAffectedItemsCount return the number of items in the database
#that where affected by the operation which generated the NodeResult object.
#In the last 'add' execution we've included three new documents to the collection,
#so the function should return 3.
$affectedItem = $result->getAffectedItemsCount();
print 'Amount of affected the document is: ' . $affectedItem . PHP_EOL;
#If needed is possible to extract the ID's generated for all the documents,
#this can be done by again calling a method from NodeResult.
foreach( $result->getDocumentIds() as $id ) {
print( $id . PHP_EOL );
}
#To modify a document the procedure is to invoke 'modify' with the expression which
#define to which documents the modification is going to be implemented, followed
#by a set of changed on the documents. In this case I'm just calling 'set' which either
#add a new field OR modify an existing one.
$coll->modify( 'product = "iPhone X" ' )->set( 'note', 'Too expensive!' )->set( 'price', '999' )->execute();
$product = readline( 'Which product do you want to comment?: ');
$comment = readline( 'What is your comment?: ');
#Yet another example of 'modify' where I make use of the binding feature which
#pick a value from an object (in this case $product is a string) and replace the
#placeholders from the modify expression, in this case: ':prod'.
$coll->modify( 'product = :prod ')->set( 'note', $comment )->bind( ['prod' => $product] )->execute();
#In this 'modify' example I'm going something a bit different! Sometimes a field might
#not be just a single value but an array of values, say for example that we need to store
#multiple comments in the item 'comment' of our documents, to do so the arrays functions
#come very handy, in the following two lines I'm appending a note to the 'node' field
#and then deleting a note from the very same array. For more details have a look at the post.
$coll->modify( 'product = "iPhone X" ' )->arrayAppend( 'note', 'And is not that cool anyway!' )->execute();
$coll->modify( 'product = "iPhone X" ' )->arrayDelete( 'note[1]' )->execute();
#'find' search for documents in the collection which match the provided expression
#the function returns a NodeDocResult object on which I'm calling fetchAll to actually
#extract the informations which match my expression.
$nodeDocResult = $coll->find( 'price > 400 and price <= 999' )->execute();
$docs = $nodeDocResult->fetchAll();
foreach( $docs as $doc ) {
var_dump( $doc );
}
?>
Let’s have a look at the output of the script:
fjanisze pecl-database-mysql_xdevapi : php example.php
The generated ID for the document is: 3464E9F4C85238A2FE8399FDA1C5F72F
Amount of affected the document is: 3
3464E9F4C85238A2FE8399FDA1C614EC
3464E9F4C85238A2FE8399FDA1C61509
3464E9F4C85238A2FE8399FDA1C61516
Which product do you want to comment?: iPhone 8
What is your comment?: Who's going to buy such an expensive phone??
array(4) {
["_id"]=>
string(32) "3464E9F4C85238A2FE8399FDA1C614EC"
["price"]=>
int(999)
["stock"]=>
int(10)
["product"]=>
string(9) "Samsung Y"
}
array(5) {
["_id"]=>
string(32) "3464E9F4C85238A2FE8399FDA1C61516"
["note"]=>
string(44) "Who's going to buy such an expensive phone??"
["price"]=>
int(649)
["stock"]=>
int(20)
["product"]=>
string(8) "iPhone 8"
}
And this is the content of the database after the sample script is executed:
mysql> mysql> select * from store;
+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+
| doc | _id |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+
| {"_id": "3464E9F4C85238A2FE8399FDA1C5F72F", "note": ["Too expensive!"], "price": "999", "stock": 2, "product": "iPhone X"} | 3464E9F4C85238A2FE8399FDA1C5F72F |
| {"_id": "3464E9F4C85238A2FE8399FDA1C614EC", "price": 999, "stock": 10, "product": "Samsung Y"} | 3464E9F4C85238A2FE8399FDA1C614EC |
| {"_id": "3464E9F4C85238A2FE8399FDA1C61509", "price": 399, "stock": 40, "product": "Nokia W"} | 3464E9F4C85238A2FE8399FDA1C61509 |
| {"_id": "3464E9F4C85238A2FE8399FDA1C61516", "note": "Who's going to buy such an expensive phone??", "price": 649, "stock": 20, "product": "iPhone 8"} | 3464E9F4C85238A2FE8399FDA1C61516 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+
4 rows in set (0.00 sec)
mysql>
Hopefully this short example highlights the versatility and flexibility of MySQL Document Stores combined with X DevAPI implemented by the X Protocol.
Documents are just JSON’s and the database is highly optimized to handle huge amounts of CRUD operation on documents, let’s be clear here that you can operate on canonical relational table while in parallel working with collections and documents! MySQL today can both operate as relational database and as Document Store, putting together the benefits of two powerful data storage paradigms!
Probably you’ve noticed that the operation on documents all terminate with a call to execute. Only after the call to execute the PHP extension (or connector, for the other languages) process the request and generate a query for the server, anything before execute is in a work-in-progress state.
Quite frankly, for operations like add I found this syntax a bit weird:
$nodeCollectionAdd = $coll->add( '{ "product" : "iPhone X", "price":1000, "stock" : 2 }' );
You can’t do very much with a NodeCollectionAdd object, the only available function is execute. There are debates about the reason of having this execute statement in each operation, but at the moment no clear idea came out from any of them.
One more note about the add function is that the document ID can be overwritten by the user. The ID should be unique within a session and should not exceed the length of thirty-two characters, to overwrite the auto-generated identifier you just need to provide your own as ‘_id‘ field in the JSON:
$result = $coll->add( '{ "_id": "MyCoolID69", "product" : "iPhone X", "price":1000, "stock" : 2 }' )->execute();
Which generate the following document:
mysql> select * from store;
+-------------------------------------------------------------------------+------------+
| doc | _id |
+-------------------------------------------------------------------------+------------+
| {"_id": "MyCoolID69", "price": 1000, "stock": 2, "product": "iPhone X"} | MyCoolID69 |
+-------------------------------------------------------------------------+------------+
1 row in set (0.00 sec)
More details about the document modification
Probably that’s the most interesting part of the extended script example, adding stuff to the database is straightforward since it’s end up to be just a single call to add, things get a bit more complicated with modifications.
The argument for modify is an expression that can be parsed and used to lookup the collection for certain documents. After modify different operations are possible on the retrieved documents, we can add or remove fields with set( string collection_field, string expression_or_literal ) and unset( string collection_field ), the former function actually add a new field or substitute the value of the existing one.
I possible to add or remove elements from an array field from the document –see example above– with arrayInsert( string collection_field[ position ], string expression_or_literal ) which insert the new element somewhere within the array, where position is bounded to 
A set of additional functionalities are provided to modify, those allow to limit the scope of the modification by limiting the amount of documents, skipping some of them or changing the order of the results (and thus in combination with ‘limit’ including or excluding some of them) or for replacing a collection field.
For an overview of all the methods for modify have a look at this picture that I’ve borrowed from here.
Let’s see a simple example script:
#Let's add a bit of stuff in the document store
foreach( range(0,20) as $i ) {
$coll->add('{"product": "ProductName'.$i.'", "price": '.rand(10, 1000).', "Qty": '.rand(0,100).'}' )->execute();
}
#Let's add a comment to the first 5 cheaper products. The interesting thing in this example is that I'm
#operating on all the documents by forcing the modify expression to be always true, in this way
#every document always match the expression and thus is selected for modification.
$coll->modify('true')->sort('price asc')->limit(5)->set('note','This is a super price!')->execute();
#Let's reduce the price of the five producs with the largest quantity in stock,
#we might want to sell them sooner or later! To do so, first find those items
#so we can get extract the price and apply a discount.
$res = $coll->find('true')->sort('Qty desc')->limit(5)->execute();
$items = $res->fetchAll();
#Apply the discount
foreach( $items as $item ) {
#Apply a 20% discount price
$new_price = round($item['price'] * 0.80);
#Modify all the items that we found before
$coll->modify('_id = "'.$item['_id'].'"')->replace('price', (int)$new_price)->execute();
print('The price of '.$item['product'].' was '.$item['price'].' and now is '.$new_price.PHP_EOL);
}
To test this latest example just add the connection and schema/collection creation code which is missing (in order to save space). I’ll not post the output since is exactly what you might expect and, it actually depends on the values provided by rand so at every execution some differences might appear. Just run the script on you environment, play with it and verify that is all good.
The sort( string expression OR multiple string expressions ) apply to your documents the order you’ve requested as argument based on the field values that are specified. (Guess what!?…) Two orderings are possible, ASC and DESC. In this latest script I’ve introduce also the find( string search_expression ) function, the expression has the same meaning as for modify and is subject mostly to the same rules.
I’ll be back with more examples on find in a future post.
Conclusion
Here I’ve briefly introduced you to some more examples on how to use the MySQL X DevAPI extension for PHP, there are certainly areas where additional information might need to be provided, for sure in the future I’ll come to this topic with more code.
Today the MySQL Document Store is still not a popular product and to be honest also the user base is limited, but keep in mind that this is and under development technology and a real attempt to introduce it to the big audience was still not made, what w’re trying to do in the meanwhile at Oracle MySQL it to provide the users with a powerful API to enable him to benefit of MySQL as a Document Store and enlarge the capabilities of this powerful database in a way that’s not possible while remaining just within the relational DB world!
Thanks for reading.