mirror of
https://github.com/php/pecl-database-mysql_xdevapi.git
synced 2026-03-23 23:02:08 +01:00
291 lines
9.9 KiB
PHP
291 lines
9.9 KiB
PHP
<?php
|
|
/*
|
|
With X DevAPI we can connect directly to a specific session by a call to getSession
|
|
or invoke getSession to connect to one or *more* servers.
|
|
|
|
Details: https://dev.mysql.com/doc/x-devapi-userguide/en/xsession-vs-node-session.html
|
|
|
|
For using the X DevAPI a MySQL Server with enabled X Plugin is required.
|
|
The X Plugin is part of MySQL 5.7.12. For instructions about installation and configuration
|
|
please refer to the MySQL documentation at:
|
|
|
|
http://dev.mysql.com/doc/refman/5.7/en/document-store-setting-up.html
|
|
|
|
The XDevAPI extension for PHP export the functions used to initiate the connection,
|
|
the syntax is: mysql_xdevapi\<name of the function>
|
|
|
|
Where mysql_xdevapi is the 'namespace' where those functions are declared.
|
|
|
|
If the connection failed the function will raise an exception (invalid user name or password, &c)
|
|
*/
|
|
|
|
$session = mysql_xdevapi\getSession("localhost","root","");
|
|
var_dump($session);
|
|
|
|
/*
|
|
Once we have a connection established, we might want to create a new
|
|
schema for the purpose of storing doc's.
|
|
|
|
We can do this by calling createSchema from the current session.
|
|
|
|
On server side is possible to verify the existence of the schema by running
|
|
'show databases;', on my machine this will display:
|
|
|
|
mysql> show databases;
|
|
+--------------------+
|
|
| Database |
|
|
+--------------------+
|
|
| information_schema |
|
|
| mysql |
|
|
| performance_schema |
|
|
| products |
|
|
| sys |
|
|
+--------------------+
|
|
|
|
The 'products' schema exist.
|
|
*/
|
|
|
|
$schema = $session->createSchema("products");
|
|
|
|
/*
|
|
Now we need create a new collection in order to store docs,
|
|
this can be performed by a call to createCollection, the argument
|
|
provided is the name of the new collection which will be stored in "products"
|
|
*/
|
|
|
|
$collection = $schema->createCollection("best_products");
|
|
|
|
/*
|
|
Till now I've created a schema and a collection, but those might have
|
|
been already in the database, to obtain an already existing schema
|
|
and colletion the user can make use of getSchema and getCollection:
|
|
*/
|
|
|
|
$schema = $session->getSchema("products");
|
|
$collection = $schema->getCollection("best_products");
|
|
|
|
|
|
/*
|
|
But a document store make no sense if we don't store... documents.
|
|
|
|
For the sake of this sample, I've created a file with some product information
|
|
which I want to load in the document store. Clearly the data might be coming
|
|
from any other source.
|
|
*/
|
|
|
|
$products = file("product_data.txt",
|
|
FILE_IGNORE_NEW_LINES);
|
|
foreach ($products as $key => &$product) {
|
|
$arr = explode("\t", $product);
|
|
$product = array('code' => $arr[0],
|
|
'name' => $arr[1],
|
|
'desc' => $arr[2],
|
|
'price' => floatval($arr[3]),
|
|
'aval' => intval($arr[4]));
|
|
|
|
/*
|
|
Is possible to add documents to the store in multiple ways, in this sample
|
|
I've an array which represent a collection of products which I want add
|
|
to the collection.
|
|
|
|
A simple call to the function 'add' with the array as argument is sufficient,
|
|
is worth noticing that in order to execute the 'add' operation an explicit call
|
|
to 'execute' is required, this is true for most of the operations we're going
|
|
to perform in this sample.
|
|
*/
|
|
|
|
$collection->add($product)->execute();
|
|
}
|
|
|
|
/*
|
|
Is possible to add documents to the collection by providing JSON strings:
|
|
*/
|
|
|
|
$collection->add('{"code": 44, "aval": 3, "desc": "A pen!", "name": "SuperPen", "price": 3.22}')->execute();
|
|
$collection->add('{"code": 434, "aval": 43, "desc": "A pencil!", "name": "SuperPencil", "price": 2.22}')->execute();
|
|
|
|
/*
|
|
Now that we have some documents in our collection, let's lookup
|
|
the database for the three most expensive products in our catalogue.
|
|
|
|
For this purpose I'm calling the function 'find' without arguments,
|
|
(we look in all the data available), then I chain a call to 'sort'
|
|
in order to have the data sorted by the price in ascending order,
|
|
followed by 'limit' call, which set the amount of data I want to retrieve.
|
|
|
|
This call returns a 'DocResult' object, from which I can fetch the
|
|
retrieved rows:
|
|
1) fetchAll, will fetch all the rows
|
|
2) fetchOne, will fetch just one row
|
|
*/
|
|
|
|
$expensive = $collection->find()->sort("price desc")->limit(3)->execute();
|
|
$exp_data = $expensive->fetchAll();
|
|
|
|
/*
|
|
The call to print_r will output the three most expensive items:
|
|
|
|
Array
|
|
(
|
|
[0] => Array
|
|
(
|
|
[doc] => {"_id": "df768423a59c11e69f47bcee7b785c27", "aval": 6, "code": "ABABA", "desc": "Available in two colors!", "name": "PHP Expert", "price": 2499.99}
|
|
)
|
|
|
|
[1] => Array
|
|
(
|
|
[doc] => {"_id": "df74ea6fa59c11e69f47bcee7b785c27", "aval": 3, "code": "51A51", "desc": "Your favorite plastic surgeon", "name": "PlasticFace", "price": 1499.99}
|
|
)
|
|
|
|
[2] => Array
|
|
(
|
|
[doc] => {"_id": "df74ea81a59c11e69f47bcee7b785c27", "aval": 2, "code": "56411", "desc": "The best of the best!", "name": "Programmer2", "price": 299.99}
|
|
)
|
|
|
|
)
|
|
|
|
*/
|
|
|
|
print_r($exp_data);
|
|
|
|
/*
|
|
I want to change the amount of available units of the product "SuperDent".
|
|
now I have +10 produts in my stock, to perform this operation I need
|
|
to modify the document, for this purpose I'll upload the new value with a
|
|
'set' call from 'modify'.
|
|
*/
|
|
|
|
$collection->modify("name like 'SuperDent'")->set("aval", 15)->execute();
|
|
|
|
/*
|
|
Is possible to use the binding feature instead of hardcoding
|
|
the product name, this code is equivalen (in terms of result)
|
|
to the previous instruction:
|
|
*/
|
|
$productName = "SuperDent";
|
|
$collection->modify("name like :pr_name")->bind(["pr_name" => $productName])->set("aval", 15)->execute();
|
|
|
|
/*
|
|
I might want to add a new attribute in one or more of my documents,
|
|
for example all the items for which I have less than 10 products available
|
|
shall now have a new "comment" attribute.
|
|
|
|
Adding attributes change only the selected documents in the collection
|
|
and does not effect the other.
|
|
*/
|
|
$message = "Stock too low, order more items.";
|
|
$collection->modify("aval < 10")->set("comment",$message)->execute();
|
|
|
|
/*
|
|
Again, parameter binding is allowed, is possible to provide
|
|
the value directly to 'bind' instead to pointing to a variable.
|
|
*/
|
|
|
|
$collection->modify("aval < :amount")->bind(["amount" => 10])->set("comment", $message)->execute();
|
|
|
|
/*
|
|
Some products are not available anymore and I want to remove
|
|
the related documents from my products collection,
|
|
|
|
I can perform this operation by a call to 'remove'. Let's say I
|
|
do not have anymore "Programmers" in my catalog.
|
|
In the DB's I have three type* of programmers and I want to remove them all:
|
|
|
|
*Programmer1,Programmer2,Programmer3
|
|
*/
|
|
|
|
$collection->remove("name like 'Programmer%'")->execute(); //Note the '%'
|
|
|
|
/*
|
|
To remove the comments for some items before one
|
|
can make use again of the 'modify' operation, this time
|
|
unsetting the values.
|
|
|
|
The actual implementation of 'unset' expect as argument
|
|
an array of names, where each name refers to an attribute to remove.
|
|
(That's why the strange syntax ["comment"]).
|
|
*/
|
|
|
|
$collection->modify("comment like '%'")->unset(["comment"])->execute();
|
|
|
|
/*
|
|
The extension allows you to run SQL query from the script
|
|
with minimum effort, in order to do that a session object
|
|
is required.
|
|
|
|
Let's use SQL to create two tables for our new products:
|
|
*/
|
|
|
|
$session = mysql_xdevapi\getSession("localhost", "root", "");
|
|
$session->sql("create table products.new_products_table(name text,price float,description text)")->execute();
|
|
$session->sql("create table products.new_cheap_products_table(name text,price float,description text)")->execute();
|
|
|
|
/*
|
|
Is possible to extract an array containing all the tables
|
|
in the database.
|
|
|
|
The output of the following instructions is:
|
|
-> new_cheap_products_table
|
|
-> new_products_table
|
|
*/
|
|
|
|
$tables = $schema->getTables();
|
|
print "There are ".count($tables)." tables in the DB:".PHP_EOL;
|
|
foreach( $tables as $entry ) {
|
|
print " -> ".$entry->getName().PHP_EOL;
|
|
}
|
|
|
|
|
|
/*
|
|
And easily new elements can be inserted into the tables:
|
|
*/
|
|
|
|
$new_prod_tbl = $tables["new_products_table"];
|
|
$new_prod_tbl->insert(["name", "price", "description"])->values(["bPhone","605.15","The best bPhone from Banana Corporation"])->execute();
|
|
$new_prod_tbl->insert(["name", "price", "description"])->values(["bPhone ultraplus","933.1","Even bigger bPhone from Banana Corporation"])->execute();
|
|
$new_prod_tbl->insert(["name", "price", "description"])->values(["bPad","345.33","Completely useless gadget from Banana Corporation"])->execute();
|
|
|
|
|
|
/*
|
|
Then is possible to perform a selection the table in order
|
|
to retrieve data.
|
|
|
|
The output of the following select operation is:
|
|
-> bPhone ultraplus
|
|
-> bPhone
|
|
*/
|
|
|
|
$result = $new_prod_tbl->select(["name"])->where("price > 500")->orderBy("price desc")->execute();
|
|
foreach( $result->fetchAll() as $item ) {
|
|
print " -> ".$item["name"].PHP_EOL;
|
|
}
|
|
|
|
/*
|
|
Removing elements is straightforward, a call
|
|
to delete followed by the deleting criteria is
|
|
all that is needed.
|
|
|
|
After the following instruction all that remains
|
|
in the new_products_table is the bPad product.
|
|
*/
|
|
|
|
$new_prod_tbl->delete()->where("price > 500")->execute();
|
|
|
|
/*
|
|
Is possible to drop the schema with a call to dropSchema,
|
|
providing the name of the schema do drop.
|
|
*/
|
|
|
|
$session->dropSchema("products");
|
|
|
|
/*
|
|
All the operations which I've listed here are executed
|
|
(operation request transmitted to the server) after a call
|
|
to 'execute', all the chaining we're doing before execute
|
|
is not effecting the collection.
|
|
|
|
All the changes once executes are reflected on server side,
|
|
and is possible to query the DB's to make a verification
|
|
*/
|
|
?>
|