background image

TWebStellarDataStoreClientDataset Component

Introduction

The Stellar DataStore service is an instantly available, secure and worry-free cloud data storage service. The component TWebStellarDataStoreClientDataset makes it easy for a Delphi TMS WEB Core Application to use database tables on the Stellar DataStore service by a familiar syntax of using ClientDataSet. It also allows a seamless integration of the Stellar Data Store data tables with data-aware components like TWebDBGrid, TWebDBTableControl and many more... All the database operations can be done in the standard Delphi way through the TWebStellarDataStoreClientDataset component. All you need to do is specify the Stellar DataStore settings for authentication and authorization and choose the table(s) to connect to and the fields to use. Then connect a DataSource and Data components to it and make the dataset active.

Your first web application using TWebStellarDataStoreClientDataset

Set up your Stellar DataStore project in the Stellar DataStore web admin app

Follow these steps:

  1. Navigate to https://stellards.io/ and sign up for Stellar DataStore if not already done
  2. Go to My Account → Manage Database
  3. Under "Tables", create the tables you need and add the required Fields to each table
  4. The Table name or Table ID will be need to use these from your client app
  5. Under "Applications", select to get client access via OAuth or via access tokens. See next paragraph for details and recommendations for chosing either one access method.
  6. When chosing OAuth, first set your app Name, CallbackURL and Role. The Name's only purpose is for remembering what the OAuth access is used for. The CallbackURL will typically be the start page of your web client application (typically something like http://localhost:8000/Project1/Project1.html). Note that this URL might be different when you deploy your application to a web server. Finally, also select the Role. The Role will determine the access rights the application will have. When these settings are configured, the app will generate a ClientId and Secret. Keep these settings in a safe place as these will be needed in your web applications.
  7. When chosing Access Tokens, also here you can specify an app Name, the duration of validity of the access token, the role and finally also an optional domain restriction. When the domain is set, the Stellar Data Store endpoints will only be accessible with the generated access token when executed from the specified domain. Keep the generated access token in a safe place.

Create a TMS WEB Core Application

Create a standard TMS WEB Core Application in the Delphi IDE by choosing File, New, Other, TMS WEB Core Application. A new web form is created.

Set up the TWebStellarDataStoreClientDataSet component

Go to the Tool Palette and select the TWebStellarDataStoreClientDataSet component from the “TMS Data Access” section and drop it on the web form.

Specify the Component Properties

For an OAuth based flow, setup the properties either in code or in the Object Inspector under App:

  • App.ClientId: specify the ClientId here obtained via the Stellar Data Store web interface
  • App.Secret: specify the Secret here obtained via the Stellar Data Store web interface
  • App.CallbackURL: Set this to the start URL of the web application
  • TableName: Select what table from the project you want to consume via the client dataset

For a setup with an access token, set the public property TWebStellarDataStoreClientDataSet.AccessToken to the token obtained via the Stellar Data Store web app

Setup Fields

The DataSet field definitions need to be set up either in Object Inspector by rightclicking on the “Fields Editor” or in the WebFormCreate event code.

Select the fields in the Object Inspector

Follow these steps:

  1. Right-click the TWebStellarDataStoreClientDataSet and select “Edit DataSet Fields”
  2. Under "Connection Settings", either set the obtained access token or the OAuth flow's ClientId, Secret and CallbackURL if these properties have not been automatically retrieved from the WebStellarDataStoreClientDataSet component being edited

  1. Click the “Connect” button and follow the authentication instructions. When an access token is set, no authentication will be needed. If the process is successfull, a dialog with the list of available tables and fields is displayed.

  1. Select the table from the tables list (or from here you can also add tables and define meta data from the tables)
  2. When the WebStellarDataStoreClientDataSet component's Fields collection is still undefined, the fields from the selected table will now automatically be added
  3. Use the "Fields Editor" from the context-menu when you want to further add or remove fields manually

  1. Note that each table in Stellar Data Store has an id field. This id field cannot be edited. It is the key field for each table.

Create the Fields in code

Here is an example of adding the field definitions in code in the OnCreate event. In the Object Inspector, double-click on OnCreate event of the Web Form. This creates an event handler procedure WebFormCreate. The following code in it sets up the field definitions. What fields you add are based on how you defined them for the Table in Stellar Data Store. Note that id field must be defined as data type ftInteger.

