[Harpo]

Saludos!
Les traigo una pequeña demo de cómo usar una característica de SQLite: Full-Text Search (FTS5). Iba a contestar al mensaje de Gambafeliz sobre filtrar acentos y pensé que quizá ya estuviera este tema expuesto en el foro antiguo, no encontré nada al respecto. Seguramente los veteranos ya conozcan esta característica, para los neófitos será algo nuevo. Y cómo lo mejor es un ejemplo práctico aquí tienen una demo básica para empezar con el tema.

¿Qué es FTS5?
En un módulo interno de SQLite que permite realizar búsquedas "Full-Text" mediante la creación de tablas virtuales. Este tipo de búsquedas ignora acentos, mayúsculas ... se lo traga casi todo. Aquí tienen un par de enlaces:
SQLite FTS5
Full-Text Search Wikipedia

Para los que no quieran descargar la demo:
Para usar FTS5 hay que crear una tabla virtual:

SQL

CREATE VIRTUAL TABLE FTS5_Direcciones USING FTS5(Autonomia,Provincia,Poblacion, CodPostal, Direccion)

La tabla contendrá los campos de texto sobre los que queremos buscar.

Después la cargamos desde una fuente de datos estándar, en este caso una vista que recoge datos de otras tablas:

SQL

INSERT INTO FTS5_Direcciones(Autonomia, Provincia, Poblacion, CodPostal, Direccion) SELECT Autonomia, Provincia, Poblacion, CodPostal, Direccion FROM Direcciones

Por último, podemos lanzar las consultas en formato FTS5 que tienen su particularidad:

SQL

SELECT * FROM FTS5_Direcciones WHERE FTS5_Direcciones MATCH 'sierra OR (fuente NOT madrid)' ORDER BY RANK

La demo.
Antes de nada, el programa es un "hijo putativo"   de una aplicación propia que utilizo para trastear con bases de datos, le he quitado lo irrelevante para el caso y limpiado, pero no tiene control de errores ni comentarios.... no debería darles ningún problema.



En la imagen:
A la izquierda hay un GridView con información de la conexión y recoge el tamaño de la base de datos SQLite, tema relevante porque verán que esta engorda cual luchador de sumo, aunque no es un problema. Debajo un Log de lo que se va ejecutando con el tiempo empleado y el SQL ejecutado.

A la derecha empezando por arriba:

• La primera linea contiene botones para ejecutar la demo (pasos 1 a 4), otro para borrar la tabla virtual FTS5, otro que muestra las tablas en la base de datos y el que cierra la aplicación.
  
• La segunda es un Combobox que una vez completados los 4 pasos contiene sentencias SQL Full-Text de ejemplo para ver las posibilidades.
  
• La tercera es TextArea donde se cargan las sentencias SQL, se pueden modificar y volver a lanzar. Todas las sentencias que ejecuta la demo pasan por aquí. A su lado hay 3 botones: ejecutar el SQL, limpiar el TextArea, limpiar el GridView. Si pulsan Enter en TextArea ejecuta la sentencia. Si hacen doble click sobre una linea del Log se vuelve a cargar el SQL aquí.
  
• Por último un GridView con los resultados.
  

Todo, o casi todo, tiene un tooltip explicativo.

Los pasos a dar:

1. Comprobar que nuestro SQLite tiene FTS5 habilitado, en la imagen se puede ver en la linea 8. En el LOG se ve la versión de SQLite que estamos utilizando.
   
2. Crear y cargar los datos. Hubiera subido la base de datos que uso para pruebas, son datos de direcciones en España: Autonomía - Provincia - Población - Código Postal - Dirección. Pero hay una limitación de tamaño en lo que se puede subir al foro. Los datos van en un CSV con algo más de 100.000 registros, se puede limitar pero así se puede ver el rendimiento en las búsquedas. Ojo que este paso tarda sus buenos 15 minutos.
   
3. Crear la tabla virtual para las búsquedas FTS5. Si pulsan el botón "Tables" verán lo que se crea en la base de datos.
   
4. Cargar la tabla virtual con los datos del CSV. Estos dos últimos pasos son rápidos, no llegan a un 1 segundo a pesar de los 100.000 registros.
   

