5 Collection Specifications

A collection specification is a JSON object that provides information about the Oracle Database table or view underlying a collection object. The table or view is created when you create the collection.

Note:

In collection specifications, you must use strict JSON syntax. That is, you must enclose each nonnumeric value in double quotation marks.

When you create a collection using PUT collection you can set custom metadata for it by providing a collection specification for it as the request body.

Collection metadata for an existing collection can be returned as part of a GET catalog operation.

Example 5-1 shows the collection specification that specifies the default collection metadata.

Table 5-1 describes the collection specification fields and their possible values.

Note:

If you omit one of the optional columns (created-on timestamp, last-modified timestamp, version, or media type) from the collection specification then no such column is created. At a minimum, a collection has a key column and a content column.

Table 5-1 Collection Specification Fields

Field	Description	Possible Values
schemaName	SQL name of database schema (user account) that owns table or view underlying collection object.	—
tableName or viewName	SQL name of table or view underlying collection object.	—
keyColumn.name	Name of key column.	Default: ID
keyColumn.sqlType	SQL data type of key column.	VARCHAR2 (default), NUMBER, RAW
keyColumn.maxLength	Maximum length of key column, if not of NUMBER data type.	Default: 255
keyColumn.assignmentMethod	Key assignment method.	SEQUENCE, GUID, UUID (default), or CLIENT
keyColumn.sequenceName	If keyColumn.assignmentMethod is SEQUENCE, then this field must specify the name of a database sequence.	Name of existing database sequence
contentColumn.name	Name of content column.	Default: JSON_DOCUMENT
contentColumn.sqlType	SQL data type of content column.	VARCHAR2, BLOB (default), CLOB
contentColumn.maxLength	Maximum length of content column, if not of LOB data type.	The default length is 4000 bytes. If MAX_STRING_SIZE = STANDARD then maxLength can be at most 4000 (bytes). If MAX_STRING_SIZE = EXTENDED, then maxLength can be at most 32767 (bytes).
contentColumn.validation	Validation level of content column. Corresponds to SQL condition is json, which determines the syntax to which JSON content must conform. STANDARD validates according to the JSON RFC 4627 standard. (It corresponds to the strict syntax defined for Oracle SQL condition is json.) STRICT is the same as STANDARD, except that it also verifies that the document does not contain duplicate JSON field names. (It corresponds to the strict syntax defined for Oracle SQL condition is json when the keywords WITH UNIQUE KEYS are also used.) LAX validates more loosely. (It corresponds to the lax syntax defined for Oracle SQL condition is json.) Some of the relaxations that LAX allows include the following: It does not require JSON field names to be enclosed in double quotation marks ("). It allows uppercase, lowercase, and mixed case versions of true, false, and null. Numerals can be represented in additional ways.	STANDARD (default), STRICT, LAX
contentColumn.compress	Compression level for SecureFiles stored in content column.	NONE (default), HIGH, MEDIUM, LOW
contentColumn.cache	Caching of SecureFiles stored in content column.	TRUE, FALSE (default)
contentColumn.encrypt	Encryption algorithm for SecureFiles stored in content column.Foot 1	NONE (default), 3DES168, AES128, AES192, AES256
creationTimeColumn.name	Name of optional created-on timestamp column. This column has SQL data type TIMESTAMP and default value SYSTIMESTAMP.	Default: CREATED_ON
lastModifiedColumn.name	Name of optional last-modified timestamp column. This column has SQL data type TIMESTAMP and default value SYSTIMESTAMP.	Default: LAST_MODIFIED
lastModifiedColumn.index	Name of nonunique index on timestamp column. The index is created if a name is specified.
versionColumn.name	Name of optional version (ETag) column. This column has SQL data type VARCHAR2(255) unless the method is SEQUENTIAL or TIMESTAMP, in which case it has data type NUMBER. Note: If the method is TIMESTAMP then the version is stored as an integer representation of the date and time with microsecond precision. It does not store a date/time string or a SQL date/time type.	Default: VERSION
versionColumn.method	Versioning method.	SEQUENTIAL, TIMESTAMP, UUID, SHA256 (default), MD5, NONE
mediaTypeColumn.name	Name of optional object media type column. This column has SQL data type VARCHAR2(255).
readOnly	Read/write policy: TRUE means read-only.	TRUE, FALSE (default)

^Footnote 1

Set up Encryption Wallet before creating a collection with SecureFile encryption. For information about the SET ENCRYPTION WALLET clause of the ALTER SYSTEM statement, see Oracle Database SQL Language Reference.

Example 5-1 Default Collection Metadata

{
   "schemaName" : "mySchemaName",
   "tableName" : "myTableName",
   "keyColumn" :
   {
      "name" : "ID",
      "sqlType" : "VARCHAR2",
      "maxLength" : 255,
      "assignmentMethod" : "UUID"
   },
   "contentColumn" :
   {
      "name" : "JSON_DOCUMENT",
      "sqlType" : "BLOB",
      "compress" : "NONE",
      "cache" : true,
      "encrypt" : "NONE",
      "validation" : "STANDARD"
   },
   "versionColumn" :
   {
     "name" : "VERSION",
     "method" : "SHA256"
   },
   "lastModifiedColumn" :
   {
     "name" : "LAST_MODIFIED"
   },
   "creationTimeColumn" :
   {
      "name" : "CREATED_ON"
   },
   "readOnly" : false
}

See Also:

• Oracle Database JSON Developer’s Guide for information about the syntax possibilities used by SQL condition is json

• http://tools.ietf.org/html/rfc4627 for the JSON RFC 4627 standard

• Oracle Database SecureFiles and Large Objects Developer's Guide for information about SecureFiles LOB storage

• Key Assignment Method
  The key assignment method determines how keys are assigned to objects that are inserted into a collection.
• Versioning Method
  The versioning method determines how the REST server computes version values for objects when they are inserted into a collection or replaced.

5.1 Key Assignment Method

The key assignment method determines how keys are assigned to objects that are inserted into a collection.

Table 5-2 Key Assignment Methods

Method	Description
SEQUENCE	Keys are integers generated by a database sequence. You must specify the name of the sequence in the keyColumn.sequenceName field.
GUID	Keys are generated by the SQL function SYS_GUID(), which returns a globally unique RAW value (16 bytes). If necessary, the RAW value is converted to the SQL data type specified by keyColumn.sqlType.
UUID	Keys are generated by the built-in UUID capability of the Java Virtual Machine (JVM) on which the REST server is running, which returns a universally unique RAW value. If necessary, the RAW value is converted to the SQL data type specified by keyColumn.sqlType.
CLIENT	Keys are assigned by the client application (not recommended).

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 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.

Caution:

If client-assigned keys are used and the key column type is VARCHAR2 then Oracle recommends that the database character set be AL32UTF8. This ensures that conversion of the keys to the database character set is lossless.

Otherwise, if client-assigned keys contain characters that are not supported in your database character set then conversion of the key into the database character set during a read or write operation is lossy. This can lead to duplicate-key errors during insert operations. More generally, it can lead to unpredictable results. For example, a read operation could return a value that is associated with a different key from the one you expect.

5.2 Versioning Method

The versioning method determines how the REST server computes version values for objects when they are inserted into a collection or replaced.

Table 5-3 Versioning Methods

Method	Description
MD5	The REST server computes an MD5 checksum on the bytes of object content. For bytes with character data types (such as VARCHAR2 and CLOB), the computation uses UTF-8 encoding. For bytes with data type BLOB, the computation uses the encoding used to transmit the POST body, which can be either UTF-8 or UTF-16. For a bulk insert, the request body is parsed as an array of objects and the bytes of the individual objects are re-serialized with UTF-8 encoding, regardless of the encoding chosen for storage. In all cases, the checksum is computed on the bytes as they would be returned by a GET operation for the object.
SHA256 (default)	The REST server computes a SHA256 checksum on the bytes of object content. For bytes with character data types (such as VARCHAR2 and CLOB), the computation uses UTF-8 encoding. For bytes with data type BLOB, the computation uses the encoding used to transmit the POST body, which can be either UTF-8 or UTF-16. For a bulk insert, the request body is parsed as an array of objects and the bytes of the individual objects are re-serialized with UTF-8 encoding, regardless of the encoding chosen for storage. In all cases, the checksum is computed on the bytes as they would be returned by a GET operation for the specific object.
UUID	Ignoring object content, the REST server generates a universally unique identifier (UUID)—a 32-character hexadecimal value—when the object is inserted and for every replace operation (even if the replace operation does not change the object content).
TIMESTAMP	Ignoring object content, the REST server generates an integer value, derived from the value returned by the SQL SYSTIMESTAMP function. The integer value changes at the level of accuracy of the system clock (typically microseconds or milliseconds).
SEQUENTIAL	Ignoring object content, the REST server assigns version 1 when the object is inserted and increments the version value every time the object is replaced.
NONE	The REST server does not assign version values during insert and replace operations. During GET operations, any non-null value stored in the version column is used as an ETag. Your application is responsible for populating the version column (using, for example, a PL/SQL trigger or asynchronous program).

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.