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!

Monday, June 26, 2017

Indexing the MySQL Document Store

Indexing and the MySQL Document Store

The MySQL Document Store allows developers who do not know Structured Query Language (SQL) to use MySQL as a high efficient NoSQL document store. It has several great features but databases, NoSQL and SQL, have a problem searching through data efficiently. To help searching, you can add an index on certain fields to go directly to certain records. Traditional databases, like MySQL, allow you to add indexes and NoSQL databases, for example MongoDB, lets you add indexes. The MySQL Document Store also allows indexing.

So lets take a quick look at some simple data and then create an index.

mysql-js> db.foo.find()
[
    {
        "Name": "Carrie",
        "_id": "888881f14651e711940d0800276cdda9",
        "age": 21
    },
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    },
    {
        "Last": "Stokes",
        "Name": "Dave",
        "_id": "davestokes"
    }
]
3 documents in set (0.01 sec)

mysql-js> db.foo.createIndex("ageidx").field("age","INTEGER", false).execute()
Query OK (0.01 sec)

The _id field was already indexed by default and I chose the age key for a new index. By the way you can crate UNIQUE and NON UNIQUE indexes. The arguments for the createIndex function are as follows. The first is the key in the JSON data to index. Second comes the index data type and age is an integer. And the third specifies if NOT NULL is supported and setting it to false means the column can contain NULL. BTW note that the last record has no age key which would be noted as a null; so if some of your records do not have the key to be indexed you should have this set to false.

So What Happened?

So lets take a look at what happened behind the scenes, using SQL.
mysql> DESC foo;
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| Field                                          | Type        | Null | Key | Default | Extra             |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
| doc                                            | json        | YES  |     | NULL    |                   |
| _id                                            | varchar(32) | NO   | PRI | NULL    | STORED GENERATED  |
| $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C | int(11)     | YES  | MUL | NULL    | VIRTUAL GENERATED |
+------------------------------------------------+-------------+------+-----+---------+-------------------+
3 rows in set (0.00 sec)

A VIRTUAL generated column was created. You may recall that virtual generated columns are not created until referenced, hence do not take up the space of a stored generated column. So what if $ix_i_F177B50B40803DD7D3962E25071AC5CAA3D1139C is not as human friendly as ageidx.

So lets try a search.

mysql-js> db.foo.find("age = 24")
[
    {
        "Name": "Alex",
        "_id": "cc8a81f14651e711940d0800276cdda9",
        "age": 24
    }
]
1 document in set (0.00 sec)

Sadly there is no corresponding function to the EXPLAIN SQL command. Which means there is no nice and easy way to see how much the index gains us in terms of performance.

Drop the index

But what if you want to remove that new index? Well, it is as simple as creating the index in the first place.

mysql-js> db.foo.dropIndex("ageidx").execute()
Query OK (0.01 sec)

Monday, June 19, 2017

Update on MySQL Document Store and Node.JS

Opps!

I admit my JavaScript skills are rusty and dusty. Plus I am new to Node.JS. So yesterdays blog drew some very helpful comments from two of MySQL's best. Johannes Schl├╝ter and Rui Quelhas let me know there was a better way to code the example. Much thanks to them.

Better Example

// Simple example to grap one record and print it
const mysqlx = require('@mysql/xdevapi');
const options = {
  host: 'localhost',
  port: 33060, // should be a number
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
};

mysqlx
  .getSession(options)
  .then (session => {
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
     return Promise.all([
       coll.find(query).execute(function (doc) {
       console.log(doc);
      }),
      session.close()
     ]);
    })
    .catch(err => {
        console.log(err.message);
        console.log(err.stack);
    });

Modern Code

This cleans up the error catching and the Promise.all helps put all the heavy lifting together.

And the PHP XDevAPI is in PECL

And you can access the XDevAPI from PHP using the PECL Extension!

Sunday, June 18, 2017

MySQL Document Store and Node.JS

JavaScript and MySQL

Last time I looked at using the new MySQL Shell in JavaScript mode to access documents. I promised that I would look at indexes this time but a few folks wanted to see how to use the MySQL Document Store with Javascript -- the language not the MySQL Shell mode-- specifically with the Node.JS connector. And they ask for a very simple example. So Indexes in the future, Node.JS now.

So this is a simple on using the MySQL Document Store from Node.JS.

Install

Download the Node.JS connector and then follow the very simple installation instructions. And use your OS's instictions for installing Node.JS.

The documentation on using the Node.JS Connector with the XDevAPI is not written for folks just starting with either the API or Node.JS.

Example


const mysqlx = require('@mysql/xdevapi');

mysqlx.getSession({             // Create session
  host: 'localhost',
  port: '33060',
  dbUser: 'root',
  dbPassword: 'Hell0Dave!'
}).then(function (session) {    // USE world_x
  var schema = session.getSchema('world_x');

//equivilent of SELECT doc FROM countryinfo where _id = 'USA'
  var coll = schema.getCollection('countryinfo');
  var query = "$._id == 'USA'";

      // Print doc 
  coll.find(query).execute(function (doc) {
    console.log(doc);
  })
  .catch(function (err) {
    console.log(err.message);
    console.log(err.stack);
  });
  session.close();
}).catch(function (err) {
  console.log(err.message);
  console.log(err.stack);
});

Output


node countryinfo.js
{ GNP: 8510700,
  _id: 'USA',
  Name: 'United States',
  IndepYear: 1776,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9363520 },
  government: 
   { HeadOfState: 'George W. Bush',
     GovernmentForm: 'Federal Republic' },
  demographics: { Population: 278357000, LifeExpectancy: 77.0999984741211 } }

Wrap-up

So that is a simple of an example that I could think of. I plan to explore more of using the XDevAPI (and fingers crossed the PHP version appears soon) and let me know if there are anything you want to see. And I will get to indexes while I am in Glasgow next week.