Page 1: DBAL
<b>Intro</b>:
Abstraction layers are one of the most important concepts in computing and programming today. The idea of an abstraction layer is to provide a standard interface to lower level functions to the higher level function. Examples of this are present in nearly every single computer you use today.
<b>An Example</b>:
A simple example is the 7 layer OSI model. Remember from top to bottom... Application, Presentation, Session, Transport, Network, Data-Link, and Physical. Now that you remember something from networking class, remember the hybrid 5 layer design for TCP/IP? It melds the presentation and session into the application layer. In TCP/IP the data-link layer is an abstraction layer. It hides the underlying physical connections. This is why TCP/IP works on twisted pair, fiber, and coax...
<b>Importance</b>:
The concept of the abstraction layer has surface in many different forms. The term 'middleware' is a form of an abstraction layer. DirectX and OpenGL are also abstraction layers. HAL (Hardware Abstraction Layer) in Windows... yes. Even EVERY programming language is an abstraction layer!
<b>Concept</b>:
We already can deduce that the purpose of an abstraction layer is to hide the underlying layers so that the higher level functions can be written once and be portable. For the article, I'll be focusing on PHP and the database support. PHP5 (and PEAR) introduced a database abstraction layer, but I always like to write my own functions.
<b>The Problem</b>:
Here is a good example. You want to create a PHP program that interacts with a database and display some type of information. How do you program the database calls? Many scripts I have seen actually use direct MySQL calls. What happens if the server can only use PostgreSQL? A problem, that's what. Even if you are going to support one database, it makes it so much easier to use an abstraction layer to provide the functionality you need without the extra stuff.
<b>The Solution</b>:
Here is my code example for your enjoyment. You are free to use and modify this code, but leave the Copyright in it
.
This is a modified abstraction layer that I use for my sites. I have more complex needs such as caching queries and such. This simple dbal script is a perfect starter to make a better script or it is fit to use by itself. With the use of abstraction layers, you are providing portability for your code. While this abstraction layer uses MySQL, you could easily make one that supports other databases and keep the same function names.
<b>Conclusion</b>:
Abstraction layers provide needed simplicity and portability in an ever changing and evolving world. If there were no such thing as an abstraction layer, you would need to be wiring CPUs with code instead of writing a PHP program or C++ program. Think of how much productivity you save by investing a bit more time up front. Careful planning and requirement specifications are a must for programming. Good habits is a plus and the abstraction layers that you make help to ensure that your code will stay portable.
Abstraction layers are one of the most important concepts in computing and programming today. The idea of an abstraction layer is to provide a standard interface to lower level functions to the higher level function. Examples of this are present in nearly every single computer you use today.
<b>An Example</b>:
A simple example is the 7 layer OSI model. Remember from top to bottom... Application, Presentation, Session, Transport, Network, Data-Link, and Physical. Now that you remember something from networking class, remember the hybrid 5 layer design for TCP/IP? It melds the presentation and session into the application layer. In TCP/IP the data-link layer is an abstraction layer. It hides the underlying physical connections. This is why TCP/IP works on twisted pair, fiber, and coax...
<b>Importance</b>:
The concept of the abstraction layer has surface in many different forms. The term 'middleware' is a form of an abstraction layer. DirectX and OpenGL are also abstraction layers. HAL (Hardware Abstraction Layer) in Windows... yes. Even EVERY programming language is an abstraction layer!
<b>Concept</b>:
We already can deduce that the purpose of an abstraction layer is to hide the underlying layers so that the higher level functions can be written once and be portable. For the article, I'll be focusing on PHP and the database support. PHP5 (and PEAR) introduced a database abstraction layer, but I always like to write my own functions.
<b>The Problem</b>:
Here is a good example. You want to create a PHP program that interacts with a database and display some type of information. How do you program the database calls? Many scripts I have seen actually use direct MySQL calls. What happens if the server can only use PostgreSQL? A problem, that's what. Even if you are going to support one database, it makes it so much easier to use an abstraction layer to provide the functionality you need without the extra stuff.
<b>The Solution</b>:
Here is my code example for your enjoyment. You are free to use and modify this code, but leave the Copyright in it
.Code
<?PHP
/*
/////////////////////////////////////////////////
Simple DBAL, modified from ASE (www.ase.cc) (www.aselabs.com)
Copyright 2006 Aron Schatz
There is no warranty offered with this script.
You may use and edit this script but this copywrite notice must remain.
/////////////////////////////////////////////////
*/
class simpledbal
{
var $myconid=FALSE;
var $myqueryid=FALSE;
var $myquerycount = 0;
var $myrowcount = 0;
var $mytime=0;
var $myrow=array();
var $myrows=array();
var $myuser='';
var $mypass='';
var $myserver='';
var $mydb='';
var $myqueries=array();
function simpledbal($server, $user, $pass, $db)
{
//Constructor
$this->myuser = $user;
$this->mypass = $pass;
$this->myserver = $server;
$this->myconid=mysql_connect($server,$user,$pass);
if($this->myconid)
{
if(!empty($db))
{
$this->mydb=$db;
$dbselect = @mysql_select_db($this->mydb);
if(!$dbselect)
{
@mysql_close($this->myconid);
return 0;
}
return $this->myconid;
}
}
return 0;
}
function query($string)
{
if(empty($string))
{
return 0;
}
$this->myquerycount++;
$this->myqueryid=FALSE;
$mtime = microtime(); //Query Times
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;
$this->myquerycount++;
$this->myqueryid=@mysql_query($string,$this->myconid);
if(!$this->myqueryid)
{
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
$a=array();
$a['query']=stripslashes($string);
$a['time']=$totaltime;
$a['failed']=1;
$this->myqueries[]=$a;
return(0);
}
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
$a=array();
$a['query']=stripslashes($string);
$a['time']=$totaltime;
$a['failed']=0;
$this->myqueries[]=$a;
return ($this->myqueryid);
}
function dbclose()
{
@mysql_close($this->myconid);
}
function fetch($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
$row=@mysql_fetch_assoc($resourceid);
$this->myrowcount++;
return ($row);
}
function getrow($resourceid=0) //Alias fetch
{
return $this->fetch($resourceid);
}
function getallrows($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
if($resourceid)
{
unset($this->myrow[$resourceid]);
while($row1=$this->fetch($resourceid))
{
$ret[]=$row1;
}
return $ret;
}
return 0;
}
function freeresource($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
if($resourceid)
{
@mysql_free_result($resourceid);
return (1);
}
return (0);
}
function insertid()
{
if($this->myconid)
{
return(@mysql_insert_id($this->myconid));
}
return 0;
}
/////////// Special Functions
function getarray($table,$clause,$order='')
{
if(@is_numeric($clause))
{
//id
$q=$this->query('SELECT * FROM '.$table.' WHERE id='.$clause.' LIMIT 1');
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return ($row);
}
else
{
if(!empty($order))
{
$order='ORDER BY '.$order;
}
$q=$this->query('SELECT * FROM '.$table.' WHERE '.$clause.' '.$order.' LIMIT 1');
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return ($row);
}
}
function getquery($query)
{
$q=$this->query($query);
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return($row);
}
function getcount($table,$clause='')
{
if(empty($clause))
{
$q=$this->query('SELECT count(*) as count FROM '.$table);
$row=$this->getrow($q);
$ret=(($row) ? $row['count'] : 0);
$this->freeresource($q);
return $ret;
}
else
{
$q=$this->query('SELECT count(*) as count FROM '.$table. ' WHERE '.$clause);
$row=$this->getrow($q);
$ret=(($row) ? $row['count'] : 0);
$this->freeresource($q);
return $ret;
}
}
}
?>
/*
/////////////////////////////////////////////////
Simple DBAL, modified from ASE (www.ase.cc) (www.aselabs.com)
Copyright 2006 Aron Schatz
There is no warranty offered with this script.
You may use and edit this script but this copywrite notice must remain.
/////////////////////////////////////////////////
*/
class simpledbal
{
var $myconid=FALSE;
var $myqueryid=FALSE;
var $myquerycount = 0;
var $myrowcount = 0;
var $mytime=0;
var $myrow=array();
var $myrows=array();
var $myuser='';
var $mypass='';
var $myserver='';
var $mydb='';
var $myqueries=array();
function simpledbal($server, $user, $pass, $db)
{
//Constructor
$this->myuser = $user;
$this->mypass = $pass;
$this->myserver = $server;
$this->myconid=mysql_connect($server,$user,$pass);
if($this->myconid)
{
if(!empty($db))
{
$this->mydb=$db;
$dbselect = @mysql_select_db($this->mydb);
if(!$dbselect)
{
@mysql_close($this->myconid);
return 0;
}
return $this->myconid;
}
}
return 0;
}
function query($string)
{
if(empty($string))
{
return 0;
}
$this->myquerycount++;
$this->myqueryid=FALSE;
$mtime = microtime(); //Query Times
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$starttime = $mtime;
$this->myquerycount++;
$this->myqueryid=@mysql_query($string,$this->myconid);
if(!$this->myqueryid)
{
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
$a=array();
$a['query']=stripslashes($string);
$a['time']=$totaltime;
$a['failed']=1;
$this->myqueries[]=$a;
return(0);
}
$mtime = microtime();
$mtime = explode(" ",$mtime);
$mtime = $mtime[1] + $mtime[0];
$endtime = $mtime;
$totaltime = ($endtime - $starttime);
$a=array();
$a['query']=stripslashes($string);
$a['time']=$totaltime;
$a['failed']=0;
$this->myqueries[]=$a;
return ($this->myqueryid);
}
function dbclose()
{
@mysql_close($this->myconid);
}
function fetch($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
$row=@mysql_fetch_assoc($resourceid);
$this->myrowcount++;
return ($row);
}
function getrow($resourceid=0) //Alias fetch
{
return $this->fetch($resourceid);
}
function getallrows($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
if($resourceid)
{
unset($this->myrow[$resourceid]);
while($row1=$this->fetch($resourceid))
{
$ret[]=$row1;
}
return $ret;
}
return 0;
}
function freeresource($resourceid=0)
{
if(!$resourceid)
{
$resourceid=$this->myqueryid;
}
if($resourceid)
{
@mysql_free_result($resourceid);
return (1);
}
return (0);
}
function insertid()
{
if($this->myconid)
{
return(@mysql_insert_id($this->myconid));
}
return 0;
}
/////////// Special Functions
function getarray($table,$clause,$order='')
{
if(@is_numeric($clause))
{
//id
$q=$this->query('SELECT * FROM '.$table.' WHERE id='.$clause.' LIMIT 1');
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return ($row);
}
else
{
if(!empty($order))
{
$order='ORDER BY '.$order;
}
$q=$this->query('SELECT * FROM '.$table.' WHERE '.$clause.' '.$order.' LIMIT 1');
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return ($row);
}
}
function getquery($query)
{
$q=$this->query($query);
$row=$this->getrow($q);
$row=(($row) ? $row : 0);
$this->freeresource($q);
return($row);
}
function getcount($table,$clause='')
{
if(empty($clause))
{
$q=$this->query('SELECT count(*) as count FROM '.$table);
$row=$this->getrow($q);
$ret=(($row) ? $row['count'] : 0);
$this->freeresource($q);
return $ret;
}
else
{
$q=$this->query('SELECT count(*) as count FROM '.$table. ' WHERE '.$clause);
$row=$this->getrow($q);
$ret=(($row) ? $row['count'] : 0);
$this->freeresource($q);
return $ret;
}
}
}
?>
This is a modified abstraction layer that I use for my sites. I have more complex needs such as caching queries and such. This simple dbal script is a perfect starter to make a better script or it is fit to use by itself. With the use of abstraction layers, you are providing portability for your code. While this abstraction layer uses MySQL, you could easily make one that supports other databases and keep the same function names.
<b>Conclusion</b>:
Abstraction layers provide needed simplicity and portability in an ever changing and evolving world. If there were no such thing as an abstraction layer, you would need to be wiring CPUs with code instead of writing a PHP program or C++ program. Think of how much productivity you save by investing a bit more time up front. Careful planning and requirement specifications are a must for programming. Good habits is a plus and the abstraction layers that you make help to ensure that your code will stay portable.