1 SODA for REST

To create a new collection, run this command, where MyCollection is the name of the collection:

curl -i -X PUT http://localhost:8080/ords/schema/soda/latest/MyCollection

The preceding command sends a PUT request with URL http://localhost:8080/ords/schema/soda/latest/MyCollection, to create a collection named MyCollection. The -i command-line option causes cURL to include the HTTP response headers in the output. If the operation succeeds then the output looks similar to this:

HTTP/1.1 201 Created
Cache-Control: private,must-revalidate,max-age=0
Location: http://localhost:8080/ords/schema/soda/latest/MyCollection/
Content-Length: 0

Response code 201 indicates that the operation succeeded. A PUT operation that results in the creation of a new collection—a PUT collection operation—returns no response body.

A successful PUT collection operation creates a database table to store the new collection. One way to see the details of this table is using SQL*Plus command describe:

SQL> describe "MyCollection"
Name                                       Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(255)
 CREATED_ON                                NOT NULL TIMESTAMP(6)
 LAST_MODIFIED                             NOT NULL TIMESTAMP(6)
 VERSION                                   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                             BLOB

The preceding table reflects the default collection configuration. To create a custom collection configuration, provide a collection specification as the body of the PUT operation. For information about collection specifications, see Collection Specifications.

To obtain a list of the collections available in schema, run this command:

curl -X GET http://localhost:8080/ords/schema/soda/latest

That sends a GET request with the URL http://localhost:8080/ords/schema/soda/latest and returns this response body:

{
  "items" : [
    {
      "name":"MyCollection",
      "properties": {
          "schemaName":"SCHEMA",
          "tableName":"MyCollection",
          ...
      }
      "links" : [
        {
         "rel" : "canonical",
         "href" :"http://localhost:8080/ords/schema/soda/latest/MyCollection"
        }
      ]
    }
  ],
  "more" : false
}

The response body includes all available collections in schema, which in this case is only collection MyCollection.

A successful GET collection operation returns response code 200, and the response body is a JSON object that contains an array of available collections and includes the collection specification for each collection.

To delete MyCollection, run this command:

curl -i -X DELETE http://localhost:8080/ords/schema/soda/latest/MyCollection

The preceding command sends a DELETE request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection and returns:

HTTP/1.1 200 OK
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0

Response code 200 indicates that the operation succeeded. A DELETE operation that results in the deletion of a collection—a DELETE collection operation—returns no response body.

To verify that the collection was deleted, get the list of available collections in schema:

curl -X GET http://localhost:8080/ords/schema/soda/latest

If MyCollection was deleted, the preceding command returns:

{
  "items" : [],
  "more" : false
}

Create MyCollection again, so that you can use it in the next step:

curl -X PUT http://localhost:8080/ords/schema/soda/latest/MyCollection

The example uses file po.json, which was included in the download. The file contains a JSON document that contains a purchase order. To load the JSON document into MyCollection, run this command:

curl -X POST --data-binary @po.json -H "Content-Type: application/json"
http://localhost:8080/ords/schema/soda/latest/MyCollection

The preceding command sends a POST request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection. It outputs something like this:

{
  "items" : [ 
    { 
      "id" : "2FFD968C531C49B9A7EAC4398DFC02EE",
      "etag" : "C1354F27A5180FF7B828F01CBBC84022DCF5F7209DBF0E6DFFCC626E3B0400C3",
      "lastModified":"2014-09-22T21:25:19.564394Z",
      "created":"2014-09-22T21:25:19.564394Z"
    }
  ],
  "hasMore" : false,
  "count" : 1
} 

A successful POST object operation returns response code 200. The response body is a JSON document that contains the identifier that the server assigned to the document when you inserted it into the collection, as well as the current ETag and last-modified time stamp for the inserted document.

To retrieve the document that was inserted in Inserting a Document into a Collection, run this command, where id is the document identifier that you copied when inserting the document:

curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id

A successful GET document operation returns response code 200. The response body contains the retrieved document.

If id does not exist in MyCollection then the response code is 404, as you can see by changing id to such an identifier:

curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/2FFD968C531C49B9A7EAC4398DFC02EF
 
{
  "type" : "http://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.1",
  "status" : 404,
  "title" : "Key 2FFD968C531C49B9A7EAC4398DFC02EF not found in collection MyCollection.",
  "o:errorCode" : "REST-02001"
}

To delete, from MyCollection, the document that you retrieved in Retrieving a Document from a Collection, run this command (where id is the document identifier):

curl -i -X DELETE http://localhost:8080/ords/schema/soda/latest/MyCollection/id

The preceding command sends a DELETE request with URL http://localhost:8080/ords/schema/soda/latest/MyCollection/id, and it returns this:

HTTP/1.1 200 OK
Cache-Control: private,must-revalidate,max-age=0
Content-Length: 0

Response code 200 indicates that the operation succeeded. A DELETE operation that results in the deletion of an object from a collection—a DELETE object operation—returns no response body.

This example uses file POList.json, which was included in the download. The file contains a JSON array of 70 purchase orders. To load the purchase orders into collection MyCollection, run this command:

curl -X POST --data-binary @POList.json -H "Content-Type: application/json" 
http://localhost:8080/ords/schema/soda/latest/MyCollection?action=insert

Parameter value action=insert causes the array to be inserted as a set of documents, rather than as a single document.

The preceding command sends a POST request with the URL http://localhost:8080/ords/schema/soda/latest/MyCollection, and it outputs something like this:

{
  "items" : [ 
  { 
    "id" : "6DEAF8F011FD43249E5F60A93B850AB9",
    "etag" : "49205D7E916EAED914465FCFF029B2795885A1914966E0AE82D4CCDBBE2EAF8E",
    "lastModified" : "2014-09-22T22:39:15.546435Z",
    "created" : "2014-09-22T22:39:15.546435Z"
  },
  { 
    "id" : "C9FF7685D48E4E4B8641D8401ED0FB68",
    "etag" : "F3EB514BEDE6A6CC337ADA0F5BE6DEFC5D451E68CE645729224BB6707FBE1F4F",
    "lastModified" : "2014-09-22T22:39:15.546435Z",
    "created":"2014-09-22T22:39:15.546435Z"
  },
  ...
  ],
  "hasMore":false,
  "count":70
}

A successful POST array insert operation returns response code 200. The response body is a JSON document that contains the identifier, ETag, and last-modified time stamp for each inserted document.

Copy an "id" field value returned by your own POST array insert operation (not a value from the preceding example). Query the collection using SQL*Plus or SQL Developer, substituting your copied value for identifier:

SELECT json_value(json_document FORMAT JSON, '$.Reference') 
  FROM "MyCollection" WHERE id = 'identifier'; 
 
JSON_VALUE(JSON_DOCUMENTFORMATJSON,'$.REFERENCE')
--------------------------------------------------------------------------------
MSULLIVA-20141102

You can use parameters to control the result. For example, you can:

• Limit the number of documents returned

• Return only document identifiers (keys), only document contents, or both keys and contents

• Return a range of documents, based on keys or last-modified time stamps

• Specify the order of the list of returned documents

To list the documents in MyCollection, returning their keys and other metadata but not their content, run the following command.

curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection?fields=id

The preceding command outputs something like this:

{
  "items" : [ 
  { 
    "id" : "023C4A6581D84B71A5C0D5D364CE8484",
    "etag":"3484DFB604DDA3FBC0C681C37972E7DD8C5F4457ACE32BD16960D4388C5A7C0E",
    "lastModified" : "2014-09-22T22:39:15.546435Z", 
    "created":"2014-09-22T22:39:15.546435Z"
  },
  {
    "id" : "06DD0319148E40A7B8AA48E39E739184",
    "etag" : "A19A1E9A3A38B1BAE3EE52B93350FBD76309CBFC4072A2BECD95BCA44D4849DD",
    "lastModified" : "2014-09-22T22:39:15.546435Z",
    "created" : "2014-09-22T22:39:15.546435Z"
  }, 
  ...
  ],
  "hasMore" : false,
  "count" : 70, 
  "offset":0,
  "limit":100,
  "totalResults":70
}

A successful GET collection operation returns response code 200, and the response body is a JSON document that lists the documents in the collection. If the collection is empty, the response body is an empty items array.

To list at most 10 documents in MyCollection, returning their keys, content, and other metadata, run this command:

curl -X GET "http://localhost:8080/ords/schema/soda/latest/MyCollection?fields=all&limit=10"

The preceding command outputs something like this:

{
  "items": [ ... ],
  "hasMore" : true,
  "count" : 10,
  "offset" : 0,
  "limit" : 10,
  "links" : [{
      "rel" : "next",
      "href" :
"http://localhost:8080/ords/schema/soda/latest/MyCollection?offset=10&limit=10"
   }]
}

The metadata in the response body shows that 10 documents were requested ("limit" : 10)) and 10 documents were returned ("count" : 10)) , and that more documents are available ("hasMore" : true). To fetch the next set of documents, you can use the URL in the field "links"."href".

The maximum number of documents returned from a collection by the server is controlled by the following:

• URL parameter limit

• Configuration parameters soda.maxLimit and soda.defaultLimit

The behavior of the PUT operation for a nonexistent document depends on the key-assignment method used by the collection.

• If the collection uses server-assigned keys (as does collection MyCollection) then an error is raised if you try to update a nonexistent document (that is, you specify a key that does not belong to any document in the collection).

