Editor for SQLite Database

This is the code for the photographs.db SQLite database detailed on the preceding page.

We use a standalone SocketIO server with the following JavaScript (Node.js) code in order to run the SQL statements sent to it by the client and to send to the client an indication of success or the error message returned by the SQLite DBMS. Note that an attempt to delete a non-existent record does not generate an error. The server also obtains one list of the filenames (of images) stored in the directory and another of the filenames already added to the database table. On request, it sends a list of any as-yet unused files to the client.

const io = require('socket.io')(3001);
const fs = require('fs');
const path = require('path');
//https://stackoverflow.com/questions/1187518/how-to-get-the-difference-between-two-arrays-in-javascript 
Array.prototype.diff = function(a) {
    return this.filter(function(i) {return a.indexOf(i) < 0;});
};
const directoryPath = path.join(__dirname, '..', 'Smart3', 'www', 'res', 'Gwen');
var db_filenames = [];
var dir_filenames;
var unused_images; 
// Get image filenames from directory 
fs.readdir(directoryPath, function (err, files) {
  if (err) {
    return console.log('Unable to scan directory: ' + err);
  } 
  dir_filenames = files;
});
// Get image filenames from database    
var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./db/photographs.db'); 
db.serialize(function() {    
  db.all('Select filename from Photos;', function(err, rows){ 
	if(err) {
      socket.emit('msg', err.message);
      return console.log(err.message); 
	}
    var i = 0;
    rows.forEach((row) => {          
      db_filenames[i] = row.filename;
      i += 1;
    });
  });      
});
db.close();   
// Connection to client(s)
io.on('connection', function(socket){
  console.log('user connected');  
  socket.on('UPDATE', function(data){
    console.log('Received from client: ' + data);    
    var sqlite3 = require('sqlite3').verbose();
    var db = new sqlite3.Database('./db/photographs.db');     
    db.run(data, (err) => {
	  if(err) {
        socket.emit('msg', err.message);
	  	return console.log(err.message); 
	  }
	  console.log('Success!');
      socket.emit('msg', 'Success!');
    });
    db.close();    
  });
  socket.on('UNUSEDIMAGES', function(data){
    console.log('Received from client: ' + data);    
    unused_images = dir_filenames.diff(db_filenames);
    if (unused_images.length > 0) {
      socket.emit('UNUSED', JSON.stringify(unused_images));
      console.log(unused_images);
    } else {
      console.log('No unused images');
    };      
  });
});  
console.log('Server running on PC at http://192.168.1.5:3001');

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.

Entry of the image filename results in:
  • the letters before the dot appearing in the second edit box as a starting point for the name of the painting;
  • the painting appearing as a thumbnail to confirm that a copy has been saved to the required location and that the filename is typed accurately;
  • the natural width and height of the image being entered into the appropriate edit boxes.
Further time-savings for the user result from populating a combo box with a list of filenames of images stored in the folder on the PC but not yet added to the database table.

See below a trimmed-down project file for the client.

