[sqlite] Calling FTS tokeniser functions manually

[Matt Hamilton]

> On 13 May 2016, at 16:04, Dan Kennedy <danielk1977 at gmail.com> wrote:
> 
> On 05/13/2016 09:19 PM, Matt Hamilton wrote:
>> Hi all,
>>  Anyone know if/how you can call the FTS5 tokeniser functions manually? e.g. 
>> I want to look something up in the fts5vocab table but can't as I need to 
>> split/stem the initial value first before querying the table?
>> 
>> To illustrate:
>> 
>> sqlite> CREATE VIRTUAL TABLE ft1 USING fts5(x, tokenize = porter);
>> sqlite> INSERT INTO ft1 VALUES('running man');
>> sqlite> CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row);
>> sqlite> SELECT * FROM ft1_v_row;
>> man|1|1
>> run|1|1
>> sqlite> SELECT count(*) FROM ft1_v_row WHERE term = 'running';
>> 0
>> sqlite>
>> 
>> How can I somehow map 'running' => 'run' in order to query the fts5vocab 
>> table to get stats on that term? And how could I tokenise 'running man' => 
>> 'run', 'man' in order to look up multiple tokens?
> 
> I think the only way to do that at the moment is from C code using the API in 
> fts5.h:
> 
> https://www.sqlite.org/fts5.html#section_7
> 
> Use xFindTokenizer() to grab a handle for the desired tokenizer module, then 
> xCreate to create an instance and xTokenize to tokenize text.
> 
> There is example code in the fts5_test_tok.c file:
> 
> http://sqlite.org/src/artifact/db08af63673c3a7d
> 
> The example code creates a virtual table module that looks useful enough:
> 
> CREATE VIRTUAL TABLE ttt USING fts5tokenize('porter');
> 
> then:
> 
> SELECT * FROM ft1_v_row WHERE term IN (SELECT token FROM ttt('running man'));
> 
> should probably work. More information in fts5_test_tok.c.

Dan,
 Great, thanks for the help and pointers. I'm having trouble though working out 
how to actually register/initialise/use a custom fts5 function. e.g. I have:


/* Add your header comment here */
#include <sqlite3ext.h> /* Do not use <sqlite3.h>! */
SQLITE_EXTENSION_INIT1

#include <stdlib.h>

static void column_size_imp(
                            const Fts5ExtensionApi *pApi,
                            Fts5Context *pFts,
                            sqlite3_context *pCtx,
                            int nVal,
 sqlite3_value **apVal
                            ){
 int rc;
 int nToken;
 rc = pApi->xColumnSize(pFts, -1, &nToken);
 if( rc==SQLITE_OK ){
   sqlite3_result_int(pCtx, nToken);
 }else{
   sqlite3_result_error_code(pCtx, rc);
 }
}

int sqlite3Fts5AuxInit(fts5_api *pApi){
 return pApi->xCreateFunction(pApi, "colsize", 0, column_size_imp, 0);
}

Then I compile it on OS X with:

gcc -g -fPIC -dynamiclib aux_func.c -o aux_func.dylib -I/opt/local/include

Then try and load the extension module:

sqlite> .load aux_func

and use it:

sqlite> select colsize(ft) from ft where ft match 'apple';
Error: no such function: colsize

What am I missing here? Am I registering it wrong?

-Matt

? 
Matt Hamilton
Quernus
matt at quernus.co.uk
+44 117 325 3025
64 Easton Business Centre
Felix Road, Easton
Bristol, BS5 0HE

Quernus Ltd is a company registered in England and Wales. Registered number: 
09076246


? 
Matt Hamilton
Quernus
matt at quernus.co.uk
+44 117 325 3025
64 Easton Business Centre
Felix Road, Easton
Bristol, BS5 0HE

Quernus Ltd is a company registered in England and Wales. Registered number: 
09076246