Gridnite part 3: saving and restoring multiple spreadsheets

By Slava Vishnyakov

Introduction

Our Gridnite collaborative spreadsheet application is coming along well — in the last two articles we did the following: Part 1: implementing the basic spreadsheet functionality, Part 2: adding formulas. Our next big steps in developing the application are the development of routines to work with files, namely saving and restoring spreadsheets and exporting them to some common spreadsheet format. Currently Gridnite works in a single file, spreadsheet.json, which is loaded via JSON_file to the db variable. This means that we can’t really work on separate files right now — if we were to implement downloading/uploading right away, we would replace the spreadsheet.json file with new contents upon restoring, and everyone who collaborated with us would have all their work destroyed (the contents would be replaced with what was just uploaded).

Lucky for us, there isn’t that much to change to achieve the desired effect. In this article we’ll show you how to implement the save/restore functionality, and do some general refactoring to keep the application more maintainable. The most up-to-date Gridnite application looks like Figure 1.

Figure 1: Gridnite 1.2 with multiple spreadsheet functionality.

You can download the third version of the Gridnite application, try it out, and play with the code as you read the article.

Contents:

• Refactoring
• Multiple files
  • Changing db to db_handles(filename)
  • Security
  • Adding a feature menu
  • Dialog boxes and Impromptu
  • Switching to a saved file
  • UTF-8
  • AA1 cells
  • Summary

Refactoring

The code is growing bigger. To begin with it was okay to keep it in index.html, but it’s starting to becoming unmanageable. There are large parts of the code that are currently stable and don’t require any changes at all, but they are getting in the way of some of the dynamically changing parts. It therefore makes sense for us to split this big file into smaller ones at this point.

We’ve cut out parts of the index.html file that are related to basic things developed in Part 1: implementing the basic spreadsheet functionality and moved them to /js/clientside_base.js. The parts that are related to formula editing have been moved to /js/clientside_formula.js. These parts are now called into the main document using <script src={{base}}js/clientside_base.js></script> calls.

The code is now much cleaner and easier to understand. There are 3 <SCRIPT> calls, which is ok for now, although in the future we would like to create a procedure to join all those files together and have them sent to the client’s browser as a single JavaScript file. This would improve things because each call takes time (latency) and it can become quite annoying to wait for all those scripts to load.

Why didn’t we split the script into smaller parts from beginning? Well, remember the KISS principle — Keep it simple, stupid. Splitting a file with less than 100 lines of code would have been unnecessarily complicated.

The only JavaScript left inside the index.html file is as follows:

var current_page__ =
{{current_page}};
var uri_base = {{base}};
var current_time =
{{current_time}};
var time_difference = (new
Date()).getTime()/1000 - {{current_time}};

The templating codes — eg {{...}} — are provided by the Markuper library, so these are tied to index.html (the only file parsed by Markuper). We also had to update the Markuper call to include current_page: safe_js_val(current_page(r)), as a substitution variable (more on this below).

Multiple files

Let’s start with some planning. While working on Gridnite Part 1: implementing the basic spreadsheet functionality we thought that at some point we would need the system to work on different files, so we even reserved a variable — current_page — for this. The original plan was to use jQuery’s Cookie plugin and store the filename currently open by the visitor (client) in a cookie. We implemented this but then realized users might want to have a few windows open with a different spreadsheet in each one (like you can have multiple spreadsheets open in any other similar application). But the cookie meant that there could be only one document open at a time, so another approach was needed.

In actual fact, it is worse than this. If you use this approach and then open another document in a different window, the cookie is updated and you end up with updates sent from Spreadsheet 2 to Spreadsheet 1, which is confusing and just plain wrong.

Just in case you’re wondering how to deal with Cookies in Opera Unite, you can modify jQuery’s Cookie plugin easily to deal with Opera Unite’s infrastructure.

So, the correct approach to deal with this is to send the relevant JSON filename in each of the AJAX requests to the server, and in the initial request too:

http://.../ajax_request?param=value&filename=file1.json

The next question is how are we going to deal with the fact that we have only one db variable (ie one file to store the data)?. The answer is that we’re going to replace the db variable with the db_handles(filename) call; this returns the handle of the file(caching it for each successive call).

Changing db to db_handles(filename)

Previously we had this call:

db = new JSON_FILE('spreadsheet.json', {
  values:{ },
  updates:[],
});

