Table of Contents

SQL indexes

An SQL index is an index containing data retrieved via an SQL statement. It's typically employed when there's a need to index custom data imported to the database from external sources, negating the need for a custom schema extender. It is not usually used for custom data which is already included in our various schema extenders, such as custom user fields, custom product fields, and so on.

As of DynamicWeb 10.08, you cannot create new build definitions for a custom index, as some features supporting this are yet to be implemented.

To create an SQL index:

  1. Go to Settings > Repositories and open/create a repository.
  2. Under the indexes section, click manage.
  3. Click New index.
  4. Provide a Name to the index.
  5. Select a Balancer
    • Dynamicweb.indexing.balancing.ActivePassive - selects the next instance on the list of instances – so if instance A is unavailable (building, has failed), instance B will be used unless it’s unavailable, in which case instance C will be used, and so on
    • Dynamicweb.indexing.balancing.LastUpdated - directs operations to the most recently updated index, ensuring users interact with the freshest data
  6. Click Save and close.

On solutions with heavy traffic and frequent product data updates we recommend using the LastUpdated mode to ensure that visitors are always shown the most recently updated product data. On solutions with only two instances (the vast majority of solutions) it is not necessary to select a balancer mode, as the “other index” will always be used when an index is unavailable.

This creates an empty index. You should now add instances to it. CustomIndexes_01

Adding Instances

An instance refers to a specific file stored in the file archive. When a query is executed, it's this file that gets searched. It's common for instances to be rebuilt regularly to incorporate the latest changes to product data. For this reason, it's recommended to maintain at least two instances. Having multiple instances ensures that while one is being updated or rebuilt, the other remains available for searches.

  1. In the Indexes section, enter the Index you want to add an instance to
  2. Click the Actions button the on top right corner and select Manage instances
  3. Click New instance
  4. Provide a name – you could call the first instance ‘Custom A’ and the other instance ‘Custom B’
  5. Select the LuceneIndexProvider
  6. Specify a folder to place the instance file under
  7. Click Save and close
  8. Repeat the process for the second instance

Once created, the two instances will look something like this: CustomIndexes_02 When an instance is built a set of index files are generated under System > Indexes > YourIndexName > YourInstanceName – but before you can build it you must create a build configuration.

Adding a Build Configuration

So now that you have two instances, you want to build them – to do so, you need to create a build definition. Each type of index has a specific builder associated with it – in the case of an SQL index, this builder is called SqlIndexBuilder.

To add the build configuration:

  1. Enter the Index in which you want to create a Build
  2. Under the Builds section, select Manage
  3. Click New build
  4. Provide a name
  5. In the Builder section, select Dynamicweb.Indexing.Builders.SqlIndexBuilder
  6. Select a Builder action - currently only Execute is available
  7. Set up Notifications if appropriate
  8. Specify a connection string in the format Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;
  9. Specify a query which retrieves the columns and rows you want to index
  10. Specify a query which returns a count of the rows being added to the index

A setting – UseStoredProcedure – can also be set in the index XML config file. When set to True, the stored procedure name must be set in the “Query” setting which will be executed when running the builder to index the data.

Adding Fields

Lucene indexes are composed of small documents, with each document divided into named fields which contain either content which can be searched or data which can be retrieved. Each field added to the index can therefore be stored, indexed, and analysed depending on what you want to use it for:

  • Stored fields have their values stored in the index
  • Indexed fields can be searched, the value is stored as a single value
  • Analysed fields have their values run through an analyser and split into tokens (words)

Generally speaking:

  • A field you want to display in frontend must be indexed
  • A field where you want to search for part of the value in free-text search must be analysed
  • A field which are to be published using the Query publisher should be Stored
  • A field you want to display as facets should be indexed, but not analysed

Since the SQL index is for custom data we do not have any default schema extenders for this type of index. Instead you can add fields to the index manually – see the Custom Fields article.

Building the Index

Once you’ve added instances, a build configuration, and a set of fields to the index, you should build it – to do so, click the Build button beneath each instance you want to build. CustomIndexes_03 Of course, you don’t want to do this manually every time – you want to rebuild the index on a schedule – see the article on tasks.

Improve this doc
To top