SQLite full search text USING fts5

Fabrizio_Cesare · August 12, 2020, 7:21am

Hi, i’m trying to implement a full search text using fts5 capabilities.
I’ve read the various tutorials and examples code.

(sorry if i don’t translate the part of the ‘italian’ code)

At the startup of the app i setup my virtual database:

Try
App.DBVirtual.Connect
Catch err as DatabaseException
MessageDialog.Show("Errore: " + err.Message)
Return
End Try

Try

App.DBVirtual.ExecuteSQL(“CREATE VIRTUAL TABLE cNotifiche USING fts5(ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato);”)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
App.DBVirtual.ExecuteSQL(“INSERT INTO cNotifiche SELECT ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato FROM Notifiche;”)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End Try

First question: if i modify my Notifiche table (in my SQLite ‘real’ database) i should modify accordingly also the virtual one, is it correct ? executing that query, isn’t it ?

App.DBVirtual.ExecuteSQL(“INSERT INTO cNotifiche SELECT ID, Pratica, Atto, Cronologico, Destinatario, Raccomandata, Giorno, Mese, Anno, Notificato, Duplicato FROM Notifiche;”)

When i digit in txt5Cerca.text in its text change event i insert:

If txt5Cerca.text <> “” Then
CercaArchivio
end If

And here’s my method CercaArchivio:

Var rs As RowSet
Var sql As String = “SELECT * FROM cNotifiche WHERE cNotifiche MATCH '” + txt5Cerca.Value + “';”
Try
rs = App.DBVirtual.SelectSQL(sql)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End Try

// Loop through each row, one-by-one, and add it to the ListBox.
If rs <> Nil Then
For Each row As DatabaseRow In rs
ListaNotifiche.AddRow(row.Column(“ID”).StringValue, row.Column(“Pratica”).StringValue, row.Column(“Atto”).StringValue, _
row.Column(“Cronologico”).StringValue, row.Column(“Giorno”).StringValue + “/” + row.Column(“Mese”).StringValue + “/” + row.Column(“Anno”).StringValue, row.Column(“Destinatario”).StringValue, row.Column(“Raccomandata”).StringValue), _
row.Column(“Notificato”).StringValue, row.Column(“Duplicato”).StringValue
ListaNotifiche.RowTagAt(ListaNotifiche.LastAddedRowIndex) = row.Column(“ID”).IntegerValue
Next

rs.Close
End If

Do you find some errors in that code ?
My ListaNotifiche not populates at all … stays always totally empty …

Beatrix_Willius · August 12, 2020, 8:14am

First try your query in an SQLEditor. As far as I can see you are missing the * around the match value. Here is my query for fts:

'prepare the prepared statement
dim PreparedForSearch as SQLPreparedStatementMBS = SQLiteIndexDBMBS.Prepare("SELECT docid FROM bodyindex WHERE messagebody MATCH ?") 
PreparedForSearch.BindType(0, PreparedForSearch.kTypeString)
SearchString = ReplaceAll(SearchString, """", """""")
dim data as RecordSet = PreparedForSearch.SQLSelectMT("'*'" + searchstring + "'*'")

If data <> Nil Then
  While Not data.EOF
    ResultIDs.AddRow data.Field("docid").StringValue
    data.MoveNext
  Wend
  data.Close
End If

Return ResultIDs

And a prepared statement is better than using the value directly.

Fabrizio_Cesare · August 12, 2020, 9:00am

I’ve added the * and the query is now:

SELECT * FROM cNotifiche WHERE cNotifiche MATCH ‘a*’;

what’s wrong ?
I’ve for sure cell in the table with ‘a’ but the listbox is still empty

Beatrix_Willius · August 12, 2020, 9:03am

You need 2 stars and not one. And make sure that you use the correct type of apostrophes.

Fabrizio_Cesare · August 12, 2020, 9:45am

“SELECT * FROM cNotifiche WHERE cNotifiche MATCH “’’" + ‘" + txt5Cerca.Value + "’ + "’’”;”

the query looks ok in an editor SQL, the apostrophes seems ok, but not works …

Sascha_S · August 12, 2020, 9:58am

What does the error message look like?

Markus_Winter · August 12, 2020, 10:32am

You are using a mix of curly quotes and straight quotes. Zoom in and you’ll see.

Don’t use curly quotes.

Fabrizio_Cesare · August 12, 2020, 10:33am

What should be the exact query to pass with double star as suggested by @Beatrix_Willius?

Sascha_S · August 12, 2020, 10:39am

Var strCercaValue As String = "*" + txt5Cerca.Value + "*"

Try

  rs = App.DBVirtual.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, strCercaValue)
  ...
Catch er As DatabaseError
  ...
End Try

Fabrizio_Cesare · August 12, 2020, 10:59am

Thanks !

Var rs As RowSet
Var strCercaValue As String = “" + txt5Cerca.Value + "”
Try
rs = App.DBVirtual.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, strCercaValue)
Catch err As DatabaseException
MessageDialog.Show("Errore DB: " + err.Message)
Return
End Try

// Loop through each row, one-by-one, and add it to the ListBox.
If rs <> Nil Then

For Each row As DatabaseRow In rs
ListaNotifiche.AddRow(row.Column(“ID”).StringValue, row.Column(“Pratica”).StringValue, row.Column(“Atto”).StringValue, _
row.Column(“Cronologico”).StringValue, row.Column(“Giorno”).StringValue + “/” + row.Column(“Mese”).StringValue + “/” + row.Column(“Anno”).StringValue, row.Column(“Destinatario”).StringValue, row.Column(“Raccomandata”).StringValue), _
row.Column(“Notificato”).StringValue, row.Column(“Duplicato”).StringValue
ListaNotifiche.RowTagAt(ListaNotifiche.LastAddedRowIndex) = row.Column(“ID”).IntegerValue
Next
rs.Close
End If

Why i receive this error ?
The database DBVirtual was correctly created, has a public property

Sascha_S · August 12, 2020, 11:01am

Because you are still working with curly quotes and even replaced the * with quotes…

Fabrizio_Cesare · August 12, 2020, 11:12am

No seems there’s something in the forum that changes the code when copy/paste…

But you’re right before SELECT i’ve the curly quotes !!!
Now fixed it but again not works:

Sascha_S · August 12, 2020, 11:26am

My fault.

To search all fields which contain an a use:

// No need for Var strCercaValue As String = "*" + txt5Cerca.Value + "*" in this case
rs = App.DBVirtual.SelectSQL(“SELECT * FROM cNotifiche WHERE cNotifiche MATCH ?”, txt5Cerca.Value)

To search for fields starting with a use:

Var strCercaValue As String = txt5Cerca.Value + "*"

Beatrix_Willius · August 12, 2020, 11:26am

Curly quotes are a really bad idea.

You are missing the field from cNotifiche in your SQL.

Fabrizio_Cesare · August 12, 2020, 11:40am

Just to understand: curly quotes are ‘created’ using OPTION 2 is it correct ?
@Sascha_S in either cases the search queries not givesresult.
At this point my bug/mistake is elsewhere … probably my virtualDB has no data correctly imported from my real database ?

I’ve inserted a msgbox here and it’s never reached …

For Each row As DatabaseRow In rs
msgbox “here”

And in fact i call this query:

rs = App.DBVirtual.SelectSQL(“SELECT * FROM cNotifiche”)
and no data is shown …

Sascha_S · August 12, 2020, 12:00pm

Sounds like you are working on macOS? I’m not at my Desk, but you can find a setting in the System Settings which will automatically replace all non curly quotes with curly quotes.

Fabrizio_Cesare · August 12, 2020, 8:12pm

Yes i’m on Mac OS. Now that i replace all the curly quotes any idea on why i continue not to see anything in the listbox? The first part of the code (where create the virtual DB and import data from ‘real’ DB for you is it ok ?

Sascha_S · August 13, 2020, 6:25am

I rarely use SQlite (mostly mySQL and MSSQL) and it’s hard to tell by just looking at the Code. Unfortunately i am very short in time these days and can’t perform tests with your Code. I’m really sorry.
But just by looking at your Code and assuming that you do not receive any error while creating the DB and inserting data into the DB, i’d say it looks good.

Maybe someone else can help us out here?

Beatrix_Willius · August 13, 2020, 8:19am

Make me a simple example and I’ll have a look.

TimStreater · August 13, 2020, 9:54am

The SQLite docs seem to say that you must supply a match() function in order to use MATCH. I’ve not used FTS so don’t know the implications of this.