Grid + Gears = ?

Translated from original posted on www.monoxrom.net

I’ve been trying to have a look at Google Gears in action, but always had some other fish to fry. You can hardly expect a user to agree on implementing some off-site plug-in developing a simple web-page. But if it is about developing for the Intranet, it doesn’t come out to be anything like a serious problem.

Still less chance a user has to implement an off-site plug-in in the Intranet. But if the boss wants it, so “keep quiet and drive” :)

Initially we have: a file with tabular data (an Excel file, in practice).
Required output: a changes-friendly interface for a remote user.

The most trivial solution – to use ready-made online services (for example, Google Spreadsheet) – doesn’t meet the case, unfortunately (authorization is required, and it is laced with some (un)healthy paranoia).
The second simple approach is using one of ready-made grid-like solutions, to which Google Gears can be attached for additional comfort.

Step One – Toolkit Preparation

Let’s reject purely commercial components at once. They also have their own niche in real life but in this case their usage is not reasonable.

There are three good solutions among open-source components: extJS Grid, dhtmlxGrid, DOJO Grid (the first two of them are produced under double licenses: GPL | Commercial, the third one has BSD-like license).

ExtJS – All other things being equal, it has the largest amount of necessary files and some performance problems working with huge datasets.

The unclear situation with its licensing policy reduces the desire of using it (the moral aspect of project development under GPL which further on was shifted to a tougher license is on developer’s head).
Let’s leave making a smaller build for people who have more spare time.

DOJO grid – It seems to be ok, but there’s no certainty about it due to absence of documentation.

dhtmlxGrid – the amount of code is relatively small, its examples and documentation, and, the main thing, working speed make this component our best card.

We’ll take it, wrap up two, please.

We use a highly classified Excel file as a data source (in further examples, this highly classified Excel file will be replaced by an unclassified one obtained with the help of freebase.com – but it was deprecated).
Let’s convert it into CVS to simplify further development.

I adore office packets: you save the file as “data with a separator , “, and get “data with a separator ; ” – such a slight misunderstanding. But saving was done, at least it’s something.

Thus, settled: dhtmlxGrid + data

Step Two – Data Loading

When we have the required toolkit in hand, we can get down to practice.
As a first step we render grid and populate it with data – prototype.

The initialization code is relatively simple:

1. //create a grid object

2.  grid =  new dhtmlXGridObject(‘for_grid’);

3. //set columns names
   

4.  grid.setHeader(“Name,Manufacturer,Format,Horizontal Resolution,Vertical Resolution,Digital zoom,Compressed Formats,Viewfinder type,LCD resolution,Weight (g),Width,Height,Depth,Also known as,Web Link”);

5. //set columns width

6.  grid.setInitWidths(“300,100,100,100,100,100,100,100,100,100,
   100,100,100,200,300″)

7. //render grid

8. grid.init()

9. /*inform grid that input data has ready-made IDs and there’s no need to worry about them

10. ready-made IDs are not typical for CSV|Excel – but we’re lucky at this point*/

11.  grid.enableCSVAutoID(false)

12. //set data separator in CSV

13.  grid.csv.cell=“;”

14. //make grid a little more attractive

15.  grid.setSkin(“modern”)

16. //load data

17.  grid.load(“data.csv”,“csv”);

It’s not that the amount of code is small, but there is no useless commands – everything is to the point. Grid is being rendered, data is being loaded, default settings allow to edit it, but everything is done not so quickly.

Initially a file with 1000 lines was used in tests. In the example above it was replaced by a file with 200 lines – using a 300kb file in a post is not very humane :) – that’s why the difference in loading speed is not so notable. Believe my word, the difference will be much more visible in case of 1000 lines.

Now that it can be fixed pretty easily by adding one command to the initializing code:

Code (javascript)

1. grid.enableSmartRendering(true)

And we get the same example, but the data is now being rendered nearly instantly – prototype

At a closer look it’s obvious that only two lines were changed in the code, one more JS file was added in addition to the command itself.
In case of dhtmlxgrid – more functionality means more included files, in contrast with extJs’s approach which initially includes everything in the core.

Step Three – Saving Changes

We have data, it can be edited, there’s only one little thing left – to save it.

In theory there’s a possibility in dhtmlxgrid to integrate with server code, but this opportunity is included only into the PRO version. That’s why it’s time for a workaround.

In order not to complicate the thing that is already simple, we add a “Save” button, a click on which transforms the grid back to CSV and sends it to the server:

