Pre-General Availability: 2017-09-04

4 NoSQL and Oracle REST Data Services

This chapter explains how to use Oracle REST Data Services to provide HTTP-based REST access to Oracle NoSQL Database (also referred to here as NoSQL) data and metadata.

See also some NoSQL example files included in the Oracle REST Data Services installation. For a short document about how to get started accessing NoSQL stores, double-click the file examples/getting-started-nosql/index.html under the location where you installed Oracle REST Data Services.

Topics:

4.1 About NoSQL and Oracle REST Data Services

Oracle REST Data Services can provide HTTP-based REST service access to Oracle NoSQL Database, through operations on the NoSQL data and metadata.Data access consists of CRUD (create, read, update, delete) operations on NoSQL tables:

Create: HTTP POST and PUT to provide services to create row data.
Read: HTTP GET to provide key, partial key, and field range queries on row data, with directives for consistency and order.
Update: HTTP PUT to provide services to update row data.
Delete: HTTP DELETE to provide services to delete row data.

Metadata access consists of HTTP GET to provide services to read NoSQL metadata.

Topics:

4.1.1 Technology Environment

The key pieces in the technology environment for using Oracle REST Data Services with NoSQL are:

REST services

Direct web service calls to Oracle NoSQL Database, and marshall data returned into JSON format.

Can be deployed using Oracle WebLogic Server, GlassFish, or Apache Tomcat.
Oracle NoSQL Database (Release 3.2.5 or later)

Provides full CRUD (create, read, update, delete) operations across single and master/replicated nodes.

Provides access through the Oracle NoSQL Database driver (.jar file).

The essential components of this environment are:

A client, such as Java, JavaScript, or C++
A web application server, such as standalone (Jetty), Oracle WebLogic Server, GlassFish, Apache Tomcat
Oracle REST Data Services: the HTTP-based REST service provider
NoSQL servlets: Oracle REST Data Services components to provide CRUD operations on the NoSQL database
Oracle NoSQL Database: distributed master/replica NoSQL database that is the target of the NoSQL servlets

Figure 4-1 provides an overview of a typical environment for Oracle REST Data Services with NoSQL.

Figure 4-1 Overview of Typical Environment

Description of "Figure 4-1 Overview of Typical Environment"

In Figure 4-1:

Oracle REST Data Services is deployed in the web server.
Clients submit HTTP requests to Oracle REST Data Services inside the web server, and receive responses. In this case the HTTP requests are targeting NoSQL stores DB1 and DB2.
Oracle REST Data Services users and roles are used by Oracle REST Data Services.
Two NoSQL stores (named DB1 and DB2) communicate with Oracle REST Data Services inside the web server.
Configuration information for the two NoSQL stores is used by Oracle REST Data Services. The DB1 store is secure (includes the NoSQL user, roles, and SSL details), and the DB2 store is non-secure. The configuration information was specified the stores were registered with Oracle REST Data Services.

4.1.2 Typical Oracle REST Data Services Input and Output

Example 4-1 is a REST service request (input) to get metadata about tables from a NoSQL database.

Example 4-1 Getting Metadata from a NoSQL Database

http://localhost:8080/ords/sales/metadata-catalog/

In Example 4-1:

http://localhost:8080/ords is the URL for accessing the Oracle REST Data Services web application from the web server running on localhost with port 8080.
/sales is the NoSQL database identifier. This identifier will map to a NoSQL configuration.
metadata-catalog/ is the component that services the metadata request.

Example 4-1 might product output similar to the following in response:

{ 
 "items": [ 
     { 
         "name": "complexUsers", 
         "links": [
                {
                    "rel": "canonical",
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/complexUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/complexUsers/"
                }
         ] 
     }, 
     { 
         "name": "evolveUsers", 
         "links": [
 
                {
 
                    "rel": "canonical",
 
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/evolveUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/evolveUsers/"
   .
   .
   .

4.2 NoSQL Store Installation and Registration

To install and register a NoSQL store involves regarding the Oracle REST Data Services installation host as a NoSQL Client. Therefore, if the NoSQL store is secure, then the security properties file (client.security) and the SSL information file (client.trust) must be copied to the Oracle REST Data Services host's network. These files are generated when the NoSQL store is first established, as explained in the NoSQL documentation.

If the NoSQL store is secure, it must be accessed by a NoSQL user. NoSQL provide a Wallet (for Enterprise Editions) and a password file (for Community editions) to hold password information. The password file is not secure because it is plain text, so Oracle REST Data Services provides an alternative storage of passwords. Creating the Wallet is also described in the NoSQL documentation; and if the Wallet is used, it too must be copied into the Oracle REST Data Services host's network.

Each Oracle REST Data Services installation has a configuration directory, which includes a nosql directory for NoSQL store configurations. The nosql directory will have one directory for each NoSQL store, and each store-specific directory includes the following files:

client.security  (for Secure noSQL stores)
client.trust (for Secure NoSQL stores)
Wallet directory (Optional for NoSQL secure stores)
nosql.properties

The nosql.properties file includes the following:

oracle.dbtools.kv.store.name=<storename>
oracle.dbtools.kv.store.hosts=<comma separated set of HOSTS:PORTS>
[And optionally the following:]
oracle.dbtools.kv.store.pagelimit=<maximum number of items per page>
oracle.dbtools.kv.store.security.properties=client.security  (the name of the NoSQL client.security file)
oracle.dbtools.kv.store.requiredRoles=<list of comma separated roles>

If oracle.dbtools.kv.store.requiredRoles property is not included in the nosql.properties file, then the REST service is authorized for anonymous users; otherwise access is granted to any user that has any of these roles.

The client.security file contains the SSL properties, and it also contains the following:

oracle.kv.auth.username=<NoSQL user used for ORDS to access the store>

The client.security file may also need to include the following:

oracle.kv.auth.wallet.dir=<wallet directory> to specify the wallet directory, if a wallet is used
oracle.dbtools.kv.store.auth.pwd=<encrypted pwd> to specify the password for the NoSQL user, if a password option is used

Note all path names in the client.security file must be specified with the full path.

The following examples show:

A store accessed by Oracle REST Data Services anonymous users on the localhost port 5000 with name kvstore (a typical KVLite store).

ords/nosql/sales
     nosql.properties
          oracle.dbtools.kv.store.name=kvstore
          oracle.dbtools.kv.store.hosts=localhost:5000

A store accessed by Oracle REST Data Services users with the role SALES on the localhost port 5000 with name kvstore (a typical KVLite store).

ords/nosql/sales
     nosql.properties
          oracle.dbtools.kv.store.name=kvstore
          oracle.dbtools.kv.store.hosts=localhost:5000
          oracle.dbtools.kv.store.requiredRoles=SALES

A secure store on xyz6160769 port 5000 accessed with user NOSQL_PUBLIC_USER and whose password is stored in a wallet. It can be accessed by anonymous Oracle REST Data Services users.

ords/nosql/sales
   nosql.properties
      oracle.dbtools.kv.store.name=mystore
      oracle.dbtools.kv.store.hosts=xyz6160769.example.com:5000
      oracle.dbtools.kv.store.security.properties=client.security
   client.security
      oracle.kv.ssl.trustStore=d:/nosqlconfs/ords/nosql/salesecure/client.trust
      oracle.kv.transport=ssl
      oracle.kv.ssl.protocols=TLSv1.2,TLSv1.1,TLSv1
      oracle.kv.ssl.hostnameVerifier=dnmatch(CN\=NoSQL)
      oracle.kv.auth.username=NOSQL_PUBLIC_USER
      oracle.kv.auth.wallet.dir=d:/nosqlconfs/ords/nosql/salesecure/wallet
   client.trust
   wallet (directory)

A secure store on xyz6160769 port 5000 accessed with user NOSQL_PUBLIC_USER and whose password is stored in the properties file (encoded) It can be accessed by anonymous Oracle REST Data Services users.

ords/nosql/sales
   nosql.properties
      oracle.dbtools.kv.store.name=mystore
      oracle.dbtools.kv.store.hosts=xyz6160769.example.com:5000
      oracle.dbtools.kv.store.security.properties=client.security
   client.security
      oracle.kv.ssl.trustStore=d:/nosqlconfs/ords/nosql/salesecure/client.trust
      oracle.kv.transport=ssl
      oracle.kv.ssl.protocols=TLSv1.2,TLSv1.1,TLSv1
      oracle.kv.ssl.hostnameVerifier=dnmatch(CN\=NoSQL)
      oracle.kv.auth.username=NOSQL_PUBLIC_USER
      oracle.dbtools.kv.store.auth.pwd=0571B5B831C9945D167D2510B5E6BB021B
   client.trust

Related Topics

4.3 Adding and Removing a NoSQL Store for Use with Oracle REST Data Services

To add a NoSQL store, use the java command with the nosqladd keyword.

Usage:

java -jar ords.war nosqladd [--secure] [--clientTrustFile] [--clientSecurityFile] [--walletDir] [--password] [--user] [--pagelimit] <storeAlias> <storeName> <hostPorts> <roles>

Options:

[--secure] Flag indicating that the store is secure.
[--clientTrustFile] Location of the client.trust file, which will be copied from this location into the NoSQL configuration area).
[--clientSecurityFile] Location of the client.security file, which will be copied from this location, with potential updates, to the NoSQL configuration area.
[--walletDir] Location of the wallet directory (folder), which will be copied from this location to the NoSQL configuration area.
[--password] Password for the connection.
[--user] User name for the connection.
[-- pagelimit] Maximum number of items per page for this store.

Arguments:

<storeAlias> Store alias.
<storeName> Store name.
<hostPorts> Host:Port values (comma-separated list).
<roles> Roles (comma-separated list).

To remove (delete) a NoSQL store, use the java command with the nosqldel keyword.

Usage:

java -jar ords.war nosqldel <storeAlias>

Arguments:

<storeAlias> Store alias.

4.4 NoSQL REST Services

A NoSQL request has the following format:

host:port/ords/<nosqlalias>/<nosqlservice>

Where:

<nosqlalias> is the alias for the NoSQL store.
<nosqlservice> is an appropriate string for the metadata, data, or DDL service being invoked.

Topics:

4.4.1 Metadata Services

GET (Read Metadata) is the only NoSQL service available.

Topics:

Read Metadata (GET)

4.4.1.1 Read Metadata (GET)

The GET (Read Metadata) service has different <nosqlservice> formats and output, depending on whether the request is for metadata for all tables or a specified table.

For all tables: <nosqlservice> = metadata-catalog/

The JSON output is of the following format, which has links to each table's metadata and the first page of data. For example:

{
 
    "items": [
        {
            "name": "complexUsers",
            "links": [
                {
                    "rel": "canonical",
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/complexUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/complexUsers/"
                }
            ]
        },
        {
            "name": "shardUsers",
            "links": [
                {
                              "rel": "canonical",
                              "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/shardUsers/",
                    "mediaType": "application/json"
                },
                {
                             "rel": "describes",
                              "href": "http://localhost:8080/ords/sales/tables/shardUsers/"
                }
            ]
        },
        {
            "name": "simpleUsers",
            "links": [
                {
                              "rel": "canonical",
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/simpleUsers/"
                }
            ]
        }
    ],
    "hasMore": false,
    "count": 3,
    "limit": 25,
    "offset": 0,
    "links": [
        {
            "href": "http://localhost:8080/ords/sales/metadata-catalog/",
            "rel": "self"
        }
    ]
 
}

For a specified table: <nosqlservice> = metadata-catalog/tables/<name>/ where <name> is the name of the table

The JSON output is of the following format, with properties, keys, column details, child tables, and links. For example:

{
     "type": "TABLE",
     "name": "simpleUsers",
     "fullname": "simpleUsers",
     "description": null,
     "primarykey": [
     "userID"
          ],
     "shardkey": [
          "userID"
     ],
     "members": [
          {
               "name": "firstName",
               "type": "STRING",
               "description": null
          },
          {
               "name": "lastName",
               "type": "STRING",
               "description": null
          },
          {
               "name": "userID",
               "type": "INTEGER",
               "description": null
          }
          ],
          "indexes": [
               {
                    "name": "compoundIndex",
                    "description": null,
                    "indexfields": [
                         "lastName",
                         "firstName"
                    ]
               },
               {
                    "name": "simpleIndex",
                    "description": null,
                    "indexfields": [
                         "firstName"
                    ]
               }
          ],
          "childtables": [ ],
          "links": [
              {               "rel": "collection",               "href": "http://localhost:8080/ords/sales/metadata-catalog/"              },
               {
                   "rel": "canonical",
                   "href":        "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/",
                   "mediaType": "application/json"
               },
               {
                   "rel": "describes",
                   "href": "http://localhost:8080/ords/sales/tables/simpleUsers/"
               }
          ]
}

For a specified table's items: <nosqlservice> = metadata-catalog/tables/<name>/item where <name> is the name of the table

The JSON output is of the following format, with properties, keys, column details, child tables, and links. For example:

{
     "name": "simpleUsers",
     "fullname": "simpleUsers",
     "description": null,
     "primarykey": [
     "userID"
          ],
     "members": [
          {
               "name": "firstName",
               "type": "STRING",
               "description": null
          },
          {
               "name": "lastName",
               "type": "STRING",
               "description": null
          },
          {
               "name": "userID",
               "type": "INTEGER",
               "description": null
          }
          ],
          "links": [
               {
                 "rel": "collection",
                 "href": "http://localhost:8080/ords/sales/metadata-catalog/"
               },
               {
                   "rel": "canonical",
                   "href":        "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item",
                   "mediaType": "application/json"
               }
          ]
}

4.4.2 Data Services

Services are available for reading, writing, and deleting NoSQL data.

Topics:

4.4.2.1 Read Data (GET)

The GET (Read Data) service has different <nosqlservice> formats and output, depending on whether the request is for all data in a table or a subset of the data in a table.

For all data in a table: <nosqlservice> = tables/<name>/

The JSON output is of the following format. The data is returned in an unordered sequence, and it includes property pair values and links from the collection to show where it belongs and that it is editable. For example:

{ 
    "items": [ 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAQABAwAAAAEAAAABAAAAAAAEwTE.", 
            "firstName": "Bob", 
            "lastName": "Johnson", 
            "userID": 109, 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/simpleUsers/109", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item",
                    "rel": "describedby"
                }
            ] 
        }, 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAKYBAwAAAAEAAAABAAAAAAAEZNg.", 
            "firstName": "Alex", 
            "lastName": "Robertson", 
            "userID": 1, 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/simpleUsers/1", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item",
                    "rel": "describedby"
                }
            ] 
        },……..
   "accepts": [ 
        "application/json" 
    ],
    "hasMore": false,
    "count": 6,
    "limit": 25,
    "offset": 0,
    "links": [ 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/", 
            "rel": "edit", 
            "targetSchema": "application/json" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/", 
            "rel": "describedby" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/", 
            "rel": "self" 
        } 
    ] }

