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.