Database Collection

SearchBlox allows you to easily index records from the database table, and results are customizable with Faceted Search.

Creating a Database Collection

You can create a Database Collection with the following steps:

• After logging in to the Admin Console, click Add Collection button. The Add Collection screen will now be displayed.
• Enter a unique name for your collection (for example, Databasecollection).
• Click on the DB Collection icon.
• Click Add to create the collection.

Supported Databases

SearchBlox databases for database collection indexing include:

• MySQL
• SQL Server
• Oracle
• PostgreSQL
• Other Applications supported through Database Collection

Database Collection Settings

• You can configure your database for indexing by using the applicable SQL query, and the database related settings in the Database collection settings.
• To access the settings for the collection, click on the collection name in the collections list.

Database Collection Queries

• To fetch data from the table or application, input the required query in the SQL field under the Queries tab.
• To find the applicable query format and prerequisites for your collection, read: SQL Query for DB Collection.

Database Settings

• Under the Settings sub-tab you will find settings for the database and tunable parameters for the search functionality.
• Database setting values must be set explicitly for Database collections.
• The mandatory settings for Database collection for databases like MySQL, Oracle, etc., are
  • Database Type
  • Host IP/Name
  • Host Port No
  • Database Name
  • Database Username
  • Database Password
• The mandatory settings for Database collection for External DB Connectors are
  • Database Type
  • Database URL String
• SearchBlox comes pre-configured with parameters when a new collection is created, but you can also select your preferred setting values for database collections.
• The following table has the list of settings available in Database collection

After saving the query and settings, test the connection. The connection has to be successful for
indexing to take place.

Indexing and Other Operations

The following operations can be performed in Database collections.

• Indexer activity is controlled from the Index sub-tab in the collection. The current status of an indexer for a particular collection is indicated.
• Indexing operation starts the indexer for the database collection.
• On reindexing that is, clicking on index again after the initial index operation, all crawled documents will be reindexed. If documents have been deleted from the database since the first index operation, they will be deleted from the index. New documents will also be indexed.
• Also, indexing is controlled from the Index sub-tab for a collection or through API. The current status of a collection is always indicated on the Collection Dashboard and the Index page.
• Index operation can also be initiated from the Collection Dashboard.
• Scheduling can be performed only from the Index sub-tab.

Schedule Frequency

Schedule Frequency supported in SearchBlox is as follows:

• Once
• Every Minute
• Hourly
• Daily
• Every 48 Hours
• Every 96 Hours
• Weekly
• Monthly

👍

Best Practices

• Please check the type of application/database that is to be indexed and provide the database settings accordingly.
• If you do not have any unique id to map in SQL SELECT query, create a view with a unique id and use the same in the SELECT query to fetch the data.
• Do not schedule the same time for index and clear operations.
• If you have multiple collections, always schedule the activity to prevent more than 2-3 collections indexing at the same time.

Viewing Search Results

• View the search results by searching for the records here: http://localhost:8080/searchblox/plugin/index.html.
• After clicking the search results, the data will appear in a grid format.

• To add customized facets, go to the index.html page. The facet can be any value in the table, which can be used to filter the results.
• Results can also be viewed in JSON format as shown here, by clicking the database search results in a regular search: http://localhost:8080/searchblox/search.jsp.

[{"uid":"1","lname":"Jones","university":"VCU","keywords":"joe","no":"1","content":"[1, Smith, Jones, 1, Georgetown, Biotech, D05, VCU]","title":"Smith","deptid":"D05","address":"1, Georgetown","dept":"Biotech","fname":"Smith"}]

If you want search results in the grid view, use SearchBlox's Database plugin. Learn more about Database Search