Howdy,
I have recently stumbled upon Sequel and I have been playing around with
SQLite more. When I was attempting to create a simple full text search.
Following the example <https://www.sqlite.org/fts5.html> in the SQLite
documentation.
FTS5 is a provided module that can implement full text search.
```ruby
# I connected to the database
DB = Sequel.connect('sqlite://test.db')
#=> #<Sequel::SQLite::Database: "sqlite://test.db">
# I attempted to run sql to set up the required virtual tables
DB.run('CREATE VIRTUAL TABLE email USING fts5(sender, title,
body');
# Error received:
#=>
/Users/romanturner/.rbenv/versions/3.1.2/lib/ruby/gems/3.1.0/gems/sqlite3-1.5.3-x86_64-darwin/lib/sqlite3/database.rb:152:in
`initialize': SQLite3::SQLException: incomplete input
(Sequel::DatabaseError)
```
I ended up just opening up sqlite3
```bash
sqlite3
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open test.db
sqlite> CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
sqlite>
```
Then I ran a little seed script placing 20 emails in the database.
After this I wanted to see if I could run a full text search using Sequel
and I couldn't find a 'match' method so I used a Sequel.lit statement:
```bash
sequel sqlite://test.db
irb(main):003:0> emails = DB[:email]
=> #<Sequel::SQLite::Dataset: "SELECT * FROM `email`">
irb(main):005:0> emails.where(Sequel.lit("email MATCH 'shunned'")).all
=>
[{:sender=>"Dee Sember",
:title=>"comprehension",
:body=> "..."
}
...
]
```
In the end my goal was achieved, but I was curious if there was a better
way to achieve this. I would eventually like to tie this to a fly.io app
and be able to spin up new instances and run migrations without issue.
Any advice is appreciated!
Roman
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/2ccc0a92-d494-4506-8bb7-53e2a2bbd65cn%40googlegroups.com.
