Applies To

Contents

Introduction

• Use case
• Solution

Using Google Spreadsheet as a Data Source

Exposing the Stored Proc via WSO2 DSS

Invoke MySQL Stored Procedures from Data Service

Introduction

Enterprises today solely depend on various data and information captured out of them. Therefore it is a challenge to store the data that is crucial for an organization in a way that it is easily accessible for further processing and decision making. Generally, data is not manipulated manually now a days, but rather, automatic mechanisms are utilized to access the data. Data can be stored in conventional RDBMS data storages such as MySQL, Oracle etc. Or else, the data can be kept in Excel sheets and even reside in Google Spreadsheets. There can be instances, that data need to be migrated from one source to another. Or get data from a source and manipulate them within an application. The following use case will explain a scenario where data from a google spreadsheet needs to be retrieved for a web application.

Use case

In an IT company, along with software development, another task that is being carried out is providing patches to the customers to solve bugs and other issues that come across in the clients' production or development environments. Usually any IT organization maintain a record of the created and released patches. This is an instance where the patch details are kept in an online Google Spreadsheet. The organization had to go through the hassle of maintaining several G-Docs with respect to the version of the products. For the management and monitoring purposes, the organization needed a 'one stop shop' that would reflect all the past patch details. For this requirement, a web application was required to fetch data from these G-Spreadsheets and display them in an effective manner.

Solution

Data from Google Spreadsheets can be extracted and inserted to a MySQL database using WSO2 Data Services Server. A MySQL stored procedure can be defined to do the data extraction and the stored procedure would be exposed as a web service via the WSO2 DSS. Once the data is transferred to the RDBMS, the data can be obtained from the web application on-demand.

Using Google Spreadsheet as a Data Source

In the proposed solution for the discussed use case, Google spreadsheet will be acting as the main data source. For the ease of explanation, we will consider the following sample spreadsheet throughout this article. This sheet contains some patch information released to the clients and can be accessed from here.

As you can see, there are several columns that appear in this spreadsheet holding various types of data. Since all these data need to be fed into a database, we will first create a database that will hold the data in the sheet. In your MySQL server create as follows.

mysql> create database patch_details;
mysql> use patch_details;
mysql> CREATE TABLE patch(PATCH_NAME VARCHAR(256), SVN_REVISION VARCHAR(30), DESCRIPTION VARCHAR(256), RELEASE_DATE DATE, PRIMARY KEY(PATCH_NAME));
mysql> CREATE TABLE customer(CUSTOMER_ID INT NOT NULL AUTO_INCREMENT, CUSTOMER_NAME VARCHAR(256),PRIMARY KEY(CUSTOMER_ID)), UNIQUE KEY `CUSTOMER_NAME` (`CUSTOMER_NAME`) AUTO_INCREMENT=1 ;

Now the data needs to be extracted from the spreadsheet and stored into the above database. For that, a stored procedure similar to below, can be used. A stored procedure needs to be used because, it is not a simple insert operation to the data tables. In most of the cases, the data in the spreadsheet needs some manipulation or format change etc. before inserting to the database.

CREATE PROCEDURE `insertPatchData`(IN patchName varchar(256), IN svnRevision varchar(256), IN releaseDate varchar(256), IN customerName varchar(256))
BEGIN 
    DECLARE customerAvailable INT DEFAULT 0; 
    DECLARE patchAvailable INT DEFAULT 0; 
    SET customeravAilable = 0; 
    SET patchAvailable = 0; 

    SELECT STR_TO_DATE(releaseDate, '%m/%d/%Y') INTO @date; 
         
    SELECT 1 INTO patchAvailable FROM patch WHERE PATCH_NAME = patchName; 
    IF patchAvailable !=1     
    THEN 
        INSERT INTO patch (PATCH_NAME, SVN_REVISION, DESCRIPTION, RELEASE_DATE) VALUES (patchName, svnRevision,description, @date);
    END IF; 
         
    IF NOT customerName IS NULL 
    THEN  
        SELECT 1 INTO customerAvailable FROM customer WHERE CUSTOMER_NAME = customerName; 
        IF customerAvailable != 1 
        THEN  
            INSERT INTO customer (CUSTOMER_NAME) values (customerName); 
        END IF; 
    END IF; 
END

Exposing the Stored Proc via WSO2 DSS

