3/13/2008

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.


kick it on DotNetKicks.com

Read more...
Related Posts with Thumbnails

Recent Comments

Free DotNetNuke Stuff

Free DotNet Videos

  © Blogger template The Professional Template by Ourblogtemplates.com 2008

Back to TOP