Database objects

Introduction

During database setup of Faceted Search a number of entries in some existing Smartsite tables are inserted and a couple of database objects (tables, views, stored procedures, etc.) are created. This page describes these database objects as well as their functionalities towards Faceted Search.

Full text catalog

Of course, the full text catalog itself will be created. Faceted Search typically uses SmsSearch as the name of the full text catalog. More technical information about SQL Server's Full Text Search technology can be found here and under its children.

ContentTypeField Searchable

Tables Contents and ContentVersions will be extended with not nullable column Searchable of data type bit which defaults to True. Simply put, this column acts as a flag for common front-end searching tables/views in order to not show irrelevant pages like a login page. Note that this column should not be used for security's sake considering the fact that it defaults to True which indicates that every item is "searchable". Refer to this page for more information of this field.

Column Searchable will be added as a ContentTypeField to all ContentTypes. Note that unlike a very early version of Faceted Search, this column is not a mandatory field for a ContentType. Should this column is not a ContentTypeField of a certain ContentType, this will implicate that each item of this ContentType will be "searchable" since this bit defaults to True and cannot be modified in the Smartsite manager.

Table tsSearchGroups

A new table tsSearchGroups is introduced during the Faceted Search installation. This table can be managed in the Smartsite manager:

Tools ➤ Faceted Search Console ➤ Search Groups.

Basically an entry in SearchGroups represents a subset of the Contents table and automatically results to an indexed view which will be indexed in the full text catalog. Three default search groups are inserted after a clean installation:

• Default Front-End Search Group (PUB)
• Default Back-End Search Group (CMS)
• Default Developers Search Group (SXML)

Typically the SXML search group contains all text data types of all ContentTypes while the CMS search groups only contains text data types relevant to an author. Search group PUB only holds a subset of search group CMS since PUB is a typical Front-End search group. Note that search group separating is a good candidate for managing contents of different security-levels.

Since the Searchable column is typically considered only in the front-end, only search group PUB will ignore all contents of which this value is set to False. Refer to this page for more information on search groups management.

Tables tsSearchGroupsCTypes and tsSearchGroupsCTypeFields

The newly introduced tables tsSearchGroupsCTypes and tsSearchGroupsCTypeFields are so-called helper-tables for the stored procedure that ultimately creates the indexed views. These tables should not be tampered with nor relied to in any case. However, some Smartsite manager matrices do depend on these tables simply to acquire the settings and monitor the status of Faceted Search.

Table tsContentTypeFieldWeights

Entries of the new table tsContentTypeFieldWeights can be managed in the Smartsite manager:

Tools ➤ Faceted Search Console ➤ ContentType Weights

and:

Tools ➤ Faceted Search Console ➤ (Physical) Field Weights.

Although these appear to be two separate user actions, the corresponding data entries occurs on this single table. Therefore, it makes more sence to just explain the usage of these user actions rather than to describe the technological handling of this table.

If some particular ContentTypes deserves a different ranking score, this can be managed in ContentType Weights. On the other hand, if different ranking scoring is desired at the level of physical fields, this can be managed in (Physical) Field Weights. Both have the usual DataEditor layout and must be managed per search group.

As a default the physical fields Author, Title and Description are set to resp. 1, 4 and 2. Every entry in this table will result in an extra column of the indexed view. This means that setting a weight on 1 makes only sense if it is desireable for a column to be a part of this view without really altering the weight, since weights are normalized to 1.

Table tsFieldFilters

Entries of the new table tsFieldFilters can be managed in the Smartsite manager:

Tools ➤ Faceted Search Console ➤ Field Filters.

Field filters are scalar-valued database functions (either system functions or user defined functions) which can be set on a content type field in the indexed view. The placeholder for the column name is {FIELD}. As a default four field filters will be inserted after setup:

• plaintext removes HTML tags, vipers and outputs unicode (nvarchar) - typically used for front-end
• novipers removes vipers and outputs unicode (nvarchar) - typically used for back-end authors
• rawtext outputs unicode (nvarchar) - typically used for back-end developers
• raw does not affect field - typically used for binary data for front-end and back-end

Field filter plaintext should be used for each field of which all HTML tags and vipers should be removed. Field filter novipers only removes vipers. Field filters rawtext and raw are practically identical, however field filter rawtest transforms textual content to a unicode encoding as plaintext and novipers do. This means that field filter raw is the only recommended field filter for binary content.

Refer to this page for more information on field filters.

Objects tsSearchHistory, tsSearchTermHistory, vwtsSearchHistoryLog and TRG_TSSEARCHHISTORY_INSERT

In order to be able to keep track of historical search terms and corresponding search results, these database objects are needed. Furthermore these objects are also used for Faceted Search functionalities like Did you mean. These objects should not be tampered with since they are filled by Faceted Search automatically.

Helping user defined functions

Faceted Search introduces helping user defined functions:

• fn_tsAgeInDays returns the difference between now and the given date in days
• fn_tsCountWord counts the number of times a word occurs in a given string
• fn_tsContainsThesTerms gets content items which have a thesaurus term relation which matches the search term and content items with a thesterm relation which matches a broader/narrower term

Stored procedure pr_tsFacetedSearch

This stored procedure is used to create, alter or drop the indexed views, taking into account all Faceted Search configuration settings.

Parameter @action is the type of action this stored procedure should do:

• DROP removes the view (implicitly dropping indexes). Empty tsSearchGroupsCTypeFields releasing FK constraints.
• PREP prepares Faceted Search. Create/update view and indexes.
• PREPLAZY does same as PREP but not executing scripts if no changes detected.
• DEBUG does same as PREP but printing DML queries without executing them.
• INIT only prepares all helping tables but never creates nor alters database objects.
• TEST tests if configuration for FTS is correct.
• VALIDATE tests if significant changes are detected in configuration.

View vwtsStopWords

This view is a helping view to manage the stop words of a SQL Server 2008 database. This view should not be tampered with.