For data in a table where keys are specified: <nosqlservice> = tables/<name>/<keys>

<name> is the name of the table, and <keys> is a set of comma-separated index key values. The key values must be in the order of key definition. If not all key parts are specified, retrieval is based on partial keys. If the full key is specified, the output is a single item.

The JSON output includes property pair values and links from the collection to show where it belongs. The following example shows JSON output when a full key is specified (tables/shardUsers/Robertson,Alex).

{ 
    "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAKYBAwAAAAEAAAABAAAAAAAEZNg.", 
    "firstName": "Alex", 
    "lastName": "Robertson", 
    "userID": 1, 
    "accepts": [ 
        "application/json" 
    ], 
    "links": [ 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/1", 
            "rel": "self" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item", 
            "rel": "describedby" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/1", 
            "rel": "edit", 
            "targetSchema": "application/json" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/", 
            "rel": "collection" 
        } 
    ] }

The following example shows JSON output where a partial key is specified (tables/shardUsers/Robertson).

{ 
    "items": [ 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAMIBAwAAAAEAAAABAAAAAAAEbLw.", 
            "firstName": "Alex", 
            "lastName": "Robertson", 
            "email": "alero@email.com", 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/shardUsers/Robertson,Alex", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/shardUsers/item",
                    "rel": "describedby"
                }
            ] 
        }, 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAMQBAwAAAAEAAAABAAAAAAAEbSE.", 
            "firstName": "Beatrix", 
            "lastName": "Robertson", 
            "email": "bero@email.com", 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/shardUsers/Robertson,Beatrix", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/shardUsers/item",
                    "rel": "describedby"
                }
            ] 
        } 
    ], 
    "accepts": [ 
        "application/json" 
    ],
    "hasMore": false,
    "count": 2,
    "limit": 25,
    "offset": 0,
    "links": [ 
        { 
            "href": "http://localhost:8080/ords/sales/tables/shardUsers/", 
            "rel": "edit", 
            "targetSchema": "application/json" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/shardUsers/", 
            "rel": "describedby" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/shardUsers/", 
            "rel": "up" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/shardUsers/Robertson", 
            "rel": "self" 
        } 
    ] }