Code (javascript)

1. function save_data(){

2.   document.forms[0].data.value=grid.serializeToCSV();

3.   document.forms[0].submit();

4. }

At the same time we add a couple of code lines to make the changed data noticeable:

 Code (javascript)

1.  grid.attachEvent(“onEditCell”,function(stage,id,ind){ //in case of editing

2.   if (stage==2) // when it is finished

3.    grid.setRowTextStyle(id,“font-weight:bold;”); // mark the line

4.    return true;

5.  })

Everything is simpler on the server side:

Code (php)

1. if (isset($_POST[“data”]))

2.  file_put_contents(“data.csv”,$_POST[“data”]);

Authorization code is omitted.
Naturally, such approach of overwriting the data source is too straight and doesn’t have right to exist in real life. But why not at the stage of prototype?

With all these changes introduced we have the next version of the prototype that can load data, allows to edit data and saves the result on the server (it took me much more time to describe the process rather than to create the prototype).

Step Four – Google Gears Enters the Game

Now when everything works, we can think of some useful ingredients.
Data saving is one of the things that seem obvious. In my personal experience, data loss is the nastiest thing that may happen (network connection loss, program error, some accidentally pressed button – losing is much easier than creating).

In case of using Google Gear, you can store all the information on client’s side. Then if a page is reloaded or even closed, the data won’t be lost. During your next visit the data can be restored out of the local storage.

Unlike grid, Gears’s initialization and configuring are troublesome.
As a first step we need to install Gears and create a database.

Code (javascript)

1.  

2.  //check the availability of Google Gears; if it is not installed, everything will operate without any changes

3.  if (!window.google) return alert(“The page requires Google Gears \n It will work without it as well, but you will not be able to work offline.”);

4.  //if Google Gears is installed, we create a local database

5.  db = google.gears.factory.create(‘beta.database’);

6.  

7.  //check database creation. Gears requests user’s permission and the user can well deny the operation

8.  if (db) {

9.  //create the required table, we set all the fields as varchar(255) to avoid any mess

10.  //a table field is created for each column in grid, plus one additional field – updated – that will save the status of data lines
    

11.  db.open(‘gearsgrid’);

12.   db.execute(‘create table if not exists photodata (pkID varchar(255), Name varchar(255), Manufacturer varchar(255), Format varchar(255), Horizontal varchar(255), Vertical varchar(255), Zoom varchar(255), Compressed varchar(255), Viewfinder varchar(255), LCD varchar(255), Weight varchar(255), Width varchar(255), Height varchar(255), Depth varchar(255), Known varchar(255), Link varchar(255), updated int)’);

13.  

14.  //check whether there is data in the database or it is the first loading

15.  var rs = db.execute(’select count(*) from photodata’);

16.  //if the data is not loaded yet

17.  if (rs.field(0)==0){

18.   //create the query and get the data

19.   var request = google.gears.factory.create(‘beta.httprequest’);

20.    request.open(‘GET’, ‘data.csv?uid=’+(new Date()).valueOf());

21.    request.onreadystatechange = function() {

22.     if (request.readyState == 4){

23.     //split the derived CSV file into lines

24.      var data=request.responseText.split(“\n“);

25.      for (var i=0; i < data.length; i++){

26.      //split the lines into columns

27.      var fields=data[i].split(“;”)

28.      //set the reloading flag to 0
    

29.      fields.push(0)

30.      //save in the local database

31.      db.execute(“insert into photodata values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”,fields);

32.      };

33.     //invoke the function of grid filling

34. 
    

35.      fill_grid();

36.     }

37.    };

38.    request.send();

39.  }

40.  rs.close();

41.  }

If you don’t receive any data working with the object “request”, check the character encoding in the loaded document. If it is not Us-ASCII | Unicode, “request” will return null – such a nice peculiarity.

As promised – this code fragment came out to be fairly big, but it is not complicated. There’s just a lot of text in it.

The Gears loaded the data into the local database, and now the grid can be populated with this data.

Code (javascript)

1.  

