Does it support sqlite3 FTS?

[re-chen]

In sqlite3, I can create a FTS table like this

CREATE VIRTUAL TABLE message USING fts3(title, body, tokenize=porter);

Does sqlite_orm support it?

[fnc12]

no this extension isn't supported right now. I shall add this into TODO list

[jakemumu]

Hey! I wanted to share that I'm also interested if we could gain FTS5 support with SQLite ORM. After watching this video I'm interested in trying it:

https://www.youtube.com/watch?v=eXMA_2dEMO0

[fnc12]

hey @jakemumu . I watched the video. It is very interesting. Let me share how I see the API of this feature:

struct Title {
    int id = 0;
    std::string primaryTitle;
    std::string originalTitle;
};
struct TitleFts {
    int id = 0;
    std::string primaryTitle;
    std::string originalTitle;
};
auto storage = make_storage("sqlite.db",
        make_table("titles",
            make_column("title_id", &Title::id, primary_key()),
            make_column("primary_title", &Title::primaryTitle),
            make_column("original_title", &Title::originalTitle)),
        make_virtual_table("titles_fts", using_fts5(
            make_column("title_id", &TitleFts::id), 
            make_column("primary_title", &TitleFts::primaryTitle)
            make_column("original_title", &TitleFts::originalTitle)),
    );

//  INSERT INTO titles_fts (title_id, primary_title, original_title)
//  SELECT title_id, primary_title, original_title 
//  FROM titles
storage.insert(
    into<TitleFts>(), 
    columns(&TitleFts::id, &TitleFts::primaryTitle, &TitleFts::originalTitle),
    select(columns(&Title::id, &Title::primaryTitle, &Title::originalTitle)));

//  SELECT count(*) 
//  FROM titles
//  INNER JOIN titles_fts ON titles_fts.title_id = titles.title_id
//  WHERE titles_fts MATCH 'star wars'
auto rows = storage.select(count<Title>(), 
    inner_join<TitleFts>(on(is_equal(&Title::id, &TitleFts::id))),
    where(match<TitleFts>("star wars")));

//  SELECT count(*) 
//  FROM titles
//  INNER JOIN titles_fts ON titles_fts.title_id = titles.title_id
//  WHERE titles_fts MATCH 'star wars'
auto rows = storage.select(count<Title>(), 
    inner_join<TitleFts>(on(is_equal(&Title::id, &TitleFts::id))),
    where(match<TitleFts>("star wars")));

Please share your thoughts on that

[jakemumu]

That API looks incredible if it's possible! It would greatly improve the text search capabilities for us -- one thing to ensure is possible is a select from across multiple tables into the FTS index table since generally when adding FTS the goal is to have a performant search against a record and it's relations.

If you do get a version working I'm happy to beta test it

[fnc12]

@jakemumu you can help a lot if you post SQL queries examples which you lack here. I'll try to implement this API and report here

[fnc12]

more API concept. I found a great example here https://www.sqlitetutorial.net/sqlite-full-text-search/ and drafted how I'd like to implement API:

struct Post {
	std::string title;
	std::string body;
};

///	CREATE VIRTUAL TABLE posts 
///	USING FTS5(title, body);
auto storage = make_storage("storage.db",
	make_virtual_table("posts", using_fts5(
		make_column("title", &Post::title),
		make_column("body", &Post::body))));

//	`make_virtual_table` and `using_fts5` are new functions here. `make_virtual_table` will create a new storage element, 
//	which will be serialized to CREATE VIRTUAL TABLE during `sync_schema` call,
//	`using_fts5` will accept only columns without constraints (compile time check)

storage.sync_schema();

const std::vector<Post> postsToInsert = {
	Post{"Learn SQlite FTS5", "This tutorial teaches you how to perform full-text search in SQLite using FTS5"},
	Post{"Advanced SQlite Full-text Search", "Show you some advanced techniques in SQLite full-text searching"},
	Post{"SQLite Tutorial", "Help you learn SQLite quickly and effectively"},
};

///	INSERT INTO posts(title,body)
///	VALUES('Learn SQlite FTS5','This tutorial teaches you how to perform full-text search in SQLite using FTS5'),
///	('Advanced SQlite Full-text Search','Show you some advanced techniques in SQLite full-text searching'),
///	('SQLite Tutorial','Help you learn SQLite quickly and effectively');
storage.insert_range(postsToInsert.begin(), postsToInsert.end());

///	SELECT * FROM posts;
auto posts = storage.get_all<Post>();

///	SELECT * 
///	FROM posts 
///	WHERE posts MATCH 'fts5';
auto specificPosts = storage.get_all<Post>(where(match<Post>("fts5")));

//	`match` is a new function here. It represents `X MATCH Y` AST node

///	SELECT * 
///	FROM posts 
///	WHERE posts = 'fts5';
auto specificPosts2 = storage.get_all<Post>(where(is_equal<Post>("fts5")));

//	`is_equal<Post>("fts5")` is a new overload here. Current `is_equal` implementation accepts two arguments only. 
//	This accepts one and one explicit template argument. This overload represents `X = Y` node where `X` is a table name.

///	SELECT * 
///	FROM posts('fts5');
///
///	TODO

///	SELECT * 
///	FROM posts 
///	WHERE posts MATCH 'text' 
///	ORDER BY rank;
auto orderedPosts = storage.get_all<Post>(where(match<Post>("fts5"), order_by(rank())));

//	`rank()` is a new function here. It will return a special type which will be serialized as `rank` SQLite keyword

///	SELECT highlight(posts,0, '<b>', '</b>'), 
///	       highlight(posts,1, '<b>', '</b>')
///	FROM posts 
///	WHERE posts MATCH 'SQLite'
///	ORDER BY rank;
auto highlightedPosts = storage.select(columns(highlight<Post>(0, "<b>", "</b>"), highlight<Post>(1, "<b>", "</b>")),
        from<Post>(),
	where(match<Post>("SQLite")),
	order_by(rank()));

//	`highlight` is a new function here. It accepts one explicit template argument and three function arguments.
//	It represents `highlight(A, B, C, D)` node where `A` is a table name, `B`, `C` and `D` are scalar values

[fnc12]

@jakemumu @re-chen you can check early API in feature/fts5 branch. It is still in development but your feedback is appreciated

[fnc12]

@jakemumu @re-chen please check the branch

[jakemumu]

hey! I'm so sorry my GitHub notifications have been flooded @fnc12 --thank so you much!!!!!! this will greatly improve my text search.

I will check this out this week

[fnc12]

one more PR to go https://github.com/fnc12/sqlite_orm/pull/1224

[fnc12]

please check dev branch and report whether something doesn't work. Looks like the feature is done

[fnc12]

I am closing this issue cause the feature is done. Of course is has a lot of small things which are not implemented but most part is done. If somebody requires something more please feel free to reopen this issue. Also any bugs related to FTS5 feature support in sqlite_orm can be also reported here

[fnc12]

BTW we have X account with news https://twitter.com/sqlite_orm/status/1710679011066544512?s=61&t=2YuUa0BKraI7i63kP98tOg