Generating Hyperlinks from Relational Queries

27/02/2019

Introduction

Oracle REST Data Services (ORDS) provides an easy to use mechanism to transform relational result sets into JSON representations, and can even automatically paginate the result set, providing hyperlinks to navigate amongst the pages of the result set.

For many use cases it would be convenient to able to treat certain columns in the result set as hyperlinks, and ORDS supports a couple of mechanisms to do this:

Primary Key Hyperlinks - A column given the reserved alias $.id identifies the primary key column(s) of a single row in the result set. This/these column value(s) is/are used to form a self hyperlink that points to a child resource of the current resource that provides specific detail about that particular row in the result set.
Arbitrary Hyperlinks - A column whose alias starts with the reserved character $ is treated as a hyperlink. The subsequent characters in the column alias indicate the link relation type.

Primary Key Hyperlinks

Very often when modelling REST APIs, we want to model the Resource Collection Pattern, we want a collection resource that enumerates hyperlinks to other resources.

In the simplest case the query is against a single table, which has a single column that identifies the primary key of each row. The collection resource would present summary information about each row, and provide a self link for each row that points to a resource that will provide more detailed information about the row. For example if we use the EMP table created in the Getting Started with Oracle REST Data Services tutorial, we can define a service like so:

begin
    ords.define_service(
        p_module_name => 'links.example',
        p_base_path => 'emp-collection/',
        p_pattern => '.',
        p_source => 'select empno "$.id", empno id, ename employee_name from emp order by empno ename');
    commit;
end;

The reserved value . is used for the p_pattern value. This indicates the path of the resource template is the base path of the resource module, emp-collection/ in this case.
The EMPNO column is aliased as $.id, causing a hyperlink to be produced

Below is sample output from invoking this service:

{
	"items": [{
		"id": 7369,
		"employee_name": "SMITH",
		"links": [{
			"rel": "self",
			"href": "http://localhost:8080/ords/ordstest/emp-collection/7369"
		}]
	}, 
    ...
    ],
	"hasMore": false,
	"limit": 25,
	"offset": 0,
	"count": 14,
	"links": [{
		"rel": "self",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/"
	}, {
		"rel": "describedby",
		"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/"
	}, {
		"rel": "first",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/"
	}]
}

Note how the value of the EMPNO column is concatenated with the URL of the service to produce a new hyperlink with relation self.

Actually the value is not simply concatenated, it is resolved using the algorithm specified in RFC3986. Thus ORDS can take the value of the column, and apply the resolution algorithm to produce a new absolute URL.

If you were to attempt to navigate to this URL, you would get a 404 HTTP Status, because a resource handler for that endpoint has not yet been defined. A sample resource handler is provided below:

begin
  ords.define_template(
      p_module_name    => 'links.example',
      p_pattern        => ':id');
  ords.define_handler(
      p_module_name    => 'links.example',
      p_pattern        => ':id',
      p_source_type    => ords.source_type_collection_item,
      p_source         => 'select emp.empno "$.id", emp.* from emp where empno = :id');
  commit; 
end;

Composite Primary Keys

ORDS also supports composite primary keys, if multiple columns in the query form the primary key of a row, then each of those columns must be aliased as $.id, ORDS will combine the values to form the relative path of the item URL. This is described in the Route Patterns Specification.

Arbitrary Hyperlinks

Rich hypermedia documents will have many different hyperlinks, using a varied set of link relation types. ORDS provides a convenient mechanism to turn any column value into a hyperlink: any column whose alias starts with the $ character is treated as a hyperlink. For example, to expand the example in the previous section, it would be great if each employee resource could provide a hyperlink to their manager:

begin
  ords.define_handler(
      p_module_name    => 'links.example',
      p_pattern        => ':id',
      p_source_type    => ords.source_type_collection_item,
      p_source         => 'select emp.empno "$.id", emp.*, emp.mgr "$related" from emp where empno = :id');
  commit; 
end;

Note the column named $related, this value is treated as a hyperlink by ORDS and the column value is treated as a path relative to the containing resource’s base URI. As with the $.id column the value is transformed into an absolute URI by applying the algorithm specified by RFC 3986.

Below is a sample of the updated employee resource:

{
	"empno": 7369,
	"ename": "SMITH",
	"job": "CLERK",
	"mgr": 7902,
	"hiredate": "1980-12-17T00:00:00Z",
	"sal": 800,
	"comm": null,
	"deptno": 20,
	"links": [{
		"rel": "self",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/7369"
	}, {
		"rel": "describedby",
		"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/item"
	}, {
		"rel": "collection",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/"
	}, {
		"rel": "related",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/7902"
	}]
}

