PHP abstract database connectivity
When we write applications in php-mysql/oracle or some other database we use some driver to connect to the databse. For example libmysql (in newer versions it's replaced by php-mysqlnd or widely known as php-mysql native driver) is a commonly used driver which gives you the ability to call functions like mysql_connect, mysql_query, mysql_fetch_assoc etc.
Now consider if you've written your application for mysql database and want to connect it to the mysqli (mysql Light) or oracle or some other database, what will you do?
Since you've hardcoded the functions into the application in all the scipts, you have to change the function calls, so that it can connect to the databases like mysqli, oracle etc. Now you've changed your codes to meet oracle (oci8) library functions like oci_connect, oci_query, oci_fetch_assoc etc.
hmm... now it works for oracle.
wait ... now it need to get back to the mysql connections. Rewrite the codes again?
Got a better idea. Why don't you create separate scripts for mysql, mysqli, oracle etc. And now we can select which ever database we want to connect to. Clever.. huh.
Not so fast. Now I decided to use all the databases togather. Thats gonna be some problem. Again when we switch the scripts thats gonna increase the complexity of the application a lot.
What can we do to reduce the complexity of the dynamic script selection? One intelligent answer (some may not agree!) might be to create abstract database connectivity class. Check the following code.
abstract class Database
{
// Connects to the database using host, user and password
// as paramaeters. Uses database as default database.
abstract function Connect($host, $database, $user, $password);
// Returns true is connection is still open.
abstract function IsOpen();
// Closes the connection.
abstract function Close();
// Executes an SQL statement and returns a datatable object
// if the SQL returns a row set
abstract function Execute($sql);
// Executes an SQL statement and returns a resource object
// if the SQL returns a row set
abstract function ExecuteRaw($sql);
// Begins a transaction.
abstract function Begin();
// Ends the current transaction and saves
// changes to the database.
abstract function Commit();
// Ends the current transaction and discards
// changes to the database.
abstract function Rollback();
// Throws a new exception
function ThrowException($message)
{
throw new Exception($message);
}
};
?>
hmm... now you can create mysql database class, oracle database class and define the abstract functions from the abstract database class to reality. Very clever.... hahahaaa
What if I say now you don't have to write a line to manage abstract layer for database. Because it's already there.
PDO means Php Data Object. You can learn more about PDO from the php PDO manual. Form the manual,
"The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. ... PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data. PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility."
Sounds familiar? What we have been trying to do is out there.
(to be continued ...)
