Adding a Connection to Apache CouchDB

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Apache CouchDB icon is not available, click the Add More icon to download and install the Apache CouchDB connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

Connecting to Apache CouchDB

Basic Authentication

Set the following to connect to data:

• AuthScheme: Basic.
• Url: The Url of your Apache CouchDB instance. For example: http://localhost:5984
• User The Apache CouchDB user account used to authenticate.
• Password The Apache CouchDB password associated with the authenticating user.

Anonymous

Set the following to connect to data:

• AuthScheme: None.
• Url: The Url of your Apache CouchDB instance. For example: http://localhost:5984
• PublicDatabases: A comma-separated list of public databases to list as tables.

Inferring the Data Type

You can use the following properties to configure automatic data type detection, which is enabled by default.

• TypeDetectionScheme: You can use this property to enable or disable automatic type detection based on the value specified in RowScanDepth.
• RowScanDepth: This property determines the number of rows that will be scanned to determine column data types.

Apache CouchDB is a schema-less document database that provides high performance, availability, and scalability. These features are not necessarily incompatible with a standards-compliant query language like SQL-92. The following sections show various schemes that the Sync App offers to bridge the gap with relational SQL and a document database.

Automatic Schema Discovery

Parsing Nested Data

The Sync App models the schemaless Apache CouchDB objects into relational tables and translates SQL queries into Apache CouchDB queries to get the requested data.

Free-Form Queries

The schema of the table is necessary to report metadata, but you can write Free-Form Queries not tied to the schema to select, insert, update, or delete data from columns that do not exist in the schema (without flattening).

Vertical Flattening

See Vertical Flattening to process JSON arrays as separate tables.

JSON Functions

Query Mapping

See Query Mapping for more details on how various Apache CouchDB operations are represented as SQL.

Dynamic Views

The Sync App automatically infers a relational schema by inspecting a series of Apache CouchDB documents in a collection. You can use the RowScanDepth property to define the number of documents the Sync App will scan to do so. The columns identified during the discovery process depend on the FlattenArrays and FlattenObjects properties.

Flattening Objects

If FlattenObjects is set, all nested objects will be flattened into a series of columns. For example, consider the following document:

{
  id: 12,
  name: "Lohia Manufacturers Inc.",
  address: {street: "Main Street", city: "Chapel Hill", state: "NC"},
  offices: ["Chapel Hill", "London", "New York"],
  annual_revenue: 35,600,000
}

If FlattenObjects is not set, then the address.street, address.city, and address.state columns will not be broken apart. The address column of type string will instead represent the entire object. Its value would be {street: "Main Street", city: "Chapel Hill", state: "NC"}. See JSON Functions for more details on working with JSON aggregates.

You can change the separator character in the column name from a dot by setting SeparatorCharacter.

Flattening Arrays

The FlattenArrays property can be used to flatten array values into columns of their own. This is only recommended for arrays that are expected to be short, for example the coordinates below:

"coord": [ -73.856077, 40.848447 ]

It is best to leave other unbounded arrays as they are and piece out the data for them as needed using JSON Functions.

As discussed in Automatic Schema Discovery, intuited table schemas enable SQL access to unstructured Apache CouchDB data. JSON Functions enable you to use standard JSON functions to summarize Apache CouchDB data and extract values from any nested structures. However, you are not limited to these schemes.

After connecting you can query any nested structure without flattening the data. Any relations that you can access with FlattenArrays and FlattenObjects can also be accessed with an ad hoc SQL query.

Let's consider an example document from the following Restaurant data set:

 
{
  "address": {
    "building": "1007",
    "coord": [
      -73.856077,
      40.848447
    ],
    "street": "Morris Park Ave",
    "zipcode": "10462"
  },
  "borough": "Bronx",
  "cuisine": "Bakery",
  "grades": [
    {
      "grade": "A",
      "score": 2,
      "date": {
        "$date": "1393804800000"
      }
    },
    {
      "date": {
        "$date": "1378857600000"
      },
      "grade": "B",
      "score": 6
    },
    {
      "score": 10,
      "date": {
        "$date": "1358985600000"
      },
      "grade": "C"
    }
  ],
  "name": "Morris Park Bake Shop",
  "restaurant_id": "30075445"
} 

SELECT [address.building], [grades.1.grade] FROM restaurants WHERE restaurant_id = '30075445'

It is possible to retrieve an array of documents as if it were a separate table. Take the following JSON structure from the restaurants collection for example:

