Product DocsMenu

Coveo Platform 7.0 >
Administrator Help > Connectors > Database Connector > Enabling Incremental Refresh on a Source

Enabling Incremental Refresh on a Database Source

Incremental refresh keeps documents up-to-date by scanning repositories and re-indexing modified documents at short intervals. To enable this feature on a database source, database items that have to be indexed, such as tables or views, must contain a Date type field to indicate their latest modification date. Fields can be given any name as long as it has the right data type. Furthermore, whenever a record is modified, it is important to update the latest modification date field (see Configuring and Indexing a Database Source and Additional XML Attributes).

Note: The incremental refresh does not take into account deleted documents. A source full refresh or rebuild is required.

In the SQL query, the SELECT statement must have a WHERE clause with a criterion on the last modification date field.

Example: The following simple example should work with common database engines such as Microsoft SQL Server 2012, PostgreSQL, and MySQL. You must replace [PARAMETER] by @LastRefresh in an SqlClient scenario (MSSQL/SQLServer databases) or by ? otherwise (e.g., NorthWind databases). The [PARAMETER] field is sent by the crawler to the query to indicate when the last increment refresh was performed.

<Accessor type="query"
  OrderByFieldName="dateCreated"
  OrderByFieldType="DateTime"
  IncrementalRefreshFieldName="dateModified">
  <![CDATA[
    Select
    id,
    title,
    dateModified,
    content,
    author
    FROM blog
    WHERE dateModified>=[PARAMETER] 
    order by dateModified
    OFFSET @startRow ROWS FETCH NEXT (@endRow-@startRow) ROWS ONLY;
  ]]>
</Accessor>

The example also includes support for pagination (see OFFSET FETCH Clause (SQL Server Compact)).

What's Next?

Ensure that you also created an incremental refresh schedule on your database source in CES (see Scheduling a Source Incremental Refresh).

People who viewed this topic also viewed