Wednesday, December 14, 2016

A Simple Python Example Program for the MySQL Document Store

Last time we looked at a simple example program using the X Devapi and Node.JS. This time lets look at the Python version. Well, not actually the same. This time instead of looking for the Canadian record, the program limits the query to the first two records found.

Besides Python 2.7, you will need to install the Google Protobuf code plus the development release of the Python Connector and a recent version of MySQL 5.7.

The Code


import mysqlx

session = mysqlx.get_session({          # Authenticate to server
  'host':       'localhost',
  'port':       33060,
  'user':       'dstokes',
  'password':   'S3cR3t%'})

# Connect to Schema 'world_x'
schema = session.get_schema('world_x');

# Set collection to 'countryinfo'
collection = schema.get_collection('countryinfo')

# Ask for two records
result = collection.find().limit(2).execute()

docs = result.fetch_all()

# Print requested records
for i, data in enumerate(docs):
   print "{iteration}: {data}".format(iteration = i, data=data)

# Clean up
session.close()

A Note About the 'Collection' versus 'Table'

Take a quick peek at the table used for the example from the world_x database (see last entry for location and details for installation). There are actually two '_id's in the data. There is one that is the generated column you see below and the other is in the JSON column named doc. The collection itself is the JSON data. How to prove? Change the line starting with result in the above example and replace it with result = collection.find('GNP > 100000').limit(2).execute() or something similar with a key from the data.
mysql> desc countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
2 rows in set (0.00 sec)

Results


$python test.py
0: {"GNP": 828, "Name": "Aruba", "government": {"GovernmentForm": "Nonmetropolitan Territory of The Netherlands", "HeadOfState": "Beatrix"}, "demographics": {"LifeExpectancy": 78.4000015258789, "Population": 103000}, "_id": "ABW", "IndepYear": null, "geography": {"SurfaceArea": 193, "Region": "Caribbean", "Continent": "North America"}}
1: {"GNP": 5976, "Name": "Afghanistan", "government": {"GovernmentForm": "Islamic Emirate", "HeadOfState": "Mohammad Omar"}, "demographics": {"LifeExpectancy": 45.900001525878906, "Population": 22720000}, "_id": "AFG", "IndepYear": 1919, "geography": {"SurfaceArea": 652090, "Region": "Southern and Central Asia", "Continent": "Asia"}}

Next Time

Next time we will build on these two simple example programs.

Monday, December 12, 2016

A Simple Node.JS Example Program for the MySQL Document Store

I have chatting with others who want to use the new X Devapi Document Store features but have not been able to find simple example programs. Finding the bridge between 'Hello World' and something useful can often be discouraging.

Example Code

Here is a very short Node.JS Docstore example program that accesses the 'countryinfo' collection. More on why Node.JS and what software you need below. But for now regard this code to get one specific record from the database.

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

mysqlx.getSession({             //Auth to server
        host: 'localhost',
        port: '33060',
        dbUser: 'root',
        dbPassword: 'password'
}).then(function (session) {    // use world_x.country.info
     var schema = session.getSchema('world_x');
     var collection = schema.getCollection('countryinfo');

collection                      // Get row for 'CAN'
  .find("$._id == 'CAN'")
  .limit(1)
  .execute(doc => console.log(doc))
  .then(() => console.log("\n\nAll done"));

  session.close();
})

Code Explained

On the first line the X Devapi library is loaded. Line three is authentication to the server followed by getting to the world_x/countryinfo schema. The we find the record for Canada. Is this simpler than "SELECT * FROM countryinfo WHERE _ID = 'CAN'"? Maybe, but I also see where this approach has some facets that need exploiting, er, exploring.

Results

So what does the code do?

:~/xdevn$ node demo.js
{ GNP: 598862,
  _id: 'CAN',
  Name: 'Canada',
  IndepYear: 1867,
  geography: 
   { Region: 'North America',
     Continent: 'North America',
     SurfaceArea: 9970610 },
  government: 
   { HeadOfState: 'Elisabeth II',
     GovernmentForm: 'Constitutional Monarchy, Federation' },
  demographics: { Population: 31147000, LifeExpectancy: 79.4000015258789 } }


All done

Why Node??

I like to try to expand my programming skills every year. MySQL recently released a new driver for the X Devapi Document Store for those who program in Node.JS. JavaScript has become the 'and' in the way many developers describe their jobs. They program in their favorite language 'and' in JavaScript to get their work done. PHP, Python, Rails and the like all have their coders also writing JavaScript. I have not done anything series with JavaScript since they first started trying to call it ECMAscript. At the recent ConFoo I started asking about learning modern JavaScript (and variants) and was pointed at Node.JS. Those of you looking to add Node.JS to your skill set can benefit from the books from Leanpub.com and their Node books (Inexpensive, easy to read, and wonderfully detailed).

The new Connector/Node.JS

First download the Node.JS connector for the MySQL Document Store. Also grab a copy of the world_x database, an update of the World database MySQL has been using for a long time in examples. Follow the directions for installing both of these packages. You will also need to load Google's Protobuffers.

The tutorial is very good but I needed a small stepping stone.Hopefully I can build on this and add the analogs in Python too.

Monday, November 28, 2016

2017 Database Administrator Holiday Gift Guide

TO DBAs:Database Administrators and other computer type folks are very hard to buy presents for at this time of year. Please help your family and friends out out by printing this off and notating which of the following items your would find acceptable.

TO The Friends and Family of a DBA: Yes, they are a pain in the pa-toot to buy a holiday gift for but hopefully this lest will make things a little easier. There are a lot of t-shirt on this list and this is a change to get that one threadbare relic from a long ago computer conference sent to the Valhalla for mangy cotton blend fabrics. Good luck and hopefully you will see something below that will keep you from just handing over a gift card. o

Amazon

Need to work on your laptop in bed or a variety of angles? Then this Furinno X7-BK Ergonomics Aluminum Adjustable Cooling Fan Laptop Tray Table is what you want, available in many colors.

Zazzle

Zazzle.com has a nice collection of DBA centric gifts.
And in female styles also.

Think Geek

Infectious Disease Ball

Cafe Press

Cafe Press has a line of t-shirts, also in m/f sizes and styles
Mugs too!

TeeSpring

Teespring has some interesting shirts

Bonanza

Bonanza.com also has shirts

Uncommongood

Yes, we al have too many phone chargers. Here is uncommongoods.com way to help identify them uniquely.
Other suggestions: Passes for movie theaters (new Star Wars movie on the way), Amazon gift cards, or Bacon of the Month Club membership.

Friday, November 11, 2016

Testing MySQL 8

MySQL 8

MySQL 8 is now available for testing. The list of features is impressive and I am sure many PHP developers will be interested in having a true data dictionary, invisible indexes, and more. Right now it is in a milestone release. I am still doing a lot of work with 5.7 and found a great way to have 8 around when I needed by using Docker. Follow the Docker install guide for your platform of choice and then run
docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=hidave -d mysql:8.0

Checking to See if it Really Works

 docker ps -a
CONTAINER ID        IMAGE               COMMAND                  CREATED              STATUS              PORTS               NAMES
ae1824fc01b5        mysql:8.0           "docker-entrypoint.sh"   About a minute ago   Up About a minute   3306/tcp            mysql8
Note that our instance of MySQL 8 has the container id of ae1824fc01b5. And odds are your container id will be anything but ae1824fc01b5.

