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 😉
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!
CheckDBEngine? How do you initialize the SQLite driver?
ahhh, here is the code with some modification
procedure TForm1.InitializeForm;
begin
inherited;
// this is a good place to initialize components
w3Button1.enabled:=False;
TW3Dispatch.RequestAnimationFrame( procedure ()
begin
SyncDBStart;
end);
end;
procedure TForm1.SyncDBStart;
begin
if SQLiteReady then
begin
FDB:=TSQLiteDatabase.Create;
w3button1.enabled:=True;
end else
TW3Dispatch.RequestAnimationFrame(SyncDBStart);
end;