SQLite + Smart Mobile Studio = True

In case you missed the great moment, here is a link to my previous post on the subject.

In short: Someone has ported the engine SQLite database engine to JavaScript. This means that you can now create, query, save, load and otherwise do whatever you wish without relying on the browser to provide you with a native DB API. This means that if you desire SQL database support in your mobile applications then Smart Mobile Studio now provides that out of the box!

I must mention though that we also provide our own lightweight TW3Dataset class, which for known table structures is much more efficient and faster to work with. But TW3Dataset does not have SQL support and searching for records must be done “the old way”. The SQLite engine applies internal indexes to make searching for data fast and simple, and you get to use SQL which can simplify complex expressions.

Libraries

The SQLite implementation is pure JavaScript and will be stored in the libraries folder. When you include the sqlite unit in your projects, this library is automatically included – and the file copied to your /res/ folder.

To initialize the SQLite driver you simply do like this:

procedure TForm1.InitializeForm;
begin
  inherited;
  w3Button1.enabled:=False;
  w3_RequestAnimationFrame( procedure ()
    begin
      SyncDBStart;
    end);
end;

Procedure TForm1.SyncDBStart;
begin
  if CheckDBEngine then
  begin
    FDB:=TSQLiteDatabase.Create;
    w3button1.enabled:=True;
  end else
  w3_RequestAnimationFrame(SyncDBStart);
end;

As you can see from this code, I disable the GUI while the database engine is loading. I use an async-loop to check if the driver has initialized, and only when true do I re-enable the GUI and create an instance of the database.

You may want to modify your application unit and make sure the driver is loaded before you show a form, or perhaps show a fancy CSS3 dialog with “Loading, please wait” until all your resources are in place? The new system.fileutils.pas unit should give you some inspiration there 😉

Excellent performance and great storage use

Well, here is a picture of the testbed app. Notice the bytesize of a SQLite database with 10.000 inserted records (!)
This means that you can easily stuff thousands of records into a database stored in LocalStorage (max 5 megabytes)! Perfect for lazy updates where you upload the data to your server when the device is connected to the internet.

Enjoy!