Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

ralphschindler
Hey All,

I have created a pull request for new features and fixes within Zend\Db.
  It is located here:

   https://github.com/zendframework/zf2/pull/934

but in short these are the improvements:

   * It now has expression objects that can be used in
     place of column names

   * Verbiage change:  No longer using "databaseOrSchema",
     or "databaseSchema". Instead, always using "schema".

   * Table Gateway has a selectWith(Select $select) method

   * Improved unit testing for Select and Adapter

   * PredicateSet has a getPredicates() method

   * Where object is now simply an facade of PredicateSet.

   * Cleaned up internal implementations of Sql specifications
     and joins to use string keys as associative array

   * Fixed PDO Driver Connection to create proper SQLite
     connection string (was broken by previous patch)

I have also a few floating questions that need some answers, or some
justification:

   * Current Zend\Db\Sql\Select is not prefixing the columns
     with the table name.  It seems like the default behavior
     should be to prefix them, especially in cases where joins
     are used. Thoughts?

   * Does anyone have an example of how Union should work? I
     need both a simple and complex example.

   * Does anyone have examples of using aliases or the need for
     Expressions inside the columns of a join statement?

   * When using where, if you use the following syntax:

     where(array(
       'name' => 'Foo',
       'age' => 33
     ));

     becomes: WHERE "name" = 'Foo' AND "age" = '33'

     does this usage make sense considering this API:

     where(array(
       'name = ?' => 'Foo',
       'age = ?' => 33
     ))

     becomes: as WHERE name = 'Foo' AND age = '33'

     Notice we've overloaded the string => string representation
     and utilize the operator when a ? is not found, is this
     a valid API, or should we drop the operator (AND/OR)
     combination from where?


There are probably some more, but until the current request makes it
into master and more usages cases emerge, my attention is turning to
Zend\Di for some time.

Let me know what you guys thing of Zend\Db.

-ralph

Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

Tomáš Fejfar
Reply inline

On Wed, Mar 21, 2012 at 3:00 PM, Ralph Schindler <[hidden email]> wrote:
Hey All,

I have created a pull request for new features and fixes within Zend\Db.  It is located here:

 https://github.com/zendframework/zf2/pull/934

but in short these are the improvements:

 * It now has expression objects that can be used in
   place of column names
Great! :) 

 * Verbiage change:  No longer using "databaseOrSchema",
   or "databaseSchema". Instead, always using "schema".

 * Table Gateway has a selectWith(Select $select) method

...

I have also a few floating questions that need some answers, or some justification:

 * Current Zend\Db\Sql\Select is not prefixing the columns
   with the table name.  It seems like the default behavior
   should be to prefix them, especially in cases where joins
   are used. Thoughts?
Yes. Even for selects without joins IMO.  

 * Does anyone have an example of how Union should work? I
   need both a simple and complex example.
$select1 = $this->getSomeSelect();
$select2 = $this->getOtherSelect();
$select3->union($select1, $select2);
$select4->union($select1, $select2, Db::UNION_ALL); 
$select5->columns($columns)->union($select1, $select2); //select some cols from unioned subselect

 * Does anyone have examples of using aliases or the need for
   Expressions inside the columns of a join statement?

 * When using where, if you use the following syntax:

   where(array(
     'name' => 'Foo',
     'age' => 33
   ));

   becomes: WHERE "name" = 'Foo' AND "age" = '33'

   does this usage make sense considering this API:

   where(array(
     'name = ?' => 'Foo',
     'age = ?' => 33
   ))

   becomes: as WHERE name = 'Foo' AND age = '33'

   Notice we've overloaded the string => string representation
   and utilize the operator when a ? is not found, is this
   a valid API, or should we drop the operator (AND/OR)
   combination from where?
Not sure, but I would love to see this working: 
where('my_int_col BETWEEN ? AND ?', array(3,4))
Currently (ZF1) it replaces both ? with same parameter. 


There are probably some more, but until the current request makes it into master and more usages cases emerge, my attention is turning to Zend\Di for some time.

Let me know what you guys thing of Zend\Db.

-ralph


Reply | Threaded
Open this post in threaded view
|

Fwd: [zf-contributors] Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

