SQLite has the ability to do full text searching and this feature is available with Xojo. Read on to learn how to best use it!
What is full text searching? It is a fast way to look for specific words in text columns of a database table. Without full text searching, you would typically search a text column using the LIKE command. For example, you might use this command to find all books that have “cat” in the description:
SELECT Title FROM Book WHERE Desc LIKE '%cat%";
But this select actually finds row that has the letters “cat” in it, even if it is in another word, such as “cater”. Also, using LIKE does not make use of any indexing on the table. The table has to be scanned row by row to see if it contains the value, which can be slow for large tables.
Full text seach is a way to avoid these two issues. With SQLite, you enable full text search by creating what is called a “virtual table” using the fts4 engine. This is done like this:
CREATE VIRTUAL TABLE BookSearch USING fts4(id, Title, Desc);
This creates a “fake” table that is hooked up to the full text search engine. You can now populate this table with the data you want to search, usually copying it from data in a normal table:
INSERT INTO BookSearch SELECT id, Title, Desc FROM Book;
With the data in place, you are now able to search it using a SELECT statement with the special MATCH keyword. For example, this searches for all books that have the word “cat” in the description:
SELECT id, Title FROM BookSearch WHERE Desc MATCH 'cat';
That is pretty much all you need to know to get started with full text searching, but here are a couple other tips. You can do wildcard searching with the “*” character. This searches the column for all text that starts with “prog”, so it will find “program”, “progress”, etc:
SELECT id, Title FROM BookSearch WHERE Desc MATCH 'prog*';
And you can also search all the columns in the FTS table by using the table name itself as a column in the WHERE clause. This searches Title and Desc for “cat”:
SELECT id, Title FROM BookSearch WHERE BookSearch MATCH 'cat';
There are even more advanced search capabilities you can perform, which you can read about here: http://www.sqlite.org/fts3.html
Want to try it out? Download Xojo – it’s free for development and testing and give it a shot!
