Using Polymorphism to create a database abstraction layer - PHP

In the previous section you saw how a simple database abstraction layer can be created to access both MySQL and SQLite systems. However, the major drawback with this class was the poor implementation presented for many of its methods, since none of them used Polymorphism to improve the way that database systems are accessed.

Considering this issue, I'm going to define a new set of classes, which this time will take advantage of Polymorphism to work with the two database systems.

Having said that, here is the signature of the first class that I plan to create in this section. This one is simply an abstract interface, from which I'll derive a couple of subclasses for working specifically with MySQL and SQLite.

This abstract class looks like this:

// define abstract 'DBConnector' class
abstract class DBConnector{
   abstract public function __construct($host,$user,$password,$database);
   abstract public function query($query);
   abstract public function fetchRow();
   abstract public function countRows();
}

As you can see, the above abstract class is indeed very easy to follow, since it's merely an interface that defines generically the methods that will be implemented later by the respective subclasses.

As I stated before, these subclasses will be responsible for working specifically either with MySQL or SQLite. Let me show you the corresponding signatures for each of them. Here they are:

// define concrete 'MySQL' class
class MySQL extends DBConnector{
   private $mysqli;
   private $result;
   // connect to MySQL
   public function __construct($host,$user,$password,$database){
     $this->mysqli=new MySQLI($host,$user,$password,$database);
     if(mysqli_connect_errno()){
       throw new Exception('Error connecting to MySQL database
server : '.$this->mysqli->error);
     }
   }
   // run query against database
   public function query($query){
     if(!$this->result=$this->mysqli->query($query)){
       throw new Exception('Error running query '.$query.' :
'.$this->mysqli->error);
     }
   }
   // fetch database table row
   public function fetchRow(){
     return $this->result->fetch_array(MYSQL_ASSOC);
   }
   // count database table rows
   public function countRows(){
     if(!$rows=$this->result->num_rows){
       return 'No database rows were returned by the query';
     }
     return $rows;
   }
}

// define concrete 'SQLite' class
class SQLite extends DBConnector{
   private $sqlite;
   private $result;
   public function __construct($host,$user,$password,$database){
     $this->sqlite=new SQLiteDatabase($database);
     // this statement should be executed only once
     $this->sqlite->query("BEGIN;
CREATE TABLE users (id INTEGER(4) PRIMARY KEY, name CHAR(255),
email CHAR(255));
INSERT INTO users (id,name,email) VALUES
(NULL,'User1','user1@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User2','user2@domain.com');
INSERT INTO users (id,name,email) VALUES
(NULL,'User3','user3@domain.com');
COMMIT;");
   }
   // run query against database
   public function query($query){
     if(!$this->result=$this->sqlite->query($query)){
       throw new Exception('Error running query '.$query.' :
'.$this->sqlite->error);
     }
   }
   // fetch database table row
   public function fetchRow(){
     return $this->result->fetch(SQLITE_ASSOC);
   }
   // count database table rows
   public function countRows(){
     if(!$rows=$this->result->numRows()){
       return 'No database rows were returned by the query';
     }
     return $rows;
   }
}

Definitely, things are getting really exciting now! As you can see, the two subclasses listed above present the same methods (remember that they were derived from the parent "DBConnector"), but in this case each of them is implemented differently so they can work with either MySQL or SQLite.

Of course, defining these child classes in this way implies having two completely independent structures, which can be easily updated with minor hassles. Nonetheless, the most important thing to note here is that I created two polymorphic classes, since they belong to the same family of objects, but behave differently using the same methods. Quite good, right?

Having these handy polymorphic classes at our disposal, it's possible to create highly expansible database abstraction layers. If a new database system needs to be added to the layer in question, the process is reduced only to deriving the concrete subclass that deals with that specific system. Period.

However, you may be wondering…how does a given application know which class to use according to the type of database system being utilized? The answer is that it simply uses a factory class that returns to client code the correct type of object to work with MySQL, SQLite or whatever system you may want to incorporate into your application.

To demonstrate the previous concept, below I included the signature of a simple factory class. As I said before, it is tasked with spawning the correct type of database object, according to the database requirements of a specific application.

Given that, here's how this brand new class looks:

// define 'DBFactory' class
class DBFactory{
   // create database class instance
   public function createDB
($db,$host='',$user='',$password='',$database='db.sqlite'){
     if($db!='MySQL'&&$db!='SQLite'){
       throw new Exception('Invalid type of database class');
     }
     return new $db($host,$user,$password,$database);
   }
}

Wasn't that simple? I bet it was! As you can see, the above factory class implements the required logic to create two specified database objects for working with MySQL and SQLite. Logically, after instantiating the correct type of object, the procedure for handling a given database system is only a matter of using its respective methods.

Now, do you realize the convenience of working with polymorphic objects? I hope you do.

Okay, having defined the group of classes required to implement this simple database abstraction layer, it's time to leap forward and develop a functional example, where all these classes will be put to work in conjunction. Doing so, you'll have a better idea of how Polymorphism can play a relevant role when using multiple database systems.

To see how the previously defined classes will be used together in the same practical example, keep reading.