Skip to end of metadata
Go to start of metadata

<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

<ac:macro ac:name="unmigrated-inline-wiki-markup"><ac:plain-text-body><![CDATA[

Zend Framework: Zend_Db_Expr Function Abstraction Component Proposal

Proposed Component Name Zend_Db_Expr Function Abstraction
Developer Notes http://framework.zend.com/wiki/display/ZFDEV/Zend_Db_Expr Function Abstraction
Proposers Benjamin Eberlei
Zend Liaison TBD
Revision 1.0 - 3 August 2009: Initial Draft. (wiki revision: 7)

Table of Contents

1. Overview

Currently Zend_Db_Select does not allow to write completly portable applications when it comes to
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, possible a subset of ANSI-SQL 92.

2. References

3. Component Requirements, Constraints, and Acceptance Criteria

  • This component MUST abstract as much SQL functions as possible that are common between: MySQL, PgSql, SqLite, Oracle, IBM Db2, SqlSrv
  • This component MUST be nested inside each Zend_Db_Select instance and globally inside the Adapter
  • 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.

4. Dependencies on Other Framework Components

  • Zend_Db_Adapter_Abstract
  • Zend_Db_Select

5. Theory of 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:

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:

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.

6. Milestones / Tasks

  • Milestone 1: Community Review
  • Milestone 2: Zend Acceptance
  • Milestone 3: Implementation & Documentation

7. Class Index

  • Zend_Db_Expr_ExpressionAbstract
  • Zend_Db_Expr_Mysql
  • Zend_Db_Expr_Sqlite
  • Zend_Db_Expr_Oracle
  • Zend_Db_Expr_IbmDb2
  • Zend_Db_Expr_PgSql
  • Zend_Db_Expr_MsSqlServer

8. Use Cases

9. Class Skeletons

]]></ac:plain-text-body></ac:macro>

]]></ac:plain-text-body></ac:macro>

Labels:
None
Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.

12 Comments

comments.show.hide

  1. Aug 04, 2009

    Luiz Fernando Furtado

    <p>Hi, using silverstripe CMS I found, IMHO a great thing. It's just a small piece of code, no complexity, I translated to ZF as follow:</p>

    <ac:macro ac:name="code"><ac:plain-text-body><![CDATA[
    $stmt = array(
    'others' => 'select slq generic',
    'mysqli' => 'select mysql specific...',
    'oci' => 'select oracle specific...',
    'firebird' => 'select firebird specific...',
    );

    $dbconn->fetchAll($stmt); // and others functions
    ]]></ac:plain-text-body></ac:macro>

    <p>The adapter select a sql based on the adapter type.</p>

    <p>I hope that complex queries or vendor specific otimized queries this aproach is very usefull.</p>

    <p>Best regards.</p>

  2. Aug 17, 2009

    Stephan Wentz

    <p>This is very nice. I did the same code here, but only for mysql and postgres. This required modification of the adapters to include the fn() method, right?</p>

    <p>I'd love to see more complex functions like date comparison functions.</p>

    <p>Is there code that can be reviewed/tested somewhere? Maybe I can provide you code that is missing in your version.</p>

    1. Aug 17, 2009

      Benjamin Eberlei

      <p>its only a proposal without code for now, more complex functinos can be added, however depending on the proposals conclusion on wheater to support database specific operations or not.</p>

      1. Aug 17, 2009

        Stephan Wentz

        <p>Like I wrote before, we have something like this working. But without the $db->fn() or $select->fn() support. This would require changing the Adapters or Zend_Db_Select, but it would be the best solution for this.</p>

  3. Oct 28, 2009

    Keith Pope

    <p>This looks good to me, I think it would also go some way to eventually creating a generic criteria type object that could be used to query entities via repositories.</p>

  4. Oct 29, 2009

    Matthew Weier O'Phinney

    <ac:macro ac:name="note"><ac:parameter ac:name="title">Zend Acceptance</ac:parameter><ac:rich-text-body>
    <p>This proposal is accepted for immediate development in the standard incubator.</p>

    <p>We are particularly excited about this proposal, as it also raises the idea of a standard "criteria" or query object that could be used even outside RDBMS systems.</p></ac:rich-text-body></ac:macro>

  5. Oct 29, 2009

    Stephan Wentz

    <p>It would be great if the Expression could be extended by own functions.</p>

    <p>Benjamin, which range of expressions are you going to implement? Only expressions? Or functions, too?</p>

    <p>Maybe the whole thing should be implemented in a way that you can add your own extensions. Like plugins to a Zend_Db_Adapter_Abstract.<br />
    Zend_DB_Expr would provide $db->expr(), other (or own) classes could provide something like $db->fn(), for function abstraction.</p>

  6. Nov 17, 2009

    Sébastien Cramatte

    <p>I'm interested in this class but I don't see it in svn incubator ? </p>

  7. Feb 02, 2010

    Stephan Wentz

    <p>Maybe this should be split into $select->op() for operators and $select->fn() for functions.<br />
    Operators should be equal in most of the RDBMs, functions not.</p>

  8. Feb 02, 2010

    Luiz Fernando Furtado

    <p>What about concatenation?</p>

    <p>Firebird, Postgree, MSSQL (and Oracle?) use the operator sintax: 'literal' || 'literal2' || fieldX<br />
    MSSQL use + in the place of ||</p>

    <p>mySQL use a buildin funcion concat: concat('literal', 'literal2', fieldX)</p>

    <p>Is it a $select->op() or $select->fn() ? Please explain more about diferences.</p>

    <p>A sugestion:<br />
    new Zend_Db_Expr('oracle' => '..', 'mssql' => '..', 'othes' => '..')</p>

    1. Feb 02, 2010

      Stephan Wentz

      <p>MSSQL wouldn't be a problem - $select->op()->concat('a', 'b', 'c') would lead to 'a' + 'b' + 'c'.<br />
      MySQL - yea. concat() really sucks. The first thing I do on bigger projects in turn sql_mode in MySQL to PIPES_AS_CONCAT...<br />
      But, here also, $select->op()->concat('a', 'b', 'c') would result in CONCAT('a', 'b', 'c') - op() or fn() should use the original ANSI syntax. And that would mean concatenation is an operator.</p>

  9. Sep 07, 2010

    Martin Hujer

    <p>Any progress on this proposal? Does some code exist? I'm just about to need this for my project, so I don't want to do it again uselessly</p>