New ideas/questions for Zend_Db

Thread: New ideas/questions for Zend_Db_Table

Search this list only Search all lists
New ideas/questions for Zend_Db_Table
	2007-11-14 15:27:40
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#a 13756929 Sent from the Zend Framework mailing list archive at Nabble.com.

Re: New ideas/questions for Zend_Db_Table
	2007-11-14 15:52:34
-- dinok <dino.knollgmx.de> 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 \| matthewzend.com Zend - The PHP Company \| http://www.zend.com/

Re: New ideas/questions for Zend_Db_Table
	2007-11-14 15:57:47

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/div> <?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/div> $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/div>   '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/div> $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/div> $_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/div> * 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
Re: New ideas/questions for Zend_Db_Table
	2007-11-14 16:12:08
I posted this question earlier that might should be put into consideration as well: http://www.nabble.com/Zend_Db_T able-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

Re: New ideas/questions for Zend_Db_Table
	2007-11-14 16:22:05
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-revis ed-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)->upd ate/insert/delete A possible implementation: http://nopaste.php- quake.net/9491 http://nopaste.php- quake.net/9491 -- View this message in context: http://www.nabble.com/ New-ideas-questions-for-Zend_Db_Table-tf4808143s16154.html#a 13757706 Sent from the Zend Framework mailing list archive at Nabble.com.

Re: New ideas/questions for Zend_Db_Table
	2007-11-14 16:52:55
Responding to only some of the items here: -- dinok <dino.knollgmx.de> 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)->upd ate/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 \| matthewzend.com Zend - The PHP Company \| http://www.zend.com/

[1-6]

about | contact Other archives ( Real Estate discussion Medical topics )