compared with
Current by Thomas Weidner
on Apr 05, 2010 08:54.

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

Changes (14)

View Page History
<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[{zone-template-instance:ZFPROP:Proposal Zone Template}
{composition-setup}

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(), ...

{zone-data}
{zone-template-instance}
{zone-template-instance}]]></ac:plain-text-body></ac:macro>