Managing Tables Using the IntelliJ Plugin

Learn how to view table data in your data store from IntelliJ Schema Explorer.

Perform the following steps:

1. Locate the Schema Explorer, and click the Refresh icon to reload the schema.

2. Locate your table under your tenant identifier, and expand it to view its columns, primary key, and shard key details. If you created a sample application as described in the Creating applications using Oracle NoSQL Java SDK from IntelliJ topic, you will be able to see the audienceData table.

3. Double-click the table name to view its data. Alternatively, you can right-click the table and select Browse Table.

4. A record viewer window appears in the main editor. Click Execute to run the query and display table data.

5. To view individual cell data separately, double-click the cell.

Perform DDL operations using IntelliJ

You can use IntelliJ to perform DDL operations.

Some of the DDL operations that can be performed from inside the IntelliJ plugin are

• CREATE TABLE

• DROP TABLE

• CREATE INDEX

• DROP INDEX

• ADD COLUMN

• DROP COLUMN

• EXECUTE DDL

CREATE TABLE

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click the connection name and choose Create Table.

• 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.

• You have the option to view the DDL statement before creating. 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.

• Click Create to create the table.

• 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

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click on the table that you want to drop. Choose Drop Table.

• A confirmation window appears, click Ok to confirm the drop action.

CREATE INDEX

• Locate the Schema Explorer, and click the Refresh 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:

  • Form Based Index Creation (Simple DDL Input) : Enter the details for creating an index without writing any DDL statement. Specify the name of the index and the columns to be part of the index. If the column is of JSON data type, you see an additional field called “JSON Path to Index Field” appear. Enter the path to the location of the JSON field, and choose the data type for it.

  • Create Index as DDL Statement (For Advanced DDL input) : Enter a valid DDL statement to create an index. It can also include complex data type i.e. array, map, and record.

• Click Add Index.

DROP INDEX

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.

• Locate the target-index which has to be dropped and right-click on it. Click Drop Index.

• A confirmation window appears, click Ok to confirm the drop action.

ADD COLUMN

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click on the table where column needs to be added. Choose Add Column.

• You can add new COLUMNs in two modes:

  • Simple DDL Input : You can use this mode to add new columns without writing a DDL statement. 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. This mode can also create columns of complex data type. For example, array, map, or record and also in nested format.

• In both the modes, specify the name of the column and define the column with its properties - datatype, default value and whether it is nullable.

• Click Add Column.

DROP COLUMN

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Click on the target table to see the listed columns, Primary Keys, Indexes and Shard Keys.

• Locate the target-column which has to be dropped and right-click on it. Click Drop Column.

• A confirmation window appears, click Ok to confirm the drop action.

EXECUTE DDL

You can execute any table-specific DDL statements and table-independent operations like managing namespaces, regions, and roles using the Execute DDL option.

1. Click on the Execute icon (a triangle) in the Schema Explorer to open the Execute DDL window. Alternatively, you can also right-click the connection name and choose Execute DDL.

2. In the new window, enter the SQL statement which is a System Request (like creating or dropping region/role/namespace, etc). Click Execute. The result of the DDL is displayed in the lower portion of the window.

   Note: You can also perform any other DDL operation like CREATE TABLE from this window.

Operations supported using Execute DDL:

• Create a Namespace: You can add one or more namespaces to your store, create tables within them, and grant permission for users to access namespaces and tables. Example:

  CREATE NAMESPACE ns1

• Create a remote region: You can create remote regions from each local region in a Multi-Region NoSQL Database. Example:

  CREATE REGION fra

• Add a local region:You can set a name to the local region in a Multi-Region NoSQL Database. Example:

  SET LOCAL REGION lnd

• Drop a region: You can remove a region. Example:

  DROP REGION fra

• Create an on-premises Multi-Region table: You can create an on-premises Multi-Region table on a data store and specify the list of regions that the table should span. You can also expand a Multi-Region table by adding more regions or contract a Multi-Region table by removing the table from any existing region.

  Example: Create a Multi-Region Table

  CREATE TABLE users(id INTEGER, name STRING, team STRING,
  PRIMARY KEY(id))IN REGIONS fra,lnd

  Example: Expand a Multi-Region Table

  CREATE REGION dub;
  ALTER TABLE users ADD REGIONS dub;

  Example: Contract a Multi-Region Table

  ALTER TABLE users DROP REGIONS dub;

