MyCodeIdeas: July 2016

Sunday, July 24, 2016

WSO2 DSS - Dynamic SQL Query

Dynamic SQL query support allows you to change SQL queries (e.g., defining additional conditions in the SQL) in the runtime without changing the data service configuration. For this to work, you must specify required SQL query statements (e.g., with WHERE clause) as a QUERY_STRING data type.

Example Service is as follows.

<data name="DynamicQueryTest" transports="http https local">
   <config enableOData="false" id="TestDB">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="DynamicQuery" useConfig="TestDB">
      <sql>Select :columns from :table :whereclause</sql>
      <result element="EmployeeData" rowName="Employee">
         <element column="EMPLOYEENUMBER" name="EMPLOYEENUMBER" optional="true" xsdType="string"/>
         <element column="LASTNAME" name="LASTNAME" optional="true" xsdType="string"/>
         <element column="FIRSTNAME" name="FIRSTNAME" optional="true" xsdType="string"/>
         <element column="EXTENSION" name="EXTENSION" optional="true" xsdType="string"/>
      </result>
      <param name="columns" sqlType="QUERY_STRING"/>
      <param name="table" sqlType="QUERY_STRING"/>
      <param name="whereclause" sqlType="QUERY_STRING"/>
   </query>
   <operation name="DynamicQueryTest">
      <call-query href="DynamicQuery">
         <with-param name="columns" query-param="columns"/>
         <with-param name="table" query-param="table"/>
         <with-param name="whereclause" query-param="whereclause"/>
      </call-query>
   </operation>
   <resource method="GET" path="DynamicTestRes">
      <call-query href="DynamicQuery">
         <with-param name="columns" query-param="columns"/>
         <with-param name="table" query-param="table"/>
         <with-param name="whereclause" query-param="whereclause"/>
      </call-query>
   </resource>
</data>

Note : Since column names are also dynamic, in the output mapping all possible column names are defined with optional parameter.

There you can invoke the "DynamicQueryTest" operation as follows.

<body>
   <p:DynamicQueryTest xmlns:p="http://ws.wso2.org/dataservice">
      <!--Exactly 1 occurrence-->
      <p:columns>EMPLOYEENUMBER,LASTNAME</p:columns>
      <!--Exactly 1 occurrence-->
      <p:table>EMPLOYEES</p:table>
      <!--Exactly 1 occurrence-->
      <p:whereclause>where EMPLOYEENUMBER > 1</p:whereclause>
   </p:DynamicQueryTest>
</body>

Rest operation can be invoked as follows.

https://172.17.0.1:9443/services/DynamicQueryTest.SecureHTTPEndpoint/DynamicTestRes?columns=LastName&table=Employees&whereclause=where EMPLOYEENUMBER > 1

Dynamic query support can lead to SQL injection attacks. Therefore, it is recommended that the clients validate the values set to QUERY_STRING at runtime.

References : WSO2 DSS - Dynamic SQL Query

Thursday, July 21, 2016

Custom Validators with WSO2 DSS

Input validation allows data services to validate the input parameter values that are presented in a request. This allows to stops the execution of the request, if the input has not met the required criteria. WSO2 Data Services Server provides a set of built-in validators which will be sufficient for the mostly used use cases. And also it provides an extension mechanism, where custom validators can be written.
So input validators in WSO2 DSS can be categorized as follows.

Built-in validators

Long Range Validator- Validates if an integer value is in the specified range.
Double Range Validator- Validates if an integer value is in the specified range.
Length Validator- Validates the string length of a given parameter against a specified length.
Pattern Validator- Validates the string value of the parameter against a given regular expression.

Custom Validator- allows the user to instroduce custom validation logic by adding Custom Validators.

In this post custom validators will be discussed in detail.

For defining a custom validator, the user must implement the interface "org.wso2.carbon.dataservices.core.validation.Validator" or the "org.wso2.carbon.dataservices.core.validation.ValidatorExt". If the validation fails, the validate method in the interface by default throws an exception of type ValidationException .

The definition of the Validator interface is as follows.

public interface Validator {
    public void validate(ValidationContext context, String name, ParamValue value) throws ValidationException;
}

ValidatorExt interface is introduced from the DSS 3.5.1 release onwards. If the custom validator needs to be initialized by providing any properties, it is required to implement the interface ValidatorExt. It extends the Validator interface and the definition of the interface is as follows.

public interface ValidatorExt extends Validator {
    public void init(Map<String, String> props);
}

A sample Java code for Custom Validator class which implements the ValdiatorExt is as follows. You can add any validation logic as required.

package org.acme;

