« Return to Thread: New ideas/questions for Zend_Db_Table

Re: New ideas/questions for Zend_Db_Table

by Simon Mundy :: Rate this Message:

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 +61 (0) 3 9654 4324 | Mobile 0438 046 061 | Fax +61 (0) 3 9654 4124

http://www.peptolab.com

« Return to Thread: New ideas/questions for Zend_Db_Table