Oracle NoSQL Database Connector

Oracle NoSQL Database Connector enhances your experience of building an application with -

• Oracle NoSQL Database Cloud Service
• Oracle NoSQL Database on-premises
• Oracle NoSQL Database Cloud Simulator

You can use Oracle NoSQL Database Visual Studio (VS) Code extension to:

• View the tables in a well-defined tree structure with Table Explorer.
• View information on columns, indexes, primary key(s), and shard key(s) for a table.
• View column data in a well-formatted JSON Structure.
• Create tables using form-based schema entry or supply DDL statements.
• Drop tables.
• Add new columns using form-based entry or supply DDL statements.
• Drop Columns.
• Create Indexes.
• Drop Indexes.
• Execute SELECT SQL queries on a table and view query results in tabular format.
• Execute DML statements to update, insert, and delete data from a table.
• Download the Query Result after running the SELECT query into a JSON file.
• Download each row of the result obtained after running the SELECT query into a JSON file.

See Extension Documentation for more details.

Getting Started

Connect to your database

1. Click the Oracle NoSQL DB view in the Activity Bar.
2. Click Add Connection to open the Oracle NoSQL DB Show Connection Settings page.
3. In the Connection Settings page, click Cloud, Onprem or CloudSim to connect to Oracle NoSQL Database Cloud Service, Oracle NoSQL Database (Onprem) or Oracle NoSQL Database Cloud Simulator.
4. Enter the connection information and connect.

Manage Tables

Once you connect to your deployment -

• Use the Table Explorer left navigation to explore your tables.

• Click the table name to view its columns, indexes, primary key(s), and shard key(s). The column name displays along with its data type.

You can refresh the schema or table at any time to re-query your deployment and populate Oracle NoSQL Database with the most up-to-date data.

• In the Table Explorer, locate the connection and click the Refresh icon to reload the schema. Alternatively, you can right-click the connection and select Refresh Schema.

• In the Table Explorer, locate the table name and click the Refresh icon to reload the table. Alternatively, you can right-click the table name and select Refresh Table.

Create New Tables

To create a new table:

1. Hover over the database where you want to add the new table.

2. Click the Plus icon that appears.

3. In the prompt, enter the details for your new table.

   You can create the Oracle NoSQL Database table in two modes:

   • Simple DDL Input : You can use this mode to create the table declaratively, that is, without writing a DDL statement.
   • Advanced DDL Input : You can use this mode to create the table using a DDL statement.

4. (Optional) Click Show DDL to view the DDL statement formed based on the values entered in the fields in the Simple DDL input mode. This DDL statement gets executed when you click Create.

5. Click Create.

Drop a Table

1. Right-click the target table and select Drop table.
2. A confirmation window appears, click "yes" to drop the table.

Add new Columns to the table

1. Right-click the target table and select Add Columns.

2. In the Add Columns panel, enter the details for adding new columns.

   You can Add new COLUMNs in two modes like CREATE TABLE:

   • Simple DDL Input : You can use this mode to add new columns declaratively, that is, without writing a DDL statement.
   • Advanced DDL Input : You can use this mode to add new columns into the table by supplying a valid DDL statement.

3. Click Add Column(s).

Drop a column

1. Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
2. Locate the target-column which has to be dropped and right-click on it.
3. Click on Drop Column.
4. A confirmation window appears, click "yes" to drop the column.

Create an Index

• Via Simple Input

1. Right-click the target table and select Create Index.
2. In the Create Index panel, be on "Simple Input" section and enter the details for creating an index declaratively, that is, without writing any DDL statement.
3. Click Create Index.

• Using DDL statement

1. Right-click the target table and select Create Index.
2. In the Create Index panel, navigate to the "Using Advanced DDL" section and enter the DDL statement.
3. Click Create Index.

• Creating index on JSON columns

1. Right-click the target table and select Create Index.
2. In the Create Index panel, be on "Simple Input" section and select the JSON columnName from the dropdown, enter the JSON path to the index field and select the type of the JSON index field or pass the DDL statement via "using Advanced DDL" section.
3. Click Create Index.

Drop an Index

1. Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.
2. Locate the target-index which has to be dropped and right-click on it.
3. Click on Drop Index.
4. A confirmation window appears, click "yes" to drop the index.

Insert a new Row into the table

1. Right-click the target table and select Insert Row.

2. In the Insert Row panel, enter the details for inserting a new row.

   You can INSERT a new ROW in two modes:

   • Simple Input : You can use this mode to insert the new row declaratively, that is, without writing a DML statement.
   • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a json Object containing the columnName and its corresoponding value as key-value pairs.

3. Click Insert Row.

Execute SQL Queries on a Table

• Right-click the target table and select Browse Table.

• In the SQL window, specify the SELECT query in the text box and click execute button. The query result is displayed in tabular format. You can adjust the number of rows per page.

• To view individual cell data separately, click the table cell.

• To perform DML operations like Update and Delete Row, right-click on the particular row. A context-menu will appear. If you click on:

  • Delete Row : A confirmation window appears, click "yes" to delete the row.
  • Update Row : A separate HTML panel opens below the listed rows, containing the columnNames and its corresponding value in a form-based entry and as a JSON key-pair object. You can choose either of the two methods and supply new values. NOTE: PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be update in any rows.
  • Download JSON: Row Object gets downloaded into a JSON file.

Show Query Plan

• Right-click the target table and select Browse Table.
• In the SQL window, specify any query in the text box and click "Show Query Plan" button. The query execution plan will be displayed in a window.

View Table DDL

• Right-click the target table and select View Table DDL.
• A window having the DDL statement of the present status of the table will be displayed.

SDK

This extension uses Oracle NoSQL Node.js SDK which is included in VSIX package under '/oracle-nosqldb' directory. This directory includes a separate README and CHANGELOG file specific to the SDK.