SQL Resources in previous releases were constrained to a maximum of one parent and one child extension. 0.7.3 enables an SQL Resource to join an abitrary number of tables to create a flat or hierarchical application-specific view.
metadata
that is a peer to the
query
element. The
metadata
element contains a database element, with the default name, and one or more
table
elements. The default database and table roles are no longer defined in the document element.
Following is an example with three parent extensions:
<?xml version="1.0" encoding="UTF-8"?>
<rs:sqlResource xmlns:rs="http://restsql.org/schema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://restsql.org/schema SqlResource.xsd ">
<query>
select film.film_id, title, release_year "year", language_id, comment, genre, city, country
from film_origin, film_genre, film_comment, film
where film.film_id = film_comment.film_id
and film.film_id = film_genre.film_id
and film.film_id = film_origin.film_id
</query>
<metadata>
<database default="sakila" />
<table name="film" role="Parent" />
<table name="film_comment" role="ParentExtension" />
<table name="film_genre" role="ParentExtension" />
<table name="film_origin" role="ParentExtension" />
</metadata>
</rs:sqlResource>
The SQL Resources sections in the Concepts page have been updated. SQL Resource definitions are defined one per XML file and are constained by an XML schema. Examples are found here. These examples may also be explored with the HTTP API Explorer.
restSQL will throw an SqlResourceException (or 500 via HTTP API) on access of a resource whose definition is invalid. See SQL Resource Rules for all constraints.
0.7.3 additionally adds an ORDER BY clause to all SELECT (GET) operations. Read results (SELECT) are ordered by the primary key(s), in ascending order. In a hierarchical query, the children are ordered within the parent by the child table's primary key(s).