1. WebStellarDataStoreClientDataSet1.FieldDefs.Clear;
2. WebStellarDataStoreClientDataSet1.FieldDefs.Add('id', ftInteger);
3. WebStellarDataStoreClientDataSet1.FieldDefs.Add('note',ftString);
4. WebStellarDataStoreClientDataSet1.FieldDefs.Add('date',ftDate);
5. WebStellarDataStoreClientDataSet1.Active := True

Add Data Components that connect to the DataSet

Now select and drop a TWebDataSource, TWebDBGrid and TWebDBNavigator component on the Web Form.

Set up the DataSource and Data components

Set the DataSource’s DataSet property to WebStellarDataStoreClientDataset1. Then set the DataSource property of the grid and navigator to point to TWebDataSource1.

Set up the Columns of the DBGrid

Do that by clicking on the Columns property of the DBGrid.

Set up a New Record event

Since we will be adding New Records with the DB Navigator, we need to set up the default values of the record. For this, we set up an OnNewRecord event procedure for the Stellar Data Store Client Data Set in the Object Inspector and type the following code in it.

procedure TForm1...NewRecord(DataSet: TDataSet);
begin
 DataSet.FieldByName('note').AsString := 'New Note';
 DataSet.FieldByName('date').AsDateTime := Date; // set to today
end;

Run the Web Application

Now you can build and run the application. When you run it for the first time, the component automatically asks you to login by using your credentials for Stellar Data Store. The DB Grid will appear empty as there are no records. Try adding new records with the Navigator and see how it works.

Todo List Demo

Please find this demo in the folder Demo\DBBackend\StellarDataStore\TodoList. This Demo connects the component to a Tasks table to show you the Tasks with their status, description and dates.

Additional features in this Demo

Add, Update, Delete through separate data aware controls and buttons The Demo allows you to perform add, update, delete operations through datbase field editor controls and buttons instead of through the Navigator.

Sorting on columns

We want to be able to sort on any column of the DB Grid by clicking on the header of the column. So we need to be able to read all the records in the order of that field. For this, we need to add a Sort Field Definition specifying the field to be sorted on. This is done in the event procedure GridTasksFixedCellClick.

1. WebStellarDataStoreClientDataSet1.ClearSortFieldDefs;
2. WebStellarDataStoreClientDataSet1.AddSortFieldDef(LIndex, gridTasks.Columns[ACol].SortIndicator = siAscending);
3. WebStellarDataStoreClientDataSet1.Refresh;

The first parameter to AddSortFieldDef call is the field name and the second parameter is a boolean flag that is true for ascending order and false for descending order. The Demo uses its own logic to pass this information and then Refreshes (reloads) the data in the desired order.

Updating, inserting and deleting data

This Demo also shows an example of connecting Data components like CheckBox or a Memo to the database so that those fields can be edited in the current record. After editing, a call to Update from the update button takes care of committing the changes to the cloud database. Similary, the Demo has examples of Inserting a new record and Deleting the current record by respective calls.

Troubleshooting

Exceptions are displayed in a red alert message at the bottom of the web page. You can also look at the Browser Console for error messages. If you start getting authentication errors when the application was working earlier, it’s most probably a changed IP address. In any case, the first thing you can try is clear the Local Storage which is under Applications in Chrome Developer tools.

Reference Section

TWebStellarDataStoreClientDataset

Below is a list of the most important properties and methods of TWebStellarDataStoreClientDataSet component.

Properties of TWebStellarDataStoreClientDataSet

Property Description
AccessToken This is a public property that can be used to set the access token obtained from the Stellar Data Store web app for access token based access
Active Set this to True to activate the DataSet. Field definitions must be present along with other properties described below.
App.ClientId Get this value from the OAuth application ClientId setting in the Stellar Data Store web app
App.CallbackURL Holds the start page URL of the application, also set for generating the OAuth ClientId and Secret
App.Secret Get this value from the OAuth application Secret setting in the Stellar Data Store web app
Connection: TWebStellarDataStoreConnection Can be linked to a TWebStellarDataStoreConnection component. The connection component can be linked to multiple datasets and can be responsible for the authentication & authorization with the Stellar Data Store service. The connected TWebStellarDataStoreClientDataSet will then use the authorized connection obtained by the linked connection component. This way, only a one-time connection is needed.
PersistTokens.Enabled When true, an obtained access token and refresh token is automatically persisted in local storage for reuse when the web app restarts to avoid a new authentication and authorization cycle
PersistTokens.Key Set the key under which the values are persisted in the app's local storage
TableJoinQuery Specify a join statement to work with data from two joined tables in Stellar Data Store. The join is specified as: TableName1;JoinField1=TableName2;JoinField2.
TableName Specify a table name to connect to from the Stellar Data Store web app
TableSortQuery Specify the sort order of records retrieved from the Stellar Data Store table. The sort order is defined as: Field1;asc|desc&Field2;asc|desc...
TableWhereQuery This allows to specify server side filtering. The filter is defined as Field1;condition;value&Field2;condition;value...
OnError This is an event property that notifies the application of any errors from Stellar Data Store. The event can be set up at design time in Object Inspector by double-clicking on it. If the Application does not subscribe to this event, an Exception is raised on such errors. If subscribed, the application can then decide what to do. For example, show error, raise exception or take some corrective action. Note that hard errors (Delphi Exceptions) are not passed in this event. Rather, they cause an Exception that appears in a red alert. But in any case, all errors are always logged to the browser console.
OnTableNotFound This event is triggered when the connection was succesfull but the specified table was not found. From this event handler, it is possible to write table initialization code. As this is an event handler that can contain async code, mark it with the async attribute and call the Done procedure at the end

