API for SQLite's fts5 extension

[mainrs]

Hello,

I'd like to ask how an implementation of such a feature in sea-orm would look like. What would be an approach that could get merged into the project?

I thought about an extension trait on the SchemaBuilder that might be inside an sqlite specific module with builder methods to create such fts5 tables.

[tyt2y3]

Hi, can you explain a bit more about your usecase, problem, and how would you like to interact with the fts5 feature?

[mainrs]

Sure!

Usecase: I have an existing table songs that contains the artist, title and year of a song. In a new schema migration, I want to add the ability to do full-text search on the artist and title fields. For that I want to leverage the native SQLite module fts5 1 with the trigram tokenizer 2.

To make this work, I currently manually create the virtual fts5 table using raw SQL and create custom hooks that trigger on insert, update and delete of the songs table and perform the corresponding action inside the songs_fts table too. The code can be found at the end.

Problem: The fts5 module is more performant than a custom implementation using normal SQL queries. With the amount of data inside my database, I would highly benefit from fts5.

Interaction: Inside the migration crate of my project, I'd like to be able to use the SchemaManager to create a virtual fts5 table. The API I use should make clear that this feature is only available in SQLite and by using it, I will lock myself into a specific backend instead of having a universal migration that runs on all backends available. Maybe this happens through an import path containing sqlite. Or maybe I have to call a specific method on the builder containing sqlite in its name.

For example:

use sea_orm_migration::sqlite::FtsTable;

let manager: &SqliteManager = todo!();
manager.create_table(Fts5Table::new()...).await?;

manager
            .create_table(
                Table::create()
                    .table(Song::Table)
                    .if_not_exists()
                    .col(
                        ColumnDef::new(Song::Id)
                            .integer()
                            .not_null()
                            .auto_increment()
                            .primary_key(),
                    )
                    .col(ColumnDef::new(Song::Title).string().not_null())
                    .col(ColumnDef::new(Song::Artist).string().not_null())
                    .col(ColumnDef::new(Song::Year).integer())
                    .to_owned(),
            )
            .await?;
let db = manager.get_connection();
        db.execute_unprepared(
            "CREATE VIRTUAL TABLE songs_fts using fts5(id UNINDEXED, artist, title, tokenize=\"trigram\");
            CREATE TRIGGER songs_after_insert AFTER INSERT ON song BEGIN
                INSERT INTO songs_fts (rowid, artist, title)
                VALUES (new.id, new.artist, new.title);
            END;
            "
        ).await?;

[tyt2y3]

Thank you for your explanation. I understand how it works now. I think it is worth considering its commonality with Postgres full text search, and whether we can achieve some degree of abstraction between them.