As I mentioned in my previous post, I love the Sequel library from Jeremy Evans! One thing in particular I love about the framework is how easy it is to extend with custom plugins and extensions.
One example I've developed for my day job is to create an extension for the BETWEEN/AND
clause of SQL.
module Sequel
module SQL
class BetweenExpression < BooleanExpression
module DatasetMethods
def between_sql_append(sql, bet_expr)
sql << '('
literal_append(sql, bet_expr.expr)
sql << ' BETWEEN '
literal_append(sql, bet_expr.lower_bound)
sql << ' AND '
literal_append(sql, bet_expr.upper_bound)
sql << ')'
end
end
attr_reader :expr, :lower_bound, :upper_bound
def initialize(expr, lower, upper)
@expr = expr
@lower_bound = lower
@upper_bound = upper
freeze
end
to_s_method :between_sql
end
module Builders
def between(expr, lower, upper)
case expr
when ::Sequel::SQL::BetweenExpression
::Sequel::SQL::BetweenExpression.new(expr.expr, lower, upper)
else
::Sequel::SQL::BetweenExpression.new(expr, lower, upper)
end
end
end
end
Dataset.register_extension(:between_expressions, SQL::BetweenExpression::DatasetMethods)
Dataset.include SQL::BetweenExpression::DatasetMethods
end
Let's breakdown that example.
- Because
BETWEEN/AND
is a filtering expression that yields a true/false answer I've decided to make theBetweenExpression
a subclass ofSequel::SQL::BooleanExpression
. There is alsoSequel::SQL::GenericExpression
which is used for things like mathematical expressions that could be used as projections or as filters (e.g.my_column + 2
). - The internal
DatasetMethods
module follows Jeremy's pattern for encapsulating behavior. It is used when registering the extension as well as adding functionality toSequel::Dataset
. - An extension determines what state it needs, so the
initialize
message can be designed however you like. - In our case, we need to know what is being tested. It's an expression of any sort called
expr
. This expression can be aSymbol
, aSequel::SQL::Identifier
, aSequel::SQL::AliasedExpression
, or any other type of expression that the framework knows how to literalize (e.g.(a + b)
which we could write asSequel[:a] + Sequel[:b]
). - We also need to know the lower and upper bounds of the SQL type to which we're comparing. Again, these can be complex Sequel expressions, constants, symbols, etc.
-
The magic (yep, I sorta buried the lead) ... We define a
between_sql_append
method and then send theBooleanExpression.to_s_method
message with:between_sql
as its only argument. What gives? This is the convention in extensions. The framework will take that symbol and create an instance method on our expression that will dispatch to ourbetween_sql_append
message when our expression is being literalized. I believe you can defineBetweenExpression#to_s_append(dataset, string)
instead of using this hook, but I prefer to follow Jeremy's pattern.
I've not yet talked about two portions: building this expression and the literalizing it.
Sequel::SQL::Builders
The Builders module in Sequel eases the creation of various types of expressions without using Sequel's core extensions (e.g. Sequel.lit(...)
, Sequel.function(...)
). For my purposes, I want to be able to create the BETWEEN/AND
clause like this: Sequel.between(what, lower, upper)
, so I define the between
method that takes an expression and the bounds.
When altering expressions in Sequel, it's common to do a bit of runtime type identification (RTTI) on the parameters being sent with the message and build up from there. In our case, if the expression given to us is already a BetweenExpression
we'll use its expr
as the expression we're testing against. For example, this is perfectly valid (though questionable): Sequel.between(Sequel.between(:a, :b, :c), :d, :e)
. This type of thing comes in handy when expressions are being passed around the system and altered based on the current context.
Literalization
BetweenExpression#between_sql_append
tells Sequel how to append the given expression to a SQL string. It's important to remember that the context of this method is within an instance of a Dataset
. Datasets dispatch to expressions to iteratively build up the SQL string that is eventually sent to the database.
Our method knows the basic bits of the clause (BETWEEN
and AND
), but it doesn't need to know how to create the SQL string for any of the expression's instance variables. Remember that the expr
, lower_bound
, and upper_bound
instance variables could be any type of Sequel::SQL::Expression
--- even another Dataset.
To make things easy on extension and plugin developers, Jeremy provides the literal_append
message that receives the SQL string as its first argument and the "thing" you want to append to it. That message will take care of dispatching to a wide array of messages to create the proper behavior.
Conclusion
Writing this extension was pretty simple, and definitely beats creating literal strings: Sequel.literal("? BETWEEN ? AND ?", :foo, :bar, :baz)
. When possible, I prefer to build datasets with purpose-built expressions rather than mixing in raw SQL.
I'll be releasing this as a gem soon. You can see the fleshed out code with added behavior here.
Top comments (0)