Transfer of Data in a SQLite Database on a PC to a Dataset

You should find this demonstration easier to reproduce if you have worked through the preceding page. See our TW3Image demonstration to see the original on which this development is based. Instead of hard-coding the data into the dataset, we code a server to send the data in a SQLite table to the client containing the dataset. We transfer all the data in the single Photos table of the photographs.db database with the SQL statement SELECT * FROM Photos but could easily download only a subset as required. The following page has the code of a client-server system that we use to edit the photographs.db database.

To create and populate the photographs.db database, we used this node.js program:

const sqlite3 = require('sqlite3').verbose(); 

let db = new sqlite3.Database('./db/photographs.db', (err) => {
  if (err) {
    console.error(err.message)
  }
  console.log('Created photographs.db database.');
});
db.serialize(function() {
  db.run("CREATE TABLE Photos (filename text, name text primary key, category text, image_width integer, image_height integer, painting_width integer, painting_height integer)");
  db.run("INSERT INTO Photos VALUES('Lee.jpg','Lee','Portrait',565,565,0,0),"+
    "('SelfPortrait.jpg','Self Portrait','Portrait',407,406,0,0),"+
    "('CrazyManMichael.jpg','Crazy Man Michael','Figure',416,419,50,50),"+
    "('PublicDecency.jpg','Public Decency','Figure',424,423,30,30),"+
    "('ThreeHerrings.jpg','Three Herrings','Still Life',581,582,0,0),"+
    "('OrangesAndCherries.jpg','Oranges and Cherries','Still Life',497,374,0,0),"+
    "('DumfriesLandscape.jpg','DumfriesLandscape','Landscape',250,166,0,0),"+
    "('CarrickShore.jpg','Carrick Shore','Landscape',499,332,0,0),"+
    "('RiverNithAtDumfries.jpg','River Nith at Dumfries','Landscape',432,434,70,70),"+
    "('OneWeeBoat.jpg','One Wee Boat,PortPatrick','Boat',557,564,30,30),"+
    "('WeeBoats.jpg','Wee Boats,PortPatrick','Boat',497,371,50,40),"+
    "('Puffins.jpg','Puffins','Bird',378,192,40,20),"+
    "('Crow.jpg','Crow','Bird',573,382,0,0),"+
    "('Heron.jpg','Heron','Bird',291,580,21,43)");
});
  db.close();

We use a standalone SocketIO server with the following JavaScript code in order to send data from the sqlite database to the client.

const io = require('socket.io')(3000);
io.on('connection', function(socket){ 
  console.log('user connected');
  socket.on('SQL', function(data){
    console.log('Received from client: ' + data);    
    var sqlite3 = require('sqlite3').verbose();
    var db = new sqlite3.Database('./db/photographs.db');
    db.all(data.toString(), function(err,rows){
      if (err) {
            socket.emit('dbdata', err.toString());
            return;
          };         
          socket.emit('dbdata', JSON.stringify(rows))
          console.log(rows);
        });
        db.close();    
  });
});
console.log('Server running on PC at http://192.168.1.5:3000');

The Smart Pascal code of the client, set up with the localhost URL for testing on the same PC, compiles with Version 3.0 of Smart Mobile Studio. Make sure that socket.io.client.js (stored in a location such as C:\ProgramData\The Smart Company\Smart Mobile Studio\Libraries\socket.io) is in the www/lib folder.

The Smart Pascal form code follows.

unit Form1;
{ALL IMAGES COPYRIGHT GWEN ADAIR https://www.facebook.com/gwenadairpainter/ and
https://gwenadair.wixsite.com/painter}
interface

uses 
  System.Dataset, System.Types, System.Types.Convert, system.objects, System.Time,
  System.IOUtils, System.Device.Storage, SmartCL.System, SmartCL.Time,
   SmartCL.Graphics, SmartCL.Components,
  SmartCL.FileUtils, SmartCL.Device.Storage, SmartCL.Forms, SmartCL.Fonts,
  SmartCL.Theme, SmartCL.Borders, SmartCL.Application, SmartCL.Controls.Image,
  SmartCL.Controls.Button, SmartCL.Controls.ListBox, SmartCL.Controls.Label,
  SmartCL.Controls.Memo, System.Lists, SmartCL.Grid, SmartCL.Net.SocketIO;

type
  TForm1 = class(TW3Form)
  const
    PORTRAIT_NOTES = 'I paint from the life and also from photos, and each has ' +
      'merits. In a portrait there should be a relationship between the painter ' +
      'and the sitter, so I believe at least one sitting should be from the life. ' +
      'On the other hand, when the painting is of a stranger and is painted as a ' +
      'study or to create an image I feel is interesting and arresting to look ' +
      'at, I will happily work exclusively from photos, because I find it ' +
      'fascinating that we think we can tell so much about a person from a ' +
      'fraction of a second of their life. We read the expression, then create a ' +
      'whole narrative.';
    FIGURE_NOTES = 'Most of my current work is figure composition, where I love ' +
      'the challenge of creating balanced and interesting compositions with positive ' +
      'forms and negative spaces. The human figure is what I love to paint, and I work ' +
      'from photographic sources, often combined from several images.';
    BIRD_NOTES = 'Birds began to appear in the backgrounds of my paintings, or as ' +
      'props for some of the figures. But I discovered that they have real personalities ' +
      'which are fun to paint.';
    OTHER_NOTES = 'In addition to my main work which is figurative, I also enjoy ' +
      'painting still lives, boats and landscapes. These paintings allow me to explore ' +
      'colour and texture.';
    ZOOM_FACTOR = 0.75;
    procedure lboCategoriesSelected(Sender: TObject; ItemIndex: Integer);
    procedure lboImagesSelected(Sender: TObject; ItemIndex: Integer);
    procedure Download(Sender: TObject);
    procedure Populate(Sender: TObject);

  private
    {$I 'Form1:intf'}
    FSocket: TW3SocketIO;
    DownLoadTimer, PopulateTimer: TW3Timer;
    FResults: array of variant;
    ImageTable: TW3Dataset;
    Categories: array of string;
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  PopulateTimer := TW3Timer.Create(Self);
  DownLoadTimer := TW3Timer.Create(Self); 
  PopulateTimer.OnTime := Populate;
  DownLoadTimer.OnTime := Download;
  PopulateTimer.Enabled := True;
  DownLoadTimer.Enabled := True;
  DownLoadTimer.Delay := 50;
  PopulateTimer.Delay := 500;
  FSocket := TW3SocketIO.Create;  
  FSocket.OnConnected := procedure (Sender: TW3SocketIO)
    begin
      FSocket.On('dbdata', procedure (const Data: Variant)
        begin
          asm
            @FResults = JSON.parse(@Data);
          end;
          for var i := 0 to FResults.Length - 1 do
            begin
              ImageTable.Append;
              ImageTable.Fields.FieldByName('filename').asString := FResults[i].filename.toString();
              ImageTable.Fields.FieldByName('name').asString := FResults[i].name.toString();
              ImageTable.Fields.FieldByName('category').asString := FResults[i].category.toString();
              ImageTable.Fields.FieldByName('image_width').asInteger := StrToInt(FResults[i].image_width.toString());
              ImageTable.Fields.FieldByName('image_height').asInteger := StrToInt(FResults[i].image_height.toString());
              ImageTable.Fields.FieldByName('painting_width').asInteger := StrToInt(FResults[i].painting_width.toString());
              ImageTable.Fields.FieldByName('painting_height').asInteger := StrToInt(FResults[i].painting_height.toString());
              ImageTable.Post;
            end;
        end);       
     end;     
end;

procedure TForm1.InitializeForm;
begin
  inherited; 
  categories := ['Portrait', 'Figure', 'Still Life', 'Landscape', 'Boat', 'Bird'];
  // Create the dataset and store the data
  ImageTable := TW3Dataset.Create;
  ImageTable.fieldDefs.Add("name", ftString);
  ImageTable.fieldDefs.Add("filename", ftString);
  ImageTable.fieldDefs.Add("category", ftString);
  ImageTable.fieldDefs.add("painting_width", ftInteger);  // in cm
  ImageTable.fieldDefs.add("painting_height", ftInteger); // in cm
  ImageTable.fieldDefs.add("image_width", ftInteger);     // in pixels
  ImageTable.fieldDefs.add("image_height", ftInteger);    // in pixels
  ImageTable.CreateDataset; 
  FSocket.connect('http://127.0.0.1:3000', True);  
end;

procedure TForm1.Download(Sender: TObject);
begin 
  FSocket.emit('SQL', "SELECT * FROM Photos");
  DownloadTimer.Enabled := False;
end;

procedure TForm1.Populate(Sender: TObject);
begin
  PopulateTimer.Enabled := False;
  lboCategories.BeginUpdate; // populate categories listbox
  for var i := 0 to 5 do
    lboCategories.AddItem(Categories[i]);
  lboCategories.EndUpdate;
  // Mimic selections from each listbox
  lboCategoriesSelected(nil, 3);
  lboImagesSelected(nil, 0);
  // Show the selections so that they match up with the image displayed
  lboCategories.SelectedIndex := 3;
  lboImages.SelectedIndex := 0;
end;

procedure TForm1.lboImagesSelected(Sender: TObject; ItemIndex: Integer);
begin
  ImageTable.First;
  var Found := False;
  repeat
    if ImageTable.Fields.FieldByName('name').asString = lboImages.Text[ItemIndex] then
      begin
        Found := true;
        var w := ImageTable.Fields.FieldByName('painting_width').asInteger;
        var h := ImageTable.Fields.FieldByName('painting_height').asInteger;
        if w > 0 then
          lblSize.Caption := 'Painting size ' + inttostr(w) + 'cm x ' + inttostr(h) + 'cm'
        else
          lblSize.Caption := '';
        photo.Url := 'res/gwen/' + ImageTable.Fields.FieldByName('filename').asString;
        photo.Width :=  ImageTable.Fields.FieldByName('image_width').asInteger;;
        photo.Height := ImageTable.Fields.FieldByName('image_height').asInteger;
        if photo.Width > 425 then
          begin  // Changing the Zoom property worked for Chrome and Edge but not Firefox.
            Photo.Width := round(Photo.Width * ZOOM_FACTOR);
            Photo.Height := round(Photo.Height * ZOOM_FACTOR);
          end;
        photo.Left := (Memonotes.Left - Photo.Width) div 2; // centre horizontally
      end;
    ImageTable.Next;
  until Imagetable.EOF or Found;
end;

procedure TForm1.lboCategoriesSelected(Sender: TObject; ItemIndex: Integer);
begin
  photo.Url := '';
  lboImages.Clear;
  lboImages.BeginUpdate;
  ImageTable.First;
  repeat   // Could try filter instead with FindNext instead of Next
   if ImageTable.Fields.FieldByName('category').asString = lboCategories.Text[ItemIndex] then
      lboImages.AddItem(ImageTable.Fields.FieldByName('name').asString);
    ImageTable.Next;
  until Imagetable.EOF;

  // Select the first image and show the selection
  lboImagesSelected(nil, 0);
  lboImages.SelectedIndex := 0;

  case ItemIndex of   // Populate memo with appropriate notes for the category selected.
    0: MemoNotes.Text := PORTRAIT_NOTES;
    1: MemoNotes.Text := FIGURE_NOTES;
    2, 3, 4: MemoNotes.Text := OTHER_NOTES;
    5: MemoNotes.Text := BIRD_NOTES;
  else
    MemoNotes.Text := OTHER_NOTES;
  end;
end;

initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
  {$R 'socket.io.client.js'}
end.    

Programming - a skill for life!

How to access a SQLite database on a PC from a web page