Methods of TWebStellarDataStoreClientDataset

Only the methods specific to TWebStellarDataStoreClientDataset are listed below. Other methods from the base DataSet classes are used in the standard way.

Method Description
Refresh procedure Refresh(Force: Boolean=False);
Refresh reloads all the objects from the database. If AddSortFieldDef has been used to set up sorting definitions, the objects are loaded in the order specified. In addition, the current record pointer is restored after the Reload which is convenient for the user interface of the web application. Refresh is internally postponed till all the pending updates started asynchronously are finished. The Force parameter ignores the pending updates and forces a reload.
AddSortFieldDef and ClearSortFieldDefs Use AddSortFieldDef to add one or more sort definitions for loading the data. Before using a series of these calls, you must clear all sort definitions by calling ClearSortFieldDefs.
procedure AddSortFieldDef(aField: String; isAscending: Boolean));
Where
- aField - the field name for the sorting order
- isAscending - Set True for ascending order.
ClearTokens After a successful authentication & authorization, the TWebStellarDataStoreClientDataset will store the obtained access tokens in the local storage so that a next time, this does not need to be obtained again. If for some reason this needs to be removed, call
procedure ClearTokens;
LoadTokens Force to load the obtained access & refresh tokens from the browser local storage
SaveTokens Force to save the obtained access & refresh tokens to the browser local storage

TWebStellarDataStoreConnection

This component allows to authenticate and authorize one time against the Stellar Data Store service and use this connection with multiple TWebStellarDataStoreClientDataSet components that are linked to this TWebStellarDataStoreConnection via its Connection property. As TWebStellarDataStoreConnection performs authentication and authorization similar to the standalone TWebStellarDataStoreClientDataSet, it has the same properties to set the OAuth characteritics.

Property Description
App.ClientId Get this value from the OAuth application ClientId setting in the Stellar Data Store web app
App.CallbackURL Holds the start page URL of the application, also set for generating the OAuth ClientId and Secret
App.Secret Get this value from the OAuth application Secret setting in the Stellar Data Store web app
PersistTokens.Enabled When true, an obtained access token and refresh token is automatically persisted in local storage for reuse when the web app restarts to avoid a new authentication and authorization cycle
PersistTokens.Key Set the key under which the values are persisted in the app's local storage
Event Description
OnAccessToken Event triggered when the connection has obtained the access token. Get the access token via WebStellarDataStoreConnection.AccessToken
OnConnect Event triggered when the WebStellarDataStoreConnection made a successful authentication and authorization via OAuth2
OnError Event triggered when the authentication and authorization failed at some point. Inspect the response to see information on possible causes of the failure
OnRequestResponse Event triggered returning the result object of HTTP requests internally performed by WebStellarDataStoreConnection
OnResponse Event triggered returning the response as text of HTTP requests internally performed by WebStellarDataStoreConnection

TStellarDataStore Class

Introduction

TStellarDataStore is a non-visual component that enables to use the Stellar Data Store API in an efficient way. It offers access to the tables, user profile and user project information.

TStellarDataStore

TStellarDataStore is the main class offering access to the Stellar Data Store API in an object oriented way.

Properties of TStellarDataStore

Property Description
ProjectID: string Holds the project GUID after GetProject is called
Tables: TStellarDataStoreTables Collection filled with the table information after a call to GetTables
User: TStellarDataStoreUser Class holding the user account information after a call to GetUser

