Data stored within a database has more value only when the data can be presented to interested parties as and when needed. However, when we present this data to the audiences, it is also important to ensure integrity of security.
In today’s world, services and APIs are the preferred way of wrapping data to be exposed to the outside world. This is because; the data consumers could use a variety of channels to gain access to data. However, the effort to expose to multiple channels should be minimal and unified to ensure data consistency and integrity. Exposing databases as services is a useful technique to deal with this requirement.
There are many tools and techniques to expose databases as services. However, when you pick and choose a tool or technique for this, some of the key aspects that you pay attention to has to be on:
- Tools to generate services as opposed to create manually
- Effort to implement and fine tune
- How much code, how much configuration
- Security options
- What forms of database security supported
- Security at services layer
WSO2 Data Services Server is a very good tool to generate services for regular databases. In this article, we will see how MySQL databases can be provides with services in quick time using WSO2 Data Services Server.
1. Download and install WSO2 Data Services Server
2. It is assumed you have downloaded and installed MySQL
3. Have a test database of your choice ready to try out service generation features. For the purpose of this article we will use a database designed for a coffee shop
Coffee Shop Database
The sample database used is that of a coffee shop. The entity relationship diagram is given below.
An order can have one or more drinks. A drink can be ordered by many.
For each drink in the order, there is the option of adding something additional, such as a shot.
Note: The example discussed in this article was tried out and tested with WSO2 Data Services Server 3.1.0. The instructions given here should work for later versions too.
Setup MySQL Driver
To make WSO2 Data Services Server (DSS) talk to a MySQL server you can use the MySQL JDBC Driver. You have to pick the version of the JDBC driver that maps to the MySQL server you version you have.
You need to copy MySQL Driver to repository/components/lib folder within WSO2 DSS installation.
If you have WSO2 DSS already running, you have to restart the server to ensure this driver is loaded by the server.
Create a MySQL Data Source
First run the WSO2 DSS login to the management console (the default username/password pair is admin/admin). On the left menu, go to “Configure” tab and then click on “Data Sources” menu item.
Click on “Add Data Source” at the bottom of the table.
Values you have to provide
| Data Source Type | Select RDBMS form the drop down |
| Name | Provide a meaningful name e.g. MySQL_LH |
| Description | Optional: Description about this data source e.g. MySQL Instance on localhost |
| Data Source Provider | Select default in the drop down |
| Driver | JDBC Drover. As we are using MySQL use that driver. e.g. com.mysql.jdbc.Driver |
| URL | JDBC URL for the data source e.g. jdbc:mysql://localhost:3306/coffee_shop In here we are going to use the MySQL instance running on the default port in the local machine. Also, note that you need to have the database name as part of the data source URL in addition to the server parameters. In WSO2 DSS, this the accepted format due to MySQL integration API semantics. |
| User Name | Database user name that can be used to connect to the database. e.g. monty NOTE: you need to pick the username based on the users and access privileges that user has for the database you are going to use for tey out service generation. |
| Password | Database user’s password. e.g. monty123 |
Generate the Service
Go to “Main” tab on the left menu and click on Generate under
Home > Manage > Services > Add > Data Service > Generate
For “Carbon Data Source” select the data source you created in the above step.
Also provide the name of the database to use. In our example, we will use the coffee_shop database.
Click next and you will be given the option of selecting the tables in the given database to be used for this Web service. For our example, lets pick all the tables in the coffee_shop database.
When you go to the next step of the wizard, you are given the option of generating a service each for each table, or generating a single service for all tables in the database.
Let’s generate a single service for all tables in the database and also, provide a service name. Let’s use, for example CoffeeShopService.
When you click next in this step, the service will be generated and deployed. Click finish to complete the process, and you are ready now to use this service.
Explore the Generated Service
Go to the main tab on the left menu and go to
Home > Manage > Services > List
You will see the list of services and among them the example service that we generated, namely CoffeeShopService. If you click on the service name, you will be taken to the service’s dashboard, which will list all information about the service.
You can try the service by clicking on “Try this service” link. You can also use this “Try-it” tool to explore the characteristics of the service generated.
For each table selected, the service generation creates 5 operations
- Insert operation
- Add new data to the table
- Select all operation
- Retrieve all data in the table as a service operation
- Note that, if the data volume stored in the table is large, it might not be a good idea to invoke this operation as the response message size would be huge
- Select with key operation
- Retrieve data in the table corresponding to given primary key
- Update operation
- Update a given element in the table
- You will have to provide the corresponding primary key of the data being updated
- Delete operation
- Delete a given element in the table
- You will have to provide the corresponding primary key of the data being deleted
In our example, we selected 5 tables when generating the service. So we have 25 operations altogether in this generated service.
The list of operations is:
- delete_addition_operation
- delete_drink_operation
- delete_order_addition_operation
- delete_order_drink_operation
- delete_order_operation
- insert_addition_operation
- insert_drink_operation
- insert_order_addition_operation
- insert_order_drink_operation
- insert_order_operation
- select_all_addition_operation
- select_all_drink_operation
- select_all_order_addition_operation
- select_all_order_drink_operation
- select_all_order_operation
- select_with_key_addition_operation
- select_with_key_drink_operation
- select_with_key_order_addition_operation
- select_with_key_order_drink_operation
- select_with_key_order_operation
- update_addition_operation
- update_drink_operation
- update_order_addition_operation
- update_order_drink_operation
- update_order_operation
Try the service
The drink table is loaded with some test data. So let’s invoke select all operation on drink operation.
This operation does not require any input parameters, as we are blindly selecting all data in the table.
Next, let’s select the drink with primary key “3”.
So we get “Mocha” as the response
Modify the Service
As it as mentioned earlier, go to
Home > Manage > Services > List
And click on the generated example service, namely CoffeeShopService. This will take you to the service’s dashboard.
In there, scroll to the bottom of the page and locate “Edit Data Service (XML Edit)” on the left column of the page. Click on this, and you will be taken to the XML editor, where you can modify the XML descriptor of the service.
Let’s say, when we retrieve all the dinks, we do not want to list the primary key for each drink. So in select_all_drink_operation, in hte correponding quey, select_all_drink_query, lets remove the logic to pick the primary key.
Right now, the logic looks like:
<operation name="select_all_drink_operation">
<call-query href="select_all_drink_query"/>
</operation>
<query id="select_all_drink_query" useConfig="default">
<sql>SELECT id, name, cost FROM drink</sql>
<result element="drinkCollection" rowName="drink">
<element column="id" name="id" xsdType="xs:integer"/>
<element column="name" name="name" xsdType="xs:string"/>
<element column="cost" name="cost" xsdType="xs:decimal"/>
</result>
</query>
In the updated query, lets remove the id related logic:
<operation name="select_all_drink_operation">
<call-query href="select_all_drink_query"/>
</operation>
<query id="select_all_drink_query" useConfig="default">
<sql>SELECT name, cost FROM drink</sql>
<result element="drinkCollection" rowName="drink">
<element column="name" name="name" xsdType="xs:string"/>
<element column="cost" name="cost" xsdType="xs:decimal"/>
</result>
</query>
Lines changes are in blue color.
And the result now does not have the “id” element.
Summary
Creating MySQL data services using WSO2 DSS is quite simple, time saving and productive to developers.
The steps involved are
- set up the MySQL driver
- create a data source to point to the database
- use generate operation and follow the instructions
- pick the tables you want
- create multiple services per table or a single service for all tables
- name the service(s)
- try out the service using Try-it tool
- [up until this point we have written zero code]
- modify the generated service descriptor in XML to suit the needs
- again minimal coding and changes are in XML
In just five quick steps, you are in production with services generated against your MySQL database.
If you want more quality of service aspects such as security, response caching, access throttling you can configure those quality of service aspects using the service dashboard.
Comments