Help Center

Menu

Implementer training - Indexing SQL database content

Funnelback has the ability to connect to any SQL DBMS (with a valid JDBC driver) and index the output from running a valid SQL query within that database.

Each row from the result of the query is indexed as a separate document within Funnelback with fields mapped to Funnelback metadata classes.

Funnelback only supports indexing of PostgreSQL database by default. Connection to other database types requires a server administrator to add the appropriate database drivers.

Limitations and gotchas

  • Funnelback query language does not support table joins or grouping at query time.

  • Best practice is to have the database owner create a view containing all the relevant fields in a de-normalised form so that Funnelback can run a select * from view SQL query against the table view. This simplifies the Funnelback configuration and also makes it clearer within the DBMS what will be available within Funnelback.

  • Each row in the resulting table is indexed as an item or document in Funnelback.

  • Records are downloaded and stored internally as XML.

  • Fields within the indexed view are mapped to metadata classes within Funnelback.

  • There is no built-in DLS support for database record access

Data source types - SQL databases

Tutorial: Index an SQL database

This tutorial covers the steps required to index an SQL database. For this example we will index an SQLite database using the SQLite JDBC driver that is included with Funnelback.

Further information about the Chinook database that is used in this exercise (including schema information): http://www.sqlitetutorial.net/sqlite-sample-database/

Before we start the tutorial we need to upload a database file that will be used for the training example.

  1. Download a copy of the Chinook database from the following location: https://docs.squiz.net/training-resources/chinook.db. Ensure the saved file is named chinook.db.

  2. Log in to the search dashboard where you are doing your training.

    See: Training - search dashboard access information if you’re not sure how to access the training. Ignore this step if you’re treating this as a non-interactive tutorial.

  3. Create a new search package named Database example.

  4. Create a new results page named Chinook search.

  5. On the Chinook search results page click the edit web resources item from the templates panel.

  6. Click the upload files button then upload the chinook.db file you saved in step 1, above. After a successful upload the file should be shown in the directly listing with a file size of 864kB.

The following steps take you through the process of creating a database data source.

  1. Return to the Database example search package you created above and create a new data source with the following attributes:

    • Data source type: database

    • Data source name: `Chinook database `

  2. Configure the data source. Select the edit data source configuration option from the settings panel and add the following to the data source configuration:

    • db.jdbc_class: set this to the appropriate JDBC driver class string. This is an SQLite database so set this to: org.sqlite.JDBC

    • db.jdbc_url: Set this to the JDBC connection URL for the database. For SQLite this is a path to where the SQLite database is on the local filesystem. jdbc:sqlite:/opt/funnelback/conf/default~sp-database-example/chinook-search/chinook.db

    • db.full_sql_query: select * from albums

    • db.primary_id_column: Albumid

      exercise index an sql database 01

  3. Update the data source by selecting Update this data source from the update panel on the data source’s management screen.

  4. Change to the Chinook search results page and run a query for !showall and confirm that results are being returned.

    exercise index an sql database 02

  5. View the cached copy of For Those About To Rock We Salute You and observe the XML record that is stored by Funnelback

    exercise index an sql database 03

The cached XML record highlights the requirement for Funnelback to be provided with denormalized data - the XML record includes only an ID for the album artist rather than the artist’s name. For useful search results to be printed the record needs to expand the artist IDs. For a traditional database query this join would be performed at query time.

With Funnelback you would need to update the query that Funnelback runs to perform any table joins, or ideally have the database owner provide you with a view tailored for Funnelback to query.