{
  "_id" : ObjectId("568c37b748ddf53c5ed98932"),
  "address" : {
    "building" : "1007",
    "coord" : [-73.856077, 40.848447],
    "street" : "Morris Park Ave",
    "zipcode" : "10462"
  },
  "borough" : "Bronx",
  "cuisine" : "Bakery",
  "grades" : [{
      "date" : ISODate("2014-03-03T00:00:00Z"),
      "grade" : "A",
      "score" : 2
    }, {
      "date" : ISODate("2013-09-11T00:00:00Z"),
      "grade" : "A",
      "score" : 6
    }, {
      "date" : ISODate("2013-01-24T00:00:00Z"),
      "grade" : "A",
      "score" : 10
    }, {
      "date" : ISODate("2011-11-23T00:00:00Z"),
      "grade" : "A",
      "score" : 9
    }, {
      "date" : ISODate("2011-03-10T00:00:00Z"),
      "grade" : "B",
      "score" : 14
    }],
  "name" : "Morris Park Bake Shop",
  "restaurant_id" : "30075445"
}

SELECT * FROM [restaurants.grades]

You may also want to include information from the base restaurants table. You can do this with a join. Flattened arrays can only be joined with the root document. The Sync App expects the left part of the join is the array document you want to flatten vertically. Disable SupportEnhancedSQL to join nested Apache CouchDB documents -- this type of query is supported through the Apache CouchDB API.

SELECT [restaurants].[restaurant_id], [restaurants.grades].* FROM [restaurants.grades] JOIN [restaurants] WHERE [restaurants].name = 'Morris Park Bake Shop'

The Sync App can return JSON structures as column values. The Sync App enables you to use standard SQL functions to work with these JSON structures. The examples in this section use the following array:

[
     { "grade": "A", "score": 2 },
     { "grade": "A", "score": 6 },
     { "grade": "A", "score": 10 },
     { "grade": "A", "score": 9 },
     { "grade": "B", "score": 14 }
]

JSON_EXTRACT

SELECT Name, JSON_EXTRACT(grades,'[0].grade') AS Grade, JSON_EXTRACT(grades,'[0].score') AS Score FROM Students;

JSON_COUNT

SELECT Name, JSON_COUNT(grades,'[x]') AS NumberOfGrades FROM Students;

JSON_SUM

SELECT Name, JSON_SUM(score,'[x].score') AS TotalScore FROM Students;

JSON_MIN

SELECT Name, JSON_MIN(score,'[x].score') AS LowestScore FROM Students;

JSON_MAX

SELECT Name, JSON_MAX(score,'[x].score') AS HighestScore FROM Students;

DOCUMENT

The DOCUMENT function can be used to retrieve the entire document as a JSON string. See the following query and its result as an example:

SELECT DOCUMENT(*) FROM Customers;

{ "id": 12, "name": "Lohia Manufacturers Inc.", "address": { "street": "Main Street", "city": "Chapel Hill", "state": "NC"}, "offices": [ "Chapel Hill", "London", "New York" ], "annual_revenue": 35,600,000 }

The Sync App maps SQL queries into the corresponding Apache CouchDB queries. The Sync App uses the Selector syntax to compute the desired results.

A detailed description of all the transformations is out of scope, but we will describe some of the common elements that are used.

SELECT Queries

The SELECT statement is mapped to the GET and POST methods. Below are example queries and the corresponding request payloads.

SELECT *
FROM sales

SELECT _id, value
FROM sales

{
  "fields": [
    "_id",
    "value"
  ],
  "skip": 0,
  "selector": {
    "_id": {
      "$exists": true
    }
  }
}

SELECT * 
FROM sales 
WHERE value=175033291697

{
  "selector": {
    "value": {
      "$eq": 175033291697
    }
  },
  "skip": 0
}

SELECT * 
FROM sales 
WHERE value = 175033291697 OR month='June'

{
  "selector": {
    "$or": [
      {
        "value": {
          "$eq": 175033291697
        }
      },
      {
        "month": {
          "$eq": "June"
        }
      }
    ]
  },
  "skip": 0
}

SELECT * 
FROM sales
WHERE name LIKE 'A%'

{
  "selector": {
    "name": {
      "$regex": "A%"
    }
  },
  "skip": 0
}

SELECT * FROM sales
WHERE month='June'
ORDER BY _id ASC

{
  "skip": 0,
  "sort": [
    {
      "_id": "asc"
    }
  ],
  "selector": {
    "month": {
      "$eq": "June"
    }
  }
}

SELECT *
FROM sales
WHERE month='June'
ORDER BY _id DESC

{
  "skip": 0,
  "sort": [
    {
      "_id": "desc"
    }
  ],
  "selector": {
    "month": {
      "$eq": "June"
    }
  }
}

INSERT Statements

The INSERT statement is mapped to the HTTP POST request, as shown in the following query and request payload.

SQL Query

INSERT INTO users (_id, age, status, [address.city], [address.postalcode]) 
VALUES ('bcd001', 45, 'A', 'Chapel Hill', 27517)

Apache CouchDB Query

{
  "address": {
    "city": "Chapel Hill",
    "postalcode": 27517
  },
  "_id": "bcd001",
  "age": 45,
  "status": "A"
}

UPDATE Statements