Notice the new related link which points to the employee’s manager? Pretty cool. The really nice thing about this link is that it will take you direct to the employee’s manager resource, and that in turn will also have a related link pointing to their manager, and so on up the management chain until you reach employee number 7839, who is the president of the company and whose MGR column is null. ORDS is smart enough if the column value is null to leave out the hyperlink altogether:

{
	"empno": 7839,
	"ename": "KING",
	"job": "PRESIDENT",
	"mgr": null,
	"hiredate": "1981-11-17T00:00:00Z",
	"sal": 5000,
	"comm": null,
	"deptno": 10,
	"links": [{
		"rel": "self",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/7839"
	}, {
		"rel": "describedby",
		"href": "http://localhost:8080/ords/ordstest/metadata-catalog/emp-collection/item"
	}, {
		"rel": "collection",
		"href": "http://localhost:8080/ords/ordstest/emp-collection/"
	}]
}

You might be wondering, ‘Why choose the link relation related instead of something more intuitive like manager?’. You absolutely could use a custom link relation such as manager (although to be pedantic, per RFC 8288 Section 2.1.2 any extension link relation needs to be a URI, rather than a simple value), but the price of using a custom link relation is reduced interoperability. The more your application uses non registered link relation types, the fewer clients there will be that understand those custom link relation types. Conversely if you stick to registered link relation types the more clients there will be that will be able to comprehend and navigate your link relations.

Of course there can be a benefit to more tightly specifying a link’s relation type, removing ambiguity about which link a client should choose to navigate to. There is a definite trade-off to be made, but the rule of thumb is to wherever possible use existing registered link relation types instead of extension link relation types.

URL Resolution

Since ORDS takes care to correctly resolve column values using the URI resolution algorithm specified by RFC 3986, this actually unleashes a lot of flexibility in producing hyperlink URIs.

Child paths

We can use relative paths to refer to child resources:

select 'child/resource' "$related" from dual

Assuming the base URL of the containing resource is https://example.com/ords/some_schema_alias/some/resource then the link would look like:

{
 "rel": "related",
 "href": "https://example.com/ords/some_schema_alias/some/child/resource"
}

Ancestor Paths

ORDS lets you use the familiar ../ and ./ syntax to refer to parent paths of the current resource, for example:

select '../' "$up", './' "$self" from dual

Assuming the base URL of the containing resource is https://example.com/ords/some_schema_alias/some/collection/ then the links would look like:

{
 "rel": "up",
 "href": "https://example.com/ords/some_schema_alias/some/"
},
{
 "rel": "self",
 "href": "https://example.com/ords/some_schema_alias/some/collection/"
}

Absolute URLs

A hyperlink value can be an absolute path or fully qualified URL, for example:

select '/cool/stuff' "$related", 'https://oracle.com/rest' "$related" from dual

Assuming the base URL of the containing resource is https://example.com/ords/some_schema_alias/some/collection/ then the links would look like:

{
 "rel": "related",
 "href": "https://example.com/cool/stuff"
},
{
 "rel": "related",
 "href": "https://oracle.com/rest"
}

You can have multiple links of the same link relation

Context Root Relative Paths

In ORDS the context root path is the URL of the root resource of an ORDS enabled schema, in the examples above it is: https://example.com/ords/some_schema_alias.

It can be convenient to express resource paths relative to that URL and ORDS provides a handy syntax to do that:

select '^/another/collection/' "$related" from dual

Assuming the base URL of the containing resource is https://example.com/ords/some_schema_alias/some/collection/ then the link would look like:

{
 "rel": "related",
 "href": "https://example.com/ords/some_schema_alias/another/collection"
}

Any path starting with ^/ ¹ is resolved relative to the context root path.

Dynamic Paths

For simplicity’s sake the above examples all use literal values for the value of hyperlinks, but in case it’s not clear from the MGR column example, the hyperlink value can be completely dynamic, formed from any value that is a string (or can be automatically cast to a string), for example, to return to the manager hyperlink example for a moment, say instead of wanting to point directly to the manager’s employee resource, we wanted to point to a more specialized resource just for managers, that might show additional information about the manager such as the total number of reports, we could redefine the GET handler for the emp-collection/:id resource as follows:

begin
  ords.define_handler(
      p_module_name    => 'links.example',
      p_pattern        => ':id',
      p_source_type    => ords.source_type_collection_item,
      p_source         => 'select emp.empno "$.id", emp.*, decode(emp.mgr, null, null, '^/managers/' || emp.mgr) "$related" from emp where empno = :id');
  commit; 
end;

The value of the $related column is formed from ^/managers/:emp.mgr unless emp.mgr is null, in which case a null value is subsituted (which causes ORDS not to generate the hyperlink)