Una vez terminados los 4 pasos se carga el Combobox con búsquedas de prueba predefinidas, para trastear con ellas. Verán que la base de datos va engordando a medida que se ejecutan. Con el botón de "Remove" se puede borrar la tabla virtual y repetir los pasos 4 y 5.

El resultado:


Espero que les sirva para sus proyectos, para mi es muy útil.
Un saludo.

[Shordi]

¡Buen aporte!

[gambafeliz]

Muchas gracias Harpo por tu gran esfuerzo. Y por ayudarme en la distancia, claro

Lo miraré como un buen alumno y no descartes que te pregunte como un niño en sus comienzos en el cole.

Saludos, joven.

[Harpo]

Muchas gracias Harpo por tu gran esfuerzo. Y por ayudarme en la distancia, claro

Lo miraré como un buen alumno y no descartes que te pregunte como un niño en sus comienzos en el cole.

Saludos, joven.

De nada caballero.

Unas cosillas a tener en cuenta:

• Las tablas virtuales FTS5 son eso, tablas. Si inserta nuevos registros en la base de datos es necesario recargarlas antes de las búsquedas. Así que es necesario tener una política en este tema, no es lo mismo buscar llamadas de una empresa de telemarketing que facturas. La primera quizás sea necesario actualizarla dos veces al día mínimo, la segunda una vez al mes.
  
• La base de datos crece, aunque no es normal tener tablas de 100.000 registros las recargas van dejando basura. Conviene tener una política de limpieza y hacer copias periódicas. En mi caso hago una copia diaria de la base de datos, la copia del viernes la limpio y verifico, si es correcta borro las copias del lunes al jueves. El último viernes de mes si todo es correcto borro las copias del resto de viernes del mes. Esa copia mensual la guardo como si fuera antimateria
  
• Si va a jugar con las búsquedas en FTS5 le recomiendo usar un juego de datos pequeño (100-200 registros) que conozca a la perfección. Así sabrá exactamente qué está buscando y podrá comprobar si la búsqueda que lanza hace exactamente lo que usted quiere, tendrá un mejor conocimiento del funcionamiento de FTS5. Si busca 'maria AND lopez' obtendrá todas las María que se apelliden López, tengan o no nombres compuestos, acentos, o mayúsculas, y lo mismo para los apellidos. Pero no encontrará a las 'Mariajo'  o a las 'Lopezón'. Por cierto, el 'AND' tiene que ir en mayúsculas, en minúsculas forma parte de la cadena a buscar.
  

Le dejo algunos enlaces que pueden serle de interés:
Full Text query syntax
Pragma integrity check
Vacuum


Un saludo.

[gambafeliz]

He estado pensando sobre FTS5 y tras como me lo cuentas, he pensado que es poco practico. ¿estoy equivocado?, ¿ves realmente un ejemplo donde es practico?

Te lo digo por que mantener las tablas y mantener una tabla virtual para obtener datos ambiguos como "Clinica" y Clínica" no se si compensa este método. Diría que se puede contar con los dedos de la mano quien lo usa en real a nivel incluso mundial. No se yo. ¿En fin que opinas al respecto?

Otra cosa me ha gustado tu código, gracias, ya que viendo código real de unos y de otros, me enriqueces muchísimo técnicamente. Incluso he visto algo en tu código que pensaba que Gambas ni lo hacia, tengo que probarlo, pero si funciona, sigo diciendo que Gambas es maravilloso. Gracias.

[Harpo]

He estado pensando sobre FTS5 y tras como me lo cuentas, he pensado que es poco practico. ¿estoy equivocado?, ¿ves realmente un ejemplo donde es practico?

Te lo digo por que mantener las tablas y mantener una tabla virtual para obtener datos ambiguos como "Clinica" y Clínica" no se si compensa este método. Diría que se puede contar con los dedos de la mano quien lo usa en real a nivel incluso mundial. No se yo. ¿En fin que opinas al respecto?