<SMART>
  <Project version="3" subversion="0">
    <Name>Photo_DB_Editor</Name>
    <Options>
      <Compiler>
        <Assertions>1</Assertions>
        <Optimize>1</Optimize>
        <HintsLevel>1</HintsLevel>
        <ProjectSearchPath></ProjectSearchPath>
      </Compiler>
      <Codegen>
        <Obfuscation>1</Obfuscation>
        <InlineMagics>1</InlineMagics>
        <Devirtualize>1</Devirtualize>
        <MainBody>1</MainBody>
        <CodePacking>1</CodePacking>
        <SmartLinking>1</SmartLinking>
        <Verbosity>1</Verbosity>
      </Codegen>
      <ConditionalDefines />
      <Linker>
        <SourceMap>0</SourceMap>
        <CompressCSS>0</CompressCSS>
        <ExternalCSS>0</ExternalCSS>
        <Theme>default.css</Theme>
        <CustomTheme>0</CustomTheme>
        <EmbedJavaScript>1</EmbedJavaScript>
      </Linker>
      <Output>
        <JavaScriptFileName>main.js</JavaScriptFileName>
        <HtmlFileName>Photo_DB_Editor.html</HtmlFileName>
        <OutputFilePath>www\</OutputFilePath>
      </Output>
      <Import />
      <Execute />      
    </Options>
    <Files>
        <File type="main">
        <Name>Photo_DB_Editor</Name>
        <Source>
          <![CDATA[uses SmartCL.System, Unit1, Form1;

  var Application := TApplication.Create;
  Application.RunApp;
]]>
        </Source>
      </File>
            <File type="unit">
        <Name>Unit1</Name>
        <Source>
          <![CDATA[unit Unit1;
interface
uses
  Pseudo.CreateForms, System.Types, SmartCL.System, SmartCL.Components, SmartCL.Forms, 
  SmartCL.Application;
type
  TApplication  = class(TW3CustomApplication)
  end;
implementation
end.]]>
        </Source>
      </File>
           <File type="form">
        <Name>Form1</Name>
        <Source>
          <![CDATA[
unit Form1;
{ALL IMAGES COPYRIGHT GWEN ADAIR https://www.facebook.com/gwenadairpainter/ and
https://gwenadair.wixsite.com/painter}
interface

uses 
  System.Types, System.Types.Convert, System.Objects, SmartCL.System, SmartCL.Graphics,
  SmartCL.Components, SmartCL.Forms, SmartCL.Fonts, SmartCL.Theme, SmartCL.Borders, 
  SmartCL.Application, SmartCL.Controls.Button, SmartCL.Controls.Label, SmartCL.Controls.EditBox,
  SmartCL.Controls.ComboBox, SmartCL.Controls.Image, SmartCL.Net.SocketIO;

type
  TForm1 = class(TW3Form)

  procedure btnSubmitClick(Sender: TObject);
  procedure btnDeleteClick(Sender: TObject);
  procedure btnUnusedImagesClick(Sender: TObject);
  procedure edtFilenameChanged(Sender: TObject);
  procedure cboUnusedImagesChanged(Sender: TObject);
  private
    {$I 'Form1:intf'}
    Categories: array[0..5] of string;
    FResults: array of Variant;
    FSocket: TW3SocketIO;
  protected
    procedure InitializeForm; override;
    procedure InitializeObject; override;
  end;

implementation

procedure TForm1.btnSubmitClick(Sender: TObject);
var
  strInsert: string;
  i: integer;
begin
  lblResult.Caption := '';
  // Construct SQL statement
  strInsert := "INSERT INTO Photos VALUES('" + edtFilename.Text + "','"+ edtName.Text + "','" + 
    cboCategories.Items[cboCategories.SelectedIndex] +  "'," + edtImageWidth.Text +
    "," + edtImageHeight.Text+ "," + edtPaintingWidth.Text+ "," + edtPaintingHeight.Text + ");"; 
  // Send SQL statement to server    
  FSocket.emit('UPDATE', strInsert);
  // If filename added to database is in combo box of unused files, remove it.
  i := cboUnusedImages.IndexOf(edtFilename.Text);
  if i > -1 then
    cboUnusedImages.Remove(i);
  // Register the change in the combo box of unused files. 
  cboUnusedImagesChanged(Self);    
end;

procedure TForm1.btnDeleteClick(Sender: TObject);
var
  strDelete: string;
begin
  lblResult.Caption := '';
  // Construct SQL statement
  strDelete := "DELETE FROM Photos WHERE name = '" + edtName.Text + "';";
  // Send SQL statement to server   
  FSocket.emit('UPDATE', strDelete);
end;

procedure TForm1.btnUnusedImagesClick(Sender: TObject);
begin
  lblResult.Caption := 'No unused images';
  // Request list of unused images from server.
  FSocket.emit('UNUSEDIMAGES', 'Get unused images');
end;

procedure TForm1.edtFilenameChanged(Sender: TObject);
begin
  // Put letters before the dot into name edit box as starting point painting name
  var dotpos := pos('.',edtFilename.Text);
  edtName.text := LeftStr(edtFilename.Text, dotpos - 1);
  // Load the image  
  photo.URL := 'res/gwen/' + edtFilename.Text;   
end;

procedure TForm1.cboUnusedImagesChanged(Sender: TObject);
begin
  // Copy selected filename to edit box
  edtFilename.Text := cboUnusedImages.Item[cboUnusedImages.SelectedIndex];
  // Register the filename change 
  edtFilenameChanged(Self);
end;

procedure TForm1.InitializeObject;
begin
  inherited;
  {$I 'Form1:impl'}
  // Show the image suitably scaled
  photo.OnLoad := procedure(Sender: TObject)
                  begin
                    edtImageWidth.Text := IntToStr(photo.PixelWidth);
                    edtImageHeight.Text := IntToStr(photo.PixelHeight);
                    photo.ImageFit := fsScaleDown; 
                 end;
  lblTitle.Font.Size := 24;
  Categories := ['Portrait', 'Figure', 'Still Life', 'Landscape', 'Boat', 'Bird'];
  for var i := 0 to 5 do
    cboCategories.add(Categories[i]);
  // Change width and height edit boxes to be spin-edit boxes  
  edtImageWidth.InputType := itNumber;
  edtImageHeight.InputType := itNumber;
  edtPaintingWidth.InputType := itNumber;
  edtPaintingHeight.InputType := itNumber;
  FSocket := TW3SocketIO.Create;  
  FSocket.OnConnected := procedure (Sender: TW3SocketIO)
    begin
      // Process message from server 
      FSocket.On('msg', procedure (const Data: Variant)
        begin
          lblResult.Caption := Data.toString();
        end);
      // Processed data of unused files from server  
      FSocket.On('UNUSED', procedure (const Data: Variant)
        begin
          asm
            @FResults = JSON.parse(@Data);
          end;
          cboUnusedImages.Clear;
          // Populate combo box
          for var i := 0 to FResults.Length - 1 do
             cboUnusedImages.Add(FResults[i]);
          cboUnusedImagesChanged(Self);
          lblResult.Caption := 'See combo box for unused image(s)';          
        end);         
    end;      
end;

procedure TForm1.InitializeForm;
begin
  inherited; 
  FSocket.connect('http://127.0.0.1:3001', True);  
end;

initialization
  Forms.RegisterForm({$I %FILE%}, TForm1);
  {$R 'socket.io.client.js'}
end.
]]>
        </Source>
        <Design>
          <![CDATA[<?xml version="1.0" encoding="utf-16"?>
<Form version="3" subversion="0">
  <object type="TW3Form">
    <Caption>W3Form</Caption>
    <Name>Form1</Name>
    <object type="TW3Label">
      <Caption>Filename</Caption>
      <Width>128</Width>
      <Top>56</Top>
      <Left>8</Left>
      <Height>32</Height>
      <Name>lblFilename</Name>
    </object>
    <object type="TW3Label">
      <Caption>Name</Caption>
      <Width>128</Width>
      <Top>56</Top>
      <Left>152</Left>
      <Height>32</Height>
      <Name>lblName</Name>
    </object>
    <object type="TW3EditBox">
      <Value></Value>
      <Range></Range>
      <Width>128</Width>
      <Top>96</Top>
      <Left>8</Left>
      <Height>32</Height>      
      <Name>edtFilename</Name>
      <OnChanged>edtFilenameChanged</OnChanged>
    </object>
    <object type="TW3EditBox">
      <Value></Value>
      <Range></Range>
      <Width>128</Width>
      <Top>96</Top>
      <Left>152</Left>
      <Height>32</Height>
      <Name>edtName</Name>
    </object>
    <object type="TW3Label">
      <Caption>Category</Caption>
      <Width>100</Width>
      <Top>56</Top>
      <Left>300</Left>
      <Height>32</Height>
      <Name>lblCategory</Name>
    </object>
    <object type="TW3Label">
      <Caption>Unused Images</Caption>
      <Width>128</Width>
      <Top>56</Top>
      <Left>460</Left>
      <Height>32</Height>
      <Name>lblImages</Name>
    </object>
    <object type="TW3ComboBox">
      <Width>128</Width>
      <Top>96</Top>
      <Left>300</Left>
      <Height>32</Height>
      <Name>cboCategories</Name>
    </object>    
    <object type="TW3ComboBox">
      <Width>150</Width>
      <Top>96</Top>
      <Left>460</Left>
      <Height>32</Height>
      <Name>cboUnusedImages</Name>
      <OnChanged>cboUnusedImagesChanged</OnChanged>
    </object>
    <object type="TW3Label">
      <Caption>Image Width</Caption>
      <Width>128</Width>
      <Top>192</Top>
      <Left>16</Left>
      <Height>32</Height>
      <Name>lblImageWidth</Name>
    </object>
    <object type="TW3Label">
      <Caption>Image Height</Caption>
      <Width>128</Width>
      <Top>192</Top>
      <Left>160</Left>
      <Height>32</Height>
      <Name>lblImageHeight</Name>
    </object>
    <object type="TW3Label">
      <Caption>Painting Width</Caption>
      <Width>128</Width>
      <Top>192</Top>
      <Left>360</Left>
      <Height>32</Height>
      <Name>lblPaintingWidth</Name>
    </object>
    <object type="TW3Label">
      <Caption>Painting Height</Caption>
      <Width>128</Width>
      <Top>192</Top>
      <Left>496</Left>
      <Height>32</Height>
      <Name>lblPaintingHeight</Name>
    </object>
    <object type="TW3EditBox">
      <Value></Value>
      <Range></Range>
      <Width>128</Width>
      <Top>232</Top>
      <Left>8</Left>
      <Height>32</Height>
      <Name>edtImageWidth</Name>
    </object>
    <object type="TW3Button">
      <Caption>Submit</Caption>
      <Width>128</Width>
      <Top>328</Top>
      <Left>16</Left>
      <Height>32</Height>
      <Name>btnSubmit</Name>
      <OnClick>btnSubmitClick</OnClick>
    </object>
    <object type="TW3Button">
      <Caption>Delete Record</Caption>
      <Width>128</Width>
      <Top>328</Top>
      <Left>160</Left>
      <Height>32</Height>
      <Name>btnDelete</Name>
      <OnClick>btnDeleteClick</OnClick>
    </object>
    <object type="TW3Button">
      <Caption>Unused Images</Caption>
      <Width>160</Width>
      <Top>328</Top>
      <Left>300</Left>
      <Height>32</Height>
      <Name>btnUnusedImages</Name>
      <OnClick>btnUnusedImagesClick</OnClick>
    </object>    
    <object type="TW3EditBox">
      <Value></Value>
      <Range></Range>
      <Width>128</Width>
      <Top>232</Top>
      <Left>160</Left>
      <Height>32</Height>
      <Name>edtImageHeight</Name>
    </object>
    <object type="TW3EditBox">
      <Value></Value>
      <Text>0</Text>
      <Range></Range>
      <Width>128</Width>
      <Top>232</Top>
      <Left>360</Left>
      <Height>32</Height>
      <Name>edtPaintingWidth</Name>
    </object>
    <object type="TW3EditBox">
      <Value></Value>
      <Text>0</Text>
      <Range></Range>
      <Width>128</Width>
      <Top>232</Top>
      <Left>496</Left>
      <Height>32</Height>
      <Name>edtPaintingHeight</Name>
    </object>
    <object type="TW3Label">
       <Caption></Caption>
      <Width>400</Width>
      <Top>380</Top>
      <Left>16</Left>
      <Height>32</Height>
      <Name>lblResult</Name>
    </object>
    <object type="TW3Label">
      <Caption>Editor for photographs.db</Caption>
      <Width>440</Width>
      <Top>8</Top>
      <Left>8</Left>
      <Height>48</Height>
      <Name>lblTitle</Name>
    </object>
    <object type="TW3Image">
      <Width>168</Width>
      <Top>280</Top>
      <Left>500</Left>
      <Height>112</Height>
      <Name>photo</Name>
    </object>
  </object>
</Form>]]>
        </Design>
        <AutoCreate>
          <IsAutoCreate>1</IsAutoCreate>
          <IsMainForm>1</IsMainForm>
          <Order>1</Order>
        </AutoCreate>
      </File>
    </Files>
    <Target>Browser</Target>
    <Generator>Visual Components Project</Generator>
  </Project>
</SMART>

Programming - a skill for life!

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