Foswiki is getting SQL RDBMS support

For a long time we've talked about stitching a relational database into Foswiki, mainly to speed up query searches. To keep things simple Foswiki uses a text database, which stores topics in lots of individual files. By default searches are implemented by searching every one of these files for matching text. This isn't as bad as it sounds; searching is quite fast, but doesn't scale up terribly well, so people have coupled in various full-text search engines, such as Kinosearch, to accelerate text searches. However there's been very little successful work yet on speeding up query searches – which is a shame, because query searches are heavily used in most wiki applications. The problem has always been that it's difficult to map from a Foswiki search query to any kind of structured search language, such as SQL.

One approach (championed by WikiRing member Sven Dowideit) has been to support a wider range of queries in the text search engine. Using the code I wrote to map query searches to regular expressions, Sven has been able to couple Foswiki to the MongoDB database, with some pretty impressive results. This is already available as the MongoDBPlugin. Other approaches have tried to map Foswiki queries to other database search models, such as XQuery.

SQL is still the most mature query language, and the RDBMS that support it are among some of the most mature and robust software on the planet. It's important that Foswiki has solutions that fall into that comfort zone. To that end I have implemented a new module, the DBIStoreContrib, that maps from the Foswiki query language to SQL. This allows us to use many of the existing RDBMS, such as MySQL, SQLite and PostgreSQL, to accelerate queries. By caching topics into the database in parallel to the text database, we avoid the risk of data loss that has made people nervous of other wiki solutions that use RDBMS, such as Mediawiki, while still maximising search performance.

It's early days for this module, and there's still lots to do to optimise how it works, so watch this space! If you are interested, and have a development environment, then you can check out the DBIStoreContrib from the Foswiki Subversion trunk.

Foswiki – the very best wiki application platform – is taking another giant leap forward….