Connect to the Container

 docker exec -it ae1824fc01b5 bash
Start up the MySQL command line interface.

# mysql -u root -p       
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.0-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 
MySQL 8 is running. There is no data loaded. To be able to do that from the host box, we will need to talk to the MySQL Container. And to do that we need the IP Address.
ip a show eth0                                                                                                       
6: eth0@if7:  mtu 1500 qdisc noqueue state UP group default 
    link/ether 02:42:ac:11:00:02 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.2/16 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:acff:fe11:2/64 scope link 
       valid_lft forever preferred_lft forever
Exit off the container. Now you can load your data and I used the world_x test database mysql -u root -p -h 172.17.0.2 < world_x.sql

Test!


#!/usr/bin/php
connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

$res = $mysqli->query("SELECT Name FROM city ORDER BY Name limit 7");

echo "City names...\n";
for ($row_no = $res->num_rows - 1; $row_no >= 0; $row_no--) {
    $res->data_seek($row_no);
    $row = $res->fetch_assoc();
    echo " Name = " . $row['Name'] . "\n";
}

Now for the first data out of MySQL 8 in the container
shell>php test.php
City names...
 Name = Abakan
 Name = Abaetetuba
 Name = Abadan
 Name = Aba
 Name = Aalborg
 Name = Aachen
 Name = A Coru�a (La Coru�a)
So the test environment is set up. Onto the tests of MySQL 8.

Wednesday, October 12, 2016

NomadPHP and MySQL JSON Lightening Talk

I will be presenting a short lightening talk on MySQL's JSON Data Type for NomandPHP on Thursday the 13th of October
. Chuck Reeves will be doing the main presentation on How the 3rd Normal Form Destroyed a Family. Get your tickets here.

Friday, August 12, 2016

Moving data from MongoDB to MySQL's JSON Document Store

I had an interesting phone call from someone wanting to move from MongoDB to MySQL's new JSON Document Store. The big question was 'How do I export my Mongo data into something I can read into MySQL?"

The good news is that the folks at Mongo have given us the tools for the hard part of a simple process. For this example I am going to use some test data create previously in a database creatively named dave. The numbers are made up and I am using my name and the names of the canines residing in my home. So a quick peek at the data:


$ mongo
MongoDB shell version: 3.2.8
connecting to: test
> use dave
switched to db dave
> db.dave.find()
{ "_id" : 123, "name" : "Dave" }
{ "_id" : 456, "name" : "Jack" }
{ "_id" : 789, "name" : "Dexter" }
{ "_id" : 787, "name" : "Boo" }
> 
bye

Dumping That Data

First use mongodump -d dave to write out the data much as you would with mysqldump. Under you current working directory of your shell (assuming you are on Unix/Linux) there will be created a directory named dump. And under dump is a directory named after the example database dave.

A dave.bson file was created with the data.

BSON to Text

Executing bsondump dave.bson > output.file will convert the MongoDB BSON formatted data into something much easier to work with.

$ cat output.file 
{"_id":123.0,"name":"Dave"}
{"_id":456.0,"name":"Jack"}
{"_id":789.0,"name":"Dexter"}
{"_id":787.0,"name":"Boo"}

The output.file can be processed in a number of ways to pull it into MySQL such as using your favorite text editor to wrap insert statements around the data or using a simple program that reads a line from the text file and then send data to the database.

Thursday, August 4, 2016

MySQL Document Store -- The NoSQL Zipcodes

The MySQL Document Store functionality allows developers to use a relation database with or without SQL (structured Query Language), also known as NoSQL. The example in this blog is hopefully a simple look at this new feature of MySQL. The example data used is from JSONStudio.com and is a JSON formatted data set for US zip (postal) codes (656K compressed). So download your copy of this data set and lets get to work.

Create a collection

Collections are tables and below we create a collection name 'zip' in the test database in the Python dialect.

mysqlsh -u root -p --py test
Creating an X Session to root@localhost:33060/test
Enter password:
Default schema `test` accessible through db.

Welcome to MySQL Shell 1.0.4 Development Preview

Copyright (c) 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type '\help', '\h' or '\?' for help.

Currently in Python mode. Use \sql to switch to SQL mode and execute queries.
mysql-py> db.createCollection("zip")


Is it there?

As soon as most of use create a table we want to see if it is there.

