On Jul 15, 5:48 pm, David Lee <[EMAIL PROTECTED]> wrote: > > Prepared Statements: This would allow you to prepare a statement once > > and call it multiple times with different inputs. This is potentially > > much faster on certain databases, up to 60% on PostgreSQL, and from > > what I've heard possibly more on Oracle. Here's how I think it should > > work: > > > DB[:items].filter(:x=>:S?).prepare(:items_with_x) # Hopefully nobody > > uses a column named "S?" > > DB.call(:items_with_x, 2) # The equivalent of SELECT * FROM items > > WHERE x = 2, but faster > > Since #filter accepts a hash, there is no way to know what order you > must provide the parameters. If we're going to go with the :x=>:S? > route, I think we should provide the following two ways to specify the > parameters: > > dataset.filter(:x => :S?, :x => :T?).prepare(:items) > > 1. Using hashes: > DB.call(:items, :S? => 3, :T? => "hi") > > 2. Using multiple arguments > DB.call(:items, 3, "hi") # parameters are alphabetized, so 3 would > map to :S? and "hi" would map to :T?
For a long time you have been able to specify equality in filters using an array, preserving order: dataset.filter([[:x, :S?], [:x, :T?]]).prepare(:items) That's not to say that using multiple placeholders isn't possible. The idea of calling a prepared statement with a hash is an interesting use case. I'm not sure how easy it will be to add that functionality though. > Also, should #prepare return a PreparedStatement object? > > x = dataset.filter(:x=>:S?).prepare(:asdf) > x.call(3) > x.call(4) > x = DB.prepared[:asdf] > x.call(5) > x.call(6) I like this idea, and it should be easy to implement once prepared statement support is in place. > Finally, I think #call should be #exec or #execute, since that's what > I've seen most often in other implementations. Database#execute is already taken, and #exec is too similar to it. While it would be possible to make #execute support both depending on the arguments, #execute is adapter specific, and would need to be overridden in all adapters, which I'm hesitant to do since I can't test all of them. Database#call will have a default implementation that will interpolate the variables manually and then just call execute with the resulting string, so it will work on all databases. Database adapters can then override #call to use parametrized queries, prepared statements, or stored procedures. Before I get started on this work, I need to read up on how different databases handle things so I don't tie the design to any one database. 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 -~----------~----~----~----~------~----~------~--~---
