Fulltext Search for Firebird SQL

• Download source files - 9.51 Kb

Introduction

In this article, we will talk about searching the data in a Firebird database using DotLucene full-text search engine. We will focus on storing the index directly in the database (the source code of the solution is attached).

Firebird SQL

Firebird SQL (excellent embedded database with a good .NET support) has no built-in fulltext search support so far. Instead, you need to rely on third party tools. Fortunately, there is a great search engine library available: DotLucene. It is an open-source .NET library (ported from Java) that can index any data (structured or unstructured) that you are able to convert to raw text. Using an additional library for fulltext doesn't look too elegant at first sight. However, it has some advantages. Let's compare it quickly with MySQL integrated fulltext search:

MySQL Fulltext Search

MySQL fulltext search has these drawbacks (compared to DotLucene):

• You can use it only in MyISAM tables (i.e. no transactions)
• You can't browse the index (see Luke)
• You need to store transformed text in the DB (i.e. for indexing HTML, you need to store another copy of the text with stripped HTML tags)
• It doesn't support highlighting of the query words in the result
• You will hardly modify the sources to do custom changes
• The license doesn't allow to use it in commercial application for free
• It is reported to be slow on large data sets

How to Index the Data?

For basics about using DotLucene to index your data, I recommend reading:

• DotLucene: Full-Text Search for Your Intranet or Website Using 37 Lines of Code (CodeProject Article)
• DotLucene Tutorial
• DotLucene Online Demo

The following applies for indexing the database:

• You are using a different source of data (obviously ;-). Instead of reading from the disk, you need to load it from the database.
• When indexing texts, you don't need to it in the index in full, just keep them in the database.
• Create an additional Field that will contain the primary key of the indexed document (so you can later load it from the database).
• When indexing HTML, you need to strip the HTML tags (you need to supply raw text to DotLucene).

Where to Store the Index

On a server, it's no problem to store the index in a separate directory (you can also load it to RAM to make your searches super fast - if you have enough RAM, of course). In a desktop application, it might be useful to store the index in a Firebird database.

DotLucene supports a mechanism for adding custom index storages. All storage types (file system and RAM are built-in) are implemented as a class derived from Lucene.Net.Store.Directory abstract class. I have created a Directory implementation that stores the index directly in a Firebird SQL database.

FbDirectory class

All index reading/writing operations in DotLucene are done using a Directory class. The new FbDirectory class is based on FSDirectory. The filesystem operations are replaced with database operations. Here you can see what we need to implement:

using System;

namespace Lucene.Net.Store
{
    public abstract class Directory 
    {
        public abstract String[] List();
        public abstract bool FileExists(String name);
        public abstract long FileModified(String name);
        public abstract void TouchFile(String name);
        public abstract void DeleteFile(String name);
        public abstract void RenameFile(String from, String to);
        public abstract long FileLength(String name);
        public abstract OutputStream CreateFile(String name);
        public abstract InputStream OpenFile(String name);
        public abstract Lock MakeLock(String name);
        public abstract void Close();
    }
}

Performance Tips

1. If performance is your main concern, use the standard FSDirectory instead to store the index on disk. My tests show that database storage is twice slower than filesystem. Use the database storage only when you have no other choice.
2. Use compound index format (IndexWriter.SetUseCompoundFile(true);). This is default in DotLucene 1.4 but in 1.3 you have to do it manually.
3. Create the index in memory, optimize, then save it on disk using FbDirectory.Copy(); This will only help you if you are rebuilding the whole index from scratch.
4. If you are adding a document to the index from a desktop application, do it in background (in a separate thread). You are still able to search while you are adding a new document.

Useful Resources

• DotNetFirebird.org: Using Firebird SQL in .NET
• DotLucene: Open-source search engine library
• Embedded Firebird: Full-Featured Embedded Database with 2 MB Runtime
• DotLucene: Full-Text Search for Your Intranet or Website Using 37 Lines of Code

You must Sign In to use this message board.

FAQ    Noise Tolerance Very HighHighMediumLowVery Low   Layout Expand Posts & RepliesThread ViewNo JavascriptNo JS + Preview   Per page 102550

Msgs 1 to 12 of 12 (Total in Forum: 12) (Refresh) FirstPrevNext

