Modifying Singleton Tables Using APIs

Learn how to modify a singleton table using APIs.

You modify a singleton table to:
  • Add new fields to an existing table
    Example:
    String alterTableDDL = ALTER TABLE <table name> (ADD age INTEGER);
TableRequest treq = new TableRequest().setStatement(alterTableDDL);
  • Delete currently existing fields in a table
    Example:
    String alterTableDDL = ALTER TABLE <table name> (DROP age);
TableRequest treq = new TableRequest().setStatement(alterTableDDL);
  • To change the default TTL value
    Example:
    String alterTableDDL = ALTER TABLE <table name> USING TTL 7 DAYS;
TableRequest treq = new TableRequest().setStatement(alterTableDDL);
  • Modify table limits
    Example:
    TableLimits newLimits = new TableLimits(10, 10, 1);
TableRequest treq = new TableRequest().setTableName(tableName).setTableLimits(newLimits);
  • Freeze the schema of the table

    Example: Use freeze schema if the table contains JSON columns 

    String alterTableDDL = ALTER TABLE <table name> freeze schema;
TableRequest treq = new TableRequest().setStatement(alterTableDDL);

    Example: Use freeze schema force if the table does not contain any JSON columns 

    String alterTableDDL = ALTER TABLE <table name> freeze schema force;
TableRequest treq = new TableRequest().setStatement(alterTableDDL);
  • Unfreeze the schema of the table

    Example:

    String alterTableDDL = ALTER TABLE <table name> unfreeze schema;
TableRequest treq = new TableRequest().setStatement(alterTableDDL);

Related Topics

Using TableRequest API to alter table

You can use TableRequest API to change the definition of a NoSQL table.

The TableRequest class is used to modify tables. Execution of operations specified by this request is asynchronous. These are potentially long-running operations. TableResult is returned from TableRequest operations and it encapsulates the state of the table. See Oracle NoSQL Java SDK API Reference for more details on the TableRequest class and its methods.

Download the full code AlterTable.java from the examples here.
/**
* Alter the table stream_acct and add a column
*/
private static void alterTab(NoSQLHandle handle) throws Exception {
   String alterTableDDL = "ALTER TABLE " + tableName +"(ADD acctname STRING)";
   TableRequest treq = new TableRequest().setStatement(alterTableDDL);
   System.out.println("Altering table " + tableName);
   TableResult tres = handle.tableRequest(treq);
   tres.waitForCompletion(handle, 60000, /* wait 60 sec */
   1000); /* delay ms for poll */
   System.out.println("Table " + tableName + " is altered");
}

The borneo.TableRequest class is used to modify tables. All calls to borneo.NoSQLHandle.table_request() are asynchronous so it is necessary to check the result and call borneo.TableResult.wait_for_completion() to wait for the operation to complete. See Oracle NoSQL Python SDK API Reference for more details on table_request and its methods.

Download the full code AlterTable.py from the examples here.
def alter_table(handle):
   statement = '''ALTER TABLE  stream_acct(ADD acctname STRING)'''
   request = TableRequest().set_statement(statement)
   table_result = handle.do_table_request(request, 40000, 3000)
   table_result.wait_for_completion(handle, 40000, 3000)
   print('Table stream_acct is altered')

The TableRequest class is used to modify tables. Execution of operations specified by TableRequest is asynchronous. These are potentially long-running operations. This request is used as the input of a Client.DoTableRequest() operation, which returns a TableResult that can be used to poll until the table reaches the desired state. See Oracle NoSQL Go SDK API Reference for more details on the various methods of the TableRequest class.

Download the full code AlterTable.go from the examples here.
//alter an existing table and add a column
func alterTable(client *nosqldb.Client, err error, tableName string)(){
   stmt := fmt.Sprintf("ALTER TABLE %s (ADD acctName STRING)",tableName)
   tableReq := &nosqldb.TableRequest{
		Statement: stmt,
   }
   tableRes, err := client.DoTableRequest(tableReq)
   if err != nil {
      fmt.Printf("cannot initiate ALTER TABLE request: %v\n", err)
      return
   }
   // The alter table request is asynchronous, wait for table alteration to complete.
   _, err = tableRes.WaitForCompletion(client, 60*time.Second, time.Second)
   if err != nil {
      fmt.Printf("Error finishing ALTER TABLE request: %v\n", err)
      return
   }
   fmt.Println("Altered table ", tableName)
   return
}

You can use execute the tableDDL method to modify a table. This method is asynchronous and it returns a Promise of TableResult. The TableResult is a plain JavaScript object that encapsulates the state of the table after the DDL operation. For method details, see NoSQLClient class.

Download the full JavaScript code AlterTable.js from the examples here and the full TypeScript code AlterTable.ts from the examples here. 
//alter a table and add a column
async function alterTable(handle) {
   const alterDDL = `ALTER TABLE ${TABLE_NAME} (ADD acctname STRING)`;
   let res =  await handle.tableDDL(alterDDL);
   console.log('Table altered: ' + TABLE_NAME);
}

You can use either of the two methods ExecuteTableDDLAsync and ExecuteTableDDLWithCompletionAsync to modify a table. Both the methods return Task<TableResult>. TableResult instance encapsulates the state of the table after the DDL operation. See Oracle NoSQL Dotnet SDK API Reference for more details on these methods.

Download the full code AlterTable.cs from the examples here.
private static async Task alterTable(NoSQLClient client){
   var sql = $@"ALTER TABLE {TableName}(ADD acctname STRING)";
   var tableResult = await client.ExecuteTableDDLAsync(sql);
   // Wait for the operation completion
   await tableResult.WaitForCompletionAsync();
   Console.WriteLine("  Table {0} is altered", tableResult.TableName);
}