This served us well for one file (spreadsheet.json), but as noted above we’re going to replace it with the db_handles(filename) call so that we can handle any number of files. We could do it as simply as this:

function
db_handles(filename) {
  return new JSON_FILE(filename, {
    values:{ },
    updates:[],
  });
};

But to make it play nicely with a filesystem we’re going to cache the file handle returned by JSON_FILE for successive calls:

db_handles = {};
function
db_handle(db_name) {
  if(!db_handles[db_name])
  {
    db_handles[db_name] = new JSON_FILE(db_name, {
      values:{  },
      updates:[  ],
    });
  };
  return
  db_handles[db_name];
};

This avoids creating a multitude of open files, which would probably be dealt with by JavaScript’s garbage collector, but nevertheless it’s more efficient to not have to open a file for every action.

Now we need to search through all our files for calls to the db variable (usually called via db.store) and replace them with db(filename).store. But what is the filename? It’s the GET variable we talked about earlier that we’re going to send in each AJAX request. We could just use db_handles(r.GET.filename) call (r.GET is the Uniteness framework shortcut for r.connection.request.queryItems['filename'][0]), but we’re going to have this in a lot of places. What if we need to change it later? Since we’re not sure that we will always use the GET variable (?filename=...) to deliver the filename, it’s a good idea to create a function that will be called in each db_handles(...) call, instead of hard-writing r.GET.filename every time. Let’s call this function current_page():

function
current_page(r) {
  return
  UTF8.decode( r.GET.filename ? r.GET.filename : 'spreadsheet.json' );
};

So, if we have a filename variable we return it, otherwise we return the default spreadsheet.json file. The r varible holds the WebServerRequestEvent object (we’ll talk more about UTF-8 decoding in a little while).

Next we look though our .js files, replace db.store with db_handles(current_page(r)).store, and we’re done with this part of the code modification.

The last thing to do in this section is to modify the AJAX calls to include the filename. Remember that we have two types of AJAX calls — one is for setting the cell value and the other (done every 5 seconds) is to retrieve the latest updates made to the spreadsheet by other users. So, we just need to add +'&filename='+encodeURIComponent(current_page()) to each call. We haven’t written the current_page() function yet — this is executed in the user’s browser, not as server-side Opera Unite JavaScript). This function is really simple:

function current_page(page) {
  return current_page__;
};

If we need to change where current_page is stored later on, this function allows us to just change it in one place.

Security

Remember that you should never trust data supplied by any user. What if they supply us with a filename like `cat /etc/passwd` on Linux or c:\windows\hosts on Windows (or some other malicious files or commands?) Luckily for us, Opera Unite’s sandboxing of the filesystem is done really well, so it is hard to insert malicious code into our applications. Ideally we should check what the user supplied to us and limit it, but Opera have done a great job, so we don’t really need to do that. We just pass the filename as it was sent to Opera Unite’s JavaScript.

Adding a feature menu

We also need to add a menu to allow our users to access functions such as New Spreadsheet and Switch to other Spreadsheet. Right now those will just be two links (keep it simple!), but at some point in future, when the menu grows big enough, we’ll refactor the code again and add proper drop-down menus (similar to the Opera 10 Operating system UI showcase).

Now we’re going to add a simple <DIV CLASS=menu_items> to index.html to hold the menu and then use jQuery to add the menu items.

$(document).ready(function()
{
  $('<span>File:
  <b id=filename><a href=#
  onClick=files_switch(); return false;>'
  +current_page()+ '</a></strong></span> | ' +
  '<a href=#
  onClick=files_new(); return false;>New spreadsheet</a>
  ' +
  '<a href=#
  onClick=files_switch(); return false;>Switch to
  another file</a> ' +
  '').appendTo('.menu_items');
});

This creates a status line containing the current open file and two links to functions that display dialogs. Clicking on the file name has the same effect as clicking the Switch to another file link. That’s just a little usability quirk. The user might be thinking Is this where I enter the filename I want? or Where’s the button to open another file?. Since it’s only 20 more characters to type for us, it is a no-brainer to add this.

Next we want to create the dialog boxes containing the controls for the functions accessed by clicking the two links mentioned above. We could just create a pop-up. I know, I know … I’m just joking. Pop-ups are annoying and blocked by most browsers. But we do need a dialog nevertheless. We’ll use the awesome Impromptu plugin for jQuery.

