XML configuration further customization

This is for:

In this article

Prerequisites
Enabling the refresh capability
Enabling pause/resume on updates
Defining items to delete
Retrieving complement information

To index database content, you must provide a list of XML queries defining the content that Coveo should retrieve and how it should index it.

In addition, you can customize your XML configuration to enable functionalities that will make your source and the indexing process more efficient. This article explains how to enable these quality of like functionalities.

Prerequisites

You have configured your Database source and have successfully run an initial build.

Enabling the refresh capability

A refresh operation keeps your source up to date by crawling your database and re-indexing only modified items. To support the refresh operation, the source needs to know the last update date of your database records. The source compares the last update date of a record with the date of the last source refresh to determine whether the record has changed since the last refresh and needs to be re-indexed.

To enable the refresh capability

Ensure that the target database has a Date type field indicating the latest modification date of the records to index. This field must be dedicated for Coveo and a record’s date must be updated whenever the record is modified.
In your XML configuration, add an ORDER BY clause to the <Accessor> SQL query, sorting by your latest modification date field.
In your XML configuration, configure the <ModifiedDate> element to pull the information from your latest modification database field using the dynamic value syntax. For example, if your database field name is dateModified, your <ModifiedDate> element should be configured as follows:
```
<ModifiedDate>%[dateModified]</ModifiedDate>
```
In your XML configuration, set the value for the <Accessor> element’s IncrementalRefreshFieldName attribute to the name of your latest modification database field.

In the SQL query, add a WHERE clause with a criterion on your last modification date field.

Example

The following example should work with common database engines such as Microsoft SQL Server, PostgreSQL, and MySQL.

<Accessor type="query" IncrementalRefreshFieldName="dateModified">
    Select
    id,
    title,
    dateModified,
    content,
    author
    FROM blog
    WHERE dateModified>=[LAST_REFRESH_PARAMETER] 
    order by dateModified
    
</Accessor>

	The source crawler can add the last source refresh date to the query. With a MSSQL or SQL Server database, select the `Microsoft SQL Server` data provider type and, in the XML configuration, replace `[LAST_REFRESH_PARAMETER]` with `@LastRefresh`. With a database of a different type, select the `ODBC` data provider type and, in the XML configuration, replace `[LAST_REFRESH_PARAMETER]` with `?`.
	Append a row-limiting clause to paginate the results. See Enable pause/resume on updates for instructions.

Enabling pause/resume on updates

By default, Database sources don’t offer the option to pause and resume content update actions. Enabling pause and resume operations requires paging, that is, segmenting a single query into multiple smaller queries to retrieve all records. When you pause a content update, the crawler stops execution after the current query. When you resume, it moves on to the next query.

The query segmentation is achieved by appending a row-limiting clause to your Accessor query that uses the @pageOffset and @pageSize placeholders. The crawler repeatedly executes the query, with @pageOffset starting at zero and increasing by your @pageSize value at each iteration, until no additional rows are returned.

Note

Adding paging not only adds pause/resume support. This also provides the following benefits:

Memory efficiency: Prevents large result sets from exhausting crawler memory.
Reduced database load: Spreads the query load across smaller, sequential requests instead of one large query.

To enable pause and resume actions

In your XML configuration, set the value for the <Accessor> element’s OrderByFieldName attribute, and if required, for the OrderByFieldType attribute.
1. Set IncrementalRefreshFieldName to the name of your latest modification database field.
2. Set OrderByFieldName to the name of your latest modification database field.
3. (If required) Set OrderByFieldType to the .NET data type of your database field.
  
  More on the OrderByFieldType attribute
  
  This attribute isn’t normally required. The source automatically tries to determine the data type by preparing the SQL query - without however executing it - and looking at the schema of the results.
  
  However, if a specific DBMS doesn’t handle that process correctly, you can manually specify the data type with this attribute.
  
  The allowed types are the following: DateTime, short, ushort, int, uint, long, ulong, float, double, string, System.DateTime, System.Int16, System.UInt16, System.Int32, System.UInt32, System.Int64, System.UInt64, System.Single, System.Double, and System.String.

