I'm not sure I'd call 30% a modest improvement. :-) That's a huge
difference when you're talking lots of transactions.
Regarding the API, I just want to point out that 'id = :id' is not my
syntax - it's pervasive in DB adapters and ORM's alike. Hence the
reason I naively tried it in Sequel and stumbled on it raising an OCI
error ("not all variables bound").
I think there are a couple things that are getting intertwined:
1) My proposal to extend the Sequel syntax - bind vars or not - to
support :named placeholders like other DB toolsets
2) Your concern that bind variables can be enabled/disabled per-query
since some adapters (or queries) may not want to use them
3) Backwards compatibility/spec/adapter concerns (I'm a huge proponent
of backwards compat, so let's assume that one will be solved)
Regarding #1, let me try to show why this is really powerful. Here's
a real snippet from our codebase:
curs = conn.parse("SELECT lb.* FROM
(SELECT rownum as rn, a.* FROM
(SELECT * FROM #{table_name}
WHERE game_type_id = :game_type_id
AND platform_id = :platform_id
AND rank >= :rank-:num_above_below
AND rank <= :rank+:num_above_below
ORDER BY rank) a) lb,
(SELECT rn from
(SELECT rownum as rn, b.player_id FROM
(SELECT player_id FROM #{table_name}
WHERE game_type_id = :game_type_id
AND platform_id = :platform_id
AND rank >= :rank-:num_above_below
AND rank <= :rank+:num_above_below
ORDER BY rank) b)
WHERE player_id = :player_id) p_rownum
WHERE lb.rn >=
p_rownum.rn-:num_above_below AND lb.rn <= p_rownum.rn+:num_above_below
ORDER BY lb.rank", sql_args])
sql_args = {:game_type_id => game_type_id,
:platform_id => platform_id,
:player_id => player_id,
:rank => player.rank,
:num_above_below => num_above_below}
You'll notice that the bind params are reused multiple times in the
statement - :player_id, :num_above_below, etc. (That query is highly-
tuned c/o my team's DBA)
When that query was first written, the programmer used ?'s and
positional binding, and it was a mess. You couldn't tell which ? went
with which parameter, and it actually had a bug since the wrong one
was bound to the wrong position, and it took a good long while to
figure out.
So in this case, your suggestion of wrapping the existing API:
dataset.bindfilt('id = :id', :id=>1) == dataset.filter('id = ?', :
$id).bind(:id=>1)
Doesn't work, since it's the *:named placeholder itself* that's so
important. Replacing it with ?'s results in the problems I mentioned
above.
2) Regarding enabling/disabling bind vars, as a variant of your idea,
how about this:
# No bind variables (default behavior remains unchanged)
User.filter('id = ?', 14).all
User.filter('id = :id', :id => 14).all
User.filter('lower(city) like lower(:search) or lower(state) like
lower(:search)', :search => string).all
# Enable bind variables by calling bind()
User.filter('id = ?', 14).bind.all
User.filter('id = :id', :id => 14).bind.all
# bind() accepts delayed bind args too
User.filter('lower(city) like lower(:search) or lower(state) like
lower(:search)').bind(:search => string).all
Additionally, how about an adapter setting (off by default) to
globally always use bind()?
DB = Sequel.connect(..., :logger => ..., :always_bind_variables =>
true) # or whatever
Then a person could still disable it per-query with bind(false):
User.filter('id = ?', 14).bind(false).all
This way we get the "best of all worlds."
Finally, there's no reason why the existing API couldn't remain around
for compat, and this stuff could all be shortcuts to it. But
the :named placeholders would have to go in core since they're in the
sql string like ? is.
Thoughts?
-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
-~----------~----~----~----~------~----~------~--~---