import org.wso2.carbon.dataservices.core.engine.ParamValue;
import org.wso2.carbon.dataservices.core.validation.ValidationContext;
import org.wso2.carbon.dataservices.core.validation.ValidationException;
import org.wso2.carbon.dataservices.core.validation.ValidatorExt;

import java.util.Map;

public class EmployeeEmailValidator implements ValidatorExt {
    String domain;
    int maxLength;

    @Override public void validate(ValidationContext context, String name, ParamValue value)
            throws ValidationException {

        if (!isEmpty(value)) {
            String email = value.getScalarValue();
            String[] emailParts = email.split("@");
            
            String partUser;
            String partDomain;

            if (emailParts.length == 2) {
                partUser = emailParts[0]; 
                partDomain = emailParts[1]; 
            } else {
                throw new ValidationException("Invalid Email Format", name, value);
            }

            //Validate for the property "domain"
            if (!partDomain.equals(domain)) {
                throw new ValidationException("Invalid Email Domain", name, value);
            }

            //Validate for the property "maxLength"
            if (partUser.length() > maxLength) {
                throw new ValidationException("Invalid Email Length", name, value);
            }

        } else {
            throw new ValidationException("Email should not be empty", name, value);
        }
    }

    @Override public void init(Map<String, String> map) {
        domain = map.get("domain");
        String len = map.get("maxLength");
        if (len != null) {
            maxLength = Integer.parseInt(len);
        }
    }

    private boolean isEmpty(ParamValue val) {
        return val == null || val.getScalarValue() == null || val.getScalarValue().length() == 0;
    }
}

The class structure for the above class is as follows.

You can complie and create the jar file as follows. When doing the compile, you need to point your build path to org.wso2.carbon.dataservices.core_x.x.x.jar file.

javac -d "classes" -cp /home/testuser/DSS/wso2dss-3.5.1/repository/components/plugins/org.wso2.carbon.dataservices.core_4.3.5.jar org/acme/EmployeeEmailValidator.java
jar cvf emailvalidator.jar -C classes/ .

Then copy the created jar file to the wso2dss-x.y.z/repository/components/lib folder and restart the service. Then you can create the data service which use this custom validator.

When adding the validator you can add the custom properties via the UI as follows.

The complete data services definition for the above example is as follows.

<data name="AddEmployeeDS" transports="http https local">
   <config enableOData="false" id="TestDB">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="addEmployeeQuery" useConfig="TestDB">
      <sql>insert into Employees (employeeNumber,lastName,firstName,email,salary) values(:employeeNumber,:lastName,:firstName,:email,:salary)</sql>
      <param name="employeeNumber" sqlType="STRING"/>
      <param name="lastName" sqlType="STRING"/>
      <param name="firstName" sqlType="STRING"/>
      <param name="email" sqlType="STRING">
         <validateCustom class="org.acme.EmployeeEmailValidator">
            <properties>
               <property name="maxLength">5</property>
               <property name="domain">abc.com</property>
            </properties>
         </validateCustom>
      </param>
      <param name="salary" sqlType="STRING"/>
   </query>
   <operation name="AddEmployee">
      <call-query href="addEmployeeQuery">
         <with-param name="employeeNumber" query-param="employeeNumber"/>
         <with-param name="lastName" query-param="lastName"/>
         <with-param name="firstName" query-param="firstName"/>
         <with-param name="email" query-param="email"/>
         <with-param name="salary" query-param="salary"/>
      </call-query>
   </operation>
</data>

Now service creation is completed and you can test. For example if you invoke the AddEmployee operation with invalid email length you will receive a response like below.

References: https://docs.wso2.com/display/DSS350/Adding+Input+Mappings#AddingInputMappings-Customvalidators

Sunday, July 3, 2016

WSO2 DSS - Working With Nested Queries

Nested queries help you to use the result of one query as an input parameter of another, and the queries executed in a nested query works in a transactional manner.

You can use the default h2 database tables comes with the WSO2 DSS to run these samples.

Nested Query Sample with XML Output Mapping

<data name="TestNested" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result element="Customer">
         <element column="CUSTOMERNAME" name="Name"/>
      </result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result element="Entries" rowName="Entry">
         <element column="ORDERNUMBER" name="ORDERNUMBER" xsdType="string"/>
         <element column="ORDERDATE" name="ORDERDATE" xsdType="string"/>
         <element column="STATUS" name="STATUS" xsdType="string"/>
         <element column="CUSTOMERNUMBER" name="CUSTOMERNUMBER" xsdType="string"/>
         <call-query href="CustomerNameSQL" requiredRoles="">
            <with-param name="customerNumberin" query-param="customerNumber"/>
         </call-query>
      </result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>
http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

Request:

http://172.17.0.1:9763/services/TestNested.HTTPEndpoint/orderlist

Sample Response:

