issues: 737153927

This data as json

id node_id number title user state locked assignee milestone comments created_at updated_at closed_at author_association pull_request body repo type active_lock_reason performed_via_github_app reactions draft state_reason
737153927 MDU6SXNzdWU3MzcxNTM5Mjc= 197 Rethink how table.search() method works 9599 closed 0   6079500 5 2020-11-05T18:04:34Z 2020-11-08T17:07:37Z 2020-11-08T17:07:37Z OWNER  

I need to improve this method to help build sqlite-utils search in #192 (PR is #195).

The challenge is deciding how it should handle sorting by relevance - especially since that is easy in FTS5 but not at all easy in FTS4.

Latest test failure: 114 -> assert [("racoons are biting trash pandas", "USA", "bar")] == table.search( 115 "bite", order="rowid" 116 ) 117 118 119 def test_optimize_fts(fresh_db): (Pdb) table.search("bite") [(2, 'racoons are biting trash pandas', 'USA', 'bar', -9.641434262948206e-07)] The problem here is that the table.search() method now behaves differently for FTS4 v.s. FTS5 tables.

With FTS4 you get back just the table columns.

With FTS5 you also get back the rowid as the first column and the rank score as the last column.

This is weird. It also makes me question whether having .search() return a list of tuples is the right API design.

Originally posted by @simonw in https://github.com/simonw/sqlite-utils/pull/195#issuecomment-722542895

 140912432 issue     
{
    "url": "https://api.github.com/repos/simonw/sqlite-utils/issues/197/reactions",
    "total_count": 0,
    "+1": 0,
    "-1": 0,
    "laugh": 0,
    "hooray": 0,
    "confused": 0,
    "heart": 0,
    "rocket": 0,
    "eyes": 0
}
   completed

Links from other tables