Sqlite extensions - text comparison within sqlite

  1. 9 months ago

    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
    
    
    

or Sign Up to reply!