Пример полнотекстового поиска в Android

Question

Мне трудно понять, как использовать полнотекстовый поиск (FTS) с Android. Я прочитал документацию SQLite по расширениям FTS3 и FTS4. И я знаю, что это можно сделать на Android. Однако мне трудно найти примеры, которые я могу понять.

Основная модель базы данных

Таблица базы данных SQLite (с именем example_table) имеет 4 столбца. Тем не менее, есть только один столбец (названный text_column), который должен быть проиндексирован для полнотекстового поиска. Каждый ряд text_column содержит текст длиной от 0 до 1000 слов. Общее количество строк превышает 10000.

• Как бы вы создали таблицу и / или виртуальную таблицу FTS?
• Как бы вы выполнили запрос FTS на text_column?

Дополнительные примечания:

• Поскольку нужно индексировать только один столбец, используя только таблицу FTS (и отбрасывая example_table) было бы неэффективно для запросов не FTS.
• Для такой большой таблицы, сохраняя дубликаты записей text_column в таблице FTS было бы нежелательно. Этот пост предлагает использовать таблицу внешнего контента.
• Таблицы внешнего контента используют FTS4, но FTS4 не поддерживается до Android API 11. Ответ может предполагать API >= 11, но было бы полезно прокомментировать варианты поддержки более низких версий.
• Изменение данных в исходной таблице не приводит к автоматическому обновлению таблицы FTS (и наоборот). Включение триггеров в ваш ответ не обязательно для этого базового примера, но, тем не менее, было бы полезно.

Answer 1

Самый простой ответ

Я использую простой SQL ниже, чтобы все было максимально понятно и читабельно. В вашем проекте вы можете использовать удобные методы Android. db Объект, используемый ниже, является экземпляром SQLiteDatabase.

Создать таблицу FTS

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

Это может пойти в onCreate() метод вашего расширенного SQLiteOpenHelper учебный класс.

Заполнить таблицу 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.')");

Было бы лучше использовать SQLiteDatabase# insert или подготовленные операторы, чем execSQL,

Запрос FTS Table

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

Вы также можете использовать метод SQLiteDatabase# query. Обратите внимание MATCH ключевое слово.

Более полный ответ

Виртуальная таблица FTS выше имеет проблемы с этим. Каждый столбец индексируется, но это пустая трата пространства и ресурсов, если некоторые столбцы не нужно индексировать. Единственный столбец, которому нужен индекс FTS, это, вероятно, text_column,

Для решения этой проблемы мы будем использовать комбинацию обычной таблицы и виртуальной таблицы FTS. Таблица FTS будет содержать индекс, но не фактические данные из обычной таблицы. Вместо этого он будет иметь ссылку на содержимое обычной таблицы. Это называется таблицей внешнего контента.

Создать таблицы

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 не поддерживается в Android до API версии 11. Вы можете либо (1) предоставить функцию поиска только для API >= 11, либо (2) использовать таблицу FTS3 (но это означает, что база данных будет больше, поскольку существует полнотекстовый столбец в обеих базах данных).

Заполните таблицы

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, Я просто использую его для удобства чтения.)

Если вы пытались сделать запрос FTS сейчас fts_example_table вы не получите никаких результатов. Причина в том, что изменение одной таблицы не приводит к автоматическому изменению другой таблицы. Вы должны вручную обновить таблицу FTS:

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

(The docid это как rowid для обычной таблицы.) Необходимо обязательно обновлять таблицу FTS (чтобы она могла обновлять индекс) каждый раз, когда вы вносите изменения (INSERT, DELETE, UPDATE) в таблицу внешнего содержимого. Это может стать громоздким. Если вы только делаете предварительно заполненную базу данных, вы можете сделать

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

который восстановит всю таблицу. Однако это может быть медленным, так что это не то, что вы хотите делать после каждого небольшого изменения. Вы сделаете это после завершения всех вставок во внешней таблице содержимого. Если вам нужно автоматически синхронизировать базы данных, вы можете использовать триггеры. Идите сюда и прокрутите немного вниз, чтобы найти дорогу.

Запрос к базам данных

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) таблицы внешнего контента, вы можете использовать соединение. (Благодаря этому ответу за помощь с этим.)

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:

• SQLite FTS3 и FTS4 Расширения (документы SQLite)
• Хранение и поиск данных (документы для Android)

Дополнительные примечания

• Операторы множеств (AND, OR, NOT) в запросах SQLite FTS имеют стандартный синтаксис запроса и расширенный синтаксис запроса. К сожалению, Android, очевидно, не поддерживает расширенный синтаксис запросов (см. Здесь, здесь, здесь и здесь). Это означает, что смешивание AND и OR становится трудным (требующим использования UNION или проверка PRAGMA compile_options похоже на то). Очень неудачно. Пожалуйста, добавьте комментарий, если есть обновление в этой области.

Answer 2

Не забывайте, когда используете контент для перестроения таблицы fts.

Я делаю это с помощью триггера на обновление, вставка, удаление