Consultas OR automáticas utilizando SQLite FTS4

Tengo una base de datos SQLite FTS4, y me gustaría poder realizar consultas OR desde la entrada del usuario, por ejemplo, si el usuario ingresa “hola” me gustaría hacer

SELECT * FROM fts_table WHERE text MATCHES 'hello OR there'. 

Sin embargo, si paso simplemente paso la cadena de usuario, obtendré una consulta AND implícita. Por supuesto, yo mismo podría tokenizar la cadena e insertar OR, pero luego estoy usando mi propio tokenizador que podría diferir del tokenizador que usa internamente el FTS de SQLite.

¿Hay alguna forma de acceder al tokenizador de SQLite desde mi contenedor C, o bien construir una consulta SQL que haga esto, en la línea de:

 SELECT * FROM fts_table WHERE text MATCHES interpolate('hello there', ' OR ') 

Gracias cualquier puntero sería apreciado.

EDITAR:

Cualquier futuro lector de esta pregunta debería tener en cuenta esta otra pregunta: cómo escapar de la cadena para la consulta FTS de SQLite no es seguro simplemente interpolar O en la cadena, porque a menudo terminará con cadenas no válidas, por ejemplo, si el usuario ingresa hello AND there la interpolación OR creará hello OR AND OR there cual no es válido. Asi que

Entonces, para responder parte de mi pregunta: resulta que puede acceder a un puntero a la función de tokenizador de SQLite a través de SELECT fts3_tokenizer() . Consulte http://www.sqlite.org/fts3.html#section_8_1 para obtener más detalles.

Esto devuelve una estructura sqlite3_tokenizer_module , que contiene los punteros de función xCreate , xOpen y xNext .

Deberá agregar el archivo de encabezado fts3_tokenizer.h a su proyecto como se describe aquí. http://www.mail-archive.com/sqlite-users%40sqlite.org/msg62644.html

Estoy poniendo la definición de sqlite3_tokenizer_module continuación (copiada literalmente porque incluye comentarios útiles)

 /* ** Structures used by the tokenizer interface. When a new tokenizer ** implementation is registered, the caller provides a pointer to ** an sqlite3_tokenizer_module containing pointers to the callback ** functions that make up an implementation. ** ** When an fts3 table is created, it passes any arguments passed to ** the tokenizer clause of the CREATE VIRTUAL TABLE statement to the ** sqlite3_tokenizer_module.xCreate() function of the requested tokenizer ** implementation. The xCreate() function in turn returns an ** sqlite3_tokenizer structure representing the specific tokenizer to ** be used for the fts3 table (customized by the tokenizer clause arguments). ** ** To tokenize an input buffer, the sqlite3_tokenizer_module.xOpen() ** method is called. It returns an sqlite3_tokenizer_cursor object ** that may be used to tokenize a specific input buffer based on ** the tokenization rules supplied by a specific sqlite3_tokenizer ** object. */ typedef struct sqlite3_tokenizer_module sqlite3_tokenizer_module; typedef struct sqlite3_tokenizer sqlite3_tokenizer; typedef struct sqlite3_tokenizer_cursor sqlite3_tokenizer_cursor; struct sqlite3_tokenizer_module { /* ** Structure version. Should always be set to 0 or 1. */ int iVersion; /* ** Create a new tokenizer. The values in the argv[] array are the ** arguments passed to the "tokenizer" clause of the CREATE VIRTUAL ** TABLE statement that created the fts3 table. For example, if ** the following SQL is executed: ** ** CREATE .. USING fts3( ... , tokenizer  arg1 arg2) ** ** then argc is set to 2, and the argv[] array contains pointers ** to the strings "arg1" and "arg2". ** ** This method should return either SQLITE_OK (0), or an SQLite error ** code. If SQLITE_OK is returned, then *ppTokenizer should be set ** to point at the newly created tokenizer structure. The generic ** sqlite3_tokenizer.pModule variable should not be initialized by ** this callback. The caller will do so. */ int (*xCreate)( int argc, /* Size of argv array */ const char *const*argv, /* Tokenizer argument strings */ sqlite3_tokenizer **ppTokenizer /* OUT: Created tokenizer */ ); /* ** Destroy an existing tokenizer. The fts3 module calls this method ** exactly once for each successful call to xCreate(). */ int (*xDestroy)(sqlite3_tokenizer *pTokenizer); /* ** Create a tokenizer cursor to tokenize an input buffer. The caller ** is responsible for ensuring that the input buffer remains valid ** until the cursor is closed (using the xClose() method). */ int (*xOpen)( sqlite3_tokenizer *pTokenizer, /* Tokenizer object */ const char *pInput, int nBytes, /* Input buffer */ sqlite3_tokenizer_cursor **ppCursor /* OUT: Created tokenizer cursor */ ); /* ** Destroy an existing tokenizer cursor. The fts3 module calls this ** method exactly once for each successful call to xOpen(). */ int (*xClose)(sqlite3_tokenizer_cursor *pCursor); /* ** Retrieve the next token from the tokenizer cursor pCursor. This ** method should either return SQLITE_OK and set the values of the ** "OUT" variables identified below, or SQLITE_DONE to indicate that ** the end of the buffer has been reached, or an SQLite error code. ** ** *ppToken should be set to point at a buffer containing the ** normalized version of the token (ie after any case-folding and/or ** stemming has been performed). *pnBytes should be set to the length ** of this buffer in bytes. The input text that generated the token is ** identified by the byte offsets returned in *piStartOffset and ** *piEndOffset. *piStartOffset should be set to the index of the first ** byte of the token in the input buffer. *piEndOffset should be set ** to the index of the first byte just past the end of the token in ** the input buffer. ** ** The buffer *ppToken is set to point at is managed by the tokenizer ** implementation. It is only required to be valid until the next call ** to xNext() or xClose(). */ /* TODO(shess) current implementation requires pInput to be ** nul-terminated. This should either be fixed, or pInput/nBytes ** should be converted to zInput. */ int (*xNext)( sqlite3_tokenizer_cursor *pCursor, /* Tokenizer cursor */ const char **ppToken, int *pnBytes, /* OUT: Normalized text for token */ int *piStartOffset, /* OUT: Byte offset of token in input buffer */ int *piEndOffset, /* OUT: Byte offset of end of token in input buffer */ int *piPosition /* OUT: Number of tokens returned before this one */ ); /*********************************************************************** ** Methods below this point are only available if iVersion>=1. */ /* ** Configure the language id of a tokenizer cursor. */ int (*xLanguageid)(sqlite3_tokenizer_cursor *pCsr, int iLangid); }; 
Intereting Posts