2. function fill_grid(){

3.  if (db){        //if there is Google Gears

4.   var data=[];

5.   //choose the data

6.   var rs=db.execute(“select * from photodata”);

7.    //form the array of data for grid

8.   while (rs.isValidRow()) {

9.   data.push({

10.   id:rs.field(0),

11.    data:[rs.field(1),rs.field(2),rs.field(3),rs.field(4),rs.field(5
    ),rs.field(6),rs.field(7),rs.field(8),rs.field(9),rs.field(10
    ),rs.field(11),rs.field(12),rs.field(13),rs.field(14),rs.field(15)]});

12.   rs.next();

13.   }

14.  rs.close();

15.  //load the data into grid
    

16.  grid.clearAll();

17.  grid.parse({rows:data},“json”)

18.  } else

19. //if there’s no Google Gears, load the data right from the server

20.  grid.load(“data.csv?uid=”+grid.uid(),“csv”);

21. }

22.  

Two examples described above allow us proxying the data through Google Gears. But there’s no other particular use of this, except for some traffic saving (when the second loading takes place, the data will be loaded from the local data store). Though if you load huge datasets, the saving can be substantial.

There is one step left to make Gears really wholesome:

Code (javascript)

1.  

2.  grid.attachEvent(“onEditCell”,function(stage,id,ind,value){

3.    if (stage==2){

4.     grid.setRowTextStyle(id,“font-weight:bold;”);

5.     //if there is Google Gears

6.     if (db) //save a new cell value in the local data store

7.     db.execute(“update photodata set “+columns[ind]+“=?, updated=1 where pkID=?”,[value,id]);

8.  }

9.  return true;

10. })

11.  

The result is available here.

If GoogleGears is not installed – everything continues to work as it was with the previous examples.
If GoogleGears is installed, everything gets rather interesting: edit the cell and press F5. When the page is reloaded, new edited data will be presented in grid. You might just as well close the browser window and visit the same page using some other browser – edited data will still be there. Whatever the software and the network connection troubles are, there’s no way to lose your data!

Step Five – Slight Improvements

One thing left in order to finish up with the practice – to spruce up.

Let’s start with adding a visual mark for edited lines. The field “Updated” has already been set in such a way that we need only to introduce slight changes into the data loading code to make changed data from the local data store look like newly edited one.

Code (javascript)

1. 
   

2.  //style is used to mark already edited lines

3.  data.push({

4.   id:rs.field(0),

5.    style:rs.field(16)?‘font-weight:bold;’:”,

6.    data:[rs.field(1),rs.field(2),rs.field(3),rs.field(4),rs.field(5
   ),rs.field(6),rs.field(7),rs.field(8),rs.field(9),rs.field(10
   ),rs.field(11),rs.field(12),rs.field(13),rs.field(14),rs.field(15)]})

7.  

This point turned out to be quite time consuming in spite of all its simplicity. As it turned out, the basic version of grid can’t load styles from json data. This was healed with the help of a patch found at the support forum. But the whole affair left a bitter taste in my mouth.

When the grid is saved in the database, it would be a good thing to clear the data store.

Code (javascript)

1.  

2. function save_data(){

3.  //if there is Google Gears

4.   if (db)

5.    //clear the table we used – a disputable method, but in this case it’s effective

6.    db.execute(‘drop table photodata’)

7.   document.forms[0].data.value=grid.serializeToCSV();

8.   document.forms[0].submit();

9. }

You can see the result here.

Edited lines still remain visually different when the page is reloaded, thus it’s easy to notice the changes.
Full page reloading and data store clearing take place while the page is being saved.
Everything works from the technical point of view. As the last step we can add some free useful things such as sorting and filtering. Both the possibilities are standard for grid and don’t require any voodoo programming.

Code (javascript)

1.  

2. grid.setColSorting(“str,str,str,int,int,int,str,str,int,int,int,int,int,
   str,na”);

3. grid.attachHeader(“#rspan,#select_filter,#select_filter,#rspan,
   #rspan,#rspan,#rspan,#rspan,#rspan,#rspan,#rspan,#rspan,
   #rspan,#rspan,#rspan”);

4.  

The final result is available here.

The Step Not Present, or What Can Be Improved

The client side code works pretty well. Of course, we could make local data store origination more universal, add input data validation based on the current grid structure, etc. But it can be left as it is so far.

The server code is terribly simple. Choosing a single-piece CSV as the type of data transfer and saving may result in the ultimate simplification of operations, but the price for that is loss of flexibility. In theory it is possible to replace the current saving scheme with something more elegant, allowing to use simultaneous access to data and smarter synchronization (since Gears was meant to be used in such scripts). But this will result in loss of code simplicity. So, maybe next time.

The usage of local data store significantly improved usability with a relatively small amount of code needed. Taking into account the fact that most browsers are advertised as powered with local data stores, such a solution might be implemented without any outside plugin in the offing.