Full-text SQLite query configuration to use

SQLite is a lightweight database and an acid-compliant associated database management system. It is designed to be embedded and has been used in many embedded products, it occupies very low resources. In embedded devices, it may only need several hundred KB of memory. It supports mainstream operating systems such as Windows, Linux, and UNIX, and can be combined with many programming languages, such as TCL, PHP, Java, and ODBC interfaces, similar to MySQL and PostgreSQL, the two world-renowned open-source database management systems, the processing speed is faster than that of them.

Google has contributed some resources to SQLite for help. Full-text retrieval is implemented for the first time in version 3.3.8. This version provides the function to create a virtual table dependent on external extensions: here, the full-text search algorithm can be used for text columns in any virtual table. In PHP 5.3.0, the corresponding support is only activated by the default PDO and sqlite3. Earlier versions of PHP can use the sqlite3 extension library of PECL.

A search index is usually created as follows:

• Break down text into tokens.

• Convert to lowercase letters.

• Determine the root word.

• Create an index.

Set everything

By default, SQLite provides two basic word divider: simple and Porter. They can control how words are separated. Simple splits text into different marks based on space and punctuation. Porter is designed for English use. It can extend a large amount of text to the basic form. For example, condolidate, consolidated, and consolidating are converted into consolid.

Unfortunately, SQLite has not canceled the deprecated term. For example, the, of, and to are still in the index. This greatly expands the scope of the index and slows down the search speed. The simplest solution is to manually remove the deprecated word before pressing the confirm search.

First build a test environment. We have compiled and installed sqlite-amalgamation-3.6.18.tar.gz in the Linux system.

Download the latest SQLite version

$ Wget http://www.sqlite.org/sqlite-amalgamation-3.6.18.tar.gz

Extract

$ Tar zxvf sqlite-amalgamation-3.6.18.tar.gz

$ CD sqlite-3.6.18/

Configure and compile SQLite
$ Cflags = "-dsqlite_enable_fts3 = 1"./configure

$ Make

$ Make install

Complete

View version
$ Sqlite3 -- version

Create an SQLite database file
$ Sqlite3 dbdate. DB
SQLite version 3.6.18
Enter ". Help" for instructions
Enter SQL statements terminated with ";"

Create a virtual table
SQLite> create virtual table recipe using fts3 (name, ingredients );

Insert record information

SQLite> insert into recipe values ('broccoli stew', 'fig fig ');

SQLite> insert into recipe values ('pumpkin stew', 'pumpkin onions garlic celery ');

SQLite> insert into recipe values ('broccoli Pie', 'broccoli cheese onions flour ');

SQLite> insert into recipe values ('pumpkin Pie', 'pumpkin sugar flour butter ');

Full-text Query

Select * From recipe where recipe match 'onions cheese ';
Select * From recipe where recipe match 'onions or cheese ';
Select * From recipe where recipe match 'name: Stew ingredients: onions ';
Select * From recipe where recipe match '"green onions "';
Select * From recipe where ingredients match 'onions-cheese ';
Select * From recipe where recipe match 'onions-cheese ';
Select * From recipe where recipe match 'Bu * '; select docid from recipe as RA where Ra match 'stew'; select docid from recipe as RA where recipe match 'stew '; select docid from recipe where recipe match 'ononons' and recipe match 'chees ';
Select docid from recipe where recipe match 'onions cheese '; try the query results by yourself.

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/wannet/archive/2009/09/16/4557311.aspx