mysql-py> db.getCollections();
[
    <Collection:zip>
]
mysql-py> 
So it is there. But what is the underlying structure of this table. Switch to SQL dialect (or open a mysql client.

mysql> SHOW CREATE TABLE zip;
+-------+--------------------------------+
| Table | Create Table                                                                                                                                                                                                       |
+-------+--------------------------------+
| zip   | CREATE TABLE `zip` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'))) STORED NOT NULL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------+
1 row in set (0.00 sec)

mysql> 
If you peeked at the zip code file you downloaded, you may have noticed that it has an _id field already. But what if your data set has no _id or you want to use another key/value pair from the data as an index? Simply use a stored generated column on the field of your choice. Remember good indexing practices still count as the underlying relational database still has to keep the infrastructure underneath up to date.

Loading data

I will skip over the loading of the zip code data (I can address that in a later blog post if there is any interest. For now lets take it as a given that the data has been moved into the new collection.

Finding a Rainbow

So lets look for a particular zip code. For out data set the zip code corresponds with _id field.And remember that this column is a generated column using that field from the JSON document.

mysql-py> db.zip.find("_id = '76077'")
[
    {
        "_id": "76077",
        "city": "RAINBOW",
        "loc": [
            -97.70652,
            32.281216
        ],
        "pop": 722,
        "state": "TX"
    }
]
1 document in set (0.00 sec)

mysql-py> 

How About Searching a Non-indexed JSON data

Lets look for the state of Texas, or TX in the JSON data. Previous we had the _ID field as a materialized column extracted from the JSON data. Now we are asking the MySQL server to read all the records and return the ones meeting the criteria. This does perform a full table scale of the data (not as efficient as as index) but, thanks to the relatively small amount of records, it does return fairly quickly.

mysql-py> db.zip.find("state = 'TX'")
.
.  (Omitted)

.  
{
        "_id": "79935",
        "city": "EL PASO",
        "loc": [
            -106.330258,
            31.771847
        ],
        "pop": 20465,
        "state": "TX"
    },
    {
        "_id": "79936",
        "city": "EL PASO",
        "loc": [
            -106.30159,
            31.767655
        ],
        "pop": 52031,
        "state": "TX"
    }
]
1676 documents in set (0.06 sec)

mysql-py> 

Wrap Up

So now we can create a collection and search it. But what happens when we add records and especially records without our index-able key? That will be covered in another blog soon.

Thursday, July 28, 2016

Is the new MySQL Document Store and JSON data type cheating?

Is it cheating? Is using MySQL without Structured Query Language (SQL) or putting all your data into one column proper? Impossible a year ago and probably thought as a poor/crazy practice until recently, this is a new type of MySQL usage. NoSQL has had a big impact in the SQL world with several relational products from vendors like MySQL, Microsoft, Postgresql and others offering NoSQL interfaces and JSON data types to their databases.

Several old timers have come to me asking if putting lots of data in a JSON column is cheating? After all data normalization is part of relational databases and the way to efficiency and speed is well organized data. This higgledy–piggledy fashion of putting an entire document in a column without breaking it down to its component sections does violate the first rule of data normalization. And that has worked for decades pretty well.

But things change.

Many new development projects start with no idea what their data is going to look like. Maybe, if they are lucky, they will pick an API for interchanging data. Some will decide to use a database as a persistent message queue. Sometimes the service is product and the products offered by that service are nebulous at beast at the onset so the developers do not want to be locked into a schema.

And schema changes get expensive quickly. Code written before the change needs to be updated and you have to get the DBA to run an ALTER TABLE that can take too long to complete. And rolling back is almost impossible.

Compounding this is the lack of database skills in new developers. Very few have any training in Structured Query Language, relational theory, sets, data normalization, or other skills that have been held in high esteem since the days of Codd. And these new developers argue that they do not need these skills as technology and approaches to coding have evolved. So if the foundation of relational databases is the weak link then remove it.

The JSON data type has been very popular with developers. In the past you could dump JSON documents in a CHAR column and used REGEX or have your application dig out the parts you needed from within the document. Now with MySQL 5.7 there is a native JSON data type with supporting functions so that developers can manipulate the document data easily. So storing data in a JSON format in a column of a table within a database becomes a valuable has becomes a useful part of a developers life.

Ruby on Rails was the first piece of software that allowed many developers to see the power of CRUD (Create Read Update Delete) access to a database without needing to know SQL. The MySQL Document Store features that arrived with 5.7.13 also provide CRUD so developers can use 'collections' for schema-less data storage. The developers do not need to know SQL. relational theory, or any of the other old stuff. The good news for the old timers is that the data may be stored in a schema-less fashion but data is stored in a good ol' MySQL table. Which means the decades spent gathering SQL skills still work on this new data.

But is it cheating? Yes, and no. Yes in that you would get better performance out of having all your data in fourth or fifth normalized form just like a good driver can get better performance out of good driving practices with a manual transmission. No in that the data is still in a relational database and pertinent information can still be pried out of the JSON data. Heck you can even make materialized columns from the JSON data to create indexes.

Wednesday, July 6, 2016

How to make sure that 'password' is not a valid MySQL password

Password management is an issue for many. It is not uncommon for an organization to require you to changing your password on a regular basis and many have rules on the length and content of passwords. The length and complexity (that require certain amounts of upper & lower case characters, special characters, and numerals) rules try to prevent users from using '12345', 'qwerty', or even the word 'password' itself as passwords. With MySQL 5.7 you can filter out those bad passwords, obscenities, slurs, or other words you do not want used as or part of a password.

If the STRONG password policy is set in the my.cnf file, a validate_password_dictionary_file can be specified and you can use your favorite text editor to add the words or phrases you wish to not be valid in that file. Note that words shorter than four characters are ignored. So create a file with a text editor ( mine was placed in /var/lib/mysql/dit) with the banned words, edit the my.cnf file to set the validate_password_policy to STRONG, and also add the path to your dictionary file to validate_password_dictionary_file line of the same file. Restart your server and test.

My test dictionary had words like 'foobar', 'snafu', and 'password' and trying to use password with one of the words in the dictionary file would generate a ERROR 1819 (HY000): Your password does not satisfy the current policy requirement, even if I mixed the case of the various letters in the restricted words.

MySQL 5.7 also added the ability to set lifetimes for password in age, the ability to lock accounts, and stopped adding anonymous accounts (no user name and no password) during installation.

By the way I will be speaking in Detroit at the Converge Conference on MySQL 5.7 security if you would like to know more about this and other MySQL 5.7 related information.

Wednesday, June 29, 2016

MySQL Password Security Changes for PHP Developers

MySQL 5.7 introduced many new facets to password security. The first thing most notice is that you are assigned a random root password at installation time. You then have to search the log file for this random password, use it to login, and then change it. For the examples on the post I am using a fresh install of 5.7.13 on Oracle Linux 7.1 and was provided with the easy to remember password of nLvQRk7wq-NY which to me looked like I forgot to hit escape when trying to get out of vim. A quick ALTER USER to change the password and you are on your way.

Defaults

Password Lifetime and Complexity

5.7.13 now has the default password lifetime set to 0 or 'never expire'. My fresh install shows that the value of mysql.user.password_lifetime is set to NULL which means use the server default value. The lifetime is measured in days and stored in the password_last_changed column of the nysql.users table. If the password is expired, you are put into sandbox mode where the only command you can execute is to change the password. That works great for interactive users. But what about your application? It uses a username password pair to talk to the database but it is very unlikely that anyone planned on changing passwords upon expiration. I seriously doubt anyone has set up the exception routine to handle an expired password properly. And if so, how do you notify all involved about this new password --- securely.

What to do

The best thing would be to set the default password lifetime for accounts used by applications to zero. It simply does not expire. QED & out.

But what if your company wants ALL password changed on a regular basis? And they do mean ALL. Earlier there was a listing of the defaults. The test system are set to a password length of eight characters minimum, requires mixed case, requires at least one upper case letter, one special (nonalphanumeric) character, and is of MEDIUM complexity.

MEDIUM complexity means that passwords need one numeric, one lower case, one upper case, and one special character. LOW tests the password length only. And STRONG adds a condition that sub strings of the length of four characters or long do not match entries in a specified password file (use to make sure swear words, common names, etcetera are not part of a password).

Lets create a dummy account.

CREATE USER 'foobar'@'Localhost' IDENTIFIED BY 'Foo@Localhost1' PASSWORD EXPIRE;

Checking the entry in the user table, you will find that the account's password is expired. For extra credit notice what the authentication string is set to. We can't have just a password string as some authentication tokens or hashes are not really password.

So login as foobar and you will get a notice that the password must be reset before we can do anything else.

ALTER USER 'foobar'@'localhost' IDENTIFIED By '1NewP@assword';

Corporate Standard

Your corporate rules may require you to rotate password every N days and set the corresponding complexity. With MySQL 5.7 you can follow what their model is. If you do not have a standard and want to create one, be sure to DOCUMENT well what your standard is and make sure that standard is well known.

There are ways to use packages like PAM or LDAP for authentication but that is for another day.

Friday, May 27, 2016

Generated MySQL Columns and Changing Values

I was speaking at PHP[Tek] this week on the JSON Data Type and using generated columns. JSON columns can not be indexed but data from a JSON column can be extracted via a generated column and that column can be indexed. All was going well until someone asked me about modifying data in a generated column. Was it possible?

I blinked hard. I have not tried that! I had not seen any mention of that in the documentation. So I had to admit that I did not know and would have to try that.

The Test


mysql> CREATE TABLE gentest (a INT, b INT AS (a + 1) STORED, INDEX(b));
...
mysql> INSERT INTO gentest VALUES (1),(2),(3),(4);
...
So now we have a table with data to test. So lets try to modify the value of one of the generated columns.

mysql> UPDATE gentest SET b = 9 WHERE a = 1;

And what did the server do?

It returned an error and told me The value specified for generated column 'b' in table 'gentest' is not allowed.

Lesson Learned

So now I know that the server will not allow you to mangle, er, change the value of generated columns away from their definition. And yes, I find the same thing with both the VIRTUAL and STORED versions of generated columns.

Friday, May 13, 2016

Oracle Linux 7, MySQL 5.7.12 and YUM for JSON Document Store Part 1

Okay, I will admit that I grew up professionally without package management. Well, we did have SSCS and RCS under Unix and a lot of very brilliant shells scripts but nothing modern programmers would deign to touch. Then came Linux Torvalds (and why hasn't he been given a Nobel prize?!?!) and I blundered into the Redhat world with their RPM package manager. RPMS are great but over time I was seduced by Ubuntu and became and became an apt-get fiend.

Now I need to do a demo of the new MySQL 5.7.12 Document store features on an RPM based distro, Oracle 7.1. I could have used a Vagrant or Docker image but ended up using VirtualBox. You can even use a fresh install on hardware even though that seems to be becoming passe in a virtual & containerized world. So if you are following along please adjust to your platform.

After installing the latest and greatest Virtualbox from VirtualBox.org, I pulled the ISO image from Oracle. Then I set up a virtual machine using that ISO. And now ends the hard part.

The next step is to get the MySQL YUM repository configured on the system. And this is done by downloading a RPM with the needed info inside. The instructions show you how to use the rpm program to load this download. Then fire up your text editor of choice to pry open /etc/yum.repos.d/mysql-community.repo file. Make sure the enabled lines under the [mysql57-community] is set to 1 and that the other [mysql5X-community] have enabled lines set to zero. We can only use the new Docstore feature start with 5.7.12, so the earlier versions will not do.

Now set the enabled line to 1 under [mysql-tools-preview]. This loads the docstore software.

Now sudo yum install mysql-community-server mysql-shell to get all the software loaded. Then sudo service mysqld start will bring up the server. Please note the secure root password is in your log file and you will have to look in there in order to login the first time with a quick grep 'temporary password' /var/log/mysqld.log. Then login to the server and change the ugly generated password with your new password ALTER USER 'root'@'localhost' IDENTIFIED BY 'LessUglySecret1!';

So the server is set up as a regular relational server but we need to get the document store features setup. Simply type mysqlsh -u root -p --classic --dba enableXProtocol. Then it is a simple mysqlsh -u root -p --sql to have a SQL session on the server.

Next time: More document store explorations

Monday, April 25, 2016

What happens when you create a MySQL Document Store

The MySQL Document Store introduced with version 5.7.12 allows developers to create document collections without have to know Structured Query Language. The new feature also comes with a new set of terminology. So let us create a collection and see what it in it (basically creating a table for us SQL speakin' old timers).

So start the mysqlsh program, connect to the server, change to the world-x schema (database) switch to Python mode, a create a collection (table).

What did the server do for us? Switching to SQL mode, we can use describe to see what the server has done for us.

We have a two column table. The first is named doc and is used to store JSON. And there is also a column named _id and please notice this column is notated as STORED GENERATED.

The generated column extracts values from a JSON document and materializes that information into a new column that then can be indexed. But what did the system extract for us to create this new column?

Lets use SHOW CREATE TABLE to find out.

mysql-sql> SHOW CREATE TABLE foobar;
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| Table  | Create Table

                                                                      |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
| foobar | CREATE TABLE `foobar` (
  `doc` json DEFAULT NULL,
  `_id` varchar(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,'$._id'
))) STORED NOT NULL,
  UNIQUE KEY `_id` (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql-sql>
So the 5.7.12 document store is creating an index for us on a field named _id in our JSON document. Hmm, what if I do not have an _id field in my data. So I added two records ("Name" : "Dave" and "Name" : "Jack") into my new collection and then took a peek.
mysql> select * from foobar;
+-------------------------------------------------------------+-----------------
-----------------+
| doc                                                         | _id
                 |
+-------------------------------------------------------------+-----------------
-----------------+
| {"_id": "819a19383d9fd111901100059a3c7a00", "Name": "Dave"} | 819a19383d9fd111
901100059a3c7a00 |
| {"_id": "d639274c3d9fd111901100059a3c7a00", "Name": "Jack"} | d639274c3d9fd111
901100059a3c7a00 |
+-------------------------------------------------------------+-----------------
-----------------+
2 rows in set (0.00 sec)

mysql>

But what if i do have a _id of my own?

The system picked up the _id for the Dexter record. Remember that the index on the _id field is marked UNIQUE which means you can not reuse that number.

So we know the document store wants is creating an unique identification number (that we can also use).

Update: The client generates the identification number, the server can not due to possible conflicts in future sharding projects.

Friday, April 22, 2016

The new MySQL X DevAPI Protocol and what it means for PHP

The relational database world just changed and you didn't notice, did you? Rather than speaking SQL to your data, what if you could talk to it in PHP directly? No longer do you have to struggle to remember arcane structured query language syntax and just grab data with PHP. You can still write bad queries full of N+1 errors and a whole host of other problems. But you are speaking PHP to your data. And this started with MySQL 5.7.12 and its new Document Store Functionality.

The Document Store functionality expands on the new JSON data type in MySQL 5.7 with a new server plugin, a new API, and a suite of new components designed to make MySQL accessible for users who are not familiar with the SQL language or prefer to use a schemaless data store. This MySQL Server plugin enables communication using the X Protocol. And clients that implement X DevAPI and enables using MySQL as a document store easily. So you can talk to your data in the language of your choice (say Python, Javascript, or SQL) via a new shell or use a new connector to use these features. For those in the PHP world I must warn you that the new connector to support the new X DevAPI is on the way -- not here yet but on the way.

Trying the XDevAPI

You will need MySQL 5.7.12 with the X plugin enabled and the new mysqlsh. Details on the installation of the shell and plugin can be found under Using MySQL as a Document Store in the MySQL 5.7 Reference Manual. Fire up the shell and you will see something like this:

That mysql-js> prompt lets you know that your shell is currently in Javascript more. Currently the shell has modes for Javascript, Python, or SQL. To connect to a database simply type

db = session.getSchema('world_x')
It is then easy to use Javascript to create, remove, update or delete data.

The X Plugin extends MySQL Server to be able to function as a document store and the X Protocol, which is designed to expose the ACID compliant storage abilities of MySQL as a document store. Documents are stored in JSON format and enable schema-less storage. Using the X DevAPI you can use a NoSQL-like syntax to execute Create, Read, Update, Delete (CRUD) operations against these documents. The server will be listening to port 33060 (configurable) for communications in the new protocol.

And any data you drop into the document store is avail from the document store in JSON format AND is accessible at the same time from SQL. And by using the new connectors (Java, .NET, Node.JS for now) that support the X protocol, your code hits the database without have to fidget and grumble about using SQL. You are talking in your language to a relational database using NoSQL.

And you data you add via the X protocol in Node.JS can be used by someone with Python, or Java, or SQL at the same time.

Wednesday, March 23, 2016

Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev -- Follow Up

Last time this blog covered digging into a JSON document in a MySQL 5.7 table. The goal was to pull certain records matching a particular criteria. Both Peter Zaitsev and Morgan Tocker get my thanks for their kind comments. My example was a little contrived in that an application would be used to fine tune seeking for a particular key value pair. I was trying to pull single records which is kind of silly when it is much easier to use PHP to parse the data. What follows below is a sample PHP script to grab out the matching records and then feed the results, the JSON document, into an array.
#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$foo = array();
$query0 =
"SELECT* FROM restaurant WHERE json_contains(data, '{\"grade\": \"A\"}', '$.grades')";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n\n", $row[0]);
    $foo =  json_decode($row[0]);
        var_dump($foo);
} else {
        printf("Errormessage: %s\n", $mysqli->error);

}


$mysqli->close();
In this case we get the data into an array and the the processing is limited to a var_dump().

Thursday, March 17, 2016

Digging Down into JSON data with the MySQL Functions -- A Question from Peter Zaitsev

How do you dig down into the JSON data, say like in comments on a blog post? This is a badly paraphrased question from Peter Zaitsev of Percona at the end of a recent presentation of mine at the Great Wide Open conference. I have to admit I had not done like that with JSON data but it had to be able to be done, right? Surely it can be done. Can't it? Time to dig. I could not find an example of JSON data on a blog with comments. But the good folks at Mongo had an example of a restaurant with grades.
{
  "address": {
     "building": "1007",
     "coord": [ -73.856077, 40.848447 ],
     "street": "Morris Park Ave",
     "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
     { "date": { "$date": 1393804800000 }, "grade": "A", "score": 2 },
     { "date": { "$date": 1378857600000 }, "grade": "A", "score": 6 },
     { "date": { "$date": 1358985600000 }, "grade": "A", "score": 10 },
     { "date": { "$date": 1322006400000 }, "grade": "A", "score": 9 },
     { "date": { "$date": 1299715200000 }, "grade": "B", "score": 14 }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
}
So how do we get the grade information and just the grades (A,A,A,A, and B) from grades?

I created a quick little table named restaurant with a single column named data for this test and then added the above document to the table. Yeah, real creative on the table and columns names there. Let's look at the fields, or keys.

mysql> select json_keys(data) from restaurant;
+----------------------------------------------------------------------+
| json_keys(data)                                                      |
+----------------------------------------------------------------------+
| ["name", "grades", "address", "borough", "cuisine", "restaurant_id"] |
+----------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>
So can we see the path to the grades key? Yes, but not the date, grade, or score keys from under the grades tag. Note that in the case the 'one' or 'all' second argument returns the same result.
mysql> select json_contains_path(data,'one','$.grades') from restaurant;
+-------------------------------------------+
| json_contains_path(data,'one','$.grades') |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)
Grab the grades.
mysql> select json_extract(data,'$.grades') from restaurant\G
*************************** 1. row ***************************
json_extract(data,'$.grades'): [{"date": {"$date": 1393804800000}, "grade": "A",
 "score": 2}, {"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, {"da
te": {"$date": 1358985600000}, "grade": "A", "score": 10}, {"date": {"$date": 13
22006400000}, "grade": "A", "score": 9}, {"date": {"$date": 1299715200000}, "gra
de": "B", "score": 14}]
1 row in set (0.00 sec)

This is all the info from the grades tag. Now to whittle this down to just the individual grade entries. We can get the individual grades as $.grades.[N] such as:
mysql> select json_extract(data, '$.grades[1]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[1]'): {"date": {"$date": 1378857600000}, "grade": "
A", "score": 6}
1 row in set (0.00 sec)

mysql> select json_extract(data, '$.grades[4]') from restaurant\G
*************************** 1. row ***************************
json_extract(data, '$.grades[4]'): {"date": {"$date": 1299715200000}, "grade": "
B", "score": 14}
1 row in set (0.00 sec)

Much closer!

And then the individual grade from the 4th entry:

mysql> select json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') from
restaurant;
+--------------------------------------------------------------+
| json_extract(json_extract(data, '$.grades[4][0]'),'$.grade') |
+--------------------------------------------------------------+
| "B"                                                          |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
I need to go read up on JSON pathing and experiment so I can grab all the grades at once but right now I fell like I am halfway done with the 'homework' Peter assigned.

Tuesday, March 15, 2016

Modifying JSON Values in MySQL 5.7

The JSON Functions for using MySQL 5.7 are overwhelming at first glance. A peek at documentation such as the JSON Modification Functions may make you shy at proceeding further. In previous posts the functions to create JSON documents for use in a MySQL column and the functions to get the meta information about JSON documents have been covered. And how to use the built-in JSON functions from PHP have been covered. But now it is time to cover modifying JSON data.

Appending

Lets start at the beginning and ask the server for a JSON array with the values of 1, 2, 3, 4, and 5.
mysql> select JSON_ARRAY(1,2,3,4,5);
+-----------------------+
| JSON_ARRAY(1,2,3,4,5) |
+-----------------------+
| [1, 2, 3, 4, 5]       |
+-----------------------+
1 row in set (0.00 sec)

mysql> 

Using the $ operator to represent our array, we can append an array with the numbers 6, 7, and 8 in it to our array.
mysql> SELECT JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8));
+----------------------------------------------------------------+
| JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$',JSON_ARRAY(6,7,8)) |
+----------------------------------------------------------------+
| [1, 2, 3, 4, 5, [6, 7, 8]]                                     |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
Or change values.
mysql> select JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99);
+----------------------------------------------------+
| JSON_ARRAY_APPEND(JSON_ARRAY(1,2,3,4,5),'$[2]',99) |
+----------------------------------------------------+
| [1, 2, [3, 99], 4, 5]                              |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> 


Inserting an Array

But what if we need an array inside an array? We can modify arrays by inserting new arrays in them where we want. In the first example we add 9 & 10 at the beginning of an array, $[0], and the second the new array in the third position, $[2]. Remember array counting starts at zero!
mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10));
+------------------------------------------------------------------+
| JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[0]',JSON_ARRAY(9,10)) |
+------------------------------------------------------------------+
| [[9, 10], 1, 2, 3, 4, 5]                                         |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10));
+------------------------------------------------------------------+
| JSON_ARRAY_INSERT(JSON_ARRAY(1,2,3,4,5),'$[2]',JSON_ARRAY(9,10)) |
+------------------------------------------------------------------+
| [1, 2, [9, 10], 3, 4, 5]                                         |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Merging Arrays