Methods of TStellarDataStore

Only the methods specific to TWebStellarDataStoreClientDataset are listed below. Other methods from the base DataSet classes are used in the standard way.

Method Description
GetUser Async method retrieving the user account information. Returns true when successful and user account information is set via User property
GetTables Async method retrieving all table information. Returns true when successful and the tables collection is filled with all project's tables
GetProject Async method retrieving the unique user project GUID
HasTable(TableName: string) Async method returning true when the project has the table specified by name
CreateTable(TableName: string; isMultitenant: boolean; Description: string) Async method returning true when the project has the table specified by id
DeleteTable(TableName: string) Async method deleting a table by table name
DeleteTable(TableId: integer) Async method deleting a table by table id

TStellarDataStoreTables

TStellarDataStoreTables is a collection of TStellarDataStoreTable. After calling TStellarDataStore.GetTables, this collection is filled with all the tables from the Stellar Data Store project

Methods of TStellarDataStoreTables

Method Description
FindTable(TableName: string): TStellarDataStoreTable Returns the table from the Tables collection by name

TStellarDataStoreTable

TStellarDataStoreTables is a collection of TStellarDataStoreTable. After calling TStellarDataStore.GetTables, this collection is filled with all the tables from the Stellar Data Store project

Properties of TStellarDataStoreTable

Property Description
DB: TStellarDataStore Provides access to the owner TStellarDataStore class
Fields: TStellarDataStoreFields Collection of the fields metadata of the Stellar Data Store table
Entities: TStellarDataStoreEntities Collection of the entities data of the Stellar Data Store table
WhereQuery: string The filter specifier for returning the Stellar Data Store table data
SortQuery: string The sort order specifier for returning the Stellar Data Store table data
JoinQuery: string The table join specifier for returning the Stellar Data Store table data
Name: string Holds the name of the table
TableID: string Holds the unique id of table
Description: string Holds the table description
Multitenant: boolean When true, the table is by default multi-tenant, i.e. multiple users having access to the table will only work on their own data

Methods of TStellarDataStoreTable

Method Description
CreateField(AName: string; AType: TFieldType; ASize: integer = 0) Async method creating a new field in the table
DeleteField(AName: string) Async method deleting the field with the specified name from the table
GetFields Async method filling the Fields collection of the table with all fields retriebed for the Stellar Data Store table
GetEntities Async method filling the Entities collection with the data of the Stellar Data Store table
GetEntitiesAsArray Async method returning a JSON array of the records of the Stellar Data Store table
InsertArray(AArray: TJSArray) Async method inserting new records into the Stellar Data Store table
UpdateArray(AId: string; AArray: TJSArray) Async method updating an array of records in the Stellar Data Store table
Delete(AId: string) Async method deleting a table by Id from the Stellar Data Store project

TStellarDataStoreFields

TStellarDataStoreFields is a collection of TStellarDataStoreField. After calling TStellarDataStoreTable.GetFields, this collection is filled with all the fields from the Stellar Data Store table

Method Description
FindField(FieldName: string): TStellarDataStoreField Returns the field from the Fields collection by name

TStellarDataStoreField

Properties of TStellarDataStoreField

Property Description
FieldName: string Name of the field
FieldType: TFieldType Type of the field
FieldSize: integer Size of the field
FieldID: string Unique ID of the field

Methods of TStellarDataStoreField

Method Description
class function FieldTypeNameToFieldType(AName: string): TFieldType; Converts the Stellar Data Store field type name to a Delphi TFieldType
class function FieldTypeNameToFieldSize(AName: string): integer; Gets the field size from the Stellar Data Store field name
class function FieldTypeToFieldTypeName(AType: TFieldType; ASize: integer): string; Returns the field type to use for Stellar Data Store from a Delphi field type and Size

TStellarDataStoreEntities

TStellarDataStoreEntities is a collection of TStellarDataStoreEntity. After calling TStellarDataStoreTable.GetEntities, the Entities collection is filled with data from the Stellar Data Store table

TStellarDataStoreEntity

Class holding an entity from a table

Properties of TStellarDataStoreEntity

Property Description

Methods of TStellarDataStoreEntity

Method Description
EntityID: string Holds the unique Id of the entity
Value[AName: string]: string Offers access to a value by name within the entity
Values: TStrings Offers access to the values array of the entity

TStellarDataStoreUser

Properties of TStellarDataStoreUser

Property Description
Name: string Name of the user owning the project
EMail: string Email address of the user owning the project