Full text search with embedded DB in Delphi

Question

We are creating an open source Twitter client and are looking for an embedded DB with the smallest footprint possible that works with Delphi and that lends itself well to full text search (I know that doesn't go with small footprint very well). Ideally it should be free or open source too (demanding I know).

I am leaning toward SQLite, but I have not used it before and don't know if it supports full text search, or how well it works with Delphi. I've used DBISAM before and it is embedded with full text search, but not free. Firebird is another option we considered.

There might be a combination that makes this work. What would you use, and how does it rate for 1) Footprint, 2) Full text search, 3) Free/Open source.

UPDATE: Thanks to everyone for your suggestions. So many good choices to choose from.

Answer 1

We've integrated DotLucene into one of our internal Delphi-based apps, using Hydra (Mike did the work on that, back then); if that's an option we wanna pursue here, i'm sure we can extract that and make it reusable for this project

Answer 2

Why would you need a full text search for this? I don't use Twitter, bu I believe twitter messages are 140 characters max? These would fit in one varchar field. You don't need full-text search to find something in such a field.

Answer 3

Sybase Advantage Database Server has a royalty free local server engine (multi-threaded and record level locking), great Delphi TDataSet descendant components (source code included), and a great full text search engine.

Advantage full text search supports AND, OR, NOT, and NEAR operators. Advantage also provides SCORE and SCOREDISTINCT scalar functions which return the total count of all instances of words in the search condition.

Advantage full text indexes are maintained at record update time which means they don't require any rebuilds. The indexes are in a compressed format; therefore the actual index size can be much smaller than the physical data. It is possible to perform full text searches on non-indexed data, but this requires a physical search of the record data and can be much slower. Indexed searches use bitmap filters for optimal performance.

The online help has all the details as far as indexing options, etc.:

http://tinyurl.com/ctjoqg

Answer 4

DBISAM all the way. Works, very solid, embedded, full text indexing works great (extensive personal experience!)

ElevateDB is prob also great, but I have no direct experience with it.

Answer 5

There's also DotLucene, which does require the .NET framework to be installed but can be easily loaded through the .net com apis. It's ideal for searching and has a query format that is common to most search engines.

Answer 6

I would go with Sqlite using Aducom Sqlite components, they are easy to work with sqlite than DiSQLite, free and work as TDataset components, I would vote up for sqlite because:

1. It's very fast and compact database.
2. Very small footprint, just around 200kb Dll.
3. It's can be linked static when used with Aducom components, so no needs for distribute the dll.
4. It has built in support for full text search.
5. It's wildly used and used in many free and commercial applications as external storage.
6. It has most of standard SQL, so most of your knowledge in sql will be used.