Re: [fw-db] Zend_Db enhancements

On Thu, Jul 1, 2010 at 8:02 PM, Bill Karwin <bill@karwin.com> wrote:

On Jul 1, 2010, at 5:42 AM, FeIn wrote:

I have a couple of questions. First question is related to having the possibility to provide arrays in $bind parameter of Zend_Db_Adapter_Abstract::query.

This is a lot harder than it may seem.

First, how would you handle the case of multiple executions of the same prepared query, with different parameter values?

 $stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND z IN (?)");
 $stmt->execute(array(1, array(1,2,3)));
 $stmt->execute(array(2, array(1,2,3,4,5,6)));

In your plan, your decorator must rewrite the parameter placeholders prior to prepare(), but you don't know how many elements in the array of values until execute-time.  

That's exactly the problem. I have found that the need to use queries where the number of parameters for IN() is not known until run-time is pretty common. By the way, is there other alternative to IN() besides "OR z = ? OR z = ? OR z = ? ..." that could help in this situation?
 

You would have to discard the prepared query handle and re-prepare a modified query during every execution, which would spoil the performance benefit of re-using the prepared statement.

I am aware of the performance implications, but I thought this can be a "use at your own risk/use only if you know what you are doing" kind of feature :).
 

Second, how would you tell the difference between a valid use of array binding and an invalid use?

 $stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND z IN (?)");
 $stmt->execute(array(array(1,2,3), 1)); // is this valid? no.

To keep the implementation simple the client code should be responsible for putting the array(s) in the correct place. I quote from Zend_Auth_Storage_Interface::

read method "Behavior is undefined when storage is empty.".  So, there are times when, in order to avoid complexity, the responsibility of using a function/method correctly is put to the client code.

 

You would have to write a parser that knew that it's okay to bind an array to the placeholder for IN(), but the placeholder for = only accepts a single value.  ...Or does it?  SQL allows you to compare tuples:

 $stmt = $adapter->prepare("SELECT FROM x WHERE (y, z) = (?)");
 $stmt->execute(array(array(1,1)));  // should this be supported? I don't think so.

Finally, how do you propose to tell the difference between parameter placeholders and string literals that happen to look like parameter placeholders?

 $stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND symbol = '?'");
 $stmt->execute(array(1, '$')); // does this need two params? no.

 $stmt = $adapter->prepare("SELECT FROM x WHERE y = ? AND tag = ':foo'");
 $stmt->execute(array(1, ':foo'=>'performance')); // does this need two params? no.

I agree with you that there are many edge cases and this can get very complex.
 

The solution I use for binding arrays to IN() predicates is to prepare as many placeholders as there are elements in my array.  It just takes a couple of lines of PHP code to produce the syntax:

 $zlist = array(1,2,3);
 $placeholders = join(",", array_fill(0, count($zlist), "?"));
 $sql = "SELECT * FROM x WHERE y = ? AND z IN ({$placeholders})";
 $stmt = $adapter->prepare($sql);
 $stmt->execute(array_merge(array(1), $zlist));

But I do this on a case-by-case basis so I'm sure it's working as I intend.  

I did it like this also, but this problem keeps repeating itself, so naturally I wanted to encapsulate the solution (or a partial solution) to avoid repeating myself every time.
 

That helps me avoid all the problems from corner cases that I described above.

Regards,
Bill Karwin

Thanks for your answer.