Friday, December 30, 2011

Baby Steps with Dojo DataGrid and JsonRestStore Error Handling

These holidays I decided to teach myself something new.  I decided to take a look at Dojo, for being able to offer professional looking and behaving UI components in web based developments.

Instead of leaping into full prototypes of UI layout, I thought I better keep my first attempt at Dojo relatively simple.  I'm starting with a DataGrid that is using a JsonRestStore as the store.  The JsonRestStore is being serviced by a CakePHP 2.0 backend.  I've written a plugin component that will assist with converting between the JsonRestStore method of paging, and the CakePHP method of paging, but that's a topic for another post, and perhaps a release to GitHub.

For this experiment, I ended up using Dojo 1.6.1, even though Dojo 1.7.1 is available.  When I first started playing with Dojo, 1.7.1 had been released, but it was not available on the CDN.  Then I tried playing with an example that defined a custom module to interact with CakePHP pagination.  Unfortunately, there was a bug in 1.7.0 that meant mixing CDNs and local custom modules was out.  So I downloaded 1.7.1.  Then there was another bug that meant mixing local custom modules with the locale Dojo path was also out.  So I ended up falling back to 1.6.1. After all, all the tutorials were based on 1.6 anyway.  It's doco like this that makes me appreciate the work that the developers at put in to the manual, before the release goes live.  Cynical aside time: I recall watching a Dojo promo for what must have been the 1.6 release.  Many of the featured developers commented how the first step to learning Dojo was to look at the code.  Only a couple mentioned going through the tutorials.  One mentioned jumping on IRC, but was quick to qualify that you should only ask questions if you knew what you were talking about.  Dojos' current documentation might be an improvement on what it used to be, but it falls short of the mark if the recommendation is to look at the code, rather than read the manual.  On the flipside, the featured developers were right.  You're going to have to dive in to the code, given the doco in its current form.  Anyway, on with the show.

My end goal is to have a DataGrid that will allow inline editing, but report errors in saving in a manner that is consistent with CakePHP forms.  That is, after the post, the form keeps the edited values, but displays an indicator and message next to each offending field.  In the case of the grid, I've opted for a red border on the offending cell, and a tooltip that will display the associated error message on mouse over of the cell.

The first thing I did was run up a basic DataGrid using a JsonRestStore.  Inside the dojo.ready(), I defined the data store and the grid.

