Tuesday, July 18, 2017

Using find() with the MySQL Document Store

The Video

The find() function for the MySQL Document Store is a very powerful tool and I have just finished a handy introductory video. By the way -- please let me have feed back on the pace, the background music, the CGI special effects (kidding!), and the amount of the content.

The Script

For those who want to follow along with the videos, the core examples are below. The first step is to connect to a MySQL server to talk to the world_x schema (Instructions on loading that schema at the first link above).

\connect root@localhost/world_x

db is an object to points to the world_x schema. To find the records in the countryinfo collection, use db.countryinfo.find(). But that returns 237 JSON documents, too many! So lets cut it down to one record by qualifying that we only want the record where the _id is equal to USA, db.countryinfo.find(‘_id = “USA”’)

Deeper Level items in the Document

You can reach deeper level items by providing the path to the object such as db.countryinfo.find(‘geography.Continent = “North America”). Be sure to remember Case Sensitivity!!

Limits, Offsets, and Specifications

It is very simple to place restrictions on the amount of output by post pending .limit(5).skip(6). And you can specify which parameters meet you specification by using find(‘GNP > 8000000’)

Limiting Fields

But what if you do not want all the document but just a few certain fields. Then post pend .fields([“Name”, “GNP”]) to find().

And once again you can dig deeper into the document with specifying the path, such as.fields([“Name”, “GNP”, “geography.Continent”]).sort(“GNP”).

Sorting

Yes, you can easily sort the output from find() by adding .sort(“GNP”,”Name”) at the end.

More Complex Searches

Of course you can make the find() function perform more complex dives into the data such as db.countryinfo.find(‘GNP> 500000 and IndepYear > 1500”).

Parameter Passed Values

And find of parameter passed values will be happy to find they have not been forgotten. db.countryinfo.find(“Name = :country”).bind(“Country”,”Canada”)

Sunday, July 16, 2017

MySQL Document Store Video Series

I am starting a series of videos on the MySQL Document Store. The Document Store allows those who do not know Structured Query Language (SQL) to use a database without having to know the basics of relational databases, set theory, or data normalization. The goal is to have sort 2-3 minute episodes on the various facets of the Document Store including the basics, using various programming languages (Node.JS, PHP, Python), and materializing free form schemaless, NoSQL data into columns for use with SQL.

The first Episode, Introduction, can be found here.

Please provide feedback and let me know if there are subjects you would want covered in the near future.

Thursday, July 6, 2017

MySQL Document Store Concepts for PHP Developers

Docstore versus traditional MySQL

I have had quite a few questions from PHP Developers who are very interested in the new MySQL Document Store versus the traditional use of MySQL with PHP. You used to have to write queries in Structured Query Language (SQL) by outing them in strings withing your PHP code. In the past I have heard many folks complain about having to use a programming language within a programming language. And several of you have just skipped all than an started using an ORM. And roughly two percent of the developers at conferences tell me they have had any formal training in SQL, relational theory, or sets!

So what changes with Document Store?

The first and biggest changing is that you stop writing queries in SQL. SQL is surprisingly hard for many programmers. Part of this is that SQL is a declarative language. CSS is also a declarative language. These languages describe what the desired output looks like. Most other languages assemble the parts to get the desired output.

The second is that you your code stops looking like string handing routines. Compare $result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) of the traditional embedded SQL code embedded in the PHP code to $result = $collection->find('_id = "USA"')->execute() of the Document store.

TraditionalDocument Store

<?PHP
// Connection parameters
$host='127.0.0.1';
$user='root';
$pass='hidave';
$db  = 'world_x';

// connect to database server
$mysqli = mysqli_connect('localhost','root','hidave');

// Choose schema
$mysqli->select_db('world_x');


// send SQL query
if ($result = $mysqli->query("SELECT doc FROM countryinfo WHERE _id='USA'")) {
    $row = mysqli_fetch_row($result);
    var_dump($row);

    /* free result set */
    $result->close();
}


$mysqli->close();
?>

<?PHP
// Connection parameters
  $user = 'root';
  $passwd = 'hidave';
  $host = 'localhost';
  $port = '33060';

  $connection_uri = 'mysqlx://'.$user.':'.$passwd.'@'.$host.':'.$port; 
  

// Connect as a Node Session
  $nodeSession = mysql_xdevapi\getNodeSession($connection_uri);
// Choose schema
  $schema = $nodeSession->getSchema("world_x");
// Specify collection to use
  $collection = $schema->getCollection("countryinfo");
// Find desired record
  $result = $collection->find('_id = "USA"')->execute();
// Fetch/Display data
  $data = $result->fetchAll();
  var_dump($data);
?>

Choice

The good old mysqli extension is still a powerful way of getting data in and out of a MySQL server. But now you have another option that just may fit better with your programming paradigm.

Monday, July 3, 2017

How to Use PHP and MySQL Document Store

PHP Developers can now try the MySQL Document Store by using the MySQL X DevAPI for PHP PECL Extension. Developers in other languages have had access for a while but now PHP coders can get in on the action and use the MySQL Document Store.

What Does the Code Look Like?


#!/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_xdevapi\getNodeSession($connection_uri);
// "USE world_x"
  $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);
?>
Well, PHP code is PHP code. The big changes is that the developer no longer needs to use Structured Query Language to talk with the database. Now one connects to the schema of choice, sets the collection to use used, and then finds the record(s) of choice. Zero SQL involved.

PECL Extension

PECL is the PHP Community Library which houses all sorts of treasures. Now it also houses the MySQL XDevAPI extension. I have had the best luck with downloading the software and building it on my system following the directions in the README file. This is not an easy build but keep plugging and you will get it built.

Output


dstokes@davelaptop:~/phpxdev$ php 001.php
mysqlx://root:hidave@localhost:33060
array(1) {
  [0]=>
  array(7) {
    ["GNP"]=>
    int(8510700)
    ["_id"]=>
    string(3) "USA"
    ["Name"]=>
    string(13) "United States"
    ["IndepYear"]=>
    int(1776)
    ["geography"]=>
    array(3) {
      ["Region"]=>
      string(13) "North America"
      ["Continent"]=>
      string(13) "North America"
      ["SurfaceArea"]=>
      int(9363520)
    }
    ["government"]=>
    array(2) {
      ["HeadOfState"]=>
      string(14) "George W. Bush"
      ["GovernmentForm"]=>
      string(16) "Federal Republic"
    }
    ["demographics"]=>
    array(2) {
      ["Population"]=>
      int(278357000)
      ["LifeExpectancy"]=>
      float(77.099998474121)
    }
  }
}

Next Time

More PHP and MySQL Document Store code!