Jump to content
IElite

Database Examples

Recommended Posts

I know the team is busy, but.....

I am still waiting for a decent example of how to create a database on a mobile device. Add, Edit, and Delete records, and make the data persistent (i.e. save and load data to and from the device and app.

I have tried WebSQL, sqlite, and TW3Dataset  with no luck.

It would be nice to see a REAL example of all three of these, but the best method for having a database on a mobile device would work fine for me.

is anything like this in the works in the IMMEDIATE future?

 

Share this post


Link to post
Share on other sites

Here is a dogs breakfast of internal data structures and conversions between them

  • step-1 set up and populate data structure (business unit with multiple departments with staff per department) 
  • step-2 manipulation in code
  • step-3 convert record structure to json text
  • step-4 define and populate TW3Dataset from json
  • step-5 save json to local storage (persistent per device)
  • step-6 reload json from local storage
  • step-7 define and populate SQLite db from json
  • step-8 save json to server
  • step-9 reload json from server

At the end of the day all these structures can be converted to some kind of string (text, json, streamstring) and then saved to local storage. When running in the browser that is the only way to save data, which gives you device-persistence. Persistence of data over multiple devices is only possible by storing data on a server.

 

unit Form1;

interface

uses
  System.Types,
  System.Types.Convert,
  System.Objects,
  System.Time,
  SmartCL.System,
  SmartCL.Time,
  SmartCL.Graphics,
  SmartCL.Components,
  SmartCL.FileUtils,
  SmartCL.Forms,
  SmartCL.Fonts,
  SmartCL.Theme,
  SmartCL.Borders,
  SmartCL.Application, SmartCL.Controls.Button, ECMA.JSON, System.DataSet,
  SmartCL.Storage.Local, System.Sqlite, W3C.XMLHttpRequest, W3C.HTML5, W3C.DOM;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    {$I 'Form1:intf'}
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
    procedure Resize; override;
    Procedure DoStuff;
  end;

// record types
type
  JStrC = record
    firstname    : string;              //external 'firstname';
    surname      : string;              //external 'surname';
  end;

  JStrB = record
    deptname     : string;              //external 'deptname';
    staff        : Array of JStrC;      //external 'staff';
  end;

  JStrA = record
    business     : string;              //external 'business'
    departments  : Array of JStrB;      //external 'departments';
  end;


implementation

{ TForm1 }


Procedure TForm1.DoStuff;
begin
//
// step1 : set up a leveled structure, (either records or classes)
//         i.e. business unit with departments with employees per department
    writeln('Step1');
//

  //business unit
  var StrA01 : JStrA;
  StrA01.business    := 'lynkfs';

    //department marketing
    var StrB01 : JStrB;
    StrB01.deptname    := 'marketing';
      //staff Keith Ansell
      var StrC01 : JStrC;
      StrC01.firstname   := 'Claire';
      StrC01.surname     := 'Adams';
    StrB01.staff.Add(StrC01);
      //staff Charles Bailey
      var StrC02 : JStrC;
      StrC02.firstname   := 'Vicky';
      StrC02.surname     := 'Anderson';
    StrB01.staff.Add(StrC02);

  StrA01.departments.Add(StrB01);

    //department frontdesk
    var StrB02 : JStrB;
    StrB02.deptname    := 'front desk';
      //staff Keith Ansell
      var StrC03 : JStrC;
      StrC03.firstname   := 'Keith';
      StrC03.surname     := 'Ansell';
    StrB02.staff.Add(StrC03);
      //staff Charles Bailey
      var StrC04 : JStrC;
      StrC04.firstname   := 'Charles';
      StrC04.surname     := 'Bailey';
    StrB02.staff.Add(StrC04);

  StrA01.departments.Add(StrB02);

  // check
  writeln(inttostr(StrA01.departments.length) + ' departments');
  for var i := 0 to StrA01.departments.length -1 do begin
    writeln(StrA01.departments[i].deptname + ' ' + inttostr(StrA01.departments[i].staff.length) + ' staff');
    for var j := 0 to StrA01.departments[i].staff.length -1 do begin
      writeln(StrA01.departments[i].staff[j].firstname + ' ' +
              StrA01.departments[i].staff[j].surname);
    end;
  end;

//
// step2 : depending on business logic, add/delete/change data in these arrays at will
    writeln('Step2');
//

  // employee Claire Adams leaves
  StrA01.departments[0].staff.Delete(0);    //or coding to that effect

  // a new empty department is created
  var StrB : JStrB;
  StrB.deptname := 'new department';
  StrA01.departments.add(StrB);

  // check
  writeln(inttostr(StrA01.departments.length) + ' departments');
  for var i := 0 to StrA01.departments.length -1 do begin
    writeln(StrA01.departments[i].deptname + ' ' + inttostr(StrA01.departments[i].staff.length) + ' staff');
    for var j := 0 to StrA01.departments[i].staff.length -1 do begin
      writeln(StrA01.departments[i].staff[j].firstname + ' ' +
              StrA01.departments[i].staff[j].surname);
    end;
  end;

//
// step3 : Turn object to json
    writeln('Step3');
//
  var textdata: string := '';

  asm
    @textdata = JSON.stringify(@StrA01);
    console.log(@textdata);
//  {"business":"lynkfs","departments":[{"deptname":"marketing","staff":[{"firstname":"Vicky","surname":"Anderson"}]},{"deptname":"front desk","staff":[{"firstname":"Keith","surname":"Ansell"},{"firstname":"Charles","surname":"Bailey"}]},{"deptname":"new department","staff":[]}]}
  end;

//
// step4 : Define and populate TW3DataSet from json
    writeln('Step4');
//
  var DataSet := TW3DataSet.Create;      // note TW3Dataset is not leveled

  Dataset.FieldDefs.Add('business',ftString);
  Dataset.FieldDefs.Add('deptname',ftString);
  Dataset.FieldDefs.Add('firstname',ftString);
  Dataset.FieldDefs.Add('surname',ftString);

  DataSet.CreateDataset;

  var v := json.parse(textdata);

  For var i := 0 to v.departments.length -1 do begin
    For var j := 0 to v.departments[i].staff.length -1 do begin
      DataSet.Append;
      Dataset.Fields.FieldByName('business').AsString := v.business;
      Dataset.Fields.FieldByName('deptname').AsString := v.departments[i].deptname;
      Dataset.Fields.FieldByName('firstname').AsString := v.departments[i].staff[j].firstname;
      Dataset.Fields.FieldByName('surname').AsString   := v.departments[i].staff[j].surname;
      DataSet.Post;
    end;
    If v.departments[i].staff.length = 0 then begin     //empty department
      DataSet.Append;
      Dataset.Fields.FieldByName('business').AsString := v.business;
      Dataset.Fields.FieldByName('deptname').AsString := v.departments[i].deptname;
      DataSet.Post;
    end;
  end;

  // check
  DataSet.First;
  while not DataSet.Eof do
  begin
    writeln(inttostr(DataSet.recno+1) + ' ' +
            Dataset.Fields.FieldByName('business').AsString + ' ' +
            Dataset.Fields.FieldByName('deptname').AsString + ' ' +
            Dataset.Fields.FieldByName('firstname').AsString + ' ' +
            Dataset.Fields.FieldByName('surname').AsString);
    DataSet.Next;
  end;

//
// step5 : save json to local storage (persistent on specific device)
    writeln('Step5');     // step 5 only needs to be run once
//
  var LocalStorage := TW3LocalStorage.Create;

  LocalStorage.Open('mytreasure');
  LocalStorage.SetKeyStr('json',textdata);
  LocalStorage.Close;

//
// step6 : retrieve json from local storage (persistent on specific device)
    writeln('Step6');
//
  textdata := '';

  LocalStorage.Open('mytreasure');
  textdata := LocalStorage.GetKeyStr('json','unknown');
  LocalStorage.Close;

  var v1 := json.parse(textdata);

  // check
  writeln(inttostr(v1.departments.length) + ' departments');
  for var i := 0 to v1.departments.length -1 do begin
    writeln(v1.departments[i].deptname + ' ' + inttostr(v1.departments[i].staff.length) + ' staff');
    for var j := 0 to v1.departments[i].staff.length -1 do begin
      writeln(v1.departments[i].staff[j].firstname + ' ' +
              v1.departments[i].staff[j].surname);
    end;
  end;

//
// step7 : save json to SQLite
    writeln('Step7');
//

  var db   : TSQLiteDatabase;
  var res  : TSQLiteResult;

  db := TSQLiteDatabase.Create;
  db.Run('CREATE TABLE `business`    (id INTEGER, business TEXT)');
  db.Run('CREATE TABLE `departments` (id INTEGER, busid INTEGER, deptname TEXT)');
  db.Run('CREATE TABLE `staff`       (id INTEGER, deptid INTEGER, busid INTEGER, firstname TEXT, surname TEXT)');

  var v2 := json.parse(textdata);

  db.Run("INSERT INTO `business`(`id`,`business`) VALUES (0,'" + v2.business + "')");
  for var i := 0 to v2.departments.length -1 do begin
    db.Run("INSERT INTO `departments`(`id`,`busid`,`deptname`) VALUES (" +
         inttostr(i) + ",0,'" + v2.departments[i].deptname + "')");
    for var j := 0 to v2.departments[i].staff.length -1 do begin
      db.Run("INSERT INTO `staff`(`id`,`deptid`,`busid`,`firstname`,`surname`) VALUES (" +
           inttostr(j) + "," + inttostr(i) + ",0,'" + v2.departments[i].staff[j].firstname +
           "','" + v2.departments[i].staff[j].surname + "')");
    end;
  end;

//  check
//  res := db.Exec("SELECT a.business, b.deptname, c.firstname, c.surname " +
//                 "from business a, departments b, staff c " +
//                 "where c.busid = a.id and c.deptid = b.id and b.busid = a.id");

// or something a bit simpler
  res := db.Exec("SELECT surname FROM staff");

  //writeln(res.columns[0]);   //title of first field : 'business'

  var rows := res.values;
  for var i := 0 to rows.length -1 do begin
    var columns := rows[i].values;
    for var j := 0 to columns.length-1 do begin
      writeln(columns[j]);
    end;
  end;

// or alternatively, same result
//
//  for var i := 0 to res.values.length -1 do begin                   //rows
//    for var j := 0 to res.values[i].values.length-1 do begin        //columns
//      writeln(res.values[i].values[j]);                             //cell
//    end;
//  end;

/*
//
// step8 : save json to server (persistent over all devices)
    writeln('Step8');
//

  var s := "UPDATE `test` SET `webjson`='" + textdata + "'";
  var FHttp := JXMLHttpRequest.Create;
  FHttp.open("POST",'...../smsdmlmysql.php');
  FHttp.setRequestHeader("Content-type","application/x-www-form-urlencoded");
  var encodedstr1 := browserapi.window.encodeURIComponent(S);
  var sql_statement := 'sql_statement=' + encodedstr1;
  FHttp.send(sql_statement);

//
// step9 : retrieve json from server
    writeln('Step9');
//

  s := "select * from test";
  var FHttp2 := JXMLHttpRequest.Create;
  FHttp2.open("POST",'...../smsdbmysql.php');
  FHttp2.setRequestHeader("Content-type","application/x-www-form-urlencoded");
  var encodedstr2 := browserapi.window.encodeURIComponent(S);
  var sql_statement2 := 'sql_statement=' + encodedstr2;
  FHttp2.send(sql_statement2);

  JGlobalEventHandlers(FHttp2).onLoad := lambda(e:JEvent)
    var smscursor := JSON.parse(FHttp2.responseText);
    for var i := 0 to smscursor.rows.length -1 do begin
      textdata := smscursor.rows[i].webjson;
    end;
    writeln(textdata);
    result :=true;
  end;
*/
end;

procedure TForm1.W3Button1Click(Sender: TObject);
begin
  DoStuff;
end;

procedure TForm1.InitializeForm;
begin
  inherited;
  // this is a good place to initialize components
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
end;

procedure TForm1.Resize;
begin
  inherited;
end;

initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.

 

Share this post


Link to post
Share on other sites

Sorry for the delay we are working non-stop on the next update, so its hectic!
First, let me explain a bit where we are, so we can pick the right storage mechanism:

The past months we have focused solely on the visual aspect of the RTL. We had to start somewhere and since the visual aspect is what most people care about first, that seemed like the best place. So as you have no doubt seen, we have gone through the RTL with a fine tooth comb, removing things that didnt work, rewriting controls from scratch and testing each part to make sure that everything works.

But that is just 1/3 of the path we are on. We are now moving into the non-visual aspects of the RTL. Here we have things like databases, non-visual controls (TComponent in Delphi and Lazarus), filesystems and ultimately node.js - which by nature is UI less and runs on the server (or the shell).

We have also brushed up Phonegap and removed the wrongs we found there. The Phonegap API has not stood still and have evolved, so it was due for an overhaul. So that is a quick "status". We are completing 1/3 of the planned system, which covers the visual aspects. DB typically falls under "non-visual", with bindings back to visual later.

DB for browsers, what is what?

Modern browsers actually have 3 DB engines built into them:

  • WebSQL
  • IndexDB
  • Microsoft Access (*)

(*) Only supported by Microsoft and relies on COM (so not for mobile).

It must be mentioned that WebSQL, which is the only really good option here, has been deprecated. But it wont vanish tomorrow. The W3C (world wide web consortium) is a bit like the vatican; they move in decades and code will linger in the codebase for ages before it changes. So I wouldnt worry too much about using WebSQL.

Secondly, there are shim's for everything these days. A "shim" is a JS re-implementation of something. What a shim does is check if a feature is supported, and if it's not then the shim installs itself and delivers the missing functionality. In other words, you can safely use websql and just download a shim if suddenly google or firefox lacks it.
Here is a nice shim for websql: https://github.com/agershun/WebSQLShim

Then there is IndexDB. This is a no-sql database and is designed to store JS structures (or objects) in a typical name-value pair style. I think JS people like this a lot since they can easily stuff raw JS objects into it, using a string key. And while it does have its uses, its not a table/row/column solutions.

And finally there is access. Which is a pure Microsoft solution, only runs in Edge or Internet Explorer and requires the onslaught of COM.
This might have changed in the later versions of edge but either way - this is Microsoft only. So I doubt that is interesting.

Thinking outside the box, Smart approach

Facing this typical JavaScript mess (sorry but it's true, the browser wars were not kind to JS) I decided we needed to become independent.

Browsers change all the time and JS have little standards to speak of compared to Delphi, C# or C++; So coming from Delphi JS will be hell to work with unless you do a long and serious study. Our role is in many ways to "tame" JS and force it to behave inside object pascal's rules. So we needed some structure, something that only change when we allow it! So that people can write programs without being afraid that it will vanish in six months.

So we created two solutions:

  • TW3Dataset for simple, single table work
  • SQLite compiled from C to JavaScript

Note: Some bugs sadly crept into TW3Dataset, but these have been fixed. So the next update will remedy the situation profoundly.

The second option, SQLite, is awesome. It is a pure, 1:1 compile from the original C code, so it contains everything WebSQL has "and then some". The reason it havent been wrapped more rigidly is because it's destined to become a part of our "DataSnap" like API later.

Like mentioned above we are on a journey here, and we have to deal with things in the right order. So each DB engine will be isolated in a "driver" like class and then register with a common API. That way people can use classes like TDatabase, TQuery, TStatement and similar high-level components to access any database they like. IndexDB is the oddball in all of this, so we might write a shim/proxy to leverage that.

Picking an engine

OK, with a situation rapport out of the way, lets pick a database. Our options are thus:

  • WebSQL
  • SQLite
  • TW3Dataset

Since TW3Dataset needs the update, I have excluded that. SQLite is awesome but could perhaps need more solid wrappers. So for this I will use WebSQL since that has more clearly defined classes.

Picking a storage point

Browsers only have cache storage. This is basically a sandboxed file that is saved in the cache folder. Under phonegap this is likewise sandboxed and stored in the "yourname.app/cache" folder if im not mistaken (not sure about that one, please check the Phonegap docs if you need the absolute path). But you dont need a path to load or save into the cache. The browser maps your data into the file-region automatically.

Also worth mentioning: When you run in the browser you have a limit of 10-15 megabytes (depends on the browser) for cache storage. When you link the project with Phonegap this limit goes away and you have the same storage rights as native applications. So you dont need to worry about running out of space if you plan to link with phonegap. For pure browser work, you are not expected to make huge databases - but rather cache info before shipping that to a server. A bit like what you would use TClientDataset for under Delphi.

But ok, let's use normal cache storage for now.

WebSQL is excellent because this will store itself automatically! You dont need to do anything in particular to load or save it. It all boils down to you creating or dropping the database.

DB example

Right! So fire up Smart and create a new visual project. Our first business is to create the database object and I typically put that into the application class. That way it can be accessed by any form in the program. A nice helper function is also good to have. Here is how it looks so far:

unit Unit1;

interface

uses
  Pseudo.CreateForms,
  System.Types,
  SmartCL.System,
  SmartCL.Components,
  SmartCL.Forms, 
  SmartCL.Application,
  SmartCL.DbSql,
  Form1;

type
  TApplication  = class(TW3CustomApplication)
  private
    FDatabase: TW3WebSQLDatabase;
  public
    procedure ApplicationStarting; override;
  published
    Property Database: TW3WebSQLDatabase read FDatabase;
  end;

  // global easy-access function to DB layer
  function Datastore: TW3WebSQLDatabase;

implementation

function Datastore: TW3WebSQLDatabase;
begin
  Result := TApplication(Application).database;
end;

procedure TApplication.ApplicationStarting;
begin
  //setup database engine
  FDatabase:=TW3WebSQLDatabase.Create;
  FDatabase.DBName:={$I 'app:name'};
  FDatabase.DBDescription:='Database for ' + {$I 'app:name'};
  FDatabase.DBSize:=(1024 * 1024) * 4;

  inherited;
end;

end.

This creates a websql database that can hold 4 megabytes of data. Which in browser terms is huge (I mean, you are not doing your taxes here are you). Then there is the main form and actually making something happen. So drop a TW3Button on the form and set the title to "Create Database".

We then need some code to create a table and insert some records. For brewity here is the whole code for Form1:

unit Form1;

interface

uses 
  System.Types,
  System.Types.Convert,
  System.Objects,
  System.Time,
  SmartCL.System,
  SmartCL.Time,
  SmartCL.Graphics,
  SmartCL.Components,
  SmartCL.FileUtils,
  SmartCL.Forms,
  SmartCL.Fonts,
  SmartCL.Theme,
  SmartCL.Borders,
  SmartCL.Application,
  SmartCL.DbSql,
  SmartCL.Controls.Button;

type
  TForm1 = class(TW3Form)
    procedure W3Button1Click(Sender: TObject);
  private
    {$I 'Form1:intf'}
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
    procedure Resize; override;
  end;

implementation

{ TForm1 }

uses unit1;

procedure TForm1.W3Button1Click(Sender: TObject);
var
  Transaction: TW3WebSQLCustomTransaction;
begin

  // activate database
  try
    if not Datastore.Active then
      Datastore.Active := True;
  except
    on e: exception do
    begin
      writeln("Failed to activate database: ");
      writeln(e.message);
      exit;
    end;
  end;

  // Get new write transaction
  if Datastore.GetWriteTransaction(Transaction) then
  begin
    // Create our table
    try
      Transaction.Execute("create table if not exists customers
        (id integer primary key asc,
         name string);",[]);
    finally
      if Transaction.LastFailed then
        WriteLn(Transaction.LastError);
      Transaction.Free;
    end;
  end else
  raise Exception.Create('Failed to create write transaction error');

  // Get new write transaction
  if Datastore.GetWriteTransaction(Transaction) then
  begin
    try
      // Populate the table with some records
      for var x := 1 to 10 do
      begin
        var MyData := 'John Doe #' + x.ToString();
        Transaction.Execute("insert into customers (name) values (?);",[MyData]);
      end;
    finally
      if Transaction.LastFailed then
        WriteLn(Transaction.LastError);
      Transaction.Free;
    end;
  end else
  raise Exception.Create('Failed to create write transaction error');

  // Get a new read transaction
  if DataStore.GetReadTransaction(Transaction) then
  begin
    // Setup the OnSuccess event handler.
    Transaction.OnSuccess := procedure (Sender: TObject)
    begin
      var Reader := TW3WebSQLReadTransaction(Sender);
      if Reader.Dataset <> nil then
      begin
        var Dataset := Reader.Dataset;
        for var x := 0 to Dataset.rows.Length - 1 do
        begin
          var Row := Dataset.rows.item(x);
          WriteLn(Row['name']);
        end;
      end;

      // All done, kill the transaction
      Reader.free;
    end;

    // Setup the OnFailed event handler
    TransAction.OnFailed := procedure (Sender: TObject)
    begin
      var Reader := TW3WebSQLReadTransaction(Sender);
      writelnF("Read transaction failed: %s", [reader.LastError]);

      // Kill the transaction
      Reader.free;
    end;

    // OK lets execute the SQL Query!
    try
      Transaction.Execute("select * from customers;",[]);
    finally
      if Transaction.LastFailed then
        WriteLn(Transaction.LastError);
    end;
  end;
end;

procedure TForm1.InitializeForm;
begin
  inherited;
  // this is a good place to initialize components
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
end;
 
procedure TForm1.Resize;
begin
  inherited;
end;
 
initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
end.

Now you are going to notice something -- namely that the database is persistent (!) So if you run this example 10 times, it will actually create 10 new records every time. The key here is the SQL that creates the database:

Transaction.Execute("create table if not exists customers (id integer primary key asc, name string);",[]);

This basically says "Create this database UNLESS it already exists".

Manual storage with SQLite

I am bit pressed for time right now. I will be away from the office for about a month pending surgery. I will be busy with forum duty etc. as much as I can, but sadly a secondary example with SQLite will have to wait a little. But I hope this one has given you an easy path to store things in the cache.

Also remember: You can look at the data using the Browser Devtools :)

I will do an article on SQLite when time allows 

image.png

Share this post


Link to post
Share on other sites

@lennart

What is "TW3WebSQLCustomTransaction"    I only find "TW3WebSQLTransaction"....however, the GetWriteTransaction method expects a "TW3CustomDbTransaction" which seems to work.

 

Also, is there a reason why you would NOT also put the "checking of database active property" and the "creation of table"  in the Application object?

e.g.

procedure TApplication.ApplicationStarting;
var
  Transaction: TW3CustomDbTransaction;
begin
  //setup database engine
  FDatabase:=TW3WebSQLDatabase.Create;
  FDatabase.DBName:={$I 'app:name'};
  FDatabase.DBDescription:='Database for ' + {$I 'app:name'};
  FDatabase.DBSize:=(1024 * 1024) * 4;

  // activate database
  try
    if not Datastore.Active then
      Datastore.Active := True;
  except
    on e: exception do
    begin
      writeln("Failed to activate database: ");
      writeln(e.message);
      exit;
    end;
  end;

  // Create our table
  // Get new write transaction
  if Datastore.GetWriteTransaction(Transaction) then
  begin
    try
      Transaction.Execute("create table if not exists customers
        (id integer primary key asc,
         name string);",[]);
    finally
      if Transaction.LastFailed then
        WriteLn(Transaction.LastError);
      Transaction.Free;
    end;
  end else
  raise Exception.Create('Failed to create write transaction error');


  inherited;
end;

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×