var dataStore = new{

var layout = [[
    {'name': 'Column 1', 'field': 'id', 'width': '100px'},
    {'name': 'Column 2', 'field': 'col2', 'width': '100px', cellType:dojox.grid.cells.Bool, styles:'text-align:center;'},
    {'name': 'Column 3', 'field': 'col3', 'width': '200px'},
    {'name': 'Column 4', 'field': 'col4', 'width': '150px'}

var grid = new dojox.grid.DataGrid({
    id: 'grid',
    store: dataStore,
    structure: layout,
    rowSelector: '20px'
}, "gridDiv");


Using examples found around the usual Dojo haunts, I added Add Row and Remove Selected Rows buttons, and functionality. The layout was updated to make columns 2 to 4 editable.

The Add and Remove functions worked well enough, but the inline editing wasn't persisting the changes to the server.  Here's where I learn my first DataGrid lesson.  DataGrid will sync the data to the store, but if the store needs persisting to a server, like a JsonRestStore does, then you are responsible for doing that yourself.  This should have been evident with the add and remove actions associated with the buttons calling  So I set up a simple action for the onApplyEdit event to save the dataStore.

var applyEdit = function(rowIdx) {;
dojo.connect(grid, "onApplyEdit", applyEdit);

I should note that my code didn't actually look like that at the time. I had actually overridden the onApplyEdit directly in the grid definition. Since then, I've come to appreciate that if you're going to provide a function to the constructor arguments, defining one to a variable, then using dojo.connect() is the best course of action, since this will append your function to be called with the event, instead of overriding it. I think. I'm still a Dojo noob, so I'm just going to use dojo.connect() because it seems like the right thing to do.

Well, that editing is great if all is going well.  But if there's an error with the save action, or at least validation is failing, what to do?

Well, I spent hours trying to find an example of error handling for DataGrids and JsonRestStores, but I couldn't find a bloody thing.  Error handling is definitely something that you end up having to write yourself.  Here's how I deal with it.

First up, I needed to communicate the fact that validation had failed for an edit.  With CakePHP, you end up with a validationErrors array in your controller that gets passed to the view, and used in the form.  I created an error element that would display the session flash and the validationErrors in a JSON array.  I would strip the model name out of the validationErrors array, so I didn't have to deal with it in Dojo.  And if I did detect an error in the save, then I would return a 409 status code.

The next part was recording those errors in a place where I could then get at it to change how the grid was rendered.  First things first, storage.  And the dataStore was the best place for that storage.  Here is the modification to the applyEdit function, storing any validation errors.

var applyEdit = function(rowIdx) {

    var actions =;

    dojo.forEach(actions, function(action){
        var result = action.deferred.then(function(){
            if ( {
            if (err.responseText) {                        
                try {
                    var responseText = JSON.parse(err.responseText);
           = responseText.validationErrors;
                } catch (e) {};

I eventually figured out that I would need to use Deferreds to be able to get a hook in to handling errors that might be returned in the JsonRest response for the JsonRestStore. Since it is possible for the save action on a store to save more than just one changed record, I would need to loop through all of the actions that were sent to the service, and perform the error checking against each one.

Thusly, I've attached a then() to the action.deferred. The first function in the then() is run on success.  This one will clear any validation errors for the target row, if any previously existed.  The second function is run if there was an error. Hopefully, the error responseText is parsable JSON containing validation errors.  I guess this part could do with further hardening.  What if the response didn't contain parsable JSON?  What if it did, but didn't contain validation errors?

Error handling in DataGrids with JsonRestStore is such a big deal.  It's such a pity that the subject does not get directly addressed in the available tutorials.

Now that I have the validation errors in the dataStore, I can use a cell formatter to detect and highlight the offending cell with a red border.  I'm actually just going to apply a CSS class, and let CSS take care of the rest.

var formatter = function(val, rowIdx, cell) {
    var item = grid.getItem(rowIdx);

    if (item.validationErrors && item.validationErrors[cell.field]) {
    return val;

Also add formatter: formatter to the records in the layout variable that are editable, namely, columns 2, 3 and 4. You'll also need to define the CSS.  You'll need to be specific, if you hope to have your CSS get included ahead of the other styles defined.

.claro .dojoxGridCell.validation-error {
    border: 1px solid red;

While I was at it, I also added the code for the tooltip that would display on mouse over of the offending cell.  This part of my code, I do believe is flawed, but I'll chat about that after the code.  First well, need some code to show the tooltip on mouse over, and hide it on mouse out.  I've also tried to add some code to prevent the tooltip from showing while the field is being edited.  I do this because I believe the HTML node of  the cell is actually replaced during editing, the lost with it is the reference to the displayed tooltip.

var tooltipNode;
var showTooltip = function(e) {
    if (gridTooltipEnabled) {
 var msg;
 var item = e.grid.getItem(e.rowIndex);
 if (item.validationErrors && item.validationErrors[e.cell.field]) {
     msg = item.validationErrors[e.cell.field].join('
 if (msg) {
     dijit.showTooltip(msg, e.cellNode);
     // hold reference to cellNode with tooltip for easy hiding
     tooltipNode = e.cellNode;

var hideTooltip = function(e) {
    tooltipNode = null;

var gridTooltipEnabled = true;
// grid definition here ...

dojo.connect(grid, "onCellMouseOver", showTooltip);
dojo.connect(grid, "onCellMouseOut", hideTooltip);
// disable and hide tooltip while editing
dojo.connect(grid, "onStartEdit", function (cell, rowIdx) {
    gridTooltipEnabled = false;
    dijit.hideTooltip(tooltipNode); // can't get to a cellNode from cell

// enable tooltip when finished editing
dojo.connect(grid, "onCancelEdit", function(rowIdx) {
    gridTooltipEnabled = true;

This code constructs a tooltip from the validation errors, and displays it. If you start editing a cell, it will attempt to hide the tooltip, and prevent another from being displayed until you have cancelled editing or have applied the edit (I have a gridTooltipEnabled = true; at the top of applyEdit()).

What I really wanted to was to be able to identify a cellNode from the cell argument passed to onStartEdit, and call dijit.hideTooltip() against that.  But I just couldn't find a way to link these two items together.

The flaw is that if two tooltips are on display when editing starts, then only one of them, the most recently activated, is going to be hidden.

The last part to go is the behaviour of retaining the entered data after an error, and only removing it if the edit is cancelled, or another cell in the store is successfully saved.  You can't micro manage a JsonDataStore.  When you call save(), it applies for all dirty objects in the store, and when you call revert(), it applies for all dirty objects in the store.

To achieve this behaviour, you need to use the revertOnError property as an argument to the function in applyEdit().  A slight side effect to this property is that the cell will not get redrawn if there is an error.  As a result, formatter is not called, and you don't get to see the red border until you go to a different page of data in the grid, and then go back.  To remedy this, you need to force a render with a grid.update() after assigning validationErrors to the target row in applyEdit().

However, if the user wishes to not attempt the fix the validation errors, and wishes to cancel the whole change, they can start editing the cell, and then cancel edit by pressing Escape.  I've added some code to the onCancelEdit action to revert if the row was dirty, and had validation errors.

dojo.connect(grid, "onCancelEdit", function(rowIdx) {
    gridTooltipEnabled = true;

    // if canceling editing and previously had errors, clear the errors and revert
    var item = grid.getItem(rowIdx);
    if (item) {
        if (dataStore.isDirty(item) && item.validationErrors) {
            // would rather revert the individual item, but this will do

As mentioned in the comments, I would rather just revert the individual row, instead of reverting the whole store. To do this, I have a feeling I would need to perform an explicit fetch on the item, and replace the dirty item in the store. I'll keep it simple for the moment.

There was a Dojo bug I had to apply a workaround for, when using the revertOnError property.  JsonRest.commit() has a bad reference to a dirtyObject variable that doesn't exist.  Simply changing it to dirtyObjects doesn't work either, but the bug reporter was good enough to provide a patch that did work when the problem was reported almost two years ago.  Unfortunately, this means that I'll never be able to use this with a CDN, and I'd need to patch and rebuild the Dojo 1.6.1 distribution.  My next task will be to upgrade this to use Dojo 1.7.1 (or 1.7.2, if it has been released by then).  Hopefully this bug will be fixed by then, or perhaps I'll be able to bump the ticket.

Well, that was my first look at Dojo, and in particular, the DataGrid and JsonRestStore.  I'm hoping that all the tutorials get an upgrade, the documentation improves and show stoppers like no local custom modules with CDNs are fixed before too long.  Even though this was just baby steps, it still seemed like I had to delve to an intermediate level to get the functionality to a point where it would be useful.

If  I can get this working with the 1.7.1 CDN, then I'll load the example to GitHub.  I'll also do an article on the CakePHP plugin that assists with JsonRestStore paging.

Tuesday, December 20, 2011

MySQL Schema Diffs

MySQL Schema Diffs. This particular issue crops up at the end of every project, so this is more of a note to self. You might find it handy, too.

Initially, I used to do it the hard way. A PHPMyAdmin export of the old schema (from the production release), and a similar export from the development release. Then do a compare, usually in Eclipse using the Compare To Each Other, and manually parse the file to generate a migration script. Yeck.

For a while, I was using DBSync, an online tool that took the two MySQL SQL dumps as input, and would generate SQL to perform a diff. While it worked, it worked fairly well, though for the last year, it has been reporting an error when trying the create the first database. Your mileage may vary.

Failing that, I had to go back to hand written SQL, however, every time I come across the issue, I do a little more research, to see if anything has changed.

Well, this time I got lucky. It turns out that the MySQL Workbench can generate the schema diffs I'm after. It's just a hard option to find. I actually found a guide at Simpcode that covers 5.0.22. I'm working with 5.2.36 CE, so here's how I do it.

  1. Get the scripts of the databases that need to be compared. I still fall back to using the PHPMyAdmin SQL dumps, making sure to not export the AUTO_INCREMENT value.

  2. Run Workbench. Close any Physical Schemata, if any.

  3. Select Create New EER Model to open the MySQL Model view.

  4. Select File > Import > Reverse Engineer MySQL Create Script...
    Select the SQL script for the new database, and select Execute.
    When the populated database view is shown, you may need to select a table in the view. Occasionally, I've found that I don't end up with the diff that I'm after. The 'default_schema' schema, generated by this import ends up getting dropped, and replaced with a 'new_schema' or 'my_schema' schema, created by the next step.

  5. Select File > Export > Forward Engineer SQL ALTER Script...
    Select the SQL script for the old database as the Input File.
    Leave the Output File blank, and select Next.

  6. After a couple of seconds, a script containing the differences will be created.
    Select Copy to Clipboard to copy this. Paste as a migration script.
    Select Cancel to close the window.

    If you don't have a proper diff, it is possible that an existing schema may have been opened. In this case, close Workbench, and start again.

  7. Remove the schema name from the CREATE and ALTER TABLE commands.
    Insert newlines before the ADD COLUMN clauses for readability.
    Double check any DROP INDEX clauses to make sure they are not accidentally being removed.
Even though I'm using CakePHP (only 1.3 in this particular instance), and Capistrano for deliveries, I'm not comfortable in handing over my schema migrations to an automated process that could be used in CakePHP and Capistrano.