Tuesday, December 25, 2012

Exposing a Cassandra data source as a web service using WSO2 Data Services Server

WSO2 Data Services Server not only support for the relational data sources but also it has inbuilt support for the NoSQL data sources like Cassandra as well. Cassandra data source support has been added using CQL JDBC driver. So creating the data service for Cassandra data source is same as the creating a data service for RDBMS type data source. Only difference is you need to use CQL instead of SQL for querying the data sources.

Let's write a simple data service to perform CRUD operations on a Cassandra data-source.

Latest Data Services Server can be downloaded from here.

Step 1:
Connect to the Cassandra server and create a keyspace and column family. This will be used later in data service.

create keyspace Students;
use Students;

create column family StudentID with comparator = 'UTF8Type' and key_validation_class = 'UTF8Type' and default_validation_class = 'UTF8Type' and column_metadata =
[{column_name : 'First Name', validation_class : UTF8Type},
{column_name : 'Last Name', validation_class : UTF8Type},
{column_name : 'Subjects', validation_class : UTF8Type},
{column_name : 'Class', validation_class : UTF8Type}];

Step 2:
Start the WSO2 Data Services Server and log on to the product management console. Click on 'Data Service' -> 'Create' link in the 'Main' menu section. This will redirect to the create data-service page. There, provide a name for service and click on 'Next'.


Step 3: Provide the connection details to connect Cassandra keyspace.

Step2 will redirect to the 'Add New Data Source' page. Click on 'Add New Data Source' link and provide the data source details as follows. Save the data source and click on 'Next'.


Step 4: Provide the query details.

Step3 will redirect to the 'Add New Query' page. In this page we can specify the  CQL queries and query parameters.  Click on 'Add New Query' link and provide the query details.

Defining Insert Student Query:
Query ID*: addStudentQuery
Data Source*: CassandraDS
CQL*: update StudentID set 'First Name'=?, 'Last Name'=?, 'Subjects'=?, 'Class'=? where 'key' = ?


Then specify the input/output parameters for the query. Click on the 'Add New Input Mapping' link to define the input parameters and define them one by one as follows. 



Once you entered the mapping details, click on 'Main Configuration' and then the 'Save' button to save the query. 

As the same way, you can define the queries and parameters for select, update, delete as well.

Defining Select Students Query:
Query ID*: getStudentsQuery
Data Source*: CassandraDS
CQL*: select * from StudentID

Defining Update Student Query:
Query ID*: updateStudentQuery
Data Source*: CassandraDS
CQL*: update StudentID set 'Class'=? where 'key'=?

Defining Delete Student Query:
Query ID*: deleteStudentQuery
Data Source*: CassandraDS
CQL*: delete from StudentID where 'key'=?

Step 5: Defining web service operations

After adding the queries to data service configuration as in the Step 4, click on 'Next' to define operations. This will redirect to the operations defining page. Click on 'Add New Operation' link. There, you can select a query for this operation that will execute when this operation get invoked. 

Following image shows the operation defines to use 'addStudentQuery' query. As the same way you can define the operations for other queries as well.


Step 5 is the final step of creating the data service. After adding operations click on 'Finish' to complete the data service. This will redirect to the service listing page and new service will be there.



Final xml configuration will be as follows.
 
