Using MultiRowOptions to Retrieve Nested Tables

When you are iterating over a table, or performing a multi-get operation, by default only rows are retrieved from the table on which you are operating. However, you can use MultiRowOptions to specify that parent and child tables are to be retrieved as well.

When you do this, parent tables are retrieved first, then the table you are operating on, then child tables. In other words, the tables' hierarchical order is observed.

The parent and child tables retrieved are identified by specifying a List of Table objects to the ancestors and children parameters on the class constructor. You can also specify these using the MultiRowOptions.setIncludedChildTables() or MultiRowOptions.setIncludedParentTables() methods.

When operating on rows retrieved from multiple tables, it is your responsibility to determine which table the row belongs to.

For example, suppose you create a table with a child and grandchild table like this:

table create -name prodTable
add-field -type STRING -name prodType
add-field -type STRING -name typeDescription
primary-key -field prodType
exit
plan add-table -name prodTable -wait

table create -name prodTable.prodCategory
add-field -type STRING -name categoryName
add-field -type STRING -name categoryDescription
primary-key -field categoryName
exit
plan add-table -name prodTable.prodCategory -wait

table create -name prodTable.prodCategory.item
add-field -type STRING -name itemSKU
add-field -type STRING -name itemDescription
add-field -type FLOAT -name itemPrice
add-field -type STRING -name vendorUID
add-field -type INTEGER -name inventoryCount
primary-key -field itemSKU
exit
plan add-table -name prodTable.prodCategory.item -wait

With tables containing data like this:

In this case, you can display all of the data contained in these tables in the following way.

Start by getting all our table handles:

package kvstore.tableExample;

import java.util.Arrays;

import oracle.kv.KVStore;
import oracle.kv.KVStoreConfig;
import oracle.kv.KVStoreFactory;

import oracle.kv.table.PrimaryKey;
import oracle.kv.table.Row;
import oracle.kv.table.Table;
import oracle.kv.table.TableAPI;

import oracle.kv.table.TableIterator;
import oracle.kv.table.MultiRowOptions;

...

private static Table prodTable;
private static Table categoryTable;
private static Table itemTable;

private static TableAPI tableH;

...

// KVStore handle creation is omitted for brevity

...

tableH = kvstore.getTableAPI();
prodTable = tableH.getTable("prodTable");
categoryTable = tableH.getTable("prodTable.prodCategory");
itemTable = tableH.getTable("prodTable.prodCategory.item"); 

Now we need the PrimaryKey and the MultiRowOptions that we will use to iterate over the top-level table. Because we want all the rows in the top-level table, we create an empty PrimaryKey.

The MultiRowOptions identifies the two child tables in the constructor's child parameter. This causes the iteration to return all the rows from the top-level table, as well as all the rows from the nested children tables.

// Construct a primary key
PrimaryKey key = prodTable.createPrimaryKey();

// Get a MultiRowOptions and tell it to look at both the child
// tables
MultiRowOptions mro = new MultiRowOptions(null, null,
        Arrays.asList(categoryTable, itemTable));

Now we perform the iteration:

// Get the table iterator
// Exception handling is omitted, but in production code
// ConsistencyException, RequestTimeException, and FaultException
// would have to be handled.
TableIterator<Row> iter = tableH.tableIterator(key, mro, null);
try {
    while (iter.hasNext()) {
        Row row = iter.next();
        displayRow(row);
    } 
} finally {
    if (iter != null) {
        iter.close(); 
    }
}        

Our displayRow() method is used to determine which table a row belongs to, and then display it in the appropriate way.

private static void displayRow(Row row) {
    // Display the row depending on which table it belongs to
    if (row.getTable().equals(prodTable)) {
        displayProdTableRow(row);
    } else if (row.getTable().equals(categoryTable)) {
        displayCategoryTableRow(row);
    } else {
        displayItemTableRow(row);
    }
} 

Finally, we just need the methods used to display each row. These are trivial, but in a more sophisticated application they could be used to do more complex things, such as construct HTML pages or write XSL-FO for the purposes of generating PDF copies of a report.

private static void displayProdTableRow(Row row) {
    System.out.println("\nType: " + 
        row.get("prodType").asString().get());
    System.out.println("Description: " + 
        row.get("typeDescription").asString().get());
}

private static void displayCategoryTableRow(Row row) {
    System.out.println("\tCategory: " + 
        row.get("categoryName").asString().get());
    System.out.println("\tDescription: " + 
        row.get("categoryDescription").asString().get());
}

private static void displayItemTableRow(Row row) {
    System.out.println("\t\tSKU: " + 
        row.get("itemSKU").asString().get());
    System.out.println("\t\tDescription: " + 
        row.get("itemDescription").asString().get());
    System.out.println("\t\tPrice: " + 
        row.get("itemPrice").asFloat().get());
    System.out.println("\t\tVendorUID: " + 
        row.get("vendorUID").asString().get());
    System.out.println("\t\tInventory count: " + 
        row.get("inventoryCount").asInteger().get());
    System.out.println("\n");
} 

Note that the retrieval order remains the top-most ancestor to the lowest child, even if you retrieve by lowest child. For example, you can retrieve all the Bolts, and all of their parent tables, like this:

// Get all the table handles
prodTable = tableH.getTable("prodTable");
categoryTable = tableH.getTable("prodTable.prodCategory");
itemTable = tableH.getTable("prodTable.prodCategory.item");

// Construct a primary key
PrimaryKey key = itemTable.createPrimaryKey();
key.put("prodType", "Hardware");
key.put("categoryName", "Bolts");

// Get a MultiRowOptions and tell it to look at both the ancestor
// tables
MultiRowOptions mro = new MultiRowOptions(null,
        Arrays.asList(prodTable, categoryTable), null);

// Get the table iterator
// Exception handling is omitted, but in production code
// ConsistencyException, RequestTimeException, and FaultException
// would have to be handled.
TableIterator<Row> iter = tableH.tableIterator(key, mro, null);
try {
    while (iter.hasNext()) {
        Row row = iter.next();
        displayRow(row);
    }
} finally {
    if (iter != null) {
        iter.close(); 
    }
}