The UPDATE statement is mapped to the HTTP PUT method, as shown in the following query and request payload.

SQL Query

UPDATE users 
SET status = 'C', [address.postalcode] = 90210
WHERE _id = 'bcd001'

Apache CouchDB Query

{
  "_id": "bcd001",
  "_rev": "1-446f6c67e3a483feae8eaf112f18892c",
  "status": "C",
  "age": 45,
  "address": {
    "city": "Chapel Hill",
    "postalcode": 90210
  }
}

DELETE Statements

The DELETE statement is mapped to the DELETE method as shown below.

SQL Query

DELETE FROM users WHERE _id = 'bcd001'

Apache CouchDB Query

For this query, a DELETE request is sent to the users/{_id}?rev={_rev} endpoint.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

Authentication

SSL

Firewall

Proxy

Logging

Schema

Miscellaneous

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

The type of authentication to use when connecting to Apache CouchDB.

Remarks

• Basic: Set this to use basic User/Password authentication.
• None: No authentication is used.

The URL used to connect to the Apache CouchDB.

Remarks

The URL used to connect to the Apache CouchDB in the format http://{server}:{port}.

The Apache CouchDB user account used to authenticate.

Remarks

Together with Password, this field is used to authenticate against the Apache CouchDB server.

The password used to authenticate the user.

Remarks

The User and Password are together used to authenticate with the server.

Specify a comma-separated list of public databases to list as tables.

Remarks

This is needed for anonymous access (AuthScheme=None) because the database listing call requires user authentication.

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

The certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

This indicates whether to use the system proxy settings or not.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

• BASIC: The Sync App performs HTTP BASIC authentication.
• DIGEST: The Sync App performs HTTP DIGEST authentication.
• NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
• PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

If left unspecified, the default location is "%APPDATA%\\CData\\ApacheCouchDB Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Whether to list views from Apache CouchDB or not.

Remarks

Set this property to true for Apache CouchDB views to show up when listing tables.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Set FlattenArrays to the number of array elements to flatten into columns. Otherwise, arrays are returned as JSON strings.

Remarks

By default, nested arrays are returned as strings of JSON. The FlattenArrays property can be used to flatten the elements of nested arrays into columns of their own: Set FlattenArrays to the number of elements you want to return from nested arrays. The zero-based index is concatenated to the column name with a dot or the character specified by SeparatorCharacter. Other elements are ignored.

For example, you can return an arbitrary number of elements from an array of strings:

["FLOW-MATIC","LISP","COBOL"]

Setting FlattenArrays to -1 will flatten all the elements of nested arrays.

Using FlattenArrays is only recommended for arrays that are expected to be short. See NoSQL Database for other ways to access nested arrays.

Set FlattenObjects to true to flatten object properties into columns of their own. Otherwise, embedded objects as raw JSON strings.

Remarks

The property name is concatenated onto the object name with a dot or the character specified by SeparatorCharacter.

For example, you can flatten the nested objects below at connection time:

[
  { "grade": "A", "score": 2 },
  { "grade": "A", "score": 6 },
  { "grade": "A", "score": 10 },
  { "grade": "A", "score": 9 },
  { "grade": "B", "score": 14 }
]

Set FlexibleSchema to true to scan for additional metadata on the query result set. Otherwise, the metadata will remain the same.

Remarks

If false, the metadata will remain the same.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting

The maximum number of results to return per page from Apache CouchDB.

Remarks

The Pagesize property affects the maximum number of results to return per page from Apache CouchDB. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

The maximum number of rows to scan to look for the columns available in a table.

Remarks

The columns in a table must be determined by scanning table rows. This value determines the maximum number of rows that will be scanned.

Setting a high value may decrease performance. Setting a low value may prevent the data type from being determined properly, especially when there is null data.

The character or characters used to denote hierarchy.

Remarks

In order to flatten out hierarchical structures, the Sync App needs some specifier that states the path to a column through the hierarchy. If this value is "." and a column comes back with the name address.city, this indicates that there is a mapped attribute with a child called city. If your data has columns that already use a single period within the attribute name, set the SeparatorCharacter to a different character or characters.

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

Determines how the provider will scan the data to determine the fields and data types in each document collection.

Remarks

Since Apache CouchDB is schema-less, the columns in a table must be determined by scanning table rows. Set TypeDetectionScheme to toggle the automatic discovery of data types. Set RowScanDepth to configure type detection.

See Also

Type detection is part of Automatic Schema Discovery. Set FlattenArrays and FlattenObjects to configure how the Sync App projects columns over the hierarchical data.

You can fine-tune the discovered columns and data types by calling CreateSchema to generate a schema file. Schema files have a simple format that makes it easy to change column behavior.

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the Sync App.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
	"MyView": {
		"query": "SELECT * FROM Movies WHERE MyColumn = 'value'"
	},
	"MyView2": {
		"query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
	}
}

"UserDefinedViews", C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json