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

I'd suggest that DBISAM, or now probably their newer ElevateDB, should be strongly considered. From your previous experience I expect you know it doesn't add much size to your executable, is reasonably fast, dependable, perfect Delphi-based solution, and has excellent support. Over the years their SQL dialect has been refined so it's now very nice with high degree of ANSI SQL-2003 compliance.

DBISAM/ElevateDB is as you note not open source, but the licensing cost is per developer only (not per deployment) so if there are only a few developers involved the cost is (in my opinion) negligible. Licensing cost is especially reasonable when you consider the level of support and responsiveness you get on DBISAM/Elevatesoft newsgroups, where the main developer/owner (Tim Young) is actively involved answering questions and solving problems. Aside from cost of a proprietary solution, there's of course also concern over whether the business will survive and product will continue to be developed and supported in the future. That's something you have to weigh for yourself, but development seems as strong as ever. They've got .NET providers for their db's, and support cross-platform development with FreePascal/Lazarus.

One plus to DBISAM's fulltext search is that it is tightly integrated into the SQL. That is, you can issue a single SELECT statement that directly combines ordinary WHERE criteria on some fields with fulltext search criteria on others. Depending on your needs, this is a nice feature that makes it really simple and fast to do some things that would take more work using a different fulltext solution. I think most add-on solutions, like Lucene and others that might be used with Firebird, don't integrate as closely with regular SQL and restrict you to retrieving a set of records with fulltext criteria only. Something like Textolution's proprietary add-on for Firebird (http://www.textolution.com/ftsib_example.asp) looks like it could accommodate constructing single complex (nested) query that would apply both regular and fulltext criteria, but DBISAM does it more elegantly. And adding a proprietary add-on to an open source db sort of defeats the purpose of going open source, anyway.

One shortcoming of DBISAM/ElevateDB's fulltext search, I think, is that is supports only use of 'AND' and 'OR' conditions, doesn't have any proximity search.

I notice that there is a fulltext solution for SQLite, http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex. I haven't ever used SQLite, but I know a lot of people love it. One area where I'm pretty sure DBISAM/Elevatesoft has big advantage is in multi-user applications where robust locking functionality is required, though that wouldn't necessarily carry any weight at all for single-user desktop app.

Answer 2

For Firebird Full-Text Search look at: http://www.firebirdfaq.org/faq328/

Leonardo.

Answer 3

Go ahead with SQLite. There are a lot of FREE sqlite components for Delphi. Some of them have ceased to be active projects. But one component of note is ZeosDB. ZeosDB is actively maintained and its sqlite support is perfect also. In case you need other DB engine support, ZeosDB is the answer again (it supports MySQL, PostgreSQL, Interbase, Firebird, MS SQL, Sybase, Oracle and SQLite).

Answer 4

NexusDB has a free embedded version and of course it's a native Delphi product.

Here's a description of it's full text search implementation.

Answer 5

I know firebird has the sphinx add-on for full text search (although according to there site it is 'far from real "full text search"' ), its free/ open source but I cant find any information to say if it works with the embedded version of firebird.

Answer 6

I have a semi-working Delphi solution here: http://sourceforge.net/projects/mutis/, a port to Delphi of Lucene.

The mayority of it work fine, but unfortunally was based in the death Delphi.NET implementation so requiere work to update to native one (yes, was a big mistake take the .NET route :( )