<data name="CassandraStudentSample">
   <config id="CassandraDS">
      <property name="url">jdbc:cassandra://localhost:9160/Students</property>
      <property name="driverClassName">org.apache.cassandra.cql.jdbc.CassandraDriver</property>
   </config>
   <query id="addStudentQuery" useConfig="CassandraDS">
      <sql>update StudentID set 'First Name'=?, 'Last Name'=?, 'Subjects'=?, 'Class'=? where 'key' = ?</sql>
      <param name="FirstName" sqlType="STRING"/>
      <param name="LastName" sqlType="STRING"/>
      <param name="Subjects" sqlType="STRING"/>
      <param name="Class" sqlType="STRING"/>
      <param name="key" sqlType="STRING"/>
   </query>
   <query id="getStudentsQuery" useConfig="CassandraDS">
      <sql>select * from StudentID</sql>
      <result element="Students" rowName="Student">
         <element column="First Name" name="FirstName" optional="true" xsdType="string"/>
         <element column="Last Name" name="LastName" optional="true" xsdType="string"/>
         <element column="Subjects" name="Subjects" optional="true" xsdType="string"/>
         <element column="Class" name="Class" optional="true" xsdType="string"/>
      </result>
   </query>
   <query id="updateStudentQuery" useConfig="CassandraDS">
      <sql>update StudentID set 'Class'=? where 'key'=?</sql>
      <param name="Class" sqlType="STRING"/>
      <param name="key" sqlType="STRING"/>
   </query>
   <query id="deleteStudentQuery" useConfig="CassandraDS">
      <sql>delete from StudentID where 'key'=?</sql>
      <param name="key" sqlType="STRING"/>
   </query>
   <operation name="addStudent">
      <call-query href="addStudentQuery">
         <with-param name="FirstName" query-param="FirstName"/>
         <with-param name="LastName" query-param="LastName"/>
         <with-param name="Subjects" query-param="Subjects"/>
         <with-param name="Class" query-param="Class"/>
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
   <operation name="getStudents">
      <call-query href="getStudentsQuery"/>
   </operation>
   <operation name="updateStudent">
      <call-query href="updateStudentQuery">
         <with-param name="Class" query-param="Class"/>
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
   <operation name="deleteStudent">
      <call-query href="deleteStudentQuery">
         <with-param name="key" query-param="key"/>
      </call-query>
   </operation>
</data>


Click on 'Try This Service' link to test the data service operations. Using these four operations, you can insert, read, update and delete data from the StudentID column family that we created at the begining.



Saturday, November 3, 2012

Distributed Transactions with WSO2 ESB

A transaction is a set of operations that executed as a single unit. When it comes to distributed transactions, it involves two or more networked computers, often using multiple databases. 

It's required to have transaction manager to handle these  distributed transactions. WSO2 carbon platform has integrated the "Atomikos" transaction manager which is a implementation of Java Transaction API (JTA). Also some products like WSO2DSS, WSO2ESB shipped this transaction manager by default and hence you don't need to provide an external JTA provider to use distributed transactions inside these products. 

WSO2ESB contains a synapse mediator called 'Transaction Mediator' which support the distributed transactions using Java Transaction API. In this post we are going to write some sample proxy service that uses the Transaction Mediator and inbuilt transaction manager that comes with WSO2 ESB to handle distributed transaction.

Sample Scenario
The scenario going to use in this sample is same as the scenario used in WSO2 ESB Transaction Mediator Sample (Sample describes above has used JBoss Application Server support to create XA data-sources and also transaction manager has been used the one provided from JBoss). But in this sample we are going to use the in built transaction manager that comes with WSO2 ESB and also XA data-sources are going to create using the Carbon data-source feature without using JBoss Application server.

Use the following scenario to show how the Transaction Mediator works. Assume we have a record in one database and we want to delete that record from the first database and add it to the second database (these two databases can be run on the same server or they can be in two remote servers). The database tables are defined in such a way that the same entry cannot be added twice. So, in the successful scenario, the record will be deleted from the first table (of the first database) and will be added to the second table (of the second database). In a failure scenario (the record is already in the second database), no record will be deleted from first table and no record will be added into the second database.

Step 1:
Create the sample Database and tables using MySQL server.

Create database DB1;
CREATE table company_x(name varchar(10) primary key, id varchar(10), price double);
INSERT into company_x values ('IBM','c1',0.0);
INSERT into company_x values ('SUN','c2',0.0);

Create database DB2;
CREATE table company_x(name varchar(10) primary key, id varchar(10), price double);
INSERT into company_x values ('SUN','c2',0.0);
INSERT into company_x values ('MSFT','c3',0.0);

Step 2:
(i) Add the mysql jdbc driver jar to {ESB_HOME}/repository/components/lib.