• If the collection uses client-assigned keys, then trying to update a nonexistent document inserts into the collection a new document with the specified key.

Retrieve a document from MyCollection by running this command, where id is the document identifier that you copied in Listing the Documents in a Collection:

curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id

The preceding command outputs the retrieved document.

To update this document with the content of file poUpdated.json, which was included in the download, execute this command:

curl -i -X PUT --data-binary @poUpdated.json -H "Content-Type: application/json"
http://localhost:8080/ords/schema/soda/latest/MyCollection/id

The preceding command outputs something like this:

HTTP/1.1 200 OK
Cache-Control: no-cache,must-revalidate,no-store,max-age=0
ETag: A0B07E0A6D000358C546DC5D8D5059D9CB548A1A5F6F2CAD66E2180B579CCB6D
Last-Modified: Mon, 22 Sep 2014 16:42:35 PDT
Location: http://localhost:8080/ords/schema/soda/latest/MyCollection/023C4A6581D84B71A5C0D5D364CE8484/
Content-Length: 0

The response code 200 indicates that the operation succeeded. A PUT operation that results in the successful update of a document in a collection—a PUT object operation—returns no response body.

To verify the document has been updated, rerun this command:

curl -X GET http://localhost:8080/ords/schema/soda/latest/MyCollection/id

The preceding command returns:

{
  "PONumber": 1,
  "Content" : "This document has been updated...."
}

The examples use the QBE.*.json files that are included in the zip file that you downloaded in installation step 3. They are in directory ORDS_HOME/examples/soda/getting-started.

The URI has this form:

/ords/schema/soda/[version/[collection/[{key/|?action=action}]]]

where:

• ords is the directory of the Oracle REST Data Services (ORDS) listener, of which SODA for REST is a component.

• schema is the name of an Oracle Database schema that has been configured as an end point for SODA for REST.

• soda is the name given to the Oracle Database JSON service when mapped as a template within ORDS.

• version is the version number of soda.

• collection is the name of a set of objects stored in schema.

  Typically, an object is a JSON document, but it can be a Multipurpose Internet Mail Extensions (MIME) type (for example, image, audio, or video).

  A JSON document is represented as textual JSON.

  Typically, an application uses a collection to hold all instances of a particular type of object. Thus, a collection is roughly analogous to a table in a relational database. One column stores keys and another column stores content.

• key is a string that uniquely identifies an object in collection.

  A specified object is specified by its key.

• action is either query, index, unindex, insert, update, or delete.

For the operation GET object, the response body is a single object.

Table 1-2 lists and describes fields that can appear in response bodies.

If an operation creates or returns objects, then its response body can have the additional fields in Table 1-3. The additional fields appear after field items.

Example 1-1 shows the structure of a response body that returns 25 objects. The first object is a JSON object and the second is a jpeg image. The collection that contains these objects contains additional objects.

To delete all objects from a collection, but not delete the collection itself, use POST bulk delete.

If the collection uses client-assigned keys, use PUT object. For information about key assignment methods, see Key Assignment Method.

Oracle REST standards strongly recommend using server-assigned keys; that is, avoiding the key assignment method CLIENT. If you need simple numeric keys, Oracle recommends SEQUENCE. If any unique identifier is sufficient, Oracle recommends UUID.

If the key assignment method is SEQUENCE, GUID, or UUID, you insert a object into the collection with the operation POST object. The REST server always interprets POST as an insert operation, assigning a key and returning the key in the response body.

If the key assignment method is CLIENT, you cannot use POST to a insert a object in the collection, because the URL path does not include the necessary key. Instead, you must insert the object into the collection using PUT object. If the object is not already in the collection, then the REST server interprets PUT as an insert operation. If the object is already in the collection, then the REST server interprets PUT as a replace operation. PUT is effectively equivalent to the SQL statement MERGE.

MD5 and SHA256 compute checksum values that change when the content itself changes, providing a very accurate way to invalidate client caches. However, they are costly, because the REST server must perform a byte-by-byte computation over the objects as they are inserted or replaced.

UUID is most efficient for input operations, because the REST server does not have to examine every byte of input or wait for SQL to return function values. However, replacement operations invalidate cached copies even if they do not change object content.

TIMESTAMP is useful when you need integer values or must compare two versions to determine which is more recent. As with UUID, replacement operations can invalidate cached copies without changing object content. Because the accuracy of the system clock may be limited, TIMESTAMP is not recommended if objects can change at very high frequency (many times per millisecond).

SEQUENTIAL is also useful when you need integer values or must compare two versions to determine which is more recent. Version values are easily understood by human users, and the version increases despite system clock limitations. However, the increment operation occurs within SQL; therefore, the new version value is not always available to be returned in the REST response body.