We can also put two arrays together with JSON_MERGE.
mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9));
+-------------------------------------------------------+
| JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_ARRAY(6,7,8,9)) |
+-------------------------------------------------------+
| [1, 2, 3, 4, 5, 6, 7, 8, 9]                           |
+-------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
Or Combine arrays with objects.
mysql> SELECT JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave'));
+--------------------------------------------------------------+
| JSON_MERGE(JSON_ARRAY(1,2,3,4,5),JSON_OBJECT('name','Dave')) |
+--------------------------------------------------------------+
| [1, 2, 3, 4, 5, {"name": "Dave"}]                            |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Removing/Replacing JSON

Sometimes you need to remove or replace items as well as add them. Here we remove the first item, $[0].
mysql> SELECT JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]');
+-------------------------------------------+
| JSON_REMOVE(JSON_ARRAY(1,2,3,4,5),'$[0]') |
+-------------------------------------------+
| [2, 3, 4, 5]                              |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> 
The JSON_REPLACE function has an argument for the new value. Here we change the first item in the array from 1 to 999.
mysql> SELECT JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999);
+------------------------------------------------+
| JSON_REPLACE(JSON_ARRAY(1,2,3,4,5),'$[0]',999) |
+------------------------------------------------+
| [999, 2, 3, 4, 5]                              |
+------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

