Database Abstraction

In the last chapter, we also built a simple database abstraction layer. Now that we know about two different databases (MySQL and PostgreSQL), you probably understand the need for such an abstraction layer. We have already discussed the database abstraction layer in the previous chapter, so if you haven't already read the corresponding section in the previous chapter, you may want to do so now. In this section, we will revisit the database abstraction layer and implement the PostgreSQL version.

We already learned how to port a PHP database application from one database to the next in the last section, so we won't go into those details again. Instead, we'll start with a DB.php file that has already been ported to PostgreSQL. The changes from the MySQL version are highlighted:

    class DB
    {
        /* Connection parameters */
        var $host = '';
        var $user = '';
        var $password = '';
        var $database = '';
        var $persistent = false;

        /* Database connection handle */
        var $conn = NULL;

        /* Query result */
        var $result = false;
        var $row = 0;

        function DB($host, $user, $password, $database, $persistent = false)
        {
            $this->host = $host;
            $this->user = $user;
            $this->password = $password;
            $this->database = $database;
            $this->persistent = $persistent;
        }

        function open()
        {
            /* Choose the appropriate connect function */
            if ($this->persistent) {
                $func = 'pg_pconnect';
            } else {
                $func = 'pg_connect';
            }

The first major change is in the open() method. We build the PostgreSQL connection string with the following block of code.

This code gives us a good deal of flexibility in constructing the connection string. For example, if we specify an empty string for the $host parameter, the host= component of the connection string will be omitted. If we didn't omit it, the resulting connecting string would be invalid.

This is an important consideration when the PostgreSQL server daemon (postmaster) is invoked without the --i parameter. Without that parameter, the PostgreSQL server will only accept local connections. A local connection is requested by omitting the host= portion of the connection string:

            /* Build the connection string */
            $connstr = '';
            if (!empty($this->host)) {
                $connstr .= 'host=' . $this->host . ' ';
            }
            if (!empty($this->user)) {
                $connstr .= 'user=' . $this->user . ' ';
            }
            if (!empty($this->password)) {
                $connstr .= 'password=' . $this->password . ' ';
            }
            if (!empty($this->database)) {
            $connstr .= 'dbname=' . $this->database;
            }

            /* Connect to the PostgreSQL server */
            $this->conn = $func($connstr);
            if (!$this->conn) {
                return false;
            }

            return true;
        }

        function close()
        {
            return(pg_close($this->conn));
        }

        function error()
        {
            return(pg_errormessage());
        }

        function query($sql = '')
        {
            $this->result = pg_exec($this->conn, $sql);
            $this->row = 0;

            return($this->result != false);
        }

        function affectedRows()
        {
            return(pg_cmdtuples($this->result));
        }

        function numRows()
        {
            return(pg_numrows($this->result));
        }

        function fetchObject()
        {

The second major change in this version of the DB class is the addition of the $row instance variable. The PostgreSQL fetching functions require an additional parameter that indicates the desired row of the resultset to return. This value is stored in the $row instance variable. It is incremented upon every call to one of the fetch methods until all of the rows of the resultset have been returned. After that point, the fetch methods return false. The row counter is reset when the result is freed or when a new query is executed.

Here, you can see the how the value of $this->row is compared against the number of rows in the resultset:

            if ($this->row >= pg_numrows($this->result)) {
                return false;
            }

Here, the $this->row variable is incremented in the call to the fetch function. Remember the ++ operator, which appears at the end of the variable, will be incremented after its value is used for this fetch operation:

            return(pg_fetch_object($this->result, $this->row++, PGSQL_ASSOC));
        }

        function fetchArray()
        {
            if ($this->row >= pg_numrows($this->result)) {
                return false;
            }

            return(pg_fetch_array($this->result, $this->row++, PGSQL_NUM));
        }

        function fetchAssoc()
        {
            if ($this->row >= pg_numrows($this->result)) {
                return false;
            }

            return(pg_fetch_array($this->result, $this->row++, PGSQL_ASSOC));
        }

        function freeResult()
        {
            $this->row = 0;
            return(pg_freeresult($this->result));
        }
    }
    ?>

We now have a working PostgreSQL version of the DB class in DB.php. At this point, we might want to rename this version to pgsql.php and the MySQL version to mysql.php. This way, it will be very simple to change between the two database implementations. However, one file is merely a drop-in replacement for the other at this point, so you may also choose to keep them named DB.php. To change your underlying database implementation, simply replace one with the other.

This test script will now work with either database (barring differences in connection parameters):

    require_once("DB.php");

    $db = new DB('localhost', 'jon', 'secret', 'library');
    if (!$db->open()) {
        die ($db->error());
    }

    if (!$db->query('SELECT * FROM author')) {
        die ($db->error());
    }

    echo("Number of rows: " . $db->numRows() . "<br />");

    while ($row = $db->fetchAssoc()) {
        echo($row['auth_id'] . " " . $row['auth_name'] . "<br />");
    }

    $db->freeResult();
    $db->close();

Now, imagine if you had DB class implementations for even more databases, such as Oracle or ODBC. You surely understand how useful a database abstraction layer can be now.