Database data sources

Funnelback must have direct access to the SQL database when using a database data source to index database content. In most situations this will not be possible due to organizational security requirements that will generally place the database within the organizations internal network, which is unreachable by Funnelback.

Because access to databases is often restricted you may need to use an alternative approach to indexing the database content that does not rely on a direct connection to the database.

Funnelback’s database connector indexes the single table of results returned by an SQL query as separate XML records.

Because of this, the table that is returned by the query must be fully denormalized (Funnelback can’t resolve any ID references to other database records and has no concept of the relational database).

The best practice method of accessing content from an SQL database is to set up a Funnelback-specific database view containing all the (denormalized) fields that either should be returned as fields in the search results, or be searchable by Funnelback.

Using a view simplifies the SQL query that Funnelback uses to retrieve the data (select * from VIEWNAME), and increases the visibility to your database administrator that Funnelback is indexing content from the database.

For databases that have multiple types of content it is often necessary to set up several Funnelback database data sources that index each of these types separately.

Where possible data transformations such as combining fields into a new field (like creating a name field from firstname and lastname or creating a sort field) and removing/renaming values (like _NULL_) should be performed in the query that sets up the view.

Database data sources are created by following the data source creation steps and selecting database from the list of data source types.

The database data source is defined by configuring the following properties:

Test the connection settings using the database connection utils API.

This can be accessed by selecting API UI from the system (cog) menu, then scrolling down to the database connection utils.

There are two API calls provided to assist with checking your database connection.

After a successful update has run, configure metadata mappings for the database fields. The update of the database data source downloads each row from the SQL query and converts it to an XML record.

The fields that were detected in the SQL results should be listed amongst the XML sources. Map the relevant fields to metadata classes. Relevant fields include:

Rebuild the index by running an advanced update to rebuild the live index.

After the reindex is complete metadata should be available for display in the search results.

Search results can then be configured. This involves:

By default, the URL used in database data sources is a system-assigned URL. If the default system-assigned URL is not modified (for example by filters) the modern UI will use the cache link as the live URL. This will result in the XML of the record being shown to the user when a result is clicked. You can use XSLT on the cache controller to style the response.

Database records can be modified (locally) prior to indexing using the filter framework.