This page elaborates on the constraints referenced in the SQL Resources sections in the Concepts page. SQL Resource definitions are defined one per XML file and are constrained by an XML schema. Examples are found here. These examples may also be explored with the HTTP API Explorer.

Structure

• Content
  • One query and one metadata element are required.
  • Within a metadata element, one database element and one table element with role Parent are required. Other tables may be required. See Tables section below.
• Queries
  • GROUP BY and ORDER BY in queries are not supported. 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).
  • Views are not allowed. Metadata, e.g. column source tables and primary keys, for views is not easily discoverable.
  • Do not use table aliases in the query. Qualify columns with the full table name. Table aliases are defined in the metadata element. The alias is used in requests and responses.
• Columns
  • Columns must be declared explictly. Do not use SELECT * or SELECT {table}.* in a query.
  • Columns may contain functions however these may only be returned passively in a read request. They may not be queried as a parameter to any operation, nor may they be contained in any rows in the body in a write operation.
  • Columns may be aliased in a query. The alias is used in requests and responses.
  • Parents in a flat or hierarchical resource and children in a many-to-many hierarchical resource must have a primary key for to operate successfully.
  • A hierarchical resource query must include the parent and child primary keys.
• Tables
  • If tables are unqualified, restSQL uses the default database defined in the metadata. If they are qualified in the query (e.g. sakila.actor), then they must be qualified in the role definitions.
  • For a flat SQL Resource, a Parent table must be declared. For a one-to-many hierarchical SQL Resource, a Parent and a Child table must be declared. For a many-to-many hierarchical, a Parent, a Join and a Child table must be declared.
  • Only one Parent, Child and Join table may be defined, but an unlimited number of ParentExtensions and ChildExtensions are possible. ParentExtensions and ChildExtensions are optional.
  • Extensions must have a foreign key to the parent, and optionally a separate primary key.
• Aliases
  • Columns may be aliased in queries. The column alias is used in both requests and responses. A 400 Bad Request is returned for requests using the cardinal column name.
  • The parent and child tables may be aliased in the metadata but not the query. Columns must be disambiguated in the query using the full table name. The aliases are used in both request body and responses body XML elements or JSON attributes, as well as request URL paramters. The metadata table element attributes are rowAlias for XML and rowSetAlias for JSON. The rowSetAlias also changes the child row key value in a result set row map when using the Java API.
  • The parent extension or child extensions tables may not be aliased. These are joined with the parent or child to form single rows and need not be aliased.

Behavior

• In a one-to-many hierarchical SQL Resource, children are fully managable. Conversely, in a many-to-many hierarchical SQL Resource, the child rows may not be created or deleted; rather they are associated and disassociated with the parent. A separate SQL Resource for the the child may be used to create and delete them. Updates to the children are not supported in a many-to-many SQL Resource.
• For hierarchical SQL Resources, parents and children are managed separately. Writes (INSERT, UPDATE, DELETE) are submitted separately. One exception is with one-to-many, if the child (and child extensions) is defined with an ON DELETE CASCADE, a delete of the parent will delete the children (and child extensions).
• 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).