MySQL JSON fields on the ground!

Having to add a series of custom fields to a quite relational application, I decided to try the new JSON fields.

As of now you can:

– create json fields
– manipulate them with json_extract, json_unquote
– create generated fields from json entries

You can not:

– index json fields directly, create a generated field and index it
– retain the original json datatype (eg. string, int), as json_extract always returns strings.

Let’s start with a simple flask app:

# requirements.txt
mysql-connector-python
Flask-SQLAlchemy==2.0
SQLAlchemy>=1.1.3

Let’s create a simple flask app connected to a db.

import flask
import flask_sqlalchemy
from sqlalchemy.dialects.mysql import JSON

# A simple flask app connected to a db
app = flask.Flask('app')
app.config['SQLALCHEMY_DATABASE_URI']='mysql+mysqlconnector://root:secret@localhost:3306/test'
db = flask_sqlalchemy.SQLAlchemy(app)

Add a class to the playground and create it on the db. We need sqlalchemy>=1.1 to support the JSON type!

# The model
class MyJson(db.Model):
    name = db.Column(db.String(16), primary_key=True)
    json = db.Column(JSON, nullable=True)

    def __init__(self, name, json=None):
        self.name = name
        self.json = json

# Create table
db.create_all()

Thanks to flask-sqlalchemy we can just db.session 😉

# Add an entry
entry = MyJson('jon', {'do': 'it', 'now': 1})
db.session.add(entry)
db.session.commit()

We can now verify using a raw select that the entry is now serialized on db

# Get entry in Standard SQL
entries = db.engine.execute(db.select(columns=['*'], from_obj=MyJson)).fetchall()
(name, json_as_string), = first_entry  # unpack result (it's just one!)
assert isinstance(json_as_string, basestring) 

A raw select to extract json fields now:

entries = db.engine.execute(db.select(columns=[name, 'json_extract(json, "$.now")'], from_obj=MyJson)).fetchall()

(name, json_now), = first_entry  # unpack result (it's just one!)
assert isinstance(json_now, basestring) 
assert json_now != entry.json['now']  # '1' != 1 

Looking inside dojo…and patching

While developing a simple rest service I needed a simple web interface.

While a full-featured dojo is quite heavy, if you use dojo mobile without importing any dijit stuff you can speed up things a lot. And if you use an http cache for static contents thing will even get better!

I choose dojo mobile 1.7 because it has a nice Grid+Store implementation.

Grid+Store is a toy that renders the json output of an url into an html table or list. When the url response changes, the page is updated.

Moreover the Grid/List can bind each row to an action, so that if you click on a item, you’ll be directed to another page. In my case the main view shows a list of file, and clicking on one file you will see it’s attributes (size & co).

So, when clicking on a file I needed to issue an ajax request retrieving file info from the server.
Dojo implements this using the “callback” tag.
ex.

  • file.txt

  • The previous html is generated by a json string like the following, so with dojo Store you can dynamically build a list with actions connected to each item.

    { 'item' : {
    'label' : 'file.txt',
    'moveTo' : 'fileView',
    'callback' : 'function() {renderfile("/path/to/file.txt");}'
    }
    }

    In dojo 1.7 the callback stuff is broken, so after looking on dojo trac, I found and apply a patch. Still no way, but at least I got an error – while before the patch the callback parameter was simply ignorated.

    The error was something like

    Object "renderfile('/path/to/file.txt')" has no method apply

    The apply() method is a js method that a function must have. Better: if a js object hasn’t the apply() method, then it’s not a function!

    Using js console I found that the callback argument was interpreted as a string, and not as a function.

    So I start investingating on the json I used to populate the grid. And I noted that the issue was related to the quote sign around the callback function.

    In fact json encloses in string only strings, not numbers or functions. But the json was generated by the server, using python simplejson library. For that lib, a js function is a string, so it’s encosed in quote.

    The solution was to find the place where the function was called in dojo, and add a test: if the object supposed to contain a method is a string, then you evaluate the string to a method.

    >>>lang.js
    +if (typeof(scope[method].apply) == "undefined" ) {
    + scope[method] = eval(scope[method]);
    +}
    return scope[method].apply(scope, arguments || []);

    >>>ViewController.js
    +if (src.callback && typeof(src.callback.apply) == "undefined") {
    + src.callback = eval(src.callback);
    +}
    w.performTransition(moveTo, evt.detail.transitionDir, evt.detail.transition, src.callback && src, src.callback);

    Json and Django: mythe and music in the dojo

    Json have been already presented by Fabio Fucci on this blog. Django is a python framework for web application, which supports json thru a library.

    We’re going to create a simple request-response application:

    1. request is issued by dojo.xhrPost;
    2. response is managed by python.

    Creating a request means creating a json string to send to the server.

    /* create the variable to post */
    var arguments={'user':'ioggstream','status':'at work'};

    dojo.xhrPost({
    url: '/json/post',
    handleAs: "json",
    /* serialize the argument object to a json string*/
    postData: dojo.toJson(arguments),
    load: function(data) {
    alert(data.result);
    },
    error: function(error) {
    alert("An unexpected error occurred: " + error);
    }
    });

    Now that the request is issued, and the postData is a json string, we use python to de-serialize the string to a python object.
    The dict() python class – aka dictionary – is an associative array. The django.simplejson can serialize and deserialize object using dict().

    Let’s see the code

    from django.utils import simplejson
    from google.appengine.ext import webapp

    class JsonService(webapp.RequestHandler):
    def post(self):
    logging.info("manage a POST request")
    # parse request body to a python dict() object
    args = simplejson.loads(self.request.body)
    # returning request in a verbose mode
    # creating a dict() object with default message
    message = {'result':'The request misses user and/or       status'}
    if 'user' in args and 'status in args:
    message['result'] = "The request user %s status is %s " % (args['user'], args['status'])
    # return the serialized object message
    return simplejson.dumps(message)