在Android的全文搜索示例 [英] Full text search example in Android

问题描述

我有一个很难理解如何使用全文搜索（FTS）与Android。我读过的FTS3和FTS4扩展的 SQLite的文档。我知道这是可以做到在Android 。不过，我有一个很难找到，我可以COM prehend任何例子。 我不明白这一点。

I'm having a hard time understanding how to use full text search (FTS) with Android. I've read the SQLite documentation on the FTS3 and FTS4 extensions. And I know it's possible to do on Android. However, I'm having a hard time finding any examples that I can comprehend. I don't understand this one.

一个SQLite数据库表（名为 example_table ）有4列。然而，有需要被编入索引的全文检索只有一列（名为 text_column ）。 text_column 的每一行包含文本，从0到1000字的篇幅不同。行的总数大于10,000。

A SQLite database table (named example_table) has 4 columns. However, there is only one column (named text_column) that needs to be indexed for a full text search. Every row of text_column contains text varying in length from 0 to 1000 words. The total number of rows is greater than 10,000.

• 你将如何设置表和/或FTS虚拟表？
• 您将如何对 text_column ？
执行FTS查询

• How would you set up the table and/or the FTS virtual table?
• How would you perform an FTS query on text_column?

补充说明：

• 由于只有一列需要建立索引，只使用FTS表（和删除 example_table ）会的低效的非FTS查询。
• 对于这样一个大表，存储 text_column 的重复条目在FTS表是不可取的。 这个帖子建议使用的外部内容表。
• 在外部内容表使用FTS4，但FTS4是之前的Andr​​oid API 11 不支持 。的回答可以假设一个API> = 11，但评论的选项支持较低的版本将是有益的。
• 更改数据在原始表不会自动更新的FTS表（反之亦然）。包括触发的在答案是没有必要的这个基本的例子，但将是有益的仍然。

• Because only one column needs to be indexed, only using an FTS table (and dropping example_table) would be inefficient for non-FTS queries.
• For such a large table, storing duplicate entries of text_column in the FTS table would be undesirable. This post suggests using an external content table.
• External content tables use FTS4, but FTS4 is not supported before Android API 11. An answer can assume an API >= 11, but commenting on options for supporting lower versions would be helpful.
• Changing data in the original table does not automatically update the FTS table (and vice versa). Including triggers in your answer is not necessary for this basic example, but would be helpful nonetheless.

推荐答案

我用下面的普通的SQL让一切都清晰易读越好。在你的项目，你可以使用Android方便的方法。下面使用的分贝对象是<一个实例href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html">SQLiteDatabase.

Most Basic Answer

I'm using the plain sql below so that everything is as clear and readable as possible. In your project you can use the Android convenience methods. The db object used below is an instance of SQLiteDatabase.

创建FTS表

db.execSQL("CREATE VIRTUAL TABLE fts_table USING fts3 ( col_1, col_2, text_column )");

这能去的的onCreate（）方法的扩展 SQLiteOpenHelper 类。

This could go in the onCreate() method of your extended SQLiteOpenHelper class.

填充FTS表

db.execSQL("INSERT INTO fts_table VALUES ('3', 'apple', 'Hello. How are you?')");
db.execSQL("INSERT INTO fts_table VALUES ('24', 'car', 'Fine. Thank you.')");
db.execSQL("INSERT INTO fts_table VALUES ('13', 'book', 'This is an example.')");

这将是更好地使用<一个href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#insert%28java.lang.String,%20java.lang.String,%20android.content.ContentValues%29">SQLiteDatabase#insert或 prepared声明，而非 execSQL 。

查询FTS表

String[] selectionArgs = { searchString };
Cursor cursor = db.rawQuery("SELECT * FROM fts_table WHERE fts_table MATCH ?", selectionArgs);

您也可以使用<一个href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#query%28java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String[],%20java.lang.String,%20java.lang.String,%20java.lang.String%29">SQLiteDatabase#query方法。注意 MATCH 关键字。

You could also use the SQLiteDatabase#query method. Note the MATCH keyword.

虚拟FTS表中有与它的问题。每一列是索引，但是这是一种浪费的空间和资源，如果某些列不需要被索引。这需要一个FTS指数仅有的一列可能是 text_column 。

The virtual FTS table above has a problem with it. Every column is indexed, but this is a waste of space and resources if some columns don't need to be indexed. The only column that needs an FTS index is probably the text_column.

要解决这个问题，我们将使用常规表和虚拟FTS表的组合。该FTS表将包含从常规表中的实际数据的索引，但没有。相反，它会产生一个连接到常规的表的内容。这就是所谓的外部内容表。

To solve this problem we will use a combination of a regular table and a virtual FTS table. The FTS table will contain the index but none of the actual data from the regular table. Instead it will have a link to the content of the regular table. This is called an external content table.

创建的表

db.execSQL("CREATE TABLE example_table (_id INTEGER PRIMARY KEY, col_1 INTEGER, col_2 TEXT, text_column TEXT)");
db.execSQL("CREATE VIRTUAL TABLE fts_example_table USING fts4 (content='example_table', text_column)");