Game, SET , and Match

The JSON_SET function can insert OR update values. Previously exiting items are updated, so we could use JSON_SET like JSON_REPLACE.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[0]',999) |
+--------------------------------------------+
| [999, 2, 3, 4, 5]                          |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[2]',999) |
+--------------------------------------------+
| [1, 2, 999, 4, 5]                          |
+--------------------------------------------+
1 row in set (0.00 sec)

Or use it like JSON_ARRAY_APPEND. Here using the not existing index of $[7] we append the value 999 to the array.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999)\g
+--------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999) |
+--------------------------------------------+
| [1, 2, 3, 4, 5, 999]                       |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> 
JSON_SET, JSON_INSERT, and JSON_REPLACE are very closely related. And all allow you to do lots of work with one call. So lets change the values for the first and third values in the array and add a new value at the end.
mysql> SELECT JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777)\g 
+------------------------------------------------------------------+
| JSON_SET(JSON_ARRAY(1,2,3,4,5),'$[7]',999,'$[0]',888,'$[2]',777) |
+------------------------------------------------------------------+
| [888, 2, 777, 4, 5, 999]                                         |
+------------------------------------------------------------------+
1 row in set (0.00 sec)
So read this entry and then go to the JSON Mofication Documentation and be less shy.

Next time -- Back to JSON coding with PHP