robertbasic
In reply to this post by ralphschindler
On Wed, Mar 21, 2012 at 3:00 PM, Ralph Schindler
<[hidden email]> wrote:
> Hey All,
>
>
>  * Improved unit testing for Select and Adapter
>
>  * PredicateSet has a getPredicates() method

This is simply my lack of knowledge, but what's a "predicate"? I don't
see the RFC explaining that.


> I have also a few floating questions that need some answers, or some
> justification:
>
>  * Current Zend\Db\Sql\Select is not prefixing the columns
>    with the table name.  It seems like the default behavior
>    should be to prefix them, especially in cases where joins
>    are used. Thoughts?

+1 on prefixing

>
>  * Does anyone have an example of how Union should work? I
>    need both a simple and complex example.

This [1] is a UNION I wrote a few days back in a ZF1 project, haven't
wrote anything more complicated than that.

[1] https://gist.github.com/2147227

>
>  * Does anyone have examples of using aliases or the need for
>    Expressions inside the columns of a join statement?
>

I just went through a bunch of SQLs in ZF1 projects, and when I used
Expressions (Zend_Db_Expr) with JOINed tables, I always wrote it so
that the Expression goes in to the "main" table, the one defined in
the "FROM" clause.
I couldn't find an example of using an alias in a joined table, but
that's probably because I always prefix the column names with the
table name (crazy, I know!).


>
> Let me know what you guys thing of Zend\Db.

Thanks for your hard work! :)

>
> -ralph
>

--
~Robert Basic;
http://robertbasic.com/
Reply | Threaded
Open this post in threaded view
|

Re: Fwd: [zf-contributors] Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

ralphschindler


> This is simply my lack of knowledge, but what's a "predicate"? I don't
> see the RFC explaining that.

http://en.wikipedia.org/wiki/File:SQL_ANATOMY_wiki.svg

The predicates are the sets of "expressions" that make up the WHERE clause.

In our case, an expression analogous to any fragment of SQL code.


>> I have also a few floating questions that need some answers, or some
>> justification:
>>
>>   * Current Zend\Db\Sql\Select is not prefixing the columns
>>     with the table name.  It seems like the default behavior
>>     should be to prefix them, especially in cases where joins
>>     are used. Thoughts?
>
> +1 on prefixing

This is my inclination as well after using Zend\Db in a project over the
weekend.  I just wanted to hear it from others as well before it created
more verbose SQL.

>>
>>   * Does anyone have an example of how Union should work? I
>>     need both a simple and complex example.
>
> This [1] is a UNION I wrote a few days back in a ZF1 project, haven't
> wrote anything more complicated than that.
>
> [1] https://gist.github.com/2147227

Noted.


>>
>>   * Does anyone have examples of using aliases or the need for
>>     Expressions inside the columns of a join statement?
>>
>
> I just went through a bunch of SQLs in ZF1 projects, and when I used
> Expressions (Zend_Db_Expr) with JOINed tables, I always wrote it so
> that the Expression goes in to the "main" table, the one defined in
> the "FROM" clause.
> I couldn't find an example of using an alias in a joined table, but
> that's probably because I always prefix the column names with the
> table name (crazy, I know!).

With regards to that, I think our current join functionality (inside the
pull request), reflects what you're currently doing.  Ping me back if not.

>>
>> Let me know what you guys thing of Zend\Db.
>
> Thanks for your hard work! :)

:)

>>
>> -ralph
>>
>
> --
> ~Robert Basic;
> http://robertbasic.com/

Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

ralphschindler
In reply to this post by Tomáš Fejfar
Inline.


>       * Does anyone have an example of how Union should work? I
>         need both a simple and complex example.
>
> $select1 = $this->getSomeSelect();
> $select2 = $this->getOtherSelect();
> $select3->union($select1, $select2);
> $select4->union($select1, $select2, Db::UNION_ALL);
> $select5->columns($columns)->union($select1, $select2); //select some
> cols from unioned subselect

In your example, the union() is acting like a factory of sorts, is that
what we want?  Or do we want to chain them?

$select1->union($select2); // returns $this

Where $select1 is now the union of everything before plus the union of
every in select2?

The problem is is that in your example, a union appears to be not a
SELECT at all, but a SQL Statement that is the combination of two
SELECTS into a special UNION clause.  IF that is the case, it would seem
that Union would need its own object type.

$union = new Sql\Union($select1, $selec2);

or

