1. Administrator's Guide
  2. Tools and Utilities
  3. Using Plugins and Extensions for Development
  4. About Oracle NoSQL Database Visual Studio Code Extension
  5. Managing Tables Using Visual Studio Code Extension
  6. Perform DDL operations using Visual Studio Code

Perform DDL operations using Visual Studio Code

You can use Visual Studio Code to perform DDL operations.

Some of the DDL operations that can be performed from inside the Visual Studio Code plugin are:

CREATE TABLE

You can create the Oracle NoSQL Database table in two modes:
  • Simple DDL Input: You can use this mode to create the Oracle NoSQL Database table declaratively, that is, without writing a DDL statement.
  • Advanced DDL Input: You can use this mode to create the Oracle NoSQL Database table using a DDL statement.
  1. Hover over the Oracle NoSQL Database connection to add the new table.
  2. Click the Plus icon that appears or right-click on the database connection name and click Create Table.
    table-explorer

  3. In the Create Table page, select Simple DDL Input.

    Table 5-9 Create an Oracle NoSQL Database Table

    Field Description
    Table Name: Specify a unique table name.
    Column Name Specify a column name for the primary key in your table.
    Column Type Select the data type for your primary key column.
    Set as Shard Key Select this option to set this primary key column as shard key. Shard key is to distribute data across the Oracle NoSQL Database cluster for increased efficiency, and to position records that share the shard key locally for easy reference and access. Records that share the shard key are stored in the same physical location and can be accessed atomically and efficiently.
    Remove Click this button to delete an existing column.
    + Add Primary Key Column Click this button to add more columns while creating a composite (multi-column) primary key.
    Column Name Specify the column name.
    Column Type Select the data type for your column.
    Default Value (optional) Specify a default value for the column.

    Note:

    Default values can not be specified for binary and JSON data type columns.
    Not Null Select this option to specify that a column must always have a value.
    Remove Click this button to delete an existing column.
    + Add Column Click this button to add more columns.
    Unit Select the unit (Days or Hours) to use for TTL value for the rows in the table.
    Value Specify expiration duration for the rows in the table. After the number of days or hours, the rows expire automatically, and are no longer available. The default value is zero, indicating no expiration time.

    Note:

    Updating Table Time to Live (TTL) does not change the TTL value of any existing data in the table. The new TTL value applies only to those rows that are added to the table after this value is modified and to the rows for which no overriding row-specific value has been supplied.
  4. 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.
  • To create a child table, right click on the desired table and choose Create Child Table. You can create a child table in two modes:
    • Simple DDL Input: You can use this mode to create a child table by simply entering a table name along with other required details.
    • Advanced DDL Input: You can use this mode to create a child table using a DDL statement.

    For more details on child tables, see Table Hierarchies in Developer's Guide.

  • Click Create to create a child table.
  • You have an option to the view the DDL statement after creating a table. Right click on the existing table. Choose View Table DDL. To copy the DDL statement, click Copy to Clipboard. Click OK to close the dialog box.

DROP TABLE

  1. Right-click the target table.
  2. Click Drop Table.
  3. Click Yes to drop the table.

CREATE INDEX

  • Locate the Table Explorer, and click the Refresh Schema icon to reload the schema.
  • Right click on the table where index need to be created. Choose Create Index.
  • In the Create Index panel, you have an option to create index in two modes:
    • Simple Input: Specify the name of the index and the columns to be part of the index. If the column type is JSON, you see an additional field called "JSON Path to Index Field". Enter the path to the location of the JSON field, and choose the data type for it.
    • Using Advance DDL: Enter a valid DDL statement to create an index on any column(s). It can also include complex data type i.e. array, map, and record.
  • Click Add Index.

DROP INDEX

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Click on the table where the index needs to be removed. The list of indexes are displayed below the column names.
  • Right click on the index to be dropped. Click Drop Index.
  • A confirmation window appears, click Ok to confirm the drop action.

ADD COLUMN

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Right click on the table where column needs to be added. Click Add columns.
  • In the Add Column(s) Panel, you have an option to add column in two modes:
    • Simple DDL Input: Specify the name of the column and define the column with its properties - datatype, default value and whether it is nullable. In case of binary or fixed binary select the data type as Binary. For fixed binary enter the size of the file in the Size field and keep the field null in case of binary data type.
    • Advanced DDL Input: You can use this mode to add new columns into the table by supplying a valid DDL statement, as well as, create columns with complex data type (e.g. array, map, or record and also in nested format).
  • Click Add New Columns.

DROP COLUMN

  • Locate the Table Explorer, and click the Refresh Schema to reload the schema.
  • Expand the table where column needs to be removed.
  • Right click the column to be removed and choose Drop Column.
  • A confirmation window appears, click Ok to confirm the drop action.