Develop PHP Applications with MySQL X DevAPI

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Session Title: Develop PHP Applications with MySQL X DevAPI • Conference: Oracle Code One • Session Type: Developer Session • Session ID: DEV5981 • Presenter: David Stokes Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Develop PHP Applications With The MySQL X DevAPI Dave Stokes MySQL Community Manager Oracle Corporation October 24, 2018 David.Stokes@Oracle.com @Stoker Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Who Am I Dave Stokes MySQL Community Manager @Stoker David.Stokes @ Oracle.com Slideshare.net/davidmstokes My new book -> Confidential – Oracle Internal/Restricted/Highly Restricted 4

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Agenda 1. X Devapi – What is it? – MySQL Without the SQL – MySQL Document Store 2. Installation of PECL Extension – Where to get mysql_xdevapi and other needed software – Build the PECL Extension 3. Coding – Examples, Examples, and a few more Examples Confidential – Oracle Internal/Restricted/Highly Restricted 6

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | No Code Changes Needed for Cluster Operations The code that is needed to connect to a MySQL document store looks a lot like the traditional MySQL connection code, but now applications can establish logical sessions to MySQL server instances running the X Plugin. Sessions are produced by the mysqlx factory, and the returned Sessions can encapsulate access to one or more MySQL server instances running X Plugin. Applications that use Session objects by default can be deployed on both single server setups and database clusters with no code changes. 7

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Session Creation Create a Session using the mysqlx.getSession(connection) method. You pass in the connection parameters to connect to the MySQL server, such as the hostname, user and so on, very much like the code in one of the classic APIs. The connection parameters can be specified as either a URI type string, for example user:@localhost:33060, or as a data dictionary, for example {user: myuser, password: mypassword, host: example.com, port: 33060} 8

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | X Devapi -> What is it? User Guide : https://dev.mysql.com/doc/x-devapi-userguide/en/ Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | X Devapi The X DevAPI wraps powerful concepts in a simple API. ● A new high-level session concept enables you to write code that can transparently scale from single MySQL Server to a multiple server environment. ● Read operations are simple and easy to understand. ● Non-blocking, asynchronous calls follow common host language patterns. Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | The X DevAPI introduces a new, modern and easy-to-learn way to work with your data. ● Documents are stored in Collections and have their dedicated CRUD operation set. ● Work with your existing domain objects or generate code based on structure definitions for strictly typed languages. ● Focus is put on working with data via CRUD operations. ● Modern practices and syntax styles are used to get away from traditional SQL-String-Building. Confidential – Oracle Internal/Restricted/Highly Restricted

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | The X DevAPI and Create Read, Update, and Delete (CRUD) operations. CRUD operations are available as methods, which operate on Schema objects. The available Schema objects consist of Collection objects, containing Documents, or Table objects consisting of rows and Collections containing Documents. Confidential – Oracle Internal/Restricted/Highly Restricted 15

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | The following table shows the available CRUD operations for both Collection and Table objects. 16 Operation Document Relational Create Collection.add() Table.insert() Read Collection.find() Table.select() Update Collection.modify() Table.update() Delete Collection.remove() Table.delete()

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Method Chaining The X DevAPI supports a number of modern practices to make working with CRUD operations easier and to fit naturally into modern development environments. This section explains how to use method chaining instead of working with SQL strings of JSON structures. 18

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Synchronous versus Asynchronous Execution Traditionally, many MySQL drivers used a synchronous approach when executing SQL statements. This meant that operations such as opening connections and executing queries were blocked until completion, which could take a long time. To allow for parallel execution, a developer had to write a multithreaded application. Any MySQL client that supports the X Protocol can provide asynchronous execution, either using callbacks, Promises, or by explicitly waiting on a specific result at the moment in time when it is actually needed. 19

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Working with Collections Working with collections of documents is straight forward when using the X DevAPI. The following examples show the basic usage of CRUD operations when working with documents. 20

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | An example installation procedure on Ubuntu 18.04 with PHP 7.2: // Dependencies $ apt install build-essential libprotobuf-dev libboost-dev openssl protobuf-compiler // PHP with the desired extensions; php7.2-dev is required to compile $ apt install php7.2-cli php7.2-dev php7.2-mysql php7.2-pdo php7.2-xml // Compile the extension $ pecl install mysql_xdevapi 26

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | The pecl install command does not enable PHP extensions (by default) and enabling PHP extensions can be done in several ways. Another PHP 7.2 on Ubuntu 18.04 example: // Create its own ini file $ echo "extension=mysql_xdevapi.so" > /etc/php/7.2/mods- available/mysql_xdevapi.ini // Use the 'phpenmod' command (note: it's Debian/Ubuntu specific) $ phpenmod -v 7.2 -s ALL mysql_xdevapi // A 'phpenmod' alternative is to manually symlink it // $ ln -s /etc/php/7.2/mods-available/mysql_xdevapi.ini /etc/php/7.2/cli/conf.d/20-mysql_xdevapi.ini 27

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | RPM users The only way currently is to load all the needed software and build PHP from scratch. And yes, that is going to be corrected. 29

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | X DevAPI -- Lots of similarities to mysqli and PDO 1. Connect to server a. Username, Password, Port, schema, encodings, etc. 2. Send Query 3. Get Results 4. Disconnect 31

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Mysqli - replaces old, deprecated mysql interface <?php $mysqli = new mysqli("example.com", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } if (!$mysqli->query("DROP TABLE IF EXISTS test") || !$mysqli->query("CREATE TABLE test(id INT)") || !$mysqli->query("INSERT INTO test(id) VALUES (1)")) { echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error; } ?> Confidential – Oracle Internal/Restricted/Highly Restricted 33

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | PDO Confidential – Oracle Internal/Restricted/Highly Restricted 34 <?php if ($db->getAttribute(PDO::ATTR_DRIVER_NAME) == 'mysql') { $stmt = $db->prepare('select * from foo', array(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true)); } else { die("my application only works with mysql; I should use $stmt->fetchAll() instead"); } ?>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | X Devapi Connection <?php $session = mysql_xdevapigetSession("mysqlx://user:password@host"); if ($session === NULL) { die("Connection could not be established"); } // ... use $session ?> 35

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Create Some Data $marco = [ "name" => "Marco", "age" => 19, "job" => "Programmer" ]; $mike = [ "name" => "Mike", "age" => 39, "job" => "Manager" ]; 38

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Store Data $schema = $session->getSchema("test"); $collection = $schema->getCollection("example"); $collection->add($marco, $mike)->execute(); var_dump($collection->find("name ='Mike'")->execute()->fetchOne()); 39

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Fetch Data $result = $collection->find()->execute()); foreach ($result as $doc) { echo "${doc["name"]} is a ${doc["job"]}.n"; } 40

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 42 #!/usr/bin/php <?PHP // Connection parameters $user = 'root'; $passwd = 'S3cret#'; $host = 'localhost'; $port = '33060'; $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; echo $connection_uri . "n"; // Connect as a Node Session $nodeSession = mysql_xdevapigetNodeSession($connection_uri); // "USE world_x" schema $schema = $nodeSession->getSchema("world_x"); // Specify collection to use $collection = $schema->getCollection("countryinfo"); // SELECT * FROM world_x WHERE _id = "USA" $result = $collection->find('_id = "USA"')->execute(); // Fetch/Display data $data = $result->fetchAll(); var_dump($data); ?>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 46 <?php $uri = "mysqlx://root:XXX@localhost:33060/?ssl-mode=disabled"; $nodeSession = mysql_xdevapigetSession( $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; ?>

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 47 $uri = "mysqlx://root:XXX@localhost:33060?ssl-mode=disabled"; $nodeSession = mysql_xdevapigetSession( $uri ); $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(); $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); }

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 48 //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"); //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!"); }

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Confidential – Oracle Internal/Restricted/Highly Restricted 49 //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();

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 51 <?php $session = mysql_xdevapigetSession("mysqlx://root:secret@localhost"); $session->sql("DROP DATABASE IF EXISTS addressbook")->execute(); $session->sql("CREATE DATABASE addressbook")->execute(); $session->sql("CREATE TABLE addressbook.names(name text, age int)")- >execute(); $session->sql("INSERT INTO addressbook.names values ('John', 42), ('Sam', 33)")->execute(); $schema = $session->getSchema("addressbook"); $table = $schema->getTable("names"); $row = $table->select('name', 'age')->execute()->fetchAll(); print_r($row);

Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 52 #!/bin/php <?php $session = mysql_xdevapigetSession("mysqlx://root:hidave@localhost:33060"); if ($session === NULL) { die("Connection could not be established"); } $schema = $session->getSchema("world"); $table = $schema->getTable("city"); $row = $table->select('Name','District') ->where('District like :district') ->bind(['district' => 'Texas']) ->execute()->fetchAll(); print_r($row);

Develop PHP Applications with MySQL X DevAPI

More Related Content

What's hot

Similar to Develop PHP Applications with MySQL X DevAPI

More from Dave Stokes

Recently uploaded

Develop PHP Applications with MySQL X DevAPI