Page 1 of 1

Table data gateway

Posted: Fri Oct 29, 2010 11:34 am
by alex.barylski
EDIT | This is really just a brain dump of what is on my mind right now. if it's toally incoherent I apologize in advance :)

A while back I implemented a simple, generic (no frills) table data gateway. Basic CRUD-S methods thats it's

My implementation will act only on a single table at a time, no JOIN's are possible, this was by choice. While this works great for in 95% of situations, there are some occassions where I wish I had something more like a data mapper, especially when working with the crud methods, a load()/save() usage would be slightly more readable I think.

I am thinking, I could probably implement something of a row data gateway using the TDG, by encapsulating the CRUD methods and providing a load()/save() interface, in addition to possibly providing mutable properties as part of the RDG - something the TDG does not do provide it's strictly a function based interface.

At which point does a RDG implementation become a data mapper, because of most examples I have seen a data mapper is essentially that, with possibly finder() methods added to the mix, which the underlying TDG would facilitate much more effectively (at least in my vision of what I want achieved). ActiveRecord has been said many times, to simply be a RDG with business logic. So if you inherited from a RDG and implemented business logic you effectively have achieved ActiveRecord.

One draw back to using a simple TDG class as I have built, is the responsibility of programmatically tying togather tables as opposed to using JOIN's. For instance I have a workorder table, with a few ID's - customer, user, etc. Before I return that result to the caller, I must iterate the recordset, and map the details from the customer, user, etc to the array. Not a great deal of work, but a full fledged ORM would assumingly handle this for me.

Eventually I would like to implement a lightweight ORM, if for no other reason than to accomodate this feature. :)

I am not sure how I envision everything to unfold so I'm just asking for opinions, for the time being lets say:

1. I see the TDG being the lowest level and providing a base for all other operations
2. I see RDG being used to provide/dictate the schema of an individual record
3. Data mapper using both TDG and RDG to meet the requirements of users who prefer data mapper
4. Some kind of ORM that handles the mismtach between object and table(s)

The latter I can see possibly being implemented by using recordset meta information stored in RDG and conventions. When a table is queried, the columns are checked and foriegn key relations are determined based on convention, for instance id_customer would cause a 1:1 mapping of workorder to customer, so querying the workorder model would automatically include the customer details as well.

Obviously 1:1 and 1:n and n:m would need to be made more obvious in the meta data, and I am not very confident this information belongs in a RDG

Anyways, I have lost my chain of thought for the time being, interested in hearing others opinions on their experience implementing any of the patterns mentioned above.

Cheers,
Alex

Re: Table data gateway

Posted: Fri Oct 29, 2010 3:35 pm
by josh
The differentiation between data mapper and the other ORM solutions is data mapper is n:n as far as database tables to classes. The best way to do it is write the queries by hand, and only create abstractions out of real working code (as opposed to trying to ask all these upfront design questions you're asking yourself, like metadata syntax for abstractions you aren't even implementing yet)

You're going inside->out. Trying to think of every method/meta-data you might need to build a query later on.

Outside-> in would be if you just put big ugly methods with big ugly SQL in there. Later on you find abstractions among the methods. This works best with TDD

Even when using my own datashuffler framework, sometimes I don't even use the framework. I just extend & completely end up overriding the base functionality 100%. The framework is just there to get stuff up & running fast, when the queries are still 'select * from table'. When the queries get ugly, its often useful to just write the whole mapping by hand. Its more clear, maintainable, performs better, less dev time, easier to test etc.. The ability to add these complex mappings 'by hand' is what makes it a data mapper. When persistence is on the same object, these mappings are a lot harder to implement.

So maybe dates get persisted one way, and its really complex. And 10 other mappers need to persist dates the same way. You'd create a 'date' mapping and then share among mappers. It can use 1 query or 500, it doesn't really matter. Hopefully now you understand what I'm talking about.

/ my 2 cents

Re: Table data gateway

