Skip to content

TWebSQLRestClientDataset, TWebSQLRestConnection

Introduction

SQLDBRESTBridge is an open-source project https://wiki.freepascal.org/SQLDBRestBridge that offers a REST bridge for SQL databases. SQLDBRESTBridge allows to create a REST API for performing CRUD operations on a SQL database. Head to the wiki page for all details related to SQLDBRESTBridge.

Configuring the SQLDBRESTBridge server back-end

The demo comes with the source code to create the server instance (project restserver.lpr) that needs to be compiled with Lazarus as well as the executable restserver.exe. The sample is based on a SQLite database todo.db. This database contains a user table and a tasks table. The tables are created with:

-- Fake autoincremental
create table t2(id integer primary key autoincrement);
-- These must match table names below !
insert into sqlite_sequence (name,seq) values ('Tasks',1);
insert into sqlite_sequence (name,seq) values ('Users',1);
drop table t2;

-- Primary key autoincrement, because this allows to assign a value
and will update sqlite_sequence
-- See https://www.sqlite.org/autoinc.html
create table Users (
 uID integer primary key autoincrement,
 uLogin varchar(50) not null,
 uPassword varchar(100) not null
);
create unique index udxUsers on Users(uLogin);

create table Tasks (
  tID integer primary key autoincrement,
  tUserFK integer not null,
  tStatus varchar(15) not null,
  tDueDate date not null default CURRENT_DATE,
  tDescription varchar(4096)
);
create index idxTaskUser on Tasks(tUserFK);

After compiling the server, start it from the command line and it will be ready listening to requests on http://localhost:8080/

Using SQLite via TWebSQLRestClientDataset

Drop a TWebSQLRestConnection component on the form. This is the non-visual component through which the communication between the dataset and the REST server will happen. The TWebSQLRestConnection URI needs to be set to the URL of the server, in this case http://localhost:8080/ The REST server can require a default login for which the credentials are set with TWebSQLRestConnection.User and TWebSQLRestConnection.Password.

Then drop two TWebSQLRestClientDataset instances on the form, one for the user table and one of the tasks table. The TWebSQLRestClientDataset metadata can be automatically initialized from the server or it can be programmatically done in the client. The example shows the Tasks table metadata being initialized from the server and the user table metadata programmatically initialized

// Retrieve metadata from server and setup indexes in the client for
tasks tabls
  cdsTasks.UseServerMetaData := True;
  cdsTasks.Indexes.Add('ByDueDate', 'tDuedate',[]);
  cdsTasks.Indexes.Add('ByStatus', 'Tstatus',[]);
  cdsTasks.Indexes.Add('ByDescr', 'Tdescription',[]);
  cdsTasks.Indexes.Add('ByDueDateDesc', 'tDuedate',[ixDescending]);
  cdsTasks.Indexes.Add('ByStatusDesc', 'Tstatus',[ixDescending]);
  cdsTasks.Indexes.Add('ByDescrDesc', 'Tdescription',[ixDescending]);
  cdsTasks.ActiveIndex := 'ByDueDate';
  cdsTasks.IDField:='tID';
  // programmatic field initialization for user table used to add new
users
  cdsnewuser.FieldDefs.Add('uID',ftLargeInt,0);
  cdsnewuser.FieldDefs.Add('uLogin',ftString,255);
  cdsnewuser.FieldDefs.Add('uPassword',ftString,255);

There is also a dataset cdsValidLogin used for the sole purpose of verifying the login.

The login is validated with:

  CDSValidLogin.Close;
  CDSValidLogin.Params.ParamByName('uLogin').AsString := edtLogin.text;
  CDSValidLogin.Params.ParamByName('uPassword').AsString := edtPassword.text;
  CDSValidLogin.Load([], nil);

When opening this dataset, it either has or has not a record, indicating the user exists or does not exist. This is handled in the cdsValidLogin.AfterOpen event;

procedure TForm1.cdsValidLoginAfterOpen(DataSet: TDataSet);
begin
  FUID := -1;
  if cdsValidLogin.Recordcount = 0 then
    Showmessage('Invalid username/password')
  else
  begin
    FUID := cdsValidLogin.FieldByName('uID').AsInteger;
    EnableTasks;
    LoadTasks;
  end;
end;

When the user is found, the user tasks dataset is loaded via LoadTasks and as the dataset is filled, the DB-aware UI controls can work on this dataset.

procedure TForm1.LoadTasks;
begin
  CDSTasks.Close;
  CDSTasks.Params.ParamByName('uID').asInteger := fUID;
  CDSTasks.Load([], nil);
end;

The CDSTasks dataset then handles all further CRUD operations. In this sample, the client-side changes are not immediately updated in the server database. For this demo, it was chosen to do this in batch via calling CDSTasks.ApplyUpdates. The dataset will then internally handle applying all client-side dataset changes in one time to the server.