Otra cosa me ha gustado tu código, gracias, ya que viendo código real de unos y de otros, me enriqueces muchísimo técnicamente. Incluso he visto algo en tu código que pensaba que Gambas ni lo hacia, tengo que probarlo, pero si funciona, sigo diciendo que Gambas es maravilloso. Gracias.

Buenos días!
Si que se ha desanimado pronto, hombre de poca fe!. En mi mensaje le decía "cosillas" a tener en cuenta. Le matizo lo que dije:

Siempre hay que tener en cuenta la naturaleza de los datos que manejamos, de ahí que puede ser necesario recargar las tablas FTS5 con más o menos frecuencia, pero será raro que estas tengan un gran volumen de datos. En el caso más extremo, utilice una base de datos SQLite en memoria, al abrir el formulario de búsqueda cree la tabla FTS5 en ella y hace la carga, al cerrar la conexión se borra la base de datos en memoria. Yo al abrir las bases de datos SQLite añado la siguiente linea:

GAMBAS

With modDB.hConn
      If Not .Opened Then .Open()
      'Attach memory database
      .Exec("ATTACH DATABASE ':memory:' AS 'mem'")
End With

Ya tiene usted una nueva base de datos lista para funcionar, en la misma conexión. La que reside en fichero es "main", la que reside en memoria "mem", si una tabla existe en ambos esquemas, y no especifica cual quiere usar, Gambas siempre leerá de "main". De hecho ni siquiera aparece en Connection.Databases, y tampoco Connection tiene un método para hacer el "attach".

Para crear la tabla FTS5 en memoria:

SQL

CREATE VIRTUAL TABLE mem.FTS5_mitabla USING FTS5( campo1, campo2, campo3)

Crear una tabla y cargarle 5.000 registros tarda milisegundos. Si cierra la conexión desaparece. Ya no tiene problemas.

Para que vea un caso práctico le subo una variante de la demo, usa una base de datos en memoria solamente, y algún otro cambio.



Si se fija arriba a la derecha verá que la base de datos es ":memory:", el programa no usa base de datos en disco. Y en el log puede ver varias cosas:

• Cargar los datos del CSV a SQLite ahora tarda 6 segundos en vez de 15 minutos, aunque tiene truco.
  
• La tabla FTS5 solo tiene dos campos, un ID que no indexa y un campo Dirección. Eso es porque el proceso de carga no separa las columnas del CSV, carga toda la linea en un solo campo (ahí está el truco). El proceso de separar las columnas lo realiza el GridView. Quería comprobar que FTS5 arroja los mismos resultados teniendo 4 campos o toda la información en uno solo.
  

También hay un nuevo botón, "Refresh". Borra los datos de búsqueda y vuelve a cargarlos, En total un poco más de un segundo para 100.000 registros, eso hace factible refrescar la tabla de búsqueda cada vez que abre el Form. Para probar esta versión solo tiene que copiar el fichero CSV del ejemplo anterior al directorio de esta, no hace falta la base de datos.

Para terminar, creo que este tipo de búsquedas cubre una necesidad específica y lo hace bien, a mí me es muy útil. Sobre si tiene uso y quién lo usa haga una cosa, coja su teléfono móvil, abra Whatsapp y pulse en la lupa de arriba, es la búsqueda. Teclee algo común en las conversaciones, como "hola" o "buenos días" y observe el resultado. Teniendo en cuenta que Whatsapp usa Sqlite yo juraría que si no es FTS5 lo han clonado. Teniendo en cuenta el número de usuarios de esa aplicación, va a resultar que FTS5 de Sqlite es la herramienta Full-Text Search para bases de datos más utilizada del mundo.

Ya me contará que hago en mi código que usted pensaba que no se podía hacer, me ha dejado intrigado.
Un saludo.

[gambafeliz]

Pues chico, te lo voy a resumir, "me has dejado con las patas colgando"

Que máquina ere, macho....

[Harpo]

Tampoco es para tanto

[gambafeliz]

Macho, es que soy muy exagerado, ya sabes es broma. Bueno gracias, saludos.

[Harpo]

Sigue sin ser para tanto, como dijo alguien una vez: Todos somos ignorantes, pero no todos ignoramos lo mismo.
Un saludo.