$union = new Sql\Union();
$union->addSelect($select1);
$union->addSelect($select2);

> Not sure, but I would love to see this working:
> where('my_int_col BETWEEN ? AND ?', array(3,4))
> Currently (ZF1) it replaces both ? with same parameter.

use Zend\Db\Sql\Select,
     Zend\Db\Sql\Predicate;

$select = new Select('foo_table');
$select->where(array('my_int_col BETWEEN ? AND ?' => array(3,4)));
echo $select->getSqlString() . PHP_EOL;

// or better

$select = new Select('foo_table');
$select->where(new Predicate\Between('my_in_col', 3, 4));
echo $select->getSqlString();
Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

Tomáš Fejfar
Inline

On Wed, Mar 21, 2012 at 4:39 PM, Ralph Schindler <[hidden email]> wrote:
Inline.



     * Does anyone have an example of how Union should work? I
       need both a simple and complex example.

$select1 = $this->getSomeSelect();
$select2 = $this->getOtherSelect();
$select3->union($select1, $select2);
$select4->union($select1, $select2, Db::UNION_ALL);
$select5->columns($columns)->union($select1, $select2); //select some
cols from unioned subselect

In your example, the union() is acting like a factory of sorts, is that what we want?  Or do we want to chain them?

$select1->union($select2); // returns $this

Where $select1 is now the union of everything before plus the union of every in select2?

The problem is is that in your example, a union appears to be not a SELECT at all, but a SQL Statement that is the combination of two SELECTS into a special UNION clause.  IF that is the case, it would seem that Union would need its own object type.
That's implementation detail. The main idea is - look how the UNION is created. It can be "abstractly" written like ($select) UNION ($select) - it's not $select WHERE something = 1 UNION $subselect -- both selects are equal and that's what I tried to say by that API. 

$union = new Sql\Union($select1, $selec2);

or

$union = new Sql\Union();
$union->addSelect($select1);
$union->addSelect($select2);

I very much like this API. Union is kind of select. It can have some of select's abbilities - like limits or orders:


(SELECT col1 AS a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT col2 AS a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;
http://dev.mysql.com/doc/refman/5.0/en/union.html

There are some nasty things about this - like that when you use aliases, you need to use alias in the ORDER, because the order is performed on the resulting dataset. 

Also it should work as subselect for scenarios like:

SELECT column_alias1 FROM ( ($select1) UNION ($select2) ) WHERE column_alias1 > 5

Also nasty problem (from discussion on the page above):
UNIONs explicitly return an unordered list unless you sort the UNION as a whole. Thus, any orderings in the source SELECTs are optimized away unless they are accompanied by a LIMIT (because then they still actually do something).


Not sure, but I would love to see this working:
where('my_int_col BETWEEN ? AND ?', array(3,4))
Currently (ZF1) it replaces both ? with same parameter.

use Zend\Db\Sql\Select,
   Zend\Db\Sql\Predicate;

$select = new Select('foo_table');
$select->where(array('my_int_col BETWEEN ? AND ?' => array(3,4)));
echo $select->getSqlString() . PHP_EOL;

// or better

$select = new Select('foo_table');
$select->where(new Predicate\Between('my_in_col', 3, 4));
echo $select->getSqlString();
Very cool ! :) 

Last thing is .... why is the __toString() gone? (or it was in beta3 I guess) I kinda like it :)

Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

DeNix
In reply to this post by ralphschindler
I'm for the separate UNION object, bacause, when select object holds
multiple SELECTs withing UNION

$select3->union($select1, $select2); , or $select1->union($select2);

it's not clear what $select1->where should return



On 21.03.2012 19:39, Ralph Schindler wrote:

> Inline.
>
>
>>       * Does anyone have an example of how Union should work? I
>>         need both a simple and complex example.
>>
>> $select1 = $this->getSomeSelect();
>> $select2 = $this->getOtherSelect();
>> $select3->union($select1, $select2);
>> $select4->union($select1, $select2, Db::UNION_ALL);
>> $select5->columns($columns)->union($select1, $select2); //select some
>> cols from unioned subselect
>
> In your example, the union() is acting like a factory of sorts, is
> that what we want?  Or do we want to chain them?
>
> $select1->union($select2); // returns $this
>
> Where $select1 is now the union of everything before plus the union of
> every in select2?
>
> The problem is is that in your example, a union appears to be not a
> SELECT at all, but a SQL Statement that is the combination of two
> SELECTS into a special UNION clause.  IF that is the case, it would
> seem that Union would need its own object type.
>
> $union = new Sql\Union($select1, $selec2);
>
> or
>
> $union = new Sql\Union();
> $union->addSelect($select1);
> $union->addSelect($select2);
>
>> Not sure, but I would love to see this working:
>> where('my_int_col BETWEEN ? AND ?', array(3,4))
>> Currently (ZF1) it replaces both ? with same parameter.
>
> use Zend\Db\Sql\Select,
>     Zend\Db\Sql\Predicate;
>
> $select = new Select('foo_table');
> $select->where(array('my_int_col BETWEEN ? AND ?' => array(3,4)));
> echo $select->getSqlString() . PHP_EOL;
>
> // or better
>
> $select = new Select('foo_table');
> $select->where(new Predicate\Between('my_in_col', 3, 4));
> echo $select->getSqlString();

Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

rhunwicks
In reply to this post by ralphschindler
On 21/03/12 17:00, Ralph Schindler wrote:
>   * Current Zend\Db\Sql\Select is not prefixing the columns
>     with the table name.  It seems like the default behavior
>     should be to prefix them, especially in cases where joins
>     are used. Thoughts?
+1 to prefixing in all cases
>   * Does anyone have an example of how Union should work? I
>         need both a simple and complex example.
I prefer $select->union($select2) to new Sql\Union.

Will you allow $select1->union($select2)->union($select3)?

Will you enforce that the number of columns in each additional select
match the first one?
>   * Does anyone have examples of using aliases or the need for
>     Expressions inside the columns of a join statement?
Tables with self-referencing foreign key will need aliases:

$select->columns(array('first_name', 'last_name'))
        ->from('emp')
        ->join('emp as manager', 'manager.emp_id = emp.manager',
array('manager_first_name'=>'first_name, 'manager_last_name'=>'last_name'));
echo $select->getSqlString;

SELECT
"emp"."first_name",
"emp"."last_name",
"manager"."first_name" AS "manager_first_name",
"manager"."last_name" AS "manager_last_name"
FROM
"emp"
JOIN "emp" AS "manager" ON ("manager"."emp_id" = "emp"."manager")

It's a somewhat contrived example, but given a table and a history table
I might want to retrieve the last 30 days worth of updates for a row in
the parent table - i.e. 30 days before the last time the row was modified:

$select->from('emp')
        ->join('history',
               new Expression('history.emp_id = emp.emp_id AND
history.history_timestamp > (emp.last_modified_timestamp - 30)'),
               array(self::SQL_WILDCARD)
          )
);
echo $select->getSqlString;

SELECT
history.*
FROM
emp
JOIN history ON (history.emp_id = emp.emp_id AND history_timestamp >
(emp.last_modified__timestamp - 30));
> There are probably some more, but until the current request makes it
> into master and more usages cases emerge, my attention is turning to
> Zend\Di for some time.
>
Is there any plan to add to Zend\Db\Metadata during Beta4: in particular
I'd like to know if there will be an easy way to get the primary key for
a table, and the foreign keys - currently there is only
TableObject::getConstraints() but it actually returns $this->columns -
will there be a TableObject->getPrimaryKey() or similar?

Similarly, are there are any plans to allow injecting the
Metadata\Source into the Metadata\Metadata using DI? At the moment the
Metadata\Source is derived from the Adapter and there not easy to
override without subclassing Metadata\Metadata

I'm happy to work on these bits if it helps.
> Let me know what you guys thing of Zend\Db.
I like the new Zend\Db - I'm slowly converting our ZF1 code to ZF2 and
we are very heavy users of Zend_Db_Table / Zend_Db_Table_Row. Our
subclasses used a custom metadata driver to get additional metadata from
the database and build the models and forms automatically. I'm planning
to take the same approach with our ZF2 models, only using Events (and
maybe traits) to avoid making our TableGateway and RowGateway too heavy.

Thanks for your efforts!

Roger
Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

rhunwicks
In reply to this post by ralphschindler
On 21/03/12 17:00, Ralph Schindler wrote:
> There are probably some more, but until the current request makes it
> into master and more usages cases emerge, my attention is turning to
> Zend\Di for some time.
>
> Let me know what you guys thing of Zend\Db.
>
> -ralph
>
I thought of another use case that I am not sure is working yet, but is
pretty important: $select->limit(), or $select->fetch($number, $offset)
which seems to serve the same purpose, but doesn't seem to be
implemented yet. I don't think you can do pagination of large data sets
without it.

Roger


Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

ralphschindler

> I thought of another use case that I am not sure is working yet, but is
> pretty important: $select->limit(), or $select->fetch($number, $offset)
> which seems to serve the same purpose, but doesn't seem to be
> implemented yet. I don't think you can do pagination of large data sets
> without it.


That is happening for this next beta.  Realize that this is the least
standard feature supported by RDMS.  As such, we need to have a solution
in place that will allow for dynamically injecting the proper vendor
based implementation.  The infrastructure (in my pull request) is there,
just not the vendor specific implementations.

-ralph
Reply | Threaded
Open this post in threaded view
|

Re: Updates to Zend\Db, Questions ISO Answers, and Use Cases Needed

xoops
In reply to this post by rhunwicks


On Thu, Mar 22, 2012 at 1:38 AM, Roger Hunwicks <[hidden email]> wrote:
On 21/03/12 17:00, Ralph Schindler wrote:
 * Current Zend\Db\Sql\Select is not prefixing the columns
   with the table name.  It seems like the default behavior
   should be to prefix them, especially in cases where joins
   are used. Thoughts?
+1 to prefixing in all cases

 * Does anyone have an example of how Union should work? I
       need both a simple and complex example.
I prefer $select->union($select2) to new Sql\Union.

Will you allow $select1->union($select2)->union($select3)?

Will you enforce that the number of columns in each additional select match the first one?

 * Does anyone have examples of using aliases or the need for
   Expressions inside the columns of a join statement?
Tables with self-referencing foreign key will need aliases:

$select->columns(array('first_name', 'last_name'))
      ->from('emp')
      ->join('emp as manager', 'manager.emp_id = emp.manager', array('manager_first_name'=>'first_name, 'manager_last_name'=>'last_name'));
echo $select->getSqlString;


Bump up.
Any plan on join alias?

 
SELECT
"emp"."first_name",
"emp"."last_name",
"manager"."first_name" AS "manager_first_name",
"manager"."last_name" AS "manager_last_name"
FROM
"emp"
JOIN "emp" AS "manager" ON ("manager"."emp_id" = "emp"."manager")

It's a somewhat contrived example, but given a table and a history table I might want to retrieve the last 30 days worth of updates for a row in the parent table - i.e. 30 days before the last time the row was modified:

$select->from('emp')
      ->join('history',
             new Expression('history.emp_id = emp.emp_id AND history.history_timestamp > (emp.last_modified_timestamp - 30)'),
             array(self::SQL_WILDCARD)
        )
);
echo $select->getSqlString;

SELECT
history.*
FROM
emp
JOIN history ON (history.emp_id = emp.emp_id AND history_timestamp > (emp.last_modified__timestamp - 30));

There are probably some more, but until the current request makes it into master and more usages cases emerge, my attention is turning to Zend\Di for some time.

Is there any plan to add to Zend\Db\Metadata during Beta4: in particular I'd like to know if there will be an easy way to get the primary key for a table, and the foreign keys - currently there is only TableObject::getConstraints() but it actually returns $this->columns - will there be a TableObject->getPrimaryKey() or similar?

Similarly, are there are any plans to allow injecting the Metadata\Source into the Metadata\Metadata using DI? At the moment the Metadata\Source is derived from the Adapter and there not easy to override without subclassing Metadata\Metadata

I'm happy to work on these bits if it helps.

Let me know what you guys thing of Zend\Db.
I like the new Zend\Db - I'm slowly converting our ZF1 code to ZF2 and we are very heavy users of Zend_Db_Table / Zend_Db_Table_Row. Our subclasses used a custom metadata driver to get additional metadata from the database and build the models and forms automatically. I'm planning to take the same approach with our ZF2 models, only using Events (and maybe traits) to avoid making our TableGateway and RowGateway too heavy.

Thanks for your efforts!

Roger



--

Taiwen Jiang (aka D.J.)

Build Xoops Engine
web and mobile application platform

CTO for EEFOCUS.com
Leading social platform for electronics professionals