Summary

restSQL 0.7.3 release adds unlimited SQL Resource table extensions.

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.

Detail

The SQL Resource definition format has changed. Metadata is now declared in an XML element 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).

Migration

Pre-0.7.3 SQL Resource definitions must be changed to the new format. No changes to clients are necessary.