Indicium provides APIs for all kinds of back-end services, such as entities, tasks, reports and process flows. These APIs use the OData protocol.
However, there are scenarios where integration in the application landscape requires applications built using the Thinkwise Platform to expose an API using a different protocol.
Think of a SOAP service with a WSDL or a REST service which doesn’t conform to OData protocol. This can be an endpoint for a message bus or a high-level composite service to integrate in the landscape.
Message protocol independent web services
In version 2021.1 of the Software Factory, Message protocol independent web services in a process flows were introduced to support this.
This feature allows you to create entirely custom APIs, receive and process the HTTP requests on the database, and set the HTTP response as well.
Note: These type of webservices provide a lot of freedom and flexibility. However, matters normally handled by Indicium such as routing, request/response parsing, response codes, idempotency and security become the responsibility of the developer.
It is highly recommended to subject these services to a pen-test before using them in a production environment.
Information regarding these services can be found here.
The case
In this blog, we’ll demonstrate how to implement a protocol-independent web service based on a sample swagger definition provided to us. You can find the swagger definition attached.
We’ve loaded the swagger definition into Postman. The swagger definition tells the story of a licensing service. Various requests are possible, both for accessing data and for modifying data.
The structure clearly deviates from the OData protocol, so we’ll use a message protocol independent webservice.
The data model behind the scenes
For this case, a simple data model will be used that matches in broad strokes the information handled by the APIs of the swagger definition.
The swagger definition doesn’t disclose the cardinality between the various entities. For the case, this doesn't really matter. The important part is that the API uses normalized data whereas the data is usually stored in a denormalized fashion.
The usage of alternative keys by the API, such as application_code and customer_number, is supported by enforcing unique constraints on these columns.
Defining a message protocol independent web service
A message protocol independent web service is backed by a process flow in the Software Factory. The process flow is marked as a message protocol independent web service API.
When marked this way, the process flow can no longer be used as a regular process flow.
The name of the process flow determines the URL in Indicium. Alternatively, an alias may be used when the desired URL endpoint does not conform to the naming conventions used for your process flows.
Access to the service is based on the role rights for the process flow, so be sure to create a role and grant the process flow to the correct service account in IAM.
Request- and response variable mapping
To be able to perform the correct actions, we’ll need information of the requests. Parts of the URL, the http method and the query string parameters determine what action should be performed or what data was requested by the end user.
The information from the http-request can be mapped to the process variable. The variables will automatically receive the correct value when the API has been called.
Furthermore, process variables can be marked to act as the response that should be provided to the user; the status code, body, content-type, headers and such.
The response variables will be set throughout the process flow and the values of the response variables will dictate the response returned to the caller when the process flow has ended.
The process flow
This example only uses decision nodes, leveraging Process-logic to handle the request.
Every Process-logic procedure is provided with a try-catch template. If any unforeseen error occurs, the catch-block will clear all response variables and set the variable responsible for the response code to 500 - Internal Server Error. In a production scenario, this would be an ideal place to perform error logging and notify an administrator.
Developers are free to use any process action that could normally be part of an autonomous, schedulable process flow.
A developer may use file connectors, database connectors or application connectors to gather or store information elsewhere. AutoML models may be leveraged to assist in decision and other webservices can be called http connectors during the processing. An SMTP connector may also be used to send e-mails when the service is invoked.
Request router
The first node in the process flow is used as a router. This determines what operation is used by the caller. This node will inspect the method and the URL to determine the operation.
It would be possible to process the entire request in a single decision node. However, we’ve chosen to split the handling based on the various operations to increase readability and maintainability. This also allows for more focused unit tests on the process logic.
If possible, it will set the license_id process variable when the operation concerns a single license.
Note that this decision note may also choose to end the flow if the request cannot be routed by navigating to the Stop flag. If this is the case, the variable providing the response code will be set to 400 - Bad Request.
Click ‘Show content’ to reveal the template responsible for the routing:
-- Initializing all routing endpoints as NULL
SET @request_router_select_licenses_with_pagination = NULL;
SET @request_router_delete_license = NULL;
SET @request_router_partially_update_license = NULL;
SET @request_router_select_single_license = NULL;
SET @request_router_insert_or_update_a_license = NULL;
SET @request_router_select_license_for_application_and_product = NULL;
SET @request_router_select_applications_for_license = NULL;
SET @request_router_stop = NULL;
-- Routing GET requests
IF @request_method = 'GET'
BEGIN
-- Routing the /licenses, /licenses/ and /licenses?querystring requests to the 'select_licenses_with_pagination' endpoint
IF PATINDEX('%/licenses', @request_path) > 0 OR PATINDEX('%/licenses/', @request_path) > 0 OR PATINDEX('%/licenses?', @request_path) > 0
BEGIN
SET @request_router_select_licenses_with_pagination = 1;
RETURN;
END
-- Routing the /licenses/check requests to the 'select_license_for_application_and_product' endpoint.
IF PATINDEX('%/licenses/check/%', @request_path) > 0
BEGIN
SET @request_router_select_license_for_application_and_product = 1;
RETURN;
END
-- Routing the /licenses/accesses requests to the 'select_applications_for_license' endpoint.
IF PATINDEX('%/licenses/accesses/%', @request_path) > 0
BEGIN
SET @request_router_select_applications_for_license = 1;
RETURN;
END
-- Routing the rest of the /licenses/X requests
IF PATINDEX('%/licenses/_%', @request_path) > 0
BEGIN
-- Parsing the license ID from the request path.
SET @license_id = dbo.get_license_id_from_path(@request_path);
-- Setting the Bad Request response and stopping the process flow because the path did not contain a valid license ID.
IF @license_id IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Invalid request path, expected a path like ''/licenses/<guid>''';
SET @request_router_stop = 1;
RETURN;
END
-- If the license ID was valid then we'll route the request to the 'select_single_license' endpoint.
SET @request_router_select_single_license = 1;
END
END
-- Routing PATCH requests
ELSE IF @request_method = 'PATCH'
BEGIN
-- Parsing the license ID from the request path.
SET @license_id = dbo.get_license_id_from_path(@request_path);
-- Setting the Bad Request response and stopping the process flow because the path did not contain a valid license ID.
IF @license_id IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Invalid request path, expected a path like ''/licenses/<guid>''';
SET @request_router_stop = 1;
RETURN;
END
-- If the license ID was valid then we'll route the request to the 'select_single_license' endpoint.
SET @request_router_partially_update_license = 1;
END
-- Routing DELETE requests
ELSE IF @request_method = 'DELETE'
BEGIN
-- Parsing the license ID from the request path.
SET @license_id = dbo.get_license_id_from_path(@request_path);
-- Setting the Bad Request response and stopping the process flow because the path did not contain a valid license ID.
IF @license_id IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Invalid request path, expected a path like ''/licenses/<guid>''';
SET @request_router_stop = 1;
RETURN;
END
-- If the license ID was valid then we'll route the request to the 'select_single_license' endpoint.
SET @request_router_delete_license = 1;
END
-- Routing PUT requests
ELSE IF @request_method = 'PUT'
BEGIN
-- Ensuring that the request path is /licenses or /licenses/ and otherwise setting a Bad Request response and stopping the process flow
IF PATINDEX('%/licenses%', @request_path) < 0 OR PATINDEX('%/licenses/_%', @request_path) > 0
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Invalid request path, expected the path to be ''/licenses''';
SET @request_router_stop = 1;
RETURN;
END
-- If the request path is valid, route the request to the 'insert_or_update_a_license' endpoint.
SET @request_router_insert_or_update_a_license = 1;
ENDÂ
Select license by licenseId
When the process flow arrives at the single-license endpoint, we’ll have to load the desired license and transform it to a valid JSON response.
As can be seen, the swagger definition leaves the mutationDateTime out of the inner coreData element of a license and provides a links-segment to refer to this single license. The procedure also ensures the data is normalized.
When the license cannot be found, a 404 response code must be returned.
In both cases, a content-type header with value application/json is added.
SQL Server provides solid JSON support to help us here. The template to select the license and transform this to match the swagger definition looks as following:
IF EXISTS(SELECT 1 FROM license WHERE license_id = @license_id)
BEGIN
-- Fetching the license as a JSON object and setting the response with it.
SET @response_body =
(
SELECT l.license_id AS 'coreData.licenseId'
, c.customer_number AS 'coreData.customerNumber'
, l.admin_id AS 'coreData.adminId'
, a.application_code AS 'coreData.applicationCode'
, p.product_code AS 'coreData.productCode'
, l.start_date AS 'coreData.startDate'
, l.end_date AS 'coreData.endDate'
, l.reference AS 'coreData.reference'
, l.quantity AS 'coreData.quantity'
, l.license_state AS 'coreData.licenseState'
, l.mutation_date_time AS 'mutationDateTime'
, (
SELECT 'License ' + l.license_id AS 'relation'
, '/licenses/' + l.license_id AS 'href'
, 'GET' AS 'action'
FOR JSON PATH
) AS 'links'
FROM license l
JOIN customer c
ON l.customer_id = c.customer_id
JOIN application a
ON l.application_id = a.application_id
JOIN product p
ON l.application_id = p.application_id
AND l.product_id = p.product_id
WHERE l.license_id = @license_id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
SET @response_status_code = 200;
END
-- If the license record does not exist, return a 404 status code.
ELSE
BEGIN
SET @response_status_code = 404;
END
SET @response_headers = '{ "Content-Type": [ "application/json" ] }';Â
Select licenses (including pagination)
The next endpoint allows the caller to select a set of licenses using pagination and filters.
Providing this functionality consists of several steps.
The first step is to load the pagination and filters. The query string is mapped to the request_query_string variable.Â
Default values are applied here as well; The swagger definition defined that the page should fall back to page 1 when no page is provided and the page size should fall back to 50 when no page size is provided.
-- Declaring variables for the query string parameters.
DECLARE @customerNumber name;
DECLARE @adminId name;
DECLARE @applicationCode name;
DECLARE @productCode name;
DECLARE @mutationDateTime name;
DECLARE @licenseState name;
DECLARE @pageIndex int;
DECLARE @pageSize int;
-- Parsing the query string parameters into their corresponding variables
SELECT @customerNumber = t1.customerNumber
, @adminId = t1.adminId
, @applicationCode = t1.applicationCode
, @productCode = t1.productCode
, @mutationDateTime = t1.mutationDateTime
, @licenseState = t1.licenseState
, @pageIndex = COALESCE(t1.[pageNr], 1) -- default to 1
, @pageSize = COALESCE(t1.[pageSize], 50) -- default to 50
FROM [dbo].[get_query_string_param_values](@request_query_string)
PIVOT
(
MAX(value) FOR name IN ([customerNumber], [adminId], [applicationCode], [productCode], [mutationDateTime], [licenseState], [pageNr], [pageSize])
) t1Â
A function is used to parse the key-value pairs from the query string to a list, which is then pivoted to set the desired variables.
-- Strip the leading '?' character from @query_string
-- Split the @query_string by the '&' character
-- Split each result by the '=' character return the left side as 'name' and the right side as 'value'.
RETURN
SELECT LEFT(parmtr.value, CHARINDEX('=', parmtr.value) - 1) AS name
, RIGHT(parmtr.value, LEN(parmtr.value) - CHARINDEX('=', parmtr.value)) AS value
FROM STRING_SPLIT(RIGHT(@query_string, LEN(@query_string) - 1), '&') parmtrNote that the code for this function is quite basic. It does not provide any form of URL-escaping and would need further adjustments to work for scenarios outside of the scope of this case.
When the pagination and filters are clear, we can load the actual data from the database using the supplied filters and pagination.
-- Fetching all license results as a JSON array.
DECLARE @licensesResult VARCHAR(MAX);
SET @licensesResult =
(
SELECT l.license_id AS 'coreData.licenseId'
, c.customer_number AS 'coreData.customerNumber'
, l.admin_id AS 'coreData.adminId'
, a.application_code AS 'coreData.applicationCode'
, p.product_code AS 'coreData.productCode'
, l.start_date AS 'coreData.startDate'
, l.end_date AS 'coreData.endDate'
, l.reference AS 'coreData.reference'
, l.quantity AS 'coreData.quantity'
, l.license_state AS 'coreData.licenseState'
, l.mutation_date_time AS 'mutationDateTime'
, (
SELECT 'License ' + l.license_id AS 'relation'
, '/licenses/' + l.license_id AS 'href'
, 'GET' AS 'action'
FOR JSON PATH
) AS 'links'
FROM license l
JOIN customer c
ON l.customer_id = c.customer_id
JOIN application a
ON l.application_id = a.application_id
JOIN product p
ON l.application_id = p.application_id
AND l.product_id = p.product_id
WHERE (@customerNumber IS NULL OR c.customer_number = @customerNumber)
AND (@adminId IS NULL OR l.admin_id = @adminId)
AND (@applicationCode IS NULL OR a.application_code = @applicationCode)
AND (@productCode IS NULL OR p.product_code = @productCode)
AND (@mutationDateTime IS NULL OR l.mutation_date_time = @mutationDateTime)
AND (@licenseState IS NULL OR @licenseState = 'ALL' OR l.license_state = @licenseState)
ORDER BY l.license_id ASC
OFFSET (@pageIndex - 1) * @pageSize ROWS FETCH NEXT @pageSize ROWS ONLY
FOR JSON PATH
);Â
The next step is constructing the links segment containing the first, previous, current, next and last page. To determine these values, we first need to determine the number of records given the current filters.
-- Determining the number of records given the current filters.
DECLARE @nrOfRowsWithFilters INT;
SET @nrOfRowsWithFilters =
(
SELECT COUNT(*)
FROM license l
JOIN customer c
ON l.customer_id = c.customer_id
JOIN application a
ON l.application_id = a.application_id
JOIN product p
ON l.application_id = p.application_id
AND l.product_id = p.product_id
WHERE (@customerNumber IS NULL OR c.customer_number = @customerNumber)
AND (@adminId IS NULL OR l.admin_id = @adminId)
AND (@applicationCode IS NULL OR a.application_code = @applicationCode)
AND (@productCode IS NULL OR p.product_code = @productCode)
AND (@mutationDateTime IS NULL OR l.mutation_date_time = @mutationDateTime)
AND (@licenseState IS NULL OR @licenseState = 'ALL' OR l.license_state = @licenseState)
);Â
Once the total number of licenses given the provided filters is known, we can construct the page links. We reuse the existing query string to retain the filters when referring to the other pages. A function is used for this, which also ensures the pagination is added even if the fallback to page 1 or page size 50 was used.
-- Calculating what the last page index is given the number of records and page size.
DECLARE @lastPage INT = CONVERT(INT, CEILING(CONVERT(decimal, @nrOfRowsWithFilters) / CONVERT(decimal, @pageSize)));
-- Producing the pageLinks JSON array given the current page index.
DECLARE @pageLinkResults VARCHAR(MAX);
SET @pageLinkResults =
(
SELECT pageLinks.relation, pageLinks.href, pageLinks.action
FROM
(
SELECT 0 as order_no
, 'first' AS relation
, '/licenses' + dbo.get_page_query_string(@request_query_string, 1, @pageSize) AS href
, 'GET' AS action
UNION ALL
SELECT 1 as order_no
, 'previous' AS relation
, '/licenses' + dbo.get_page_query_string(@request_query_string, @pageIndex - 1, @pageSize) AS href
, 'GET' AS action
WHERE @pageIndex > 1
UNION ALL
SELECT 2 as order_no
, 'self' AS relation
, '/licenses' + dbo.get_page_query_string(@request_query_string, @pageIndex, @pageSize) AS href
, 'GET' AS action
UNION ALL
SELECT 3 as order_no
, 'next' AS relation
, '/licenses' + dbo.get_page_query_string(@request_query_string, @pageIndex + 1, @pageSize) AS href
, 'GET' AS action
WHERE @pageIndex < @lastPage
UNION ALL
SELECT 4 as order_no
, 'last' AS relation
, '/licenses' + dbo.get_page_query_string(@request_query_string, @lastPage, @pageSize) AS href
, 'GET' AS action
) AS pageLinks
WHERE @lastPage != 0
ORDER BY pageLinks.order_no ASC
FOR JSON PATH
)Â
The last part is combining the licenses data and the page links into a single response body. Also, the response status code and content type is set.
-- Creating the final JSON response, coalescing both JSON arrays to an empty JSON array if null
SET @response_body =
(
SELECT JSON_QUERY(COALESCE(@licensesResult, '[]')) AS licenses
, JSON_QUERY(COALESCE(@pageLinkResults, '[]')) AS pageLinks
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);
SET @response_status_code = 200;
SET @response_headers = '{ "Content-Type": [ "application/json" ] }';Â
Â
Insert or update license
When inserting or updating a license, the data is first loaded from the body.
-- Parsing all license properties from the request body to variables.
DECLARE @bodyLicenseId name = JSON_VALUE(@request_body, '$.coreData.licenseId');
DECLARE @bodyCustomerNumber name = JSON_VALUE(@request_body, '$.coreData.customerNumber');
DECLARE @bodyAdminId name = JSON_VALUE(@request_body, '$.coreData.adminId');
DECLARE @bodyApplicationCode name = JSON_VALUE(@request_body, '$.coreData.applicationCode');
DECLARE @bodyProductCode name = JSON_VALUE(@request_body, '$.coreData.productCode');
DECLARE @bodyStartDate name = JSON_VALUE(@request_body, '$.coreData.startDate');
DECLARE @bodyEndDate name = JSON_VALUE(@request_body, '$.coreData.endDate');
DECLARE @bodyReference name = JSON_VALUE(@request_body, '$.coreData.reference');
DECLARE @bodyQuantity amount = JSON_VALUE(@request_body, '$.coreData.quantity');
DECLARE @bodyLicenseState name = JSON_VALUE(@request_body, '$.coreData.licenseState');Â
We want to ensure the mandatory fields are provided. Alternative keys for foreign keys are resolved to their actual foreign key value. If any of the checks fail, we return a 400 - bad request status code with a message in the response body.
-- Checking if any mandatory properties are explicitly set to null, if so, a bad request response will be set.
IF @bodyLicenseId IS NULL
OR @bodyCustomerNumber IS NULL
OR @bodyAdminId IS NULL
OR @bodyApplicationCode IS NULL
OR @bodyProductCode IS NULL
OR @bodyStartDate IS NULL
OR @bodyLicenseState IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Missing one or more mandatory properties. The properties ''licenseId'', ''customerNumber'', ''adminId'', ''applicationCode'', ''productCode'', ''startDate'' and ''licenseState'' are mandatory.';
RETURN;
END
-- Fetching the customer_id, application_id and product_id corresponding to the values provided in the JSON request body.
DECLARE @customerId INT = (SELECT customer_id FROM customer WHERE customer_number = @bodyCustomerNumber);
IF @customerId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find a customer with customerNumber ''' + @bodyCustomerNumber + ''' ';
RETURN;
END
DECLARE @applicationId INT = (SELECT application_id FROM application WHERE application_code = @bodyApplicationCode);
IF @applicationId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find an application with applicationCode ''' + @bodyApplicationCode + ''' ';
RETURN;
END
DECLARE @productId INT = (SELECT product_id FROM product WHERE application_id = @applicationId AND product_code = @bodyProductCode);
IF @productId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find a product with productCode ''' + @bodyProductCode + ''' ';
RETURN;
ENDÂ
At this point, the provided values are considered valid. The license can now be updated if it exists or inserted if it does not yet exist.
-- If the license already exists, then update it
IF EXISTS(SELECT 1 FROM license WHERE license_id = @bodyLicenseId)
BEGIN
UPDATE l
SET l.customer_id = @customerId
, l.admin_id = @bodyAdminId
, l.application_id = @applicationId
, l.product_id = @productId
, l.start_date = @bodyStartDate
, l.end_date = @bodyEndDate
, l.reference = @bodyReference
, l.quantity = @bodyQuantity
, l.license_state = @bodyLicenseState
, l.mutation_date_time = CONVERT(VARCHAR(50), GETDATE(), 126) + 'Z'
FROM license l
WHERE license_id = @bodyLicenseId;
set @response_status_code = 200;
set @response_body = 'License updated';
END
-- If the license does not exist, then insert it.
ELSE
BEGIN
INSERT INTO license (
license_id
, customer_id
, admin_id
, application_id
, product_id
, start_date
, end_date
, reference
, quantity
, license_state
, mutation_date_time
)
VALUES (
@bodyLicenseId
, @customerId
, @bodyAdminId
, @applicationId
, @productId
, @bodyStartDate
, @bodyEndDate
, @bodyReference
, @bodyQuantity
, @bodyLicenseState
, CONVERT(VARCHAR(50), GETDATE(), 126) + 'Z'
);
SET @response_status_code = 200;
SET @response_body = 'License created';
ENDThe response body is used to inform the caller about the updated or inserted license and a success response code is set.
Â
Partially update a license
For a partial update, we first have to find out the values that have been included in the request body. This can be a bit tricky, we need to distinguish between an omitted property and a property set to null.
To illustrate - First, we load the values from the json into variables.
-- Declaring variables for all properties in the JSON request body.
DECLARE @bodyLicenseId name;
DECLARE @bodyCustomerNumber name;
DECLARE @bodyAdminId name;
DECLARE @bodyApplicationCode name;
DECLARE @bodyProductCode name;
DECLARE @bodyStartDate name;
DECLARE @bodyEndDate name;
DECLARE @bodyReference name;
DECLARE @bodyQuantity name;
DECLARE @bodyLicenseState name;
-- Parsing the JSON request body to the corresponding variables.
SELECT @bodyLicenseId = p.licenseId
, @bodyCustomerNumber = p.customerNumber
, @bodyAdminId = p.adminId
, @bodyApplicationCode = p.applicationCode
, @bodyProductCode = p.productCode
, @bodyStartDate = p.startDate
, @bodyEndDate = p.endDate
, @bodyReference = p.reference
, @bodyQuantity = p.quantity
, @bodyLicenseState = p.licenseState
FROM
(
SELECT j.[key], j.[value]
FROM OPENJSON(@request_body, '$.coreData') j
) jr
PIVOT
(
MAX(jr.[value]) FOR jr.[key] IN (licenseId, customerNumber, adminId, applicationCode, productCode, startDate, endDate, reference, quantity, licenseState)
) pÂ
Note that we’ll get null-values in the variables when a property doesn’t exist and when a json-property has an explicit null value.
Since we need to distinguish between the two, we use OPENJSON again to inspect which keys are present. We store which keys were present in variables.
-- Declare presence of all properties in the JSON request body.
DECLARE @hasLicenseId bit;
DECLARE @hasCustomerNumber bit;
DECLARE @hasAdminId bit;
DECLARE @hasApplicationCode bit;
DECLARE @hasProductCode bit;
DECLARE @hasStartDate bit;
DECLARE @hasEndDate bit;
DECLARE @hasReference bit;
DECLARE @hasQuantity bit;
DECLARE @hasLicenseState bit;
SELECT @hasLicenseId = p.licenseId
, @hasCustomerNumber = p.customerNumber
, @hasAdminId = p.adminId
, @hasApplicationCode = p.applicationCode
, @hasProductCode = p.productCode
, @hasStartDate = p.startDate
, @hasEndDate = p.endDate
, @hasReference = p.reference
, @hasQuantity = p.quantity
, @hasLicenseState = p.licenseState
FROM
(
SELECT j.[key], 1 as [present]
FROM OPENJSON(@request_body, '$.coreData') j
) jr
PIVOT
(
MAX(present) FOR jr.[key] IN (licenseId, customerNumber, adminId, applicationCode, productCode, startDate, endDate, reference, quantity, licenseState)
) pÂ
Some fields of the license may not be set to null. We’ll check this in the next step using the variable indicating the presence of the property and using the variable with the actual value. If a value is omitted for a mandatory property, we return a 400 - Bad Request.
-- Checking if any mandatory properties are explicitly set to null, if so, a bad request response will be set.
IF (@hasLicenseId = 1 and @bodyLicenseId is null )
OR (@hasCustomerNumber = 1 and @bodyCustomerNumber is null )
OR (@hasAdminId = 1 and @bodyAdminId is null )
OR (@hasApplicationCode = 1 and @bodyApplicationCode is null )
OR (@hasProductCode = 1 and @bodyProductCode is null )
OR (@hasStartDate = 1 and @bodyStartDate is null )
OR (@hasLicenseState = 1 and @bodyLicenseState is null )
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Missing value for one or more mandatory properties. The properties ''licenseId'', ''customerNumber'', ''adminId'', ''applicationCode'', ''productCode'', ''startDate'' and ''licenseState'' are mandatory.';
RETURN;
ENDÂ
Next up we’ll resolve the alternative keys to actual foreign keys. The data model indicates the application_id and the product_id are mutually dependent so we need to perform some extra checks for various scenarios.
For instance, when only the application_id will be updated, the current product_id of the license will still need to be compatible.
DECLARE @customerId INT;
IF @hasCustomerNumber = 1
BEGIN
SELECT @customerId = customer_id
FROM customer
WHERE customer_number = @bodyCustomerNumber;
IF @customerId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find a customer with customerNumber ''' + @bodyCustomerNumber + ''' ';
RETURN;
END
END
DECLARE @applicationId INT;
IF @hasApplicationCode = 1
BEGIN
SELECT @applicationId = application_id
FROM application
WHERE application_code = @bodyApplicationCode;
IF @applicationId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find an application with applicationCode ''' + @bodyApplicationCode + ''' ';
RETURN;
END
END
DECLARE @productId INT;
IF @hasProductCode = 1 AND @hasApplicationCode = 1
BEGIN
-- Check the provided product code for the provided application
SELECT @productId = product_id
FROM product
WHERE application_id = @applicationId
AND product_code = @bodyProductCode;
IF @productId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find a product with productCode ''' + @bodyProductCode + ''' for applicationCode ''' + @bodyApplicationCode + ''' ';
RETURN;
END
END
ELSE IF @hasProductCode = 1
BEGIN
-- Check the provided product code for the current application of the license
SELECT @productId = p.product_id
FROM license l
JOIN product p
ON p.application_id = l.application_id
WHERE l.license_id = @license_id
AND p.product_code = @bodyProductCode;
IF @productId IS NULL
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to find a product with productCode ''' + @bodyProductCode + ''' ';
RETURN;
END
END
ELSE IF @hasApplicationCode = 1
BEGIN
-- Check if the application id can be changed while retaining the current product code
IF NOT EXISTS(
SELECT 1
FROM license l
JOIN product p
ON p.product_id = l.product_id
WHERE l.license_id = @license_id
AND p.application_id = @applicationId
)
BEGIN
SET @response_status_code = 400;
SET @response_body = 'Unable to apply applicationCode ''' + @bodyApplicationCode + ''' as it conflicts with the current productCode';
RETURN;
END
ENDÂ
Note that you can also rely on the database to perform integrity and mandatory checks, using a try-catch-block to report errors back to the caller.
After we’ve verified the incoming data, we can perform the partial update.
-- Updating the license with the license ID specified in the request path.
UPDATE l
SET l.license_id = IIF(@hasLicenseId = 1, @bodyLicenseId, l.license_id)
, l.customer_id = IIF(@hasCustomerNumber = 1, @customerId, l.customer_id)
, l.admin_id = IIF(@hasAdminId = 1, @bodyAdminId, l.admin_id)
, l.application_id = IIF(@hasApplicationCode = 1, @applicationId, l.application_id)
, l.product_id = IIF(@hasProductCode = 1, @productId, l.product_id)
, l.start_date = IIF(@hasStartDate = 1, @bodyStartDate, l.start_date)
, l.end_date = IIF(@hasEndDate = 1, @bodyEndDate, l.end_date)
, l.reference = IIF(@hasReference = 1, @bodyReference, l.reference)
, l.quantity = IIF(@hasQuantity = 1, @bodyQuantity, l.quantity)
, l.license_state = IIF(@hasLicenseState = 1, @bodyLicenseState, l.license_state)
FROM license l
WHERE l.license_id = @license_id
SET @response_status_code = 200;
SET @response_body = 'License updated.';Â
Delete a license
Deleting a license is one of the more easy endpoints to implement. When the request is routed here, we have the license_id variable available already so we can simply perform the delete.
DELETE
FROM license
WHERE license_id = @license_id;
SET @response_status_code = 200;
SET @response_body = 'License deleted';Â
Â
On to part 2 of 2!
So far, we’ve implemented 4 out of the 6 endpoints. This marks the end of part 1 of this case. We’ll continue in a follow-up blog with the last two endpoints and a demonstration where we test the service.
Case: A message protocol-independent web service (part 2Â of 2)
Feel free to leave a reply or comments at part 2.
Â