Real data, talking to sqLite

Most modern browsers supports some form of local database engine. For webkit and mozilla based browser that local database is sqlite, which (behind the scene) is built into the actual browser program. Internet explorer does not support this api but have other mechanisms for local data manipulation, including the ability to store data as XML.

In short, there are several database standards, but the most known are: IndexDB and Web SQL database. Before we dig into the nitty gritty of these things, let’s familiarize ourselves with the landscape.

Security and size restrictions

Browser vendors are constantly challenged when it comes to security. Hackers have proven that even the slightest weakness in security can be exploited, often with costly consequences for the unsuspecting user. Since this is the case you are not allowed to simply access or create a database anywhere you want. When you create a database the file is kept in a secure section of the browser cache and is explicitly linked to your web applications url.

There is also the case of database size, which in most browsers is set to between 5 and 10 megabyte by default. If the user needs more data he or she will have to manually change this. While 5 megabytes sounds ridicules to a delphi programmer, who is used to handle gigabytes of data in a plethora of different database engines – the browser database api was not created to be your central database solution. It was created to allow you to cache data between your web-apps and your server.

Removing the restrictions using phonegap

The web apps you create with Smart Mobile can be wrapped with a system called phonegap. Phonegap takes you generated html, javascript and css files (and any other files connected to your html) and injects it into a real .exe file for both Android and iOS. This real .exe file creates it’s own browser instance, puts the browser in full-screen – and disables the security restrictions that public browsers imposes on you. Once wrapped, you can create persistent (non cached) databases of any size. Or at least without the 5 to 10 megabyte limitation.

Using a central server

In our typical scenario we have a RPC server which supports JSON (the format browsers use to send data, a bit like XML only more compact) messages. We like to use remobjects remoting framework, but you should be able to use the standard delphi webservice components to achieve the same thing – or indeed the excellent delphi on rails toolkit by Henri Gourvest.

So on our server we would expose an interface that external client apps (web or native) can call to extract data.  Below is a hypothetical variant of such an interface. Note: this is just an example, there are must better ways of handling remote databases, but we have to start somewhere.

type
  IMyDataService = interface
    function  GetTopId: Int64;
    function  GetCountBetween: Int64;
    function  ReadRowsFromTop(const aCount: Integer):String;
    function  ReadRowsFromOffset(const aRecId: Int64):String;
    procedure UpdateRows(const aRowData: String; const aAppend: Boolean);
  end;

This would be the bare minimum you could use to mirror a small news-feed between a client and a mobile app. The functions would return the database rows in JSon format. Now lets go through a possible chain of communication between the server and your mobile app.

When you mobile application starts, it will call getTopId() to get the current topmost record-id from the database server. It will then compare the number it get back to it’s own “last known id”. If they are different, you know more news have been added on the server and that your local database needs to be updated.

If you want to know the exact number of records that have been added since your last update, you can call getCountBetween(), sending in the record-id of your last record. The server would return the number of rows added to the database since that number was “first”.

Based on this information, your web app would check that the amount of new records dont exceed the storage capacity of the database and ultimately call either readRowsFromTop() or readRowsFromOffset() if you want a spesific data range.

Again I must stress that this is not a very efficient way of doing things, it serves as an example of how you could sync a client with a server, nothing more.

IndexDB

Note: As of writing Smart 1.0 has just been released. It provides the foundation that we are going to build on, but sadly there was not enough time to add pre-made database components to this initial release. We have already started working on a TClientDataset class that will serve as the middle-man between cached updates from a server – and your local sqlite engine inside the browser. But as of writing the API is not ready.

In order to create a database we have to resort to some assembly code, which means javascript in our case. Creating a local database is quite easy. But handling javascript’s async nature in a linear way is not easy.

function w3_getIndexDB:THandle;
Begin
  result:=null;
  asm
   if (window.indexedDB) return window.indexedDB;
   if (window.webkitIndexedDB) return window.webkitIndexedDB;
   if (window.mozIndexedDB) return window.mozIndexedDB;
   if (window.msIndexedDB) return window.msIndexedDB;
  end;
end;

// Returns the request object
function w3_openIndexDB(const adbName:String):THandle;
var
 mEngine: THandle;
Begin
  mEngine:=getIndexDB;
  if (mEngine) then
  Begin
    result:=mEngine.open(adbName);
  end else
  result:=null;
end;

Now comes the tricky part, because we are really dealing with an objective database and not a sequential database. This means that each record is associated with a key and a datastore (which functions as a table). Since javascript is async, every operation we perform needs more infrastructure as well. So here is how you could open up a database and handle events:

var
  myDb:THandle;

begin
  myDB:=w3_openIndexDB('mydb');
  if (myDB) then
  begin
    myDb.onerror:=procedure (event:variant)
      begin
        //catch errors here
      end;
    myDB.onsuccess:=procedure (event:variant)
      begin
        //handle success codes here
      end;
    myDB.onupgradeneeded:=procedure (event:variant)
      begin
        //alter schema here, create tables (datastores) and setup key generators
      end;
  end;
end;

More to come soon! We are currently busy turning these things into proper objects so you don’t have to deal with this low-level stuff