<Entries xmlns="http://ws.wso2.org/dataservice">
   <Entry>
      <ORDERNUMBER>10100</ORDERNUMBER>
      <ORDERDATE>2003-01-06+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
      <Customer>
         <Name>Online Diecast Creations Co.</Name>
      </Customer>
   </Entry>
   <Entry>
      <ORDERNUMBER>10101</ORDERNUMBER>
      <ORDERDATE>2003-01-09+06:00</ORDERDATE>
      <STATUS>Shipped</STATUS>
      <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
      <Customer>
         <Name>Blauer See Auto, Co.</Name>
      </Customer>
   </Entry>
</Entries>

Nested Query Sample with JSON output Mapping

Same above service but with json output mapping is as follows.
The format of the mapping should be as follows.
"@target_query_name": "$nested_query_parameter->outer_Query_result_element_name"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>

Request:

http://172.17.0.1:9763/services/TestNestedJSON.HTTPEndpoint/orderlist

Response:

with header Accept: applicaton/json

{
    "Entries": {
        "Entry": [{
            "Customers": {
                "Customer": [{
                    "Name": "Online Diecast Creations Co."
                }]
            },
            "ORDERNUMBER": "10100",
            "CUSTOMERNUMBER": "363",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-06+06:00"
        }, {
            "Customers": {
                "Customer": [{
                    "Name": "Blauer See Auto, Co."
                }]
            },
            "ORDERNUMBER": "10101",
            "CUSTOMERNUMBER": "128",
            "STATUS": "Shipped",
            "ORDERDATE": "2003-01-09+06:00"
        }]
    }
}

Without Accept:application/json header

<Entries xmlns="http://ws.wso2.org/dataservice/CustomerOrderSQL">
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Online Diecast Creations Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10100</ORDERNUMBER>
    <CUSTOMERNUMBER>363</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-06+06:00</ORDERDATE>
  </Entry>
  <Entry>
    <Customers xmlns="http://ws.wso2.org/dataservice/CustomerNameSQL">
      <Customer>
        <Name>Blauer See Auto, Co.</Name>
      </Customer>
    </Customers>
    <ORDERNUMBER>10101</ORDERNUMBER>
    <CUSTOMERNUMBER>128</CUSTOMERNUMBER>
    <STATUS>Shipped</STATUS>
    <ORDERDATE>2003-01-09+06:00</ORDERDATE>
  </Entry>
  <Entries>

With Multiple Parameters for the nested query:

For this I have modified the two tables by adding a new column as follows.

Alter table CUSTOMERS add ISACTIVE int DEFAULT(1);
Alter table ORDERS add ISACTIVE int DEFAULT(1);

The format of the mapping is as follows.
"@target_query_name": "$nested_query_parameter1->

outer_Query_result_element_name1,$nested_query_parameter2->outer_Query_result_element_name2"

<data name="TestNestedJSON" transports="http https local">
   <config enableOData="false" id="test">
      <property name="driverClassName">org.h2.Driver</property>
      <property name="url">jdbc:h2:file:./samples/database/DATA_SERV_SAMP</property>
      <property name="username">wso2ds</property>
      <property name="password">wso2ds</property>
   </config>
   <query id="CustomerNameSQL" useConfig="test">
      <sql>Select c.CUSTOMERNAME from CUSTOMERS c where C.CUSTOMERNUMBER = :customerNumberin and c.ISACTIVE = :isactivein</sql>
      <result outputType="json">{"Customers": {"Customer": [{"Name": "$CUSTOMERNAME"}]}}</result>
      <param name="customerNumberin" sqlType="INTEGER"/>
      <param name="isactivein" sqlType="INTEGER"/>
   </query>
   <query id="CustomerOrderSQL" useConfig="test">
      <sql>Select o.ORDERNUMBER,o.ORDERDATE,o.STATUS,o.CUSTOMERNUMBER,o.ISACTIVE from ORDERS o</sql>
      <result outputType="json">{"Entries": {"Entry": [{"ORDERNUMBER": "$ORDERNUMBER","ORDERDATE": "$ORDERDATE","STATUS": "$STATUS","CUSTOMERNUMBER": "$CUSTOMERNUMBER","@CustomerNameSQL": "$customerNumberin-&gt;customerNumber,$isactivein-&gt;isactive"}]}}</result>
   </query>
   <operation name="customerOrders">
      <call-query href="CustomerOrderSQL"/>
   </operation>
   <resource method="GET" path="orderlist">
      <call-query href="CustomerOrderSQL"/>
   </resource>
</data>

References:
[1] https://docs.wso2.com/display/DSS350/Defining+Nested+Queries
[2] https://docs.wso2.com/display/DSS350/JSON+Mapping