Posted: Fri Oct 29, 2010 4:47 pm
by alex.barylski
The differentiation between data mapper and the other ORM solutions is data mapper is n:n as far as database tables to classes
My impression was that ORM would typically map 1:1 or 1:m - that is to say - one class may map to one ro more tables. I'm not sure I follow what you mean by n:n. Do you mean, one or more classes map to one table in addition to everything else?
Even when using my own datashuffler framework, sometimes I don't even use the framework. I just extend & completely end up overriding the base functionality 100%. The framework is just there to get stuff up & running fast
I tend to do the same (I think), using abstractions to get a rough prototype up and later re-implementing them as a DAL which provides a low-level API using - often blurring some business logic in the SQL but readability and performance are improved, testability is not (unless I used somehting like LINQ for PHP), it's a sacrifice I am willing to make.
So maybe dates get persisted one way, and its really complex. And 10 other mappers need to persist dates the same way. You'd create a 'date' mapping and then share among mappers. It can use 1 query or 500, it doesn't really matter. Hopefully now you understand what I'm talking about.
Hehe...to be honest I'm confused but thats my fault. If you can find the time, perhaps example code usage of your own shuffler would help clear up any confusion I might have.

Cheers,
Alex

Re: Table data gateway

Posted: Fri Oct 29, 2010 8:15 pm
by josh
PCSpectra wrote:My impression was that ORM would typically map 1:1 or 1:m - that is to say - one class may map to one ro more tables. I'm not sure I follow what you mean by n:n.
3 tables - 1 class
For example you could store years, months, & days in 3 different tables respectively; with one class to handle dates (although probably a stupid example on my part)

1 table - 3 classes
Or, you could store the dates as a serialized string in 1 field/table; with separate day, month, & year classes that get separately created & then 'wired' together by injecting into a date object

n - n
Or you could do any crazy combination you wanted. You can even have more than one mapper that can map for a single class, as opposed to a single mapper handling multiple classes or single mapper with multiple tables. SO really maybe its n:n:n not n:n.

Performance
You don't always need all data on all use cases. You can have a mapper highly optimized for each use case, aggressively (as opposed to lazily) loading all data that will be needed on a particular screen, nothing more
Hehe...to be honest I'm confused but thats my fault. If you can find the time, perhaps example code usage of your own shuffler would help clear up any confusion I might have.
Code examples are up here: http://datashuffler.org/ - but I have provided more realistic examples from my bag of 'real world stuff' below.

For example, instead of using JOINs to build up a user, it's registration date & last login date - you could instead treat 'date' as a 'field' of sorts. For example maybe the process for mapping a date is to get a string format 'MM-DD-YYYY', split it on the comma delimiter, instantiate 3 classes, and wire them together. Essentially instead of having 'date mapper' and your 'user mapper' joining to the dates, the dates are treated as 'field' or mapping. This makes the 'concrete' data mapper code more succinct & decreases the amount of mental work that has to be done to understand the forest from the trees in my opinion.

For example http://datashuffler.org/ I have 'embedded', 'foreign key single', 'foreign key multiple/assocation', & 'plugin' mappings. Code examples are shown along with descriptions of their mapping strategy. Basically these mappings implement the strategy pattern. I had one application where things had collections of grades. Employers had grades taught. Teachers had grades taught, etc.. Both also had 'subjects taught'

Here is a 'subject mapping' you'll see it uses PHP code to find related objects, instead of SQL, which is possible. (although not required. You could override the select() method or create some coupling between mappings & mappers to cause the data you need to be added to the result set, the result set is then optionally passed to the mapping. This way you can 'mix in' your mappings & still retain 1 query (which only sometimes the fastest way performance wise)

Code: Select all

<?php
/**
* Mapping class for adding subject mapping to an entity
* 
* to add this mapping on a mapper ex:
* 
* ->addMapping( new Subject_Mapping( $this, array(
*     'field' => 'educator_id',
*     'associationTable' => 'educator_subject'
* )))
* 
* @package Subject
* @subpackage Subject_Mapper
*/
class Subject_Mapping extends Shuffler_Mapper_Mapping_Association
{
    protected $class = 'Subject';
    protected $associationTable; 
    
    public function getType()
    {
        return 'subject';
    }
    
    public function getProperty()
    {
        return 'Subjects';      
    }
    
