Database Source XML Configuration Reference

This is for:

Developer System Administrator

When creating a Database source, you must provide an XML configuration defining the content to retrieve.

This XML configuration should consist of queries against your database to retrieve and copy the data from record fields to Coveo fields. It should contain only read queries, or else you could make unwanted changes to your database.

You can also run subqueries on every item to complete the information with more complex queries. See Further Configuration for other options you can implement.

Your XML configuration should start with the following:

<?xml version="1.0" encoding="utf-8" ?>
<ODBC>

The nodes defined below should appear inside the <ODBC></ODBC> node.

The following is an example of a complete configuration:

<?xml version="1.0" encoding="utf-8" ?>
<ODBC>
  <CommonMapping excludedItems="employeelist">
    <AllowedUsers>
      <AllowedUser type="Windows" allowed="true">
        <Name>everyone</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </CommonMapping>
  <Mapping type="message">
    <Accessor type="query">
      SELECT message.mid,
      message.sender,
      message.date,
      message.message_id,
      message.subject,
      message.body,
      message.folder
      FROM message
      WHERE DATE like '2001-04-07%'
    </Accessor>
      <AccessorSubQueries>
        <AccessorSubQuery name="FirstNameLastName" separator=";">
          SELECT firstName, lastName
          FROM employeelist
          WHERE Email_id = %[sender]
        </AccessorSubQuery>
      </AccessorSubQueries>
    <Fields>
      <Uri>https://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri>
      <ClickableUri>https://www.coveo.com</ClickableUri>
      <FileName>Message_%[mid].txt</FileName>
      <Title>Message_%[mid]</Title>
      <ModifiedDate>%[date]</ModifiedDate>
      <Body>%[body]</Body>
      <CustomFields>
        <CustomField name="sysAuthor">%[sender]</CustomField>
        <CustomField name="firstName">%[FirstNameLastName.firstName]</CustomField>
        <CustomField name="lastName">%[FirstNameLastName.lastName]</CustomField>
      </CustomFields>
    </Fields>
    <AllowedUsers>
      <AllowedUser type="CustomGroup" allowed="true">
        <Name>everyone</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </Mapping>
</ODBC>

Dynamic Values

Dynamic values are metadata values acting as placeholders for node values in your XML configuration. When writing your XML configuration, you associate Coveo fields and database fields with the following syntax: <COVEO_FIELD>%[<DATABASE_FIELD_NAME>]</COVEO_FIELD>. As a result, when indexing each of your database items, Coveo populates its field with the value of the specified database field.

For example, in <FileName>%[CustomerID].txt</FileName>, the %[CustomerID] is dynamic and replaced with actual customer IDs, such as JSmith00291 and BAllen00815. On the other hand, .txt is static. The resulting filenames are therefore JSmith00291.txt, BAllen00815.txt, etc., and Coveo indexes them as the value of its filename field.

Mapping (Required)

The Mapping node defines how to retrieve data from the tables, how to index this data, and who should be allowed to access it through a Coveo-powered search interface.

Dynamic values are supported.

Attribute:

  1. type (required): The name of the table to index.

As an example, the following configuration indexes content from the Customers table.

<Mapping type="Customers">
  <Accessor type="object">Customers</Accessor>
  <Fields>
    <Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>
    <ClickableUri>http://www.example.com</ClickableUri>
    <ContentType>text/html</ContentType>
    <Title>%[Company] (%[ID])</Title>
    <Body>%[Company]
      %[FirstName] %[LastName]
      %[JobTitle]
      %[BusinessPhone]</Body>
    <CustomFields>
    <CustomField name="Type">Customer</CustomField>
    <CustomField name="ID">%[ID]</CustomField>
    </CustomFields>
  </Fields>
  <AllowedUsers>
    <AllowedUser type="Windows" allowed="true">
    <Name>everyone</Name>
    <Server></Server>
    </AllowedUser>
  </AllowedUsers>
</Mapping>

Accessor (Required)

Provide a query string to use to extract the desired data from the table.

Use the type attribute to specify whether the information stored in your database should be accessed using an object or a SQL query. Allowed values are object and query for SQL queries.

The following example showcases the object type:

<Accessor type="object">Customers</Accessor>

This example showcases the query type:

<Accessor type="query">
  SELECT Shippers.Company AS ShipperName,
    Orders.[OrderID] AS ID,
    Orders.[CustomerID],
    Orders.[OrderDate],
    Orders.[ShippedDate],
    Customers.Company,
    Employees.[LastName],
    Employees.[FirstName],
    Products.[ProductName],
    Products.[ListPrice]
  FROM   Orders, [OrderDetails], Shippers, Customers, Employees, Products
  WHERE  Orders.[ShipperID] = Shippers.ID AND
    Orders.[CustomerID] = Customers.ID AND
    Orders.[EmployeeID] = Employees.ID AND
    [OrderDetails].[OrderID] = Orders.[OrderID] AND
    [OrderDetails].[ProductID] = Products.ID AND
    Orders.[OrderDate] >= [<PARAMETER>]
</Accessor>

In this last example, you would replace <PARAMETER> with @LastRefresh in an SqlClient scenario or with ? otherwise.

Paging

To specify how you want the content to be paged, you must add a paging expression to your accessor. This expression contains two parameters that you must replace with an integer that’s greater than or equal to zero.

The parameters are:

  • @pageOffset, which specifies the number of pages to skip before starting to return content. To start at page 1, enter 0.

  • @pageSize, which specifies the number of items in a page.

However, the expression to use varies depending on your data provider type:

Data provider type Expression to use

Databricks

LIMIT @pageSize OFFSET @pageOffset

MySQL

LIMIT @pageOffset, @pageSize

Microsoft SQL Server

OFFSET @pageOffset ROWS FETCH NEXT @pageSize ROWS ONLY;

PostgreSQL

LIMIT @pageSize OFFSET @pageOffset

Oracle

OFFSET @pageOffset ROWS FETCH NEXT @pageSize ROWS ONLY

Redshift

LIMIT @pageSize OFFSET @pageOffset

For example, with a Microsoft SQL Server Database source, the following accessor starts paging at the first page and returns 100 items per page:

<Accessor type="query"
  OrderByFieldName="dateModified"
  OrderByFieldType="DateTime"
  IncrementalRefreshFieldName="dateModified">
    Select
    id,
    title,
    dateModified,
    content,
    author
    FROM blog
    WHERE dateModified>=[PARAMETER]
    order by dateModified
    OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
</Accessor>

AllowedUsers

If you don’t want your database content to be available to anyone who can access your Coveo-powered search interface, you must define a permission system for Coveo to use. Coveo will replicate this permission system in your search interface, displaying only the results a user is allowed to access.

If access to your database is managed by Active Directory, you must include an AllowedUsers node with a type="Windows" attribute. For example:

<CommonMapping>
<AllowedUsers>
  <AllowedUser type="Windows" allowed="true">
  <Name>domain\group</Name>
  <Server></Server>
  </AllowedUser>
</AllowedUsers>

AllowedUser

Define the permission to give to the user or group specified under Name.

Attributes:

  1. type (required): The type of user/group (Windows, CustomGroup, or CustomUser).

  2. allowed (required): Whether the user or group specified under Name should be granted access to the content retrieved with the parentMapping configuration.

<AllowedUser type="CustomGroup" allowed="true">

You can define multiple allowed users within a single AllowedUser node by separating them by a semicolon (;).

Name

Provide the name of the user or group to which the permission defined under AllowedUser applies. Dynamic values are supported.

<Name>%[FirstName] %[LastName]</Name>
Server

Provide the domain name of the user or group specified under Name.

Fields

List the database fields to map with Coveo default field when indexing an item. The child nodes should be named after Coveo fields. Dynamic values are supported.

See also CustomFields.

The following example shows how data from the Customers table will be indexed in the Coveo title and body fields.

<Fields>
  <Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>
  <ClickableUri>http://www.example.com</ClickableUri>
  <ContentType>text/html</ContentType>
  <Title>%[Company] (%[ID])</Title>
  <Body>%[Company]
    %[First Name] %[Last Name]
    %[Job Title]
    %[Business Phone]</Body>
  <CustomFields>
    <CustomField name="Type">Customer</CustomField>
    <CustomField name="ID">%[ID]</CustomField>
  </CustomFields>
</Fields>

BinaryBody

Database BLOB field from which Coveo should retrieve the binary body (for example, image, file) of the item to index.

