Tag Archives: rest api

Auto Generate Powerful REST API Client/Server Solution For RAD Server With Delphi 10.2 Tokyo

 

Auto Tables is an automatic low code REST API generator for RAD Server. The easy to use interface allows for the quick automatic configuration of a REST server and client SDK with dynamic endpoints providing read, write, and delete access to your data. Everything can be generated from your database tables in just a few clicks. Database tables can be made available with enterprise permissions for over 30 different databases including databases such as MySQL, Microsoft SQL Server, and InterBase. Additionally, you can automatically generate endpoints for over 80 more data sources using the Embarcadero Enterprise Connectors. It provides powerful API generation functionality rivaling that of DreamFactory, LoopBack, and Sandman without the complexity.

A graphical Auto Tables for RAD Server Endpoint Editor is provided to help you set up, create, and edit your Auto Tables for RAD Server endpoints. Once you configure your endpoints you can either generate a new RAD Server project or you can save them out for loading into an existing Auto Tables for RAD Server ResourceModule. The configuration format is the standard FireDAC JSON.

Full source code for the Auto Tables RAD Server ResourceModule is provided so you control your REST API server. This gives you the freedom to enhance and modify the source code as needed for your own solutions. AutoTables for RAD Server outputs a Delphi RAD Server project, a Delphi REST client project suitable for LiveBindings, a Delphi REST SDK, an OpenAPI 2.0 api spec file, and a Swagger UI documentation interface. The OpenAPI 2.0 api spec file allows you to generate clients for your REST API in the following languages: ActionScript, Ada, Apex, Bash, C#, C++, Clojure, Dart, Elixir, Elm, Eiffel, Erlang, Go, Groovy, Haskell, Java, Kotlin, Lua, Node.js, Objective-C, Perl, PHP, PowerShell, Python, R, Ruby, Rust, Scala, Swift, Typescript

Check out and download the full Auto Tables for RAD Server source code in Delphi 10.2 Tokyo over on Github.

EndPoints

Auto Tables endpoints utilize a ResourceName for the root segment and then a single dynamic segment for the specific endpoint. The default ResourceName is v1. Parameters are passed in via the query string. An example endpoint would be:

/v1/myendpoint/?PageId=1

API Versioning

You can utilize the ResourceName segment to create different versions of your API. The default name is v1 for version 1 but you can change it to v2, v3, v4 etc. when you release different versions of the same API. By utilizing version numbers you can run different versions side by side and eventually depreciate older versions.

Request Types

Auto Tables makes the GET, POST, and DELETE HTTP/S request types available as endpoints in RAD Server.

GET

If RequestType is set to GET then a standard HTTP/HTTPS request with a query string coming in will trigger that request. GET Actions include Table, SQL, AggregateSQL, and Method.

  • Table – TableName field will be used to return the full database table
  • SQL – SQL field will be used to execute the SQL query and return the result
  • AggregateSQL – SQL field will be used to execute the SQL query list and return the results
  • Method – Method field will be used to execute the custom Method

POST

If you set Request Type to POST the defined endpoint will expect a FireDAC JSON format to be submitted in the body of the request. POST Actions include Table, SQL, AggregateSQL, and Method. The FireDAC JSON will be loaded into an TFDMemTable. If the auto increment field (ID) is zero Auto Tables will create new records with the submitted rows. If the auto increment field (ID) is greater than zero Auto Tables will edit and replace the existing record with that auto increment id using the data from the submitted row.

  • Table – TableName field will be used to append or edit submitted records
  • SQL – SQL field will be used to execute a SQL query and return the result
  • AggregateSQL – SQL field will be used to execute a SQL query list and return the results
  • Method – Method field will be used to execute the custom Method

DELETE

If you set Request Type to DELETE the defined endpoint will require an auto increment field (ID) parameter to be passed to it. A DELETE SQL statement will be executed to delete the auto increment id that was passed to the endpoint. DELETE Actions include Table, SQL, AggregateSQL, and Method.

Actions

There are a number of actions that can be taken when an Auto Tables endpoint is requested. These Actions include Table, SQL, AggregateSQL, and Method.

