Coveo Enterprise Search (CES) uses crawlers to gather files from Web servers, files servers, SharePoint servers, etc. Until now CES could only index database content through its Web crawlers if the database content was published through a Web interface or XML feed. The ODBC Custom Crawler now allows to index database content directly through ODBC connection and SQL queries.

ODBC Custom Crawler will index data from any database type, given that they provide an ODBC connector. All major database vendors provide one: MS SQL Server, Oracle, DB2, etc.

ODBC Custom Crawler was first introduced with CES 4.0 SP1 and evolved with SP2. It can be configured in two ways:

· Index a database view (SP2): configure a source with a connections string and a view name that outputs at least a field named Uri.

· Index a database table, view or query (SP1 and SP2): configured through a configuration file. This file is XML formatted and contains:

o An ODBC connection string;

o One or more SQL queries to extract records;

o "Mappings" to copy data from record fields to CES system and custom fields.

Applies To: CES(S) 4.0 SP1+

Linking

One of the most important CES system field is the required URI field. This field is the underlying link of the search result. The first question you should ask yourself is: Where do I want to redirect my user when he will click this result?

In the best case scenario, you already have a Web interface that renders (publishes) the content of your database. You can probably recreate the address of a record page with a field or two from your queries.

In the worst case scenario, you don't have anywhere to redirect your users to when they click on the search result's title. At the present, CES offers a few functionalities that can help you out (Summary page and Quick View page), but you will need to modify CES' Web interface, see Using CES as a Database Viewer.

Overview of SP1 vs. SP2

Improvements to ODBC Crawler in SP2 induced some changes in the Source configuration fields and in the configuration file.

Source Configuration

	SP1	SP2
Addresses	Leave the source addresses empty.	Specify the connection string
Items to crawl	N.A.	Comma-separated list of object names. An object is a table or view. Unless the table to index has a field named Uri we recommend to design a database View to output field names that will match CES required fields: Uri
Configuration File Path	Specify the path and file name of the configuration file.	Not required. If you want to use a configuration file: 1. Add a parameter named ConfigFile in Configuration/Crawlers/Custom Crawler/ODBC. 2. Specify the path and file name of the configuration file source's new ConfigFile property.
Open results with cached version	N.A.	Allows automatically Quick View of documents when users click on titles. Useful when indexing database content that doesn't have an actual URL to redirect users to.

ODBC Custom Crawler Configuration File

SP1

SP2

<FileName> and <ContentType> nodes

Either one of them is required for CES to associate a Document Type to the document.

If the document has no file name, recommended to use <ContentType>text/html</ContentType> to associate the document to Web Documents which is configured by default to Detect the appropriate converter.

Not required.

If not specified, the BinaryData document type will be used which is configured by default to Detect the appropriate converter.

ODBC Crawler Setup

SP1

1. Create a configuration file from scratch of from one of the samples below and save it anywhere on CES server. A best practice would be to save it in the configuration folder (default location: C:\CES4\Config\).

2. Create a source to use the ODBC crawler for your database.

a. Open the Administration Tool.

b. In the Index tab, select the Collection you want to create your source in.

c. In the Sources pane, click the Add button.

d. In the:

i. Name field: Enter a name for your source.

ii. Source Type field: Select Custom Crawler from the drop list.

iii. Configuration File field: Enter the path for the configuration file.

e. Click Save.

3. Create Custom Fields that will link to the data from your database.

a. In the Index tab select your source.

b. Click on the Field link on the left hand side of the screen.

c. From the fields’ page, select your field set from the drop down list and click edit.

d. This should bring you automatically to the Custom Fields’ page. If not, select Custom Field from the menu on the left hand side of the screen.

e. In the:

i. Name field: Enter a name for this field.

ii. Type field: Select the data type of this field

iii. Metadata Name: Use the name attribute in your <CustomField> element.

iv. Options section: Select the option that you wish this field to have.

v. Repeat steps (i) to (iv) for each custom field you need.

4. Build your Source.

SP2

Using a Configuration File

2. Create a source to use the ODBC crawler for your database.

a. Open the Administration Tool.

b. In the Index tab, select the Collection you want to create your source in.

c. In the Sources pane, click the Add button.

d. In the:

i. Name field: Enter a name for your source.

ii. Source Type field: Select Custom Crawler from the drop list.

iii. Configuration File field: Enter the path for the configuration file.

