Example of Table Creation during Schema Generation
The following example shows the base tables and virtual tables that the connector 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.
{
"Type": "Customer",
"Name": "John Doe",
"SavedAddresses": ["123 Main St.", "456 1st Ave"]
}
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.
{
"Type": "Customer",
"Name": "John Doe",
"SavedAddresses": ["123 Main St.", "456 1st Ave"]
{
"Type": "Order",
"CustomerID":"Customer_123221",
"CreditCard":
{
"Type":"Visa",
"CardNumber":"4111 1111 1111 1111",
"Expiry":"12/12",
"CVN":"123"
},
"Items":
[
{"ItemID":89123, "Quantity":1},
{"ItemID":92312, "Quantity":5}
]
}
When the connector connects to ExampleDatabase and generates the schema definition, the connector creates a collection for each document type. The connector exposes these collections as two base tables, which are shown below.
| PK | Name |
|---|---|
|
"Customer_123221" |
John Doe |
| PK | CustomerID | CreditCard_Type | CreditCard_Number | CreditCard_Expiry | CreditCard_CVN |
|---|---|---|---|---|---|
|
"Order_221354" |
"Customer_123221" |
"Visa" |
"4111 1111 1111 1111" |
"12/12" |
"123" |
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 connector generates a virtual table for each array. The following tables show the virtual tables that represent data from the SavedAddresses and Items arrays.
| PK | SavedAddresses_idx | SavedAddresses |
|---|---|---|
|
"Customer_123221" |
0 |
"123 Main St." |
|
"Customer_123221" |
1 |
"456 1st Ave" |
| PK | Items_idx | ItemID | Quantity |
|---|---|---|---|
|
"Order_221354" |
0 |
89123 |
1 |
|
"Order_221354" |
1 |
92312 |
5 |
You can select, insert, and update data in the base tables and virtual tables as if they were standard relational tables, and the connector 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:
INSERT INTO "Order_Items" ("PK", "ItemID", "Quantity") VALUES ('Order_221354', '78123', '6')
Some operations might be processed differently or not supported for certain types of data. For more information, see Write-back.
- Schema Definitions
- Base Tables
- Virtual Tables
- Data Types
- Features
- Configuring the Driver on page 1