sqlpyzer: IPython + sqlite in html5

What is a sqlpyzer?

sqlpyzer is an experimental plugin I wrote for the IPython  Notebook. It’s much easier to describe what it is in a video, so here goes:

You can find the source and the installation instructions here:

https://github.com/RishiRamraj/sqlpyzer

The Name

Sadly all of the good SQL names were taken. I was going for sqlizer but things fell apart quickly when I tried to add py.

Installation

There are installation instructions on the github page, but I’ll go into a bit more detail here. I haven’t tested these steps, so please comment with any corrections. I’m currently running Ubuntu 12.04 32bit desktop. I’ve tested the plugin using Python 2.7 and Chromium. I’m going to assume that you have your github repository located in ~/Repo. You’ll need a specific build of IPython; one that supports jsplugins. You can find such a build at:

https://github.com/RishiRamraj/ipython

Be sure to check out the “json” branch. Once you have ipython checked out you can then run the notebook by using the ipython.py script in the root directory. This script will then launch the notebook and open a browser.

user@computer:~/Repo/ipython$ python ipython.py notebook

Next, we’ll install sqlpyzer (again, sorry for the name). Close the notebook for now, we’ll get back to it later. I recommend you use pip and virtualenv. If you’re not familiar with these tools, check this out:

http://jontourage.com/2011/02/09/virtualenv-pip-basics/

Install sqlpyzer:

user@computer:~/Repo/sqlpyzer$ pip install -e .

By using a develop egg above, you’ll be able to modify sqlpyzer without having to reinstall it. Next, we’ll have to install the sqlpyzer javascript. You’ll need to find the ipython local configuration folder. In Ubuntu:

user@computer:~$ cd .config/ipython/profile_default/static/jsplugins/

Simlink the Javascript files so that you can modify the Javascript in your repository:

user@computer:~/.config/ipython/profile_default/static/jsplugins% ln -s /home/user/Repo/sqlpyzer/js/* .

Finally, run the notebook again and open the notebook located here to test the plugin:

~/Repo/sqlpyzer/sqlpyzer.ipynb

Repo Structure

There are three things of interest in the sqlpyzer repo. The sqlpyzer folder contains all of the python responsible for getting the data out of python and marshalled into json. The js folder contains all of the javascript that creates the sqlite database and marshals the json into the database.

Python

load.py

The load.py module contains one function. It is called by the load_ext function in IPython. Its job is to tell IPython how to take a data object and display it. Everything comes together on this line:

formatter.for_type_by_name(module, name, serializer)

The serializer function is called every time you issue the display command on a sqlpyzer.Data object.

data.py

This module contains a simple validator and data type. Besides validation, the only real purpose this data type servers is to be able to associate Data objects with a specific serializer function that tells IPython which Javascript handler to use.

serialize.py

The function in this module turns the Data object into a json string. The resulting object has two properties. First is the data object. Note that the “data” key can really be called anything. Second is the handler. The handler is special as it tells IPython which Javascript function to use to interpret the json being passed in.

__init__.py

In order for IPython to be able to load a module as an extension, the module needs to have a load_ipython_extension function. The __all__ list tells python that it should load the load_ipython_extension when the module is loaded, as an attribute of the module. If you’re not familiar with __all__, take a look at:

http://docs.python.org/2/tutorial/modules.html#importing-from-a-package

Everything Else

constants.py has some constants like the Javascript callback name. test.py contains tests. To run the tests use:

user@computer:~/Repo/sqlpyzer/sqlpyzer$ python test.py

Javascript

Disclaimer: I know more about Python than I do Javascript.

console.js

Ths script takes the id of a div as an argument, and creates the database object with the UI. The UI should automatically resize its controls to fit in whatever container you put it in. The display function is the interesting part in this module. It demonstrates how to retrieve data from a sqlite query. Once the console is created, it returns an object containing the database, input and output fields.

sqlpyzer.js

Possibly the largest single piece of source in this project. If you’re looking for the Javascript that associates a the handler name with the Javascript callback, look all the way at the end of the file:

IPython.json_handlers.register_handler('sqlpyzer_data', sqlpyzer.data);

The sqlpyzer.data callback takes two parameters; the json object that we serialised in from python and the new element created under the display function. It then creates a console and uses the json data to populate the database.

When reading the create_table code, it’s important to remember that Javascript doesn’t block when executing SQL. Instead, you need to specify callbacks that are called if a query succeeds or fails. You can get away with calling them in parallel in a single transaction, if your queries are independent as with this for loop. In the background, javascript is queuing the SQL execution to happen as a set of callbacks, once the for loop is complete.

// Create all the tables.
console.database.transaction(function (tx) {
     for (table in json.data) {
         sqlpyzer.create_table(tx, table, json.data[table]);
     } 
});

If your queries depend on other queries like row insertion depending on table creation, things start getting interesting. You need to specify a callback that will insert all of the rows once the table has been created. You’ll notice that the create_table function has two functions called create_schema and insert_row for this reason. create_schema exposes a callback called next that then inserts all of the rows in parallel:

// Pull it all together to create the table.
create_schema(tx, function(tx) {
    for (var index = 0; index < table.length; index++) {
        var row = table[index];
        insert_row(tx, row);
    }
});

Closures make this sort of work a easy. They basically set the context for the callbacks used to create the schema and the individual rows. Otherwise, we would have to pass all of the table level parameters into both callbacks, which would be messy. If we were doing this sort of work sequentially with a language like python, your context containers would be your for loops:

# Table level context.
for name, table in tables
    # Create the schema.
    schema = get_schema(table)
    create_schema(schema)

    # Row level context.
    for row in table:
        # Create the row.
        insert_row(schema, row)

Once you insert a row, how do you get the id of the new row? I’m glad you asked! My initial thought was to use the last_insert_rowid function:

select last_insert_rowid() as id;

However in Javascript you cannot control the order of your callbacks and so you may end up with the wrong ID. Fortunately, the result set has a handy property called insertId. You can see it at work here:

// Generate the insert query.
var query = 'INSERT INTO '+name+'('+columns.join(', ')+') ';
query += 'VALUES ('+params.join(', ')+')';
tx.executeSql(query, values, function (tx, data){
    // The inserted row id.
    var new_id = data.insertId;

Debugging

Unfortunately IPython has a nasty tendency to eat exceptions that happen in both the serialise.data function in Python and in the sqlpyzer.data function in Javascript. In Python be sure to completely test your serializers. Unfortunately, I wrote no unit tests in Javascript. Instead, I found modifying this file:

~/Repo/ipython/IPython/frontend/html/notebook/static/js/jsonhandlers.js

to this effect helped a bit:

JSONHandlers.prototype.call_handler = function(key, json, element) {
    var handler = this.handlers[key]
    if (handler !== undefined) {
        /*
        try {
            handler(json, element);
        } catch(err) {
        };
        */
        handler(json, element);
    };
};

Conclusion

That’s about it! You know about as much as I do about making IPython talk with sqlite via html5. I’m not entirely sure what that means, but I had fun. If you end up doing anything interesting with this experiment please let me know! Again, sorry about the name…

Posted in Uncategorized | Leave a comment