Note: Before using the IntelliJ plugin to create/expand or contract a Multi-Region table, you should complete all the tasks for configuring a Multi-Region data store. This includes deploying the data store, configuring the XRegion service, starting the XRegion agents, and running the proxy. See Configuring Multi-Region Data Stores for more details.

Perform DML operations using IntelliJ

You can add data, modify existing data and query data from tables using IntelliJ plugin.

Insert data

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click on the table where a row needs to be inserted. Choose Insert Row.

• 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 without writing a DML statement. Here a form based row fields entry is loaded, where you can enter the value of every field in the row.

    • For binary data type, the string typed in should be a valid Base64 encoding of a binary value or select the file to upload in the desired column.

    • For fixed binary data type, the string typed in should be a valid Base64 encoding of a binary value or upload the file of size defined during the creation of the particular column.

    Note: The file format you upload for binary data type should only have .bin extension.

  • Advanced JSON Input : You can use this mode to insert a new row into the table by supplying a JSON Object containing the column name and its corresponding value as key-value pairs. The input can also be of complex data type i.e. array, map, record.

• Click Insert Row.

Modify Data - UPDATE ROW/DELETE ROW

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click on the table where a row needs to be inserted. Choose Browse Table.

• In the textbox on the left, enter the SQL statement to fetch data from your table. Click Execute to run the statement.

• 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. Pick your option from the context-menu that appears.

  • Delete Row : A confirmation window appears, click Ok to delete the row.

  • Update Row : A separate HTML window opens displaying the column names and its corresponding values. You can enter new values for row data in two modes: form-based row fields entry (Simple DDL input) and Supply row contents as a JSON object (Advanced DDL input). In the advanced DDL input mode, JSON data is presented as a tree structure to simplify viewing and updating.

    Select Execute to refresh and view the updated data.

    Note: In any row, PRIMARY KEY and GENERATED ALWAYS AS IDENTITY columns cannot be updated.

Query tables

• Locate the Schema Explorer, and click the Refresh icon to reload the schema.

• Right click on the table and choose Browse Table.

• In the textbox on the left, enter the SELECT statement to fetch data from your table. When you start typing the query, you are prompted with the list of possible words to auto-complete the SQL statement. All SQL keywords and column names for the given table are provided in the prompt to auto complete the SQL statement.

• The SQL syntax is highlighted in every query which provides a better SQL writing experience.

• You can format the query to improve its readability. Select your query, right click and select Prettify. The query is formatted and lines are wrapped to enhance readability.

• Click Execute to run the query. The corresponding data is retrieved from the table. When you double click the data retrieved, the column data is opened in a new window. Any JSON data is displayed in a tree structure in the new window. Click ‘+’ to expand or ‘-’ to collapse the tree structure. Click Ctrl ^F in the new window to enable the search option, which allows you to search for any value in the JSON tree. Click the up or down arrow to move to the previous or next search occurrence, respectively.

• Right click on any row and click Download JSON. In the dialog box, navigate to the location where you want to save the file and click Save. Once the file is downloaded, a notification appears at the bottom-right of the screen. Click the link to open the downloaded file. The file opens in the browser.

  In case of Binary data type, simply click Download Binary Object in the output.

• Click Download Query Result, to download all the data in the query result. In the dialog box, navigate to the location where you want to save the file and click Save. In case of multiple rows, a progress bar appears on the bottom-right of the screen to showing the number of rows downloaded in real time. Once the file is downloaded, a notification appears at the bottom-right of the screen. Click the link to open the downloaded file. The file opens in the browser.

• Click Show Query Plan to view the execution plan of the query.

• Click the Previous Commands drop-down, to view the top 20 recently executed SQL statements that had provided an output.

Note: The drop-down will only show SQL statements related to the table you are working on.

Schema Explorer

• In the Schema Explorer window, you can verify the full data type of a particular column. Locate the particular column and the data type is followed by the column name.