Solving the Search Results problem when using the free MMLinks localizable module

(This article assumes that you have an advanced knowledge of what's going on inside the DotNetNuke portal framework and its Search indexer.)

For those of you who don't know what the MMLinks module is, go here to find out.

(waiting for you to read...)

Okay, now you know. With MMLinks, you can replace your standard Text/HTML modules with fully localizable ones, localizing both the title and the Text/HTML part. With one slight defect: Since DotNetNuke's search indexer doesn't know anything about the localized titles, it insists on presenting the default, non-localized module title in the Search Results page (that would be "MMLinks" if you leave it intact).

The reason for this is that localized titles for the MMLinks modules are kept in a different table: Delisoft_MMLinksTitleLocales. DotNetNuke doesn't take this table into consideration when indexing, so no localized titles in Search Results.

I tried to solve this problem by creating a trigger which affects the core SearchItem table. That's right, the table where all indexed items are kept. The trigger investigates whether the row being updated belongs to an MMLinks module instance (by checking the ModuleDefinitions table). If it does, it replaces the contents of the Title field with the localized MMLinks title. Since MMLinks modules produce one entry for each locale, voila. You've got localized MMLinks titles in Search Results (which, by the way, uses the contents of the SearchItem table).

I am providing this code in order to receive comments and / or improvements to it. Please do not use it anywhere near sensitive data or production databases. It may not work correctly.


So, first, a catch-all disclaimer to make the above statement even stronger :) :

CAUTION: THE TRIGGER HAS BEEN ONLY TESTED A COUPLE OF TIMES. IT IS SUGGESTED THAT YOU DO NOT USE THIS CODE IN A PRODUCTION ENVIRONMENT. IT IS STRONLGY ADVISED THAT YOU BACKUP YOUR DOTNETNUKE DATABASE FIRST. I DO NOT TAKE RESPONSIBILITY FOR ANY MALFUNCTION OR DATA LOSS THAT MAY RESULT FROM USING THE CODE PROVIDED HERE. USE AT YOUR OWN RISK.

And now, the code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tool
-- Create date: March 2008
-- Description: A trigger which changes the title
-- field in the SearchItem table and replaces it
-- with the LOCALIZED title when using the MMLinks
-- module. USE AT YOUR OWN RISK.
-- =============================================
ALTER TRIGGER [dbo].[tr_Tool_setLocalizedTitleForMMLInksModule]
   ON  [dbo].[SearchItem]
   AFTER INSERT, UPDATE
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON

 DECLARE @searchItemId int
 SELECT @searchItemId = searchItemId FROM Inserted

 DECLARE @moduleid int
 SELECT @moduleid = moduleid FROM Inserted

 -- Do work ONLY if entry in SearchItem table concerns an MMLinks module instance.
 IF EXISTS
  (
  SELECT
   ModuleId
  FROM
   dbo.Modules
  WHERE
   ModuleDefId in
    (
    SELECT
     ModuleDefId
    FROM
     dbo.ModuleDefinitions
    WHERE
     FriendlyName ='MMLinks'
    )
  AND ModuleId = @moduleid
  )
 BEGIN

  DECLARE @localetext nvarchar(150)

  --Get the localized text
  --In order to find the localized text, we use a trick.
  --MMLinks fillds the SearchKey column in the SearchItem table with the
  --moduleId concatenated with the locale, in the form xxx-yyy. We use this
  --information to track the correct entry in the MMLinksTitleLocales table.
  
  SELECT
   @localetext = LocaleText
  FROM
   dbo.Delisoft_MMLinksTitleLocales
  WHERE
   (CONVERT (varchar(5), ModuleId)+'-'+ LocaleKey) =
   (SELECT SearchKey FROM SearchItem WHERE SearchItemID = @searchitemid)

 
  --Once we have the localized text, do the update.
  IF @localetext IS NOT NULL AND rtrim(ltrim(@localetext))<>''
  BEGIN
   UPDATE
    dbo.SearchItem
   SET
    Title = @localetext
   WHERE SearchItemId = @searchitemid
  END

END


 SET NOCOUNT OFF
END

If you want to see immediate results, just delete everything in your SearchItem table and do a content re-index from the Host Search Admin menu.

I'm anxiously waiting for comments.

Read more...