Wednesday, March 2, 2016

Using PHP's JSON_ENCODE with the MySQL JSON Data Type

Since I have been presenting on MySQL's JSON data type, I have had questions on how to encode the JSON document into a database column. It has to be a VALID JSON document or the MySQL server will reject it. JSON is a way of storing data for interchange and it consists of two ways of storing data -- key/value pairs called objects and an ordered list of values called an array. To add to this every programming language has its own definitions of what is an object or what is an array, especially PHP. In my early experiments I had a hard time figuring out the various functions needed and am glad to report I was over complicating things.

So lets us start with the basics:

From JSON.ORG we are told this about objects:

An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).

And this about arrays:

An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).

And of course, a value is defined as:

A value can be a string in double quotes, or a number, or true or false or null, or an object or an array. These structures can be nested.

JSON's power is in the ability to store keys and values. Most of the example docs you will find are collections of objects with some arrays and other objects within them.

Objectifying Objects

Time to create an object. Lets take an associative array like so:

$my_array = array('a' => 1, 'b' => 2, 'c'=3);

We can send this through PHP's JSON_ENCODE function. And the output is:

{"a": 1, "b": 2, "c": 3}

The object begins and ends with braces, the name is separated from the value by a colon, ans pairs are separated by commas. So this is a valid JSON document. We can wrap that into single quotes and insert it into MySQL.

mysql> INSERT INTO foobar (mydata) VALUES ('{"a": 1, "b": 2, "c": 3}');
Query OK, 1 row affected (0.01 sec)

mysql> 
So PHP's JSON_ENCODE works with this simple object.

Array Array

This time we will use an array to build a JSON array.
$my_other_array  = array(1,2,'c');

And the output from PHP's JSON_ENCODE is:

[1,2,"c"]

And again wrapping that in single quotes lets us put it into our JSON column.

mysql> INSERT INTO foobar (mydata) VALUES ('[1,2,"c"]');
Query OK, 1 row affected (0.00 sec)

mysql> 

