SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Member
    Join Date
    Oct 2004
    Location
    The Netherlands
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database class model

    I'm working on a framework for personal and business usage.

    At this moment I do some database research, "what's is a good class model?".
    So I made some start and thought it would be all right to get some feedback from somebody.

    This is my model (used no image, but code):

    Controller class:
    PHP Code:
    <?php

    class Vis_DbController
    {
        function 
    __construct(){}

        public function & 
    getInstance(){} // like factory
    }
    ?>
    Mysql/Mssql, etc driver:
    PHP Code:
    <?php

    class Vis_MysqlDriver
    {
        private 
    $Ci_ResourceID;

        function 
    __construct(){}

        public function 
    connect(){}

        public function 
    disconnect(){}

        public function 
    getResourceID(){}

        public function 
    execute( & $Ps_Sql )
        {
            
    $o_Rowset = & new Vis_MysqlRowset( & $Ps_Sql );
            return 
    $o_Rowset;
        }
    }
    ?>
    and MysqlRowset:
    PHP Code:
    <?php

    class Vis_MysqlRowset
    {
        private 
    $Ca_Query;

        function 
    __construct( & $Ps_Sql ){}

        public function 
    fetchBoth(){}

        public function 
    fetchAssoc(){}

        public function 
    fetchRow(){}

        public function 
    numRows(){}

        public function 
    affectedRows(){}

        public function 
    lastInsertID(){}

        public function 
    freeRecordSet(){}
    }
    ?>
    Is this a well organised class model? What can be done better?
    Thanks, Ruben

  2. #2
    SitePoint Guru
    Join Date
    Dec 2003
    Location
    oz
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeh, looks fine.

    You might like to write an interface from which to have each of your resultset and connection classes to inherit from to force consistent usage between different databases

  3. #3
    SitePoint Wizard Mike Borozdin's Avatar
    Join Date
    Oct 2002
    Location
    Edinburgh, UK
    Posts
    1,743
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think getInstance () method should be static:
    PHP Code:
    <?php 
    class Vis_DbController 

        function 
    __construct(){} 
        public static function & 
    getInstance(){} // like factory 

    ?>

  4. #4
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by RubenBuijs
    At this moment I do some database research, "what's is a good class model?".
    So I made some start and thought it would be all right to get some feedback from somebody.
    Transactions will have a significant impact and so you should add these early. For example you will not be able to have a single global connection if you are running two separate transactions within the same script.

    The easiest solution is to have a conventional connection factory or pool and use a UnitOfWork pattern to hold both the connection and handle the transactional statements. It means that your application/library has to pass the UnitOfWork around, but that's usually what you want anyway.

    UnitOfWork is more of an application or DAO pattern though, and you shouldn't be coding it within your library. The only thing you should really change is to get rid of that nasty singleton. Again the choice of the number of connections is an application one. Libraries shouldn't tell applications what to do, they should provide the most useful services possible.

    The only other point I would make is more general. it's very difficult to code a library before you code the applications that need it. This is bottom up design and top down is far more directed. To code top down write an app. and then see what you actually need. Then write another and take what's common. Once you do that the interface will be obvious (you have already used it after all). Good libraries are the intersections of commonly used code.

    You are clearly trying to shortcut that process (which is fine) and learn at the same time. If you want to learn to write better libraries though, write more apps!

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  5. #5
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have been looking at the many database access liibraries and classes around. Many of the differences are trivially slight. But I notice that how the newly introduced prepared statements are handled is a little up in the air. Are they template based SQL generators? Or are they objects that can themselves do queries. The former is provides clearer separation of duties, but code is more verbose. The latter is more consise but you end up with duplication of functionality in classes and in libararies. A good example is the new mysqli interface where mysql_query(), mysqli_stmt_execute() and a bunch other function all do queries.

    What do people think about how prepared statements should be implemented Are they SQL generators or should they extend the basic DB class so they can do queries?

    Here an quick cut at an interface:
    PHP Code:
    class DbConnection
    {
    function 
    connect ()
    function 
    disconnect ()
    function 
    query ()
    function 
    limitQuery ()
    function 
    commit()
    function 
    rollback()
    function 
    isError ()
    function 
    getMessage ()
    }

    class 
    DbStatement
    {
    function 
    bind()
    function 
    prepare()
    function 
    isError ()
    function 
    getMessage ()
    }

    class 
    DbResult
    {
    function 
    fetchRow ()
    function 
    numRows ()
    function 
    numAffectedRows()
    function 
    lastInsertID()
    function 
    isError ()
    function 
    getMessage ()

    Should the result class have functions like lastInsertID() that are specific to a query type (i.e. SELECT, INSERT...) or should query() return a different class depending on the type of query?

    I will also throw in a class for sequences which I think is a useful addition.
    PHP Code:
    class DbSequence
    {
    function 
    nextID ()
    function 
    create ()
    function 
    reset()
    function 
    isError ()
    function 
    getMessage ()


  6. #6
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    S. Florida, USA
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arborint
    A good example is the new mysqli interface where mysql_query(), mysqli_stmt_execute() and a bunch other function all do queries.

    What do people think about how prepared statements should be implemented
    Off topic for the thread, but for your comment see what I wrote, http://www.sitepoint.com/forums/showthread.php?t=199597
    concerning the mysqli extension, and trying to class it...

  7. #7
    SitePoint Member
    Join Date
    Oct 2004
    Location
    The Netherlands
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @arborint:

    I have some questions:
    - Why is there a commit, rollback function in class DbConnection? Isn't that something for Dbstatement?
    - Why did you made three functions isErr?
    - What is the function of method limitquery, prepare and bind?
    - What is the function of DbSequence class?

    Sorry for all those questions, but I think this could help me very much.

    @lastcraft:

    I don't get you completely: I have one class, maybe more instances. Is that allright or must I have more Db's in one class?

  8. #8
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Bogota
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    The only thing you should really change is to get rid of that nasty singleton. Again the choice of the number of connections is an application one. Libraries shouldn't tell applications what to do, they should provide the most useful services possible.
    For applications writen in PHP, where you only have control per request and not through requests, why would you want to have more that one database connection in your script?

    I can see one having trouble with transactions, but only if you are implementing long transaction - the ones that spans through multiple requests. That's not really a problem in php, you just can't control your database connection through different requests, so you'll end up deciding to give up long transactions.

    I wouldn't recomend writing a connection pool in php.

    HTH,
    - Andrés
    If I have wings, why am I walking?

  9. #9
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by otnemem
    For applications writen in PHP, where you only have control per request and not through requests, why would you want to have more that one database connection in your script?
    Depends on the application, and that's the point. If the app. wants to wrap it as a Singleton that's fine. The library shouldn't force it though.

    Quote Originally Posted by otnemem
    I can see one having trouble with transactions, but only if you are implementing long transaction - the ones that spans through multiple requests.
    If you are integrating multiple systems within a single script you will definitely have multiple transactions. You will likely have them at some stage even for a single resource where parts of the application are more highly decoupled. You will also sometimes want to replace the connection with a mock one for testing.

    I have never worked on a project of more than a single server and not needed multiple transactions. Also most single server projects have involved multiple transactions when porting or backing up data. Hence why I never make my connections, or much else, Singletons anymore.

    Quote Originally Posted by otnemem
    That's not really a problem in php, you just can't control your database connection through different requests, so you'll end up deciding to give up long transactions.
    Long transactions are best avoided altogether. Better to have a session that keeps track of commands so that an undo can be issued.

    Quote Originally Posted by otnemem
    I wouldn't recomend writing a connection pool in php.
    Actually it's about a dozen lines of code...
    PHP Code:
    class ConnectionPool {
        var 
    _connections = array();

        function &
    getConnection() {
            
    $connection = &$this->_findUnusedConnection();
            if (! 
    $connection) {
                
    $connection = &$this->_createConnection();
            }
            
    $connection->begin();
            return 
    $connection;
        }

        function &
    _findUnusedConnection() {
            for (
    $i 0$i count($this->_connections); $i++) {
                if (! 
    $this->_connections[$i]->isOpen()) {
                    return 
    $this->_connections[$i];
                }
            }
            return 
    false;
        }

        function &
    _createConnection() {
            
    $this->_connections[] = &$c = &new Connection(...);
            return 
    $c;
        }

    But yes, this would be exceptional.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  10. #10
    SitePoint Guru
    Join Date
    Dec 2003
    Location
    oz
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Marcus, a couple of questions on the connection pool class

    1. If these connections have been closed and are re-opened for use by another object, does this simply avoid re-creating a new object? Since in this case, the connecting to the db would have to be done all over again each time anyway, so the cost of connecting is the same yeh?

    2. If the connection is not actually closed, just set to unused, then how would you set it to "( ! isOpen )" automatically after the last record is read from it's previous use - or do you code this manually each time when you use the db class?

    3. Finally, if it is not actually closed when finished with a result, when are the connections actually closed? When the script ends?

    Regards,
    Eli

  11. #11
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Bogota
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

    Quote Originally Posted by lastcraft
    Depends on the application, and that's the point. If the app. wants to wrap it as a Singleton that's fine. The library shouldn't force it though.
    Absolutely, this behavior shouldn't be in the database connection class. My point was more about the decision to use a Singleton or not.

    I can see your point about letting the application make that decision, and I agreed with that from the begining

    Quote Originally Posted by lastcraft
    If you are integrating multiple systems within a single script you will definitely have multiple transactions. You will likely have them at some stage even for a single resource where parts of the application are more highly decoupled. You will also sometimes want to replace the connection with a mock one for testing.

    I have never worked on a project of more than a single server and not needed multiple transactions. Also most single server projects have involved multiple transactions when porting or backing up data. Hence why I never make my connections, or much else, Singletons anymore.
    Were those PHP projects? I find the experience of others quite interesting because the nature of PHP is very different from technologies like Java where objects are able to mantain their state through a whole session. In PHP I have to recreate all my objects and put them in the same state I left them the last time I used them. There's no way around this, every request consist of a previous stage where you build up all the stuff from you database (or some kind of storage), and at the end it's all thrown away... to be built once again.

    This is why you cannot have a transaction that runs through a whole session. You can have on or more transactions per request but if you need more than that you'll have to do all sorts of things to simulate a long transaction and in the end, you'll end up using a different connection in each of your requests.

    This is why I think using a Singleton for a Database connection is not so out of line in PHP, or at least not as you make it look

    In Java you are able to use the same db connection for different requests, and that's why it is such a bad idea to have a connection for the whole application, you don't want to have all your clients (requests in the common web application) to be waiting until the first one frees it.


    Quote Originally Posted by lastcraft
    Long transactions are best avoided altogether. Better to have a session that keeps track of commands so that an undo can be issued.
    There's no other choice really.

    Quote Originally Posted by lastcraft
    Actually it's about a dozen lines of code...
    Hmm, I should've seen this comming. What I meant is that there's no need to use it... or better said, I can't find a need to use it.

    Regards,
    - Andres
    If I have wings, why am I walking?

  12. #12
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For applications writen in PHP, where you only have control per request and not through requests, why would you want to have more that one database connection in your script?
    Because you have more than one database. I have done this for several reasons, such a using a local database for the web application, but having a separate "corporate" database on another server that needs to be accessed. Or when you are running multiple database servers for features/perfomance. SQLlite will increase the the use of multiple database connections.

  13. #13
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have some questions:
    - Why is there a commit, rollback function in class DbConnection? Isn't that something for Dbstatement?
    - Why did you made three functions isErr?
    - What is the function of method limitquery, prepare and bind?
    - What is the function of DbSequence class?
    commit, rollback - These are not associated with a single query, but are commands to either commit or rollback all the preceding queries for the connection.

    isErr - I assume that every class needs to report if an error occured and give an error message.

    limitquery - limits the query to an offset and number of records within the full query. The SQL syntax to do this (in different databases) varies so much that I thought it might be better to provide a function. What do others think, should this be a standard function?

    prepare and bind - these are template functions for SQL statements. bind associates a value with a tag and prepare replaces all of the tags with asscoiated values. I actually use the same base class for HTML templates and SQL templates.

    DbSequence - sequences generate an incremental counter in a safe way so that two requests can't get the same value. They are a standard way to do what the MySQL autoincrement field feature does. How do most people handle sequences?
    Last edited by arborint; Oct 4, 2004 at 14:43.

  14. #14
    SitePoint Zealot
    Join Date
    Jun 2004
    Location
    Bogota
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arborint
    Because you have more than one database. I have done this for several reasons, such a using a local database for the web application, but having a separate "corporate" database on another server that needs to be accessed. Or when you are running multiple database servers for features/perfomance. SQLlite will increase the the use of multiple database connections.
    Yes, but I mean more than one connection per database

    However, if you are using more than one database then of course you'll need more than one database connection and give up the singleton to something different.

    I could be missing something here, but I don't think you need more than one connection per database in a given request. Besides, it's good for performace, mostly if you have your databases in a different machine.

    Cheers!
    - Andrés
    If I have wings, why am I walking?

  15. #15
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Agreed, because a PHP request runs in a single thread I also see no reason why you would need multiple connections to the same database.

  16. #16
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by lazy_yogi
    1. If these connections have been closed and are re-opened for use by another object, does this simply avoid re-creating a new object? Since in this case, the connecting to the db would have to be done all over again each time anyway, so the cost of connecting is the same yeh?
    I am assuming (but didn't state clearly - sorry) that PHP closes the connection for real at the end of a script. Really I mean that the transaction has been committed and so the connection can be returned to the pool.

    Quote Originally Posted by lazy_yogi
    2. If the connection is not actually closed, just set to unused, then how would you set it to "( ! isOpen )" automatically after the last record is read from it's previous use - or do you code this manually each time when you use the db class?
    If the client does not release the resource after commit() and another part of the system reopens it before it is reused by the first, then there will be trouble. However, this type of low level code is usually part of some kind of DAO or UnitOfWork scheme, so it's rarely a problem within the short life of a PHP script.

    Quote Originally Posted by lazy_yogi
    3. Finally, if it is not actually closed when finished with a result, when are the connections actually closed? When the script ends?
    Yeah.

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  17. #17
    SitePoint Wizard
    Join Date
    Aug 2004
    Location
    California
    Posts
    1,672
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One other point on connections. Because some databases do not implement any BEGIN COMMIT type syntax, a database class implementing $db->beginCommit() may need to close and reopen the connection to simulate the begin.

  18. #18
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi...

    Quote Originally Posted by otnemem
    Were those PHP projects?
    Yep.

    Quote Originally Posted by otnemem
    There's no way around this, every request consist of a previous stage where you build up all the stuff from you database (or some kind of storage), and at the end it's all thrown away... to be built once again.
    That's right. It's not really about efficiency here (a connection pool will be overkill except for very specific comand line scripts for example, or Gtk apps.). It's about not tying yourself to a single resource.

    The situations that have, or would have, caused me grief are:
    1) Replication. The main database is read only, query batches with writes get sent to another. Actually this could be achieved with a connection class that wraps this, but we didn't do it that way.
    2) Sessions being on a separate server.
    3) Running full test suites (this affects every project).
    4) Scripts moving data from one system to another.
    5) Data on different DB engines. Our list manager is run on Postgres for example.

    Quote Originally Posted by otnemem
    This is why I think using a Singleton for a Database connection is not so out of line in PHP, or at least not as you make it look
    I think it is except at the very top level scripts of an application. At that level you know the architecture anyway. We both agree that doing so at the lowest level is a really bad idea, so I think we only differ on where the threshhold flips over. For me testability is the big issue that pushes it right to the top.

    Quote Originally Posted by otnemem
    Hmm, I should've seen this comming. What I meant is that there's no need to use it... or better said, I can't find a need to use it.
    Connection pools are a bit over the top for web scripts .

    yours, Marcus
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  19. #19
    SitePoint Enthusiast
    Join Date
    Sep 2004
    Location
    S. Florida, USA
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lastcraft
    2) Sessions being on a separate server.
    It just so happens that I made the decision to separate the
    sessions to a separate database,
    and I was banging my head all day on how
    I could create/access two connections (one for the site and one for the sessions), using a singleton/factory.
    Then I came across the post about a connection "pool", and came up with this.
    PHP Code:
    class mPN_DB
    {
        static private 
    $connections = array();

        static public function &
    getConnection($name)
        {
            if (isset( 
    self::$connections$name ] ))
            {
                return 
    self::$connections$name ];
            }

            return 
    self::$connections$name ] = self::connectionFactory$name );
        }

        static private function &
    connectionFactory($name)
        {
            include(
    dirname__FILE__ ) . '/../' $name '.php');

            
    preg_match('/^([a-z]+):\/\/([\w]+):([\w]+)@([\w]+)\/([\w]+)/'$dsn$option);

            switch( 
    $option[1] )
            {
                case 
    'mysqli':
                    require_once( 
    dirname__FILE__ ) . '/drivers/' $option[1] . '/driver.class.php');
                    
    $db = new DB_Mysql($option[2], $option[3], $option[5], $option[4]);
                    break;
            }

            try
            {
                
    $db->connect();
            }
            catch(
    Exception $e)
            {
                
    // Need to do something else more useful here
                
    var_export($e);
                die;
            }

            return 
    $db;
        }

    This way I can have "named" connections ->
    $foo =& mPN_DB::getConnection('main');

    Thoughts/comments...


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
  •