For data in a table where an index and keys are specified: <nosqlservice> = tables/<name>/index/<indexname>/<keys>

<name> is the name of the table, <indexname> is the name of the index, and <keys> is a set of comma-separated index key values. The key values must be in the order of key definition. If not all key parts are specified, retrieval is based on partial keys. If the full key is specified, the output is a single item.

The JSON output includes property pair values and links from the collection to show where it belongs and that it is editable. For example:

{ 
    "items": [ 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAALIBAwAAAAEAAAABAAAAAAAEaIk.", 
            "firstName": "Joel", 
            "lastName": "Robertson", 
            "userID": 3, 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/simpleUsers/3", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item",
                    "rel": "describedby"
                }  
            ] 
        }, 
        { 
            "$version": "rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAL4BAwAAAAEAAAABAAAAAAAEa4k.", 
            "firstName": "Joel", 
            "lastName": "Jones", 
            "userID": 6, 
            "links": [ 
                { 
                    "href": "http://localhost:8080/ords/sales/tables/simpleUsers/6", 
                    "rel": "self" 
                },
                {
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/item",
                    "rel": "describedby"
                }   
            ] 
        } 
    ],
    "hasMore": false,
    "count": 2,
    "limit": 25,
    "offset": 0,
    "links": [ 
        { 
            "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/simpleUsers/", 
            "rel": "describedby" 
        }, 
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/index/simpleIndex/Joel", 
            "rel": "self" 
        },
        { 
            "href": "http://localhost:8080/ords/sales/tables/simpleUsers/", 
            "rel": "collection" 
        }
    ] }

