running test tries to recreate search index column in db

[caplod]

I have added search to my Event model:

class Event < ApplicationRecord
  include Litesearch::Model

  litesearch do |schema|
    schema.field :title
    schema.tokenizer :trigram
  end
end

when I run tests I get an error:

SQLite3::SQLException: fts5: error creating shadow table events_search_idx_data: table 'events_search_idx_data' already exists
    app/models/event.rb:4:in `<class:Event>'
    app/models/event.rb:1:in `<main>'

I tried rails db:test:prepare and deleting db/test/* but no change.

In the frontend it is working fine. What am I doing wrong?

[oldmoe]

Hi @caplod

can you share your db configuration? And can you share the test that generates this error?

Also what do you mean it is working fine on the frontend? you mean in development/production?

[caplod]

Here is my database.yml

default: &default
  adapter: litedb
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  idle_timeout: 0

development:
  <<: *default
  database: <%= Litesupport.root("development").join("data.sqlite3") %>

test:
  <<: *default
  database: <%= Litesupport.root("test").join("data.sqlite3") %>

production:
  <<: *default
  database: <%= Litesupport.root("production").join("data.sqlite3") %>

For the tests: It doesnt matter which test I run. The error appears always. So I suspect it hat something to do with the initialization of the database.

in my schema.rb I have some comments about errors, maybe thats the problem?

# Could not dump table "events_search_idx" because of following StandardError
#   Unknown type '' for column 'title'

# Could not dump table "events_search_idx_config" because of following StandardError
#   Unknown type '' for column 'k'

  create_table "events_search_idx_data", force: :cascade do |t|
    t.binary "block"
  end

  create_table "events_search_idx_docsize", force: :cascade do |t|
    t.binary "sz"
    t.integer "origin"
  end

# Could not dump table "events_search_idx_idx" because of following StandardError
#   Unknown type '' for column 'segid'

# Could not dump table "events_search_idx_instance" because of following StandardError
#   Unknown type '' for column 'term'

# Could not dump table "events_search_idx_row" because of following StandardError
#   Unknown type '' for column 'term'

With 'it works in frontend' I mean search in events is working in development and production.

[oldmoe]

I think I know what is going on. The schema dump logic is not excluding the correct set of tables. Let me see if I can produce a quick fix or if this is more involved than what I think

[caplod]

I still have the same error.

What I did:

• Updated the gemfile 'litestack'
• deleted schema.rb
• run rails db:schema:dump

I still have the errors in schema.rb

[anoldguy]

Hit this, too, so pulled the gem from github with no change, confirmed I was running 9cdf123063e0ce1b0856fc0b6dd7325441f59af5. I was able to work around it by manually cleaning up the schema.rb file and removing the added lines that @caplod noted earlier.

[fjorba]

If ignoring the fts5 full text tables is the solucion, please take into account that there are a few more than search_idx, if I understand well 9cdf123063e0ce1b0856fc0b6dd7325441f59af5. On a SQLite db that I have for full text searching (no relation at all with Litestack or Rails), if I create a virtual fts5 table named fulltext, SQLite automatically creates those ones:

CREATE TABLE IF NOT EXISTS 'fulltext_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS 'fulltext_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE IF NOT EXISTS 'fulltext_content'(id INTEGER PRIMARY KEY, c0, c1, c2, c3, c4, c5);
CREATE TABLE IF NOT EXISTS 'fulltext_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE IF NOT EXISTS 'fulltext_config'(k PRIMARY KEY, v) WITHOUT ROWID;