Lucene.Net - 2.0 alisson_abreu 8:17 3 Nov '08   When I try to build, I got the following error messages: Error 1 The type or namespace name 'InputStream' could not be found (are you missing a using directive or an assembly reference?) ... Lucene.Net-2.0.0 Error 2 The type or namespace name 'OutputStream' could not be found (are you missing a using directive or an assembly reference?) ... Lucene.Net-2.0.0 Error 3 The type or namespace name 'OutputStream' could not be found (are you missing a using directive or an assembly reference? ... Lucene.Net-2.0.0 Error 4 The type or namespace name 'InputStream' could not be found (are you missing a using directive or an assembly reference? ... Lucene.Net-2.0.0 Could anyone help me? Sign In·View Thread·PermaLink The code is released for businesses too? Adriano Alves de Lima 8:36 23 Oct '08   Guys, I wonder if this code is released for commercial purposes, in the end the article author does not explain whether you can use in business? Grateful now ... Sign In·View Thread·PermaLink new IndexWriter syntax ? vaclav1354 4:02 3 Aug '06   Hi! I´m trying to run LUCENE with FireBird, and have small problem. IndexWriter writer = new IndexWriter(???, new StandardAnalyzer(), true); What should be instead of ??? if the IndexWriter should use Firebird (FbDirectory) as a storage place. I didnt find any syntax example. Everywhere there is a sample with file storage. Thanx for posting small piece of code. Vaclav Sign In·View Thread·PermaLink 2.00/5 (1 vote) How to index data stored in a database using a fulltext index? Michael Freidgeim 15:54 18 Jul '06   I didn't found in the article, how to index data stored in a database using a fulltext index? The article and download describes how to use Firebird database to store index (in LCN_FLS and LCN_BLCK tables). But if I have an existing database (e.g MS Northdwind) and want to full-text index tables (e.g Customers CompanyName and ContactName columns,text columns in Orders and Products tables). How can I submit these text columns for Lucene.Net indexing? Michael Freidgeim. Blog: http://geekswithblogs.net/mnf/ Sign In·View Thread·PermaLink 2.00/5 (1 vote) Re: How to index data stored in a database using a fulltext index? Michael Freidgeim 22:20 8 Aug '06   I've found the article that answered my question Indexing Database Content with dotLucene [^] Michael Freidgeim. Blog: http://geekswithblogs.net/mnf/ Sign In·View Thread·PermaLink Problem .NET 2.0 Lucene 1.4.3 [modified] Jonas Ljunggren 12:04 26 May '06   Anyone who has solved the problems that shows then the source is compiled? The name 'Date' does not exist in the current context [return Date.GetTime(Convert.ToDateTime(cmd.ExecuteScalar()));] 'Lucene.Net.Store.InputStream' does not contain a definition for 'BUFFER_SIZE' [int bufferNumber = start / InputStream.BUFFER_SIZE;] -- modified at 17:05 Friday 26th May, 2006 Sign In·View Thread·PermaLink Re: Problem .NET 2.0 Lucene 1.4.3 Arkar Myo 2:11 30 Oct '06   Is it possible? return Convert.ToDateTime(cmd.ExecuteScalar()).Ticks; (or) return DateTime.Parse(Convert.ToString(cmd.ExecuteScalar())).Ticks; i am arkar myo. Sign In·View Thread·PermaLink Nice Article computerguru92382 15:27 1 Mar '06   Very nice article done for a cool database. Keep up the good work PJC Sign In·View Thread·PermaLink Increase performance Samuel Chen 21:41 22 Mar '05   hi Dan Letecky : Nice to meet you again. I have more than million records indexed. They are stored as the following structure: id : 0 ~ 1000000 (keyword) company_id : 0 ~ 1000000 (keyword) content : Title(some text) ||(splited by double '|') product description || company description || ... (UnStored) full_content : (same as content, Text, just for test) For example, It takes more than 10 seconds when It's searching with "+48100 +milk". How can I increase the performance ? Should I store all splited parts as keyword fields ? Or is there any other way ? thx! ps. I am using file system to store the index. Best regards Samuel www.alphatom.com Sign In·View Thread·PermaLink Re: Increase performance Samuel Chen 23:39 22 Mar '05   funny result I have downloaded luke which is recommended on your dotlucene.net site. And then searched with the same keywords. The result surprised me! It just took 31ms (and the computer was still indexing at the same time). In this search i selected org.apache.lucene.analysis.KeywordAnalyzer as "Analyzer to use to query parsing" and org.apache.lucene.analysis.cn.ChineseAnalyzer as "SnowballAnalyzer". The "default field" was also set as company_id.(blank will get nothing) Does it mean that in my case I should take SnowballAnalyzer and Default fields to increase the performance? Best regards Samuel www.alphatom.com Sign In·View Thread·PermaLink Some questions... Paul Selormey 14:35 9 Mar '05   First I really appreciate the great information you are providing with these articles. Just some few questions: UDT: Any way to define/implement user data types in .NET? .NET Runtime hosting: Any plan to implement a .NET runtime hosting similar to the new SQL Server Express 2005? .NET Assembly: Any plan to compile the Firebird with MC++ to create a .NET assembly that could be used in on-touch deployments? Best regards, Paul. Jesus Christ is LOVE! Please tell somebody. Sign In·View Thread·PermaLink 3.50/5 (2 votes) Re: Some questions... Dan Letecky 6:40 11 Mar '05   1) Yes, user defined data types are supported (see "domains"). 2) Not supported. 3) Not supported. Anyway, it doesn't matter. The embedded Firebird DLL doesn't have to be registered, just copy it with your application. Sign In·View Thread·PermaLink 2.00/5 (1 vote)

Last Visit: 16:35 5 Jul '09     Last Update: 16:35 5 Jul '09 1