Note: The Configuration File field is not displayed by default. You must configure the crawler to display it, as explained in Configuration File Path.

e. Click Save.

3. Create Custom Fields that will link to the data from your database.

a. In the Index tab select your source.

b. Click on the Field link on the left hand side of the screen.

c. From the fields’ page, select your field set from the drop down list and click edit.

d. This should bring you automatically to the Custom Fields’ page. If not, select Custom Field from the menu on the left hand side of the screen.

e. In the:

i. Name field: Enter a name for this field.

ii. Type field: Select the data type of this field

iii. Metadata Name: Use the name attribute in your <CustomField> element.

iv. Options section: Select the option that you wish this field to have.

v. Repeat steps (i) to (iv) for each custom field you need.

4. Build your Source.

Using a View

1. Create a source to use the ODBC crawler for your database.

a. Open the Administration Tool.

b. In the Index tab, select the Collection you want to create your source in.

c. In the Sources pane, click the Add button.

d. Specify the source properties.

i. Name: Name for your source.

ii. Source Type: Select Custom Crawler - ODBC.

iii. Addresses: Connection string to your database.

iv. Items to Crawl: Name of the objects to index. See Items To Crawl.

v. Open results with cached version: (optional) Check this option if you want user to automatically open see the Quick View when they click on result titles.

e. Click Save.

2. In your database, create a view that returns at least the field Uri. The value of the Uri field should be a valid Uri i.e. must have any scheme prefix followed by an unique identifier e.g. odbc://id=4. It doesn't have to be browsable or have any special signification.

Using CES as a Database Viewer

If your database records don't actually have any URI (Web address) where it can be viewed by your users, you can use CES as a basic viewer.

SP1

Disable titles' hyperlink. Your users will use the "Summary" or "Quick View" links to view record contents.

1. Backup-copy " C:\Program Files\Coveo Enterprise Search 4\Web\Coveo\Skins\Default\ResultTemplate.ascx" (or "...\SharePoint\..." if you are using CES for SharePoint).

2. Edit the original in any text editor.

3. Locate the code block

<ces:resultopenlink id="opl" runat="server">

  <ces:resulttitle id="ttl" length="90" runat="server"/>

</ces:resultopenlink>

4. Remove the 1^st and 3^rd lines (remove references to ces:resultopenlink), i.e. leave only the line

<ces:resulttitle id="ttl" length="90" runat="server"/>

5. Save the file

SP2

CHECK the Open results with cached version source option. It instructs titles' hyperlink to open documents in Quick View mode automatically.

Configuration File Reference

Basically, the only thing needed in order to create a new source that uses the ODBC custom crawler is a configuration file. This file is written in xml. Samples can be found in appendix of this document..

The file is made of two mandatory sections that must be found under the ODBC Node: Configuration and Mapping. The CommonMapping section is optional. Figure 1 illustrates this basic structure.

Figure 1 – Basic xml structure of the configuration file.

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

<ODBC>

  <Configuration>

  </Configuration>

  <Mapping>

  </Mapping>

  <CommonMapping>

  </CommonMapping>

</ODBC>

<Configuration> section

This section defines the connection string used to connect to the ODBC source you want to index and it also contains a list of the various objects you want to index.

Figure 2 – Overview of the configuration section.

<Configuration>

  <ConnectionString></ConnectionString>

  <ItemType></ItemType>

</Configuration>

<ConnectionString/>

This node represents the connection string used to access the ODBC source you want to index. Information on how to build a connection string for an ODBC source can be found at www.connectionstrings.com.

Example

<ConnectionString>Driver={SQL Server};Server=svr-ms;Database=NorthWind;UID=User;PWD=Password</ConnectionString>

<ItemType>

This node represents the list of mappings you want to index. This list is made of comma separated values.

Example

<ItemType>Customers, Orders, Products</ItemType>

These two parameters, the connection string and item type to index, can be provided via the administration interface, we will see later how to modify the ODBC custom crawler to be able to provide these two parameters via the administrator interface of CES.

<CommonMapping> section

This section defines fields that should be mapped for more than one mapping. If you plan on indexing more than one object from your source, this section will avoid you to define fields that are identical for every mapping. Figure 4 illustrates an overview of the common fields section.

Figure 4 – Overview of the common fields section.

<CommonMappings excludedItems="ItemType">

</CommonMappings>

<CommonFields> section

This node represents a Fields node, as in the mapping section, that will be added to all mapping except the ones specified in the excludedItems list. This list is made of comma separated values.