But what about a much more complex document? (Thanks to http://www.sitepoint.com/youtube-json-example/)

{"apiVersion":"2.0",
 "data":{
    "updated":"2010-01-07T19:58:42.949Z",
    "totalItems":800,
    "startIndex":1,
    "itemsPerPage":1,
    "items":[
        {"id":"hYB0mn5zh2c",
         "uploaded":"2007-06-05T22:07:03.000Z",
         "updated":"2010-01-07T13:26:50.000Z",
         "uploader":"GoogleDeveloperDay",
         "category":"News",
         "title":"Google Developers Day US - Maps API Introduction",
         "description":"Google Maps API Introduction ...",
         "tags":[
            "GDD07","GDD07US","Maps"
         ],
         "thumbnail":{
            "default":"http://i.ytimg.com/vi/hYB0mn5zh2c/default.jpg",
            "hqDefault":"http://i.ytimg.com/vi/hYB0mn5zh2c/hqdefault.jpg"
         },
         "player":{
            "default":"http://www.youtube.com/watch?vu003dhYB0mn5zh2c"
         },
         "content":{
            "1":"rtsp://v5.cache3.c.youtube.com/CiILENy.../0/0/0/video.3gp",
            "5":"http://www.youtube.com/v/hYB0mn5zh2c?f...",
            "6":"rtsp://v1.cache1.c.youtube.com/CiILENy.../0/0/0/video.3gp"
         },
         "duration":2840,
         "aspectRatio":"widescreen",
         "rating":4.63,
         "ratingCount":68,
         "viewCount":220101,
         "favoriteCount":201,
         "commentCount":22,
         "status":{
            "value":"restricted",
            "reason":"limitedSyndication"
         },
         "accessControl":{
            "syndicate":"allowed",
            "commentVote":"allowed",
            "rate":"allowed",
            "list":"allowed",
            "comment":"allowed",
            "embed":"allowed",
            "videoRespond":"moderated"
         }
        }
    ]
 }
}

Once again we wrap all in single quotes and MySQL accepts it as a valid JSON document. So the PHP built-in function JSON_ENCODE does the job very well.

Wednesday, February 24, 2016

MySQL JSON Keys

Continuing from the last entry on using MySQL's new JSON data type , let us take a look at the keys in our sample database. Use JSON_KEYS to get a lit of the Keys withing the document.
mysql> SELECT JSON_KEYS(info) FROM stooge;
+-----------------+
| json_keys(info) |
+-----------------+
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
+-----------------+
4 rows in set (0.00 sec)
So how many records have a 'job' key?
mysql> select  JSON_CONTAINS_PATH(info,'all','$.job') from stooge;
+----------------------------------------+
| JSON_CONTAINS_PATH(info,'all','$.job') |
+----------------------------------------+
|                                      1 |
|                                      1 |
|                                      1 |
|                                      1 |
+----------------------------------------+
4 rows in set (0.00 sec)
The 1 means the record has a 'job' key and a 0 would indicate a lack. Not too interesting in this case as all the JSON docs have the same keys. But how do you gran one particular key/value, also know as a object? If we know the object(s)/ record(s) sought contain something to search on we pass that to JSON_CONTAINS.
SELECT * FROM stooge WHERE JSON_CONTAINS(info,'{"job": "Head Stooge"}');
My history of fat fingering brackets, braces, and single/double quotes is pretty long and is it not cheating to use JSON_OBJECT to help here.
mysql> SELECT * FROM stooge WHERE JSON_CONTAINS(info,JSON_OBJECT('job','Head Stooge'));
+------+----------------------------------------------+
| id   | info                                         |
+------+----------------------------------------------+
|    1 | {"job": "Head Stooge", "name": "Moe Howard"} |
+------+----------------------------------------------+
1 row in set (0.00 sec)
Now up to this point we have not extracted the JSON data into a generated column for SQL compatible indexes. If we look at the EXPLAIN for the last query it tells use we had a full table scan. And full table scans are not efficient.
mysql> EXPLAIN select JSON_SEARCH(info,'Comic','job') from stooge\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stooge
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> 

Monday, February 22, 2016

Grab JSON DATA from MySQL and Update It

So lets get some data from a JSON column from a table in a MySQL database and update it. JSON data is is simply a column like a REAL, INTEGER, CHAR, or VARCAHR. So fetching the data from a JSON column is the same as any other column. The same database we have has two columns -- a integer for a identification number and a second for JSON data.

Fetch Data

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "SELECT * FROM stooge";
$result = $mysqli->query($query1);
    while($row = $result->fetch_assoc()) {
        printf("%d - %s\n", $row["id"], $row["info"]);
}

$mysqli->close();
?>
When the program executes we see the following:
1 - {"name": "Moe Howard"}
2 - {"name": "Larry Fine"}
3 - {"name": "Shemp Howard"}

So lets add another record

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "INSERT INTO stooge VALUES (4,JSON_OBJECT('name','Curly Howard'))";
print "$query1\n";
if(!$result = $mysqli->query($query1)) {
 printf("Error: %s\n", $mysqli->error);
}

$mysqli->close();
?>

Please note that the example programs are very simple and from here on out they will skip some obvious error checking. I am trying to show the concepts of using the MySQL JSON data type and not the role of proper error checking which you should have ingrained in you PERIOD. I am going for brevity in the examples and not teaching proper programming practices.
mysql -u root -p test -e "SELECT * FROM stooge\g";
Enter password: 
+------+---------------------------+
| id   | info                      |
+------+---------------------------+
|    1 | {"name": "Moe Howard"}   |
|    2 | {"name": "Shemp Howard"} |
|    3 | {"name": "Larry Fine"}   |
|    3 | {"name": "Curly Howard"} |
+------+---------------------------+
So how do we get just the name. We can use JSON_EXTRACT in two ways to get the data. We can use JSON_EXTRACT directly as in the example code below or the short hand SELECT info->"$.name" AS Name FROM stooge; The arrow operator is shorthand for JSON_EXTRACT and there is no penalty for using one over the other.
#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "SELECT JSON_EXTRACT(info, '$.name') AS Name  FROM stooge";
$result = $mysqli->query($query1);
    while($row = $result->fetch_assoc()) {
        printf("%s\n", $row["Name"]);
}

$mysqli->close();
?>

And we get the names as so:
"Moe Howard"
"Larry Fine"
"Shemp Howard"
"Curly Howard"
Don't like the double quotes? Wrap JSON_UNQUOTE around the core of the select as in 'SELECT JSON_UNQUOTE(info->"$.name") AS Name FROM stooge' to remove them.

A new Key/Value Pair

Lets add a new Key/Value pair to our data. Each of the JSON documents will get a new Key names 'job' and a value inserted. We have to tell the serve which document we are using (you can have more than one JSON column or document per table), the name of this Key, and a value. We use "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')" to tell the server we are adding a new key to the info document/column, naming it job and for this example all the records are given the same job value.
!/usr/bin/php
?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')";
$result = $mysqli->query($query1);

$mysqli->close();
?>
The data:
mysql> select * from stooge;
+------+------------------------------------------+
| id   | info                                     |
+------+------------------------------------------+
|    1 | {"job": "Comic", "name": "Moe Howard"}   |
|    2 | {"job": "Comic", "name": "Larry Fine"}   |
|    3 | {"job": "Comic", "name": "Shemp Howard"} |
|    4 | {"job": "Comic", "name": "Curly Howard"} |
+------+------------------------------------------+
4 rows in set (0.00 sec)
Note that MySQL alphabetizes the keys.

Replacement Job

Let's change Moe Howard's job. We will use the LIMIT clause on our SQL to update only one entry (and Moe is the first entry in this data set).
#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "UPDATE stooge SET info = JSON_REPLACE(info,'$.job','Head Stooge') LIMIT 1";
print "$query1\n";

$result = $mysqli->query($query1) ;


$mysqli->close();
?>

And we update only the first record:
mysql> select * from stooge;
+------+----------------------------------------------+
| id   | info                                         |
+------+----------------------------------------------+
|    1 | {"job": "Head Stooge", "name": "Moe Howard"} |
|    2 | {"job": "Comic", "name": "Larry Fine"}       |
|    3 | {"job": "Comic", "name": "Shemp Howard"}     |
|    4 | {"job": "Comic", "name": "Curly Howard"}     |
+------+----------------------------------------------+
4 rows in set (0.00 sec)

Next time we will dig deeper into the MySQL JSON Functions.

Monday, February 15, 2016

MySQL JSON Functions to Create Values

MySQL 5.7's new JSON data type has three functions to help you make sure your data is a valid utf8mb4 character set JSON document. They are JSON_ARRAY, JSON_QUOTE, and JSON_OBJECT. (You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON)) What is the big difference? And when would you use one over another?

JSON_ARRAY takes a string as input and returns a JSON array with the values from the string.

mysql> select JSON_ARRAY('')
+----------------+
| JSON_ARRAY('') |
+----------------+
| [""]           |
+----------------+
1 row in set (0.01 sec)

mysql>  SELECT JSON_ARRAY('Foo', 42, now());
+-------------------------------------------+
| JSON_ARRAY('Foo', 42, now())              |
+-------------------------------------------+
| ["Foo", 42, "2016-02-15 07:31:56.000000"] |
+-------------------------------------------+
JSON_QUOTE takes the string given as input and wraps it with double quotes.
mysql> SELECT JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}');
+----------------------------------------+
| JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}') |
+----------------------------------------+
| "{foo,1,{0,1,2},\"FOOBAR\"}"           |
+----------------------------------------+
1 row in set (0.00 sec)
And finally JSON_OBJECT takes a list of key/value pairs and returns a JSON object containing those pairs. It will error if the number of arguments is odd or a key is named NULL.
 SELECT JSON_OBJECT('a',1,'bb',345);

+-----------------------------+
| JSON_OBJECT('a',1,'bb',345) |
+-----------------------------+
| {"a": 1, "bb": 345}         |
+-----------------------------+
1 row in set (0.00 sec)
So you would use JSON_OBJECT for creating JSON Objects (key/value pairs), JSON_ARRAY for creating arrays, some of which could be including in a bigger JSON Document, and JSON_QUOTE to escape embedded quotes. Next time we will look at the MySQL JSON Functions to modify JSON data.

Friday, February 12, 2016

MySQL JSON Meta Data Fuctions

Lost post covered the use of the MySQL JSON functions to feed data into the database server and why the PHP json_encode did not provide what was needed. THis time we will look how to examine the data once it is in a JSON column. We started with a simple associative array and fed it into the database.
mysql> SELECT * FROM foobar;
+--------------------------+
| mydata                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