Table

Table will automatically return a database table from an endpoint via TFDTable as FireDAC JSON.

SQL

SQL will automatically execute the set SQL statement via TFDQuery and return FireDAC JSON when the endpoint is requested. The Params field can be used to define parameters such as PageId and Limit.

AggregateSQL

Aggregate SQL is a collection of SQL statements which are all executed and returned in a single response. Each SQL statement is executed separately and its result is added to an TFDMemTable. Once all of the SQL statements have been executed the TFDMemTable is returned from the endpoint as FireDAC JSON.

Method

Method actions allows you to define a custom method for each endpoint which will be requested. The custom methods must be defined in the Auto Tables for RAD Server ResourceModule code and have direct access to AContext, ARequest, and AResponse in RAD Server.

TableName

The TableName field is used by the GET, POST, and DELETE RequestTypes. When the RequestType is GET the TableName field is used to define the name of the table that will be returned via the endpoint when the Action is set to Table. When the RequestType is POST the TableName field is used to determine on which database table to append or edit records. When the RequestType is DELETE the TableName field will be used as the database table on which the DELETE SQL statement will be executed against.

SQL

The SQL field is used to hold the SQL the SQL Action and the SQL list for the AggregateSQL Action. The SQL list for the AggregateSQL Action consists of name=value pairs where name is the name of the field that will be returned in the FireDAC JSON from the endpoint and value is the SQL statement used to fill the name field with a result. Any number of name=value pairs may be used in the AggregateSQL field limited only by the capacity of your database to execute all of the queries and the length of the SQL field itself. The SQL field accepts FireDAC style parameters in the format of :PageId which get resolved to the parameters listed in the Params field.

SQL Field Example For SQL Action

SELECT * FROM Log LIMIT :PageId,25

SQL Field Example For AggregateSQL Action

log_count=SELECT COUNT(*) FROM Log;
most_recent=SELECT last_modified as most_recent FROM Log LIMIT 0,1;
most_recent_error=SELECT message as most_recent_error FROM Log LIMIT 0,1;

Built In Default Params

There are a number of built in parameters you can use in your SQL statements. These include :UserId, :Username, :TenantId, and :Body. UserId returns the RAD Server UserId. Username returns the RAD Server Username. TenantId returns the current TenantId that is being passed in to RAD Server. And Body returns the contents of the POST body.

 

Method

The Method field is used to define the name of the custom method in the Auto Tables ResourceModule for RAD Server that will be executed via the endpoint when the Action is set to Method. You can create your own methods using the sample code below. Simply rename the customMethod() function as needed and utilize that name “customMethod” in the Method field.

function TAutoTablesResource.customMethod(AContext: TEndpointContext; ARequest: TEndpointRequest; AResponse: TEndpointResponse): TMemoryStream;

end;

 

Params

The Params field contains a comma delimited list of parameters that can be passed in to the query string on an endpoint. The parameters are made available or utilized by the various actions.

Reserved Params

The format parameter is reserved for Auto Tables to allow you to return one of 4 formats. You can pass ?format=CSV to return the data as a CSV file. You can pass ?format=XML to return the FireDAC results as XML. You can pass ?format=BINARY to return the FireDAC results as binary. And finally the default format is FireDAC JSON so any other value or an omitted format parameter will return FireDAC JSON.

Params for the SQL and AggregateSQL Actions

For the SQL and AggregateSQL action the parameters are available as parameters in the SQL query. The parameters are automatically inserted as properties and are quoted properly to handle escape characters. For example if a PageId parameter is defined in the Params field the :PageId variable would be available for use in the SQL field such as “SELECT * FROM Log LIMIT :PageId,25”.

Params for Method Actions

The parameters defined in the Params field are directly available to you via the ARequest object in the custom methods.

Macros

The Macros field contains a comma delimited list of macros that can be passed in to the query string on an endpoint. The macros can be used in the SQL field and provide access to the FireDAC Macros functionality. Macros can contain SQL database fields and are directly substituted in the SQL string.

Macros for the SQL and AggregateSQL Actions