请注意，我们必须使用FTS4要做到这一点，而不是FTS3。 FTS4前API版本11中不支持Android的你既可以（1）仅适用于API> = 11，或（2）使用FTS3表（但这意味着数据库会大一些，因为全文列存在提供搜索功能在这两个数据库）。

Notice that we have to use FTS4 to do this rather than FTS3. FTS4 is not supported in Android before API version 11. You could either (1) only provide search functionality for API >= 11, or (2) use an FTS3 table (but this means the database will be larger because the full text column exists in both databases).

填充表

db.execSQL("INSERT INTO example_table (col_1, col_2, text_column) VALUES ('3', 'apple', 'Hello. How are you?')");
db.execSQL("INSERT INTO example_table (col_1, col_2, text_column) VALUES ('24', 'car', 'Fine. Thank you.')");
db.execSQL("INSERT INTO example_table (col_1, col_2, text_column) VALUES ('13', 'book', 'This is an example.')");

（同样，也有更好的方法做插入比 execSQL 。我只是用它为它的可读性。）

(Again, there are better ways in do inserts than with execSQL. I am just using it for its readability.)

如果您现在尝试做一个FTS查询 fts_example_table 你会得到任何结果。其原因是，改变一个表不自动改变其他表。您必须手动更新FVS表：

If you tried to do an FTS query now on fts_example_table you would get no results. The reason is that changing one table does not automatically change the other table. You have to manually update the FVS table:

db.execSQL("INSERT INTO fts_example_table (docid, text_column) SELECT _id, text_column FROM example_table");

（将 DOCID 就像 ROWID 的普通表。）您必须确保更新FTS表（以便它可以更新索引），每次你做出改变（INSERT，DELETE，UPDATE）的外部内容表的时间。这会很麻烦。如果你只是做一个prepopulated数据库，你可以做

(The docid is like the rowid for a regular table.) You have to make sure to update the FTS table (so that it can update the index) every time you make a change (INSERT, DELETE, UPDATE) to the external content table. This can get cumbersome. If you are only making a prepopulated database, you can do

db.execSQL("INSERT INTO fts_example_table(fts_example_table) VALUES('rebuild')");

这将重建整个表。这可能是缓慢的，虽然如此，这是不是你想要的每一个小变化后，做一些事情。在外部内容表中完成所有的插入后，您会怎么做。如果你确实需要自动保持数据库的同步，您可以使用触发器。 去这里和向下滚动一点点地找到方向。

which will rebuild the whole table. This can be slow, though, so it is not something you want to do after every little change. You would do it after finishing all the inserts on the external content table. If you do need to keep the databases in sync automatically, you can use triggers. Go here and scroll down a little to find directions.

查询数据库

String[] selectionArgs = { searchString };
Cursor cursor = db.rawQuery("SELECT * FROM fts_example_table WHERE fts_example_table MATCH ?", selectionArgs);

这是和以前一样，但这个时候你只能访问 text_column （和 DOCID ）。如果你需要得到外部表的内容从其他列的数据？因为 DOCID 的FTS表匹配 ROWID （在这种情况下 _id ），您可以使用连接。 （感谢这个答案以帮助这一点。）

This is the same as before, except this time you only have access to text_column (and docid). What if you need to get data from other columns in the external content table? Since the docid of the FTS table matches the rowid (and in this case _id) of the external content table, you can use a join. (Thanks to this answer for help with that.)

String sql = "SELECT * FROM example_table WHERE _id IN " +
        "(SELECT docid FROM fts_example_table WHERE fts_example_table MATCH ?)";
String[] selectionArgs = { searchString };
Cursor cursor = db.rawQuery(sql, selectionArgs);

进一步阅读

通过这些文件去仔细看使用FTS虚拟表的其他方式：

Further Reading

Go through these documents carefully to see other ways of using FTS virtual tables:

• SQLite的FTS3和FTS4扩展（SQLite的文档）
• 存储和搜索数据（Android的文档）
• Android的快速提示：使用SQLite FTS表（博客）

• SQLite FTS3 and FTS4 Extensions (SQLite docs)
• Storing and Searching for Data (Android docs)
• Android Quick Tip: Using SQLite FTS Tables (blog)

• 设置操作符（AND，OR，NOT）在SQLite的FTS查询有标准查询语法和的增强的查询语法。不幸的是，Android的显然不支持增强的查询语法（见这里，< A HREF =htt​​p://stackoverflow.com/questions/17132597/android-sqlite-fts-not-operation>此处，的这里和这里）。这意味着，混合AND和OR变得困难（需要使用 UNION的 或检查 PRAGMA compile_options 似乎）。非常不幸的。请添加评论，如果有这方面的更新。

• Set operators (AND, OR, NOT) in SQLite FTS queries have Standard Query Syntax and Enhanced Query Syntax. Unfortunately, Android apparently does not support the Enhanced Query Syntax (see here, here, here, and here). That means mixing AND and OR becomes difficult (requiring the use of UNION or checking PRAGMA compile_options it seems). Very unfortunate. Please add a comment if there is an update in this area.

这篇关于在Android的全文搜索示例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！