spEnableFullTextSearch
Definition
Copy
CREATE PROC [dbo].[spEnableFullTextSearch] AS
BEGIN
if ((SELECT fulltextserviceproperty('IsFulltextInstalled')) = 1)
BEGIN
exec sp_fulltext_database 'enable'
IF @@Error = 0
BEGIN
declare @exists int
select @exists = count(*)
from sysfulltextcatalogs
where name = 'FTPersonProfile'
IF @exists > 0
begin
exec sp_fulltext_catalog 'FTPersonProfile', 'Rebuild'
exec sp_fulltext_catalog 'FTPersonProfile', 'drop'
end
select @exists = count(*)
from sysfulltextcatalogs
where name = 'Search_SearchText'
IF @exists > 0
BEGIN
if isnull(OBJECTPROPERTY(OBJECT_ID('Search'),'TableFulltextCatalogId'),0) > 0
exec sp_fulltext_table 'Search', 'drop'
exec sp_fulltext_catalog 'Search_SearchText', 'drop'
END
exec sp_fulltext_catalog 'Search_SearchText', 'create'
exec sp_fulltext_table 'Search', 'create', 'Search_SearchText', 'PK_Search'
exec sp_fulltext_column 'Search','SearchText','add'
exec sp_fulltext_column 'Search','TagText','add'
exec sp_fulltext_table 'Search','activate'
exec sp_fulltext_catalog 'Search_SearchText', 'start_full'
exec sp_fulltext_table Search, 'Start_change_tracking'
exec sp_fulltext_table Search, 'Start_background_updateindex'
UPDATE [dbo].[Clients] SET SearchEnabled = 1
INSERT INTO [dbo].ClientContentTypes VALUES(1,89)
INSERT INTO [dbo].ClientContentTypes VALUES(1,90)
DECLARE @SQLText nvarchar(max)
DECLARE @SPExists int
select @SPExists = count(*) from dbo.sysobjects
where id = object_id(N'[dbo].[spSearchByText]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1
if @SPExists = 0
SET @SQLText = 'CREATE '
else
SET @SQLText = 'ALTER '
exec(@SQLText + 'PROCEDURE [dbo].[spSearchByText]
(
@ClientsID int,
@ClientUsersID int,
@SearchText nvarchar(768),
@SearchTextRaw nvarchar(255),
@FirstWord nvarchar(200),
@SearchTypeFilter int,
@SiteContentFilter int,
@PageContentFilter int,
@ApplyDateFilter bit,
@StartDateFilter datetime,
@EndDateFilter datetime,
@TagOnlySearch bit,
@ClientSitesIDs XML
)
AS
BEGIN
DECLARE @CurrentDate datetime
DECLARE @ContentPages table(
PageID int,
PageName nvarchar(255),
PageContentID int,
SiteContentID int,
PageGUID uniqueidentifier,
TemplateGUID uniqueidentifier null,
ContentTypesID int)
DECLARE @ValidSites table(SiteID int)
SET @CurrentDate = getutcdate()
INSERT INTO @ValidSites
SELECT ID
FROM ClientSites CS
WHERE CS.Deleted = 0 and dbo.UFN_CLIENTSITE_ISPUBLISHED(CS.ID) = 1
--get all pages with matching targeted content
INSERT INTO @ContentPages select DISTINCT sp.ID, CASE LEN(isnull(sp.DisplayName, '''')) WHEN 0 THEN sp.PageName ELSE sp.DisplayName END AS PageName,
pc.ID, s.SiteContentID, sp.guid, (SELECT sp2.GUID from SitePages sp2 where sp2.ID = sp.TemplatePageID), SC.ContentTypesID
FROM Search s
INNER JOIN TargetedContentChecks tcc on tcc.ContentID = s.SiteContentID
INNER JOIN TargetedContentPart tp on tp.ID = tcc.TargetedPartID
INNER JOIN PageContent pc on pc.SiteContentID = tp.SiteContentID
INNER JOIN SitePages sp on sp.IsTemplatePage = 0 and sp.id = pc.SitePagesID
INNER JOIN dbo.SiteContent SC on SC.ID = S.SiteContentID
where sp.deleted = 0 and SC.deleted = 0
--get all pages with matching content
INSERT INTO @ContentPages select DISTINCT sp.ID, CASE LEN(isnull(sp.DisplayName, '''')) WHEN 0 THEN sp.PageName ELSE sp.DisplayName END AS PageName,
pc.ID, s.SiteContentID, sp.guid, (SELECT sp2.GUID from SitePages sp2 where sp2.ID = sp.TemplatePageID), SC.ContentTypesID
FROM SitePages sp
INNER JOIN PageContent pc on sp.IsTemplatePage = 0 and pc.SitePagesID = sp.id
INNER JOIN Search s on s.SiteContentID = pc.SiteContentID
INNER JOIN dbo.SiteContent SC on SC.ID = S.SiteContentID
where sp.deleted = 0 and SC.deleted = 0
--delete pages from locked artifacts
delete @ContentPages
from @ContentPages CP
inner join dbo.pagemodelitem PMI on CP.PageGUID = PMI.OBJECTGUID
inner join dbo.pagemodel PM on PM.ID = PMI.PageModelID
where PM.LOCKARTIFACTS = 1
--delete pages from sites outside the scope of this search
if @ClientSitesIDs.exist(''/ids/id'') = 1
delete @ContentPages
from @ContentPages CP
inner join dbo.SitePages SP on SP.ID = CP.PageID
left outer join @ClientSitesIDs.nodes(''/ids/id'') IDS(ID) on IDS.ID.value(''@value'', ''int'') = SP.ClientSitesID
where IDS.ID.value(''@value'', ''int'') is null
--delete pages from unpublished or deleted sites
delete @ContentPages
from @ContentPages CP
inner join dbo.SitePages SP on SP.ID = CP.PageID
left outer join @ValidSites VS on VS.SiteID = SP.ClientSitesID
where VS.SiteID Is null
insert into [dbo].[SearchLog]
(SearchTypeFilter, ClientID, ClientUserID, [Text])
values
(@SearchTypeFilter, @ClientsID, @ClientUsersID, @SearchTextRaw)
if @SearchTypeFilter <= 0
SET @SearchTypeFilter = NULL
if @SiteContentFilter <= 0
SET @SiteContentFilter = NULL
-- Tag search first
if @ApplyDateFilter = 1
if @TagOnlySearch = 1
SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
ORDER BY k.RANK desc
else
SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
UNION ALL
SELECT DISTINCT cp.PageID ID , cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 0 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, SearchText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
where [dbo].[fnSearchDateRangeIsValid](@StartDateFilter, @EndDateFilter, s.SearchDate1, s.SearchDate2) = 1
ORDER BY k.RANK desc
else
if @TagOnlySearch = 1
SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
ORDER BY k.RANK desc
else
SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 1 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, TagText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
UNION ALL
SELECT DISTINCT cp.PageID ID, cp.PageName, s.SearchTypeID, s.SiteContentID, s.Title, s.ExtendedKey, k.RANK, dbo.fnSearchHighlightText(s.SEARCHTEXT,@FirstWord) SearchText, cp.PageContentID, s.SearchDate1, s.ContentUpdateDate, sc.Guid ObjectGUID, cp.PageGUID, cp.TemplateGUID, 0 AS ResultFromtag, cp.ContentTypesID
FROM Search s
INNER JOIN CONTAINSTABLE(Search, SearchText, @SearchText) k ON k.[KEY] = s.ID and k.RANK > 0
and isnull(@SearchTypeFilter,s.SearchTypeId) = s.SearchTypeId
and isnull(@SiteContentFilter,s.SiteContentId) = s.SiteContentId
INNER JOIN @ContentPages cp on cp.SiteContentID= s.SiteContentID
INNER JOIN (SELECT [ID], [Guid] FROM vwSiteContent WHERE [Status]=2) sc on sc.ID = s.SiteContentID AND dbo.fnSiteContentIsPublished(sc.id,@CurrentDate) = 1
ORDER BY k.RANK desc
END')
END
END
END