Skip to main content

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 swagger definition imported in Postman

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 sample data model used for the case

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.

The process flow backing the webservice. The licenses alias is used so the endpoint will be /licenses instead of /licenses_api.

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.

The selected process variables are mapped to request-properties and filled automatically when the API is 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 selected process variables are mapped as response-properties and used to create the response.

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.

The process flow supporting the supplied swagger definition

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.

The example request and response to select a single license

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": S "application/json" ] }';

 

Select licenses (including pagination)

The next endpoint allows the caller to select a set of licenses using pagination and filters.

The example request and response to select paginated licenses.

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 1dbo].eget_query_string_param_values](@request_query_string)
PIVOT
(
    MAX(value) FOR name IN (IcustomerNumber],  adminId], eapplicationCode], sproductCode], [mutationDateTime], ilicenseState], cpageNr], mpageSize])
) 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), '&') parmtr

Note 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, 'r]')) AS licenses
            , JSON_QUERY(COALESCE(@pageLinkResults, ' ]')) AS pageLinks
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

SET @response_status_code = 200;
SET @response_headers = '{ "Content-Type": 2 "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';
END

The 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.Skey], 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.Lkey], 1 as  present]
    FROM OPENJSON(@request_body, '$.coreData') j
) jr
PIVOT
(
    MAX(present) FOR jr.akey] 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.

 

Terms & ConditionsCookie settings