<CommonFields excludedItems="ItemType">

</CommonFields>

<Mapping> section

This section defines the mapping between the fields of the OBDC object you want to index and the fields in CES that should hold this information. This section can be duplicated as many times as you want, each occurrence would mean a new type of item to index. A mapping is made of an accessor and collection of fields, and a collection of allowed users. Figure 3 illustrates an overview of the mapping section.

Note: All nodes values can be defined using this syntax: %[odbcField]. When the indexation process will start, this value will be replaced by the actual value from the ODBC source. Ex.: In <FileName>%[CustomerID].txt</FileName>, “%[CustomerID]” is dynamic and will be replaced by the source value but ".txt” is static and will be identical for each document found in the source.

Figure 3 – Overview of the mapping section.

<Mapping type="ItemType">

  <Accessor type="AccessorType"></Accessor>

  <Fields>

    <Uri></Uri>

    <UriCaption></UriCaption>

    <ClickableUri></ClickableUri>

    <PrintableUri></PrintableUri>

    <OriginalUri></OriginalUri>

    <FileName></FileName>

    <Title></Title>

    <Body></Body>

    <ModifiedDate></ModifiedDate>

    <ContentType></ContentType>

    <CustomFields>

      <CustomField name="FieldName"></CustomField>

    </CustomFields>

  </Fields>

  <AllowedUsers>

    <AllowedUser type="SecurityType" allowed="isAllowed">

      <Name></Name>

      <Server></Server>

    </AllowedUser>

  </AllowedUsers>

</Mapping>

<Mapping type="ItemType">

Each Mapping node must have a type attribute. This attribute represents the name of this mapping and is the value that is used to reference a mapping.

<Accessor type="AccessorType">

The accessor of a mapping represents how to retrieve the information needed to be indexed. This node is mandatory in order to have a valid mapping. The type attribute of an accessor is mandatory and can have two possible values: object or query.

Depending on the value of the type attribute, the value of the accessor node will be different:

· Object: You should provide the name of an object from your source, a table or a view in the case of a SQL source.

· Query: You should provide a SQL query that will return every field that you want indexed in CES.

<Fields>

This node represents a collection of fields to be mapped when a document is indexed. Here are the nodes that he can contain: Uri, UriCaption, ClickableUri, PrintableUri, OriginalUri, FileName, Title, Body, ModifiedDate, ContentType, and CustomFields.

<Uri>

This node represents the Uri of a document. This node is mandatory in order to index documents. The mapping syntax should be used to define the mapping of this field.

<ClickableUri>

This node represents the Uri that will be opened when trying to open the documents from the user interface of CES.

<PrintableUri>

This node represents the Uri to be displayed in the UI of CES.

<FileName>

This node represents the name of the file that is indexed. The extension part of the file name will be used to select an appropriate converter. Either this node or <ContentType> must be specified. It is used to find an appropriate converter. The mapping syntax should be used to define the mapping of this field.

<ContentType>

This node represents the type of data in the document’s body. Either this node or <ContentType> must be specified. It is used to find an appropriate converter. If you don’t know the type of data you plan to index, use the value binarydata and CES will try to find the right converter for your document data. The mapping syntax should be used to define the mapping of this field.

Recommended: for database records without actual file name, don't specify <FileName> node and specify <ContentType>text/html</ContentType> instead.

<Title>

This node represents the title of the document that is indexed. The mapping syntax should be used to define the mapping of this field.

<Body>

This node represents the body of the document that is indexed; the body can by textual data or binary data i.e. you can specify a BLOB field name. The mapping syntax should be used to define the mapping of this field.

<ModifiedDate>

This node represents the date of the last modification on a document. The value provided to this field must be a date. The mapping syntax should be used to define the mapping of this field.

<CustomFields>

The CustomFields node allows mapping ODBC fields to CES custom fields. This node is made of a collection of <CustomField> node.

<CustomField name="FieldName">

This node represents a mapping of an ODBC field to a CES custom field. 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.

System fields can be overridden by creating a custom field named after the system field’s name. Ex.: <CustomField name="sysauthor">%[Author]<CustomField> would override the document’s author with data from the source.

<AllowedUsers>

The AllowedUsers node allows mapping ODBC fields to CES security. This node is made of a collection of <AllowedUser> node. This section is optional. If this section is missing, everybody will have access to every document indexed.

<AllowedUser type="SecurityType" allowed="isAllowed">

