title: Sqlite with real "Full Text Search" and spelling mistakes (FTS+spellfix together)
body: <p>Let's say we have 1 million of rows like this:</p>
  
  <pre><code>import sqlite3
  db = sqlite3.connect(':memory:')
  c = db.cursor()
  c.execute('CREATE TABLE mytable (id integer, description text)')
  c.execute('INSERT INTO mytable VALUES (1, "Riemann")')
  c.execute('INSERT INTO mytable VALUES (2, "All the Carmichael numbers")')
  </code></pre>
  
  <h2>Background:</h2>
  
  <p>I know how to do this with Sqlite:</p>
  
  <ul>
  <li><p><strong>Find a row with a <em>single-word query</em>, up to a few spelling mistakes</strong> with the <a href="https://www.sqlite.org/spellfix1.html" rel="noreferrer"><code>spellfix</code></a> module and Levenshtein distance (I have posted a <a href="https://stackoverflow.com/questions/49779281/string-similarity-with-python-sqlite-levenshtein-distance-edit-distance">detailed answer here</a> about how to compile it, how to use it, ...):</p>
  
  <pre><code>db.enable_load_extension(True)
  db.load_extension('./spellfix')
  c.execute('SELECT * FROM mytable WHERE editdist3(description, "Riehmand") &lt; 300'); print c.fetchall()
  
  #Query: 'Riehmand'
  #Answer: [(1, u'Riemann')]
  </code></pre>
  
  <p>With 1M rows, this would be super slow! As <a href="https://dba.stackexchange.com/questions/203679/does-a-levenshtein-distance-involve-some-computation-for-each-single-row">detailed here</a>, <code>postgresql</code> might have an optimization with this using <code>trigrams</code>. A fast solution, available with Sqlite, is to use a <code>VIRTUAL TABLE USING spellfix</code>:</p>
  
  <pre><code>c.execute('CREATE VIRTUAL TABLE mytable3 USING spellfix1')
  c.execute('INSERT INTO mytable3(word) VALUES ("Riemann")')
  c.execute('SELECT * FROM mytable3 WHERE word MATCH "Riehmand"'); print c.fetchall()
  
  #Query: 'Riehmand'
  #Answer: [(u'Riemann', 1, 76, 0, 107, 7)], working!
  </code></pre></li>
  <li><p><strong>Find an expression with a query matching <em>one or multiple words</em></strong> with FTS ("Full Text Search"):</p>
  
  <pre><code>c.execute('CREATE VIRTUAL TABLE mytable2 USING fts4(id integer, description text)')
  c.execute('INSERT INTO mytable2 VALUES (2, "All the Carmichael numbers")')
  c.execute('SELECT * FROM mytable2 WHERE description MATCH "NUMBERS carmichael"'); print c.fetchall()
  
  #Query: 'NUMBERS carmichael'
  #Answer: [(2, u'All the Carmichael numbers')]
  </code></pre>
  
  <p>It is case insensitive and you can even use a query with two words in the wrong order, etc.: FTS is quite powerful indeed. But the drawback is that <em>each of the query-keyword must be correctly spelled</em>, i.e. FTS alone doesn't allow spelling mistakes.</p></li>
  </ul>
  
  <h2>Question:</h2>
  
  <p><strong>How to do a Full Text Search (FTS) with Sqlite <em>and also allow spelling mistakes</em>?</strong> i.e. "FTS + spellfix" together</p>
  
  <p>Example: </p>
  
  <ul>
  <li>row in the DB: <code>"All the Carmichael numbers"</code></li>
  <li>query: <code>"NUMMBER carmickaeel"</code> should match it!</li>
  </ul>
  
  <p><strong>How to do this with Sqlite?</strong> </p>
  
  <p>It is probably possible with Sqlite since <a href="https://www.sqlite.org/spellfix1.html" rel="noreferrer">this page</a> states:</p>
  
  <blockquote>
    <p>Or, it [spellfix] could be used with FTS4 to do full-text search using potentially misspelled words.</p>
  </blockquote>
  
  <p>Linked question: <a href="https://stackoverflow.com/questions/49779281/string-similarity-with-python-sqlite-levenshtein-distance-edit-distance">String similarity with Python + Sqlite (Levenshtein distance / edit distance)</a></p>