compared with
Key
This line was removed.
This word was removed. This word was added.
This line was added.

Changes (11)

View Page History
abstraction of SQL functions. This proposal aims to close the gap as much as possible by offering
an Expression object inside Zend_Db_Select and Zend_Db_Adapter_Abstract. The largest possible
subset of functions between all the current supported vendors is integrated. Additionally integrated, possible a subset of ANSI-SQL 92.
"non" compatible modus will be included that abstracts as much functions from each vendor as possible.

Each implemented Expression/SQL Function will enforce the escaping of values.
{zone-data}

* The Adapter *SHOULD* enforce the expression object to be created only once.
* This component *WILL* offer a non-compliant modus where additional vendor-specific functions are nested in
* This component *WILL* use quoting/escaping facilities internally.
* This component *WILL* enforce escaping of all variables and columns in each expression.
* Use of this component with Zend_Db_Select is *OPTIONAL*, the old way of implementing expressions stays the same.
{zone-data}


{zone-data:operation}
You can use the Query Expression object to generate Zend_Db_Expr instances with specific supported SQL functions.

The question is, how would this query expression object be available to the consumer? There are several possible APIs:
{code}

I opt for a simple syntax, which makes the usage as non-verbose as possible.
I think the expr() method is the best choice although its the longest, because other programming languages like
C# or Java use Expression as a name for objects that perform actions on values.

Use of it would follow the lines of:

{code}
$select = $db->select();
$select->from("table")
->where( $select->expr()->eq("columnName", $value) );
->where( $select->expr()->like("columnName2", $value2."%") );
->where( $select->expr()->notEq($select->expr()->upper("col3", $value3)) );
{code}

In a first version I would opt to implement a common subset of functions accross all major database vendors, probably
ANSI SQL 92 is a good start. Additionally if "non" compatible modus will be included that abstracts as much functions
from each vendor as possible.

Because some functions have conflicting names in different vendor languages, i want to go away from sql naming
in some cases and use programming language names on some issues. I guess the hardest part of this proposal is the naming
issue, implementation should be easy if everything is agreed on.
{zone-data}

{code}
$select->fn()->eq("foo", $var, Zend_Db::PARAM_INT); // $db->quoteInto("foo = ?", $var, Zend_Db::PARAM_INT);
$select->fn()->notEq("foo", $var, Zend_Db::PARAM_INT); // $db->quoteInto("foo != ?", $var, Zend_Db::PARAM_INT);
$select->fn()->lt("foo", $var); // $db->quoteInto("foo < ?", $var);
$select->fn()->gt("foo", $var); // $db->quoteInto("foo > ?", $var);
{card:label=Use-Case 5: NULL Operations}
{code}
$select->fn()->null("column"); $select->fn()->eqNull("column"); // $db->quoteIdentifier("column")." IS NULL";
$select->fn()->notEqNull("column");
{code}
{deck}
{code}
$select->fn()->if( $select->fn()->eq("foo", 42), "a", "b");
$select->fn()->ifnull($var, $select->fn()->eqNull($var, "1234", Zend_Db::PARAM_INT);
$select->fn()->case(
array(), array(), ...