For the SQL and AggregateSQL action the macros are available as direct string replacement macros in the SQL query. It utilizes the !macro syntax provided in FireDAC. However, the macro strings are sanitized against the list of fields in the table.

Groups

Users and Groups are already built into RAD Server. You can have users in multiple different groups. The Groups field in Auto Tables for RAD Server should contain a comma delimited list of RAD Server Groups that should have access to this endpoint. A blank Groups field would allow users who are not logged into RAD Server to access the endpoint. Utilizing this feature you can create a wide variety of permissions for access to the endpoints.

UniqueID

The UniqueID field is used to hold the UniqueID field name for the POST and DELETE RequestTypes. Both RequestTypes utilize a primary key type field to know which field in the table to use for adding, editing, and deleting records. The UniqueID field allows you to customize the name of that unique id field. By default the field name is ID.

Auto Tables for RAD Server Endpoint Editor

The Endpoint Editor allows you to automatically create, quickly customize, and easily edit all of your RAD Server endpoints. It is built in a wizard type interface with multiple easy to use steps. Everything you need to get your REST API server up and running fast is auto generated for you.

It will automatically generate a Delphi RAD Server project, a Delphi REST Client project to connect to RAD Server, and an OpenAPI (Swagger) specification file for your REST API. Additionally, you can copy the dataset component for the server endpoints to the clipboard or you can copy the REST endpoint connection components for the client to the clipboard.

Copy DataSet Component

You can copy the TFDMemTable with all of the endpoint configuration in it to the clipboard. It can then be easily pasted into an existing project.

Generated RAD Server Project

The automatically generated Auto Tables for RAD Server project has all the code already in it for handling the endpoints you have configured in the editor. The project is created from a template in the templates subdirectory. You can edit the template prior to generating your Auto Tables for RAD Server project and customize it as needed.

Copy REST Components

You can copy all of the REST components that needed to connect to the Auto Tables for RAD Server into the clipboard. They can then be easily pasted into an existing project.

Generated REST Client Project

The automatically generated Auto Tables for RAD Server REST Client project has all the code already in it for connecting to your Auto Tables for RAD Server endpoints that you have configured in the editor. The project is created from a template in the templates subdirectory. You can edit the template prior to generating your Auto Tables for RAD Server REST Client project and customize it as needed. Additionally, a Delphi SDK is also generated for the Auto Tables for RAD Server. Generation of SDKs for other languages can easily be added to the modular SDK generator.

Generated OpenAPI

In addition to the server project and client project that can be generated by editor it will also generate an OpenAPI specification document in JSON format. The OpenAPI file can be used to automatically generate SDKs or client projects using tools like OpenAPI-CodeGen or SwaggerHub to automatically create clients in a variety of languages like ActionScript, Ada, Apex, Bash, C#, C++, Clojure, Dart, Elixir, Elm, Eiffel, Erlang, Go, Groovy, Haskell, Java, Kotlin, Lua, Node.js, Objective-C, Perl, PHP, PowerShell, Python, R, Ruby, Rust, Scala, Swift, and Typescript.

The OpenAPI spec generator has a number of different fields it needs filled out to generate a propert OpenAPI spec. These fields include Title, Version, Description, Terms of Service, Contact Name, Contact Email, Contact URL, License Name, and License URL.

Check out and download the full Auto Tables for RAD Server source code in Delphi 10.2 Tokyo over on Github.

 

Tags: , , , , , , , , , , , , , ,

Hospitality Survey App REST API Client Server Template For Android, IOS, OSX, And Windows

The Hospitality Survey App template can be downloaded through Embarcadero’s GetIt which is built into the RAD Studio IDE. Peacekeeper Enterprises (in Bakersfield, CA) developed this template for Embarcadero Technologies. It consists of four different projects that interconnect with each other (plus two additional add on projects). The projects are:

  • Hospitality Survey Setup
  • Hospitality Survey EMS
  • Hospitality Survey Admin
  • Hospitality Survey Client
  • Hospitality Survey Editor
  • Hospitality Survey Admin Client

You should deploy and run the projects in the following order:

1) The Hospitality Survey Setup app will help you set up your database, the tables and data, and your EMS user accounts.

2) The Hospitality Survey EMS is the RAD Server side REST resource which both the Hospitality Survey Client and the Hospitality Survey Admin interface with. It should be deployable on Windows and Linux through IIS, Apache, or the stand alone EMS server.

3) The Hospitality Survey Client is a RAD Studio client application for Android, iOS, macOS and Windows. The client application should allow you to select a tenant from your RAD Server and then log in as a user. Once logged in it will download the survey data for your current tenant and allow the survey to be filled out. Once the survey is complete it can be submitted back to the server and a new survey can be started.

4) The Hospitality Survey Admin app is an AngularJS application for the web. You should be able to log into the application with your Tenant ID and RAD Server user ID. The web app should allow you to view statistics and graphs about the results of the survey questions and each individual question and answer. You can also export a list of emails collected from the surveys.

5) The Hospitality Survey Editor allows you to edit the survey questions once your system is live.

6) The Hospitality Survey Admin Client is the same as the Hospitality Survey Admin web app except that it runs on Android, IOS, OSX, and Windows as a native client.

In Depth Developer Guide Webinar

Hospitality Survey Setup

Start: Your EMS Server should already be setup and running.

Step 1: Set a path for your stores database. Set the path to the EMSServer database.

The stores database is created, used, and populated with data in Step 4.

The EMSServer database link is used to populate the drop down of Tenant IDs in Step 3.

Step 2: Setup the demo users on the EMS Server using the EMS API.

You will need your EMSServer Host, Port, and Tenant IDs in this step. The Setup will connect to your EMSServer and create groups and users for the template.

You can create Tenant IDs using this tutorial:

http://docwiki.embarcadero.com/RADStudio/Tokyo/en/RAD_Server_Multi-Tenancy_Support#EMS_Multi-Tenant_Console

Step 3: Customize the questions to be asked in the survey for each tenant. A blank tenant_id will be asked to all tenants.

You can customize the questions in your survey on Step 3. The fields are:

  • ID – An ID for the question.
  • name – A short name for the question with no spaces.
  • title – The text of the question as it will appear in the survey.
  • type – The type of question controls which question template is loaded on the client. The existing types are: rating, yesno, edit, options
  • options – If the type of the question is set to options this field is used to populate the options. It’s value is a JSON array of options.
  • value – The value is where the user submitted data is stored. It can be left blank but could be used to provide a default answer.
  • category – The category of the question. This field is provided for expandability.
  • tenant_id – The tenant ID of the question. If the tenant_id field is blank all tenants will get the question. If it is set to a tenant only that tenant will get the question.

Be sure to update the tenant IDs to to match your tenant IDs.

Pressing the Re-Generate button will create new INSERT queries on Step 4.

Step 4: Create the tables and insert the questions data into your stores database with the Initialize All button.

This step contains the SQL setup queries for the stores database. There are three tables which will be set up which are: SURVEYS, QUESTIONS, RESULTS

Each table will be dropped and re-created when the queries are run (any existing data in the tables will be lost). In the third TMemo field the SQL queries from your questions defined in Step 3 will appear.

Pressing the Initialize All button will execute the SQL queries against your defined stores database from Step 1.

Complete: After step 4 the databases and users should be setup and ready for you to use the client and admin areas.

Your EMS database and users should be setup at this point. You should be able to connect the Hospitality Survey EMS package to your new stores database and compile the package.

Once your Hospitality Survey EMS package is running in the EMS Development Server you should be able to open and compile the Hospitality Survey Client.

After you submit some survey results via the Hospitality Survey Client you should be able to log into the Hospitality Survey Admin and see your results.

 

Hospitality Survey EMS

The Hospitality Survey EMS is the RAD Server module for the Hospitality Survey Client and the Hospitality Survey Admin to interface with.

The end points mainly return the FireDAC JSON format which can be easily loaded in to a RAD Studio client or access via standard JSON in a non-RAD Studio environment.

Endpoints:

