Resource File

Before proceeding to the following article, the reader must be familiar with the following

The REST architectural style considers each data object as a resource. The name of the resource name is included in the URL and HTTP requests like GET, POST, PUT, DELETE can be made on the URL endpoint.

Metamug uses XML documents (resource files) to describe the behaviour of REST APIs. The files are used to describe the server-side operation to be performed when an HTTP request is made to a particular resource.

Resource File

Item Request

Attribute item="true" is to indicate that the request is going to be an Item request. Item Id in the URL is directly mapped to $id variable in the SQL query.

Here's an example.

Type URI $id
Collection /movie/ null
Item /movie/22 22

When the request uri suggests an Item Request, request type with item=true attribute is executed. And $id attribute will be populated with 22. Ofcourse, HTTP method should matched.

<?xml version="1.0" encoding="UTF-8" ?>
<Resource xmlns="http://xml.metamug.net/resource/1.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
     xsi:schemaLocation="http://xml.metamug.net/resource/1.0 http://xml.metamug.net/schema/resource.xsd"
     v="1.1">
    <Desc>Contains information about movies.</Desc>

    <!-- Collection GET Request /movie -->
    <Request method="GET">
        <Desc>Get info of all movies with ratings greater than 3.</Desc>
        <Sql id="wellRatedMovies">
            SELECT name,rating FROM movie
            where rating gt 3.0
        </Sql>        
    </Request>

    <!-- Item GET Request /movie/{id} -->
    <Request method="GET" item="true">
        <Desc>Get info of movie with given id.</Desc>
        <Sql id="allMovies">
            SELECT * FROM movie where id=$id
        </Sql>
    </Request>

</Resource>

The xml document is used to describe a resource. The title of the resource file ("movie" in this case) is mapped as the name of the particular resource.

Avoid the use of $id in a collection request, as it will cause failure.

Resource Tag

<Resource> is the root element of the xml document.

Attributes

Desc Tag

<Desc> is an optional element used for describing the particular resource. The description is shown in the API documentation. Desc tag can also be used inside the Request tag to describe the request being handled.

Request Tag

1.The first Request tag is used to describe an HTTP request made on a collection resource. The children of the resource tag describe the action to be performed after the request is made.

This tag represents a GET request made to {baseUrl}/movie, where "movie" is the resource name.

2.The second Request tag in the document has a new attribute item which is set to "true". This denotes a request to fetch a particular item represented by the number in the URL.

The URL in this case will look like {baseUrl}/movie/{id}. The first Request is a collection request and has no item attribute (item="false" by default) wherein the URL was simply {baseUrl}/movie.

Attributes

Sql Tag

Sql tag holds the relation database sql statement to be executed. The sql inside the tag needs to be passed in plain text or it can be wrapped in CDATA Section. Query can be a join across multiple table or any DML statement.

There is no mapping between the resource name ("movie" in this case) and the name of the database table ("movie" in this case). A SQL statement written inside a Request tag is independent of the type of HTTP request or the resource.

Attributes

As a thumb rule, you can say, queries that return a result-set should be enclosed in <Sql type="query"> tag, rest all in <Sql type="update"> tag the later is mostly DML queries (INSERT, UPDATE, DELETE), but there is one caveat here for PostgreSQL there is a keyword called Returning which can return a result even though we're using DML queries. Now there is no RETURNING keyword in MySQL or MS-SQL (yet), but if you happen to simulate such a feature on your own, do enclose it under <Sql type='query'> tag.

XML Escape Characters

In the movie.xml example above, the first SQL was written is SELECT name, rating FROM movie where rating gt 3.0. The characters gt in this SQL represent the > (greater than) symbol. Thus, the actual SQL executed will be SELECT name, rating FROM movie where rating > 3.0. The XML format does not support the following symbols and they are required to be escaped using characters as shown below:

Symbol Escape Characters
> gt
>= ge
< lt
<= le
= eq
!= ne

Updating Data

Insert, Update and Delete operations can be performed on the database using POST, PUT and DELETE requests.

This is only a convention. It is not enforced by design. Any type/number of SQL statements can be added under a single request tag.

movie.xml

<Request method="POST" status="201">
    <Sql id="addMovie">
        INSERT INTO movie (name, rating) VALUES ($p, $q)
    </Sql>
</Request>

<Request method="PUT" status="202" item="true">
    <Sql id="updateMovie">
        UPDATE movie SET rating=$rating WHERE id=$id
    </Sql>
</Request>

<Request method="DELETE" status="410" item="true">
    <Sql id="deleteMovie">
        DELETE FROM movie WHERE id=$id
    </Sql>
</Request>

POST, PUT, DELETE requests for the resource can be added as shown above.

  1. POST request is made on a resource collection. The SQL statement contains two variables $p and $q. Here, "name" and "rating" are the column names of the table. A POST request made to {baseUrl}/movie with parameters p=Madagascar&q=4.5 will add a new movie record with the name as Madagascar and rating 4.5. The parameter names p and q are directly mapped with variable names $p and $q.
  2. PUT request is made on a resource item. In above example, a PUT request made to {baseUrl}/movie/2 with request parameter rating=4 will update the entry in the "rating" column, of the record with id=2, with the value 4.
  3. DELETE request is made on a resource item. A DELETE request made to {baseUrl}/movie/3 (for example) will delete the record with id=3.

Conditional Queries with when

Multiple elements can be used inside a Request tag. In such cases when attribute should be used in the Sql elements. when attribute evaluates a logical expression. On the successful evaluation of the expression, the SQL statement is executed.

Multiple Sql statements can be executed on the same request. If their respective "when" expressions evaluate to true. e.g when="$amount lt 10000", when="$type eq 'one' and $color eq 'blue'"

movie.xml

<?xml version="1.0" encoding="UTF-8" ?>
<Resource v="1.2" xmlns="http://xml.metamug.net/resource/1.0">
    <desc>Contains information about movies.</desc>

    <Request method="GET">
        <!-- SQL 1 -->
        <Sql id="all" when="$q eq 1">
            SELECT name,rating FROM movie
        </Sql>        

        <!-- SQL 2 -->
        <Sql id="rated" when="$q eq 2">
            SELECT name,rating FROM movie
            where rating=5.0
        </Sql>        

        <!-- SQL 3 -->
        <Sql id="lowRated" when="$q eq 3">
            SELECT name,rating FROM movie
            where rating lt 3.0
        </Sql>        
    </Request>
</Resource>

In the above example, there are three Sql elements used inside a Request. A query parameter q sent in the HTTP request will determine which of the three SQL queries will be executed and the corresponding result will be returned. SQL 1 will be executed when a request is made with URI {baseUrl}/v1.2/movie?q=1 as determined by when="$q eq 1" and so on.

Its more readable to use strings wherever possible to represent your sql like when="$q eq 'all'" or when="$q eq 'sort_name'".

SQL Query Testing on Resource Save

On saving the resource, the SQL queries written inside the resource are tested internally. If errors are found, the resource is not saved and the errors are shown in the console.

Resource Query Testing

On successful testing, the SQL queries are saved by the system and can be viewed in the SQL Catalog.