Chapter 3. Introducing Oracle NoSQL Database Tables and Indexes

Chapter 3. Introducing Oracle NoSQL Database Tables and Indexes
Prev		Next

Table of Contents

Executing DDL Statements using the CLI
Supported Table Data Types
Record Fields
Defining Child Tables
Table Evolution

Indexable Field Types

Using the Table API (in one of the supported languages) is the recommended method of coding an Oracle NoSQL Database client application. They allow 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.

Note

You should avoid any possibility of colliding keys if your store is accessed by a mix of clients that use both the Table and the Key/Value APIs.

Defining Tables

Executing DDL Statements using the CLI
Supported Table Data Types
Record Fields
Defining Child Tables
Table Evolution

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 recommended 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, description, count, 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
)

Note

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 Store.executeFuture() or Store.execute() 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 Connecting to the Store.

For example:

...
    // Store handle configuration and open skipped for brevity
...

store.on('open', function () {

    console.log('Store opened');

    store.execute('CREATE TABLE IF NOT EXISTS myTable (' +
                        'id STRING, ' +
                        'description STRING, ' +
                        'count INTEGER, ' +
                        'percentage DOUBLE, ' +
                        'PRIMARY KEY (item)) ', 
      function(err){
        if (err)
          throw err;
        else {
           console.log('Table creation succeeded.');
           store.close();
        }
      });
}).on('close', function() {
    console.log('Store closed.');
}).on('error', function(error) {
    console.log(error);
});
store.open();

Executing DDL Statements using the CLI

You can execute DDL statements using 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->

Supported Table Data Types

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 for Oracle NoSQL Database are:

Array

An array of values, all of the same type.
Binary

Implemented as a 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 (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.
String

For the node.js driver, these datatypes are handled in the following way:

Oracle NoSQL Database Datatype	node.js Datatype
Array	Javascript array. See Using Arrays.
Binary	Base64 encoded buffer. See Using Binary.
Boolean	Javascript boolean
Double	Javascript number
Enum	Javascript string. See Using Enums.
Fixed Binary	Base64 encoded buffer. See Using Fixed Binary.
Float	Javascript number
Integer	Javascript number
Long	Javascript number
Map	Javascript object. See Using Maps.
Records	Javascript object. See Using Embedded Records.
String	Javascript string

Record Fields

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, 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 allow 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))

Defining Child Tables

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.

By default, 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 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.

Table Evolution

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)

Prev		Next
kvlite Utility Command Line Parameter Options	Home	Creating Indexes