GET /survey/ – Download the survey questions in FireDAC JSON format.

POST /survey/complete – Upload the survey questions and answers.

GET /survey/results/* – Download the survey results in a paged fashion in FireDAC JSON format. The page number is placed where the * is.

GET /survey/stats/all – Download various stats about the survey results in FireDAC JSON format.

GET /survey/details/* – Download the questions and answers from a specific survey ID in FireDAC JSON format. The survey ID is placed where the * is.

GET /survey/emails/csv – Download a list of all of the email addresses from completed surveys in CSV format.

GET /tenants/ – Download a list of the server Tenants which are active in FireDAC JSON format.

The endpoints are defined like this in code:

[ResourceName(‘survey’)]
//
procedure Get(const AContext: TEndpointContext; const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
//
[ResourceSuffix(‘{query}/*’)]
procedure GetData(const AContext: TEndpointContext; const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
//
[ResourceSuffix(‘{query}’)]
procedure PostData(const AContext: TEndpointContext; const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);
//
//
if ARequest.Params.Values[‘query’] = ‘results’ then
if ARequest.Params.Values[‘query’] = ‘stats’ then
if ARequest.Params.Values[‘query’] = ‘details’ then
if ARequest.Params.Values[‘query’] = ’emails’ then
//
//
// TenantModule.pas
[ResourceName(‘tenants’)]
procedure Get(const AContext: TEndpointContext; const ARequest: TEndpointRequest; const AResponse: TEndpointResponse);

The variable in the /survey/results/* and /survey/details/* endpoints is accessed via ARequest.Segments object like below:

if ASegments.Count = 3 then // first segment is “survey” second is “results or “details” third segment is the variable
begin
SurveyId := ASegments.Items[2];
end;

User permissions:

User permissions are governed by the {$DEFINE GROUPPERMISSIONS}. You can comment or uncomment this line for dev and live deployments. User permissions are ignored when {$DEFINE GROUPPERMISSIONS} is commented out.

 

Hospitality Survey Admin

The Hospitality Survey Admin dashboard project which was built in AngularJS. The Admin Dashboard connects to RAD Server on the backend via a REST API. The stats that are displayed are for data that has been collected in the Hospitality Survey Client. AngularJS is a popular framework for building web applications. It uses straight Javascript and HTML as it’s template language. The data binding functionality that it offers eliminates a lot of the CRUD code you would normally have to build. AngularJS uses HTML as it’s View and Javascript as it’s Controller. You can quickly consume JSON from REST end points and display it using AngularJS.

You should be able to log into the Admin area using a Tenant ID and Tenant Secret plus the login and password for a user in the Managers Group. For this template the default user is Manager1.

Note: You must log in with your Tenant ID as the Store ID and not the Tenant Name.

Installation:

Copy index.html and the img, js, and styles subdirectories to your web server. Optimally you should place them on the same web server as your RAD Server DLL or Shared Object file. They could be placed in an admin subdirectory. You can place them on a different web server than RAD Server but you will need to make sure that your CORS Access-Control-Allow-Origin is set up correctly.

Within the js/admin.js file you should configure the $scope.ServerURL variable to point to the host where you RAD Server install is located. The default is: http://localhost:8080/

CORS Accept permissions:

Within the js/admin.js file you can configure the CORS settings for dev and live versions within the $scope.getHeader function. By default it is setup for development. When you want to switch to the live version (and enable user permissions) you can uncomment the line containing the X-Embarcadero-Session-Token and comment out the line without it. Switch back and forth between the two as needed.

There is a similar setting in the Hospitality Survey EMS server for configuring permissions for dev or live.

REST Endpoints & FireDAC

The Hospitality Survey EMS endpoint returns FireDAC tables as JSON. This allows interchangable compatibility between RAD Studio and clients like AngularJS. Here is an example of walking through the FireDAC table JSON tree to get to the records: data[“FDBS”][“Manager”][“TableList”][0][“RowList”]

 

In addition to AngularJS the Admin Dashboard also utilizes Chart.js, BootStrap, and a custom Bootstrap style. The Bootstrap style is from BootsWatch.com and you should be able to go there and get other styles. If you replace the existing style in the project with one of the other styles on BootsWatch.com it should just drop it without any other effort and you will have an entirely new look (just like how FireMonkey styles work). Chart.js is available from Chartjs.org which is a great Javascript library for displaying chart data dynamically in the browser. The Chart.js library has a special shim for AngularJS which facilitates the data exchange between AngularJS and Chart.js. Lastly, BootStrap is used which provides visual theming on HTML form elements (among other things). There is also a special shim for AngularJS which facilitates the easy integration with BootStrap.

 

Hospitality Survey Client

The Hospitality Survey Client connects to the Hospitality Survey EMS server on multiple end points for downloading and upload data. It is built to be cross platform on Android, iOS, macOS, and Windows using a single codebase and single UI.

The survey is dynamically generated based on the questions that are downloaded from the Hospitality Survey EMS server. Each question is assigned to a TFrame which displays that type of question.

Architecture:

The architecture of the app is built in a rapid application development style using TTabControl for handling pages and individual frames for each page. TActionList is used to consolidate much of the code in the MainForm.

Forms:

uMainForm.pas – Contains the main form of the application including the TTabControl with the design time frames.

uTenantListForm.pas – Contains a separate form for selecting the active Tenant ID.

DataModules:

uTenantsDM.pas – Contains the non visual components for downloading the Tenant list.

Design Time Frames:

uStoreLoginFrame.pas – Contains the store login frame.

uLoginFrame.pas – Contains the user login frame.

uMenuFrame.pas – Contains the main menu of the application including the Take Survey button.

uFinishFrame.pas – Contains the final frame of the application including the Thank You button after a survey has been completed.

uBackendFrame.pas – Contains the non-visual components for downloading survey data and uploading the results.

uProgressFrame.pas – Contains the animated progress controls which are displayed when the application is doing work.

Dynamic Frames:

uComboBoxFrame.pas – Contains the combo box survey question type.

uCompleteFrame.pas – Contains the complete button for the survey.

uEditFrame.pas – Contains the edit survey question type.

uRatingBarFrame.pas – Contains the star rating track bar survey question type.

uSurveyHeaderFrame.pas – Contains the header for the top of the survey.

uYesNoFrame.pas – Contains the Yes/No survey question type.

If you want to make changes to the design time frames be sure to edit the frame itself and not the version of it that is embedded in the MainForm. This will keep your changes consolidated in one place. If you changes don’t update in the MainForm you can delete the Frame from the MainForm and re-add it. Be sure to add it to the correct Tab and set to Align Client after you add the frame.

Note: You must log in with your Tenant ID as the Store ID and not the Tenant Name.

Credits:

Restaurant Image: http://www.freeimages.com/photo/restaurant-1233046

 

Hospitality Survey Editor

There is also a Hospitality Survey Editor app which will connect to your existing Hospitality Survey database and allow you to customize the questions which are asked in the client. The questions are stored in a table in the database and then downloaded to the client as JSON through RAD Server. The Hospitality Survey Client app loads the JSON and then dynamically creates the input fields using TFrames.

The Hospitality Survey Editor app was created by stripping down the Hospitality Survey Setup app and connecting it directly to the Hospitality Survey database.

Download the full source code for the Hospitality Survey Editor in FireMonkey for Delphi 10.2 Tokyo.

Hospitality Survey Admin Client

There is also a Hospitality Survey Admin Client that is another version of the Hospitality Survey Admin dashboard. It is built in Delphi 10.2 Tokyo instead of AngularJS and can be deployed to Android, iOS, macOS, and Windows 10. The Admin Client interface is very similar to the one found in the AngularJS version. TeeChart Lite which comes with RAD Studio was used for the charts in the business Dashboard. The survey results for each completed survey are displayed using the dynamic form code from the original client as well. The Admin Client is based off the main client and just updated to make new REST calls to the needed endpoints for the admin data.

Download the full cross platform source code of the Hospitality Survey Admin Client for Delphi 10.2 Tokyo.

Tags: , , , , , , , , ,