Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Basically the whole thing can be explained simply:

I need to index one or more SQL Server 2005 databases with lucene so I can search the various records.

I found a lot of examples and documentation and I started to read it. The result is that I got confused and frustrated.

So, as a beginner, what are the steps to implement it along with SQL server to search in the databases?

I need some kind of workflow to follow so I can sort out a plan to work out the project.

I don't really know where to start.

-Bonus question: Is there a way to store the Lucene index directly into the SQL server database instead of a FSDirectory or RAMDirectory?

-Last one: Do lucene supports similar word searchs? (I.E.: I search 'lu' and I get all the similar words show like the google suggestions, for example 'luke', 'lucene','lua' etc etc.)

Please be as detailed as possible, I can program in C# and I've already wrote some SQL Server CLR assemblies to do various tasks, but Lucene mechanics are quite obscure to me. Thank you!

share|improve this question

closed as too broad by gnat, MichaelT, Kilian Foth, GlenH7, Bart van Ingen Schenau Oct 19 '13 at 9:29

There are either too many possible answers, or good answers would be too long for this format. Please add details to narrow the answer set or to isolate an issue that can be answered in a few paragraphs.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Why not use Sql Server's full text search capablity? I haven't used it personally, but it looks like it's possible –  Dan Pichelman Oct 15 '13 at 20:10
    
Because it's quite buggy and Lucene's performance, expecially on big databases (which is my case, since it's a very big company) is MUCH MUCH MUCH better. –  user2212907 Oct 15 '13 at 20:39

2 Answers 2

When I integrated Lucene with our database, it was a relatively straightforward process. The key (for us, at least - I'm no expert) was to not link the two. Instead, you use Lucene to index data objects with an ID which you can use to retrieve the full record from the database.

We used the SimpleLucene wrapper library to make it simpler, although I still had a lot of trial-and-error. The idea is basically:

  1. Create an object to map to your Lucene index (populates a Lucene Document object)
  2. Create an object to map to your database (standard)
  3. Create a way to transform from one to the other, implementing IIndexDefinition<T> and IResultDefinition<T> from SimpleLucene in either your data object or a transformer class.
share|improve this answer
    
+1 for SimpleLucene - quick and easy way to get basic Lucene interaction. –  jeffreypriebe May 8 at 22:52

Lucene is a document database at heart. That means that you'll want to be able to conceptually take your SQL server records out of the database and stuff them into some sort of document-like structure. This structure will contain several sorts of fields -- some which exist purely to search against as either whole keywords (such as identifiers) or for full-text indexing. Others exist to be pulled out with results, or to enable pulling things out with results -- you did remember to add your ID to the index, right?

Data-loading-wise, it is pretty straight forward -- load stuff from sql server, stuff into said documents, add to lucene indexes. Updating is doable if you have unique identifiers, as are deletions. You can search like that in lucene without much fuss using lucene search syntax -- you will recognize it as pretty commonplace as lucene powers lots of search applications.

You can't quite host lucene in SQL server -- that might well defeat the purpose. You can, however gain some of the advantages by using Sql Server's Full Text Search which is quite useful and can power many search applications with little fuss and none of the outside overhead of lucene.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.