Table of Contents
The Oracle NoSQL Database tables API is the recommended method of coding an Oracle NoSQL Database client application. It allows you to manipulate data using a tables metaphor, in which data is organized in multiple columns of data. An unlimited number of subtables are supported by this API. You can also create indexes to improve query speeds against your tables.
You should avoid any possibility of colliding keys if your store is accessed by a mix of clients that use both the tables and the key/value APIs.
Before an Oracle NoSQL Database client can read or write to a table in the store, the table must be created. There are several ways to do this, but this manual focuses on using Table DDL Statements. These statements can be submitted to the store using the command line interface (CLI), but the recommened approach is to submit them to the store programmatically. Both methods are described in this section.
The DDL language that you use to define tables is described in Table Data Definition Language Overview This section provides a brief overview of how to use that language.
As an introductory example, suppose
you wanted to use a table named
myTable with four columns per row:
item, count1,
count2, and percentage.
To create this table, you would use the following statement:
CREATE TABLE myTable ( item STRING, description STRING, count INTEGER, percentage DOUBLE, PRIMARY KEY (item) // Every table must have a primary key )
Primary keys are a concept that have not yet been introduced in this manual. See Primary and Shard Key Design for a complete explanation on what they are and how you should use them.
To add the table definition to the store, you can add it
programmatically using the KVStore.execute()
or KVStore.executeSync()
methods. (The latter method executes the statement synchronously.)
In order to use these methods, you must establish a connection to
the store. This is described in
The KVStore Handle.
For example:
package kvstore.basicExample;
import oracle.kv.FaultException;
import oracle.kv.StatementResult;
import oracle.kv.KVStore;
import oracle.kv.table.TableAPI;
...
// kvstore open omitted
TableAPI tableAPI = kvstore.getTableAPI();
ExecutionFuture future = null;
StatementResult result = null;
String statement = null;
public void createTable() {
TableAPI tableAPI = store.getTableAPI();
StatementResult result = null;
String statement = null;
try {
/*
* Add a table to the database.
* Execute this statement asynchronously.
*/
statement =
"CREATE TABLE myTable (" +
"item STRING," +
"description STRING," +
"count INTEGER," +
"percentage DOUBLE," +
"PRIMARY KEY (item))"; // Required"
result = store.executeSync(statement);
displayResult(result, statement);
} catch (IllegalArgumentException e) {
System.out.println("Invalid statement:\n" + e.getMessage());
} catch (FaultException e) {
System.out.println
("Statement couldn't be executed, please retry: " + e);
}
}
private void displayResult(StatementResult result, String statement) {
System.out.println("===========================");
if (result.isSuccessful()) {
System.out.println("Statement was successful:\n\t" +
statement);
System.out.println("Results:\n\t" + result.getInfo());
} else if (result.isCancelled()) {
System.out.println("Statement was cancelled:\n\t" +
statement);
} else {
/*
* statement was not successful: may be in error, or may still
* be in progress.
*/
if (result.isDone()) {
System.out.println("Statement failed:\n\t" + statement);
System.out.println("Problem:\n\t" +
result.getErrorMessage());
} else {
System.out.println("Statement in progress:\n\t" +
statement);
System.out.println("Status:\n\t" + result.getInfo());
}
}
}
You can execute DDL statements using the the CLI's
execute command. This executes DDL statements
synchronously. For example:
kv-> execute "CREATE TABLE myTable ( > item STRING, > description STRING, > count INTEGER, > percentage DOUBLE, > PRIMARY KEY (item))" Statement completed successfully kv->
You specify schema for each column in an Oracle NoSQL Database table. This schema can be a primitive data type, or complex data types that are handled as objects.
Supported data types are:
Array
An array of values, all of the same type.
Binary
Implemented as a Java byte array with no predetermined fixed size.
Boolean
Double
Enum
An enumeration, represented as an array of strings.
Fixed Binary
A fixed-sized binary type (Java byte array) used to handle binary data where each record is the same size. It uses less storage than an unrestricted binary field, which requires the length to be stored with the data.
Float
Integer
Long
Map
An unordered map type where all entries are constrained by a single type.
Records
See the following section.
Java String
As described in Defining Child Tables, you can create child tables to hold subordinate information, such as addresses in a contacts database, or vendor contact information for an inventory system. When you do this, you can create an unlimited number of rows in the child table, and you can index the fields in the child table's rows.
However, child tables are not required in order to organize subordinate data. If you have very simple requirements for subordinate data, and you do not want to index the fields in the subordinate data, you can use record fields instead of a child tables. In general, you can use record fields instead of child tables if you only want a fixed, small number of instances of the record for each parent table row. For anything beyond trivial cases, you should use child tables. (Note that there is no downside to using child tables even for trivial cases.)
The assumption when using record fields is that you have a fixed known number of records that you will want to manage (unless you organize them as arrays). For example, for a contacts database, child tables allows you to have an unlimited number of addresses associated for each user. But by using records, you can associate a fixed number of addresses by creating a record field for each supported address (home and work, for example).
For example:
CREATE TABLE myContactsTable (
uid STRING,
surname STRING,
familiarName STRING,
homePhone STRING,
workPhone STRING,
homeAddress RECORD (street STRING, city STRING, state STRING,
zip INTEGER CHECK(zip >= 00000 and zip <= 99999)),
workAddress RECORD (street STRING, city STRING, state STRING,
zip INTEGER CHECK(zip >= 00000 and zip <= 99999)),
PRIMARY KEY(uid))
Alternatively, you can create an array of record fields. This allows you to create an unlimited number of address records per field. Note, however, that in general you should use child tables in this case.
CREATE TABLE myContactsTable (
uid STRING,
surname STRING,
familiarName STRING,
homePhone STRING,
workPhone STRING,
addresses ARRAY(RECORD (street STRING, city STRING, state STRING,
zip INTEGER CHECK(zip >= 00000 and zip <= 99999))),
PRIMARY KEY(uid))
Oracle NoSQL Database tables can be organized in a parent/child hierarchy. There is no limit to how many child tables you can create, nor is there a limit to how deep the child table nesting can go.
Child tables are not retrieved when you retrieve a parent table, nor is the parent retrieved when you retrieve a child table.
To create a child table, you name the table using the format:
<parentTableName>.<childTableName>.
For example, suppose you had the trivial table called
myInventory:
CREATE TABLE myInventory ( itemCategory STRING, description STRING, PRIMARY KEY (itemCategory) )
We can create a child table called
itemDetails in the following way:
CREATE TABLE myInventory.itemDetails (
itemSKU STRING,
itemDescription STRING,
price FLOAT,
inventoryCount INTEGER,
PRIMARY KEY (itemSKU)
)
Note that when you do this, the child table inherits the
parent table's primary key. In this trivial case, the child
table's primary key is actually the two fields:
itemCategory and itemSKU.
This has several ramifications, one of which is that the
parent's primary key fields are retrieved when you retrieve
the child table. See
Retrieve a Child Table
for more information.
In the event that you must update your application at some
point after it goes into production, there is a good chance
that your tables will also have to be updated to either use
new fields or remove existing fields that are no longer in
use. You do this through the use of the ALTER TABLE
statement. See Modify Table Definitions
for details on this statement.
Note that you cannot remove a field if it is a primary key field. You also cannot add primary key field during table evolution.
Tables can only be evolved if they have already been added to the store.
For example, the following statements evolve the table that was created in the previous section. Note that these would be submitted to the store, one after another, using either the API or the CLI.
ALTER TABLE myInventory.itemDetails (ADD salePrice FLOAT)
ALTER TABLE myInventory.itemDetails (DROP inventoryCount)