View Single Post
 
Old August 9th, 2007, 04:03 AM
jimibt jimibt is offline
Friend of Wrox
 
Join Date: Mar 2007
Location: Creetown, UK
Posts: 488
Thanks: 2
Thanked 11 Times in 10 Posts
Default

yes,

i created one on my site and use the sqlserver free-text searching facility on that. For simplicity, let's just deal with the current TBH modules. For the Articles and Posts, i just created the index on the Body, Title (and in the case of Articles, also, the Abstract). the sql to do this is illustrated below:

Firstly, create the storage for the indexes and give them appropriate names:

use TBHLocalSqlServer -- the name of the database registered in sqlserver
go
EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG my_Articles
go

use TBHLocalSqlServer -- the name of the database registered in sqlserver
go
EXEC sp_fulltext_database 'enable'
go
CREATE FULLTEXT CATALOG my_Posts
go


Next, add the indexes to the storage:

create FULLTEXT INDEX ON dbo.tbh_Articles
(
Body,Abstract,title
Language 0X0
)
KEY INDEX PK_tbh_Articles ON my_Articles
WITH CHANGE_TRACKING AUTO

create FULLTEXT INDEX ON dbo.tbh_Posts
(
Body,title
Language 0X0
)
KEY INDEX PK_tbh_Posts ON my_Posts
WITH CHANGE_TRACKING AUTO

And that's the freetext indexes setup. Now you have to create a generic query for these using the Contains() keyword:

i.e (this is not the sql i use, just an illustrative excerpt):

select * from (
select 'ShowArticle.aspx?ID=' + convert(nvarchar,tbh_articles.ArticleID)
+ '&search=' + @search as linkid,
tbh_articles.abstract as descriptiontext,
tbh_articles.title as title, 'article' as source from tbh_articles
where contains(body, @whereclause)
or contains(abstract, @whereclause)
or contains(title, @whereclause)

-- need to do a bit of work on the posts part to remove
-- duplicates and retain single line per post
union

select 'ShowThread.aspx?ID=' + convert(nvarchar, tbh_Posts.PostID)
+ '&search=' + @search as linkid,
convert(nvarchar(4000), tbh_posts.body) as descriptiontext,
tbh_posts.title as title, 'posts' as source from tbh_posts
where (contains(body, @whereclause)
or contains(title, @whereclause))
and approved=1

) results

(thanks to vantoko for the above idea for the sql). Behind the scenes, i've created a SearchItem BLL and DAL in the same paradigm as THB. I also have a usercontrol composed of a textbox and a button. this basically fires a redirect to my searchpage with the search criteria as parameters.

You can see this running if you go to my test site (see below). suggested search terms (multiple words on a single line) to see it working (i only have a limited amount of data at present) are (but not limited to :D):

bbc itv
mika new york
beer

i'll probably post the source code and full instuctions in a few weeks, but for now i'm in a phase of attempting to deliver a several things so can't really get down to that yet.

Hope the pointers above give enough to get you thinking of approaches.

jimi

http://www.jamestollan.com