sqlite3 安装 fts5 / simple 中文分词

安装 fts5 分词

需要安装最新 sqlite3

默认系统里的 sqlite 并没有 .load ，无法加载扩展，所以，无法测试此功能

# mbp mac sqlite3
export PATH="/usr/local/opt/sqlite/bin:$PATH";

# m1 mac sqlite3
export PATH="/opt/homebrew/opt/sqlite/bin:$PATH";

步骤

sqlite3>

.load /Users/aric/Downloads/libsimple-osx-x64/libsimple.dylib
select jieba_dict('/Users/aric/Downloads/libsimple-osx-x64/dict');


CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple');
INSERT INTO t1 VALUES ('广东深圳');
INSERT INTO t1 VALUES ('深圳宝安中心');

select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('深圳');

ActiveRecord::Base.connection.execute("CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple')")

Rails lib/native

sqlite3>

.load lib/native/libsimple.dylib
select jieba_dict('lib/native/dict');


CREATE VIRTUAL TABLE t1 USING fts5(text, tokenize = 'simple');
INSERT INTO t1 VALUES ('广东深圳');
INSERT INTO t1 VALUES ('深圳宝安中心');

select simple_highlight(t1, 0, '[', ']') as text from t1 where text match jieba_query('深圳');

Rails extension

./config/initializers/sqlite_extensions.rb 扩展，目前这个有问题 

https://fractaledmind.github.io/2023/12/24/enhancing-rails-installing-extensions/

https://dogcow.co.uk/2024/08/27/litestack-sqlite-ulid/

module SqlpkgLoader
  def configure_connection
    super

    Rails.logger.debug "Loading #{Rails.root}/lib/native/libsimple.dylib"
    Rails.logger.debug "Loading sqlite3 extensions #{@raw_connection}"

    @raw_connection = ActiveRecord::Base.connection.raw_connection
    @raw_connection.enable_load_extension(true)
    @raw_connection.load_extension("#{Rails.root}/lib/native/libsimple.dylib")
    @raw_connection.execute("select jieba_dict('#{Rails.root}/lib/native/dict')")
    @raw_connection.enable_load_extension(false)
  end
end

ActiveSupport.on_load(:active_record_sqlite3adapter) do
  prepend SqlpkgLoader
end

sqlite8 里的实现