This node represents the right of a user or group on indexed documents. The two attributes, type and allowed, of this node are mandatory. The type attribute can be one of the following values: Windows, CustomGroup, and CustomUser. The allowed attribute value can be true or false.

Two nodes can be defined inside of an AllowedUser node: Name and Server. These nodes should contain the value to use to create the security. The mapping syntax should be used if you need to use data from your ODBC source as security right on indexed documents. If these nodes are missing, everybody will have access to every document indexed.

Northwind-Advanced Sample Tour

In this section, we find a detailed explanation of the configuration file found in Appendix B - Northwind (advanced). This is the file that the ODBC crawler uses for NorthWind database. The NorthWind databases are a sample databases part of Microsoft Access and Microsoft SQL Server products.

This document has two samples: Appendix A and B. The first one is the minimal configuration and the second one is more extended. This section uses excerpts from Appendix B for SQL Server.

<Configuration>

This section is where the connection string and the table mappings are defined. It provides the crawler with the information required to connect to the database and to know what tables it will index. The <Configuration> section of the sample looks like this:

<Configuration>

    <ConnectionString>Driver={SQL Server}; Server=jpdery;    Database=NorthWind;UID=test;PWD=test

    </ConnectionString>

    <ItemType>Customers, Orders, Products</ItemType>

</Configuration>

<ConnectionString>

This is where we put the connection string for the database. Each type of database has a different syntax for the connection string. To learn more about the different kinds of connection string syntaxes, you can go to http://www.connectionstrings.com/.

Driver={SQL Server}: This part would most likely remain the same for any standard SQL Server database.

Server=jpdery;Database=NorthWind: Server is the name of the server hosting the database. Database is the name of the database to connect to. In this example the server is jpdery, and the database is NorthWind.

UID=test;PWD=test UID is the user name you wish to use to connect to the database and PWD is the password for that user.

<ItemType>

The <ItemType> node is where you list the mapping names for the database tables you want to index. In our example, we have:

<ItemType>Customers, Orders, Products</ItemType>

This tells the crawler to expect different type of database records and the queries required to retrieve them: Customers, Orders, and Products. Note that the names are comma separate in the configuration file.

<CommonMapping>

In CommonMapping, we can specify various settings that are common to all or several of the mappings that will be used. In this node, we can set <CustomFields> and <AllowedUsers> that will be used in all table unless other wise specified. These fields are discussed in more details in the <Mapping> section. In the <CommonMapping> ,in the example, we have:

The excludedItems attribute will list all the tables listed in <ItemType> that will not use the settings that are specified in <CommonMapping>. In the sample, the table “Customers” was excluded. Here is a listing of the entire <CommonMapping> node:

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

The <Mapping> node tells the crawler how to access the information in the table, how it will be stored once it has been indexed, and who can have access to it. It has an attribute called type. Here we need to put the name of the table that we will map. For this example, we will examine the mapping for the table Orders. This is the second of the three tables we will index and is the one that contains the most complex configuration.

<Mapping type="Orders">

    <Accessor type="query">

      SELECT  Shippers.CompanyName AS ShipperName, Orders.OrderID AS ID, Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,      Customers.CompanyName, Employees.LastName, Employees.FirstName

      FROM         Orders INNER JOIN

      Shippers ON Orders.ShipVia = Shippers.ShipperID INNER JOIN

      Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

      Employees ON Orders.EmployeeID = Employees.EmployeeID

    </Accessor>

    <Fields>

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

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

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

      <Title>%[ID]</Title>

      <Body>OrderDate: %[OrderDate] \n RequiredDate: %[RequiredDate] \n ShippedDate: %[ShippedDate] \nShipped via: %[ShipperName]

      </Body>

      <CustomFields>

        <CustomField name="Type">Order</CustomField>

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

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

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

        <CustomField name="Shipper">%[ShipperName]</CustomField>

        <CustomField name="sysAuthor">%[FirstName] %[LastName]</CustomField>

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

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

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

<Accessor>

This is where we specify how to access the table. It has one attribute called type that can either be equal to object, or query. In our example, the <Accessor> looks like this:

We are using the query type for our example. This means that the information stored in the database will be accessed using an SQL query and can be actually stored in multiple tables and views. The information in the <Accessor> node is the query string we are using to extract the data in the table. This query string in this example is:

SELECT Shippers.CompanyName AS ShipperName, Orders.OrderID AS ID, Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,      Customers.CompanyName, Employees.LastName, Employees.FirstName

