Article Preview

COLUMN

Searching SQLite

Using SQLite's powerful full-text-search feature

Issue: 11.4 (July/August 2013)
Author: Seth Verrinder
Author Bio: Seth is a consultant who uses Real Studio every day in his job at BKeeney Software, Inc.
Article Description: No description available.
Article Length (in bytes): 5,516
Starting Page Number: 66
Article Number: 11408
Related Web Link(s):

http://www.sqlite.org/fts3.html

Excerpt of article text...

A couple of issues back I talked about using REALSQLDatabase (note that this has been replaced by SQLiteDatabase in Xojo) as a file format for applications. In this issue I'm going to look at one of the less known but very powerful features of SQLite called Full Text Search.

One project that I worked on had a library of thousands of documents where most of them contained more than ten pages of text. We needed to be able to search for a words or phrases and get a list of all the documents that contained them. The first attempt scanned through every file each time the user did a search, which worked but it could take minutes on a large library. Using SQLite's full text search we were able to perform the same search in a fraction of a second.

This article is a brief overview of how full text search works and an example of how to set it up using REALSQLDatabase.

How it Works

The basic idea of full text search is that it creates an index that let's it efficiently perform searches. When you add a document, SQLite breaks it up into words and for each word it stores a list of documents that contain that word. Then, when you search for a word, it can look up the word very quickly in it's index and find the list of documents that contain it.

...End of Excerpt. Please purchase the magazine to read the full article.