(ii) Create two XA data-sources for above two tables. For that, add the following xml configuration to master-datasources.xml file located in {ESB_HOME}/repository/conf/datasources/ directory. (These data-sources can be created using ESB UI as well rather using master-datasources.xml config file). In this data-sources, note that we have used the data-source provider class that provided from Atomikos transaction manager "com.atomikos.jdbc.AtomikosDataSourceBean". Other than that, you can provide the XA data-source properties specific to driver under <dataSourceProps>. Restart the server after modifying master-datasources.xml.  
<datasource>
    <name>DS1</name>
    <jndiConfig>
        <name>DS1</name>
    </jndiConfig>
 <definition type="RDBMS">
 <configuration>
    <dataSourceClassName>com.atomikos.jdbc.AtomikosDataSourceBean
</dataSourceClassName>
    <dataSourceProps>
       <property name="xaDataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
</property>
       <property name="uniqueResourceName">TXDB1</property>
       <property name="xaProperties.user">root</property>
       <property name="xaProperties.password">root</property>
       <property name="xaProperties.URL">jdbc:mysql://localhost:3306/DB1</property>
       <property name="poolSize">10</property>
    </dataSourceProps>
 </configuration>
 </definition>
</datasource>

<datasource>
 <name>DS2</name>
 <jndiConfig>
    <name>DS2</name>
 </jndiConfig>
 <definition type="RDBMS">
 <configuration>
    <dataSourceClassName>com.atomikos.jdbc.AtomikosDataSourceBean
</dataSourceClassName>
    <dataSourceProps>
       <property name="xaDataSourceClassName">com.mysql.jdbc.jdbc2.optional.MysqlXADataSource
</property>
       <property name="uniqueResourceName">TXDB2</property>
       <property name="xaProperties.user">root</property>
       <property name="xaProperties.password">root</property>
       <property name="xaProperties.URL">jdbc:mysql://localhost:3306/DB2</property>
       <property name="poolSize">10</property>
     </dataSourceProps>
 </configuration>
 </definition>
</datasource>

Step 3:
Create the ESB sequnce to implement the mentioned scenario using above two data-sources.
<sequence xmlns="http://ws.apache.org/ns/synapse" name="main">
  <in>
   <send>
    <endpoint>
      <address uri="http://localhost:9000/services/SimpleStockQuoteService"/>
         </endpoint>
    </send>
   </in>
   <out>
      <transaction action="new"/>
      <log level="custom">
         <property name="text" value="** Reporting to the Database DB1**"/>
      </log>
      <dbreport useTransaction="true">
         <connection>
            <pool>
               <dsName>DS1</dsName>
            </pool>
         </connection>
         <statement>
           <sql>
             <![CDATA[delete from company_x where name =?]]></sql>
               <parameter xmlns:m1="http://services.samples/xsd" 
     xmlns:ns="http://org.apache.synapse/xsd" 
     xmlns:m0="http://services.samples" 
     expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
         </statement>
         </dbreport>
         <log level="custom">
            <property name="text" value="** Reporting to the Database DB2**"/>
         </log>
         <dbreport useTransaction="true">
            <connection>
               <pool>
                  <dsName>DS2</dsName>
               </pool>
            </connection>
            <statement>
               <sql>
                  <![CDATA[INSERT into company_x values (?,'c4',?)]]></sql>
                  <parameter xmlns:m1="http://services.samples/xsd" 
        xmlns:ns="http://org.apache.synapse/xsd" 
        xmlns:m0="http://services.samples" 
        expression="//m0:return/m1:symbol/child::text()" type="VARCHAR"/>
                  <parameter xmlns:m1="http://services.samples/xsd" 
        xmlns:ns="http://org.apache.synapse/xsd" 
        xmlns:m0="http://services.samples" 
        expression="//m0:return/m1:last/child::text()" type="DOUBLE"/>
               </statement>
            </dbreport>
            <transaction action="commit"/>
            <send/>
         </out>
      </sequence>


Testing
Successful Scenario

1. To remove the IBM record from the first database and add it to the second database, run the sample with the following options.
ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=IBM

2. Check both databases to see how the record is deleted from the first database and added to the second database.

Failure Scenario

1. Try to add an entry which is already there in the second database. This time use Symbol SUN.
ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280/ -Dsymbol=SUN

2. You will see that whole transaction has rollback. Check both databases again; there is no record deleted from the first database and no record added into the second database.

Tuesday, July 3, 2012

How to use result of a one operation as input to another operation using WSO2 Data Services Server ?

While we are dealing with database operations, one of most frequent use-case is to use result of a one query as input to a another query. When you are using WSO2 Data Services Server you have two approaches to achieve this.
  1. Using the nested query feature.
  2. Using Export Option provided in output mappings.
    Here we are going to look at the second approach mentioned above. Before that let's briefly look at the difference of two approaches.

    When we are using nested query feature , we call to a some query inside another query. So the inner query takes the input/s from the result of outer query being called and both queries get executed. Other than using the result of one query as input to a another query , the main use-case of nested query feature is to merge two result sets of two queries and build a one result. You can refer to the article "http://wso2.org/blog/sumedha/3175" for more details on nested query feature.

    The "Export option" can be used when you need to pass result of one query as input to another. But you cannot merge the two results like we did in "Nested Query". As an example if you have a table with auto-increment field and you need to use this auto-incremented key value in another query then export option can be used to do it.

    The important thing when using "Export option" is, the operation that has exported its result elements and the operation that going to consume that exported result should be invoked in a same session. So you need to enable boxcarring in dss to use "Export option".

    Let's take a simple example and look at how can we use "Export option"

    Step 1 : Creating sample Database

    Let's create a database for our sample.

    Our database 'DATASERVICE_SAMPLE' has two tables,
    Employee - containing employee records
    Salary - containing employee salary information.

    Two tables can be linked using employeeNumber column.

    Given bellow are the SQL for creating & populating our database.

    DROP DATABASE IF EXISTS DATASERVICE_SAMPLE;
    CREATE DATABASE DATASERVICE_SAMPLE;
    GRANT ALL ON DATASERVICE_SAMPLE.* TO 'dsuser'@'localhost' IDENTIFIED BY 'user123';

    USE DATASERVICE_SAMPLE;

    DROP TABLE IF EXISTS Employees;

    CREATE TABLE Employees(
    employeeNumber INTEGER,
    lastName VARCHAR(50),
    firstName VARCHAR(50),
    extension VARCHAR(10),
    email VARCHAR(100),
    officeCode VARCHAR(10),
    reportsTo INTEGER,
    jobTitle VARCHAR(50),
    department VARCHAR(50)
    );

    insert into Employees values (1002,'Murphy','Diane','x5800','dmurphy@classicmodelcars.com','1',null,'Developer','Engineering');
    insert into Employees values (1003,'Patterson','Mary','x4611','mpatterso@classicmodelcars.com','1',1002,'VP Sales','Marketing');
    insert into Employees values (1004,'Firrelli','Jeff','x9273','jfirrelli@classicmodelcars.com','1',1002,'VP Marketing', 'Marketing');

    DROP TABLE IF EXISTS Salary;

    CREATE TABLE Salary(
    employeeNumber INTEGER,
    salary DOUBLE,
    lastRevisedDate DATE
    );

    INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1002,13000,'2007/11/30');
    INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1003,30000,'2007/01/20');
    INSERT into Salary (employeeNumber,salary,lastRevisedDate) values (1004,17500,'2008/01/01');


    Step 2: Clicking on the 'Create' link in left menu and enter a name to the Data service.

    Here note that you need to enable boxcarring for data-service.



    Step 3: Enter the details about the data-source which is using to create the data-service.



     
    Step 4: Create query



    4.a. Add output mappings and export the query result



    4.b. Add query that takes above exported result as input.



    Step 5: Add web service operations

    Note that since query2 use the result exported from query1, we do not need to provide input parameters through operation when mapping query2 with operation.

    operation that use query1



    operation that use query2

    The final dbs file will look as follows:
    <data name="ExportOptionSample" enableBoxcarring="true">               
    <config id="datasource1">                              
    <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>                              
    <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/DATASERVICE_SAMPLE</property>                              
    <property name="org.wso2.ws.dataservice.user">dsuser</property>                              
    <property name="org.wso2.ws.dataservice.password">user123</property>               
    </config>               
    <query id="q1" useConfig="datasource1">                              
    <sql>select employeeNumber from Employees where department='Marketing'</sql>                              
    <result element="Entries" rowName="Entry">                                             
    <element name="employeeNumber" column="employeeNumber" export="employeeNumber" exportType="ARRAY" xsdType="xs:integer" />                              
    </result>               
    </query>               
    <query id="q2" useConfig="datasource1">                              
    <sql>update Salary set salary=salary+salary/10 where employeeNumber in (:employeeNumber)</sql>                              
    <param name="employeeNumber" paramType="ARRAY" sqlType="INTEGER" />               
    </query>               
    <operation name="op1">                              
    <call-query href="q1" />               
    </operation>               
    <operation name="op2">                              
    <call-query href="q2" />               
    </operation>
    </data>
    
    Invoke the operations in following order using try-it tool to see the result.

    begin_boxcar
    op1
    op2
    end_boxcar


    mysql> select * from Salary;

    +----------------+--------+-----------------+
    | employeeNumber | salary | lastRevisedDate |
    +----------------+--------+-----------------+
    | 1002 | 13000 | 2007-11-30 |
    | 1003 | 33000 | 2007-01-20 |
    | 1004 | 19250 | 2008-01-01 |
    +----------------+--------+-----------------+
    3 rows in set (0.00 sec)


    Saturday, February 11, 2012

    How to use UDT (User Difined Types) within WSO2 Data Services Server ?

    Other than the ordinal sql data types, WSO2 Data Services Server support to have UDT types(User Defined Types) in result of SQL query.

    This post describes how to use Oracle UDT type result parameters in your data-service.

    Step 1: Creating simple UDT type and a table.

    Login to the oracle server and execute following sql commands to create new UDT type and table.

    Creating UDT type:
    CREATE OR REPLACE TYPE  phone_t AS OBJECT (
    a_code   CHAR(3),
    p_number CHAR(8));
    /
    
    Creating table that has field type of above created UDT (phone_t):
    CREATE TABLE phone (
    per_id    NUMBER(10),
    per_phone phone_t);
    

    You can verify the table structure by executing follwing two comands.
    set describe depth all;
    desc phone;
    

    Insert some sample data to table:
    insert into phone(per_id, per_phone) values(1, phone_t('SAM', '12345678'));
    insert into phone(per_id, per_phone) values(1, phone_t('NOK', '87654321'));
    
    Step 2: Creating data-service.

    Here we are going to create a data-service that has an opertion to reteive all data from the phone table. The important thing to notice is, the phone table contains a UDT field called 'phone_t' and how we should map this into result elements when creating the data-service.

    Clicking on the 'Create' link in left menu and enter a name to the Data service.


    Next step is to enter details about the data-source which is using to create the data-service.


    Next step is to create a query to extract information along with the structure of the response.


    Following image shows how 'phone_t' column(UDT type) is mapped to output mappings in data-service.


    Note that we should provide index of UDT attribute alone with the table column name to map the corresponding element in UDT. i.e in our sample UDT 'phone_t' index 0 maps to the attribute 'a_code' and index 1 maps to the attribute 'p_number'.
    In 'phone' table 'per_phone' column type is set to UDT type 'phone_t'. So when we provide data-source column name as 'per_phone[0]', it will return the first element(i.e a_code) of phone_t object and 'per_phone[1]' will return the second element(i.e p_number) and so.

    Final step is to add web service operation and finish the data-service creation.


    The final dbs file will look as follows,


    Click on "Try It" link to invoke the service and see the result.