Example for base table and virtual table

Base tables represent data from collections of Couchbase documents. Documents appear as rows, and all attributes that are not arrays appear as columns. In each base table, the driver creates a primary key column named PK that identifies which Couchbase document each row comes from.

In the JDBC layer, the name of the base table is the document type that it represents. In the Couchbase layer, the name of the base table is the bucket that the data comes from.

Virtual tables provide support for arrays. Each virtual table contains the data from one array, and each row in the table represents an element from the array. If an element contains an array, then the driver creates additional virtual tables as needed to expand the nested data.

In each virtual table, the driver creates a primary key column named PK that identifies the Couchbase document the array comes from and references the PK column from the related base table. The driver also creates an index column (with the suffix _IDX in its name) to indicate the position of the element within the array.

In the JDBC layer, the name of the virtual table is formed using the document type that contains the array, an underscore character (_), and the name of the array. In the Couchbase layer, the name of the virtual table is formed using the name of the bucket that the data comes from, a period (.), and then the name of the array followed a set of closed square brackets ([]) for each hierarchy level in which the array is nested.

The following example shows the base tables and virtual tables that the driver would generate if it connected to a Couchbase database named ExampleDatabase, which contains two documents named Customer_123221 and Order_221354.

The Customer_123221 document is of type Customer and contains the following attributes. The SavedAddresses attribute is an array.

The Order_221354 document is of type Order and contains the following attributes. The CreditCard attribute is an object, and the Items attribute is an array of objects.

The following table shows that when the driver connects to ExampleDatabase and generates the schema, the driver creates a collection for each document type. The driver exposes these collections as two base tables, which are shown below.

The SavedAddresses array from the Customer_123221 document and the Items array from the Order_221354 document do not appear in these base tables. Instead, the driver generates a virtual table for each array. The following tables show the virtual tables that represent data from the SavedAddresses and Items arrays.

You can select, insert, and update data in the base tables and virtual tables as if they were standard relational tables, and the driver will handle the storage details within Couchbase.

For example, to append an item to the Items array, where the ItemID is 78123 and the Quantity is 6, execute the following statement: