About Filip Janiszewski

Filip is a Sw. Enginner with years of experience in big scale projects, he's passionate about technology and loves to learn new things. He's the kind of guy who needs to be continuously challenged whilst developing in his professional career. For more details have a look at http://filipjaniszewski.com

Introducing the MySQL X DevAPI PHP Extension for MySQL 8.0

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:

$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:

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:

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.

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:

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:

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:

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.

MySQL X DevAPI 8.0.3 for PHP is here!

Dear MySQL users,

I’m glad to announce that MySQL X DevAPI extension for PHP 8.0.3 has been recently released!

If for some reason you don’t know what this is all about, then in short the MySQL X DevAPI for PHP add support for the new X DevAPI in PHP, and it’s released as an extension to the language. The X DevAPI enables application developers to write code that combines the strengths of the relational and document models –MySQL as a Document Store– using a modern NoSQL-like syntax that does not assume previous experience writing traditional SQL.

For general documentation about how to get started using MySQL as a document store, see “Using MySQL as a Document Store”.

Download the extension.

Depending on your operating system and working environment, you can download the extensio from:

  • Direct link to the mysql_devapi extension pecl web page, here.
  • On Fedor-ish Linux system, use the Remi’s repo.
  • Clone the source code repo, here.

Further instruction on how to install the extension with samples are included in the readme script and documentation.

This new release contains many bug fixes, refactoring’s and improvements that are a step forward in our work to improve the quality of the code base and thus of the extension itself, beside the minor tasks we were able to deliver some big features:

Support for Array or Object “contains” operator:

New CONTAINS and NOT_CONTAINS operators introduced in any expression valid in CRUD operations. They are different from the IN operator, in that IN works with SQL and requires a set of literals at the right side. CONTAINS should accept JSON values at the left and right sides of the operator,including expressions that generate a JSON value.
Example (Where $coll is a Column object):

New API’s: getOne, removeOne, replaceOne, addOrReplaceOne:

This extension is specific to Collections. These are commands that operate at a single document level, unlike the other CRUD commands that operate on all documents that match a filter the following collection commands are introduced as a set of direct-execution complementary operations that reference documented by id, as opposed to a free-form expression.

When working with small documents (majority of the time), the simplest and easiest way to change a certain document is to use a load/modify/save pattern:

New row locking mechanism for Crud.Find / Table.Select

The MySQL SELECT statement supports now locking matching rows, for reads and for writes (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE).

The find() CRUD method is extended to support this feature, allowing safe, transactional document updates on collections. Transaction support is an important MySQL differentiator compared to other NoSQL databases. Example:

Client A execute:

Client B execute:

Then Client A commits:

And finally Client B can continue:

Support for the new MySQL Server 8.x, including fixes for phpize build.

Large refactorings and fixes where made by the team to allow this PHP extension to fully support he new MySQL server 8.x, also the problems with the phpize build are now solved and we’re happy to deliver a rock solid release with plenty of support for the new MySQL’s.

Thanks for reading,

On behalf of the MySQL/Oracle X DevAPI for PHP Team, Filip Janiszewski