Abstraction is the process of identifying the important aspects of an object and ignoring the non-essential aspects. Database abstraction hides the complexities of database by providing a database independent interface. All you need is to change the connection string and your application will be able to run no matter what database is running at the back end. You can easily switch form access to oracle, oracle to mysql, mysql to sybase and so on by changing a single string.

The database abstraction layer has its own pros and cons so depending on the requirements it must be decided if database abstraction layer should be used in an application or not. Mostly small to medium sized applications do not need database abstraction layer as portability is not an issue but if the objective of your application is to add flexibility and ease of maintenance then database abstraction layer can be a good choice for your application.

Pros and Cons of Database Abstraction: 

1. The database abstraction layer enables us to write generic code that can be used to access different databases without rewriting the code.
2. The database abstraction layer enables the application to run on different databases.
3. The database abstraction layer minimizes development time if you have to switch to some other database.
4. The database abstraction layer simplifies the complex tasks.
5. The database abstraction layer helps in portability.
6. The database abstraction layer help prevent SQL injections.
7. Adding an additional layer affects the performance of the system as execution time will be increased.
8. If any database specific function is written by mistake then it won't allow the application to run on multiple databases.
9. The database abstraction layer holds the lowest common feature among all the databases it supports.

PEAR:DB – The Database Abstraction layer:

Consider the below code which retrieves and displays first name, last name and location from USER table of the database “myDatabase”.

<?php
//open database connection
$con = mysql_connect("localhost","root”,”mypassword”);
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
//select the database
mysql_select_db("myDatabase",$con) or die("can not select db");
// execute the query
$query = "SELECT firstName, lastName, location FROM USER";
$result = mysql_query($query) or die (mysql_error());
// iterate through rows and print column data in the form Name – Location
while ($arr = mysql_fetch_row($result))
{
echo $arr[0].” ”. $arr[1].” – “.$arr[2].”\n";
}
// close database connection
mysql_close($con);
?>

The output of this program will be

John Rediff – Nigeria
Lisa Backin – USA
George Michael – UK

The above code follows four simple steps

1. Open database connection
2. Connect to the database
3. Execute query
4. Retrieve and display results

There is no problem with the code but it will be problematic if after sometime the application needs to be ported to some other database. The database will crash in such situation. This is because the above code is using the SQL-specific functions which are not compatible with other databases like Access, Oracle, PostgreSQL, SQLite or Sybase.

Now let us add data abstraction to above code. The class file is included with the PEAR installation directory.

<?php
//include database abstraction layer

include “DB.php”;
//open database connection and connect to database

if (!$con)
{
die('Could not connect: ' . mysql_error());
}
// execute the query
$query = "SELECT * FROM USER";
$result = $con ->query($query);
// iterate through rows and print column data in the form Name – Location
while ($arr = $result->fetchRow() )
{
echo $arr[0].” ”. $arr[1].” – “.$arr[2].”\n";
}
// close database connection

?>

Now our code uses database independent functions and hence it enables our code to work with any database . The only change required is that a new connection string will be passed to connect() function.   For example you can use pgsql://user@server/mydatabase to connect to PostgreSQL database and odbc://user:password@server.com/mydatabase to connect to ODBC. ODBC is the platform independent implementation of database abstraction layer.  

PEAR:DB functions:  

Now let's have a look at a list of PEAR functions that can be used to maintain data abstraction layer and what they do  

DBMS support by Database Abstraction Layers:

 More Database Articles
   Database Security: Step by step guideline
   DBA Tips for Statistics gathering on Oracle Data Dictionary Objects!!
   Oracle-SQL Guide: Look out for Fragmented Indexes
   Beware of Database Myths
   A Guideline for Oracle Instantiation with RMAN!