On Oct 11, 6:25 am, Nate Wiger <[email protected]> wrote:
> > 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)
You have to deal with things like this:
User.filter('? > ?', :id, 1)
The problem with taking all bindings and pushing them down to the
database level is that there are plenty of binds that Sequel can
handle that the database cannot.
> # 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)
There's not really as much of a need for this in Sequel, as you can
do:
User.filter{(id > 1) & (id < 100)}
# or for bind variables:
User.filter{(id > :$min) & (id < :$max)}
Sequel doesn't really recommend the use of interpolated strings for
conditions (though the existing support works fine), so I'm reluctant
to add support for interpolated strings with a hash. I'm not
completely opposed to it, since it can be done without breaking too
much backwards compatibility. But it would require that a large
portion of the community was behind it. Things like this would break,
though:
User.filter('id > 1 and ?', :name=>[1,2,3])
> 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.
Sequel allows binding using any part of the DSL (see example above),
not just for interpolated strings, so I wouldn't support changes that
handle interpolated strings specially.
> 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)
This is pretty much what you can do now (with different syntax),
except that you don't provide the bind variables until you want to run
the SQL against the database. Is there any value added from binding
the variables as a separate method?
> User.filter('id > ? and id < ?').bind(1, 100)
This cannot be handled correctly. When you provide just a plain
string to filter (with no arguments), it is interpreted as a literal
SQL string. At that point, Sequel cannot do anything with it except
insert it verbatim in the SQL call. Acting otherwise would violate
one of Sequel's core principles, which is that Sequel does not parse
SQL.
> 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
Well, the filter with a string you are using wouldn't work due to the
parsing SQL issue mentioned above, but let's assume something like:
recent_users = User.filter{created_at > :$when}.prepare
I don't think I'd have a problem with binding the variables separately
before calling a method, if it allowed things that aren't possible
now.
> If you're cool with this approach, I'll just keep plugging away. I
> just checked in the first part
> tohttp://github.com/nateware/sequel/commit/79ee33a1fc4905fd99fc01a4e452...
> - Specs coming soon, I've been using a test script that accesses my
> existing DB because it's quicker for initial dev.
I don't think you'll be able to keep backwards compatibility across
all adapters with your proposed syntax, at least not without a lot of
internal complexity. Honestly, I don't think it is a wise decision to
switch from making variable binding explicit to making it implicit.
With Sequel, there's not much of an advantage to binding variables
unless the variables are expensive to literalize (e.g. large blobs).
And I think the decision about whether to prepare a statement should
be up to the user, as preparing all statements would most likely
decrease performance for the average user.
With the current code base, the user makes the decision of which
variables should be bound and which statements prepared, so they can
choose the cases to optimize. It's assumed that the user is profiling
the app to determine when to use the bound variable/prepared statement
support, and benchmarking before and after to make sure that using
bound variables/prepared statements is faster. I strongly discourage
using bound variables/prepared statements unless you have profiled
your application and found a bottleneck that using bound variables/
prepared statements can eliminate or decrease.
Would it be possible for you to just add prepared statement/bound
variable support to the Oracle adapter using the existing syntax? If
I was going to change the existing prepared statement support, I would
still want the Oracle prepared statement support to be committed
separately.
Jeremy
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