FROM   Orders INNER JOIN

Shippers ON Orders.ShipVia = Shippers.ShipperID INNER JOIN

Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

Employees ON Orders.EmployeeID = Employees.EmployeeID

<Fields>

This is where we configure how the ODBC crawler will index the content that is read from the database.

<Uri>

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

URI is the address where a user is redirected when clicking on a search result title. It is also used by the index to uniquely identify documents.

Note: if the database records you index don't have a Web address where they can be viewed by search users, you still have to provide one. You can generate one based on any dummy address, as long as it is unique. You should create one formed with some primary key from your database.

<ClickableUri>

Specify how to access the data from the search results page.

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

In our example ,it points to http://www.coveo.com, but in a production environment it may point to a web page that will show the contents of the result that you want.

<FileName>

This is the name of the file to create that will hold the SQL query information so it can be indexed.

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

<Title>

This is the name that will be displayed on the search results page to represent this entry in the table.

<Title>%[ID]</Title>

Here again we are using %[ID] to give each entry its own name based on the ID field in the Orders table

<Body>

Here is a way that you can represent the content of the entry. It can be a mixture of static text and dynamic content taken from the table.

<Body>OrderDate: %[OrderDate] \n RequiredDate: %[RequiredDate] \n ShippedDate: %[ShippedDate] \nShipped via: %[ShipperName]

</Body>

In our example, we have:

OrderDate: %[OrderDate] \n RequiredDate: %[RequiredDate] \n ShippedDate: %[ShippedDate] \n Shipped via: %[ShipperName]

%[OrderDate], %[RequiredDate], %[ShippedDate], %[ShipperName] fields are all dynamic content types that will change with each entry taken from the table. The \n means that what comes after it is printed on a new line.

<CustomFields>

This is where we link the entries taken from the table to custom fields in Coveo Enterprise Search (CES).

<CustomField>

Each <CustomField> node represents one custom field in CES and the data that will be contained therein. In our example, we have several <CustomField> nodes. From the sample:

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

The name attribute is the name of the custom field that CES will use to access this data. Next, we have the %[OrderDate] which is used to store the data taken from that field.

<AllowedUsers>

Since the ODBC crawler doesn’t index the securities of the database automatically, we can use this field to create a way to protect the data that is taken from the database.

<AllowedUser>

In this node, we can give rights to a user to access this table. Let’s take a look at the AllowedUser in our example more closely.

There are two attributes used in AllowedUser. The first one is type. This specifies the type of user that we want to give rights to. It can be Windows, CustomGroup, or CustomUser. In the example, it is CustomGroup. Next, we have the allowed attribute. It can be set to either true or false. In the example it is true so that means access is allowed. If it was set to false, then access would have been denied.

<Name>

In the tags <AllowedUser>, you can specify the name of a user or a group you want to give permissions to. In our example, we have:

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

We have used the First Name and Last Name values that are stored in the table. This type of setup is only useful if these fields correspond with users that have access to the database. A better practice would be to have a table or tables that list the users having permission to access the information and to use the values taken from there. Another alternative is to give the name of a Windows group that contains all the users that should have access. Finally, you could put “Everyone” there and then everyone could have access to the information that was taken from the table.

<Server>

Specify the domain name for the group or user. In the example, there was no server name specified. In a real situation this would most likely be a rare occurrence.

FAQ

Q: Where do I put my configuration file?

A: The configuration file may be placed anywhere that makes sense to you since you will be specifying its path when you configure your source.

Q: Why do I get an “Invalid File Name” error in the console when crawling with my ODBC enabled source?

A: This is probably due to the name you are passing to the <FileName> element in the configuration file that is used to create a file displaying the data extracted from the table. That data most likely contains an invalid character for a file name. The invalid characters are ( \ / : * ? “” < > | ).

Example:

…

…

<FileName>%[CompanyName] (%[CustomerID]).txt</FileName>

…

</Fields>

…

</Mapping>

If CompanyName variable happens to contain North/South, the file name that the crawler tries to create would be invalid because of the / character.

Q: Why is it that one (or several) of my tables are not indexed?

A: This may be because a field in the table you are indexing takes its data from a field in another table. So the <Accessor> node of the configuration file has a query that accesses the data stored in the other table. If you don’t have permission to access data stored in that other table, the data of the original table will not be indexed.

Example:

SELECT Orders.*

FROM Orders

INNER JOIN Customers On Orders.CustomerID = Customers.CustomerID

WHERE Country = 'France'

If you don’t have permission to access Customers, the ODBC crawler won’t index Orders because both tables are joined with CustomerID field.

Q: Does it handle BLOB fields?

A: Yes. BLOB fields can be indexed and their content converted using the appropriate converter. In the configuration file, map the name of your BLOB field to the <Body> node of <Fields>.

Q: Can the OCR Module handle BLOB fields?

A: Yes. In addition to mapping the BLOB field name to <Body>, you should specify a file name having an image extension (see the extension list of the Images Document Type) to the <Filename> node. This way, the BLOB content will be converted according to the Images Document Type converter selection, which would have been previously configured to "OCR" converter (follow the OCR Module installation instructions carefully).

Q: Unhandled exception (class _STL length_error) in IndexerManager IndexingThread - basic_string?

A: If a database record field contains a value that is an invalid URI and is assigned to the PrintableURI property, the service will crash when starting the source. Workaround: make sure all PrintableURI will be valid URI or do not set PrintableURI.

Appendix A - Northwind (simple)

Microsoft SQL Server

Configuration File

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

<ODBC>

  <Configuration>

    <ConnectionString>Driver={SQL Server};Server=jpdery;Database=NorthWind;UID=test;PWD=test</ConnectionString>

    <ItemType>Customers</ItemType>

  </Configuration>

  <Mapping type="Customers">

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

    <Fields>

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

      <Title>%[CompanyName]</Title>

      <Body>%[CompanyName] \n %[ContactTitle] %[ContactName] \n%[Address]</Body>

      <ContentType>text/html</ContentType>

    </Fields>

  </Mapping>

</ODBC>

View

create view "CES_Customers" AS

select

'http://www.coveo.com/Customers/details.aspx?Id=' + CustomerID AS Uri,

CompanyName AS Title,

CompanyName + CHAR(10) + ContactTitle + ' ' + ContactName + CHAR(10) + Address AS Body

from customers

Microsoft Access

Configuration File

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

<ODBC>

  <Configuration>

    <ConnectionString>Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;Uid=Admin;Pwd=</ConnectionString>

    <ItemType>Customers</ItemType>

  </Configuration>

  <Mapping type="Customers">

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

    <Fields>

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

      <Title>%[CompanyName]</Title>

      <Body>%[CompanyName] \n %[ContactTitle] %[ContactName] \n%[Address]</Body>

      <ContentType>text/html</ContentType>

    </Fields>

  </Mapping>

</ODBC>

Query (View)

SELECT

'http://www.coveo.com/Customers/details.aspx?Id='+Customers.CustomerID AS Uri,

CompanyName & "(" & CustomerID & ")" AS Title,

CompanyName & chr(10) & ContactTitle & " " & ContactName & " " & Address AS Body

FROM Customers;

Appendix B - Northwind (advanced)

Microsoft SQL Server

Configuration File

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

<ODBC>

  <Configuration>

    <ConnectionString>Driver={SQL Server};Server=jpdery;Database=NorthWind;UID=test;PWD=test</ConnectionString>

    <ItemType>Customers, Orders, Products</ItemType>

  </Configuration>

  <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 type="Customers">

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

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[CompanyName] (%[CustomerID])</Title>

      <Body>%[CompanyName] \n %[ContactTitle] %[ContactName] %[Address]</Body>

      <CustomFields>

        <CustomField name="Type">Customer</CustomField>

        <CustomField name="ID">%[CustomerID]</CustomField>

        <CustomField name="Company">%[CompanyName]</CustomField>

        <CustomField name="Contact">%[ContactName]</CustomField>

        <CustomField name="Title">%[ContactTitle]</CustomField>

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

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

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

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

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

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

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

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

        <Name>%[CompanyName]</Name>

        <Server></Server>

      </AllowedUser>

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

        <Name>everyone</Name>

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

  <Mapping type="Orders">

    <Accessor type="query">

      SELECT      Shippers.CompanyName AS ShipperName, Orders.OrderID AS ID, Orders.CustomerID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate,      Customers.CompanyName, Employees.LastName, Employees.FirstName

      FROM        Orders INNER JOIN

      Shippers ON Orders.ShipVia = Shippers.ShipperID INNER JOIN

      Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN

      Employees ON Orders.EmployeeID = Employees.EmployeeID

    </Accessor>

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[ID]</Title>

      <Body>OrderDate: %[OrderDate] \n RequiredDate: %[RequiredDate] \n ShippedDate: %[ShippedDate] \nShipped via: %[ShipperName]</Body>

      <CustomFields>

        <CustomField name="Type">Order</CustomField>

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

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

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

        <CustomField name="Shipper">%[ShipperName]</CustomField>

        <CustomField name="sysAuthor">%[FirstName] %[LastName]</CustomField>

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

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

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

  <Mapping type="Products">

    <Accessor type="query">

      SELECT      Products.ProductID as ID, Products.ProductName, Products.Discontinued, Products.UnitPrice, Products.QuantityPerUnit, Categories.CategoryName,

        As SupplierName

      FROM         Categories INNER JOIN

      Products ON Categories.CategoryID = Products.CategoryID INNER JOIN

      Suppliers ON Products.SupplierID = Suppliers.SupplierID

    </Accessor>

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[ProductName] (%[ID])</Title>

      <Body>Name: %[ProductName] \n Category: %[CategoryName] \n Supplier: %[SupplierName]</Body>

      <CustomFields>

        <CustomField name="Type">Product</CustomField>

        <CustomField name="Product">%[ProductName]</CustomField>

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

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

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

        <CustomField name="Category">%[CategoryName]</CustomField>

        <CustomField name="Supplier">%[SupplierName]</CustomField>

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

        <Name>%[SupplierName]</Name>

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

</ODBC>

Views

CES_Customers

CREATE view "CES_Customers" AS

select

'http://www.coveo.com/Customers/details.aspx?Id=' + CustomerID AS Uri,

CompanyName + ' (' + CustomerID + ')' AS Title,

CompanyName + CHAR(10) + ContactTitle + ' ' + ContactName + CHAR(10) + Address AS Body,

'Customer' AS Type,

CustomerID AS ID,

CompanyName AS Company,

ContactName AS Contact,

ContactTitle,

Address,

City,

Region,

PostalCode,

Country,

Phone,

Fax

from customers

CES_Orders

CREATE view "CES_Orders" AS

SELECT

'http://www.coveo.com/Orders/details.aspx?Id=' + convert(varchar, Orders.OrderID) AS Uri,

'http://www.coveo.com' AS ClickableUri,

Orders.OrderID AS Title,

'OrderDate: ' + convert(varchar, OrderDate) + CHAR(10) + 'RequiredDate: ' + convert(varchar, RequiredDate) + CHAR(10) + 'ShippedDate: ' + convert(varchar, ShippedDate) + CHAR(10) + 'Shipped via: ' + Shippers.CompanyName AS Body,

'Order' AS Type,

OrderDate,

RequiredDate,

ShippedDate,

Shippers.CompanyName AS Shipper,

FirstName + ' ' + LastName AS sysAuthor

FROM

Orders

INNER JOIN Shippers ON Orders.ShipVia = Shippers.ShipperID

INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID

INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID

CES_Products

CREATE view "CES_Products" AS

SELECT

'http://www.coveo.com/Products/details.aspx?Id=' + convert(varchar, Products.ProductID) AS Uri,

'http://www.coveo.com' AS ClickableUri,

ProductName + ' (' + convert(varchar, Products.ProductID )+ ')' AS Title,

'Name: ' + ProductName + CHAR(10) + 'Category: ' + CategoryName + CHAR(10) + 'Supplier: ' + Suppliers.CompanyName AS Body,

'Product' AS Type,

ProductName AS Product,

Discontinued,

UnitPrice,

QuantityPerUnit,

CategoryName AS Category,

Suppliers.CompanyName AS Supplier

FROM

Microsoft Access

Configuration File

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

<ODBC>

  <Configuration>

    <ConnectionString>Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;Uid=Admin;Pwd=</ConnectionString>

    <ItemType>Customers, Orders, Products</ItemType>

  </Configuration>

  <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 type="Customers">

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

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[CompanyName] (%[CustomerID])</Title>

      <Body>%[CompanyName] \n %[ContactTitle] %[ContactName] %[Address]</Body>

      <CustomFields>

        <CustomField name="Type">Customer</CustomField>

        <CustomField name="ID">%[CustomerID]</CustomField>

        <CustomField name="Company">%[CompanyName]</CustomField>

        <CustomField name="Contact">%[ContactName]</CustomField>

        <CustomField name="Title">%[ContactTitle]</CustomField>

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

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

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

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

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

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

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

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

        <Name>%[CompanyName]</Name>

        <Server></Server>

      </AllowedUser>

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

        <Name>everyone</Name>

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

  <Mapping type="Orders">

    <Accessor type="query">

      SELECT [Shippers.CompanyName] AS [ShipperName], [Orders.OrderID] AS [ID], Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Customers.CompanyName,

      Employees.LastName, Employees.FirstName

      FROM Shippers INNER JOIN

        (Employees INNER JOIN

          (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)

        ON Employees.EmployeeID = Orders.EmployeeID)

      ON Shippers.ShipperID = Orders.ShipVia

    </Accessor>

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[ID]</Title>

      <Body>OrderDate: %[OrderDate] \n RequiredDate: %[RequiredDate] \n ShippedDate: %[ShippedDate] \nShipped via: %[ShipperName]</Body>

      <CustomFields>

        <CustomField name="Type">Order</CustomField>

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

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

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

        <CustomField name="Shipper">%[ShipperName]</CustomField>

        <CustomField name="sysAuthor">%[FirstName] %[LastName]</CustomField>

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

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

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

  <Mapping type="Products">

    <Accessor type="query">

      SELECT      Products.ProductID as ID, Products.ProductName, Products.Discontinued, Products.UnitPrice, Products.QuantityPerUnit, Categories.CategoryName,

      Suppliers.CompanyName As SupplierName

      FROM         Categories INNER JOIN

        (Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID)

      ON Categories.CategoryID = Products.CategoryID

    </Accessor>

    <Fields>

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

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

      <ContentType>text/html</ContentType>

      <Title>%[ProductName] (%[ID])</Title>

      <Body>Name: %[ProductName] \n Category: %[CategoryName] \n Supplier: %[SupplierName]</Body>

      <CustomFields>

        <CustomField name="Type">Product</CustomField>

        <CustomField name="Product">%[ProductName]</CustomField>

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

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

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

        <CustomField name="Category">%[CategoryName]</CustomField>

        <CustomField name="Supplier">%[SupplierName]</CustomField>

      </CustomFields>

    </Fields>

    <AllowedUsers>

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

        <Name>%[SupplierName]</Name>

        <Server></Server>

      </AllowedUser>

    </AllowedUsers>

  </Mapping>

</ODBC>

Queries (Views)

CES_Customer

SELECT

'http://www.coveo.com/Customers/details.aspx?Id='+Customers.CustomerID AS Uri,

'http://www.coveo.com' AS ClickableUri,

CompanyName & "(" & CustomerID & ")" AS Title,

CompanyName & chr(10) & ContactTitle & " " & ContactName & " " & Address AS Body,

'Customer' AS Type, CustomerID AS ID,

FROM Customers;

CES_Orders

SELECT

'http://www.coveo.com/Orders/details.aspx?Id=' & Orders.OrderID AS Uri,

'http://www.coveo.com' AS ClickableUri,

Orders.OrderID AS Title,

'OrderDate: ' & OrderDate & Chr(10) & 'RequiredDate: ' & RequiredDate & Chr(10) & 'ShippedDate: ' & ShippedDate & Chr(10) & 'Shipped via: ' & Shippers.CompanyName AS Body,

'Order' AS Type,

OrderDate, RequiredDate, ShippedDate, Shippers.CompanyName AS Shipper,

FirstName & ' ' & LastName AS sysAuthor

FROM Shippers INNER JOIN

(Employees INNER JOIN

(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)

ON Employees.EmployeeID = Orders.EmployeeID)

ON Shippers.ShipperID = Orders.ShipVia;

CES_Products

SELECT

'http://www.coveo.com/Products/details.aspx?Id=' & Products.ProductID AS Uri,

'http://www.coveo.com' AS ClickableUri,

ProductName & ' (' & Products.ProductID & ')' AS Title,

'Name: ' & ProductName & Chr(10) & 'Category: ' & CategoryName & Chr(10) & 'Supplier: ' & Suppliers.CompanyName AS Body,

'Product' AS Type,

ProductName AS Product,

Discontinued,

UnitPrice,

QuantityPerUnit,

CategoryName AS Category,

Suppliers.CompanyName AS Supplier

FROM

Categories INNER JOIN

(Products INNER JOIN Suppliers ON Products.SupplierID = Suppliers.SupplierID)

ON Categories.CategoryID = Products.CategoryID