SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    lightweight no-fuss PDO table- & row-gateway

    greetings!

    after a long absence from *the* best advanced php-forum,
    i am back to get some comments on my lightweight, no fuss PDO mysql-focused table-gateway and table-row-gateway.
    Roughly inspired by Zend_Db_Table but leaving all abstraction-layers and automatic joining.

    Currently i am casually working on a db-application we have to create for university, so i am playing around with the great ZendFramework, jQuery and ExtJS to create a MediaDB.
    As its a database-centric application, I have to work with many tables and complex queries which are handcrafted as i went insane on most abstraction-layers
    To put it short, my old lightweight ActiveRecord-implementation being a pain in the a**, I prototyped a table- and row-gateway to abstract the most ugly parts of sql-internals.

    as I know everyone visiting this forum is able to see thru some plain code and myself being a lazy *******, a link to .phpS-files follows:
    PdoTable
    usage-hint

    this was prototyped this evening and will contain some bugs but as always: any comments appreciated!
    i hope there is still a need for some lightweight libraries in times of new omni-frameworks born every day

    so long,
    kai

  2. #2
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    28
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice work! Code looks pretty good after a rough sighting. +1 kudos

    When you say lightweight, what does that mean in terms of memory usage?
    I'm asking that, because i've made the experience that all ORM Libraries have an high memory consumption.

    And... well it's seems that you finish database related stuff always in december? Your Last Post with Active Record is from 12.2005.
    Refering to the ActiveRecord Implementation, did you do any improvements or changes to it?
    http://www.sitepoint.com/forums/showthread.php?t=328486
    http://www.shize.de/php/ActiveRecord.phps
    http://www.shize.de/php/ActiveRecord-ex.phps

    regards

  3. #3
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hello mr. vain, thanks for the review.
    indeed it seems i have december-deadlines

    i have reworked the ActiveRecord a few times but those were only minor changes, biggest one using the Zend_Db_Adapter which was no good idea as it is a fine piece of architecture but too heavy for my needs
    (i wont never ever switch the db-layer in a production mysql/db2 system).

    pros of the current implementation:
    - some differentiation between the table/model itself and its rows bound to a specific PrimaryKey (see Zend_Db_Table)
    this facilitates to just query/find or save/delete specific rows and not mix those two different concepts (one being the store, the other the entry)
    - a lot more features to easily "find" using different constraint-styles or even "select"/query directly using handcrafted sql to get all sql-benefits
    - as i am playing with the übergreat ExtJS-grid and much paging there was the need to get count(*) and total-count(*) easily, which is directly accessible thru the result-object
    - memory consumption should be equal to the ActiveRecord, by default the result creates a Row-Object one at a time but is also able to create an result-array of Row-Objects or an plain result-array (Row-Objects recursively converted to arrays)
    - regarding ORM: this is by no means an Object-Relational-Mapper as it is not able to model or map object-relations to tables. as this is a database-centric application for university, its ERM is the evolving but not changing architectural blueprint plus i have to show my sql-skills to put it short, i didnt see a reason to model the relations in a cumbersome way if i have to use multiple ways to get the relations i really need. with the current implementation I am able to query the relations programmatically faster than modelling those into the table-class.
    - additional plus: the current impl is able to auto-fetch the table's metadata
    - minus (dont know if it really is a minus): the column->type binding for the pdo-parameter-binding is currently not implemented. i dont even know if i ever will as i think these are validation-issues not belonging into this class.

    regards,
    kai

  4. #4
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *updated*

  5. #5
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *updated*

    most bugs should have been fixed by now...

    It's a Zend-Framework Db-Table inspired Table- and Row-Component (without auto-joining, it's no ORM)

    http://www.shize.de/php/PdoTable.phps

    Any comments appreciated,
    Kai


    Small Usage Example:
    PHP Code:
    <?php
    class Media extends PdoTable {
        
        public function 
    getSelect() {
            return 
    '
            SELECT 
                media.*,
                
                mediatype.name AS mediatype_name,
                
                locale_lang.lang,
                locale_lang.code AS lang_code,
                
                locale_country.country,
                locale_country.code AS country_code,
                
                location.name AS location_name,
                location.comment AS location_comment_general,
                
                ( SELECT person.name
                    FROM person
                    JOIN media_has_person
                        ON media_has_person.person_id = person.person_id
                    WHERE media_has_person.media_id = media.media_id
                    ORDER BY media_has_person.seq ASC, person.name ASC
                    LIMIT 1    ) AS person_name,
                
                ( SELECT genre.name
                    FROM genre
                    JOIN media_has_genre
                        ON media_has_genre.genre_id = genre.genre_id
                    WHERE media_has_genre.media_id = media.media_id
                    ORDER BY media_has_genre.seq ASC, genre.name ASC
                    LIMIT 1    ) AS genre_name
            FROM media
            
            LEFT JOIN mediatype
                ON media.mediatype_id = mediatype.mediatype_id
            
            LEFT JOIN locale AS locale_lang
                ON media.lang_id = locale_lang.locale_id
            
            LEFT JOIN locale AS locale_country
                ON media.country_id = locale_country.locale_id
            
            LEFT JOIN location
                ON media.location_id = location.location_id
            '
    ;
        }
    }

    // find with standard constraint
    $media = new Media($db);
    $res $media->find('is_collection = "true"');

    foreach (
    $res as $row) {
        echo 
    $res['title'];
        
        
    $res['foo'] = 'bar';
        
    $res->saveRow();
    }


    // complex example
    $constr = array(
        
    'foo' => 'bar',            foo bar
        
    'foo != ?' => 'bar'        foo != bar
        
    'foo IS NULL'            foo IS NULL
    );
    $order 'title ASC';
    $limit 10;
    $offset 20;

    $res2 $media->find($constr$order$limit$offset)
    // without limit, the query would have returned $res2->total(); rows


    //   or  without   a  special   class-definition
    $fooTbl =  new   PdoTable($db'foobar_table''foobar_id');
    $fooTbl->find( ... );
    ?>

  6. #6
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A few comments:

    In PdoTableRow->saveRow(), you create a transaction. I'd prefer leaving this to a higher level of the application, since some (Eg. MySql) RDBMS'es don't allow nested transactions.

    Why do you declare certain methods final, when there is no real need for it? This is unnecessarily restrictive.

    Perhaps more in the stylistic corner here, but your use of references makes me a bit uneasy. There is really no need to use them -- Just return values from your functions instead.

    If your aim is to have a simple library, I don't think you should provide the class PdoTableRow. PHP's associative arrays are powerful enough for simpler needs. Perhaps the PdoTableRow class could stay, but I think it would be easier on the user, if it was an advanced option, rather than the default.

  7. #7
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *updated*

    Quote Originally Posted by kyberfabrikken View Post
    In PdoTableRow->saveRow(), you create a transaction. I'd prefer leaving this to a higher level of the application, since some (Eg. MySql) RDBMS'es don't allow nested transactions.
    you're absolutely right, changed.

    Quote Originally Posted by kyberfabrikken View Post
    Why do you declare certain methods final, when there is no real need for it? This is unnecessarily restrictive.
    the library works be extending the classes, at least the table-class and often implementing custom row-classes as well. as such, I provide pre* and post* methods to hook into the important execution flow but it doesn't make sense to overwrite core-methods.
    e.g.: in a project we're using this class, a developer overwrote unknowingly core-methods which was quite nasty to debug...

    Quote Originally Posted by kyberfabrikken View Post
    Perhaps more in the stylistic corner here, but your use of references makes me a bit uneasy. There is really no need to use them -- Just return values from your functions instead.
    if you mean the helper-method 'arrify', you're right, that could be done returning ...

    Quote Originally Posted by kyberfabrikken View Post
    If your aim is to have a simple library, I don't think you should provide the class PdoTableRow. PHP's associative arrays are powerful enough for simpler needs. Perhaps the PdoTableRow class could stay, but I think it would be easier on the user, if it was an advanced option, rather than the default.
    currently i have implemented custom row-classes for many entities like media, person, genre, etc. which contain custom methods. as i need these methods, the whole PdoTable class works with row-classes by default.
    i found this approach offers maximum flexibility while not being slower or much more memory intensive.
    furthermore, i just like the row-classes flexibility to save or delete a row, i think that's the charming point of a table-&row-gateway. but as you are right, arrays are great to handle data, the row-class implement array-access to work with the rows as if it were a plain array ...

    thanks for your comments,
    kai

  8. #8
    SitePoint Member
    Join Date
    Feb 2008
    Posts
    5
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can always find more help on the website of Nouveau Riche University. Of course, there you can find some data but you need to have good knowledge of PHP as to modify the data to suit your needs.

  9. #9
    SitePoint Zealot
    Join Date
    Jun 2003
    Location
    hamburg, germany
    Posts
    103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    W T F ?

  10. #10
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,807
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    *scratches head* possibly in the wrong place!

    Great work by the way Kai
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •