Database class model

[RubenBuijs]

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

class Vis_DbController
{
    function __construct(){}

    public function & getInstance(){} // like factory
}
?>

Mysql/Mssql, etc driver:

<?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

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

[lazy_yogi]

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

[Mike Borozdin]

I think getInstance () method should be static:

<?php 
class Vis_DbController 
{ 
    function __construct(){} 
    public static function & getInstance(){} // like factory 
} 
?>

[lastcraft]

Hi...

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

[arborint]

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:

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.

class DbSequence
{
function nextID ()
function create ()
function reset()
function isError ()
function getMessage ()
} 

[trini0]

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...

[RubenBuijs]

@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?

[otnemem]

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

[lastcraft]

Hi...

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.

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.

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.

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

Actually it's about a dozen lines of 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

[lazy_yogi]

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

[otnemem]

Hi!

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

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.

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.

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

[arborint]

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.

[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?

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?

[otnemem]

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

[arborint]

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.

[lastcraft]

Hi...

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.

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.

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

[arborint]

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.

[lastcraft]

Hi...

Were those PHP projects?

Yep.

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.

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.

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

[trini0]

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.

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...