Community

Product DocsMenu

Example of a Configuration File for the Database Connector

You can use the following configuration file with the Database connector to index data from the sample Northwind database that can be used with Microsoft Access and Microsoft SQL Server.

Notes:

  • The following configuration file example will work with the Northwind database available from Microsoft Access 2013. When using other versions, validate that schema of your Northwind database matches with the parameter names in the following configuration file example.

  • In the following example, you must replace [PARAMETER] by @LastRefresh in an SqlClient scenario or by ? otherwise.

  • A Database source can regularly run incremental refreshes to re-index only the latest changed information from the database when you configure incremental refresh (see Enabling Incremental Refresh on a Database Source). 

<?xml version="1.0" encoding="utf-8" ?> 
<ODBC>
  <CommonMapping excludedItems="Customers">
    <AllowedUsers>
      <AllowedUser type="Windows" allowed="true">
        <Name>everyone</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </CommonMapping>
  <Mapping type="Orders">
    <Accessor type="query">
      SELECT Shippers.Company AS ShipperName, 
             Orders.[Order ID] AS ID, 
             Orders.[Customer ID], 
             Orders.[Order Date], 
             Orders.[Shipped Date], 
             Customers.Company, 
             Employees.[Last Name], 
             Employees.[First Name],
             Products.[Product Name],
             Products.[List Price]
      FROM   Orders, [Order Details], Shippers, Customers, Employees, Products
      WHERE  Orders.[Shipper ID] = Shippers.ID AND
             Orders.[Customer ID] = Customers.ID AND
             Orders.[Employee ID] = Employees.ID AND
             [Order Details].[Order ID] = Orders.[Order ID] AND
             [Order Details].[Product ID] = Products.ID AND
             Orders.[Order Date] >= [PARAMETER]
    </Accessor>
    <Fields>
      <Uri>http://www.coveo.com/Orders/details.aspx?Id=%[ID]</Uri>
      <ClickableUri>http://www.coveo.com</ClickableUri>
      <FileName>%[ID].txt</FileName>
      <Title>Order ID: %[ID]: %[Product Name]</Title>
      <ModifiedDate>%[OrderDate]</ModifiedDate>
      <Body>
        Customer: %[Company]
        OrderDate: %[Order Date]<br/> 
        ShippedDate: %[Shipped Date]<br/>
        Shipped via: %[ShipperName]<br/>
        %[Product Name], $%[List Price]
      </Body>
      <CustomFields>
        <CustomField name="Type">Order</CustomField>
        <CustomField name="OrderDate">%[Order Date]</CustomField>
        <CustomField name="Shipper">%[ShipperName]</CustomField>
        <CustomField name="sysAuthor">%[First Name] %[Last Name]</CustomField>
      </CustomFields>
    </Fields>
    <AllowedUsers>
      <AllowedUser type="CustomGroup" allowed="true">
        <Name>%[First Name] %[Last Name]</Name>
        <Server></Server>
      </AllowedUser>
    </AllowedUsers>
  </Mapping>
  <Mapping type="Customers">
    <Accessor type="object">Customers</Accessor>
    <Fields>
      <Uri> http://www.coveo.com/Customers/details.aspx?Id= %[ID]</Uri>
      <ClickableUri>http://www.coveo.com</ClickableUri>
      <ContentType>text/html</ContentType>
      <Title>%[Company] (%[ID])</Title>
      <Body>%[Company]
          %[Job Title] %[First Name] %[Last Name] 
          %[Business Phone]</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>
</ODBC>

The following list presents the different attributes found in the sample configuration file:

<CommonMapping>

You can specify multiple settings common for all or several of the mappings in the configuration file. Unless specified otherwise, <CustomFields> and <AllowedUsers> nodes used for all tables can be added (see <Mapping>). The excludedItems attribute lists all the objects from the ItemType source parameter that does not use the settings specified in CommonMapping. The following represents the entire CommonMapping nodes where the table Customers is excluded.

Refer to the following example:

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

<Mapping>

Defines how the connector retrieves data from the tables, how the indexed data is stored, and who has access to it. It has an attribute called type. It is important to add the name of the table used for mapping. The mapping for the table Orders is displayed and two tables are indexed, however one contains the most complex configuration.

Refer to the following example:

Note: All node values can be defined using the following syntax: %[odbcField]. When the indexing process starts, this value is replaced by the actual value from the Database source. For example, in <FileName>%[CustomerID].txt</FileName>, the term %[CustomerID] is dynamic and replaced by the source value; however, .txt is static.

<Accessor>