    /**
    * Perform the select on the association table, load the rows ( subjects ) from that table and
    * populate the model with a loaded subject collection
    * 
    * @param Subject_Interface
    */
    protected function doLoad( Subject_Interface $model )
    {
        $db = $this->getReadAdapter();
        
        $select = $db->select()
            ->from( array( 'assoc' => $this->getAssocTableName() ) )
            ->where( 'assoc.' . $db->quoteIdentifier( $this->getAssocFk() ) . ' = ?', $model->getId() );
        $rs = $this->getReadAdapter()->query( $select )->fetchAll();
        
        $subjects = array();
        foreach( $rs as $row )
        {
            if( $row['subject_level3_id'] )
            {
                $subject = $this->getMapper( 'Subject_Level3' )->find( $row['subject_level3_id'] );
                array_push( $subjects, $subject );
            }
            else if ( $row['subject_level2_id'] )
            {
                $subject = $this->getMapper( 'Subject_Level2' )->find( $row['subject_level2_id'] );
                array_push( $subjects, $subject );
            }
            else if ( $row['subject_level1_id'] )
            {
                $subject = $this->getMapper( 'Subject_Level1' )->find( $row['subject_level1_id'] );
                array_push( $subjects, $subject );
            }
            
        }
        
        foreach( $subjects as $subject )
        {
            $model->addSubject( $subject );
        }           
        return $model; 
    }
    
    /**
    * Perform the save function for the model updating the association table for this entity
    * with the assigned values from the model
    */
    public function save( Shuffler_Model $model )
    {                  
        $db = $this->getWriteAdapter(); 
        // delete the association rows
        $db->delete( $this->getAssocTableName(), $this->getAssocFk() . ' = ' . (int)$model->getId() ); 

        // and re-insert
        $collection = $model->getSubjects();
        foreach( $collection as $subject )
        {    
            $values = array();   
            foreach( $subject->getPath() as $node )
            {
                $values[ 'subject_level' . $node->getLevel() . '_id' ] = $node->getId();
            }
            $values[ $this->getAssocFk() ] = $model->getId();
            $db->insert( $this->getAssocTableName(), $values );        
        }
    }
}
It is used like this: (control F and highlight all 'addMapping')

Code: Select all

class Educator_Mapper extends Search_Searchable_Mapper
{
    
    /** Set up the field mappings */
    function init()
    {
        $this->addPrimaryKey( 'id' )
        
        ->addSingle( 'user',  'User', array(
            'immutable' => true
        ))
        ->addSingle( 'address',  'Address', array(
            'field' => 'address_id',
            'property' => 'Address'
        ))
        ->addSingle( 'perm_address',  'Address', array(
            'field' => 'permanent_address',
            'property' => 'PermanentAddress'
        ))
        ->addAssociation( 'terms', 'Term', array(
            'property' => 'DesiredJobTerm'
        ))
        ->addMapping( new Subject_Mapping( $this, array(
            'field' => 'educator_id',
            'associationTable' => 'educator_subject'
        )))
        ->addMapping( new Application_Mapping( $this ) )
        ->addAssociation( 'grades', 'Grade' )
        ->addMapping( new Employer_Type_Mapping( $this, array(
            'field' => 'educator_id',
            'associationTable' => 'educator_desired_employer_type'
        )))
        ->addMapping( new Geography_Mapping( $this, array(
            'field' => 'educator_id',
            'associationTable' => 'educator_desired_job_location'
        )))
        ->addEmbedded( 'cover_letter' )
        ->addEmbedded( 'career_objective' )
        ->addEmbedded( 'desired_position' )
        ->addEmbedded( 'last_position' )
        ->addEmbedded( 'years_experience' )
        ->addAssociation( 'qualifications',  'Qualification' )
        ->addSingle( 'education',  'Education', array(
            'property' => 'HighestEducation',
            'field' => 'highest_education_id'
        ))
        ->addAssociation( 'willing_tos',  'Job_Willing', array(
            'associationTable' => 'educator_job_willing',
            'accessorMethod' => 'getWillingTo',
            'mutatorMethod' => 'addWillingTo'
        ))
        ->addAssociation( 'desired_neighborhoods',  'Neighborhood', array(
            'associationTable' => 'educator_desiredneighborhood',
            'property' => 'desiredNeighborhood'
        ))
        ->addAssociation( 'documentation',  'Documentation_Level2', array(
            'associationTable' => 'educator_documentation',
            'accessorMethod' => 'getDocumentation',
            'mutatorMethod' => 'addDocumentation'
        ))
        ->addAssociation( 'saved_job',  'Job', array(
            'associationTable' => 'educator_savedjobs',
            'accessorMethod' => 'getSavedJobs',
            'mutatorMethod' => 'addSavedJob'
        ))
        ->addCollection( 'certification', 'Certification' )
        ->addCollection( 'application', 'Application' )
        ->addCollection( 'video', 'Video' )
        ->addCollection( 'document', 'Document' )
        ->addEmbedded( 'nbct' )
        ->addEmbedded( 'housse' )
        ->addEmbedded( 'hq' );
    }