In your XML configuration, append the row-limiting clause to your <Accessor> element query. The expression to use depends on your data provider type:

Data provider type Expression

Data provider type	Expression
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`

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

Microsoft SQL Server example:

<Accessor type="query"
  OrderByFieldName="dateModified"
  OrderByFieldType="DateTime"
  IncrementalRefreshFieldName="dateModified"> 
    Select
    id,
    title,
    dateModified,
    content,
    author
    FROM blog
    WHERE dateModified>=@LastRefresh
    order by dateModified
    OFFSET @pageOffset ROWS FETCH NEXT @pageSize ROWS ONLY; 
</Accessor>

	The row-limiting clause.
	You can enable both pause/resume actions and the refresh capability at the same time.

Defining items to delete

To delete items from the index based on a condition that changes over time (for example, age), use the AccessorForItemsToDelete element in your XML configuration.

Item deletion takes place during refresh operations only. Ensure you have enabled the refresh capability on your source.

Retrieving complement information

The Database source acquires information about each indexed item through a query performed against a database. For each query, it’s possible to associate one or more subqueries to be executed and used to complement information.

Example

You can run a main query, and for each row, run a subquery that crawls more/different information. All the results of a single row from the main query, along with everything from the subquery, are merged into a single item.

The source requires a mapping configuration to execute properly. For each mapping type, it’s necessary to specify an Accessor representing the SQL query to execute.

Note

Coveo forbids using dynamic expressions that populate the Uri field with metadata extracted through a subquery. This is because Coveo wouldn’t be able to resolve the Uri value should the query ever fail. As a result, the impacted content items wouldn’t be indexed or might be deleted from your index.

Specifying subqueries

To specify subqueries, set the type of the Accessor to query.

<Accessor type="query">

Following the Accessor definition, add an AccessorSubQueries node with all subqueries:

Note

The master key (value following SELECT) in the AccessorSubQuery node must match exactly the one returned by the server. The key you include must also have the same casing. The following error is thrown when the key couldn’t be found:

Unable to index document : There’s a formatting error in a sub query. Cannot find master key %[key].

<AccessorSubQueries>
  <AccessorSubQuery name="FirstNameLastName" separator=";" allowDuplicates = "false">
    SELECT firstName,
    lastName
    FROM employeelist
    WHERE Email_id = %[sender]
  </AccessorSubQuery>
</AccessorSubQueries>

Subquery attributes

name

Subquery name referred to in the Fields section of the mapping.
separator

Separator used when concatenating many rows.
allowDuplicates (optional)

This attribute is mainly used when your subquery returns many rows. If set to false, duplicates in the results are ignored in the concatenation of the results. If set to true, duplicates are present.
singleQuoteEscapeSequence (optional)

When the value of the returned field contains single quotes, these single quotes must be escaped. By default when you omit this attribute, the source escapes the single quotes by doubling them (for example, ''). Usually, this escaping mechanism should work. However, some database types require a different escaping sequence for single quotes. In such cases, use this attribute to specify the single quote escape sequence.
Example
For the MySQL database, the single quote escaping sequence is \'. In this case, in the AccessorSubQuery tag, include the singleQuoteEscapeSequence attribute as follows:
<AccessorSubQuery name="FirstNameLastName" separator=";" allowDuplicates = "false" singleQuoteEscapeSequence="\'">

Subquery master key

In a subquery, a master key used in the WHERE clause must respect the format %[fieldName], which corresponds to metadata acquired from the main accessor. The master key is used to make the join between the main query and subqueries.

Specifying subquery metadata for fields

The <Fields> section of the mapping configuration is used to specify the metadata to use for indexing.

See the following example for a typical <Fields> section of a mapping configuration:

<Fields>
  <Uri>link: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>

The metadata of a subquery can be specified for a field or a custom field. The way to specify is similar to the way it’s done when referring a field coming from the main accessor: %[subQueryName.fieldName]. In the above example, custom field firstName is referring subquery named FirstNameLastName and uses the firstName metadata.

See the following for a complete mapping configuration, used in Coveo unit tests:

<?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>link: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>