Ejemplo de búsqueda de texto completo en Android

Me está costando entender cómo usar la búsqueda de texto completo (FTS) con Android. He leído la documentación de SQLite en las extensiones FTS3 y FTS4 . Y sé que es posible hacerlo en Android . Sin embargo, estoy teniendo dificultades para encontrar ejemplos que pueda comprender.

El modelo básico de la base de datos

Una tabla de base de datos SQLite (llamada example_table ) tiene 4 columnas. Sin embargo, solo hay una columna (llamada text_column ) que debe indexarse ​​para una búsqueda de texto completo. Cada fila de text_column contiene texto que varía en longitud de 0 a 1000 palabras. El número total de filas es mayor que 10,000.

  • ¿Cómo configuraría la tabla y / o la tabla virtual FTS?
  • ¿Cómo realizaría una consulta FTS en text_column ?

Notas adicionales:

  • Debido a que solo se necesita indexar una columna, solo usar una tabla FTS (y descartar la tabla de example_table ) sería ineficiente para las consultas que no sean FTS .
  • Para una tabla tan grande, almacenar las entradas duplicadas de text_column en la tabla FTS sería indeseable. Esta publicación sugiere usar una tabla de contenido externo .
  • Las tablas de contenido externo usan FTS4, pero FTS4 no es compatible con Android API 11 . Una respuesta puede suponer una API> = 11, pero sería útil comentar las opciones para soportar versiones más bajas.
  • El cambio de datos en la tabla original no actualiza automáticamente la tabla FTS (y viceversa). Incluir los factores desencadenantes en su respuesta no es necesario para este ejemplo básico, pero sería útil, no obstante.

La respuesta más básica

Estoy usando el sql simple a continuación para que todo sea lo más claro y legible posible. En su proyecto, puede usar los métodos de conveniencia de Android. El objeto db utilizado a continuación es una instancia de SQLiteDatabase .

Crear tabla FTS

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

Esto podría ir en el método onCreate() de su clase SQLiteOpenHelper extendida.

Rellene la tabla 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.')"); 

Sería mejor usar SQLiteDatabase # inserción o declaraciones preparadas que execSQL .

Consultar tabla FTS

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

También puede usar el método de consulta SQLiteDatabase # . Tenga en cuenta la palabra clave MATCH .

Respuesta más completa

La tabla FTS virtual anterior tiene un problema. Cada columna está indexada, pero esto es una pérdida de espacio y recursos si algunas columnas no necesitan ser indexadas. La única columna que necesita un índice FTS es probablemente la text_column .

Para resolver este problema, utilizaremos una combinación de una tabla regular y una tabla FTS virtual. La tabla FTS contendrá el índice pero ninguno de los datos reales de la tabla normal. En cambio, tendrá un enlace al contenido de la tabla normal. Esto se llama tabla de contenido externo .

enter image description here

Crea las tablas

 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)"); 

Tenga en cuenta que tenemos que usar FTS4 para hacer esto en lugar de FTS3. FTS4 no es compatible con Android antes de la versión 11 de la API. Usted podría (1) solo proporcionar funcionalidad de búsqueda para API> = 11, o (2) usar una tabla FTS3 (pero esto significa que la base de datos será más grande porque existe la columna de texto completo en ambas bases de datos).

Rellene las tablas

 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.')"); 

(Una vez más, hay mejores formas de hacer inserciones que con execSQL . Solo lo estoy usando para su legibilidad).

Si intentas hacer una consulta FTS ahora en fts_example_table , no obtendrás resultados. La razón es que cambiar una tabla no cambia automáticamente la otra tabla. Tienes que actualizar manualmente la tabla FTS:

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

(El docid es como el rowid de una tabla normal). rowid asegurarse de actualizar la tabla FTS (para que pueda actualizar el índice) cada vez que realice un cambio (INSERT, DELETE, UPDATE) en la tabla de contenido externo. . Esto puede ser engorroso. Si solo está haciendo una base de datos prepoblada, puede hacer

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

que reconstruirá toda la tabla. Sin embargo, esto puede ser lento, por lo que no es algo que quieras hacer después de cada pequeño cambio. Lo haría después de terminar todas las inserciones en la tabla de contenido externo. Si necesita mantener las bases de datos sincronizadas automáticamente, puede usar desencadenantes . Ve aquí y baja un poco para encontrar direcciones.

Consulta las bases de datos

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

Es lo mismo que antes, excepto que esta vez solo tiene acceso a text_column (y docid ). ¿Qué sucede si necesita obtener datos de otras columnas en la tabla de contenido externo? Como el docid de la tabla FTS coincide con el rowid (y en este caso _id ) de la tabla de contenido externo, puede usar una combinación. (Gracias a esta respuesta por su ayuda con eso).

 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); 

Otras lecturas

Consulte detenidamente estos documentos para ver otras formas de utilizar tablas virtuales FTS:

  • Extensiones SQLite FTS3 y FTS4 (documentos SQLite)
  • Almacenamiento y búsqueda de datos (documentos de Android)

Notas adicionales

  • Los operadores de conjunto (AND, OR, NOT) en consultas SQLite FTS tienen la syntax de consulta estándar y la syntax de consulta mejorada . Lamentablemente, Android aparentemente no es compatible con la syntax de consulta mejorada (ver aquí , aquí , aquí y aquí ). Eso significa que mezclar AND y OR se vuelve difícil (lo que requiere el uso de UNION o la comprobación de PRAGMA compile_options parece). Muy desafortunado. Por favor, agregue un comentario si hay una actualización en esta área.

No olvide utilizar contenido de para reconstruir la tabla fts.

Lo hago con un desencadenador de actualización, insertar, eliminar