Dialog boxes and Impromptu

Impromptu is a really great piece of software for creating dialogs. You just supply HTML, some callbacks and few parameters, and it creates a nice-looking dialog box that even plays well with other jQuery plugins. It’s as simple as $.prompt('Have a nice <strong>day</strong>!').

Let’s start with a New File dialog. It’s going to be a simple imitation of the prompt(...) call that sends the user to a new location when OK is clicked: http://.../gridnite/?filename=new_file_name). We could use the usual prompt(...), but we would need to have other complex dialog boxes as well and it’s a good idea to keep the interface consistent, therefore we’re going to deliberately over-complicate things and use Impromptu for this too.

$.prompt (
  'New file
  name:<br><input type=text id=alertName
  value=>', {
    submit: cb,
    buttons: {
      Ok:true
    },
    persistent: false
  }
);
$('#alertName').focus();

submit: cb is a callback to the cb(v,m,f) function (more details below), persistent: false means that the user can click anywhere around the dialog box to make it go away, and the very last line gives the text-field focus, to aid with keyboard accessibility.

If the user clicks OK, the cb function is called:

function cb(v,m,f) {
  an =
  m.children('#alertName');
  if(!an.attr('value')) {
    an.css(border, solid
    #ff0000 1px);
    return false;
  };
  var fn =
  an.attr('value');
  if(!fn.match(/\./))
  {
    fn += '.json';
  };
  switch_page(fn);
  return true;
};

This is just basic jQuery stuff — we get a reference to the element with the ID of alertName — <INPUT>. Then we check if it contains a value — if not we put a red border around it and don’t close the dialog (using return false). We check the value for . (dot) symbol and if it has no value we add .json to the end. Then we call switch_page(...) with the filename we just specified as its argument.

function switch_page(page) {
  document.location =
  uri_base + '?filename='+encodeURIComponent(page);
};

Switching to a saved file

This requires a server-side component. The client-side is fairly simple:

function files_switch() {
  $.getJSON(uri_base
  + 'list_files?json=1', function(files_list) {
    files_list_html =
    '';
    for(var i=0;
    i<files_list.length; i++) {
      files_list_html
      += '<a href=# onClick=switch_page(this.text); return
      false;>' + files_list + '</a>';
    };

    $.prompt(
      'Open
      file:<br><div class=file_list>' + files_list_html +
      '</div>',
      {
        buttons:
        {
          Ok:true, Cancel:false
        },
        persistent: false
      }
    );
  });
};

We use an AJAX request to http://..../gridnite/list_file?json=1 to return the list of spreadsheet files on server as a JSON object (array in this case). Then we just create a list of links to those files with onClick=switch_page(this.text); return false;. To make it nicer and more OS-like, we’re going to add icons to links from the great FamFamFam Silk collection.

The server-side is a little more complicated. To iterate through the spreadsheet files on the server and return them as a JSON object, we do this:

function glob(dir_name) {
  try {
    var dir =
    opera.io.filesystem.mountSystemDirectory(dir_name);
    var ret = [];
    dir.refresh();
    //Load the contents of the directory
    for(var
    i=0,file;file=dir;i++) {
      if(file.isFile) {
        ret.push(file.path.replace(/^\/[^\/]*?\//, ''));
      };
    };
    return ret;
  }

  catch(err) {
    return [];
  };
};

function list_files(r) {
r.write(JSON.stringify(glob('storage')));
};

The glob function is a modification of an example from the fileio documentation. We open the directory and iterate through it, pushing elements to the array, replacing /storage/ or whatever the directory is from the filename, then returning the array. Then we JSONify it and return it to the user, where the Impromptu-generated dialog is shown.

UTF-8

AA1 cells

Another problem that we decided to deal with straight away was the small number of rows in Gridnite — at some point we’re going to make it dynamically extensible, but for now we’ve just added more of them. There was a hidden problem with it however — while all of Part 2: adding formulas’s regular expressions expected cell names such as A1 or B34, none of them expected AA1. So we’ve had to modify the regexps from the clientside_formula function from [A-Z][0-9]{2,3} to [A-Z]{1,2}[0-9]{2,3}.

Summary

This rounds up our tour of the third lot of new functionality to be added to Gridnite — saving and restoring multiple files. We hope you have found it useful and interesting.

This article is licensed under a Creative Commons Attribution, Non Commercial - Share Alike 2.5 license.