Click here to Skip to main content
13,901,395 members
Click here to Skip to main content
Add your own
alternative version


77 bookmarked
Posted 9 Mar 2005

Fulltext Search for Firebird SQL

, 9 Mar 2005
Rate this:
Please Sign up or sign in to vote.
How to search data stored in a Firebird SQL database using a fulltext index.


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:

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


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Dan Letecky
Czech Republic Czech Republic

You may also be interested in...

Comments and Discussions

GeneralLucene.Net - 2.0 Pin
alisson_abreu3-Nov-08 7:17
memberalisson_abreu3-Nov-08 7:17 
QuestionThe code is released for businesses too? Pin
Adriano Alves de Lima23-Oct-08 7:36
memberAdriano Alves de Lima23-Oct-08 7:36 
Questionnew IndexWriter syntax ? Pin
vaclav13543-Aug-06 3:02
membervaclav13543-Aug-06 3:02 
QuestionHow to index data stored in a database using a fulltext index? Pin
Michael Freidgeim18-Jul-06 14:54
memberMichael Freidgeim18-Jul-06 14:54 
AnswerRe: How to index data stored in a database using a fulltext index? Pin
Michael Freidgeim8-Aug-06 21:20
memberMichael Freidgeim8-Aug-06 21:20 
GeneralProblem .NET 2.0 Lucene 1.4.3 [modified] Pin
Jonas Ljunggren26-May-06 11:04
memberJonas Ljunggren26-May-06 11:04 
GeneralRe: Problem .NET 2.0 Lucene 1.4.3 Pin
Arkar Myo30-Oct-06 1:11
memberArkar Myo30-Oct-06 1:11 
GeneralNice Article Pin
Paul Conrad1-Mar-06 14:27
professionalPaul Conrad1-Mar-06 14:27 
GeneralIncrease performance Pin
Samuel Chen22-Mar-05 20:41
memberSamuel Chen22-Mar-05 20:41 
GeneralRe: Increase performance Pin
Samuel Chen22-Mar-05 22:39
memberSamuel Chen22-Mar-05 22:39 
GeneralSome questions... Pin
Paul Selormey9-Mar-05 13:35
memberPaul Selormey9-Mar-05 13:35 
GeneralRe: Some questions... Pin
Dan Letecky11-Mar-05 5:40
memberDan Letecky11-Mar-05 5:40 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web03 | 2.8.190306.1 | Last Updated 9 Mar 2005
Article Copyright 2005 by Dan Letecky
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid