Click or drag to resize

Full Text Search

This topic contains the following sections:

This document details the requirements and procedure to activate the Full Text Search on Packflow.

Summary

This feature enables Packflow queries to filter on the content of files hosted in FileHolder fields.

FTS 1

When activated, Packflow indexes the content of files (if the type is handled) in an external database. Filters targeting a FileHolder field will then be able to rely on the indexed content.

Note Note
The Index database has to be installed in the same SQL instance as the main database.
Requirements

Note Note
If you installed Packflow on a SQL instance with the Full-Text feature (of SQL Server) enabled, the Index database should already exist and this section can be skipped.
This feature uses the Full-Text indexes of SQL Server. Those are not always available by default. ("Full-text search is an optional component of the SQL Server Database Engine.")

SQL

If you use the Express version of SQL Server, you have to download another version labelled "Advanced" or "ADV". These versions exist for SQL Server 2008 (R2) and 2012. Be sure to select the "ADV" file.

Install a new SQL Server instance using the setup. Do not forget to check the "Full-Text" box on the Features screen.

Database migration

If the Packflow database was installed on another SQL Server instance without the Full-Text feature, you will have to move it to the new instance.

  • Connect to the old instance using SQL Server Management Studio.
  • Right-click on the database > Tasks > Back Up...
  • Verify the destination and execute the backup.
  • Connect to the new instance.
  • Right-click on the "Databases" node > Restore Database...
  • In the "Source for restore" section, select the option "From device" > "..." > Add > Select the folder or file of your backup
  • Enter the name of you backup file if the box is still blank.
  • Click OK (twice).
  • A new line appeared in the grid, select the box in the "Restore" column.
  • In the "Destination for restore" section select your database in the "To database" list box. It should be the last choice.
  • In the "Options" tab, check the paths specified in the grid of the "Restore options" section. These paths should target the folder of the NEW instance, not the old instance.
  • Click on "OK" to validate the dialog.

Very important, change the connection string of your Packflow site!

  • Go to the virtual directory of your PACkflow site, usually "C:\inetpub\wwwroot\Packflow\YOURSITE\".
  • Open the ConnectionStrings.config file, with administrative rights.
  • Update the connection string. If you kept the same database name, only the instance name should be changed.
  • Save the file.

Note Note

During our tests, the migrated Packflow database was not working properly. Some SQL operations were throwing the error "Failed to load Msxmlsql.dll". Packflow uses some XML features in SQL and the new instance didn't seem to get that dll.

We had to copy the msxmlsql.dll file from the directory "C:\Program Files\Microsoft SQL Server\120\LocalDB\Binn" or equivalent. The "msxmlsql.rll" should also be copied from the subfolder "Binn\Resources\CODE". These files must be copied in their corresponding directories of the new SQL instance.

Index database

Finally, an Index database must be created in the same instance as the main database.

If Packflow has been directly installed on a SQL instance with Full-Text enabled, the initial site creation already has created it. In that case this step can be skipped.

The database has to contain the same schema definitions (DBU and DBADMIN) as the main database.

A creation script is available in the "Create Site" > "Manual Creation" tool of Packflow Manager.

Please note that the database name should be the same as the main database with "_Index" as suffix.

Activation

In order to be available to the end-user. There are three level of activation.

Timer Job

The first level is the most important. You have to start the timer job (scheduled task) that will update the Index database with the content of files.

  • Open Packflow Manager.
  • Connect to your Packflow server and expand the node of the concerned Packflow site.
  • Click on the "Timer Jobs" node.
  • Click on the "Create" button.
  • Under the "Packflow" node, choose the PFIndexerJob, and click "Ok".
  • In the "Settings" tab, click on the "Add trigger" button.
  • In the dialog, select the following values: Minutely (Type), the current time (Start time), 5 (Period). Click "Ok".
  • Click on the bottom right button "Save".
FTS 3

Site

The second level lets queries use the Index database. You have to enable it on the site.

Check the box located in "Site Web Administration > Site Settings > Miscellaneous > Enable Full-Text search".

FTS 2

This box can be checked/unchecked at any time to enable/disable the feature.

Modeling

Finally, you have to choose which fields are indexed.

This is done in the application model using Packflow Designer. Check the box under a FileHolder definition and deploy the application to see the timer job indexing the corresponding files.

FTS 4
Usage

When everything is ready, filters can be used on Packflow queries to filter on the indexed content.

API

On PFQuery, any filter targeting a FileHolder field will rely by default on the indexed content.

A dedicated filter object PFQueryFilter_File can be used to choose to filter the files' content or not.

If the field is also selected in your query, you will be able to use the two related properties (FoundExcerpt and FoundExcerptKeyWords) of the PFFieldFileHolderValue.

These properties provide some information to display when the filter has been positive in one of the files' content.

Navigation

The Packflow gridviews have a special behavior related to these filter.

If you use the gridview headers for a FileHolder field (or the quicksearch) with Full-Text, you will get this kind of result:

FTS 5
Note Note
Please note that SQL Server Full-Text does NOT provide post-fix searches. You can use this functionality for exact word searches and searches with the first letters of words. In other words, in the previous screen, if you search "empus", the gridview will show no result. On the contrary, searching "temp" will return results.