Menu
Simba Technologies
Simba Technologies

Simba Couchbase ODBC Driver with SQL Connector 1.1.0
Installation and Configuration Guide

Schema Definition

Couchbase is able to store data that follows different rules of data typing and structure compared to traditional relational tables and columns. Couchbase data is organized into buckets and documents, which can contain nested arrays or arrays of differently-typed elements. Because traditional ODBC toolsets might not support these data structures, the data needs to be mapped to a relational form. To achieve this, the Simba Couchbase ODBC Driver generates a schema definition that maps the Couchbase data to an ODBC-compatible format.

Note:

The schema definition that the driver generates is distinct from the metadata schema that exists as part of the database architecture.

When the driver connects to a database that does not already have the necessary schema definition, it automatically generates one by doing the following:

  1. For each document type identified in the database, the driver samples data from multiple documents to detect the structure of the data.
  2. The driver organizes all the documents into collections based on their type, and saves these collections as part of the schema definition. Using the schema definition, the driver exposes collections as tables in ODBC applications.
  3. For each array detected in the database, the driver generates a virtual table to expand the data, and saves these virtual tables as part of the schema. Using the schema, the driver exposes virtual tables as normal tables in ODBC applications.
  4. The driver defines a Couchbase data type for each column and maps each Couchbase data type to the SQL data type that is best able to represent the greatest number of values.

In addition to the automatic schema generation done by the driver, you can also manually edit the schema by using the Schema Editor. The Schema Editor is a Java application that is installed as part of the Simba Couchbase ODBC Driver. For more information about using the Schema Editor, see the Schema Editor User Guide, which is located in the \Tools\Schema Editor\Docs subfolder in the Simba Couchbase ODBC Driver installation folder. For information about how to define a schema, see Defining a Schema.

The tables defined in the schema definition refer to the actual data in the Couchbase database, allowing you to interact with the data through the ODBC layer while leaving the storage of the data in its denormalized form in the database. By querying the tables, you can access the contents of the database. When you write or modify data in a table, the corresponding data in the Couchbase database is updated.

Base Tables

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 ODBC 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

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 that identifies the document that the array comes from and references the 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 ODBC 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.

Example of Table Creation during Schema Generation

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.

{

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

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 driver 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 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:

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.