Invocation of this stored procedure can extract data from the spreadsheet and fill the necessary tables of the database. For the SOA enablement of this process, we can expose this stored procedure as a web service. WSO2 Data Services Server has the capability of exposing any database query or a stored procedure with multiple Nested Queries. Creation of the service via DSS to expose the above insertPatchData procedure can be done either using the rich XML editor available in DSS or using the UI editor. The following step by step guide will take you to the configuration of the service.

• Step 1

Download and extract WSO2 Data Services Server to a preferred location. We will refer to this location as DS_HOME. Navigate to DS_HOME/bin directory. Execute wso2server script and start up the server.
For windows, wso2server.bat
For Linux, sh wso2server.sh

• Step 2

Once the server started up successfully, log into the management console via the URL https://localhost:9443/carbon with the default server credentials (username - admin and password - admin). In the Main tab, go to Create under Manage menu's Data Service which is in Web Services list.
Enter the Data Service Name as PatchDetailsDataService. Leave the other configuration in the wizard as it is and click Next >.
Click on Add New Data Source. Here you need to specify the Google Spreadsheet as the data source. For that, give a preferred Id as the Datasource Id. Then specify the other parameters as follows.

Datasource Id: Gspread
Data Source Type: https://docs.google.com/a/wso2.com/spreadsheet/ccc?key=0As95HY9XJ7UqdEdVWmdYY21sZ0dCQTM0SUJOOHdhNXc
Google Spreadsheet URL: Private
Visibility: private
User Name : user_name
Password: password

Hit on Save. (Before saving the configuration, you can click on Test Configuration and test the availability of the newly created data source. )Once the configuration is saved, it will be appeared in the Datasource list. Clicking on Finish will deploy the data service in DSS along with the Data source configuration. You can check the backend logs as well to confirm the successful deployment of the service. A similar log like below will get printed in the backend console at the accurate deployment.

INFO {org.wso2.carbon.core.deployment.DeploymentInterceptor} - Deploying Axis2 service: test1 {super-tenant}

The data service will be listed in service List page which is in Main tab's Web Service. Once you go into the Service dashboard of the deployed PatchDetailsDataService, you will be able check the XML configuration of the created data service by Specific Configuration --> Edit Data Service (XML Edit) . The configuration will be as follows.

<data name="PatchDetailsDataService1">
<config id="Gspread">
<property name="gspread_datasource">https://docs.google.com/a/wso2.com/spreadsheet/ccc?key=0As95HY9XJ7UqdEdVWmdYY21sZ0dCQTM0SUJOOHdhNXc</property>
<property name="gspread_visibility">private</property>
<property name="gspread_username">user_name</property>
<property name="gspread_password">password</property>
</config>
</data>

From here onwards, you can add the rest of the configuration either via the provided rich XML editor of WSO2 DSS or use the Data Service wizard.

• Step 3

In step 2, we added the G-Spreadsheet as a data source. Similarly, we need to add the destination MySQL database as well as a data source of this data service. For that, click on the Edit Data Service (Wizard) of the PatchDetailsDataService's dashboard, create a new data source and specify the parameters as follows.

Datasource Id: Gspread
Data Source Type: PatchDB
Data Source Type: RDBMS Non-XA-Datasource
Database Engine: MySQL
Driver Class: com.mysql.jdbc.Driver
JDBC URL: jdbc:mysql://localhost:3306/patch_details
User Name : root
Password: root123

Saving this configuration would append the following attributes to the data service.

<config id="PatchDB">
<property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
<property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/patch_details</property>
<property name="org.wso2.ws.dataservice.user">root</property>
<property name="org.wso2.ws.dataservice.password">root123</property>
</config>

• Step 4

Now we will have to add the query that retreives data from the Google spreadsheet and mapping it into a resulting element. Here, in this query the column names of the output mappings should be same as the column titles in spreadsheet. As discussed earlier as well, the mapping can be done through the wizard as well as from the XML editor. The final query configuration will be as follows.

<query id="getPatches" useConfig="Gspread">
<gspread>
<worksheetnumber>1</worksheetnumber>
<startingrow>2</startingrow>
<maxrowcount>-1</maxrowcount>
<hasheader>true</hasheader>
</gspread>
<result element="patches" rowName="patch">
<element name="PatchName" column="Patch Name" xsdType="xs:string" />
<element name="Client" column="Client" xsdType="xs:string" />
<element name="Date" column="Date" xsdType="xs:date" />
<element name="Description" column="JIRA/Issue Description" xsdType="xs:string" />
<element name="SVNRevision" column="SVN Revision" xsdType="xs:string" />
<element name="Fixedby" column="Fixed by" xsdType="xs:string" />
<element name="Notes" column="Additional Comments" xsdType="xs:string" />
<element name="Jars" column="Jars" xsdType="xs:string" />
</result>
</query>

