What shouldn't be done when accessing distinct database systems - PHP

As I explained in the introduction, Polymorphism can be used in all sorts of clever ways to build smarter applications. But in this particular case, I'm going to demonstrate how to use it to create a simple -- yet robust -- database abstraction layer that can be utilized to access different database systems.

However, I'm going to start on the opposite side. First I'll show you a poor implementation of a sample database abstraction layer, which will take care of working with MySQL and SQLite simultaneously. The key point is that this application isn't going to take advantage of Polymorphism.

Having said that, please take a look at the signature of the following class. As I explained before, it  will be capable of using the two database systems previously mentioned. Here is how the class in question looks:

// define 'DBHandler' class (poor implementation of a class and
lack of Polymorphism)
class DBHandler{
   private $db;
   private $result;
   private $mysqli;
   private $sqlite;
   public function __construct($db,$host,$user,$password,$database){
     if($db!='MySQL'&&$db!='SQLite'){
       throw new Exception('Invalid type of database class');
     }
     $this->db=$db;
     if($this->db=='MySQL'){
       $this->connectMySQL($host,$user,$password,$database);
     }
     else{
       $this->connectSQLite($database);
     }
   }
   // run query
   public function query($query){
     if($this->db=='MySQL'){
       $this->queryMySQL($query);
     }
     else{
       $this->querySQLite($query);
     }
   }
   // fetch row
   public function fetchRow(){
     if($this->db=='MySQL'){
       return $this->fetchRowMySQL();
     }
     else{
       return $this->fetchRowSQLite();
     }
   }
   // connect to MySQL
   private function connectMySQL($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 MySQL database
   private function queryMySQL($query){
     if(!$this->result=$this->mysqli->query($query)){
       throw new Exception('Error running query '.$query.' :
'.$this->mysqli->error);
     }
   }
   // fetch row from MySQL database table
   private function fetchRowMySQL(){
     return $this->result->fetch_array(MYSQL_ASSOC);
   }
   // create SQLite database
   private function connectSQLite($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 SQLite database table
   private function querySQLite($query){
     if(!$this->result=$this->sqlite->query($query)){
       throw new Exception('Error running query '.$query.' :
'.$this->mysqli->error);
     }
   }
   // fetch row from SQLite database table
   private function fetchRowSQLite(){
     return $this->result->fetch(SQLITE_ASSOC);
   }
} 

If you take some time to carefully examine the definition of the above class, you'll see that it's been provided with the ability to perform a few useful tasks, like connecting either to MySQL or SQLite, in addition to running queries, fetching and counting database table rows in both database applications, and so on. Quite simple, right?

In addition, a couple of examples on how to use this class are listed below:

try{
   // use 'DBHandler' class (poorly implemented example)
   $dbh=new DBHandler('MySQL','host','user','password','database');
   $dbh->query('SELECT name,email FROM users');
   while($row=$dbh->fetchRow()){
     echo $row['name'].' '.$row['email'].'<br />';
   }
}
catch(Exception $e){
   echo $e->getMessage();
   exit();
}

try{
   // use 'DBHandler' class (poorly implemented example)
   $dbh=new DBHandler('SQLite','host','user','password','database');
   $dbh->query('SELECT name,email FROM users');
   while($row=$dbh->fetchRow()){
     echo $row['name'].' '.$row['email'].'<br />';
   }
}
catch(Exception $e){
   echo $e->getMessage();
   exit();
}

At first glance, the previous abstraction class seems to fit the requirements for working with both database systems. However, it should be noticed that the code used to perform this process is extremely inefficient.

Why do I say this, if the prior class does what's expected? Well, if you study the respective definitions of many of its methods, you'll see that there's always a conditional statement that checks to see whether the used database system is MySQL or SQLite. This is really a poor approach from a development point of view, and certainly can be even worse if more methods are added to the original class. Imagine how much time will be wasted when updating this abstraction class!

The previous class isn't taking advantage of Polymorphism. This feature could facilitate the maintainability of a complete database application. It could also noticeably improve the way that the different database systems are accessed.

But, how can we take advantage of this feature in a truly useful fashion? To see how a simple database abstraction layer can be built using the functionality provided by a few polymorphic classes, you'll have to read the following section.