Page 1 of 1

Help with OO Database Interface Design

Posted: Tue Aug 26, 2008 9:22 am
by ericm
I am trying to learn about proper design before proceeding with much more code. My goal is to write a couple of database classes that will help those on my team with no real PHP/programming experience get the information they need into their site design.

What I've done so far is to create a top level DB class that interfaces with Oracle using OCI8. I have a __construct that establishes the connection, as well as a few other functions that accept SQL and returns result sets or success/error codes depending on the action taken.

One thing I read in another thread was a recommendation not to do the DB connect in a constructor, but rather have a seperate connect function. Currently I have a private variable in the DB class that holds the connection information and since all querying has to go through the DB class functions, the connection is available. The whole point of the DB class is to run queries, so I don't see why automatically creating the connection would be wasteful.

I also have individual classes that are more specific to the task at hand. Say, for instance, I was looking to create a blog website -- all of the queries, etc. needed to implement this would be stored in the BlogDB class. My goal here, from a book that I read, was to prevent others using from writing repetitive SQL statements throughout -- if they need something from the database, a function is called. If there isn't a function available for such, we write it.

Is this a good design schema? I just found out that we have the Pear DB package available, but I'm hesistant to use it for several reason: 1. I'm not familiar with it. 2. It's rather complex and I know that we will be sticking with Oracle only. 3. I want to write my own code for learning purposes and so that I know the inner workings of everything. Is this a bad decision on my part?

Any input you can give is appreciated.

Re: Help with OO Database Interface Design

Posted: Tue Aug 26, 2008 11:32 am
by Christopher
This is a complex subject and you are asking a lot of questions that have very long answers. ;) It sounds like you are proposing have a class per application that contains all of the SQL. That is a reasonable first step. There are a number of other ways to solve the problem -- including a number of data access patterns.

Re: Help with OO Database Interface Design

Posted: Tue Aug 26, 2008 11:37 am
by ericm
arborint wrote:This is a complex subject and you are asking a lot of questions that have very long answers. ;) It sounds like you are proposing have a class per application that contains all of the SQL. That is a reasonable first step. There are a number of other ways to solve the problem -- including a number of data access patterns.
I worked on a project several years back where we inherited code from over a decade ago. It was unfortunately poorly designed, which caused us endless headaches. I'm relatively junior when it comes to creating a sound design, but I want to make sure I'm headed in the right direction to avoid future complications as a result of how I'm choosing to design things now.

That said, you said that my decision to extract all SQL code and necessary accessor functions into a seperate class is a "reasonable first step". This leads me to believe you might have a better recommendation? I am open to all ideas and welcome any discussion.

Do you care to elaborate on the data access patterns, etc.?

Re: Help with OO Database Interface Design

Posted: Tue Aug 26, 2008 2:37 pm
by Christopher
ericm wrote:I worked on a project several years back where we inherited code from over a decade ago. It was unfortunately poorly designed, which caused us endless headaches. I'm relatively junior when it comes to creating a sound design, but I want to make sure I'm headed in the right direction to avoid future complications as a result of how I'm choosing to design things now.
That sounds like all of us! ;)
ericm wrote:That said, you said that my decision to extract all SQL code and necessary accessor functions into a seperate class is a "reasonable first step". This leads me to believe you might have a better recommendation? I am open to all ideas and welcome any discussion.
There is no one right way given how many different problems and other constraints there are. You mentions so technical reasons and some team reasons and some personal reasons for your decisions. Perhaps that is why you sensed that I might think there is a better way.
ericm wrote:Do you care to elaborate on the data access patterns, etc.?
Putting data access and business logic into Model classes is probably the most straightforward solution -- and is very flexible. Arranging those Models into a Domain Model is the advanced step. The individual Model classes can be custom or implement various patterns depending on their needs. Table Data Gateway is an easy and popular class for single table classes.

Re: Help with OO Database Interface Design

Posted: Wed Aug 27, 2008 10:16 am
by ericm
I'll research the Table Data Gateway pattern to see if it fits. Do you know of any examples online of good database code design. I'd like to be a bit more assured that what I have in the works is my best option and that I'm doing things the "right" way. Once I have things finished, I'll post my code for critique.

Re: Help with OO Database Interface Design

Posted: Wed Aug 27, 2008 10:42 am
by Christopher
Post some code showing the direction you are heading.

Re: Help with OO Database Interface Design

Posted: Wed Aug 27, 2008 1:07 pm
by ericm
arborint wrote:Post some code showing the direction you are heading.
I've just provided a quick re-type (kind of psuedo code) for the time being. I'm not on my development system.

The OracleHandler at this point is very basic. It creates the connection and allows child classes to execute queries.

Code: Select all

 
 
class OracleHandler
{
 
private $connection;
 
function __construct()
{
   try {
      if (!$this->connection = @oci_pconnect(...)) {
         throw new OracleException(OracleException::ORACLE_CONNECTION_ERROR, oci_error());
   }
   catch (OracleException $e)
   {
       $e->handleError();
   }
}
 
function __destruct()
{
   $this->close();
}
 
function close()
{
   // kills the oracle connection and set $this->connection to null
}
function query($query)
{
   if (!this->isValidQuery($query))
      // throw appropriate ORACLE EXCEPTION
 
   if (!oci_parse)
      // throw appropriate ORACLE EXCEPTION
 
   if (!oci_execute)
      // throw appropriate ORACLE EXCEPTION
 
   if ($this->queryReturnsResultSet)
      if (!oci_fetch_all)
         // throw appropriate ORACLE EXCEPTION
      else
         return $results
}
 
function isValidQuery($query)
{
   // checks to make sure the query is of type SELECT, UPDATE, INSERT or DELETE
   // limit
}
 
function queryReturnsResultSet($query)
{
   // checks to see if the query should return a result set
}
}
 
 
Child classes would extend the OracleHandler and contain various functions relevant to the project the class is being used for. In this example, a blog...

Code: Select all

 
 
class Blog extends OracleHandler()
{
 
function __construct()
{
   parent::__construct();
}
 
fucntion selectById($id)
{
   return $this->query('select * from xyz where id = $id');
}
 
// More function of a similar type.  All SQL would be contained here.
 
}
 
Then finally the PHP file would interface with the child-level class to do blog-specific database actions. A very crued example.

Code: Select all

 
 
require 'Blog.php';
 
<? $b = new Blog(); $id9 = $b->selectById(9); var_dump($id9); ?>
 
 

Re: Help with OO Database Interface Design

Posted: Wed Aug 27, 2008 1:15 pm
by allspiritseve
I think you would be much better off using composition instead of inheritance with your connection class. Especially if you want to start testing your classes (I highly recommend it).

ie:

Code: Select all

$db = new OracleHandler ($config);
 
class BlogGateway   {
 
function contruct ($db) {
    $this->db = $db;
    }
 
function getById ($id)  {
    // I'm using PDO... use your own OracleHandler api here.
    $stmt = $this->db->prepare ('SELECT * FROM blogs WHERE id = :id');
    $stmt->bindValue (':id',  $id);
    return $stmt->fetch (PDO::FETCH_ASSOC);
    }
 
}