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

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 2

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 3

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 4

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 5

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

Leonardo.

Answer 6

I've had a lot of success using DiSQLite. It has FTS support and a ton of other features. They have a Free version and a Professional version. I believe the Free version does FTS as well. I've tried many of the SQLite implementations for Delphi and this is the best one I've seen. It compiles straight into your application so there are no external DLLs.

I've looked at a lot of the free libraries out there for embedded databases in Delphi and many are not supported anymore, never got released, or only work in Delphi5.

Answer 7

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 8

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 9

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 10

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.

Answer 11

Sqlite is pretty much single user/single connection only. Doing an update locks the entire database. You deal with this by setting a timeout value for other connections to wait (default is no timeout). Multi-user access can become very slow and/or give frequent timeout depending on what value you use.

I have used the Asqlite components from http://www.aducom.com/. Free and Open source. Doesn't currently support d2009 by the looks, but it is under development. There are a number of other component sets as well but I haven't used any other.

You can add full text searching to any delphi app using Rubicon (now under new management at http://www.href.com/rubicon). However this is not free :(

Answer 12

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 13

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 14

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 :( )