MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
MySQL X DevAPI 8.0.3 for PHP is here!
Posted on Nov 27, 2017 by Filip Janiszewski
Category: Document Store, JSON
Tags: connectors, mysql-development, mysql-news, mysqlx, php, xdevapi

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

$coll->find('"Spavatore" IN jobs[*].title AND "Spavare" IN hobby')->execute();
$coll->find('("Spavatore" IN jobs[*].title OR "Mangiatore" IN jobs[*].title ) AND 12000 IN jobs[*].Salary')->execute();
$coll->find('true IN [(1>5), !(false), (true || false), (false && true)] AND _id > 5')->execute();
$coll->find('true IN [1-5/2*2 < 3-2/1*2] AND $.code > $._id')->execute();

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.

bool Collection.replaceOne(string id, Document doc)
Updates (or replaces) the document identified by id with the provided one, if it exists.

bool Collection.addOrReplaceOne(string id, Document doc)
Add the given document. If the id or any other field that has a unique index on it already exists in the collection, it will update the matching document instead.

Document Collection.getOne(string id)
Return the document with the given id. This is a shortcut for:
    Collection.find("_id = :id").bind("id", id).execute().fetchOne()

Result Collection.removeOne(string id)
Removes the document with the given id. This is a shortcut for:
    Collection.remove("_id = :id").bind("id", id).execute()

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:

doc = collection.getOne(id);
doc["address"] = "123 Sesame Street";
collection.replaceOne(id, doc);

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:

sessionA.startTransaction();
doc = collection.find("_id = 123").lockExclusive().execute().fetchOne();
doc["xxx"] = "foo";
doc["counter"] = doc["counter"] + 1;
collection.modify("_id = 123").replace(doc).execute();

Client B execute:

sessionB.startTransaction();
doc = collection.find("_id = 123").lockExclusive().execute().fetchOne();

# The document with _id = 123 is already locked by Client A, so Client B will block now.

Then Client A commits:

sessionA.commit();

# The lock on _id = 123 is released, so Client B can now continue

And finally Client B can continue:

doc["xxx"] = "bla";
doc["yyy"] = "bar";
doc["counter"] = doc["counter"] + 1;
collection.modify("_id = 123").replace(doc).execute();
sessionB.commit();

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

 Search
Categories