> Note that in Sequel, bound variables/prepared statements are never
> used unless you specifically call Dataset#prepare or Dataset#call.
> I'm not sure if that is mentioned specifically in the documentation.
You're right, it isn't, and that took me some codebase digging to
figure out. But in the process, I came up with some ideas that I
think would make bind variables more useful.
As you said, it's apparent that bind variables were added later on,
and as a side effect they are a bit awkward to use. If you have a
database with bind variable support (I use both Oracle and Postgres)
you'll want to take advantage of it as much as possible.
Unfortunately, using the core Sequel syntax doesn't provide a way for
the underlying Dataset driver to make this decision.
So here's my idea - actually I already have this working, it was just
a few lines of code here and there. Plus, it maintains backwards
compatibility as well as adds new features even for databases without
bind variable support:
# Bind existing ? placeholders
User.filter('id > ?', 1)
User.filter('id > ?', 1).filter('id < ?', 100)
# Add :named placeholder (like Oracle and ActiveRecord)
User.filter('id > :min and id < :max', :min => 1, :max => 100)
User.filter('id < :exclude and id > :exclude', :exclude => 42)
Underneath, the internals change, to enable the Oracle/Postgres/etc
adapters to hook in and use real bind variables instead of literal
(value)
The implementation for this is simple - the
placeholder_literal_string_sql function changes a bit. Then, two new
functions, bind_named_placeholder, and bind_ordered_placeholder, are
added to Dataset that do string interpolation by default. These are
overridden in subclasses appropriately. For example, both Postgres
and Oracle would override these to do actual binding.
The exact same approach works for insert/update/delete with just a few
tweaks to methods like update_set_sql and the such.
Taking this one further, how about adding a new bind() method to
dataset that allows delayed binding, to simplify things a bit more:
# Decide what to bind later
User.filter('id > :min and id < :max').bind(:min => 1, :max => 100)
User.filter('id > ? and id < ?').bind(1, 100)
It could be a bit easier and cleaner than the call(:insert, :name =>
'Foo') syntax:
recent_users = User.filter('created_at > :when').prepare
recent_users.bind(:when => today).all
yesterday = recent_users.bind(:when => yesterday)
yesterday.delete
If you're cool with this approach, I'll just keep plugging away. I
just checked in the first part to
http://github.com/nateware/sequel/commit/79ee33a1fc4905fd99fc01a4e4529f9a69b254c2
- Specs coming soon, I've been using a test script that accesses my
existing DB because it's quicker for initial dev.
-Nate
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
