SitePoint Sponsor

User Tag List

Page 4 of 5 FirstFirst 12345 LastLast
Results 76 to 100 of 118

Thread: ActiveRecords

  1. #76
    SitePoint Addict Jasper Bekkers's Avatar
    Join Date
    May 2007
    Location
    The Netherlands
    Posts
    282
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    Maybe someone knows of a relational database however that does not require a primary key?
    Every SQL:2003 compliant database allows the absence of a primary key.
    Design patterns: trying to do Smalltalk in Java.
    I blog too, you know.

  2. #77
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pakmannen View Post
    Ah, so you're not using eager loading then?
    It's actually a work in progress, I haven't worked out the kinks, but the way it works is that eager loaded objects are stored with the other property values; the association array is only accessed when there's no property value.

    Quote Originally Posted by pakmannen View Post
    Another thing I thought about. My constructor for an ActiveRecord currently does something like this:
    PHP Code:
    __construct($columns)
    {
        foreach (
    $columns as $column => $value)
        {
            
    $this->_columns[$column] = $value;
        }

    That is, takes an entire array of properties/columns and adds them to the class member $_columns.
    Wouldn't it be simpler to do this?

    PHP Code:
    __construct($columns)
    {

        
    $this->_columns $columns;



    Quote Originally Posted by pakmannen View Post

    What I was thinking was, maybe it should be enough to supply the id of a record to the constructor. That way you could populate a User object by specifying the User id. For instance:
    PHP Code:
    $user = new User($_SESSION['uid']); 
    In that case, I suppose the Finders should only ever select the id in a table. Of course, that becomes a problem when you've got tables without primary keys though. I dunno.. thoughts? Reason is I saw one implementation which did it like this, so I started to wonder.. :P Maybe I should just get on with it instead of checking how everybody else does things.. Argh

    Alternative is of course to do the above with a finder.
    PHP Code:
    $user User::FindById($_SESSION['uid']); 
    I would use the finder approach, if not you're basically duplicating your finder code in both places. As for lack of primary keys, that's not something that you should allow to happen, and there's no advantage to the constructor approach over the finder one in that case anyway.

  3. #78
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > Every SQL:2003 compliant database allows the absence of a primary key.

    Umm...

    Well, if that is the case then my assumption is wrong, but my reasoning isn't though? To me, it doesn't make sense for a database table to be without a primary key; MySql throws an error when you create a table and don't define a primary key (try it) so maybe MySql isn't compliant.

    Maybe it's broken?!

    > and there's no advantage to the constructor approach over the finder one in that case
    > anyway.

    I could argue that it helps to clean up your client script, but I suppose that is academic in regards to eager loading 33?

  4. #79
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    > Every SQL:2003 compliant database allows the absence of a primary key.

    Umm...

    Well, if that is the case then my assumption is wrong, but my reasoning isn't though? To me, it doesn't make sense for a database table to be without a primary key; MySql throws an error when you create a table and don't define a primary key (try it) so maybe MySql isn't compliant.
    MySQL only throws an error if you try to make an autonumber field that isn't part of a primary key. Otherwise it works fine, try it:

    Code:
    CREATE TABLE `foo` (
    `bar` VARCHAR( 255 ) NOT NULL
    )
    Quote Originally Posted by Dr Livingston View Post
    >
    > and there's no advantage to the constructor approach over the finder one in that case
    > anyway.

    I could argue that it helps to clean up your client script, but I suppose that is academic in regards to eager loading 33?
    I was referring specifically to the missing primary key issue; the issue he describes would be present regardless of the approach used. As for the client script, I generally use my constructors for passing in dependencies, otherwise you'd do what, use a singleton inside the constructor to get a connection to the database?

  5. #80
    SitePoint Addict Jasper Bekkers's Avatar
    Join Date
    May 2007
    Location
    The Netherlands
    Posts
    282
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    MySql throws an error when you create a table and don't define a primary key (try it) so maybe MySql isn't compliant.

    Maybe it's broken?!
    Code:
    mysql> create table bar (foobar int);
    Query OK, 0 rows affected (0.08 sec)
    
    mysql> show warnings;
    Empty set (0.00 sec)
    
    mysql> select version();
    +---------------------+
    | version()           |
    +---------------------+
    | 5.0.45-community-nt |
    +---------------------+
    1 row in set (0.00 sec)
    
    mysql> show index from bar;
    Empty set (0.00 sec)
    But your right, it doesn't really make sense to not have a primary key.
    Design patterns: trying to do Smalltalk in Java.
    I blog too, you know.

  6. #81
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for clearing that up, but it still doesn't make any sense (to me anyways).

    > use a singleton inside the constructor to get a connection to the database?

    Nope. You would be better of therefore just to create a new instance from within the constructor in that case, however you could create the instance via the Singleton on a class method of that class,

    PHP Code:
    public function getConnection() {
    if( 
    is_null$this -> conn ) ) {
    $this -> conn QRegistry::get'connection' );
    }
    return 
    $this -> conn;

    That way, you can override it in the concrete implementation if required. Whilst not the perfect solution, it does give you a few options

    PHP Code:
    class ... extends Active_Record {
    // ...
    public function setConnectionQConnection_Interface $conn ) {
    $this -> conn $conn;
    }
    // ... etc ... 
    > the issue he describes would be present regardless of the approach used.

    I see... Well, that would be the case then

  7. #82
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    I don't know of any relational databases that allow you to have a table without a defined primary key first?

    Maybe someone knows of a relational database however that does not require a primary key? I can't understand why though, since pretty much every relation hinges on a primary key.
    The two I work with every day: Oracle and MySQL

    MySQL:
    Code:
    CREATE TABLE `testtab` (
    `no` VARCHAR( 1 ) NULL ,
    `primary` VARCHAR( 2 ) NULL ,
    `key` VARCHAR( 3 ) NULL
    ) ENGINE = MYISAM ;
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  8. #83
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That has been pointed out of course, but I still don't understand why those who developed those databases, or those who thought up the standards would allow for something like this to creep in.

    Like I said earlier, I can't really make any sense of it, and until it was pointed out I hadn't thought that something like this would have been possible.

    It's unfortunate

  9. #84
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr Livingston View Post
    That has been pointed out of course, but I still don't understand why those who developed those databases, or those who thought up the standards would allow for something like this to creep in.

    Like I said earlier, I can't really make any sense of it, and until it was pointed out I hadn't thought that something like this would have been possible.

    It's unfortunate

    Consider the simple case of a table tracking hits to a website:

    Code:
    CREATE TABLE `hit_log` (
    `timestamp` DATETIME NOT NULL ,
    `url` VARCHAR( 255 ) NOT NULL
    ) ENGINE = MYISAM ;
    Is the primary key the timestamp? What if two users hit at the same time?

    Is the primary key the timestamp and the URL? Again, what if two users hit at the same time on the same page?

    Do you complicate the table structure by adding a count, and then every time you log a hit you have to first do a select to see if a similar hit has occoured, and if so bump the count, otherwise insert a record with a count of 1.

    Or...have a table without a primary key and just dump the data in there. Sometimes reality does not like our clean notions of data modeling and normalization
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  10. #85
    SitePoint Addict Jasper Bekkers's Avatar
    Join Date
    May 2007
    Location
    The Netherlands
    Posts
    282
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje View Post
    Do you complicate the table structure by adding a count, and then every time you log a hit you have to first do a select to see if a similar hit has occoured, and if so bump the count, otherwise insert a record with a count of 1.
    What's wrong with doing something like (apart from needing some sort of key...)
    Code:
    INSERT INTO 
        `hit_log`(
            `timestamp`, 
            `url`, 
            `count`
        )
        VALUES(
            NOW(),
            "sitepoint.com", 
            1
        )
        ON DUPLICATE KEY 
        UPDATE 
            `count` = `count` + 1;
    Sometimes reality does not like our clean notions of data modeling and normalization
    Because of exactly that, surrogate keys exist.
    Design patterns: trying to do Smalltalk in Java.
    I blog too, you know.

  11. #86
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    > Sometimes reality does not like our clean notions of data modeling and normalization

    Maybe... But I have the notion of doing things right, or to put it another way, by the book so for me I would work out another solution to this problem, that would involve the use of primary key(s).

    To me, what you've proposed is nothing more than a hack, well that's the impression I'm left with anyways

  12. #87
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it's simply a question of what the data is going to be used for. Primary keys are necessary if you're doing CRUD, but if you're just dumping data to do reports on afterwards, they're actually detrimental, as your db server has to generate ids and keep things indexed.

  13. #88
    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)
    Quote Originally Posted by 33degrees View Post
    ... they're actually detrimental, as your db server has to generate ids and keep things indexed.
    If I'm not mistaken, most RDBMS'es creates an internal index column for tables, which doesn't have a single, integer-based PK column. Whether you want to declare it explicitly, makes no difference for performance.

  14. #89
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 33degrees View Post
    Wouldn't it be simpler to do this?
    Haha, yeah, that was pretty dumb. I probably copied that piece from the __set method and forgot to use my brain. Thanks for pointing it out.

    I would use the finder approach, if not you're basically duplicating your finder code in both places. As for lack of primary keys, that's not something that you should allow to happen, and there's no advantage to the constructor approach over the finder one in that case anyway.
    Yeah, I don't know what I was thinking about really.. Forget I ever posted that

    Now, back to associations. I'm a bit curious if you've done any eager loading (joins) with has_many relationships? How did you go about doing that? I think I've got the has_one and belongs_to working, sort of. Here's what that looks like at the moment:
    PHP Code:
    foreach ($pdo as $row)
    {
        
    // Declare the object
        
    $object = new Blog();

        
    // Declare associate array (could probably do $object->associated or something instead)
        
    $associated = array();

        
    // Loop through all result columns ($i)
        
    for ($i=0$meta $pdo->getColumnMeta($i); $i++)
        {
            
    // If the column's table name corresponds to that of our object..
            
    if (strtolower($meta['table']) == strtolower(get_class($object)))
            {
                
    // Add column value to our object
                
    $object->$meta['name'] = $row[$i];
            }
            
    // Otherwise we're dealing with an association..
            
    else
            {
                
    // If no key in $associated corresponds to the column's table name..
                
    if (!array_key_exists($meta['table'], $associated))
                {
                    
    // Add that key, which is an object
                    
    $associated[$meta['table']] = new $meta['table'];
                }
                
    // Add column value to the object
                
    $associated[$meta['table']]->$meta['name'] = $row[$i];
            }
        }
        
    // Move our associate array to our object
        // (Could maybe access $object->associated directly instead
        // using the $associated array)
        
    $object->_associated $associated;

        
    // Add our object to an array
        
    $objects[] = $object;

    Obviously you have to do something different if you want has_many, since the result array looks quite different.

  15. #90
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pakmannen View Post
    I'm a bit curious if you've done any eager loading (joins) with has_many relationships? How did you go about doing that?
    I haven't done it yet, but I have thought about how it would work. Basically what happens is that for every row in the joined has_many table, you have the data for the first table. In other words, you'll have redundant data, so you have to keep track of primary keys, only creating new objects if an object with that primary key doesn't exist.

  16. #91
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by 33degrees View Post
    I haven't done it yet, but I have thought about how it would work. Basically what happens is that for every row in the joined has_many table, you have the data for the first table. In other words, you'll have redundant data, so you have to keep track of primary keys, only creating new objects if an object with that primary key doesn't exist.
    Yep, that was what I figured out as well. I've got that part working but is temporarily stuck with making the has_many's themselves part of an array..

  17. #92
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alright, so here's eager loading with belongs to and has many capabilities. I'm sure I've missed something somewhere but it appears to be working.
    PHP Code:
    public function findBySql($sql$values = array())
    {
        
    // Get DB instance
        
    $db Database::getInstance();

        
    // Prepare and execute SQL
        
    $pdo $db->prepare($sql);
        
    $pdo->execute($values);

        
    // Declare array
        
    $objects = array();

        foreach (
    $pdo as $row)
        {
            
    // Define the object for this row
            
    if (!array_key_exists($row[0], $objects))
            {
                
    $object = new $this->_table();
            }
            else
            {
                
    $object $objects[$row[0]];
            }

            
    // Loop through row columns with meta data
            
    for ($i=0$meta $pdo->getColumnMeta($i); $i++)
            {
                if (isset(
    $row[$i]))
                {
                    
    // Standard procedure
                    
    if (strtolower($meta['table']) == strtolower(get_class($object)))
                    {
                        
    $object->$meta['name'] = $row[$i];
                    }

                    
    // Belongs to procedure
                    
    else if (isset($this->_belongsTo) && in_array($meta['table'], $this->_belongsTo))
                    {
                        if (!
    array_key_exists($meta['table'], $object->_associated))
                        {
                            
    $object->_associated[$meta['table']] = new $meta['table']();
                        }
                        
    $object->_associated[$meta['table']]->$meta['name'] = $row[$i];
                    }

                    
    // Has many procedure
                    
    else if (isset($this->_hasMany) && in_array($meta['table'], $this->_hasMany))
                    {
                        if (!
    array_key_exists($meta['table'], $object->_associated))
                        {
                            
    $object->_associated[$meta['table']] = array();
                        }
                        if (
    in_array('primary_key'$meta['flags']))
                        {
                            
    $object->_associated[$meta['table']][] = new $meta['table']();
                        }
                        
    end($object->_associated[$meta['table']])->$meta['name'] = $row[$i];
                    }
                }
            }

            
    // Add object to array if not a duplicate
            
    if (!array_key_exists($row[0], $objects))
            {
                
    $objects[$object->id] = $object;
            }
        }

        
    // Return objects
        
    return $objects;

    What bothers me a bit is that I, for every row, loop through the $objects array to see if the current row already exists. Not sure if there is any way around that though. Well, input and thoughts appreciated!

    Edit - Updated code
    Last edited by pakmannen; Sep 22, 2007 at 10:59.

  18. #93
    SitePoint Guru 33degrees's Avatar
    Join Date
    May 2005
    Posts
    707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not index the objects by their primary key, and do an array_key_exists?

  19. #94
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right of course, I updated the code above to include this. Thanks for spotting..

  20. #95
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    368
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    actually mysql archive tabletype doesnt have primary key

    people keep forgeting that mysql is a collection of tabletypes:
    *myisam
    *innodb
    *memory
    *ndb
    *archive

    and so on

  21. #96
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kyberfabrikken View Post
    getColumnMeta() is a bit weird. I don't use it myself. Instead, I implement reflection on a per-driver basis. In practise, I use MySql 90% of the time, and SqLite for the remainder. Here's a snippet:
    Damn, I just found a problem with getColumnMeta(), or at least I think it's a problem. When combining a JOIN with ORDER BY, the primary_key flag is dropped, for some reason. Guess I have to look into a more stable solution.

    I don't understand how what Kyber wrote on the previous page would help though, since it appears to get information about a specified table. What I need is to know what table a certain column belongs to. Which may not be possible with all databases, but I'm only interested in MySql, where I know it works.

  22. #97
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by pakmannen View Post
    Damn, I just found a problem with getColumnMeta(), or at least I think it's a problem. When combining a JOIN with ORDER BY, the primary_key flag is dropped, for some reason. Guess I have to look into a more stable solution.

    I don't understand how what Kyber wrote on the previous page would help though, since it appears to get information about a specified table. What I need is to know what table a certain column belongs to. Which may not be possible with all databases, but I'm only interested in MySql, where I know it works.
    You should not be using getColumnMeta() at all, due to the warning comments about it in the PHP manual. I am not sure what query you should use to dynamically get the relationships, but SHOW TABLE STATUS looks like it might be it (you will have to try it).

    I would highly recommend using variables in your model to define the relationships instead, as it will be portable across multiple database engines. MyISAM does not support the foreign key relationships your ActiveRecord pattern is trying to dynamically get from the database, so right now this method won't work with any other engine than InnoDB.

  23. #98
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hm, not sure we're on the same page here. I'm not trying to get any foreign key relationships dynamically (at least I don't think so) and I do have variables in my Active Records that defines the relationships:
    PHP Code:
    class Blog extends ActiveRecord
    {
        
    $_hasMany = array('comment');

    The problem I'm having is that when you Join two tables together, they are combined into a single resulting array. Sort of like this:
    PHP Code:
    Result Array(
      [
    0] = 1      // id of table #1
      
    [1] = 'text' // text of table #1
      
    [2] = 5      // id of table #2
      
    [3] = 'text' // text of table #2

    This makes it impossible to know from which table the keys were fetched. That is basically the information I need, and getColumnMeta() supplies exactly that.

  24. #99
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    698
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Might be better to use aliases to get this info. I'm sure you are building queries dynamically so it should not be overly difficult.

    Just prefix column names with the table name maybe using two __ as a separator. Or possible use the active record class name. Just remember that some databases will change the case of returned column names. And remember that you might need to join against a table more than once so you might want to add an optional instance identifier as well.

    SELECT
    person.person_id as person__person_id__instance1

  25. #100
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    Might be better to use aliases to get this info. I'm sure you are building queries dynamically so it should not be overly difficult.

    Just prefix column names with the table name maybe using two __ as a separator. Or possible use the active record class name. Just remember that some databases will change the case of returned column names. And remember that you might need to join against a table more than once so you might want to add an optional instance identifier as well.

    SELECT
    person.person_id as person__person_id__instance1
    Yeah, basically what 33degrees said a while back:
    Rails, on the other hand, seems to handle this by explicitly declaring all the fields to return, prefixing each with a table identifier; it could be because not all databases allow you to do what my code above does.
    I guess it's the most sensible approach overall, as it'll probably work everywhere.


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
  •