Add/Edit a Source - Panel
- Amazon S3 Source
- Box (Personal) Source
- Box Business V2 Source
- Box Business Legacy Source
- Confluence Cloud Source
- Confluence Legacy Source
- Confluence Self-Hosted Source
- Database Source
- Dropbox (Personal) Source
- Dropbox Business Source
- Exchange Enterprise Source
- Exchange Online (Personal) Source
- File System Source
- Generic REST API Source
- Gmail for Work Source
- Gmail (Personal) Source
- Google Drive (Personal) Source
- Google Drive for Work Source
- Jira Software Cloud Source
- Jira Software Self-Hosted Source
- Jive Cloud Source
- Jive Server Source
- Lithium Source
- Microsoft Dynamics Source
- OneDrive for Business Source
- Push Source
- RSS Source
- Salesforce Source
- SharePoint Online Source
- SharePoint Online Legacy Source
- SharePoint Server Source
- Sitecore Source
- Sitemap Source
- Twitter Source
- Web Source
- YouTube Source
- Zendesk Source
Add/Edit Database Source
A Database source allows you to retrieve and make searchable the content of a local database via the Coveo On-Premises Crawling Module (see Coveo On-Premises Crawling Module).
Your company developer created a custom database to manage the parts used in your facilities, their location in your warehouse, and purchase orders. You decide to index data regarding purchase orders only so that your buyers can find this content via your Coveo Cloud V2 powered search page.
When you have the required privileges, you can add the content of a local database to your Coveo Cloud V2 organization. Your Database source can be shared, private, or secured (see Source Permission Types).
If you want to create a secured source, contact the Coveo Support team for assistance.
Source Features Summary
See [Enabling Refresh on a Database Source](#enabling-refresh-on-a-database-source)
|Permission types||Secured||Contact the Coveo Support team|
Add or Edit a Database Source
You can configure a Database source though the Coveo Cloud V2 administration console if you use a 64-bit driver to connect to your database. If you use a 32-bit driver, you must use the Coveo Cloud V2 Platform API and a JSON source configuration (see Creating a Crawling Module Source Using the Source API).
Ensure the Coveo On-Premises Crawling Module is installed on a server that has access to the database of which you want to retrieve the content (see Crawling Module Deployment Overview).
Go to the Add/Edit a Database Source panel:
To add a source, in the main menu, under Content, select Sources > Add Source button > Database.
To edit a source, in the main menu, under Content, select Sources > source row > Edit in the Action bar.
In the Add/Edit a Database Source panel, in the Configuration tab, if not already done, click Download Crawling Module to install the Coveo On-Premises Crawling Module on a server that has access to the database of which you want to retrieve the content.
Enter appropriate values for available parameters:
A descriptive name for your source under 255 characters (not already in use for another source in this organization).
You cannot change the source name once it is created.
The parameters to use to connect to your database.
Since connection strings are not encrypted, they should never contain credentials in plain-text. You can hide the password and user ID in the connection string by respectively introducing the
@pwdtokens. The Database source internally replaces the tokens with the information provided in the Authentication section (see Authentication).
You must provide either both tokens or none at all. If you do not provide tokens, but add source credentials, the behavior will be the same as before, meaning that the credentials will be used to impersonate the process running the queries.
For a basic connection string:
Data Source=mydatabase.mycompany.com;Initial Catalog=MyDatabase;User Id=companyUser;Password=MyPassword
Hiding password and user ID using tokens:
Data Source=mydatabase.mycompany.com;Initial Catalog=MyDatabase;User Id=@uid;Password=@pwd
The connection string syntax differs from one database type to another (see Connection Strings).
The table or view object names (
<Mapping type="name">) that are defined in the database configuration and that you want to index (see XML Configuration).
Select the software driver that provides access to your database.
Select who should be able to access the database content through a Coveo search (see Source Permission Types). Available options are:
Private - Only you, the source creator
Shared - Everyone
Secured - Only users authenticated in the search interface will see the source items for which they have read permission.
In the Authentication section, in the Username box, enter the username of a dedicated administrator account that has access to all the content you wish to include. In the Password box, enter the corresponding password.
In the Database Configuration section, in the XML configuration box, enter a XML-formatted configuration instructing Coveo Cloud V2 to retrieve and copy the data from record fields to Coveo default and standard source fields.
Ensure that your configuration only contain read-only queries to not make any changes to your database.
The source indexes items through a query against a database. Subqueries can run on every item to complete the information with more complex queries (see Complement Information Retrieval Using Subqueries).
If you want to index certain database items depending on a characteristic that may vary, your configuration must include instructions as to which items are no longer relevant and can be deleted from the index.
MESSAGEitems with a
message_idthat is less than
100. The configuration is the following for your MS-SQL database:
<?xml version="1.0" encoding="utf-8" ?> <ODBC> <CommonMapping> <AllowedUsers> <AllowedUser type="Windows" allowed="true"> <Name>everyone</Name> <Server></Server> </AllowedUser> </AllowedUsers> </CommonMapping> <Mapping type="MESSAGE"> <Accessor type="query" IncrementalRefreshFieldName="date"> SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY mid) AS mid, -- message.mid, message.sender, message.date, message.message_id, message.subject, Cast(@LastRefresh as nvarchar(4000)) as FunkyDate, message.body, message.folder FROM message ) AS T WHERE mid <= 100 </Accessor> <Fields> <Uri>http://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri> <ClickableUri>http://www.coveo.com</ClickableUri> <FileName>Message_%[mid].txt</FileName> <Title>Message_%[mid] - %[FunkyDate]</Title> <ModifiedDate>%[date]</ModifiedDate> <Body>%[body]</Body> <CustomFields> <CustomField name="sysAuthor">%[sender]</CustomField> </CustomFields> </Fields> <AllowedUsers> <AllowedUser type="CustomGroup" allowed="true"> <Name>everyone</Name> <Server></Server> </AllowedUser> </AllowedUsers> </Mapping> </ODBC>
However, if, among the items with a
100, you want to index items of a certain age only, you need to add an instruction for Coveo Cloud V2 to delete the items that do not satisfy your age condition. Since the age of an item changes constantly, each of the indexed items will eventually become irrelevant and will need to be removed from your index.
You want items that are less than a month old only to be searchable. With the following instruction inserted before
<Fields>in your XML configuration, every time your Database source is refreshed, items that no longer meet your age criterion are deleted from the index (see Refresh, Rescan, and Rebuild):
<AccessorForItemsToDelete type="query"> SELECT message.mid FROM message WHERE message.date < DATEADD(month, -1, GETDATE()) AND message.mid < 100 </AccessorForItemsToDelete>
In the Configuration Access tab, determine whether each group and API key can view or edit the source configuration. In the Access Level column, select View or Edit for each available group. On the left-hand side of the tab, if available, click Groups or API Keys to switch lists.
If you remove the Edit access level from all the groups of which you are a member, you will not be able to edit the source again after saving. Only administrators and members of other groups that have Edit access on this resource will be able to do so. To keep your ability to edit this resource, you must grant the Edit access level to at least one of your groups.
Optionally, consider editing or adding mappings between Database item metadata and fields in your Coveo Cloud organization (see Edit the Mappings of a Source: [SourceName]).
You can only manage mapping rules once you build the source (see Add/Edit a Source - Panel).
Finish adding/editing your source:
Click Add Source/Save when you want to save your source configuration changes without starting a build/rebuild, such as when you know you want to do other changes soon.
Click Add and Build Source/Save and Rebuild Source when you are done editing the source and want to make changes effective.
Back in the Coveo Cloud administration console Sources page, you can review the progress of your Database source addition or modification (see Sources - Page).
Enabling Refresh on a Database Source
Refresh keeps items up-to-date by scanning repositories and re-indexing modified items 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.
The refresh takes into account deleted items when the
AccessorForItemsToDelete accessor is configured in the XML configuration (see XML Configuration). Otherwise, a source rescan 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.
The following example should work with common database engines such as Microsoft SQL Server 2012, PostgreSQL, and MySQL. You must replace
@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 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)).
Complement Information Retrieval Using Subqueries
The Database source acquires information about each indexed item through a query performed against a database. For each query, it is possible to associate one or more subqueries to be executed and used to complement information.
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 is necessary to specify an
Accessor representing the SQL query to execute.
To specify subqueries, you must set the type of the
Accessor to query.
Accessor definition, add an
AccessorSubQueries node with all subqueries:
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 could not be found:
Unable to index document : There is a formatting error in a sub query. Cannot find master key %[key].
<AccessorSubQueries> <AccessorSubQuery name="FirstNameLastName" separator=";" behaviorOnMultiRows="join" allowDuplicates = "false"> SELECT firstName, lastName FROM employeelist WHERE Email_id = %[sender] </AccessorSubQuery> </AccessorSubQueries>
Subquery name referred to in the
Fieldssection of the mapping.
Separator used when concatenating multiple rows.
Action to take when a subquery returns more than one row. The only supported behavior is join, which concatenates values with the provided separator.
This attribute is mainly used when your subquery returns multiple rows. If set to
false, duplicates in the results are ignored in the concatenation of the results. If set to
true, duplicates are present.
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 (e.g.,
''). This escaping mechanism should work in most cases. 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.
For the MySQL database, the single quote escaping sequence is
\'. In this case, in the
AccessorSubQuerytag, include the
singleQuoteEscapeSequenceattribute as follows:
<AccessorSubQuery name="FirstNameLastName" separator=";" behaviorOnMultiRows="join" 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
<Fields> section of the mapping configuration is used to specify the metadata to use for indexing.
Refer to the following example for a typical
<Fields> section of a mapping configuration:
<Fields> <Uri>http://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri> <ClickableUri>http://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 is 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
Refer to 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=";" behaviorOnMultiRows="join"> SELECT firstName, lastName FROM employeelist WHERE Email_id = %[sender] </AccessorSubQuery> </AccessorSubQueries> <Fields> <Uri>http://www.coveo.com/Emails/details.aspx?Id=%[mid]</Uri> <ClickableUri>http://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>