4.4.2.2 Idempotent Write (PUT)

The PUT (Idempotent Write) service has different <nosqlservice> formats and output, depending on whether the operation is upsert, put if present, or put if version. ("Put if version" means that the PUT operation will succeed only if the latest version in the NoSQL database is the stated version. This prevents a client from overwriting a newer value with an older value.)

For Upsert (update it present, create if not): <nosqlservice> = tables/<name>/

The body of the request is JSON format of the form returned by the Read Data (GET) operations, but without links. For example:
```
{ "userID": 108, "firstName": "Bob", "lastName": "Johnson"}
```
For Put if Present: <nosqlservice> = tables/<name>/<key>

<name> is the name of the table, and <key> is the primary key value of the new object.

The body of the request is JSON format of the form returned by the Read Data (GET) operations, but without links. For example:
```
{ "userID": 108, "firstName": "Bob", "lastName": "Johnson"}
```
If the row is not present, the status 404 (NOT_FOUND) is returned.
For Put if Present for a specified version: <nosqlservice> = tables/<name>/<key>

<name> is the name of the table, <key> is the primary key value of the new object, and the header parameter If-Match specifies the value of the $version property on an item. If the version matches the one specified, then it is updated.

The body of the request is JSON format of the form returned by the Read Data (GET) operations, but without links. For example:
```
{ "userID": 108, "firstName": "Bob", "lastName": "Johnson"}
If-Match= rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAL4BAwAAAAEAAAABAAAAAAAEa4k
```
If the row is not present, the status 404 (NOT_FOUND) is returned. If the row does not match the specified version, the status 412 (PRECONDITION_FAILED) is returned.

Related Topics

Read Data (GET)

4.4.2.3 Write (POST)

The POST (Put if Absent) service has the following <nosqlservice> format: <nosqlservice> = tables/<name>/

<name> is the name of the table.

The body of the request is JSON format of the form returned by the Read Data (GET) operations, but without links. For example:

{ "userID": 108, "firstName": "Bob", "lastName": "Johnson"}

If the row is present, the status 400 ((BAD_REQUEST) This row already exists) is returned. If the row is absent, the status 201 (CREATED) is returned.

Related Topics

Read Data (GET)

4.4.2.4 Delete (DELETE)

The DELETE (Delete) service can have an optional version identifier specified.

