15 minutes Guide to sqlREST |
This guide shows, how a relational database can be enabled as REST style Web Service within minutes. You can read the guide and try the rest examples online or you can deploy sqlREST and do it on your computer.
To run sqlREST you need a Java runtime version 1.4 or higher and a Servlet 1.2 conformant Web Container like Tomcat 4.X or newer.
The Installation is quite simple:
After the installation you can start exploring the Web Service with your browser. A good starting point for querying the web service is the list of toplevel resources. The toplevel list can be accessed using a URL like the following:
http://host:port/sqlrest/
Perhaps at your installtion the right URL is:
http://localhost:8080/sqlrest/
You should get a result like the one in picture 1.
From here you can dive deeper into the service. This Web Service offers four kinds of resources. There are customers, products, invoices and items. Every kind of resource have a XLink attribute href with an URL. A list of resources is a resource too.
You can copy and paste the URL in Addressbar of your browser.
| Attention: | Cause version 0.3.1 doesn't support composed primary keys, the items resources can't be accessed. |
|---|
As you might have guessed the resources shown in the response are generated on-the-fly from database metadata. Metadata provides a description of data. For example information about the tables, columns and relations in a database. Every kind of resource correspondends to a table in the database. sqlREST can be used with almost all database on condition there is a suitable JDBC driver. The meta data provided by the driver about the database is used by sqlREST to compute the response and to execute actions on server side.
Next we want to see a list of invoices. Enter the following URL in your addressbar:
http://localhost:8080/sqlrest/INVOICE/
The result is a list pointing to resources representing invoices. Every row in the invoice table is referenced by a XLink href attribute as you can see in picture 2.
Choose an invoice an follow the link. For e.g.:
http://localhost:8080/sqlrest/INVOICE/31/
The representation of an invoice entity is shown in picture 3. The details of the invoice contains an id, a total amount and an id of the customer the invoice belongs to. The element CUSTOMERID has an XLink reference to the refered customer. sqlREST uses the database meta data to create links to referenced resources.
As you can imagine, if you follow the link:
http://localhost:8080/sqlrest/CUSTOMER/22/
you will get the representation of a customer like the one in the screenshot in picture 4.
We have followed the links in the browser, like we follow links in every web application. Despite the fact, that we can't follow the links by a klick. We have to copy the location into the addressbar. A Web Service is for engines not for humans. A client requests the resources, interprets the result and dives deeper into the system following the links.
In an RPC style Web Service like SOAP or XML-RPC you may get back a result of a certain type. For example you get back a string type with the value BA4C. The clients needs to know the meaning of the result in order to make further processing. Maybe BA4C is a title of something or BA4C is some code or it is a primary key refering to a different resource. Every method with a correspondending parameter type can be invoked with the value BA4C. But not every call makes any sense. The knowledge about the methods and parameters that can be called with a previously returned value must be available to the client.
getCustomer("BA4C"); // OK - A Customer will be returned
getProduct("BA4C"); // Nonsense - No product will be found
setTitle("BA4C"); // Will work, but not a nice title for a document
setDescription("BA4C"); // Will work also, but it is no description
In a RESTian Web Service you get the value BA4C and a link to follow. The client gets all the information with the response it needs, for following the link.
GET requests for querying can be send to RESTian Web Service using an ordinary Web Browser like Mozilla, Opera or MS Internet Explorer. Unfortunatly POST, PUT and DELETE can't be send that easy. The reminder of this guide uses a REST Gateway called RESTGate to illustrate the examples with screenshots. You can use also small programs in Java, Perl, Ruby or your prefered language, if you want to try the examples. With the screenshots provided in this guide it should be possible to follow without trying the examples yourself.
The screenshots in this guide are form an early version of RESTGate. Formerly known as webrest.
Lets have a look at the REST Gateway. A request with URL, Method and Content can be specified in a form. RESTGate takes the information from the form and sends a HTTP request to a REST Web Service.
The Web Services processes the request and answers with a response Message. The response is read by the gateway and transformed to a HTML Page as you can see in Picture 6.
Next we will see how to work with DELETE, POST and PUT methods.
HTTP provides the DELETE Method to remove resources from the server. This method is scarcely used in normal Web applications. In REST the DELETE Method is the choice to remove resources. A DELETE request send to resource will cause its deletion in a RESTian Web Service. Picture 7 shows RESTGate with a DELETE request for an invoice.
At server side sqlREST receives the delete request and executes the deletion of the addressed resource. In Picture 8 you can see the server is responding with a HTTP Status Code of 200. A Status Code of 200 means, everything is Ok.
The representation of a product resource is shown in figure 9. We want to update the price to a new value. To modify an existing resource we use a POST request.
With the request we send content to the server containing new values for the resource. Figure 10 shows the POST request method and the new value for the price expressed in XML.
At server side the product resource is altered to the new value 8.5. sqlREST creates a SQL-Update statement using the XML content and executes the statement. Depending on success the Status Code is either 200 OK or 403 Forbidden.
The next picture shows a representation of the modified resource.
New resources can be created by POST and PUT requests.
A new resource can be created by a POST request. The request have to be send to a higher level resource. To create a new product we send a POST request containing all the data to a URL representing the product table. In figure 13 you can see a request creating a new product with the ID 60.
To create a resource with PUT a request is send to a URL, which should be the address for the new resource. The URL in figure 14 contains the new ID with a value of 61. In the content there are only elements with values for none-primarykey field.
You can connect sqlREST with your own database and build sample Web Services using the REST Style. All you have to do, is to configure a JDBC connection as described in the user guide.
Enjoy sqlREST!