FTS5 Options: Unindexed columns, prefix indexes, tokenizers & content

[trevornagy]

Opening this for visibility, feel free to close if you want this discussion in: #622

Does the implementation FTS5 support unindexed columns, prefix indexes and tokenizers? https://www.sqlite.org/fts5.html#the_unindexed_column_option

What about the content option? https://www.sqlite.org/fts5.html#external_content_and_contentless_tables These are all important features for me, can't see any examples or tests in the fts5 feature branch using them

Thanks!

[fnc12]

hey Trevor. I'll start implementing this soon. Thanks

[fnc12]

PR is on its way https://github.com/fnc12/sqlite_orm/pull/1279

[fnc12]

@trevornagy please check unindexed() column constraint function in dev branch. Other features are on its way and will be merged soon

[trevornagy]

@fnc12 Just tested with the unindexed PR commit on dev, getting the following errors:

1>  error C2039: 'unindexed': is not a member of 'sqlite_orm'
1>  sqlite_orm.h(23342,11):
1>  see declaration of 'sqlite_orm'
1>   error C3861: 'unindexed': identifier not found
1> error C2672: 'sqlite_orm::make_column': no matching overloaded function found
1>  sqlite_orm.h(2994,37):
1>  could be 'sqlite_orm::internal::column_t<G,S,Op...> sqlite_orm::make_column(std::string,G,S,Op...)'

[fnc12]

@trevornagy unindexed function exists in dev branch. Proof https://github.com/fnc12/sqlite_orm/blob/dev/include/sqlite_orm/sqlite_orm.h#L2183. I bet you messed up with the commits. Just pull dev branch, it should work

[trevornagy]

Sorry! You're correct, I copied the incorrect commit id. It works as expected.

[trevornagy]

Thank you very much for your quick work by the way!

[fnc12]

PR with prefix is on its way https://github.com/fnc12/sqlite_orm/pull/1282

[fnc12]

@trevornagy please check new prefix function in dev branch

[trevornagy]

@fnc12 Looked at the API and it looks like you create the prefix inside a make_column? This seems strange to me as each prefix doesn't apply to a column but rather the whole table?

https://www.sqlite.org/fts5.html#prefix_indexes

The test in using_fts5.cpp has the expected as: expected = R"(USING FTS5("title", "body" prefix=2))"; But I would expect it as: expected = R"(USING FTS5("title", "body", prefix=2))";

Note the , between "body" and "prefix=2"

Maybe I'm misinterpreting the example/test though?

[fnc12]

@trevornagy thanks for pointing out. This is my misunderstanding. I'll refactor it soon as a table constraint not column constraint

[fnc12]

PR is here https://github.com/fnc12/sqlite_orm/pull/1284 @trevornagy btw you can still use prefix func at table level anyway

[fnc12]

@trevornagy prefix fix is merged

[trevornagy]

Awesome, works as expected now. Thank you!

[fnc12]

@trevornagy this is good! Only tokenize table constraint left to close this issue. I'll add it soon

[trevornagy]

Content was originally requested as well as tokenizer, but yes, amazing work. Really appreciated

[fnc12]

@trevornagy sorry I forgot to mention content. Just my negligence, of course content will be implemented within this issue ASAP

[fnc12]

@trevornagy I got a question: how are you going to use tokenize feature? Do you have exact SQL you expect to be used? It can help me with unit tests

[trevornagy]

Sure, here's an example of tokenize: tokenize='unicode61 remove_diacritics 1'

[fnc12]

PR https://github.com/fnc12/sqlite_orm/pull/1292

[fnc12]

@trevornagy please check tokenize feature in dev

[fnc12]

PR with content is on its way https://github.com/fnc12/sqlite_orm/pull/1296

[fnc12]

@trevornagy please check content function in dev branch

[fnc12]

@trevornagy do you also need contentless_delete, content=tbl and content_rowid?

[trevornagy]

Will test soon, sorry. As for the others, let me verify works with what we currently have and I'll get back to you! Thanks for all the work so far

[trevornagy]

Hey @fnc12, my apologies for the delayed response. I think I'll need content='tbl', to replicate the following example below.

What I'm trying to accomplish is documented here: https://www.sqlite.org/fts5.html#external_content_tables

The example in sqlite would look something like:

-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl');

What's missing is being able to specify the external content b & c in this table. I personally do not need the content_rowid option as I just use the default.

Once again, sorry for the delayed response!

[fnc12]

@trevornagy table content is on its way https://github.com/fnc12/sqlite_orm/pull/1304

[fnc12]

hey @trevornagy . Please check out dev for new content<User>() function which you asked about. Also please say whether all required sub-features of FTS5 are implemented. Thanks

[trevornagy]

Perfect, thank you very much! That is all of the features! I really appreciate this!