X

The Oracle NoSQL Database Blog covers all things Oracle NoSQL Database. On-Prem, Cloud and more.

Transcend the Basic Table and Indexes Naming

Vishal Settipalli
Principal Product Manager

Introduction

As an application developer, one of the challenges you might face is coming up with cryptic database table names in order to manage and organize large number of tables that your application require. More tables require more unique and obscure names which will be hard to manage and confusing.

Oracle NoSQL Database introduces a useful feature called Namespaces.  Namespaces allows one to group application tables together, improves schema management and increases security.

Namespaces

A typical RDBMS like Oracle supports associating a schema with a user. This support allows users to provide specific application roles to tables in their schema. Similarly, NoSQL database Cassandra has the concept of keyspaces that are used for granting or revoking namespaces to specific users.

Oracle NoSQL Database release 18.3 has introduced Namespaces. Broadly based on the Oracle RDBMS schemas, Oracle NoSQL database namespaces allow multiple tables and indexes with the same name to exist in the same Oracle NoSQL store but under different namespaces. This means you can now create different individual silo of tables and group them for ease of management. The chart below shows two namespaces. Within each, there are multiple tables. And, the nice thing is each table within the namespace needs to be unique within the namespace but not across namespaces.

Multiple namespaces are allowed in a KV Store. You do not have to create namespaces when you install your NoSQL Database for the first time. A default namespace “sysdefault” exists. If you create tables before you create any namespace, or, if you do not fully qualify a table name with a namespace, tables are created in the default namespace (sysdefault) automatically.

Use Case – Customer Profile

Let’s take a typical bank which has a growing customer base and multiple departments – Loans, Retail Banking etc. The bank is interested in storing customer profile independently for each department. With Oracle NoSQL Database 18.3, the bank creates separate namespaces for each of the departments in the Oracle NoSQL Store and can use the same table names.

This way, the bank is able to segregate all the operations on tables under a namespace to a specific department. This separation improves both security and manageability.

Examples:

Below are examples of how to work with namespaces.

  • Create a Namespace

Create NAMESPACE [IF NOT EXISTS] master;

Create NAMESPACE [IF NOT EXISTS] transactions;

  • Drop a Namespace

Drop NAMESPACE [IF EXISTS] master [CASCADE];

If you include the CASCADE option all tables and indexes related to the namespace are removed.

  • Query tables

Select * from master:customerProfile; (Queries the “profile” table in the namespace “master”)

Select * from profile; (Queries the “profile” table in the default namespace “sysdefault”)

Select * from transaction:accountTransactions; (Queries the “sales” table in the namespace “transaction”)

Select * from accountTransactions; (Queries the “sales” table in the default namespace “sysdefault”)

  • API

The API has been modified to include a new configuration Executeoptions.setNamespace method. This method takes a String (namespace name) as a parameter.

Store.execute(“Select * from customerProfile”, new ExecuteOptions.setNamespace(“master”);

To work with the “put” and “get” API’s the table object is required and to get the table object. To get the table object and if you have associated your tables to namespaces, you must see the new 2-arg call

TableAPI.getTable (String namespace, String tablename);

  • Roles associated with Namespaces.

New roles associate with Namespaces are:

CREATE_ANY_NAMESPACE – can create Namespaces

INSERT_IN_NAMESPACE – can insert/update data in Namespace

DELETE_IN_NAMESPACE – can delete data in Namespace

DROP_ANY_NAMESPACE - can drop namespaces

CREATE/DROP/EVOLVE_TABLE_IN_NAMESPACE – can perform alter table operations in Namespace

CREATE/DROP_INDEX_IN_NAMESPACE – Can perform index related operations in Namespace

READ_IN_NAMESPACE – can read data in Namespace

CREATE_TABLE_IN_NAMESPACE – can create a table in a Namespace

DROP_TABLE_IN_NAMESPACE - can drop a table in a Namespace

EVOLVE_TABLE_IN_NAMESPACE – can alter a table in a Namespace

 

Summary

  • There can be multiple Namespaces in an Oracle NoSQL Database kvstore.
  • Tables with same names can be created across different namespaces.
  • Specific Authorization roles can be provided to each namespace.
  • More details can be found in the official documentation – Namespaces.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services