New ideas/questions for Zend_Db_Table

[dinok]

Hi all,

I am currently working on some improvements for Zend_Db_Table.
These improvements provide an easier and faster way for database relationships.
As I've often seen, many people want to be able to use JOINs and other things.

So my improvements are:
- enable JOINs for one to one relationships
- enable preFetch for one to many relationships (one SELECT for all related, php assigns each record to the related table
- easy way to cache complete objects with relations
- some kind of a singleton implementation for TableClasses (static class like the registry? or get instance in each table class)


The first two points are working in a early status, but now my problem:
If I got a resultset with 10 rows and each of these rows(e.g. users)  owns zero, one or many related rows (e.g. email addresses), I always put references of the tables in the rowsets.
This has two negative consquences. One the one hand, the memory is unnessesary spent and on the other hand you are not able to keep the related objecs small (e.g. for caching).
So why don't we build a connection manager, which holds each table and we just refer to the TableClassName instead of the instance?
And just if we need the table (manipulating rows), we get the reference from the connection manager.

Is this a good idea or do I miss anything?

Best regards

[weierophinney]

-- dinok <[hidden email]> wrote
(on Wednesday, 14 November 2007, 01:27 PM -0800):
> I am currently working on some improvements for Zend_Db_Table.
> These improvements provide an easier and faster way for database
> relationships.
> As I've often seen, many people want to be able to use JOINs and other
> things.
>
> So my improvements are:
> - enable JOINs for one to one relationships

You may want to look at the work Simon Mundy is doing. He's adding
support for Zend_Db_Select in Zend_Db_Table, and part of this is the
ability to return result sets from JOINs.

> - enable preFetch for one to many relationships (one SELECT for all
> related, php assigns each record to the related table

If you do this, it should be optional. I know many situations where I
only use the related table data in a fraction of requests to the parent
table.

> - easy way to cache complete objects with relations

This would be nice.

> - some kind of a singleton implementation for TableClasses (static class
> like the registry? or get instance in each table class)

Perhaps a factory that uses Zend_Registry for storage? This would
promote re-use, and be pretty lightweight.

> The first two points are working in a early status, but now my problem:
> If I got a resultset with 10 rows and each of these rows(e.g. users)  owns
> zero, one or many related rows (e.g. email addresses), I always put
> references of the tables in the rowsets.
> This has two negative consquences. One the one hand, the memory is
> unnessesary spent and on the other hand you are not able to keep the related
> objecs small (e.g. for caching).
> So why don't we build a connection manager, which holds each table and we
> just refer to the TableClassName instead of the instance?
> And just if we need the table (manipulating rows), we get the reference from
> the connection manager.
>
> Is this a good idea or do I miss anything?

I think I'd need to see an implementation to better understand the
concerns.

--
Matthew Weier O'Phinney
PHP Developer            | [hidden email]
Zend - The PHP Company   | http://www.zend.com/

[Simon Mundy]

Some javascript/style in this post has been disabled (why?)

Hi Dino

This should probably be moved to the Db lists as well (I've CC'd this in).

I agree that there could be a more efficient way to provide relationships - the current gateway pattern is great for more straightforward applications, but I'm also hitting the wall in certains apps where I do need to maintain a series of complex relations for table rows.

I saw the proposal you'd written up previously and I agree that performance/memory tuning is needed for more serious usage. Caching would be less of an issue is an object is pre-fetched - you could simply cache the parent record and all subsequent records could be serialised within it.

I was less convinced by the application of using properties to describe table relationships (e.g. $row->Email) It would seem to me to clash too easily with column names - I had suggested off-list that perhaps an array access style of interface may be more desirable. E.g.

$userModel = new User();

$user = $userModel->find(1);

$type = $user['Role']->type; // one-to-one relationship

$user['Address']->createRow()->setFromArray('address_1' => '123 Street',

                                            'suburb'    => 'Roadsville',

                                            'state'     => 'NY',

                                            'postcode'  => 'ABC123');

print_r($user['Address'][0]->toArray()); // one-to-many relationship

$user->save();

The existing relationship maps within the Table class would be perfect for defining these kinds of relationships and - as you say - could allow for pre-fetching to reduce the number of hits to the database.

Here's my original email if it helps - perhaps we could combine these to establish a more formal proposal and get some community feedback?

Objectives

- Passing relationship definitions to rowsets/rows so that they can also be self-aware of table lookups

- Convenience methods for adding/deleting/looking up rows in a related rowset

- Auto-generation of foreign key values for new rows

- Establishing a clearly-defined method of accessing lookups

To start backwards from that list, my initial thought was to introduce ArrayAccess on Zend_Db_Table_Row to cleanly divide the way row properties and row lookups are performed. 

Lookups would use the 'Rule' name as defined in the parent table class. Let's use the current classes on the ZF manual as a starting point:-

<?php
class Accounts extends Zend_Db_Table_Abstract
{
    protected $_name            = 'accounts';
    protected $_dependentTables = array('Bugs');
}

class Products extends Zend_Db_Table_Abstract
{
    protected $_name            = 'products';
    protected $_dependentTables = array('BugsProducts');
}

class Bugs extends Zend_Db_Table_Abstract
{
    protected $_name            = 'bugs';

    protected $_dependentTables = array('BugsProducts');

    protected $_referenceMap    = array(
        'Reporter' => array(
            'columns'           => 'reported_by',
            'refTableClass'     => 'Accounts',
            'refColumns'        => 'account_name'
        ),
        'Engineer' => array(
            'columns'           => 'assigned_to',
            'refTableClass'     => 'Accounts',
            'refColumns'        => 'account_name'
        ),
        'Verifier' => array(
            'columns'           => array('verified_by'),
            'refTableClass'     => 'Accounts',
            'refColumns'        => array('account_name')
        )
    );
}

class BugsProducts extends Zend_Db_Table_Abstract
{
    protected $_name = 'bugs_products';

    protected $_referenceMap    = array(
        'Bug' => array(
            'columns'           => array('bug_id'),
            'refTableClass'     => 'Bugs',
            'refColumns'        => array('bug_id')
        ),
        'Product' => array(
            'columns'           => array('product_id'),
            'refTableClass'     => 'Products',
            'refColumns'        => array('product_id')
        )
    );

}

We'll use a 'Bug' as our base row:-

$bugsModel = new Bugs();

$bug = $bugsModel->find(1);

Retrieving data

We use the $_referenceMap rule names as our lookups. Couldn't be simpler!

$reporter = $bug['Reporter'];

$name = $bug['Engineer'][0]->name; 

Extending the definitions

We can take the $_referenceMap one step further and add relationtypes to each. Taking the 'Bugs' class we can define:-

        'Reporter' => array(

            'columns'           => 'reported_by',

            'refTableClass'     => 'Accounts',

            'refColumns'        => 'account_name',

            'refType'           => self::ONE_TO_ONE

        ),

        'Engineer' => array(

            'columns'           => 'assigned_to',

            'refTableClass'     => 'Accounts',

            'refColumns'        => 'account_name',

            'refType'           => self::ONE_TO_MANY

        ),

        'Verifier' => array(

            'columns'           => array('verified_by'),

            'refTableClass'     => 'Accounts',

            'refColumns'        => array('account_name'),

            'refType'           => self::ONE_TO_MANY

        )

This way the class can have strict behaviours set by the developer. It will affect the way in which lookups are called (e.g. a one-to-one would return a Zend_Db_Table_Row, whereas a one-to-many or many-to-many would return a Zend_Table_Rowset). It also affects the way in which the retrieval is called:-

$reporter = $bug['Reporter'] // Returns a single row OR null if not yet defined, similar to fetchRow();

$engineers = $bug['Engineer'] // Returns a Rowset

Saving data

So retrieving/accessing rows would be reasonably simple. The second step would be performing a cascaded 'save()' from the parent row, which in itself is not too difficult as you've already implemented a way of determining if a row is 'clean' or not. The extra step I would take would be to determine if a row has been 'related' to a parent, and if so then provide its parent row primary key(s) as default values for the corresponding internal columns. That would be reasonably easy using the inherited relationship definition from its table class.

NOTE: A save should be explicit unless a developer so wishes to override this (see configuration below).

Assigning relationships

We can not only retrieve data using the rule name but also set relationships. E.g.

$group = array(array('name' => 'Bob', 'role' => 'member'), array('name' => 'Ken', 'role' => 'admin'));

$bug['Engineer']->setFromArray($group);

or even using existing rowsets/rows

$accountsModel = new Accounts();

$account = $accountsModel->find('Darby');

$bug['Engineer'][0] = $account;

$bug->save();

$accountsModel = new Accounts();

$accounts = $accountsModel->find(array('Darby', 'Bill'));

$bug['Verifier'] = $accounts;

$bug->save();

These will, of course, need to match the appropriate rowset/row class constraints if appropriate (i.e. you can't assign a rowset of class 'Zend_Db_Table_Rowset' if the Bugs class defines the rowsetClass as 'BugsRowset').

Row class inheritance

There's a nifty feature in Doctrine that could also be easily adapted to Zend_Db_Table - row class inheritance depending on a field value. For example, say we had a column called 'role' in the 'Accounts' table. We can use the $_rowClass property to map to values for each row:-

$_rowClass = array('role' => array('admin' => 'AccountsAdminRow'), 'user' => 'AccountsUserRow', self::ROWCLASS_DEFAULT => 'AccountsGuestRow'));

Reference configuration

In the referenceMap there can be settings for altering the behaviour of relations via the 'refOptions' key. These can all be 'bits' and masked as required:-

* ZEND_DB_TABLE_ROW_AUTOSAVE - upon __destruct() cascade save()s through self and all children.

* ZEND_DB_TABLE_ROW_AUTOLOAD - loads all relation data on load(). The opposite of lazy-loading. Applies only to children.

* ZEND_DB_TABLE_ROW_AUTOCONSTRAIN - Use the table's info() to validate column values and to ensure that children are correctly linked (prevents 'NULL' foreign keys, string values for integers, etc).

* ZEND_DB_TABLE_ROW_DEFERLOAD - lazy load data for relationships. Applies only to children.

* ZEND_DB_TABLE_ROW_ALL / ZEND_DB_TABLE_ROW_NONE - self explanatory :)

I'd imagined that the children would lazy-load by default, however it may make more sense to load them all up at once from a single SQL query (saves more round trips). This would be user-configurable.

Hi all,

I am currently working on some improvements for Zend_Db_Table.
These improvements provide an easier and faster way for database
relationships.
As I've often seen, many people want to be able to use JOINs and other
things.

So my improvements are:
- enable JOINs for one to one relationships
- enable preFetch for one to many relationships (one SELECT for all related,
php assigns each record to the related table
- easy way to cache complete objects with relations
- some kind of a singleton implementation for TableClasses (static class
like the registry? or get instance in each table class)


The first two points are working in a early status, but now my problem:
If I got a resultset with 10 rows and each of these rows(e.g. users)  owns
zero, one or many related rows (e.g. email addresses), I always put
references of the tables in the rowsets.
This has two negative consquences. One the one hand, the memory is
unnessesary spent and on the other hand you are not able to keep the related
objecs small (e.g. for caching).
So why don't we build a connection manager, which holds each table and we
just refer to the TableClassName instead of the instance?
And just if we need the table (manipulating rows), we get the reference from
the connection manager.

Is this a good idea or do I miss anything?

Best regards
--
View this message in context: http://www.nabble.com/New-ideas-questions-for-Zend_Db_Table-tf4808143s16154.html#a13756929
Sent from the Zend Framework mailing list archive at Nabble.com.

--

Simon Mundy | Director | PEPTOLAB

""" " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "

Please support Movember today!  Visit http://www.movember.com/au/donate 

Registration number for Simon 160725

202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000

Voice [phone] | Mobile [phone] | Fax [phone]

http://www.peptolab.com

[Gunter Sammet]

I posted this question earlier that might should be put into
consideration as well:

http://www.nabble.com/Zend_Db_Table-and-sorting-by-lookup-key-t4807160s16154.html

Currently I am having some issues sorting my tables without doing
joins if the sorting is base on a lookup ID. If you cache the the
objects without doing a full join, you can't sort (unless I am missing
something). E.g. consider the following SQL:

SELECT c.* FROM customers c, cities ci WHERE c.city_id = ci.city_id
ORDER BY ci.name DESC;

I haven't found a way on how to sort that without doing the join. For
one of my listing view generators I am creating a helper class that
selects all cities once and stores them in an associative array. The
listing then just pulls the city name out of the array to display in
the listing. All cities are selected only once with a simple SQL:
SELECT city_id, city_name FROM cities and then reused for the whole
listing.
But now I would like to implement sortable headers and for that I
can't sort on the ID. I can't think of anything else than doing a join
to be able to sort. Or?
Any thoughts?

Gunter

[dinok]

Matthew Weier O'Phinney-3 wrote:

You may want to look at the work Simon Mundy is doing. He's adding
support for Zend_Db_Select in Zend_Db_Table, and part of this is the
ability to return result sets from JOINs.

This is currently not possible :(
See:
http://www.nabble.com/Re%3A-revised-Zend-Db-Table-and-JOINs-t4737221s16154.html

Matthew Weier O'Phinney-3 wrote:

If you do this, it should be optional. I know many situations where I
only use the related table data in a fraction of requests to the parent
table.

Yes, of course. This is how its also implemented.
I've added the parameter 'fetchLazy' to findDependetRowsets/findParentRow methods.
Default it's set to true. This means, if a related record is not found in the $_data array, the old zf methods with find... are used. If it's set to false, the function returns (as the zf function too) null because no relations were found.

Matthew Weier O'Phinney-3 wrote:

Perhaps a factory that uses Zend_Registry for storage? This would
promote re-use, and be pretty lightweight.

Yes, exactly. This should be the fastest way and we can use just the classNames instead of the hole class reference.

Example:
Zend_Db_Table_Manger::getTable('className'): returns a classNameTable.

Zend_Db_Table_Row/Rowset->__construct(): gets just the name instead of the class

Zend_Db_Table_Row->save(): Zend_Db_Table_Manger::getTable($this->_tableName)->update/insert/delete

A possible implementation:
http://nopaste.php-quake.net/9491

[weierophinney]

Responding to only some of the items here:

-- dinok <[hidden email]> wrote
(on Wednesday, 14 November 2007, 02:22 PM -0800):

> Matthew Weier O'Phinney-3 wrote:
> > If you do this, it should be optional. I know many situations where I
> > only use the related table data in a fraction of requests to the parent
> > table.
> >
> Yes, of course. This is how its also implemented.
> I've added the parameter 'fetchLazy' to findDependetRowsets/findParentRow
> methods.
> Default it's set to true. This means, if a related record is not found in
> the $_data array, the old zf methods with find... are used. If it's set to
> false, the function returns (as the zf function too) null because no
> relations were found.

Good -- for BC and performance reasons, it's best to keep the default to
load only on demand.

> Matthew Weier O'Phinney-3 wrote:
> > Perhaps a factory that uses Zend_Registry for storage? This would
> > promote re-use, and be pretty lightweight.
> >
> Yes, exactly. This should be the fastest way and we can use just the
> classNames instead of the hole class reference.
>
> Example:
> Zend_Db_Table_Manger::getTable('className'): returns a classNameTable.
>
> Zend_Db_Table_Row/Rowset->__construct(): gets just the name instead of the
> class
>
> Zend_Db_Table_Row->save():
> Zend_Db_Table_Manger::getTable($this->_tableName)->update/insert/delete
>
> A possible implementation:
> http://nopaste.php-quake.net/9491 http://nopaste.php-quake.net/9491 

I'll take a look at that tomorrow.

--
Matthew Weier O'Phinney
PHP Developer            | [hidden email]
Zend - The PHP Company   | http://www.zend.com/