Modifying Table Data Using APIs

Learn how to update and delete Oracle NoSQL Database Cloud Service table data using APIs.

This article has the following topics:

Modifying Tables

Learn how to modify tables.

You modify a table to:

  • Add new fields to an existing table

  • Delete currently existing fields in a table

  • To change the default TTL value

  • Modify table limits

Examples of DDL statements are:
/* Add a new field to the table */
ALTER TABLE users (ADD age INTEGER)

/* Drop an existing field from the table */
ALTER TABLE users (DROP age)

/* Modify the default TTL value*/
ALTER TABLE users USING TTL 4 days

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle . To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

When altering a table with provisioned capacity, you may also use the TableRequests.setTableLimits method to modify table limits.
TableLimits limits = new TableLimits(40, 10, 5);
TableRequest treq = new TableRequest().setTableName( "users" ).setTableLimits(limits);
TableResult tres = handle.tableRequest(treq);
/* wait for completion of the operation */ tres.waitForCompletion(handle, 60000,
/* wait for 60 sec */
1000);
You can also use the Oracle NoSQL Database Java SDK to modify a table and change the capacity model to an on-demand capacity configuration. You can also choose to change the storage capacity.
// Previous limit in Provisioned Mode
// TableLimits limits = new TableLimits(40, 10, 5);    
// Call the constructor to only set storage limit (for on-demand)    
TableLimits limits = newTableLimits(10);    
TableRequest treq = newTableRequest().setTableName("users").setTableLimits(limits);    
TableResult tres = serviceHandle.tableRequest(treq);    
tres.waitForCompletion(serviceHandle, 50000,3000);
You can also change the definition of the table. The TTL value is changed below.
/* Alter the users table to modify the TTL value to 4 days. 
* When modifying the table schema or other table state you cannot also 
* modify the table limits. These must be independent operations. 
*/
String alterTableDDL = "ALTER TABLE users " + "USING TTL 4 days";
TableRequest treq = new TableRequest().setStatement(alterTableDDL); 
/* start the operation, it is asynchronous */
TableResult tres = handle.tableRequest(treq);
/* wait for completion of the operation */ 
tres.waitForCompletion(handle, 60000, 1000);
/* wait for 60 sec */
/* delay in ms for poll */
If using the Oracle NoSQL Database Cloud Service table limits can be modified using borneo.TableRequest.set_table_limits(). If the table is configured with provisioned capacity, the limits can be set as shown in the example below:
from borneo import TableLimits, TableRequest
# in this path the table name is required, as there is no DDL statement 
request = TableRequest().set_table_name('users')
request.set_table_limits(TableLimits(40, 10, 5))
result = handle.table_request(request)
# table_request is asynchronous, so wait for the operation to complete, 
# wait for 40 seconds, polling every 3 seconds 
result.wait_for_completion(handle, 40000, 3000)
You can also use the Oracle NoSQL Database Python SDK to modify a table and change the capacity model to an on-demand capacity configuration. You can also choose to change the Storage capacity.
from borneo import TableLimits, TableRequest
# in this path the table name is required, as there is no DDL statement
request = TableRequest().set_table_name('users')
request.set_table_limits(TableLimits(10))
result = handle.table_request(request)
# table_request is asynchronous, so wait for the operation to complete,
# wait for 40 seconds, polling every 3 seconds
result.wait_for_completion(handle, 40000, 3000)
Specify the DDL statement and other information in a TableRequest, and execute the request using the nosqldb.DoTableRequest() or nosqldb.DoTableRequestAndWait() function.
req:=&nosqldb.TableRequest{
    Statement: "ALTER TABLE users (ADD age INTEGER)",
}
res, err:=client.DoTableRequestAndWait(req, 5*time.Second, time.Second)
The Oracle NoSQL Database Cloud Service table limits can be modified using TableRequest.TableLimits. If the table is configured with provisioned capacity, the limits can be set as shown in the example below.
req := &nosqldb.TableRequest{
    TableName: "users",
    TableLimits: &nosqldb.TableLimits{
        ReadUnits: 100,
        WriteUnits: 100,
        StorageGB: 5,
    },
}
res, err := client.DoTableRequestAndWait(req, 5*time.Second, time.Second)
You can also use the Oracle NoSQL Database Go SDK to modify a table and change the capacity model to an on-demand capacity configuration. You can also choose to change the Storage capacity.
req := &nosqldb.TableRequest{ TableName: "users",
TableLimits: &nosqldb.TableLimits{StorageGB: 10}}
res, err := client.DoTableRequestAndWait(req, 5*time.Second, time.Second)
Use NoSQLClient#tableDDL to modify a table by issuing a DDL statement against this table. Table limits can be modified using setTableLimits method. It takes table name and new TableLimits as arguments and returns Promise of TableResult. If the table is configured with provisioned capacity, the limits can be set as shown in the example below.
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const TableState = require('oracle-nosqldb').TableState;
const client = new NoSQLClient('config.json');

