Full-text SQLite query configuration to use

Source: Internet
Author: User
Tags sqlite query

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.