The <Accessor> node contains the query string used to extract the data from the table. This node is mandatory in order to have a valid mapping. It defines the method used to access the specified table. There is an attribute called type that can either be equal to object or query. In the example above, the query type is used, meaning that the information stored in the database is accessed using a SQL query and can be stored in multiple tables and views.

Refer to the following example where you must replace [PARAMETER] by @LastRefresh in an SqlClient scenario or by ? otherwise: 

SELECT Shippers.Company AS ShipperName, 
             Orders.[Order ID] AS ID, 
             Orders.[Customer ID], 
             Orders.[Order Date], 
             Orders.[Shipped Date], 
             Customers.Company, 
             Employees.[Last Name], 
             Employees.[First Name],
             Products.[Product Name],
             Products.[List Price]
      FROM   Orders, [Order Details], Shippers, Customers, Employees, Products
      WHERE  Orders.[Shipper ID] = Shippers.ID AND
             Orders.[Customer ID] = Customers.ID AND
             Orders.[Employee ID] = Employees.ID AND
             [Order Details].[Order ID] = Orders.[Order ID] AND
             [Order Details].[Product ID] = Products.ID AND
             Orders.[Order Date] >= [PARAMETER]

Note: You can use the paged query execution feature to return results in small batch number and decrease the likelihood of execution timeout (see Enabling Paged Query Execution for the Database Connector).

<Fields>

Collection of fields to be mapped when a document is indexed.

<Uri>

Address to which the user is redirected when clicking on the title of a search result. It is also used by the index to identify documents.

Refer to the following example:

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

Note: Even though the indexed database records do not have an actual Web address where they can be viewed by users, you must provide one. It is possible to generate an URI based on any dummy address as long as it is unique. You can create one formed with a primary key provided from your database.

<ClickableUri>

URI opened when trying to open documents from the user interface of CES.

Refer to the following example:

<ClickableUri>http://www.coveo.com</ClickableUri>

<FileName>

Name of the file indexed. The extension of the filename is used to select the appropriate converter.

Refer to the following example: 

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

<Title>

Name displayed on the search result page representing the title of the document indexed.

Refer to the following example:

<Title>Order ID: %[ID]: %[Product Name]</Title>
Note: %[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.

<Body>

Body of the document indexed. It can be a mix of static and dynamic content taken from the table.

Refer to the following example:

<Body>
        Customer: %[Company]
        OrderDate: %[Order Date]<br/> 
        ShippedDate: %[Shipped Date]<br/>
        Shipped via: %[ShipperName]<br/>
        %[Product Name], $%[List Price]
      </Body>
<Body>OrderDate: %[OrderDate] \n RequiredDate: %[Required Date] \n ShippedDate: %[ShippedDate] \nShipped via: %[ShipperName]
</Body>

The fields (%[field_name]) are all dynamic content types that change with each entry taken from the table.

Note: You cannot use both Body and BinaryBody nodes at the same time.

<CustomFields>

Mapping of ODBC fields to CES custom fields. It is made of a collection of <CustomField> nodes.

<CustomField>

Each <CustomField> node represents a custom field in CES and the data contained therein. In the example above, several <CustomField> nodes like the following are displayed: 

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

The name attribute is mandatory and represents the name of the CES custom field to bind data to. The value of this node should use the mapping syntax. The %[Order Date] expression instructs the connector to copy the information from the OrderDate database field to the OrderDate custom field in CES.

<AllowedUsers>

Mapping of ODBC fields to CES security. The connector does not index the permissions of the database automatically, therefore this field can be used to protect the data taken from the database.

Note: If you do not insert this section, everybody will have access to all the documents indexed.

<AllowedUser>

Rights given to users or groups regarding indexed documents. In the following example, there are two mandatory attributes:

Refer to the following example:

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

  • Type: It specifies the type of user to which rights are given (Windows, CustomGroup or CustomUser).

  • Allowed: It can be set to true or false.

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

<Name>

Name of a user or group to which you want to grant permissions. The FirstName and LastName values are used and stored in the table. This type of setup is useful only if these fields correspond to users that have access to the database.

Refer to the following example: 

<Name>%[First Name] %[Last Name]</Name>

You should have one or more tables listing the users as well as their respective permissions. Also, provide the name of a Windows group that contains all the users that should have access. Otherwise, this field can be set to Everyone, meaning everyone has access to the information taken from the table.

<Server>

Domain name for the current group or user. In the above example, no server name is specified. However, in an everyday context, it needs to be set to a specific value.

People who viewed this topic also viewed