async function modifyUsersTableLimits() {
    const tableName = 'users';
    try {
        let result = await client.setTableLimits(tableName, {
            readUnits: 40,
            writeUnits: 10,
            storageGB: 5
        });
        // Wait for the operation completion using specified timeout and
        // specified polling interval (delay)
        await client.forCompletion(result, TableState.ACTIVE, {
            timeout: 30000,
            delay: 2000
        });
        console.log('Table limits modified');
    } catch(error) {
        //handle errors
    }
}
You can also use the Oracle NoSQL Database Node.js SDK to modify a table and change the capacity model to an on-demand capacity configuration. You can also choose to change the Storage capacity.
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const TableState = require('oracle-nosqldb').TableState;
const client = new NoSQLClient('config.json');
async function modifyUsersTableLimits() {
const tableName = 'users';
  try {
     let result = await client.setTableLimits(tableName, {storageGB: 10 });
     // Wait for the operation completion using specified timeout and polling interval (delay)
     await client.forCompletion(result, TableState.ACTIVE, { timeout: 30000, delay: 2000 });
     console.log('Table limits modified');
  } 
  catch(error) { //handle errors } 
}
Use ExecuteTableDDLAsync or ExecuteTableDDLWithCompletionAsync to modify a table by issuing a DDL statement against this table. Table limits can be modified using SetTableLimitsAsync or SetTableLimitsWithCompletionAsync methods. They take table name and new TableLimits as parameters and return Task<TableResult>. If the table is configured with provisioned capacity, the limits can be set as shown in the example below.
var client = new NoSQLClient("config.json");
var tableName = "users";
try {
    var result = await client.SetTableLimitsWithCompletionAsync(
       tableName, new TableLimits(40, 10, 5));
    // Expected output: Table state is Active.
    Console.WriteLine("Table state is {0}.", result.TableState);
    Console.WriteLine("Table limits have been changed");
}
catch(Exception ex) {
    // handle exceptions
}
You can also use the Oracle NoSQL Database .NET SDK to modify a table and change the capacity model to an on-demand capacity configuration. You can also choose to change the Storage capacity.
var client = new NoSQLClient("config.json");
var tableName = "users";
try {
   var result = await client.SetTableLimitsWithCompletionAsync( tableName, new TableLimits(10));
   // Expected output: Table state is Active.
   Console.WriteLine("Table state is {0}.", result.TableState);
   Console.WriteLine("Table limits have been changed");
}
catch(Exception ex) { // handle exceptions }

Deleting Data

Learn how to delete rows from your table.

After you insert or load data into a table, you can delete the table rows when they are no longer required.

The following example considers that the default compartment is specified in NoSQLHandleConfig while obtaining the NoSQL handle. See Obtaining a NoSQL Handle. To explore other options of specifying a compartment for the NoSQL tables, see About Compartments.

To delete a row from a table:
/* identify the row to delete */
MapValue delKey = new MapValue().put("id", 2);

/* construct the DeleteRequest */
DeleteRequest delRequest = new DeleteRequest().setKey(delKey)
                                              .setTableName("users");
/* Use the NoSQL handle to execute the delete request */
DeleteResult del = handle.delete(delRequest);
/* on success DeleteResult.getSuccess() returns true */
if (del.getSuccess()) {
  // success, row was deleted
} else {
  // failure, row either did not exist or conditional delete failed
}

You can perform a sequence of DeleteRequest operations on a table using the MultiDeleteRequest class.

See the Java API Reference Guide for more information about the APIs.

Single rows are deleted using borneo.DeleteRequest using a primary key value as shown below.
from borneo import DeleteRequest
# DeleteRequest requires table name and primary key
request = DeleteRequest().set_table_name('users') 
request.set_key({'id': 1})
# perform the operation
result = handle.delete(request)
if result.get_success():
# success -- the row was deleted
# if the row didn't exist or was not deleted for any other reason, False is returned

Delete operations can be conditional based on a borneo.Version returned from a get operation.You can perform multiple deletes in a single operation using a value range using borneo.MultiDeleteRequest and borneo.NoSQLHandle.multi_delete() .

Single rows are deleted using nosqldb.DeleteRequest using a primary key value:
key := &types.MapValue{}
key.Put("id", 1)
req := &nosqldb.DeleteRequest{
    TableName: "users",
    Key: key,
}
res, err := client.Delete(req)

Delete operations can be conditional based on a types.Version returned from a get operation.

To delete a row, use delete method. Pass to it the table name and primary key of the row to delete. In addition, you can make delete operation conditional by specifying on a Version of the row that was previously returned by get or put. You can pass it as matchVersion property of the opt argument: { matchVersion: my_version }. Alternatively you may use deleteIfVersion method.

delete and deleteIfVersion methods return Promise of DeleteResult, which is plain JavaScript object, containing success status of the operation.
const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const client = new NoSQLClient('config.json');

async function deleteRowsFromUsersTable() {
    const tableName = 'users';
    try {
        let result = await client.put(tableName, { id: 1, name: 'John' });

        // Unconditional delete, should succeed
        result = await client.delete(tableName, { id: 1 });
        // Expected output: delete succeeded
        console.log('delete ' + result.success ? 'succeeded' : 'failed');

        // Delete with non-existent primary key, will fail
        result = await client.delete(tableName, { id: 2 });
        // Expected output: delete failed
        console.log('delete ' + result.success ? 'succeeded' : 'failed');

        // Re-insert the row
        result = await client.put(tableName, { id: 1, name: 'John' });
        let version = result.version;

        // Will succeed because the version matches existing row
        result = await client.deleteIfVersion(tableName, { id: 1 }, version);
        // Expected output: deleteIfVersion succeeded
        console.log('deleteIfVersion ' + result.success ?
            'succeeded' : 'failed');

        // Re-insert the row
        result = await client.put(tableName, { id: 1, name: 'John' });

        // Will fail because the last put has changed the row version, so
        // the old version no longer matches.  The result will also contain
        // existing row and its version because we specified returnExisting in
        // the opt argument.
        result = await client.deleteIfVersion(tableName, { id: 1 }, version,
            { returnExisting: true });
        // Expected output: deleteIfVersion failed
        console.log('deleteIfVersion ' + result.success ?
            'succeeded' : 'failed');
        // Expected output: { id: 1, name: 'John' }
        console.log(result.existingRow);
    } catch(error) {
        //handle errors
    }
}

Note that similar to put operations, success results in false value only if trying to delete row with non-existent primary key or because of version mismatch when matching version was specified. Failure for any other reason will result in error. You can delete multiple rows having the same shard key in a single atomic operation using deleteRange method. This method deletes set of rows based on partial primary key (which must be a shard key or its superset) and optional FieldRange which specifies a range of values of one of the other (not included into the partial key) primary key fields.

To delete a row, use DeleteAsync method. Pass to it the table name and primary key of the row to delete. This method takes the primary key value as MapValue. The field names should be the same as the table primary key column names. You may also pass options as DeleteOptions. In addition, you can make delete operation conditional by specifying on a RowVersion of the row that was previously returned by GetAsync or PutAsync. Use DeleteIfVersionAsync method that takes the row version to match. Alternatively, you may use DeleteAsync method and pass the version as MatchVersion property of DeleteOptions.
var client = new NoSQLClient("config.json");
var tableName = "users";
try
{
    var row = new MapValue
    {
        ["id"] = 1,
        ["name"] = "John"
    };

    var putResult = await client.PutAsync(tableName, row);
    Console.WriteLine("Put {0}.",
       putResult.Success ? "succeeded" : "failed");

    var primaryKey = new MapValue
    {
        ["id"] = 1
    };
    // Unconditional delete, should succeed.
    var deleteResult = await client.DeleteAsync(tableName, primaryKey);
    // Expected output: Delete succeeded.
    Console.WriteLine("Delete {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Delete with non-existent primary key, should fail.
    var deleteResult = await client.DeleteAsync(tableName,
        new MapValue
        {
            ["id"] = 200
        });
    // Expected output: Delete failed.
    Console.WriteLine("Delete {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Re-insert the row and get the new row version.
    putResult = await client.PutAsync(tableName, row);
    var version = putResult.Version;
    // Delete should succeed because the version matches existing
    // row.
    deleteResult = await client.DeleteIfVersionAsync(tableName,
        primaryKey, version);
    // Expected output: DeleteIfVersion succeeded.
    Console.WriteLine("DeleteIfVersion {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Re-insert the row
    putResult = await client.PutAsync(tableName, row);
    // This delete should fail because the last put operation has
    // changed the row version, so the old version no longer matches.
    // The result will also contain existing row and its version because
    // we specified ReturnExisting in DeleteOptions.
    deleteResult = await client.DeleteIfVersionAsync(tableName,
        primaryKey, version);
    // Expected output: DeleteIfVersion failed.
    Console.WriteLine("DeleteIfVersion {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Expected output: { "id": 1, "name": "John" }
    Console.WriteLine(result.existingRow);
}
catch(Exception ex) {
    // handle exceptions
}
Note that Success property of the result only indicates whether the row to delete was found and for conditional Delete, whether the provided version was matched. If the Delete operation fails for any other reason, an exception will be thrown. You can delete multiple rows having the same shard key in a single atomic operation using DeleteRangeAsync method. This method deletes set of rows based on partial primary key (which must include a shard key) and optional FieldRange which specifies a range of values of one of the other (not included into the partial key) primary key fields.