• Step 5

Following to the defining of the query, you need to define the operation. From the wizard or from the XML configuration, the above created query should be passed into the operation, as the call-query parameter.

<operation name="getPatchData">
<description>This operation is associated with the query 'getPatches'</description>
<call-query href="getPatches" />
</operation>

• Step 6

With the configuration upto now, you should be able to extract all the patch details in the spreadsheet. If you try-it this service, you will see the data in the sheet appears as the response. We can try this out. Finish creating the service if you are using the wizard. Or else, if you are using the XML editor, Save the configuration. Once the service with the new configuration additions get deployed, go to the Web Service List and click on Try this service.
If you Send a request from this tool, within a couple of seconds, the response will be generated with all the relevant data filtered out from the spreadsheet.
 

• Step 7

Now this service is capable of extracting data from the spreadsheet. Next step is to insert the extracted data into the MySQL database.
In order to do that, another query needs to be defined for the data insertion to the database. We will create the query insertPatchTodb query. In the Query wizard, specify MySQL datasouce configuration as the Data source.
The mandatory SQL statement should be invoking the stored procedure we created earlier in MySQL database. Therefore the SQL will be CALL insertPatchData(?, ?,?,?,?,?,?,?,?,?,?).
Since we are doing an INSERT operation here, we need to specify the input mappings. Column attributes should be mapped to the input parameters of the stored procedure.

<query id="insertPatchTodb" useConfig="wesdb">
<sql>CALL insertPatchData(?, ?,?,?,?,?,?,?,?,?,?)</sql>
<param name="wesName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="1" />
<param name="customerName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="2" />
<param name="releaseDate" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="3" />
<param name="description" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="4" />
<param name="componentJarName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="5" />
<param name="svnRevision" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="6" />
<param name="engineerName" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="7" />
<param name="notes" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="8" />
<param name="publicJiraUrl" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="9" />
<param name="supportJiraUrl" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="10" /
<param name="svnLocation" paramType="SCALAR" sqlType="STRING" type="IN" ordinal="11" />
</query>

• Step 8

Now you need to give the data retrieved from the spreadsheet as input to the "insertPatchTodb" query. To do this use nested query feature of WSO2 DSS and call insertPatchTodb query inside getPatches query.
If you are using the Wizard, click on Edit Query of getPatches query. Click on Add New Output Mapping. Specify the Mapping Type as query and select the query as insertPatchTodb. This will generate a Query Parameter Mapping table. Make sure that the mapping type of each mapping is column. Instead of the auto-generated mapping names, the mapping name needs to be changed as the exact column name of the respective columns. Save the configuration. The XML configuration of this segment will be as below.

<call-query href="insertPatchTodb">
<with-param name="wesName" column="Patch Name" />
<with-param name="customerName" column="Client" />
<with-param name="releaseDate" column="Date" />
<with-param name="description" column="Support Jira Issue" />
<with-param name="publicJiraUrl" column="Public Jira Issue" />
<with-param name="componentJarName" column="Jars" />
<with-param name="svnRevision" column="Support svn revision (https://svn.wso2.com/wso2/custom/projects/projects/carbon/3.2.3/trunk/)" />
<with-param name="engineerName" column="Fixed by" />
<with-param name="notes" column="Additional Comments" />
<with-param name="supportJiraUrl" column="Support Jira Issue" />
<with-param name="svnLocation" column="Support svn revision (https://svn.wso2.com/wso2/custom/projects/projects/carbon/3.2.3/trunk/)" />
</call-query>

• Step 9

Now when you execute the 'getPatchData' operation both data retrieving from google spreadsheet and inserting data to tables will be executed simultaneously.
Check the two tables created in MySQL using the following commands. You will see the data has been retreived to the tables.

mysql> select * from patch;
mysql> select * from customer;

Now you can invoke general SQL calls on this database and get data from these tables and use it inside the web-application.  

References

1. Retrieve data from Google spreadsheet and insert data into a database using WSO2 Data Service Server - Dinusha Senanayake 

Author

Manisha Eleperuma

Software Engineer, WSO2 Inc.

[email protected]