For Delete with no version specified: <nosqlservice> = tables/<name>/<keys>

<name> is the name of the table, and <keys> is the primary key value or set of comma--separated primary key values of the rows to be deleted. If not all key parts are specified, deletion is based on partial keys.

If a specified row is present, the status 204 (NO_CONTENT) is returned. If a specified row is absent, the status 404 (NOT_FOUND) is returned.
For Delete if the version and keys must result in only one object being returned (that is, the object is fully specified): <nosqlservice> = tables/<name>/<keys>

<name> is the name of the table, <keys> is the primary key value or set of comma--separated primary key values of the rows to be deleted, and the header parameter If-Match specifies the value of the $version property on an item. If the version matches the one specified, then it is deleted. For example:
```
If-Match= rO0ABXcsAAQC5a2Fp3hHLajIZEw7k4elAAAAAAAAAL4BAwAAAAEAAAABAAAAAAAEa4k
```
If a specified row-version combination is present, the status 204 (NO_CONTENT) is returned. If a specified row is present but not for the specified version, the status 412 (PRECONDITION_FAILED) is returned. If a specified row is absent, the status 404(NOT_FOUND) is returned.

4.4.3 DDL Services

Services are available for manipulating NoSQL tables and indexes.

For table requests, the body of the request is a text format corresponding to that specified in Getting Started with Oracle NoSQL Database Tables, Appendix A ("Table Data Definition Language Overview").

Topics:

Related Topics

Getting Started with Oracle NoSQL Database Tables

4.4.3.1 Create/Update (POST)

For DDL create and update operations, the service has the following <nosqlservice> format: <nosqlservice> = ddl/

Example:

http://localhost:8080/ords/sales/ddl/

The body of the request contains the DDL to be executed on the NoSQL store. Examples of the body of the request:

CREATE TABLE IF NOT EXISTS myUsers (firstName STRING,lastName STRING,userID INTEGER,PRIMARY KEY (userID))

CREATE INDEX IF NOT EXISTS myUser_index ON myUsers(lastName, firstName)

ALTER TABLE myUsers(ADD newcolumn STRING)

If the operation succeeds, a status of OK is returned, along with a Location header variable giving the location of the new resource. For example (creating an index):

Status: 200: OK
Headers: 
Location: http://localhost:8080/ords/sales/metadata-catalog/tables/myUsers/index/myUser_index

If the operation fails, an error code is returned.

If the operation does not complete within 1 second, a status of ACCEPTED is returned, along with a task ID so that you can send requests to see if it has finished. For example (creating an index):

Status: 202: Accepted
Headers: 
Location: http://localhost:8080/ords/sales/ddl/tasks/16

4.4.3.2 Drop (POST)

For DDL drop operations, the service has the following <nosqlservice> format: <nosqlservice> = ddl/

Example:

http://localhost:8080/ords/sales/ddl/

The body of the request contains the DDL to be executed on the NoSQL store. Examples of the body of the request:

DROP INDEX IF EXISTS myUser_index ON myUsers

DROP TABLE IF EXISTS myUsers

If the operation succeeds, a status of NO_CONTENT is returned.

If the operation fails, an error code is returned.

If the operation does not complete within 1 second, a status of ACCEPTED is returned, along with a task ID so that you can send requests to see if it has finished. For example (dropping a table):

Status: 202: Accepted
Headers: 
Location: http://localhost:8080/ords/sales/ddl/tasks/17

4.4.3.3 Poll (GET)

The Poll service has the following <nosqlservice> format: <nosqlservice> = ddl/tasks/<id>

Example:

http://localhost:8080/ords/sales/ddl/tasks/17

If the operation succeeds, a status of OK is returned, and the Location header can provide additional information.