Dynamic values are supported, however you must enter the name of the desired field only, that is, without the %[].

<BinaryBody>customerLogo</BinaryBody>
Note

Alternatively, you can use a Body node to index non-binary content as the body of an item. The BinaryBody and Body nodes are mutually exclusive.

Body

Body of the item to index. Dynamic values are supported.

Example:

<Body>
  Customer: %[Company]
  OrderDate: %[OrderDate]<br/>
  ShippedDate: %[ShippedDate]<br/>
  Shipped via: %[ShipperName]<br/>
  %[ProductName], $%[ListPrice]
</Body>
Note

Alternatively, you can use a BinaryBody node to index binary content as the body of an item. The Body and BinaryBody nodes are mutually exclusive.

ClickableUri

URI of the page to open when a user clicks a Database search result in a Coveo-powered search interface. It can be identical to the Uri.

Dynamic values are supported.

See also PrintableUri.

ContentType

Type of content of an item. Enter text/html or the extension of item. Coveo uses this information to determine which converter should handle the item during the indexing process.

If your database records have filenames, you can provide a FileName instead. It is required to provide either a FileName or a ContentType.

CustomFields

Associate database fields with Coveo custom fields.

CustomField

Each CustomField node represents a Coveo custom field and the data it contains. Dynamic values are supported.

For instance, with the following mapping, the content of the Order Date database field will populate the Coveo OrderDate field.

<CustomField name="OrderDate">%[OrderDate]</CustomField>

Attribute:

  1. name (required): The name of the Coveo custom field to populate with the database field data.

FileName

Name of the item to index. Dynamic values are supported. Include the extension. Coveo uses the extension to determine which converter should handle the item during the indexing process.

For instance, with the following configuration, Coveo will index each item as a TXT file named after the item ID.

<FileName>%[ID].txt</FileName>

If your database records do not have filenames and you do not want to create an artificial one, provide a ContentType instead. It is required to provide either a FileName or a ContentType.

ModifiedDate

Date on which the indexed item was last modified. Dynamic values are supported.

This date is used by Coveo to enable the refresh capability on your Database source.

<ModifiedDate>%[LastEditedOnDate]</ModifiedDate>

PrintableUri

URL to display to a user who is about to click a Database search result in a Coveo-powered search interface. For instance, when clicking an item, users could be redirected to example.com/id/123456. However, you could decide to overwrite the displayed URL with the following node:

<PrintableUri>example.com</PrintableUri>

Dynamic values are supported.

If you do not provide a printableUri, Coveo displays the clickableUri.

Title

Item name to display on a Coveo-powered search interface. Typically, it corresponds to the title of the indexed item.

Dynamic values are supported.

In the following example, %[ID] and %[Product Name] are used to give each entry its own name based on the ID and product name fields in the Orders table.

<Title>Order ID: %[ID]: %[ProductName]</Title>

Uri (Required)

Address to which an end user is directed when clicking the title of a Database search result in a Coveo-powered search interface.

Note

Even if your database records aren’t available through a URI, you must still provide a value that will be different for all items, since the Coveo index also uses the uri value as a unique identifier for items. In this case, you can use a dynamic value whose value is unique to each item.

However, Coveo strongly discourages using dynamic expressions that populate the Uri field with metadata extracted through a subquery. If the subquery ever fails, Coveo won’t be able to resolve the Uri value. As a result, the impacted content items won’t be indexed or might be deleted from your index. To prevent such issues, Coveo is considering forbidding this practice in the future.

Dynamic values are supported, as shown in the following example.

<Uri>http://www.example.com/Customers/details.aspx?Id=%[ID]</Uri>

See also ClickableUri and PrintableUri.

CommonMapping

The CommonMapping node allows you to specify settings common to all or several of the mappings provided in the configuration file.

Use the excludedItems node attribute to list the objects to which the CommonMapping settings do not apply. For instance, the following configuration excludes the Customers table.

<CommonMapping excludedItems="Customers">
  <Fields>
    <CustomFields>
      <CustomField name="ID">%[ID]</CustomField>
    </CustomFields>
  </Fields>
  <AllowedUsers>
    <AllowedUser type="Windows" allowed="true">
      <Name>everyone</Name>
      <Server></Server>
    </AllowedUser>
  </AllowedUsers>
</CommonMapping>