MySQL 5.7 has a group of JSON functions for looking at attributes. See the MySQL Manual 12.16.5 Functions That Return JSON Value Attributes. And they do not always work in an intuitive fashion! JSON_DEPTH returns the maximum depth of a JSON document. It will return a NULL if passed a NULL and return an error if the document is not valid JSON. An empty array, object or scalar value has a depth of 1. Non empty arrays or objects with elements or members of depth 1 returns a 2. Beyond that it returns the number of the number of the depth of the JSON document. Clear as mug, right? So lets look at our example array now fed into MySQL.
mysql> SELECT JSON_DEPTH('{"a": 1, "b": 2, "c": 3}');
+----------------------------------------+
| json_depth('{"a": 1, "b": 2, "c": 3}') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set (0.00 sec)
Still not a lot of help. So lets go simpler.
mysql> select json_depth('[]'),json_depth('[1]');
+------------------+-------------------+
| json_depth('[]') | json_depth('[1]') |
+------------------+-------------------+
|                1 |                 2 |
+------------------+-------------------+
1 row in set (0.00 sec)
So the empty array [] has a depth of 1. An array with a value inside it has a depth of two. But it helps if we go even simpler. Let look at a cut down version of the test array and then the JSON doc.
mysql> select json_depth('{"a": 1}');
+------------------------+
| json_depth('{"a": 1}') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)
The JSON doc itself looks like
{
  "a": 1
}
Now it gets a little clearer! The document has a depth of two from the two elements, a and 1. So lets go a little overboard. Here is an example from http://json-schema.org/example1.html
{
    "id": 1,
    "name": "A green door",
    "price": 12.50,
    "tags": ["home", "green"]
}
mysql> select JSON_DEPTH('{"id": 1, "name": "A green door", "price": 12.50,"tags": ["home", "green"]}'); 
+---------------------------------------------------------+
| JSON_DEPTH('{
    "id": 1,
    "name": "A green door",
    "price": 12.50,
    "tags": ["home", "green"]}') |
+----------------------------------------------------------+
|                                                        3 |
+----------------------------------------------------------+
Run again without the 'tags' line and the depth is 2. So this is a case where I have to draw out the doc to understand the depth. JSON_LENGTH returns the length of the doc or of the element in the document you are referencing. The length of a scalar is one, the length of an array is the number of elements, the length of an object is the number of objects, and be cautious as it doesn't count nested objects or arrays. mysql> select json_length('{"a": 1, "b": 2, "c": 3}'); +-----------------------------------------+ | json_length('{"a": 1, "b": 2, "c": 3}') | +-----------------------------------------+ | 3 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select json_length('{"a": 1, "b": 2, "c": 3, "d": 4}'); +-------------------------------------------------+ | json_length('{"a": 1, "b": 2, "c": 3, "d": 4}') | +-------------------------------------------------+ | 4 | +-------------------------------------------------+ 1 row in set (0.00 sec) An example were a single item in the doc is tested for length.
mysql> select json_length('{"a": 1, "b": 2, "c": 3}','$.a');
+-----------------------------------------------+
| json_length('{"a": 1, "b": 2, "c": 3}','$.a') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> 
JSON_TYPE returns the a string telling the type of the item. mysql> select json_type('{"a": 1, "b": 2, "c": 3}');
+---------------------------------------+
| json_type('{"a": 1, "b": 2, "c": 3}') |
+---------------------------------------+
| OBJECT                                |
+---------------------------------------+
1 row in set (0.00 sec)
And you can drill down to individual elements.
mysql> select json_type(json_extract('{"a": 1, "b": 2, "c": 3}','$.a'));
+-----------------------------------------------------------+
| json_type(json_extract('{"a": 1, "b": 2, "c": 3}','$.a')) |
+-----------------------------------------------------------+
| INTEGER                                                   |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
And finally JSON_VALID returns a 1 if you have a valid JSON document. VEry handy if you have doubts for testing the data before trying to shove it into the database.
mysql> select json_valid('{"a": 1, "b": 2, "c": 3}');
+----------------------------------------+
| json_valid('{"a": 1, "b": 2, "c": 3}') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)
Next time we will look at more MySQL 5.7 JSON functions and see how they can be used by a PHP code for world domination betterment of the world.

MySQL's JSON Functions Verses PHP's JSON Functions

The MySQL JSON data type only accepts valid JSON documents. PHP has a handful of JSON functions but sadly json_encode does not provide what the database server wants. Lets start with a simple array.
$alpha = array('a' => 1, 'b' => 2, 'c' => 3);
This is a very simple associative array that we want to turn into a JSON doc with there elements, where 'a' is equal to 1 etcetera. This would look like this:
{"a": 1, "b": 2, "c": 3}
Using the built in PHP function json_encode we get this:
{"a":1,"b":2,"c":3}
Fantastic. We should be able to feed that into query, send it to the server, and be good to go. But when we try to feed that into MySQL it will return a syntax error. MySQL can be fussy about sending data in quotes, especially unescaped, in a query. So the associate array has to be serialized (fancy way to saw written out to a string) in a format MySQL can accept. That format means having the key in single quotes and the value unquoted.
$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
Next we use the MySQL JSON_OBJECT function to make sure it will pass muster with the server.
$query = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query\n";
if ($result = $mysqli->query($query)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
Then we can check the database.
mysql> select * from foobar limit 1;
+--------------------------+
| mydata                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

Complete test program listing:
#!/usr/bin/php
 1, 'b' => 2, 'c' => 3);
echo var_dump($alpha);
echo "json_encoded: " . json_encode($alpha) . "\n";
echo "json_encoded HEX: " . json_encode($alpha,JSON_HEX_QUOT) . "\n";
$query0 = "SELECT JSON_OBJECT(" . json_encode($alpha) . ")";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);

}


$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
$query1 = "SELECT JSON_OBJECT($alphaz)";
echo "$query1\n";

if ($result = $mysqli->query($query1)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
        echo "FOO!!!!\n";
}
$query2 = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query2\n";
if ($result = $mysqli->query($query2)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
$query3 = "SELECT * FROM foobar";
if ($result = $mysqli->query($query3)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

$mysqli->close();
?>

Thursday, February 11, 2016

MySQL's JSON Data Type

MySQL 5.7 features a native JSON datatype. So just like a INT, CHAR, or other data type, you can now store valid JSON documents in a MySQL database. Previously you could put JSON formatted data into a CHAR, a TEXT, or similar data type. But it was a real pain to search this JSON data for items contained in them. But now you can store the JSON and there is a list functions to allow full access to this data in the column.
shell$ mysql -u root -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE jdata (mydata JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO jdata (mydata) VALUES (JSON_OBJECT('name', 'Dave', 'height', 65));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM jdata;
+-------------------------------+
| mydata                        |
+-------------------------------+
| {"name": "Dave", "height": 65} |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(mydata,'$.name') FROM jdata;
+-------------------------------+
| JSON_EXTRACT(mydata,'$.name') |
+-------------------------------+
| "Dave"                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> 

Please note the above SELECT statement. SQL is designed to pull data from columns but JSON data is in documents so we now need functions to pull keys from documents in that column. JSON_EXTRACT is looking in the mydata column and we use the $.name to say 'we want the value of this key in the current document'. Over the next few blog posts I will cover these functions and more.