Credit:
http://robots.thoughtbot.com/post/159807745/the-ideal-of-agnosticism
http://stephen-veit.blogspot.com/2009/03/implementing-regexp-in-sqlite3.html
I found a way to add regexp support to SQLite3 databases in Sequel.
SQLite3 supports the
REGEXP operator but leaves it up to the end-user to implement it. You
can use the
SQLite3::Database#create_function from the sqlite3-ruby gem to define
the implementation.
To get it working, you'll need to add the regexp implementation to the
database instance and change how Sequel reacts when a regular
expression is used with SQLite3 databases.I couldn't found how to
access the SQLite3::Database instance through Sequel, so I just
patched the sqlite3-ruby gem instead.
USAGE:
Should be the same as the other databases that support REGEXP
Sequel:
dataset.filter( :this_field => /(?i:this_regex)/ )
SQL:
SELECT ... WHERE this_field REGEXP "(?i:this_regex)";
CAVEATS:
* If you need to use regular expression flags like case-
insensitivity, you'll need to
explicitly define them within the regular expression like the
example above.
* This only adds regexp support from within Ruby (using the sqlite3-
ruby gem.)
* Expect a performance penalty for using it. I haven't tried it yet
with joins or large
datasets.
Patch for : sqlite3-ruby-1.2.5
File: sqlite3-ruby-1.2.5/lib/sqlite3/database.rb
#--------------------------------------------------------------------
Create an instance method like:
def enable_regexp
create_function("regexp", 2) do |func, expr, value|
begin
if value && value.to_s.match(Regexp.new(expr.to_s))
func.set_result 1
else
func.set_result 0
end
rescue => e
end
end
end
And call it from the initialize method. I called it right before line
85
def initialize( file_name, options={} ) # :yields: db
#...
enable_regexp # PATCH
if block_given?
# ...
Patch for : sequel-3.9.0
File : sequel-3.9.0/lib/sequel/adapters/shared/sqlite.rb
#--------------------------------------------------------------------
In the complex_expression_sql method definition
at line #247 change:
raise Error, "SQLite does not support pattern matching via regular
expressions"
to:
"(#{literal(args.at(0))}#{'NOT ' if [:'!~', :'!~*'].include?(op)}
REGEXP #{literal(args.at(1))})"
I slightly modified the same logic used in the MySQL file.
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
