Full Text Search
This topic contains the following sections:
This document details the requirements and procedure to activate the Full Text Search on Packflow.
This feature enables Packflow queries to filter on the content of files hosted in FileHolder fields.
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.
|The Index database has to be installed in the same SQL instance as the main database.|
|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.|
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.
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.
Very important, change the connection string of your Packflow site!
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.
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.
In order to be available to the end-user. There are three level of activation.
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.
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".
This box can be checked/unchecked at any time to enable/disable the feature.
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.
When everything is ready, filters can be used on Packflow queries to filter on the indexed content.
On PFQuery, any filter targeting a FileHolder field will rely by default on the indexed content.
A dedicated filter objectcan 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.
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:
|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.|