Overview of SODA

1 Overview of SODA

Simple Oracle Document Access (SODA) is a set of NoSQL-style APIs that let you create and store collections of documents (in particular JSON) in Oracle Database, retrieve them, and query them, without needing to know Structured Query Language (SQL) or how the documents are stored in the database.

There are separate SODA implementations for use with different languages and with the representational state transfer (REST) architectural style. SODA for REST can itself be accessed from almost any programming language. It maps SODA operations to Uniform Resource Locator (URL) patterns).

Note:

This book describes the features that are present in different SODA implementations. Some features described here may not be available for some implementations. In addition, different implementations can have different ways of providing some of the features. Please refer to the documentation for a particular implementation for detailed information about it.

SODA APIs are document-centric. You can use any SODA implementation to perform create, read, update, and delete (CRUD) operations on documents of nearly any kind (including video, image, sound, and other binary content). You can also use any SODA implementation to query the content of JavaScript Object Notation (JSON) documents using pattern-matching: query-by-example (QBE). CRUD operations can be driven by document keys or by QBEs.

Oracle Database supports storing and querying JSON data natively. SODA document collections are backed by ordinary database tables and views. Because of this, you can take advantage of database features for use with the content of SODA documents.

But you do not need database administrator (DBA) assistance or knowledge of SQL to develop or deploy a SODA application. SODA CRUD and query operations are automatically mapped to SQL operations on the underlying database tables or views, and these operations are optimized.

The SQL standard defines a set of SQL/JSON operators that allow direct querying of JSON data. Database views based on these operators provide schema-on-read behavior that is immune to changes in the structure of your documents. If needed, developers with SQL knowledge can use SQL/JSON to perform advanced operations on your SODA data that make full use of the database. For example, a SQL developer can apply database analytics and reporting to it, and can include it in aggregation and join operations that involve other data. In addition, your SODA applications can use database transactions.

These SODA abstractions hide the complexities of SQL and client programming:

Collection
Document

A document collection contains documents. Collections are persisted in an Oracle Database schema (also known as a database user). In some SODA implementations a database schema is referred to as a database.

A SODA collection is analogous to an Oracle Database table or view.

SODA is designed primarily for working with JSON documents, but a document can be of any Multipurpose Internet Mail Extensions (MIME) type.

In addition to its content, a document has other document components, including a unique identifier, called its key, a version, a media type (type of content), and the date and time that it was created and last modified. The key is typically assigned by SODA when the document is created, but client-assigned keys can also be used. Besides the content and key (if client-assigned), you can set the media type of a document. The other components are generated and maintained by SODA. All components other than content and key are optional.

A SODA document is analogous to, and is in fact backed by, a row of a database table or view. The row has one column for each document component: key, content, version, and so on.

In addition to the documents it contains, a collection also has associated collection metadata. This specifies various details about the collection, such as its storage, whether it should track version and time-stamp document components, how such components are generated, and whether the collection can contain only JSON documents.

In some contexts collection metadata is represented as a JSON document. This metadata document is sometimes called a collection specification. You can supply a custom collection specification when you create a collection, to provide metadata that differs from that provided by default.

SODA provides CRUD operations on documents. JSON documents can additionally be queried, using query-by-example (QBE) patterns, also known as filter specifications. A filter specification is itself a JSON object.

SODA APIs provide operations for collection management (create, drop, list) and document management (CRUD).

These are some of the actions you can perform using SODA:

Create collections
Open existing collections
Drop collections
List all existing collections
Create documents
Insert documents into a collection
Find a document in a collection, by key or by key and version
Find all documents in a collection
Find documents in a collection, by keys or by QBE
Replace (update) a document in a collection, by key or by key and version (optimistic locking)
Remove a document from a collection, by key or by key and version (optimistic locking)
Remove documents from a collection, by keys or by QBE

Your applications use a database transaction when performing one or more such actions.^Foot 1

1.1 Overview of SODA Documents

SODA is designed primarily to manipulate JavaScript Object Notation (JSON) documents, that is, documents whose content is JSON data, but other kinds of documents can also be used. A document has other components, besides its content.

Here is a textual representation of the content of a simple JSON document:

{ "name"    : "Alexander",
  "address" : "1234 Main Street",
  "city"    : "Anytown",
  "state"   : "CA",
  "zip"     : "12345" }

You can set the following document components (with an application client, for example):

Key
Content
Media type

In a collection, each document must have a document key, which is unique for the collection. By default, collections are configured to automatically generate document keys for inserted documents. If you want to instead use your own, custom, keys for a collection then you must provide the key for a document when you create it.

The media type specifies the type of content for a document. For JSON documents the media type is "application/json".

The following document components are set and maintained automatically by SODA itself:

Version
Creation time stamp
Last-modified time stamp

A SODA document is an abstract object that encapsulates its components, including its content — it is a carrier of content. A SODA create-document operation creates such a programmatic document object, and a document object is returned by some SODA operations, such as find operations.^Foot 2

A document is stored in Oracle Database as a row in a table or view, with each component in its own column.

In a client application, a SODA document is represented in a way that is appropriate to the particular SODA implementation used. For example:

In SODA for Java, a document is represented as a Java interface.
In SODA for PL/SQL, a document is represented as a PL/SQL object type.
In SODA for C, a document is represented as an Oracle Call Interface (OCI) handle.

In all cases, there are methods or functions to create documents and access their components.

To write content to SODA collections and read content from them, you use create-document, write, and read operations:

You use a SODA create-document operation to create a document object with content that you provide. (The content can be JSON data or something else.)
You use a SODA write operation (such as insert), to store the document persistently in Oracle Database. (The document content is written to a database table.)
You use a SODA read operation (such as find), to fetch the document back from Oracle Database. You use specific getter operations to read specific document components (such as key and content).

1.2 Overview of SODA Document Collections

A SODA document collection is a set of documents that is backed by an Oracle Database table or view.

By default, creating a SODA document collection creates the following in Oracle Database:

Persistent default collection metadata.
A table for storing the collection, in the database schema to which your SODA client is connected.

All SODA implementations provide a get-metadata operation, which returns the metadata for a collection, represented in JSON. The default collection metadata, which is returned for a default collection, is shown in Example 1-1.

The default metadata specifies a collection that tracks five components for each document: key, content, version, last-modified time stamp, and created-on time stamp. These are specified in JSON by fields keyColumn, contentcolumn, versionColumn, lastModifiedColumn, and creationTimeColumn, respectively. Each of these components is stored in a separate column in the table or view that backs the collection in Oracle Database. The metadata further specifies various details about these components and the database columns that back them.

In Example 1-1, for the key component: the column name is "ID", the column type is "VARCHAR2", the maximum key length is 255, and the key generation method used is "UUID".

In a client application, a document collection is represented in a way that is appropriate to the particular SODA implementation used. For example:

In SODA for Java, a collection is represented as a Java interface.
In SODA for PL/SQL, a collection is represented as a PL/SQL object type.
In SODA for C, a collection is represented as an Oracle Call Interface (OCI) handle.

When a collection is created, the create-collection operation returns a Java or PL/SQL object or an OCI handle, which you can use to perform various collection read and write operations.^Foot 3

When you create a collection you can specify things such as the following:

Storage details, such as the name of the table that stores the collection and the names and data types of its columns.
The presence or absence of columns for creation time stamp, last-modified time stamp, and version.
Whether the collection can store only JSON documents.
Methods of document key generation, and whether document keys are client-assigned or generated automatically.
Methods of version generation.

This configurability also lets you map a new collection to an existing database table or view.

To configure a collection in a nondefault way, you must define custom collection metadata and pass it to the create-collection operation. This metadata is represented as JSON data.

Note:

Although you can customize collection metadata to obtain different behavior from that provided by default, Oracle recommends against this unless you have a compelling reason. Customizing collection metadata requires familiarity with Oracle Database concepts, such as SQL data types. Because SODA collections are implemented on top of Oracle Database tables (or views), many collection configuration components are related to the underlying table configuration.

Reasons to use custom metadata include:

To configure SecureFiles LOB storage.
To configure a collection to store documents other than JSON (a heterogeneous collection).
To map an existing Oracle Database table or view to a new collection.
To specify that a collection mapping to an existing table is read-only.
To use a VARCHAR2 column for JSON content, and to increase the default maximum length of data allowed in the column.

You might want to increase the maximum allowed data length if your database is configured with extended data types, which extends the maximum length of these data types to 32767 bytes. For more information about extended data types, see Oracle Database SQL Language Reference.

1.3 Default Naming of a Collection Table

By default, the name of the table underlying a document collection is derived from the collection name.

If you want a different table name from that provided by default then use custom collection metadata to explicitly provide the name.

The default table name is derived from the collection name you provide, as follows:

Each ASCII control character and double quotation mark character (") in the collection name is replaced by an underscore character (_).
If all of the following conditions apply, then all letters in the name are converted to uppercase, to provide the table name. In this case, you need not quote the table name in SQL code; otherwise, you must quote it.
- The letters in the name are either all lowercase or all uppercase.
- The name begins with an ASCII letter.
- Each character in the name is alphanumeric ASCII, an underscore (_), a dollar sign ($), or a number sign (#).
  
  Note:
  
  Oracle recommends that you do not use dollar-sign characters ($) or number-sign characters (#) in Oracle identifier names.

For example:

Collection names "col" and "COL" both result in a table named "COL". When used in SQL, the table name is interpreted case-insensitively, so it need not be enclosed in double quotation marks (").
Collection name "myCol" results in a table named "myCol". When used in SQL, the table name is interpreted case-sensitively, so it must be enclosed in double quotation marks (").

Related Topics

Table or View

Footnote Legend

Footnote 1: SODA for REST is an exception in this regard; you cannot use database transactions for its actions.
Footnote 2: Because REST is not a programming language, SODA for REST has no programmatic “object” that represents a document. But SODA for REST operations involve the same concept of a document. For example, when you read a document you obtain a JSON representation of it, which includes all of the components (key, content, version, and so on).
Footnote 3: This is the case only for language-based SODA implementations. In SODA for REST a collection is essentially represented by just a URL.
Footnote 4: SODA for REST is an exception here. for security reasons, in this context an error is raised for SODA for REST, to disallow access to existing tables using REST operations.