Can’t believe I did not know before that the Xojo SQLite-engine allows you to add extensions for things such as Levenshtein distance… just added Spellfix to my project in which I want to match titles that are very similar but might have some minor spelling mistakes (read differences).
Another problem I have is that titles may have subtitles added to the same field. Would it be wise to create a shorter title field that caps things of as a text control?
(FYI I am trying to compare the output of several search databases with our own publication registration system. If only it were as easy as comparing DOI or other identifiers… sigh)
For people that do not know what I am talking about, but are interested in similar text searches, a simplified example:
Compile the spellfix.c from Sqlite as explained here: https://julifos.wordpress.com/2018/11/13/mac-sqlite3-command-line-one-liner-load-extension/
//Create database if not already existing
db = New SQLiteDatabase
db.DatabaseFile = f
If db.CreateDatabaseFile Then
db.LoadExtensions = true
//location of the created dylib
Dim g As FolderItem = SpecialFolder.Desktop.Child("spellfix.dylib")
//create a table
Dim sql As String
sql = "CREATE TABLE Articles (LucrisID INTEGER NOT NULL," +_
"Title TEXT," +_
"PRIMARY KEY(LucrisID))"
App.db.SQLExecute(sql)
If App.DB.Error Then
System.DebugLog("DB Error: " + App.DB.ErrorMessage)
Else
System.DebugLog("Articles table created successfully.")
End If
Dim newRec As New DatabaseRecord
newRec.Int64Column("LucrisID") = 1
newRec.Column("Title") = "Hello World"
App.db.InsertRecord("Articles", newRec)
If App.db.Error Then
System.debuglog(App.db.ErrorMessage)
End If
Dim newRec2 As New DatabaseRecord
newRec2.Int64Column("LucrisID") = 2
newRec2.Column("Title") = "Hello World!"
App.db.InsertRecord("Articles", newRec2)
If App.db.Error Then
System.debuglog(App.db.ErrorMessage)
End If
//load the extension (you only need to do this once, otherwise you get an initialisation error)
sql = "SELECT load_extension ('"+g.NativePath+"','sqlite3_spellfix_init')"
db.SQLExecute(sql)
//try it out
sql As String = "SELECT * FROM Articles WHERE editdist3(Title, 'Hello Worl') < 450"
Dim rs As RecordSet
rs = App.db.SQLSelect(sql)
if rs <> nil then
Dim txt As String
while not rs.EOF
txt = txt + EndOfLine + rs.Field("LucrisID").Int64Value.ToText + " - " + rs.Field("Title").Value
rs.MoveNext
wend
TextArea2.text = txt
else
if app.db.Error then
TextArea2.text = App.db.ErrorMessage
else
TextArea2.text = ""
end if
end if
end if