If the Location header is the same as the URL request (for example, http://localhost:8080/ords/sales/ddl/tasks/17), then the task is still ongoing.

If the Location header is blank or null, then the task has finished and this task was a drop operation, so there is no new location.

If the Location header is not blank and is not the same as the URL request, then it is the location of the new or updated resource.

4.4.4 Common Parameters

Several groups of parameters can be used for with multiple operations and for specific desired results.

Topics:

4.4.4.1 Parameters to Skip Results and Limit the Number of Results

In GET operations, you can specify a limit and an offset by using ?limit={limit}&offset={offset}. For example:

<nosqlservice> = metadata-catalog/?limit=2&offset=1

The preceding example returns two objects per page, starting with the second object (that is, skipping the first one). The JSON output might be as follows:

{
   "items": [
      {
         "name": "evolveUsers",
           "links": [
                {
                    "rel": "canonical",
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/evolveUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/evolveUsers/"
                }
            ]
            },
            {
               "name": "shardUsers",
           "links": [
                {
                    "rel": "canonical",
                    "href": "http://localhost:8080/ords/sales/metadata-catalog/tables/shardUsers/",
                    "mediaType": "application/json"
                },
                {
                    "rel": "describes",
                    "href": "http://localhost:8080/ords/sales/tables/shardUsers/"
                }
            ]
            }
      ],
      "hasMore": false,
      "count": 2,
      "limit": 2,
      "offset": 1,
   "links": [
      {
         "href": "http://localhost:8080/ords/sales/metadata-catalog/",
         "rel": "self"
      },
      {
         "href": "http://localhost:8080/ords/sales/metadata-catalog/?limit=2",
         "rel": "prev"
      },
      {
         "href": "http://localhost:8080/ords/sales/metadata-catalog/?limit=2",
         "rel": "first"
      }
   ]

4.4.4.2 Parameters to Specify Query Conditions

You can limit rows retrieved by using field ranges, and you can specify the order of retrieval. The query structure for this is a simple list of ANDed pairs. For example:

?q={"firstName":{"$between":["A","C"]},"$direction":"reverse"}

Special characters must be escaped using URL encoding. For example:

http://localhost:8080/ords/sales/tables/simpleUsers/index/simpleIndex/?q={"firstName":{"$between":%5B"A","C"%5D}}

4.4.4.3 Parameters to Specify Direction (Order), and Consistency and Durability Guarantees

You can specify values for the direction (order), consistency guarantees, and durability guarantees, overriding any related default values in the store.

For direction (order), specify the $direction property with one of the values as follows:
```
$direction = "forward" | "reverse" | "unordered"
```
For direction (order), the default is "forward".

For primary key retrieval using a partial or empty key, only "unordered" is allowed, and any other value is ignored.

For consistency guarantees, specify the $consistency property with appropriate values as follows (note that <LONG> means a long number):

$consistency = <Absolute> | <None> | <Replica> | <Time> | <Version> 
<Absolute> = "absolute"
<None> = "none"
<Replica>  =  "replica"
<Time> = <Lag> "," <Timeout>
<Lag> = <LONG> "," <Units>
<Timeout> = <LONG> "," <Units>
<Units> = "nanoseconds" | "microseconds" | "milliseconds" | "seconds" | "minutes" | "hours" | "days"
<version> = <version_id> "," <LONG> "," <Units>
<version_id> = <version id of item as returned in GET>

For example:

http://localhost:8080/ords/sales/tables/shardUsers/Robertson,Beatrix?q={"$consistency":"time(3,seconds,2,minutes)"}

For durability guarantees, specify a header called NoSQL-Write-Option with a value in the following format (note that <LONG> means a long number):

<WriteOption> =  (<DurabilityPolicy> | <DurabilityGuarantees>)  ";" <WriteTimeOut> 
<DurabilityPolicy> = "Durability" "=" <DurabilityValue>
<DurabilityValue> =  "COMMIT_NO_SYNC" | "COMMIT_SYNC" | "COMMIT_WRITE_NO_SYNC"
<DurabilityGuarantees> =  <MasterSyncPolicy> ";"  <ReplcaSyncPolicy> ";" <ReplicaAckPolicy>
<MasterSyncPolicy> = "MasterSync" "=" "NO_SYNC" | "SYNC" | "WRITE_NO_SYNC"
<ReplicaSyncPolicy> = "ReplicaSync" "=" "NO_SYNC" | "SYNC" | "WRITE_NO_SYNC"
<ReplicaAckPolicy> = "ReplicaAck" "=" "ALL" | "NONE" | "SIMPLE_MAJORITY"
<WriteTimeOut> = "TimeOut" "=" <LONG> [ ;  "Units" "=" <Units>]

For example, create a header NoSQL-Write-Option with the value:

Durability = COMMIT_NO_SYNC; TimeOut = 3; Units = seconds