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
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.
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.
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;
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